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)
