Problem

Developers and DBAs often need to retrieve scripts for existing SQL Server replication configuration. One common way of performing such tasks is using SQL Server Management Studio. This approach requires many mouse clicks and can take a significant amount of time.

A Better Solution

RMO (Replication Management Objects) and PowerShell can be combined to create a powerful tool  capable of mapping and scripting SQL Server replication. Below I present a brief introduction with several code snippets on how to retrieve replication information using PowerShell and RMO.

Pre-requisites

• SQL Server Client Tools SDK feature installed (option available during SQL Server installation)
• PowerShell 4+

What is RMO?

RMO or Replication Management Objects is a Microsoft Library that allows developers to build applications to interact and administer SQL Server replication. RMO Concepts is a great introduction article to get you started with the RMO library.

Loading the RMO Assembly

The first step to use the RMO in PowerShell is loading the assembly. PowerShell is very flexible when it comes to loading assemblies. My favorite approach is:

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Rmo') | Out-Null

Alternatively, a more explicit approach can be used:

Add-Type -AssemblyName "Microsoft.SqlServer.Rmo, Version=15.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop

Note that it is required to specify the version of SQL Server. This is why using the [System.Reflection.Assembly] is my preferred approach.

Opening an RMO connection to SQL Server

Once we load the RMO assembly, we can open a connection to the SQL Server replication server(s).

Take a minute to review the result of the snippet below.

$SqlInstance = $env:COMPUTERNAME
$ReplicationServer = New-Object "Microsoft.SqlServer.Replication.ReplicationServer" $SqlInstance
$ReplicationServer

In the snippet above, I am setting the variable of $SqlInstance to the name of the local server. This means the SQL Server instance will be the default (MSSQLSERVER) instance on the local server.

I then create the $ReplicationServer object by instantiating the ReplicationServer class in the  Microsoft.SqlServer.Replication library using our SqlInstance.

Navigating through RMO objects

The ReplicationServer library can be instantiated with any server partaking in replication. Those can be the distributor, publisher, and/or subscriber. For many tasks, I prefer to create an RMO object based on the distributor server because it has visibility to publisher and subscriber instance replication properties.

Note that in many configurations, one SQL Server instance can take on multiple replication roles such as both distributor and publisher, distributor and subscriber, and in very few cases distributor, publisher, and subscriber.

Once we instantiate the ReplicationServer class, one quick way to check if the SQL Server instance is a distributor is by checking the property IsDistributor

$DistributorInstance = $env:COMPUTERNAME
$ReplicationServer = New-Object "Microsoft.SqlServer.Replication.ReplicationServer" $DistributorInstance 
$ReplicationServer.IsDistributor

From the distributor, we can also check what SQL Server instances are publishers by checking the property DistributionPublishers

$ReplicationServer.DistributionPublishers

Furthermore, publications can be listed by exploring the object one additional level

$ReplicationServer.DistributionPublishers.DistributionPublications

Subscriptions can also be further expanded from the DistributionPublications property

$ReplicationServer.DistributionPublishers.DistributionPublications.DistributionSubscriptions

Retrieving list of publisher databases

Publisher databases can be listed from the distributor SQL Server instance as stated above, or from the publisher SQL Server instance. Here is a snippet on how we can list these databases from the publisher server:

$SqlInstance = $env:COMPUTERNAME
$PublisherServer = New-Object "Microsoft.SqlServer.Replication.ReplicationServer" $SqlInstance
$PublisherServer.ReplicationDatabases | Where-Object {$_.HasPublications -eq $true}

Transaction publications can be retrieved using:

$PublisherServer.ReplicationDatabases.TransPublications

The same approach applies to merge publications:

$PublisherServer.ReplicationDatabases.MergePublications

Scripting the Replication Objects

The RMO library contains a Script method (or function) across several of its classes that allows users to easily scripts objects. I will show examples of this method below. These examples will be geared towards transactional replication but the process would be identical for other replication types.

The Script method typically takes as an input a set of scripting options. These options allow you defined in detail what you want to include or exclude during the scripting process and can be built using the enumerator Microsoft.SqlServer.Replication.ScriptOptions

Let's assume you want to script replication push subscriptions. You could specify the scripting options as such:

$ScriptOptions = [Microsoft.SqlServer.Replication.ScriptOptions]::Creation `
                 -bor [Microsoft.SqlServer.Replication.ScriptOptions]::IncludeReplicationJobs `
                 -bor [Microsoft.SqlServer.Replication.ScriptOptions]::IncludePublisherSideSubscriptions

These options allow to define in a greater detail that we want to script a CREATE operation (instead of a drop operation), we want to include the replication jobs and we want the publisher side subscriptions (push subscriptions).

