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)
