In my previous article, “Five Tips to Get Your Organisation Releasing Software Frequently” I looked at how teams can automate processes to speed up release frequency. In this post, I’m looking specifically at automating deployments using the SQL Compare command line.

SQL Compare compares SQL Server schemas and deploys the differences. It works very effectively in scenarios where only one deployment target is required – source and target databases are specified, compared, and a change script is automatically generated and applied.

But if multiple targets exist, and pressure to increase the frequency of releases builds, this solution quickly becomes unwieldy.

This is where SQL Compare’s command line comes into its own. I’ve put together a PowerShell script that loops through the Servers table and pulls out the server and database, these are then passed to sqlcompare.exe to be used as target parameters. In the example the source database is a scripts folder, a folder structure of scripted-out database objects used by both SQL Source Control and SQL Compare. The script can easily be adapted to use schema snapshots.

-- Create a DeploymentTargets database and a Servers table
CREATE DATABASE DeploymentTargets
GO
USE DeploymentTargets
GO
CREATE TABLE [dbo].[Servers](
       [id] [int] IDENTITY(1,1) NOT NULL,
       [serverName] [nvarchar](50) NULL,
       [environment] [nvarchar](50) NULL,
       [databaseName] [nvarchar](50) NULL,
CONSTRAINT [PK_Servers] PRIMARY KEY CLUSTERED ([id] ASC)
)
GO
-- Now insert your target server and database details
INSERT INTO dbo.Servers ( serverName , environment , databaseName)
VALUES  ( N'myserverinstance' , N'myenvironment1' , N'mydb1')
INSERT INTO dbo.Servers ( serverName , environment , databaseName)
VALUES  ( N'myserverinstance' , N'myenvironment2' , N'mydb2')

Here’s the PowerShell script you can adapt for yourself as well.

# We're holding the server names and database names that we want to deploy to in a database table.  
# We need to connect to that server to read these details
$serverName = ""
$databaseName = "DeploymentTargets"
$authentication = "Integrated Security=SSPI"
#$authentication = "User Id=xxx;PWD=xxx"  # If you are using database authentication instead of Windows authentication.

# Path to the scripts folder we want to deploy to the databases
$scriptsPath = "SimpleTalk"

# Path to SQLCompare.exe
$SQLComparePath = "C:\Program Files (x86)\Red Gate\SQL Compare 10\sqlcompare.exe"

# Create SQL connection string, and connection
$ServerConnectionString = "Data Source=$serverName;Initial Catalog=$databaseName;$authentication"
$ServerConnection = new-object system.data.SqlClient.SqlConnection($ServerConnectionString);

# Create a Dataset to hold the DataTable 
$dataSet = new-object "System.Data.DataSet" "ServerList"

# Create a query
$query =  "SET NOCOUNT ON;"
$query += "SELECT serverName, environment, databaseName "
$query += "FROM   dbo.Servers; "

# Create a DataAdapter to populate the DataSet with the results
$dataAdapter = new-object "System.Data.SqlClient.SqlDataAdapter" ($query, $ServerConnection)
$dataAdapter.Fill($dataSet) | Out-Null

# Close the connection
$ServerConnection.Close()

# Populate the DataTable
$dataTable = new-object "System.Data.DataTable" "Servers"
$dataTable = $dataSet.Tables[0]

#For every row in the DataTable
$dataTable | FOREACH-OBJECT {
  "Server Name:  $($_.serverName)"
  "Database Name:  $($_.databaseName)"
  "Environment:  $($_.environment)"

  # Compare the scripts folder to the database and synchronize the database to match
  # NB. Have set SQL Compare to abort on medium level warnings.  
  $arguments = @("/scripts1:$($scriptsPath)", "/server2:$($_.serverName)", "/database2:$($_.databaseName)", "/AbortOnWarnings:Medium") # + @("/sync" ) # Commented out the 'sync' parameter for safety, 

  write-host $arguments
  & $SQLComparePath $arguments
  "Exit Code: $LASTEXITCODE"

  # Some interesting variations

  # Check that every database matches a folder.  
  # For example this might be a pre-deployment step to validate everything is at the same baseline state.  
  # Or a post deployment script to validate the deployment worked.
  # An exit code of 0 means the databases are identical.
  #
  # $arguments = @("/scripts1:$($scriptsPath)", "/server2:$($_.serverName)", "/database2:$($_.databaseName)", "/Assertidentical")

  # Generate a report of the difference between the folder and each database.  Generate a SQL update script for each database.
  # For example use this after the above to generate upgrade scripts for each database
  # Examine the warnings and the HTML diff report to understand how the script will change objects
  #
  #$arguments = @("/scripts1:$($scriptsPath)", "/server2:$($_.serverName)", "/database2:$($_.databaseName)", "/ScriptFile:update_$($_.environment+"_"+$_.databaseName).sql", "/report:update_$($_.environment+"_"+$_.databaseName).html" , "/reportType:Interactive", "/showWarnings", "/include:Identical")
}

It’s worth noting that the above example generates the deployment scripts dynamically. This approach should be problem-free for the vast majority of changes, but it is still good practice to review and test a pre-generated deployment script prior to deployment. An alternative approach would be to pre-generate a single deployment script using SQL Compare, and run this en masse to multiple targets programmatically using sqlcmd, or using a tool like SQL Multi Script.  You can use the /ScriptFile, /report, and /showWarnings flags to generate change scripts, difference reports and any warnings.  See the commented out example in the PowerShell:

#$arguments = @("/scripts1:$($scriptsPath)", "/server2:$($_.serverName)", "/database2:$($_.databaseName)", "/ScriptFile:update_$($_.environment+"_"+$_.databaseName).sql", "/report:update_$($_.environment+"_"+$_.databaseName).html" , "/reportType:Interactive", "/showWarnings", "/include:Identical")

There is a drawback of running a pre-generated deployment script; it assumes that a given database target hasn’t drifted from its expected state. Often there are (rightly or wrongly) many individuals within an organization who have permissions to alter the production database, and changes can therefore be made outside of the prescribed development processes. The consequence is that at deployment time, the applied script has been validated against a target that no longer represents reality.

The solution here would be to add a check for drift prior to running the deployment script. This is achieved by using sqlcompare.exe to compare the target against the expected schema snapshot using the /Assertidentical flag. Should this return any differences (sqlcompare.exe Exit Code 79), a drift report is outputted instead of executing the deployment script.  See the commented out example.

# $arguments = @("/scripts1:$($scriptsPath)", "/server2:$($_.serverName)", "/database2:$($_.databaseName)", "/Assertidentical")

Any checks and processes that should be undertaken prior to a manual deployment, should also be happen during an automated deployment. You might think about triggering backups prior to deployment – even better, automate the verification of the backup too.

You can use SQL Compare’s command line interface along with PowerShell to automate multiple actions and checks that you need in your deployment process.

Automation is a practical solution where multiple targets and a higher release cadence come into play. As we know, with great power comes great responsibility – responsibility to ensure that the necessary checks are made so deployments remain trouble-free.

 (The code sample supplied in this post automates the simple dynamic deployment case – if you are considering more advanced automation, e.g. the drift checks, script generation, deploying to large numbers of targets and backup/verification, please email me at [email protected] for further script samples or if you have further questions)