How to script out push subscription creation at the subscriber?

Question

I'm trying to set up a push subscription to a SQL Server publication from the subscriber.

I could set up the subscription at the publisher using the Replication Wizard in Management Studio. However, I would prefer to script the process relative to the subscriber so I can automate the deployment of a new SQL Server subscriber instance.

Initially, I'm happy to prompt for the name of the publisher before deployment. If I can get this working, I will look for a way to inject the correct value for my environment automatically.

What is a simple way to do this for a SQL Server instance that has to create multiple subscriptions at different publishers?

I'm open to using any supported SQL Server scripting solution: SMO, RMO, Sqlcmd, WMI, PSDrive, even pure T-SQL.

I've attempted to solve this problem in two ways. The first is a complete solution using T-SQL, but it involves some manual steps.

Using T-SQL

I have a manual solution in T-SQL. The solution is based on the output of the Management Studio Replication Script Generator output.

Using Management Studio, I run the following script to generate a T-SQL script that I can run at the publisher:

PRINT N'
EXECUTE MyDatabase.dbo.sp_addsubscription
  @publication = N''MyPublication'',
  @subscriber = ''' + CAST(SERVERPROPERTY('ServerName') AS SYSNAME) + ''',
  @destination_db = ''SubscriberDatabase'',
  @subscription_type = N''Push'',
  @sync_type = N''automatic'',
  @article = N''all'',
  @update_mode = N''read only'',
  @subscriber_type = 0;

EXECUTE MyDatabase.dbo.sp_addpushsubscription_agent
  @publication = N''MyPublication'',
  @subscriber = ''' + CAST(SERVERPROPERTY('ServerName') AS SYSNAME) + ''',
  @subscriber_db = ''SubscriberDatabase'',
  @job_login = null,
  @job_password = null,
  @subscriber_security_mode = 1,
  @frequency_type = 64,
  @frequency_interval = 1,
  @frequency_relative_interval = 1,
  @frequency_recurrence_factor = 0,
  @frequency_subday = 4,
  @frequency_subday_interval = 5,
  @active_start_time_of_day = 0,
  @active_end_time_of_day = 235959,
  @active_start_date = 0,
  @active_end_date = 0,
  @dts_package_location = N''Distributor'';';

On the MYSUBSCRIBER instance, the output would look like this:

EXECUTE MyDatabase.dbo.sp_addsubscription
  @publication = N'MyPublication',
  @subscriber = 'MYSUBSCRIBER',
  @destination_db = 'SubscriberDatabase',
  @subscription_type = N'Push',
  @sync_type = N'automatic',
  @article = N'all',
  @update_mode = N'read only',
  @subscriber_type = 0;

EXECUTE MyDatabase.dbo.sp_addpushsubscription_agent
  @publication = N'MyPublication',
  @subscriber = 'MYSUBSCRIBER',
  @subscriber_db = 'SubscriberDatabase',
  @job_login = null,
  @job_password = null,
  @subscriber_security_mode = 1,
  @frequency_type = 64,
  @frequency_interval = 1,
  @frequency_relative_interval = 1,
  @frequency_recurrence_factor = 0,
  @frequency_subday = 4,
  @frequency_subday_interval = 5,
  @active_start_time_of_day = 0,
  @active_end_time_of_day = 235959,
  @active_start_date = 0,
  @active_end_date = 0,
  @dts_package_location = N'Distributor';

I copy the output and execute the script at the publisher instance to set up the subscription.

I think I can't automate this in pure T-SQL without editing the script before running it, because T-SQL by design does not handle user input.

Using PowerShell and RMO

PowerShell has simple ways to process user input, so this seems like a good way to prototype the automation process.

MSDN has an eight-step guide to set up a push subscription using the .NET Replication Management Objects (RMO).

Here are the first two steps:

1. Create a connection to the Publisher by using the ServerConnection class.
2. Create an instance of the TransPublication class by using the Publisher connection from step 1. Specify Name, DatabaseName, and ConnectionContext.

I'm trying to translate these steps into a PowerShell script, but I can't get past step 2.

In the following code examples, I use fictional object names. I believe this does not affect the answerability of the question because the error message is identical when I use the real object names.

First attempt: setting the properties

My first attempt is to create the TransReplication object then set its properties. The code looks like this:

Add-Type -AssemblyName "Microsoft.SqlServer.Rmo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";

$Publisher = New-Object Microsoft.SqlServer.Management.Common.ServerConnection MyServer

$Publication = New-Object Microsoft.SqlServer.Replication.TransPublication
$Publication.Name = 'MyPublication'
$Publication.DatabaseName = 'MyDatabase'
$Publication.ConnectionContext = $Publisher

When I execute this script, I see the following error:

Exception setting "ConnectionContext": "Cannot convert the "server='(local)';Trusted_Connection=true;multipleactiveresultsets=false" value
 of type "Microsoft.SqlServer.Management.Common.ServerConnection" to type "Microsoft.SqlServer.Management.Common.ServerConnection"."
At line:8 char:14
+ $Publication. <<<< ConnectionContext = $Publisher
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : PropertyAssignmentException

It looks like it's failing becuase it can't convert the type ServerConnection to the type ServerConnection. I don't understand how this could fail for the stated reason, because the value is already of the required type.

Second attempt: overloading the constructor

My second attempt is to specify the property values of the TransReplication object in the constructor. The code looks like this:

Add-Type -AssemblyName "Microsoft.SqlServer.Rmo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";

$Publisher = New-Object Microsoft.SqlServer.Management.Common.ServerConnection MyServer

$Publication = New-Object Microsoft.SqlServer.Replication.TransPublication 'MyPublication', 'MyDatabase', $Publisher

When I execute this script, I see the following error:

New-Object : Cannot find an overload for "TransPublication" and the argument count: "3".
At line:5 char:26
+ $Publication = New-Object <<<<  -TypeName Microsoft.SqlServer.Replication.TransPublication 'MyPublication', 'MyDatabase', $Publisher
    + CategoryInfo          : InvalidOperation: (:) [New-Object], MethodException
    + FullyQualifiedErrorId : ConstructorInvokedThrowException,Microsoft.PowerShell.Commands.NewObjectCommand

It looks like the New-Object cmdlet can't find the three-argument constructor documented by MSDN:

public TransPublication(
  string name,
  string databaseName,
  ServerConnection connectionContext
)

Parameters

• name
  Type: System.String
  A String value that specifies the name of publication.
• databaseName
  Type: System.String
  A String value that specifies the name of the publication database.
• connectionContext
  Type: Microsoft.SqlServer.Management.Common.ServerConnection
  A ServerConnection object that represents a connection to the Publisher or to the Distributor for a non-SQL Server Publisher.

As far as I can tell, I'm overloading the constructor correctly.

Am I doing something wrong? Is there something unusual about my environment? Am I better off using another solution?

Answer 1

I'm going to outline a T-SQL way that uses most of what you already have.

1. Create a table to hold your publication information (for the solution that I have written, it's just the name of the publication and an identity column)
2. Create a table to hold your subscriber information (in mine, I have name of subscriber, subscriber database, identity column, and a column that references back to the publication)
3. When you want to create a new subscription, you put a new row in the subscriber table with the relevant information
4. In your "add subscription" script, do a left join between your table and syssubscriptions to find out what subscriptions are in your table but don't yet exist. Run a cursor over that info and for each subscription that doesn't exist, create it.
5. After you're done adding all of the subscriptions, call sp_startpublication_snapshot for each publication that has uninitialized subscribers.
6. Fin (aka "you're done").