Options
How to package and deploy replication scripts
isme
Posts: 119
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)
2. Rename all the *.sql replication scripts to *.sqlrepl.
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.
The PreDeploy.ps1 script looks like this:
The PostDeploy.ps1 script looks like this:
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!
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)
$ Get-ChildItem | Select Name Name ---- Assemblies Data Database Triggers Defaults Extended Properties Functions Replication Rules Search Property Lists Security Sequences Service Broker Storage Stored Procedures Synonyms Tables Types Views RedGate.ssc RedGateDatabaseInfo.xml
2. Rename all the *.sql replication scripts to *.sqlrepl.
$ Get-ChildItem -Path Replication | Select Name Name ---- CreatePublication.sqlrepl DropPublication.sqlrepl PostDeploy.ps1 PostSnapshot.sqlrepl PreDeploy.ps1
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.
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:
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:
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
Comments
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
Redgate Software
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.
Cheers,
Iain