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:

UPLOAD

1

Only merge Subscriber changes with the
Publisher.

DOWNLOAD

2

Only merge Publisher changes with the
Subscriber.

BIDIRECTIONAL

3

Merge all changes between the Publisher and
Subscriber (default).

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":

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.

0

No restrictions. Changes made at the Subscriber are uploaded
to the Publisher.

1

Changes are allowed at the Subscriber, but they are not
uploaded to the Publisher.

2

Changes are not allowed at the Subscriber.

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_optionsoption 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.