Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Automating Database Deployments To and From Source Control With SQL Compare

DZone's Guide to

Automating Database Deployments To and From Source Control With SQL Compare

Automated database deployments are a time-saver, and deploying both to and from version control is even better. And doing it from the command line? What's not to like.

· Database Zone
Free Resource

Find out how Database DevOps helps your team deliver value quicker while keeping your data safe and your organization compliant. Align DevOps for your applications with DevOps for your SQL Server databases to discover the advantages of true Database DevOps, brought to you in partnership with Redgate

Our goal is to show how to use the SQL Compare Pro command line with PowerShell to automate the following processes:

  • Getting a new database into version control, or updating the object scripts in the VCS to reflect the latest database changes
  • Creating a new build script for a database version, from the object scripts in the VCS
  • Creating a database migration script to upgrade a target database to the version represented by the source object scripts

Getting a New Database Into Version Control

If we have an existing database that is not yet in source control, we can use SQL Compare to compare the source database to a directory of object scripts, which is linked to a Version Control System such as GitHub, SVN, or TFS.

SQL Compare will update the target directory, producing a set of object creation scripts that represent the current state of the database metadata in the source database. If the directory is empty, SQL Compare will write out the script files for every object in the database and create the necessary subdirectories to hold them. Then, it is easy to use a version control tool such as TortoiseSVN, TortoiseGit or GitHub to create a repository.

Feodor Georgiev’s article, How to create a directory of object-level scripts using SQL Compare, shows how to do this using the SQL Compare UI, mainly, but also includes a PowerShell script that automates the database-to-directory deployment using the SQL Compare Pro command line. Please refer to that article for the details on this script; here I use an adapted version of that code to generate the initial directory of scripts for my example database, Customers (the link is to the build script):

$SQLCompare="${env:ProgramFiles(x86)}\Red Gate\SQL Compare 12\sqlcompare.exe"# full path
$MyServerInstance='MyServer\MyInstance' #The SQL Server instance
$MyDatabase='Customers' #The name of the database
$MyDatabasePath =
"$($env:HOMEDRIVE)$($env:HOMEPATH)\Documents\GitHub\$MyDatabase\Source"
$MyReportDirectory = "$($env:HOMEDRIVE)$($env:HOMEPATH)\Documents\GitHub\$MyDatabase\Reports"

if (-not (Test-Path -PathType Container $MyReportDirectory))
    {
        # we create the report directory 
        New-Item -ItemType Directory -Force -Path $MyReportDirectory;
    }

if (-not (Test-Path -PathType Container $MyDatabasePath))
    {
        # we create the script directory (normally you get this from the VCS)
        New-Item -ItemType Directory -Force -Path $MyDatabasePath;
    }

$AllArgs = @("/server1:$MyServerInstance", "/database1:$MyDatabase ",
"/scripts2:$MyDatabasePath", '/q', '/synch',
"/report:$MyReportDirectory\$MyDatabase.html",
"/reportType:Simple","/rad","/force" )
&$SQLCompare $AllArgs
if ($?) {'updated successfully'}
else {if ($LASTEXITCODE=63) {'Database and scripts were identical'}
else {"we had an error! (code $LASTEXITCODE)"}}

Listing 1: Deploying from database-to-script folder

Of course, Listing 1 represents only the basics of what’s possible. You can set a range of other switches and project options during command line deployments.

Using a SQL Compare Project in a Command Line Switch

An alternative approach is to set up a database-to-directory deployment through the SQL Compare GUI, and then save the resulting project and execute it from a batch file whenever you need to push changes into the VCS. This is useful if you want to use the UI to establish complex object filtering rules, and makes use of other advanced project comparisons, and then automate. Command-line use is ideal for regular routine tasks that are usually scheduled.

Developers can also use the script in Listing 1 to ‘back fill’ the ‘state’ of each object in the VCS, so that its definition reflects their changes. For each tested set of changes to their development database, a developer can compare their source database to the script folder in the VCS and generate a database-to-script folder deployment script.

Each deployment will result in the VCS source file of any changed object being updated so that its definition matches that in the source database. Likewise, a file will be added to the VCS for any new object, and the file for any object that is deleted in the database will usually be deleted from the directory (occasionally, I’ve seen an empty, 0KB, file left in the directory).

Figure 1 shows a sample of the report generated after adding a stored procedure to my development database, and increasing the size of the Customer.Address.City column from NVARCHAR(30) to NVARCHAR(40), and then running Listing 1.

SQL Compare PowerShell

Figure 1

Take care when back-filling the database ‘state’, in this manner. If you’re working on a shared database, each developer on the team will need to add a /filter argument, to be sure to capture only his or her changes. Having updated their local working directory with any changes, each developer can push those changes to the master branch in the remote shared repository, so other developers can access them. In bigger teams, it’s potentially easier to coordinate all this through a tool such as SQL Source Control.

Each developer can pull from the project’s GitHub repository the latest object-level scripts, with the committed changes of other developers, use SQL Compare to compare their newly-updated working folder with their development database, and generate a deployment script to update the latter with these changes. They can also use the latest set of scripts to build a new copy of the current database version.

Deploying a New Database From Version Control

The database build mechanism must create a build script from the correct version of the object creation scripts for all necessary components, including the DDL scripts to create all the schema-scoped objects, such as tables, stored procedures, functions, views, aggregates, and synonyms.

