SQL Server 2005 Express Edition – Part 16 – Transactional and Merge Replication

In the recent installments of our series dedicated to SQL Server 2005
Express Edition, we started exploring its replication characteristics. As we
have pointed out, its capabilities in this area are fairly restricted, limiting
it exclusively to the role of a subscriber. Absence of SQL Server Agent has
further impact on the range of available configuration options, necessitating
employing somewhat unconventional implementation methods. In particular,
setting up pull subscriptions requires a workaround that leverages either
Windows Synchronization Manager (a utility built into the operating system),
replication-specific executables (included with SQL Server 2005 Express Edition
installation files), or custom coding (based on .NET Replication Management
Objects programming model).

In our
previous article
, we have presented the first one of these solutions in the
context of deploying snapshot replication. As we have demonstrated, while such
an approach has several important benefits (ease of setup and inherent
scheduling abilities), it suffers from a number of limitations, especially on
Windows XP and Vista clients. (In the case of the former, only a single
subscription can appear in the "Items to Synchronize" dialog box;
with the latter, Sync Center does not offer support for SQL Server 2005 Express
at all). Depending on your requirements, this situation might force you to
resort to the remaining alternatives. However, before we provide their
overview, we will first step through sample implementation of the other
replication two categories – transactional and merge (whose basic characteristics
we have described earlier).

In order to implement transactional replication, launch the New Publication
Wizard on the publisher (using the New Publication… option in the context
sensitive menu of the Local Publications subnode of the Replication node in the
Object Explorer window of SQL Server Management Studio). As before, we will
choose AdventureWorks as the source database, select Transactional publication
(instead of Snapshot publication) entry on the Publication Type page and use the
Department (HumanResources) table as the replication article (on the Articles
page), without specifying any filtering. Since transactional replication relies
on availability of the initial snapshot, you will be prompted to decide whether
you want to create one immediately so it is ready when the subscription is
initialized (this is the approach we will use in our demonstration) or whether
to schedule the snapshot agent at a later date and time. You will also need to
configure security options for both snapshot and log reader agent accounts (for
more information about the recommended settings, refer to the Replication Agent
Security Model
article of the SQL Server 2005 Books Online). In the final
step, assign a unique name to your publication and click on the Finish command
button to complete the procedure.

The process of subscribing to this new publication is very similar to the
one we documented earlier when discussing snapshot replication. As before,
start by launching the New Subscription Wizard using the New Subscription…
entry from the context sensitive menu of the Local Subscriptions subnode
(located under the Replication node of Object Explorer of the SQL Server
Management Studio Express on the subscriber). Once on the Publication page,
connect to the SQL Server instance operating as the publisher and select the
transactional publication, whose creation we described in the previous section.
At this point, you will have to decide whether to implement a push (with its
agents running on the distributor) or a pull subscription (which, in our case,
in absence of the SQL Server Agent component, implies the need for an alternative
synchronization method). In addition, you will be prompted to designate a
subscription database, pick (irrelevant in our case) the Distribution Agent
Security mechanism (which determines whether to impersonate the process account
for connections to the distributor and the subscriber), as well as confirm the
synchronization schedule. Note that you will not be able to choose the continuous
option, which is available when subscriptions are hosted on an instance of SQL
Server 2005 Standard or Enterprise Editions, but instead, you will have to
leverage the "Run on demand only" setting (triggered by one of our
custom workarounds). Finally, specify that you want to initialize the new
subscription at the first synchronization, ensure that the checkbox
"Create the subscription" on the Wizard Actions page is enabled, and
click on the Finish command button on the last page of the wizard.

After you confirmed that the initial snapshot of the Department (HumanResources)
table has been created, (it should appear in the snapshot folder as long as you
followed the instructions provided above), you can employ Windows
Synchronization Manager (providing that you enabled the "Use Windows
Synchronization Manager" option in the subscription Properties dialog box
in the Local Subscriptions node of the Object Explorer in the SQL Server
Management Studio Express on the subscriber) to initiate synchronization. (The
Status entry in the subscription Properties dialog box would provide the
summary of its outcome). By reviewing the content of the same Properties dialog
box of the subscription, you should be able to monitor its status (displayed in
the lower right corner).

Setup of merge replication can also be handled using the friendly interface
of the New Publication wizard. Once you select its entry on the Publication
Type page, you will be prompted to select the version of the database engine on
prospective subscribers from the following list:

  • SQL Server 2005 (which is the option suitable in our case),
  • SQL Server 2005 Mobile Edition (requiring snapshot files in
    character format),
  • SQL Server 2000 (lacking support for such features as logical
    records or replication of Data Definition Language changes),
  • SQL Server for Windows CE (subject to all of the listed above
    restrictions).

