04 April 2011 20:46 antxxxx

Scripting replication with powershell

One of the great things with powershell is the ability to easily script out sql server objects. With a bit of work you can also use it to script out replication objects, such as publications, articles and subscribers. This can be used as part of a disaster recovery plan so that you always have a script available to recreate replication. You can do all of this from management studio, but when you have lots of publications, this can take a long time

This article explains how to script transactional push scriptions. It is very easy to change the objects to script out other types of replication as well (eg merge, snapshot, pull)

First you need to start off with either by starting sqlps, or by loading the appropriate sql server assemblies. How to do that is detailed on Martin Bells blog at http://sqlblogcasts.com/blogs/martinbell/archive/2009/09/06/Using-SQL-Server-in-Powershell-outside-of-SQLPS.aspx

 

The next thing you need to do is load the RMO assembly. This is the assembly that encapsulates replication functionality for SQL Server

 

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Rmo")

 

Now you need to create a new replication server object and connect to it. One thing to note is that even if your server or database is set to a case insensitive collation, most of these objects need the variables to be defined with the same case. So if you have a publication called SalesTables, it wont show up if you use the examples below and set the publication name to be salestables.

 

 

$servername=”NAME OF PUBLISHER SERVER”

$repserver = New-Object "Microsoft.SqlServer.Replication.ReplicationServer"

$srv = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection" $servername

$srv.connect()

$repserver.ConnectionContext = $srv

 

So now we have a $repserver which represents a replication object at a server level. But we are interested in scripting replication, which must be done at a database level, so we create a new object to be the database

 

$databasename=”DATABASE PUBLISHED FOR REPLICATION”

$repdb = $repserver.ReplicationDatabases[$databasename]

  

From this object, we can then list out the transactional publications using

 

$repdb.transpublications

 

If you want to get an individual publication, you can use this

 

$publication_name = “PUBLICATION NAME”

$publication_object=$repdb.transpublications[$publication_name]

 

From this $publication_object we can then see articles, by looking at

 

$publication_object.TransArticles

 

And subscriptions

 

$publication_object.TransSubscriptions

 

So now we have the 3 things that we would want to script for replication, the publication (in $publication_object), a collection of articles (in $publication_object.TransArticles) and a collection of subscriptions (in $publication_object.TransSubscriptions)

We can set various scripting options using the ScriptOptions enumeration. These vary slightly between different versions of sql server, but you can see the values for all versions at http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.replication.scriptoptions.aspx

 

To include which options you want, you just need to create a variable like

 

$script_val = [Microsoft.SqlServer.Replication.ScriptOptions]::Creation -bxor

                        [Microsoft.SqlServer.Replication.ScriptOptions]::IncludeGo

 

So this option will include creation and a go seperator. To include more options, you just need to separate them using a -bxor statement,

 

Finally we can now script our replication objects using a simple

 

$publication_object.Script($script_val)

 

Foreach ($article in $publication_object.TransArticles) {

            $article.Script($script_val)

}

 

Foreach ($subscription in $publication_object.TransSubscriptions) {

            $subscription.Script($script_val)

}

 

One thing to note is that if you have a seperate publisher and distributor, then some of the job properties (such as profiles and schedules) may not be scripted correctly. This is due to problems with RMO and not this technique. You get the same issue if you script replication from management studio.

You now have the basis for creating a script which can be scheduled to run and automatically create a script of all your replication enviroment. You can customise this as much as you like - for instance to make the output re-runnable, or just more human readable.

 

 

 

Comments

No Comments