How to package and deploy replication scripts

Automated deployment for web applications and databases

How to package and deploy replication scripts

Postby isme » Tue Dec 03, 2013 8:22 pm

Hey RG,

I'd like to share a little trick we discovered for deploying replication scripts within a database package without extra build steps or packages.

Our environment has front-end and back-end servers, so we need pre-deploy and post-deploy steps to tear down and recreate publications and subscriptions.

We implemented it in the conventional way. Two PowerShell scripts, PreDeploy.ps1 and PostDeploy.ps1, invoke the replication control scripts using sqlcmd.exe. See the PowerShell scripts forum for examples.

We tried to figure out how to package these with nuspec and RgPublish, but neither easily met our needs. Both solutions introduced inter-package dependencies that we struggled to understand.

After a little bit of experimentation, we found a simpler, albeit hackier, solution.

The trick is to store the replication scripts in the database schema folder and give them special names.

1. Move the 'Replication' scripts folder inside the the schema folder (the one that contains RedGateDatabaseInfo.xml)

Code: Select all
$ Get-ChildItem | Select Name

Database Triggers                                                                           
Extended Properties                                                                         
Search Property Lists                                                                       
Service Broker                                                                               
Stored Procedures                                                                           

2. Rename all the *.sql replication scripts to *.sqlrepl.

Code: Select all
$ Get-ChildItem -Path Replication | Select Name


SQL Compare parses every *.sql file in the schema folder. It raises an error when it finds a non-schema statement like EXECUTE sys.sp_addpublication.

SQL Compare ignores *.sqlrepl files. Renaming the non-schema files lets us smuggle arbitrary scripts into the package.

TeamCity packages everything it finds in the schema folder.


We create a release from the package in the usual way.

When we deploy the package, RGDM runs the PreDeploy.ps1 and PostDeploy.ps1 scripts automatically.

Code: Select all
2013-12-03 17:29:51 +00:00 INFO   Executing powershell script G:\\Temp\\rkidbrzr.n3a\\Packages\\..\\Applications\\ApiServices DEV\\ApiServices-backend-database\\0.133\\db\\state\\repl\\PreDeploy.ps1...
2013-12-03 17:29:51 +00:00 INFO   Dropping publication.
2013-12-03 17:30:42 +00:00 INFO   Executing powershell script G:\\Temp\\rkidbrzr.n3a\\Packages\\..\\Applications\\ApiServices DEV\\ApiServices-backend-database\\0.133\\db\\state\\repl\\PostDeploy.ps1...
2013-12-03 17:30:42 +00:00 INFO   Creating publication.

The PreDeploy.ps1 script looks like this:

Code: Select all
Write-Host 'Dropping publication.'

$ScriptRoot = Split-Path -Parent -Path $MyInvocation.MyCommand.Definition

Copy-Item -Path \"$ScriptRoot\\PostSnapshot.sqlrepl\" -Destination \"\\\\$RedGateDatabaseServer\\ReplicationData\\ApiServices\" -Force

sqlcmd.exe -E -b -V 1 -S $RedGateDatabaseServer -d $RedGateDatabaseName -i $ScriptRoot\\DropPublication.sqlrepl | Out-Default

The PostDeploy.ps1 script looks like this:

Code: Select all
Write-Host 'Creating publication.'

$ScriptRoot = Split-Path -Parent -Path $MyInvocation.MyCommand.Definition

sqlcmd.exe -E -b -V 1 -S $RedGateDatabaseServer -d $RedGateDatabaseName -i $ScriptRoot\\CreatePublication.sqlrepl | Out-Default

This hack is testament to the flexibility and power of RGDM. But does it make you scream \"YOU'RE DOING IT WRONG!\"?

How would you implement this, RG?

Thanks for your help!
Iain Elder, Skyscanner
Posts: 83
Joined: Tue Jun 12, 2012 1:49 pm
Location: Edinburgh

Postby csmith » Fri Dec 06, 2013 5:46 pm

Hi Iain

We really like how you've made this work and thanks for sharing the powershell here.

At the moment, this seems like a great way to make DM do what you need. You've highlighted something that we're considering adding proper support for - pre and post deployment sql scripts.

We'd obviously need to add a better way to specify and package these scripts - probably through rgpublish. We would also need to allow users to configure the sqlcmd switches they'd like the scripts run with.

Do you think something like that would meet your requirements with replication control scripts?

Best regards,
Chris Smith
Project Manager
SQL Lighthouse
Posts: 120
Joined: Thu Jun 30, 2011 2:15 pm
Location: Cambridge

Postby isme » Fri Jan 17, 2014 7:34 pm

Hi Chris,

Sorry for the late reply. We got really busy at the end of December.

Having a well-known place to store non-schema scripts would be great.

The hack works for now, but of course the behavior of SQL Compare could change in the future. We'd prefer some official level of support for this situation.

We've extended this workaround with a Jobs folder for SQL Agent jobs and a Logins folder for server-level logins.

Now any script that should be hidden from SQL Compare has a *._sql extension (following Python's naming convention for private fields). It's simpler than *.sqlrepl for replication, *.sqljob for jobs, and so on.

A way to set the sqlcmd switches at a step level or project level would help us stay consistent.

The -U (user) and -P (password) switches could take values from the target machine setup if using SQL authentication.

We use Invoke-Sqlcmd as well when our SQL scripts produce output. It's much easier to query PowerShell objects than parse text.

Iain Elder, Skyscanner
Posts: 83
Joined: Tue Jun 12, 2012 1:49 pm
Location: Edinburgh

Return to Deployment Manager

Who is online

Users browsing this forum: No registered users and 0 guests