The drop option would look like this:

$ScriptOptions = [Microsoft.SqlServer.Replication.ScriptOptions]::Deletion

To get a better idea of what can be included or exclude, visit the ScriptOptions link or run the following command:

[Microsoft.SqlServer.Replication.ScriptOptions].GetEnumValues()

Scripting the Distributor

Using the snippet below we can quickly script out the distributor, distribution database, agent profiles and all jobs hosted in the distributor SQL Instance:

$SqlInstance = $env:COMPUTERNAME
$DistributorServer = New-Object "Microsoft.SqlServer.Replication.ReplicationServer" $SqlInstance
$ScriptOptions = [Microsoft.SqlServer.Replication.ScriptOptions]::Creation `
        -bor [Microsoft.SqlServer.Replication.ScriptOptions]::IncludeAll `
        -bor [Microsoft.SqlServer.Replication.ScriptOptions]::IncludeGo
$DistributorServer.Script($ScriptOptions)

Note that the jobs in the output for the script above include ALL replication jobs in this given instance. This means that log reader agent jobs, snapshot agent jobs, and possibly distribution agent jobs will be present in the script. If the whole script is not of interest, individual parts can also be scripted.

The statement to add the distributor to a given SQL Server instance can be generated via:

$ScriptOptions = [Microsoft.SqlServer.Replication.ScriptOptions]::Creation `
        -bor [Microsoft.SqlServer.Replication.ScriptOptions]::IncludeGo
$DistributorServer.ScriptInstallDistributor($SqlInstance,$ScriptOptions)

The distribution database can also be independently scripted by running:

$ScriptOptions = [Microsoft.SqlServer.Replication.ScriptOptions]::Creation `
        -bor [Microsoft.SqlServer.Replication.ScriptOptions]::IncludeGo
$DistributorServer.Script($ScriptOptions)

Scripting Publications

Publications can easily be scripted by running this code:

$ScriptOptions = [Microsoft.SqlServer.Replication.ScriptOptions]::Creation `
        -bor [Microsoft.SqlServer.Replication.ScriptOptions]::IncludeGo
$PublisherServer.ReplicationDatabases.TransPublications.Script($ScriptOptions)

Publications for a single database can be scripted via:

$PublisherServer.ReplicationDatabases['MyDatabaseName'].TransPublications.Script($ScriptOptions)

A single publication can also be scripted in the same way:

$PublisherServer.ReplicationDatabases['MyDatabaseName'].TransPublications['MyPublicationName'].Script($ScriptOptions)

Or

($PublisherServer.ReplicationDatabases.TransPublications | Where-Object {$_.Name -eq 'MyPublicationName'}).Script($ScriptOptions)

Scripting Articles

Articles can be retrieved by exploring the object tree under transpublications property.

$ScriptOptions = [Microsoft.SqlServer.Replication.ScriptOptions]::Creation `
        -bor [Microsoft.SqlServer.Replication.ScriptOptions]::IncludeGo
$PublisherServer.ReplicationDatabases['MyDatabase'].TransPublications['MyPublication'].TransArticles.Script($ScriptOptions)

Scripting Subscriptions

Push subscriptions, the ones where the distribution agent job is hosted on the Distributor SQL Server instance, can be retrieved on the publisher (or distributor side) with this code:

$ScriptOptions = [Microsoft.SqlServer.Replication.ScriptOptions]::Creation `
        -bor [Microsoft.SqlServer.Replication.ScriptOptions]::IncludeGo
$PublisherServer.ReplicationDatabases.TransPublications.TransSubscriptions.Script($ScriptOptions)

Pull subscriptions, the ones whose distribution agent job is hosted in the subscriber SQL Server instance, must be retrieved from the subscriber SQL Server instance. This code helps with that:

$ScriptOptions = [Microsoft.SqlServer.Replication.ScriptOptions]::Creation `
            -bor [Microsoft.SqlServer.Replication.ScriptOptions]::IncludeSubscriberSideSubscriptions `
            -bor [Microsoft.SqlServer.Replication.ScriptOptions]::IncludeGo
$SubscriberServer.ReplicationDatabases.TransPullSubscriptions.Script($ScriptOptions)

Note: the output above includes the script to execute on both the publisher and subscriber sides of replication. You may control the output using the ScriptOptions set.

Pull subscriptions are perhaps the only reason I ever connect to the Subscriber server to perform any task with RMO.

Conclusion

This article provided a brief introduction to the usage of Replication Management Objects (RMO) with PowerShell to build powerful scripts capable of identifying and scripting different types of SQL Server replication configurations.

The examples above were simple yet powerful. With few lines of PowerShell code, any developer or DBA is capable of performing SQL Server replication tasks in a fast and consistent way.