16 August 2018
16 August 2018

Documenting your Database with SQL Change Automation

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

It would be wrong to portray SQL Change Automation (SCA) as being suitable only for epic project deployments, of the sort described in my previous article. It can do smaller tasks as well. To demonstrate, I’ll show how you can use SCA to check that the source code builds a database, and to provide a web-based documentation of the result, within the local domain. The documentation will help a team-based development because it records dependencies that otherwise only become apparent on the live database, and it exposes a range of useful properties that can’t be garnered from the build script.

The PowerShell

You are going to need to use a SQL build script as a source for this task. SCA prefers a SQL Source Control project, or SQL Compare project, but will cope with a directory containing one or more SQL Scripts that can combine to build a database. SSMS can produce these script for you, but don’t include the Data Control Language (DCL) stuff, such as the database users. SCA is canny enough to arrange and run the files in the right order, to deal with any dependencies. Databases must be built in the right order, after all.

SQL Doc requires a live working database, to generate the documentation. In this script, SQL Doc uses the temporary database created by the Invoke-DatabaseBuild cmdlet. It scripts out the database objects and gives information about database properties.

The Invoke-DatabaseBuild cmdlet builds a database project by checking whether the database definition can be deployed to an empty database. It the build is successful the cmdlet creates an IProject object that represents the built project, which can be used as the input for other SQL Change Automation cmdlets. The cmdlet can, if no SQL Server is available, create a temporary copy of the database on LocalDB. Instead of creating a database, as is the case if your login doesn’t have the necessary permissions, it is possible to use an existing database as the build target, as long as you don’t care about its existing contents, or having two SCA-based scripts that use the same target running concurrently!

Assuming the build succeeds, the New-DatabaseDocumentation cmdlet then takes the output of the Invoke-DatabaseBuild cmdlet, as IProject object, and generate documentation for the schema contained within it. It creates a SchemaDocumentation object that represents the documentation for a validated database project.

To generate the documentation, the cmdlet once more creates a temporary copy of the database. The same rules apply as to the Invoke-DatabaseBuild cmdlet. This double-build makes for a slow process. It is best to avoid doing documentation as part of the main build process and instead do it separately on a schedule as an overnight process, since it has no dependencies on the main build.

The database documentation website

The resulting website is far quicker to browse than SSMS, and it provides at-a-glance details that would be difficult to get any other way, without tapping in SQL, including all the information from the extended properties.

It allows members of other teams, such as ops and governance, to get up-to-date access to what is in the build. If you have PHP on your website, it is very easy to add ways of searching the documentation, such as via ‘brute-force’ regex searches, and the results are reasonably quick. Once one gets used to the interface, it becomes an irritation if you no longer have the website documentation!

Conclusion

It takes some time to generate database documentation, so it is tempting to abandon it as a routine part of the build and release process. It is much better to do it separately, as a parallel process to the build, but with the same tools.

If you abandon the documentation, you not only make it a bit more difficult for other development team members, such as database developers and DBAs, but also you risk losing some of the DevOps exchange of information and advice that is so useful for rapid releasing and deployment.

If documentation needs to be done remotely, rather than within the local domain, then it makes sense to use the standard SCA method of adding the documentation in the NuGet build artifact package, and then unzipping the build artifact to deploy the various documentation files. I have shown how to do this in previous articles.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

You may also like

  • Webinar

    Extending DevOps practices to SQL Server databases with Redgate - with Git & Jenkins

    Join Microsoft Data Platform MVP Grant Fritchey to learn how Redgate’s Database DevOps solution works with Git and Jenkins to improve your database development and deployment processes.

  • Webinar

    How to keep your delivery processes secure with Database DevOps

    Extending DevOps practices to the database, brings additional advantages, ensuring you protect personal data across your SQL Server estate, while also improving the efficiency and quality of software delivery. We’ll explore the impact database DevOps has on regulatory and compliance requirements and how approaches such as automation, can improve accuracy, transparency, and faster recovery across the entire database development cycle.

  • Event

    IP EXPO Europe 2018

    IP EXPO Europe is Europe’s number ONE IT event for those looking to find out how the latest IT innovations can drive their business forward. Visit Redgate at Stand D27

  • Webinar

    Extending DevOps Practices to SQL Server – Branching & Merging with Git

    Learn how to automate your database build and deployment processes using Redgate’s solution with Git, Jenkins and Octopus Deploy. See how to branch and merge database changes with Git.

  • University

    Take the SQL Change Automation course

    In this course, you’ll learn how to use SQL Change Automation to capture database changes in migration scripts that can be customized and used for deployments. This gives you full control over the deployment. You’ll also learn how to use these migration scripts as part of an automated CI/CD pipeline.

  • Forums

    SQL Change Automation Forum

    Continuous integration and automated deployments for your SQL Server database