Using Replication Management Objects, SQL Server subscriptions can be synchronized programmatically without using SQL Server Agent or SQL Server Management Studio.  Common uses include:

• Express edition subscribers
• Sync from within an application on-demand without an agent job
• Display agent status information in an application

In a previous post I provided a link to a code sample showing how to synchronize a Merge pull subscription in SQL Server Express using RMO.  Taking this a step further, for this post I will discuss how to implement a Merge Agent progress bar during synchronous execution, handling the MergeSynchronizationAgent.Status event and displaying the results in a Windows Form.

Synchronously synchronize asynchronously

When using the MergeSynchronizationAgent.Synchronize method to synchronize the Merge Agent it is important to realize that this starts the agent synchronously and control remains with the running agent until it completes.  This can make updating UI controls a bit tricky as UI events can be delayed until the Merge Agent finishes synchronizing, which is not very useful.  The key to making this work smoothly is to use a BackgroundWorker to synchronize the agent on a separate thread asynchronously and report progress back to the main UI thread when the MergeSynchronization.Status event is raised.

From here the synchronization BackgroundWorker.DoWork event handler can subscribe to the MergeSynchronizationAgent.Status event and start the Merge Agent synchronously for a specified subscription.

Status Event

The MergeSynchronizationAgent.Status event handler reports the Merge Agent progress passing StatusEventArgs PercentCompleted and Message back to the main UI thread which is handled by the synchronization BackgroundWorker.ProgressChanged event handler.

ProgressChanged Event

Finally, the synchronization BackgroundWorker.ProgressChanged event handler smoothly updates the progress bar and text box controls according to the Merge Agent status.

Sample

This code sample can be downloaded from the MSDN Code Gallery:

• SQL Server Express — Merge Pull Subscription Progress Bar (RMO Programming)

If you have any questions about the sample, or would like help integrating this into your application, feel free to ping me or leave a comment below.

-Brandon Williams (blog | linkedin | twitter)

I’m pleased to announce that this Wednesday April 11th 2012 Hilary Cotter will be presenting for the PASS DBA Virtual Chapter on SQL Server Replication.  Replication is a powerful and flexible data distribution solution that has become one of the more mature features in SQL Server.  Come listen to Microsoft MVP and replication expert Hilary Cotter discuss SQL Server Replication.  We should be in for a treat and I hope to see you all there.

PASS DBA Virtual Chapter Live Meeting Event:  http://dba.sqlpass.org/

Date and time:  April 11th 2012 12:00 pm Mountain time

Topic:  SQL Server Replication – sponsored by Quest Software

Presenter:  Hilary Cotter

Abstract:  Replication is a native SQL Server component which allows you to copy data from one database or server to another and can be configured to replicate bi-directionally.  In this webcast, SQL Server MVP., Hilary Cotter discusses replication types as well as common use cases and problems, as well as troubleshooting and monitoring and how to squeeze optimal performance from merge, transactional and bi-directional replication.

Bio:  Hilary Cotter is an industry veteran and has been a SQL Server MVP for 11 years. He specializes in replication, full-text search and SQL Server Service Broker. He has worked for many fortune 500 companies implementing cutting edge replication solutions. He has written and co-authored several books.

Live Meeting link:  The Live Meeting link for the webcast can be found on the PASS DBA Virtual Chapter website.

I was recently asked about synchronizing a Merge pull subscription in SQL Server Express using RMO, the lack of a SQL Server Agent, and what to do about it.  Since SQL Server Express does not include the SQL Server Agent, pull subscriptions must be synchronized by some other means.  The problem becomes evident when trying to open the View Synchronization Status dialog for a pull subscription in SQL Server Express.

The View Synchronization Status dialog depends on a SQL Server Agent job which does not exist in SQL Server Express.  To accommodate for the lack of a SQL Server Agent, SQL Server Express Merge pull subscriptions can also be synchronized using Replication Management Objects (RMO), Windows Synchronization Manager, or by executing batch scripts.  While not optimal without the SQL Server Agent, plenty of options are available. This is where it really pays to know the different ways a subscription can be synchronized when tasked with an Express subscriber.

Batch script

The Merge Agent (replmerg.exe) executable can be run from the command line on-demand or from a batch script as a scheduled task.  To run from the command line, execute replmerg.exe from the COM folder.

