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)