SQL Compare can read a directory of scripts, parse them to create a ‘model’ of the database from the scripts, compare that with a target database, and generate a deployment script that will synchronize the two. When comparing to an empty target database, we can instruct SQL Compare to compare to the model database, the template from which SQL Server builds all user databases. The net result is that SQL Compare will generate a complete build script for the database, with all the objects automatically built in the correct dependency order.

Listing 2 demonstrates this approach. Having created a blank Customers database on the target SQL Server instance, running it will generate a database build script in the VCS project directory. If a build script already exists, we simply overwrite it in this simple example:

$SQLCompare="${env:ProgramFiles(x86)}\Red Gate\SQL Compare 12\sqlcompare.exe"# full path
$MyServerInstance='MyServer\MyInstance' #The SQL Server instance
$MyDatabase='Customers' #The name of the database
$MyDatabasePath =
"$($env:HOMEDRIVE)$($env:HOMEPATH)\Documents\GitHub\$MyDatabase\Source"
$MyBuildFile = "$($env:HOMEDRIVE)$($env:HOMEPATH)\Documents\GitHub\$MyDatabase\Build"

if (-not (Test-Path -PathType Container $MyBuildFile))
    {
        # we create the build directory
        New-Item -ItemType Directory -Force -Path $MyBuildFile;
    }

$AllArgs = @("/scripts1:$MyDatabasePath", "/server2:$MyServerInstance",
      '/quiet', "/database2:model","/scriptfile:$MyBuildFile\$MyDatabase.sql")
if (Test-Path "$MyBuildFile\$MyDatabase.sql" ) {Remove-item "$MyBuildFile\$MyDatabase.sql" }
&$SQLCompare $AllArgs
if ($?) {'Script generated successfully'}
   else {"we had an error! (code $LASTEXITCODE)"}

Listing 2: Creating a database build script

An alternative to comparing to the model database on the target instance is simply to use the /empty2 command line switch instead, if that’s more convenient. The risk here is that the resulting database may have database option settings different to those defined in the model database for that instance.

Having verified and tested the build script, simply set the target database to Customers and run the script, using SSMS or SQLCMD, to deploy a new build of that database version. The team can use this same script to reproduce that exact same build on any target server.

Updating an Existing Database From Version Control

The script for updating an existing database with the latest changes in the VCS, shown in Listing 3, is suspiciously similar, except this time we are not comparing with model but with the target database.

Again, it will generate a migration script in the project directory for that database. You’ll need to ensure that the migration script is named in a way that indicates the source and target version of the database on which it operates. You now have a script that takes a database from one version to another, reliably, on any environment:

$SQLCompare="${env:ProgramFiles(x86)}\Red Gate\SQL Compare 12\sqlcompare.exe"# full path
$MyServerInstance='MyServer\MyInstance ' #The SQL Server instance
$MyDatabase='Customers' #The name of the database
$MyDatabasePath =
"$($env:HOMEDRIVE)$($env:HOMEPATH)\Documents\GitHub\$MyDatabase\Source"
$MyMigrationFile = "$($env:HOMEDRIVE)$($env:HOMEPATH)\Documents\GitHub\$MyDatabase\Migrations"

if (-not (Test-Path -PathType Container $MyMigrationFile))
    {
        # we create the migrations directory 
        New-Item -ItemType Directory -Force -Path $MyMigrationFile;
    }

$AllArgs = @("/scripts1:$MyDatabasePath", "/server2:$MyServerInstance",
      '/quiet', "/database2:$MyDatabase","/scriptfile:$MyMigrationFile\$MyDatabase.sql")
if (Test-Path "$MyMigrationFile\$MyDatabase.sql" ) {Remove-item "$MyMigrationFile\$MyDatabase.sql" }
&$SQLCompare $AllArgs
if ($?) {'Script generated successfully'}
   else {"we had an error! (code $LASTEXITCODE)"}

Listing 3: Creating a database migration script

Ultimately, you’ll be deploying this sort of script to a target database that contains data, so it will need careful testing to ensure that the script preserves all data correctly in the target database. Occasionally, there will be messy problems that SQL Compare can’t tackle automatically, such as when you split a table or rename a column. In such cases, you’ll need to hand craft those migration scripts, or use a migrations tool such as ReadyRoll to help.

In this simple example, we again just delete any existing migration script and replace it with the current one. In practice, you’ll have a unique naming convention for each script, based for example on the source and target database version, or on the date.

Summary

Here we’ve simply sketched out, using working examples, the ways that SQL Compare can help with aspects of deployment. The SQL Toolbelt allows you to do much more. I’ve already shown you in this series of articles how to automatically fill the build database with data for testing using SQL Data Generator, and how to document a new build automatically via SQL Doc. It’s also very easy to provision all of your test cells with a database stocked with data via SQL Data Compare. Once you understand the principles you can then go on to add plenty more, such as code analysis, to the toolchain.

Align DevOps for your applications with DevOps for your SQL Server databases to increase speed of delivery and keep data safe. Discover true Database DevOps, brought to you in partnership with Redgate

Topics:
database ,tutorial ,database deployment ,version control ,sql compare

Published at DZone with permission of Phil Factor. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}