Phil Factor

16 August 2018

• SQL Change Automation
• Database Builds and Deployments
• Database Documentation

Documenting your Database with SQL Change Automation

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.