How to synchronize SQL Express subscribers using VBA

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.

OPTION 1:

— Retrieve the [application_name] for a given [subscriber_server]
SELECT [subscriber_server]
,[db_name]
,[application_name]
,[last_sync_date]
,[last_sync_status]
,[last_sync_summary]
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’

OPTION 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>’

OPTION 3:

–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
————————————————
http://msdn.microsoft.com/en-us/library/ms186757(SQL.90).aspx
“Members of SQLAgentUserRole and SQLAgentReaderRole can only start jobs that they own.”

SQL Server Agent Fixed Database Roles
http://msdn.microsoft.com/en-us/library/ms188283(SQL.90).aspx

Monitor Replication:
—————————
•sp_replmonitorhelpsubscription
http://blogs.msdn.com/b/repltalk/archive/2010/09/20/how-to-monitor-the-health-of-sql-server-replication.aspx

Tracking Merge Agent Performance using Distribution database system tables
http://blogs.msdn.com/b/repltalk/archive/2010/03/16/tracking-merge-agent-performance-using-distribution-database-system-tables.aspx