Likewise, this can be saved as a batch file and run from Task Scheduler on a schedule.  This option alone provides a pretty good replacement for the SQL Server Agent and synchronizing subscriptions in Express.

Windows Synchronization Manager

Another option to synchronize pull subscriptions in SQL Server Express is Windows Synchronization Manager, or Sync Center.  Sync Center provides options for setting and viewing sync partnerships, setting subscription properties and sync schedules, and viewing sync results and conflicts.  This tool is ideal for having non-technical users synchronize SQL Server Express pull subscriptions on-demand as it offers a user-friendly interface.  Synchronization schedules can also be configured as needed.

RMO

For all the developers, Replication Management Objects (RMO) can be used to synchronize SQL Server Express Merge pull subscriptions through managed code access.  The RMO MergeSynchronizationAgent class exposes a Synchronize method which can be used to synchronize a subscription without an agent job — additional properties must be supplied.  A code sample demonstrating how to synchronize a SQL Server Express Merge pull subscription using RMO can be downloaded from the MSDN Code Gallery.

So

As we can see, there are options for synchronizing pull subscriptions in SQL Server Express.  That is because Replication was built with SQL Server Express subscribers in mind.  Pull subscriptions can be synchronized using batch scripts, Windows Synchronization Manager, and RMO which should be sufficient enough to synchronize the data.  If you happen to know of another way to synchronize SQL Server Express pull subscriptions and would like to share, feel free to leave a comment below.

Looking for ways to do things more quickly and efficiently, I thought I would talk a little bit about sp_addscriptexec and how it can be leveraged to expedite the process of executing SQL scripts in a replication topology.  By creating and saving a SQL script on a publisher, it can then be distributed to and executed on UNC deployed subscribers using sp_addscriptexec.

The syntax for sp_addscriptexec per BOL

sp_addscriptexec [ @publication = ] publication
[ , [ @scriptfile = ] 'scriptfile' ]
[ , [ @skiperror = ] 'skiperror' ]
[ , [ @publisher = ] 'publisher' ]

The problem with numbers

If you’re dealing with a large number of subscribers, database administration can be tricky.  Tasks such as adding logins and users, granting permissions, maintaining indexes, and managing constraints must be done individually at each node and deploying all of the scripts can be very time consuming.  Rather than visit each node, sp_addscriptexec should be used to post the ad-hoc script to all subscribers in the topology, saving valuable time.  Put simply – if you’re visiting each node to execute a script, you’re doing it wrong.

The benefit with using sp_addscriptexec is that the publisher can act as a centralized script repository.  Scripts can be saved to the publisher and executed on demand for subscribers.  This process is quicker and more efficient than copying, saving, and executing scripts directly at each subscriber.  Not only does this save time, but space as well.

Executing scripts

Applying scripts to subscribers from the publisher can be done by:

• Create and test the script
• Save the script to the publisher
• Execute sp_addscriptexec at the publisher to apply script to subscribers

From here, the script will be executed at each subscriber on the next synchronization.  Taking this one step further — to limit a script to run on only certain subscribers, a check for HOST_NAME can be performed in the script.

-- Limit script to Subscriber1, Subscriber2, and Subscriber3
IF HOST_NAME() IN ('Subscriber1', 'Subscriber2', 'Subscriber3')
	-- script goes here

Something to consider

A caveat with sp_addscriptexec is that by default, if the script being executed encounters an error at the subscriber, the replication agent will fail and retry on subsequent syncs in an endless loop, be aware of this.  This behavior can be overridden by passing a value of 1 for @skiperror which instructs the agent to skip errors.  At the very least, SQL scripts should be tested thoroughly at the publisher before calling sp_addscriptexec to apply them to subscribers.

This is probably a rare occurrence — but I've noticed that data validation for an article with 10 billion or more rows will fail due to an arithmetic overflow error in sp_MSarticle_validation.

Performing data validation for a specific article in a transactional publication is done using sp_article_validation which in turn calls sp_MSarticle_validation.  In the definition of sp_MSarticle_validation, a local variable named @actual_rowcount is defined which is of the type bigint.  Later on in sp_MSarticle_validation, a command is built to execute sp_table_validation and the @expected_rowcount parameter passed in is derived by converting @actual_rowcount to a varchar(10).

