Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

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?

share|improve this question
add comment

1 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").
share|improve this answer
    
Do the supporting objects exist in the publisher database, or the subscriber database? How does the subscriber know where to find the publisher? It looks like your solution is driven by the publisher. I'm looking for a process driven by the subscriber, so that I can set up subscribing instances on-demand. –  Iain Elder Oct 2 '12 at 16:04
    
All of the objects live at the publisher, but if you're setting up push subscriptions, all of the commands are run there anyways. But even if they were pull subscriptions, there'd have to be some commands run at the publisher. And since subscribers can be ephemeral but the publisher is (in theory) permanent, it makes sense (to me) to store all of the relevant info at the publisher. –  Ben Thul Oct 2 '12 at 17:29
    
Everything you say about about storing objects and running commands at the publisher makes sense. In any implementation, I want host of the subscriber to control the process that sets up the replication. If that means making a remote connection to call replicataion stored procedures at te publisher, so be it. The sequence of steps to set up the complete instance with a subscription could be long and varied (install SQL Server, configure global server settings, create user database schemas, associate logins with Windows accounts, insert static data, set up publications and subscrptions). –  Iain Elder Oct 2 '12 at 22:23
    
One question that I feel I need to ask: why are you letting just anyone subscribe to your publication? Take a look at what permissions are required to run the various replication stored procedures. You know your environment best, but it seems like you're asking for trouble at some point. –  Ben Thul Oct 2 '12 at 22:39
    
Only administatrators will be allowed to subscribe to the publication as part of deploying all the components for the instance. We are experimenting with spinning up display-layer instances on-demand, so we want a one-step process. Currently, it takes the best part of a day to deploy all the components for one instance. –  Iain Elder Oct 3 '12 at 0:28
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.