How to script out push subscription creation at the subscriber?

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_t

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

• 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)

• 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)

• When you want to create a new subscription, you put a new row in the subscriber table with the relevant information

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

• After you're done adding all of the subscriptions, call sp_startpublication_snapshot for each publication that has uninitialized subscribers.

• Fin (aka "you're done").

StackExchange Database Administrators Q#25210, answer score: 2