3 August 2018


3 August 2018


Practical PowerShell Processes 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.

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:


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.

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 - with Git & Bamboo

    Learn how to automate your database build and deployment processes using Redgate’s solution with Git and Atlassian Bamboo

  • Article

    Database CI with Redgate SQL Change Automation, PowerShell and Jenkins

    While the practice of Continuous Integration (CI) started with application code, you can apply the same principles to databases. Database CI is the process by which we build, test and deploy the database on commit of every database change, and report any errors. In this way, CI reduces your feedback loop such that changes that

  • Article

    Database Continuous Integration with SQL Clone and SQL Change Automation

    When you are working as part of an application development team, it is worth settling down into a routine of doing a daily build from source control, and then provisioning test and development instances of the database. One major advantage is that the code and database can be scrutinized as early as possible, and you

  • Article

    Staying In-sync with drift correction

    Getting everyone in a team behind a process change is hard. As a database developer, not only do you need to champion the new process within your own team, but you also need to extend the olive branch to your DBA to ensure everyone is on-board with the changes. This is where you may encounter

  • 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.

    If you’re interested in learning more about automating database changes with the state-based approach, then see the SQL Change Automation with SQL Source Control course.

  • Forums

    SQL Change Automation Forum

    Continuous integration and automated deployments for your SQL Server database

  • Chad Liu

    Hi Phil,
    It is an excellent article. Could you please explain the markup tag

    in the last several lines of your script? I’ve got stuck at that point where the powershell console complains about the syntax error as below.

    < : The term '<' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a
    path was included, verify that the path is correct and try again.


  • Phil Factor

    Thanks, Chad; I’m also grateful for you for pointing out a place where WordPress has taken a vengeance on my article. I’ve asked Redgate to remove both of them. In the meantime, you can safely take them both out of the script and the PowerShell will run a lot better without.