Phil Factor

7 October 2019

• SQL Change Automation
• Database Builds and Deployments

Recreating Databases from Scratch with SQL Change Automation

You are using SQL Change Automation (SCA), or perhaps SQL Compare, to prepare development databases. You have the routine task of updating a list of development databases, on various instances, from the current source of the database. This isn’t exactly rocket science, but it needs to be done often, and so must be entirely automated. You don’t care what is on the existing copies of the databases, so you want to “kill and recreate”. You also want the databases correctly versioned and checked, and you need the code analysis too.

I’ll show how to automate all this, keeping things simple to start with, and ignoring the three elephants chewing meditatively on branches on the corner of the room. These are called ‘development-data’, ‘passwords-in-script’ and ‘database-in-use’. Firstly, development databases are no use without data, secondly, you can’t leave passwords in scripts and, finally, you shouldn’t and would be unlikely to want to, kill a database if it is in use.

Having demonstrated the basic principles of how we get SCA to recreate databases from scratch, I’ll show a final script that deals with the three elephants humanely.

The basic script

We’ll start the script by itemizing the target databases, using a connection string for each. At the same time, we specify the source and the details about the current build and database version.

We delete the database if it currently exists. Then we recreate each of the databases by using an SCA release object. First, we create a build artifact from the source (a single build script, or a set of object build scripts), adding to it the project name, version and description. From that, we create a release artifact. There is always one build artifact per database version, but generally we need a separate release artifact for each target. However, here, we can be certain all targets are identical, so having created it the first time, we simply reuse the same release object on each empty database.

The elephants in the room

Now, let’s look at the elephants, ‘database-in-use’ and ‘development-data’, and then the baby elephant ‘passwords-in-script’.

Database-in-use

As we have created a connection with the server, it is easy to check whether the database is in use. We’ll simply see if anyone has an open session, and if so, we won’t delete the database. If the number is greater than zero, we can’t deploy the new version of the database, but we want to press on with the other databases because the chances are that only one or two developers are working late or have left a session open in SSMS after packing up for the day.

You can do this in SQL, as follows:

However, SMO has a built-in method of doing this called srv.GetActiveDBConnectionCount($databaseName), so we’ll use that.

One precaution of checking for existing connections is that we mustn’t leave our own connections lying around when we use the PowerShell IDE, so we need to close our own connections. This isn’t necessary with the command line script as it is done automatically by .NET when the PowerShell session ends.

Development-data

I admit that where each database requires a lot of data, it saves a lot of time and space to use SQL Clone for this. You build-and-fill one database, then distribute clones of it to all the development instances. Otherwise, we can just use SCA and store the data in a separate directory as native BCP files. You can do it other ways if you don’t mind waiting longer.

I use a PowerShell script to read BCP data out of a database (see Scripting out SQL Server Data via PowerShell). It must match the metadata of the tables in the databases that you are creating. If your new version of the database has altered the tables, then you will need an initial step of doing a single, initial build to an existing version of the database using SCA, using migration scripts where necessary, and then saving the data from that initial build.

The process of reading the data in is relatively straightforward using command-line BCP. Here is a fragment that does it, using a connection string and a SMO database connection. After the BCP session is completed for the databases, all the database constraints are reenabled (they are automatically disabled by BCP to allow the import to succeed whatever order you import the tables).

Passwords-in-script

This only applies if one or more of your targets is on a server that can only be reached via SQL Server credentials, such as a SQL Server in a container, or an Azure connection. Here we simply leave out the password from the connection string. We store the passwords in an encrypted XML file in your user area, using Import-CliXml and Export-CliXml in PowerShell. The downside is that you will be asked for the password to create the password file, if the file isn’t there. The Export-Clixml cmdlet encrypts credential objects by using the Windows Data Protection API. The encryption ensures that the contents of the credential object can be decrypted only by your user account and on only that computer.

The reason that a connection string is so useful is that everything about a connection is stored there and read by the driver, even such things as connection timeouts, and this information is easy to extract once you turn it into a .NET object. Once it is a working script, this should be pretty safe, but it isn’t if you wander off to lunch without locking the Windows session.

This complicates the code and has the disadvantages, and maybe also the advantages, of making the code look more complicated than it is.

The final code

Here is the complete PowerShell code.

Conclusions

It is a curious thought that we often waste time synchronizing development databases to bring them up to the latest version when it is often better, quicker and almost easier to do a clean build and, if you need data, just BCP the data into the empty database.

There comes a point when the amount of data needed precludes this option and a tool like SQL Clone is needed. However, for smaller data volumes, SCA provides a have a nice clean version of the data for development work every time a new version is released.