Phil Factor

3 August 2018

2 Comments

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

Practical PowerShell Processes with SQL Change Automation

This article uses the PowerShell cmdlets of SQL Change Automation (SCA), formerly DLM-Automation, to take the source code of a database from a directory, validate it, document it and then create a NuGet package of it. Up to this point, the script uses a very similar approach to that described in SQL Change Automation with PowerShell Scripts and Deploying Multiple Databases from Source Control using SQL Change Automation.

In these previous articles, we simply placed the validated database source and the documentation into a NuGet package and used the Sync-DatabaseSchema to synchronize the schema of a target database with the validated source and saved the migration script that was generated to the same directory in which we stored the NuGet package.

This article now takes forward the story of the NuGet package. I show how to:

• Extract the documentation from the NuGet package, and install it in a website.
• Perform code analysis of the validated source, using the command-line version of SQL Code Guard, and display a report that lists all the problems in the code, and their locations, as an HTML page.
• Use the NuGet package as a Source, to produce a migration script, and then use it to upgrade a target database to the same version as the one defined by the NuGet package.
• Save the migration script, and a browser-based report of what has changed on the target machine.

This article, along with the previous two, are designed to make it easy to put together a script for special purposes. For a full explanation of some of the more utilitarian parts of the script, please refer to the previous articles.

Working with NuGet

Before we start, a word about NuGet. A NuGet package is a single ZIP file, with the .nupkg extension, the contents of which must follow certain conventions. A NuGet package generally contains compiled code (DLLs) as well as other files related to that code. It also holds a descriptive XML manifest (a .nupsec file), for information such as the package ID (the database name in this case) , version number (e.g. 1.0.1) and author.

For security reasons, NuGet packages must be signed, and a signed package must be immutable, including the manifest. So, while it’s easy to read the contents of an existing NuGet package, you can’t change those contents without invalidating the package signatures.

SCA uses the NuGet format for storing the source code, or metadata, of a database, as a ‘SCA artefact’. This allows database developers to have the choice of hosting versions of database code, either privately in the cloud, for workflow systems such as Visual Studio Team Services, or on a private network, or on a package server. It allows packages to be made available only to a specific group of consumers.

Although zip files can be encrypted with AES-256 encryption, the NuGet convention has no common standard for doing so. This prevents any sort of sensitive data being stored in a NuGet package and means that great care is needed in publishing a NuGet package to make sure that the database code is not distributed beyond those needing access to it.

Currently, SCA can add the documentation of a database to the NuGet package, but no more than that. As the documentation is about the database code, this makes sense. After all, it is designed to be just a database metadata package. If you need more than that, it is probably better to create a second NuGet artifact file for such things as data, scheduled SQL Agent tasks, SSIS projects, PowerShell scripts, R packages and so on. Since a NuGet package is just a ZIP file, with a .nupkg extension, it is very easy to create one. The simplest approach would be to create the folder structure on your local file system, then create the .nuspec file directly from that structure, using wildcard specifications. Nuget.exe’s pack command then automatically adds all files from the nuspec file, other than folders that begin with a dot. This means that you don’t have to specify in the manifest all the files that you want to include in the package, so you can liberally add all the files that are needed.

Defining the Task

In this demonstration, we will use the good old NorthWind database, the source of which is in a directory.

In this case, all objects are stored separately in their own file. Tables, for example …

We create an empty version of the NorthWind database, on a target server, and then we set the PowerShell script going…

…and if all goes well, we get the NorthWind database documentation:

We also get a list and details of all the code smells that need fixing at some point:

We get a browser-based report of what has changed on the target machine (everything in this case as it is a blank database):

And we get a text version of the build script:

Less visible as an end-product is a NuGet package that can be used subsequently with SCA PowerShell cmdlets to deploy (‘Publish’) the database as many times as required.

The PowerShell

The PowerShell script is well documented, so I’m going to present it without too much elaboration. It uses a Hashtable, $config, to store the configuration data. This gives us some versatility, especially when updating many target databases, as we can iterate through the hashtable. It also separates the data from the process.

We need some variables to create the config hashtable, but this is just for convenience. You have quite a lot of choices in how you deal with such things as databases and file locations since you can alter them at the level of the hashtable or the initial variables that are used in its construction. Every site has its own naming conventions.

So, that is it. Remember, that this is just for demonstrating the PowerShell processes and not everything is done to production standards.

Note that the system is versatile. For example, where the change, or migration, script needs to be checked and amended, before being executed on the target database, you would need to get signoff for the change script in $update.UpdateSql before the Use-DatabaseReleaseArtifact cmdlet is executed. It is likely that every server would have a different change script, depending on the metadata, so this would have to be done on every target of a deployment that needed a signoff.

Extracting the documentation without unzipping

There is one interesting aspect I didn’t include in the script. The $documentation object that is output by the New-DatabaseDocumentation cmdlet contains the documentation so, if you can publish the documentation at this stage, then as an alternative to unzipping the NuGet file you can do this to get documentation out to the website:

Conclusion

The use of a NuGet file merely as a read-only container of files comes as a slight shock to the seasoned database developer, especially when one is used to all the wonders of Chocolatey. However, it makes sense to have this for the database metadata and its documentation, especially when the database is a full participant in an application development process, using applications such as TFS, and deploying to cloud and remote servers.

However, the seasoned database developer will still be wondering about all the extras such as scheduled jobs, alerts and ETL. In SCA culture, this is kept very separate because there is no common way of handling them. To show how to create separate NuGet packages for extra bits will be a topic for a future article.