Merge Changes: 1 - Dynamic Filters and Unidirectional Merge

Introduction

This article begins to take a look at some of the new functionality in merge replication, using the SQL Server 2005 Beta CTP of April 2005. Specifically, I was interested in how to implement two different techniques: dynamic filters and unidirectional merge replication. Why these in particular? Well, because I had always though that the SQL 2000 implementation was rather awkward :) It involved manually editing the text in job steps, which is very much the exception in replication administration. This makes maintenance more difficult, and I had previously made a feature request that these features be added to the merge profile parameters which would avoid having to make such low-level editing. So, I was interested to see if this functionality had been changed to make the configuration more sophisticated and our lives easier.

Background

In case the reader is not entirely familiar with these concepts, here's an (extremely selective) potted intro.

(a) Dynamic Filters

Filters may be static or dynamic. When static, each subscriber gets the same data. An example static filter is Country = 'UK'. In this case the table data is filtered on synchronization to ensure that each subscriber gets only UK data. Dynamic filters ensure that each subscriber gets (potentially) a different horizontal partition of data. The subscriber is identified in the filter clause via a dynamic function. An example dynamic filter is Country = HOST_NAME() where the function HOST_NAME() resolves to the NETBIOS name of the (pull) subscriber's computer. In order to avoid having to rename each subscriber's computer to have NETBIOS country names and so make all this work, a common solution is to use the -HOSTNAME parameter in the merge agent's job which overrides the value returned from the dynamic function eg -HOSTNAME Wales. Another, less-frequently used solution, is to implement UDFs but let's forget this for now. So, to summarize, the -HOSTNAME  parameter doesn't exist in the subscription setup or the profile, and in most cases DBAs have resorted to editing the merge agent's job to get it working.

(b) Unidirectional Merge

The value of -EXCHANGETYPE determines the direction of merge replication changes. Once again this is implemented by manually editing the merge agent's job step, placing -EXCHANGETYPE 1|2|3 as text. The values 1|2|3 (| means 'or') are explained below:

By default, changes flow in either direction (-EXCHANGETYPE = 3). Entering a value of 2 means that changes to a replicated article at the subscriber are not prohibited, are recorded in the merge metadata tables via merge triggers, and are subsequently filtered out when the merge agent synchronizes. This means there may be a huge amount of metadata unnecessarily recorded, slowing down data changes and synchronization.

So, if we combined together dynamic filters and unidirectional changes, a merge agent's job step to have downloads only of Wales data would look like this:

How have things changed in SQL Server 2005?

(a) Dynamic Filters

When defining the subscriber, you are faced with a form allowing you to define (hardcode) a value for the HOST_NAME() function, which overrides the NETBIOS name.

Scriptwise this corresponds to

sp_addmergesubscription @hostname = 'hostname'

and is referred to as the "partition definition". So, we no longer have to edit job steps.

As in SQL Server 2000, this value overrides the value returned by the HOST_NAME() function. The HOSTNAME parameter input taken from UI is used to set the properties in the MSsubscription_properties table so that the merge agent can read the property from this table for subsequent syncs.  This is true for pull subscriptions.  However in case of push subscriptions there isn't a table that maintains such properties on the publisher hence the "-HOSTNAME host_name" string is put in the job command line at the distributor.  So, if you forget to assign a value when creating the subscription, it is still possible in push subscriptions to revert to SQL Server 2000 methodology, and manually place the "-HOSTNAME host_name" string in the merge job step. The command line -HOSTNAME parameter, if specified, (either by the DBA or by the UI), it is always used and always overrides everything else. For example, the HOSTNAME property in MSsubscription_properties table can be overridden by specifying a –HOSTNAME value in the command-line.   

(b) Unidirectional Merge

When adding an article, there is an option to define the "subscriber_upload_options":

sp_addmergearticle @subscriber_upload_options= subscriber_upload_options

This defines restrictions on updates made at a Subscriber (with a client subscription). The parameter "subscriber_upload_options" is a tinyint, and can have one of the following values.

So, how is this actually implemented?

Option 0 is defined as "no restrictions" and is the bidirectional default. This is much like using "-EXCHANGETYPE = 3" in SQL Server 2000. There are the usual 3 triggers on the subscriber to log all data changes to the merge metadata tables: MSMerge_ins_...MSMerge_upd_...MSMerge_del_...
Option 1 is defined as "download only, but allow subscriber changes". This is equivalent to "-EXCHANGETYPE = 2" but in this case there are no triggers at all on the subscriber. So, although these 2 methods are logically equivalent, the implementation has become much more sophisticated in SQL Server 2005 :). There is no firing of triggers to unnecessarily log metadata at the subscriber, which makes both subscriber data changes and the subsequent synchronization significantly faster.
Option 2 disallows all subscriber changes. In this case there is a special trigger - MSmerge_downloadonly_.... - which will rollback any attempt to change subscriber data. This option is new to SQL Server 2005 and has no equivalent in SQL Server 2000. If a user attempts to perform an update, they'll be faced with this message:

Msg 20063, Level 16, State 1, Line 1
Table '(null)' into which you are trying to insert, update, or delete data has been marked as read-only. Only the merge process can perform these operations.

The "-EXCHANGETYPE [1|2|3]" parameter to the merge agent's job is still available, even though it seems to have been largely superseded by the new functionality. This parameter still works with bidirectional (@subscriber_upload_options=2) but on efficiency grounds I wouldn't recommend using -EXCHANGETYPE as an alternative to the corresponding @subscriber_upload_options option in general. Perhaps an exception could be made in the case of -EXCHANGETYPE = 1 as there is no corresponding value for @subscriber_upload_options.

NB:  The EXCHANGETYPE specification skips the upload phase or the download phase of the merge agent and affects all articles in the publication whereas the @subscriber_upload_options in sql2005 is specified at article level.  So you can now have a subset of articles in the publication as download only.  This is a typical customer scenario where there are some tables that are lookup only (such as product price catalog) but there are other articles that are order entry which need to upload as well. 

One final point - to take advantage of this property (@subscriber_upload_options) the subscription must be marked as a 'Client' subscription and not the default of 'Server' subscription (see below). If you leave the default 'Server' option, the subscriber will always be created as bidirectional.

Conclusions

We've looked at the SQL Server 2005 replacements for the -EXCHANGETYPE and -HOSTNAME parameters in merge replication. The new functionality is a big step forward as both are now explicitly set in the publication and subscription properties. This is not implemented as additional merge profile parameter changes as I had expected (and requested) but it achieves the same end - no more hacking of replication jobs to get things working. In the case of the -EXCHANGETYPE replacement (@subscriber_upload_options) we see a significant improvement in performance and additionally the granularity to control matters at an article level rather than applying to the complete subscription.