Script to start subscribers synchronizing

Question

When I want to force an update of the subscribers to my merge replication publication, I can go into the Replication Monitor, right click on the subscription, and choose Start Synchronizing. I'd like to be able to script this (using vba/vbscript or a command line). I'd also like users to be able to run the script (what permissions would be required, if any?).

I'm sure this has been asked and answered many times before, but my Googling attempts came up empty.

Answer 1

On a test system, why not run SQL Server Profiler from your login - and capture the SQL statements as you click on Replication Monitor?

Answer 2

Assuming the publication already exists (and a valid snapshot is available on a valid folder), here are the T-SQL and command line instructions that you need to initiate and maintain a subscription on both publisher and subscriber. We use this code to start a web replication from scratch (no database on the subscriber's side). Once the subscription declared on both sides through stored procedures, you have to run (on a regular basis) the synchronization command-line instruction on the subscriber's side (see below).

As this code is specific to web replication, please check additional references on organizing such a replication on your server, including publication parameters, etc. You'll find all the stuff related to security in the corresponding docs. Of course, synchronization command-line parameters are strictly related to the specific subscription and its parameters. In case your subscription is not done through web (like in this example), you 'll have to adjust publication, subscription and synchronization parameters consequently. Please be aware that many parameters here reflects our specific situation, where for example the publisher is also the subscriber. You might have to adapt depending on your situation.

PS: SQLEXPRESS server cannot be a publisher. You'll have to get the complete version of SQL to publish your database. SQLEXPRESS servers can be Subscribers.

1. Publisher code: one sp

   exec sp_addmergesubscription 
       @publication = N'myPublication', 
       @subscriber = N'mySuscriber\SQLEXPRESS', 
       @subscriber_db = N'myDatabaseOnMySubscriber', 
       @subscription_type = N'pull', 
       @subscriber_type = N'local', 
       @subscription_priority = 0, 
       @sync_type = N'Automatic'
   go
   

2. Subscriber code: two sp's

   exec sp_addmergepullsubscription 
       @publisher = N'myServerName', 
       @publication = N'myPublicationName', 
       @publisher_db = N'myMainDatabase', 
       @subscriber_type = N'Local', 
       @subscription_priority = 0, 
       @description = N'', 
       @sync_type = N'Automatic'
   
   
   exec sp_addmergepullsubscription_agent
       @publisher = N'myServername', 
       @publisher_db = N'myMainDatabase', 
       @publication = N'myDatabaseOnMySubscriber', 
       @distributor = N'myServerName', 
       @distributor_security_mode = 1, 
       @distributor_login = N'', 
       @distributor_password = N'', 
       @enabled_for_syncmgr = N'True', 
       @frequency_type = 4, 
       @frequency_interval = 1, 
       @frequency_relative_interval = 1, 
       @frequency_recurrence_factor = 0, 
       @frequency_subday = 8, 
       @frequency_subday_interval = 1, 
       @active_start_time_of_day = 0, 
       @active_end_time_of_day = 235959, 
       @active_start_date = 0, 
       @active_end_date = 0, 
       @alt_snapshot_folder = N'', 
       @working_directory = N'', 
       @use_ftp = N'True', 
       @job_login = null, 
       @job_password = null, 
       @publisher_security_mode = 1, 
       @publisher_login = N'', 
       @publisher_password = N'', 
       @use_interactive_resolver = N'False', 
       @dynamic_snapshot_location = N'', 
       @use_web_sync = 1, 
       @internet_url = N'https://mySecuredWebPage:myOpenPort/myPublicationName/replisapi.dll',
       @internet_login = N'myDomain\myUserName', 
       @internet_password = null, 
       @internet_security_mode = 0, 
       @internet_timeout = 300
   go
   

3. Subscriber BAT file for synch

   "C:\Program Files\Microsoft SQL Server\90\COM\replmerg.exe"
       -Publisher [myServerName]  
       -PublisherDB [myMainDatabase]  
       -Publication [myPublicationName] 
       -Distributor [myServerName] 
       -Subscriber [mySubscriber\SQLEXPRESS] 
       -SubscriptionType 1 
       -SubscriberSecurityMode 1 
       -SubscriberDB [myDatabaseOnMySubscriber] 
       -InternetURL [https://mySecuredWebPage:myOpenPort/myPublicationName/replisapi.dll] 
       -InternetLogin [myDomain\myUserName] 
       -InternetPassword [myPassword]