Next, you will see the familiar Articles page, where, for the purpose of our
example, we will choose the Production.ProductDescription table (which already
includes the uniqueidenifier column, required for resolution of potential merge
conflicts). While on this page, note that the range of properties that can be
configured for the selected articles is significantly more extensive compared with
those available for snapshot or transactional replication (including the ability
to manage identity range, control change merging behavior, or dictate which
object types and settings should be copied to subscribers). For the sake of
simplicity, accept the defaults and skip the filtering options. Accept the
default entries on the Snapshot Agent page (leading to the immediate creation
of a snapshot and scheduling its agent to run on biweekly basis). Finally, set
the snapshot agent security, assign a unique name to the publication, and
trigger its creation by completing the wizard.

In order to subscribe to the newly created merge publication, launch the New
Subscription Wizard on the subscriber (using the same "New
Subscription…" entry in the context sensitive menu of the Local
Subscriptions subnode of SQL Server Management Studio Express) and connect to
the publisher from the Publication page. As before, you will be offered a
choice between running all agents at the distributor, resulting in a push
subscription, or at the subscriber, generating a pull subscription, which
requires a custom synchronization mechanism (make sure that you select the
latter). Follow by designating the subscription database on the subscriber,
assign the same merge agent security settings we chose before ("By
impersonating the process account"), accept default synchronization
schedule ("Run on demand only"), and leave initialization of the
subscription with its "At first synchronization" default. On the next
page of the wizard, specify one of two possible subscription types you want to
use on your subscriber. Client type is suitable for most scenarios,
automatically performing the conflict resolution for a particular row change
when the first subscriber (which introduced such change) synchronizes with the
publisher (subsequent conflicts with other subscribers are effectively
ignored). Server type gives you more flexibility in managing multiple changes
to the same row by different subscribers (allowing assigning arbitrary
priorities to each subscriber and supporting republishing data to other
subscribers). For the purpose of our sample demo, select the Client option,
ensure that "Create the subscription" option is enabled on the next
page, and proceed with the wizard to its completion by clicking on the Finish
command button.

In order to perform merge replication (via means other than the already discussed
Windows Synchronization Manager), you can take advantage of the replmerg.exe
utility, which is used by traditional Replication Merge SQL Server Agent jobs.
Despite the fact that the agent functionality is not available in SQL Server
2005 Express Edition, this executable is still included with the installation
files and can be found in the Program
FilesMicrosoft SQL Server90COM
folder. While its syntax is
rather cumbersome, you can fairly easily come up with parameters necessary to
invoke synchronization by reviewing the content of the Replication Merge job
step on any existing SQL Server 2005 Standard or Enteprise Edition installation
(for a detailed description of all parameters, refer to the MSDN article Replication Merge
Agent
). For example, assuming that our publisher/distributor is located on
the default SQL Server 2005 Enterprise Edition instance on the computer ALPHA,
that our SQL Server 2005 Express Edition-based subscription database called AdventureWorksRepl
resides on OMEGA, and that our publication is called ProductDescription(Production),
then the following would allow you to trigger synchronization from the Command
Prompt. (Note that entire content below constitutes a single command and should
be typed in without any line breaks).

replmerg -Publisher [ALPHA] -PublisherDB [AdventureWorks] -Publication [ProductDescription(Production)] 
 -Distributor [ALPHA] -SubscriptionType 1 -Subscriber [OMEGASQLEXPRESS]
 -SubscriberSecurityMode 1 -SubscriberDB [AdventureWorksRepl] 

Similarly, you can initiate transactional replication by utilizing the distribution
agent executable distrib.exe (also stored in the Program FilesMicrosoft SQL Server90COM folder), by
launching it with an equivalent set of parameters (their full listing is
provided in the MSDN article Replication
Distribution Agent
, which, fortunately, fairly closely corresponds to those
used by replmerg.exe). Assuming our sample scenario as well as identically
named publisher/distributor and subscriber, you would be able to invoke
transactional replication by executing the following from the Command Prompt. (Again,
the entire content listed below needs to be typed as a single command).

distrib -Publisher [ALPHA] -PublisherDB [AdventureWorks] -Publication [Department(HumanResources)] 
 -Distributor [ALPHA] -SubscriptionType 1 -Subscriber [OMEGASQLEXPRESS] 
 -SubscriberSecurityMode 1 -SubscriberDB [AdventureWorksRepl]

Both utilities can be easily configured for unattended execution by
employing Windows Scheduled Tasks functionality. In our next article of this
series, we will review other replication scenarios involving SQL Server 2005
Express Edition-based subscriptions.

»


See All Articles by Columnist
Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles