Using Windows Synchronization Manager in Replication on SQL Server 2005

Article by Paul Ibison

Introduction

Windows Synchronization Manager (WSM) is a windows utility which can be used to initiate various SQL Server replication features for a subscriber. It came into being in Windows 2000 and exists in all subsequent Windows versions. In this article I'll look at setting it up on Windows XP and a companion article will look at using Windows Vista, which are the most likely candidates these days for a client subscription.

Initially you might wonder why this is being mentioned at all - why use this strange-sounding tool when we typically and easily control the replication process through SQL Server Agent jobs? Well, first of all in the case of SQL Server Express it doesn't include SQL Server Agent so an alternative means of synchronization has to be used. You could go down the route of .NET programming and create an RDL synchronization methodology, but you might want to use WSM as a cheaper, quicker and simpler solution. Users of SQL Server Express are not the only people who might want to use WSM though - it can be considered a simple tool for testing the replication synchronization setup regardless of the edition of SQL Server, and it is also a tool that can be used completely independently of SQL Server Management Studio so it could naturally be used by any non-DBA support person who needs to synchronize a subscription.

Finding WSM

So let's take a look at it and see what it can do. On Windows XP it is found here:

If you open WSM on a machine which has no subscriptions enabled it will look as below.

The Microsoft SQL Server 2005 list item won't exist, and unless you have a great desire to synchronize your home page (what?) it's game over! As an aside, this is quite different to SQL Server 2000 where there is always an empty check box and the option to create a subscription but this ability has now been removed.

So, how can we start using WSM? Firstly, you'll need an existing pull subscription on the same machine. In itself though this is not enough, and there is a mandatory subscription setting which can be modified here:

I mention that the subscription needs to be pull and to be honest I'd always assumed this was a requirement but when researching for this article I noticed that the @enabled_for_syncmgr parameter exists for both sp_addpullsubscription_agent and sp_addpushsubscription_agent. So, although this is not exposed in the GUI, it is indeed possible to use WSM to sync up push subscriptions! However I doubt it ever really gets used, so we'll concentrate on pull.

Once the subscription is enabled, we can start to play around with WSM. It automatically picks up the details of the subscription (as far as I can tell this is from the registry) which will be listed as below.

Using the Functionality of WSM

The WSM allows you to:

synchronize a subscription
reinitialize a subscription
remove a subscription

The synchronization can be manually initiated from the initial form above. You'll have as many entries as you have subscriptions and each one can be manually synchronized separately. This is a common use of the WSM and further investigation of the properties and capabilities may not be needed if this is all you'll ever require.

Properties Tabs

Reinitializing and removing the subscription require you to highlight the subscription and select "Properties" which opens the dialog box below:

You'll always have the "Identification" and "Other" tabs and exactly which of the login tabs are displayed seems to depend on the type of subscription: in a standard transactional replication case it'll just show the "Distributor Login" while for merge replication there will also be the "Publisher Login" tab.

The text boxes on the Identification tab are entirely read-only and this tab enables the Reinitialize and Removal processes (buttons at the bottom). Again this is different to SQL Server 2000 where each of the text boxes was editable. This is now greatly simplified - there is no possibility of a typo or of a user attempting to synchronize a subscription on another computer (which is not supported).

The behaviour of these functions is pretty much the same as using the SQL Server Agent, with just a couple of exceptions in the case of merge replication:
(1) when you reinitialize in merge replication, there is usually the option to have any outstanding changes uploaded to the Publisher before the snapshot is applied, and this option is not there for WSM.
(2) there is an option to use "interactive conflict resolution" which is initiated directly from the WSM.

The Distributor Login tab allows you to enter details for the authentication - i.e. who the WSM will run as. If it uses windows authentication (the default), then it runs as the user currently logged on, who must therefore also exist in the publication access list. Alternatively you can enter the details of a SQL Server login. In this case the WSM will work the first time it is used but the password is not retained for subsequent future uses, and must be re-entered each time.

The Other tab allows you to configure the query timeout and login timeout of the synchronization process.

The Setup Tabs

Clicking on the Setup button on the initial WSM form opens up another tabbed form called "Synchronization Settings".

This is a form which controls exactly when the synchronization is to occur. Obviously it could be run manually each time and this is fine and wouldn't require any additional configuration. However perhaps you don't want staff to have to initiate the synchronization process and prefer it to be automated in some way. in this case the tab names correspond to the 3 options on offer - when logging on or off, when the computer is idle or on a scheduled basis. The latter is particularly interesting, as we end up with effectively the same functionality as the SQL Server agent jobs.

Conclusions

Prior to writing this article I hadn't really used WSM much and really didn't expect much, so I was pleasantly surprised by the range of synchronization functionality on offer. While it'd be nice to also be able to add a subscription this is not a huge problem and for the case of syncing up merge on SQL Server Express i'll definitely consider it as an option in the future.