Red Gate forums :: View topic - How to package and deploy replication scripts
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
Deployment Manager
Deployment Manager forum

How to package and deploy replication scripts

Search in Deployment Manager forum
Post new topic   Reply to topic
Jump to:  
Author Message
isme



Joined: 12 Jun 2012
Posts: 74
Location: Edinburgh

PostPosted: Tue Dec 03, 2013 8:22 pm    Post subject: How to package and deploy replication scripts Reply with quote

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:

$ 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.

Code:

$ 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.

Code:

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:

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:

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
Back to top
View user's profile Send private message
csmith



Joined: 30 Jun 2011
Posts: 114
Location: Cambridge

PostPosted: Fri Dec 06, 2013 5:46 pm    Post subject: Reply with quote

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
_________________
Chris Smith
Project Manager
Deployment Manager
Back to top
View user's profile Send private message
isme



Joined: 12 Jun 2012
Posts: 74
Location: Edinburgh

PostPosted: Fri Jan 17, 2014 7:34 pm    Post subject: Reply with quote

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.

Cheers,
Iain
_________________
Iain Elder, Skyscanner
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group