The offending statement can be observed in the following code snippet from sp_MSarticle_validation:

select @command = 'exec dbo.sp_table_validation @table = ''' + replace(@destination_table, '''', '''''')  + ''', @expected_rowcount = ' +
	        convert(varchar(10), @actual_rowcount) + ', @expected_checksum = ' +
	        convert(varchar(100), @actual_checksum) + ', @rowcount_only = ' + convert(varchar(5),@rowcount_only) +
	        ', @full_or_fast = ' + convert(varchar(10), @full_or_fast) +
	        ', @shutdown_agent = ' + convert(varchar(10), @shutdown_agent)

 
Considering @actual_rowcount is a bigint, it should be converted to a varchar(19), rather than a varchar(10).  This is where an arithmetic overflow error occurs when validating an article that has 10 billion or more rows, causing validation to fail.

If you find yourself needing to validate an article with 10 billion or more rows, please vote this item as important to expedite a fix.

One of the challenges in working with Merge Replication is handling conflicts since writes can occur at any node in a topology.  This opens the door for the possibility of the same row being updated and different subscribers between synchronizations.  In an earlier post I demonstrated how to handle conflicts using the business logic handler framework but today I'd like to show how to create Merge Replication conflict alerts based on the Performance Monitor counter SQLServer:Replication Merge Conflicts/sec.

Ideally proper planning would be done in an application to minimize the chances of conflicts occurring, whether that is achieved through column-level tracking, partitioning the writes by utilizing a location specific identifier column and extending the primary key to this column, filtering, or some combination of the three.  However, sometimes a pesky developer can unknowingly introduce an application change which results in an onslaught of conflicts and having alerts in place is a smart precaution for just this scenario.

To setup a conflict alert we must first identify the Performance Monitor counter SQLServer:Replication Merge Conflicts/sec instance name that we would like to monitor.  In the Add Counters dialog in Performance Monitor we can identify the instance name for the Merge conflicts counter for a publication:

For this example the instance name that I will be monitoring is WS2008R2_1-AdventureWorksLT-TestMergePub1-WS2008R2_1-49.

From here we can use sp_add_alert and specify a merge conflict performance condition using our instance name to alert us when a conflict arises.  This can be done with the following bit of T-SQL:

USE msdb
GO
 
EXEC sp_add_alert @name=N'Merge Conflict Alert',
        @message_id=0,
        @severity=0,
        @enabled=1,
        @delay_between_responses=0,
        @include_event_description_in=0,
        @category_name=N'[Uncategorized]',
        @performance_condition=N'SQLServer:Replication Merge|Conflicts/sec|WS2008R2_1-AdventureWorksLT-TestMergePub1-WS2008R2_1-49|>|0'

In this example I specified the performance condition to alert me whenever a conflict arises.  This can easily be modified to only raise alerts when a certain threshold is met to suit your needs.  An alert response can also be configured to send an email when the conflict performance condition is met.  I hope this sheds some light on how to create conflict alerts for Merge Replication and as always, if you have any questions feel free to contact me or leave a comment below.

Over the last couple of years I've come to the conclusion that the default replication agent profiles just do not cut it.  For example, usually I want my replication agent jobs (Snapshot, Log Reader, Distribution, or Merge) to have some sort of a custom replication agent profile which differs from the default agent profile.  I'm usually interested in increasing agent timeout parameters and/or tuning the Merge Agent -UploadGenerationsPerBatch/-DownloadGenerationsPerBatch parameters to suit my needs.  I thought I would take a moment to share with you a trade secret that is readily available but generally unknown to the average database administrator to achieve this goal without having to manually set every individual agent profile.

Before creating publications and subscriptions, which in turn create replication agent jobs (minus SQL Server Express Edition), custom Snapshot, Log Reader, Distribution, and Merge Agent profiles can be configured via SSMS or using T-SQL.  Then, using the Distributor Properties dialog, or T-SQL, we can set the default profile for the replication type we're wanting to set so that all newly created agents will use said profile.

To do this using SSMS, on the Distributor right-click on the Replication node in Object Explorer -> Distributor Properties..., click on Profile Defaults... — Add a new agent profile and check the profile as Default for New.

From here all newly created agents will use that profile.

If you must use T-SQL, the same thing be accomplished using the undocumented stored procedure sp_MSupdate_agenttype_default.  The stored proc takes 1 parameter profile_id, and when executed will set the default agent profile for the agent_type of the specified agent profile.  This technique can be useful when creating several publications and/or subscriptions and all of them need to use a custom replication agent profile without having to set each one individually after the fact.

When creating a subscription in SQL Server you might come across the error:

The remote server "%s" does not exist, or has not been designated as a valid Publisher, or you may not have permission to see available Publishers.

This error is pretty straightforward and indicates that the replication agent login used to connect to the Publisher is not a member of the publication access list (PAL).  That would be the Distribution Agent process account for Transactional Replication and the Merge Agent process account for Merge Replication.  Add the agent process account to the PAL and try to create the subscription again.

SQL Azure Data Sync

This weekend I decided to get a little crazy and setup a SQL Azure sync group to synchronize a SQL Azure database with an on-premise replication publisher.  I was curious if this would work, as I’ve been unable to find any documentation stating whether or not SQL Azure Data Sync can co-exist with SQL Server Replication.  Not only was I able to get this working, but I took it a step further and downloaded the Windows Phone SDK — then wrote a quick and dirty Windows Phone app which connects to my SQL Azure sync group hub database and pulls down Customer and related Order data to my mobile device.

I was able to create a sync group in SQL Azure Data Sync to synchronize the Customer, SalesOrderHeader, and SalesOrderDetail tables in the AdventureWorksLTAZ2008R2 database, which also acted as an on-premise Merge publisher in my local network with 3 subscribers.  The solution provided a sync group hub database containing the Merge publication Customer and related Order data.  I settled for synchronizing the sync group every 30 minutes and kept my subscribers synching with the publisher on their normal schedules, which was every 1 minute in my test topology.

Windows Phone Application

From here, I created a 2 projects, 1 Windows Azure WCF Service Web Role which allows for Windows Phone app interaction with a SQL Azure database using Entity Framework, and 1 Windows Phone application using WCF to connect to the SQL Azure database through the service reference and consume the Customer and related Order data.

A couple of screenshots of the app:

As you can see, the potential for SQL Azure Data Sync is interesting.  Not only can sync groups be useful in providing an offsite database storage solution, but when synchronized with an on-premise replication publisher, can also provide a mechanism for exposing enterprise data to the cloud to be consumed by a mobile device.  SQL Azure Data Sync is currently available for preview and can be found in the Windows Azure Management Portal.  More information about SQL Azure Data Sync can be found in SQL Azure Data Sync on MSDN.

This blog post is basic in nature but highlights a very real requirement that may arise when administering replication.  Occasionally we, as Database Administrators, need to grant non-administrator business users the ability to run replication agent jobs due to business needs.  Instead of giving the business user the ability to execute/stop/start jobs by adding them to a SQL Server Agent fixed database role, it is probably wise to limit the business user to execute only the replication agent job in question.

For instance, if we want to give a business user the ability to run only the Snapshot Agent job – first, we can create a stored procedure RunSnapshotAgent that runs the Snapshot Agent job, using WITH EXECUTE AS to run the stored procedure as the database owner:

CREATE PROCEDURE dbo.RunSnapshotAgent
WITH EXECUTE AS 'snapshotagent_job_owner'
AS
EXEC sp_start_job
	@job_name = 'snapshot_agent_job'
GO

Where 'snapshot_job_owner' is the snapshot agent job owner and 'snapshot_agent_job' is the snapshot agent job name.

Next, we can create a role named snapshot_executor, and add the business user to that role:

CREATE ROLE snapshot_executor;
 
EXEC sp_addrolemember
	@rolename = 'snapshot_executor',
	@membername = 'BUSINESSUSER1';
GO

Where ‘BUSINESSUSER1’ is the business user’s login.

Then, grant execute rights for the RunSnapshotAgent stored procedure to the snapshot_executor role to allow the business user to start only the snapshot agent job:

GRANT EXEC ON dbo.RunSnapshotAgent TO snapshot_executor;
GO

After creating the stored procedure, creating the role, and adding the business user to that role; the business user can then execute the RunSnapshotAgent stored procedure to run only the Snapshot Agent:

EXEC
dbo.RunSnapshotAgent;
GO