How to synchronize SQL Express subscribers using VBA

How to synchronize SQL Express subscribers using VBA

Chris Skorlinski
SQL Server Escalation Services

Question to research:  How can subscribers with just VBA do an "on demand synchronization"?  

Background:  SQL 2008 R2 Standard Edition Publisher/Distributor with SQL 2008 R2 Express subscribers.  SQL Express does not include SQL Agent to schedule Replication jobs, therefore, the subscribers are setup using “push” Replication from the Publisher.  They would like the ability for subscribers to perform ‘on demand synchronization.  Normally we can use Replication Management Objects (RMO) with VB or C# application and write the sync code.  But for this particular project the don’t have ability to write client-side VB or C# code.  They can only write VBA code.  This provides challenges for synchronizing clients.

These options are based on VBA’s ability to connect to the SQL Server Publisher/Distributor and query tables and/or execute stored procedures.


— Retrieve the [application_name] for a given [subscriber_server]
SELECT [subscriber_server]
  FROM [AdventureWorksLT].[dbo].[sysmergesubscriptions]
  WHERE subscriber_server = ‘C755\SQL2K8R2’
[application_name] ==  C755\SQL2K8R2-AdventureWorksLT-AW_Merge_Products-C755\SQL2K8R2-2

— Start the SQL Merge Agent "PUSH" Job on the Publisher/Distributor.
EXEC msdb.dbo.sp_start_job @job_name = ‘C755\SQL2K8R2-AdventureWorksLT-AW_Merge_Products-C755\SQL2K8R2-2’


— Retrieve the [mergeagentname] from sp_replmonitorhelpsubscription
sp_replmonitorhelpsubscription  @publisher =’C755\sql2k8r2′, @publication_type=2

— — Start the SQL Merge Agent "PUSH" Job on the Publisher/Distributor using the [mergeagentname] as the Job_Name
   EXEC msdb.dbo.sp_start_job @job_name = ‘<mergeagentname>’


–Copy all the Agent parameters from the Job Step.
–Execute a CMD Prompt for Merge.exe using all the Merge Agent parameters.

c:>… mssql\com\merge.exe -Publisher [C755\SQL2K8R2] -PublisherDB [AdventureWorksLT] -Publication [AW_Merge_Products] -Subscriber [C755\SQL2K8R2] -SubscriberDB [AdventureWorksLT_Sub1]   -Distributor [CH755\SQL2K8R2] -DistributorSecurityMode 1 

Permissions Needed for sp_start_job
"Members of SQLAgentUserRole and SQLAgentReaderRole can only start jobs that they own."

SQL Server Agent Fixed Database Roles

Monitor Replication:

Tracking Merge Agent Performance using Distribution database system tables

Comments (1)

  1. Kris Hokanson says:

    I just finished a project where we had an Access application storing its data locally on SQL Express and using a merge publication to synchronize that data up to our corporate servers via web replication.  Started out by creating the app as a COM managed add-in to Access so that we could develop the code in C# and use RMO.  This worked pretty well for x86 boxes but when I tried it on my x64 boxes it wasn't happy.  

    After some hair loss I abandoned the RMO process and basically used option 3 although still as an add-in even though it could just as easily have been done within Access using VBA.  Since contacting the publisher/distributor is not really an option when using web replication option 3 would typically be the way to go and it works fine for the most part.