SQL Server 2005 Express Edition – Part 15 – Snapshot Replication

In the previous
of our series dedicated to SQL Server 2005 Express Edition, we
started exploring its replication characteristics. As we pointed out, this was
one of the areas where its feature set has been significantly restricted compared
with both its predecessor (MSDE) as well as full-fledged counterparts (such as
SQL Server 2005 Standard, Developer, or Enterprise Editions), not only limiting
it strictly to the role of the subscriber but also eliminating traditional
agent-based modes of operation. In this article, we will examine practical
implications of these restrictions by illustrating the process of setting up a
snapshot replication.

In our demonstration, we will use a straightforward arrangement consisting
of an installation of SQL Server 2005 Enterprise Edition (however, Standard or
Developer would suffice in this case as well) functioning as both Publisher and
Distributor communicating with a subscriber hosted by an instance of the SQL
Server 2005 Express Edition (by employing this simplified scenario, we will be
able to focus on the configuration of the latter) running on top of the Windows
XP Professional operating system. Since we will rely on the integrated Windows
authentication, both computer accounts should be members of the same or trusted
Active Directory domains. Our replication will consist of articles representing
objects from the AdventureWorks database (in case you have not included it with
your original installation, you can either re-run the setup process or download
it from the CodePlex
Web site

Start by making sure that SQL Server Agent Service is running (as well as being
assigned the automatic startup type) on a system that will be serving as our
publisher and distributor. Next, launch the SQL Server Management Studio and
connect to the local instance of the database engine. Right-click on the
Replication node in the Object Explorer window and invoke from its
context-sensitive menu the Configure Distribution wizard. On the Distributor
page, select the local server as a target and click on the Next command button.
When prompted to designate a snapshot folder, use Windows Explorer to create a
share corresponding to its preconfigured location (i.e. Program FilesMicrosoft SQL ServerMSSQL.1MSSQLReplData)
and provide its UNC notation (in the format \servernamesharename).
Once this is completed, you will also need to choose a name of the distribution
database and a location where its files will be stored. Accept the default and
point to the SQL Server instance that will become the publisher (which, in our
case, is the same system that hosts distributor). Choose the "Configure
distribution" option on the next page and complete the wizard by clicking
on the Finish command button.

The context-sensitive menu of the Replication node will offer an option to
display "Publisher Properties…" at this point. In the resulting
dialog box, you should see the list of all non-system databases, which you can
designate to function as publishers for Transactional or Merge replication.
Since in our examples we will be using AdventureWorks to demonstrate both
scenarios, mark checkboxes in both Transactional and Merge columns next to its
name, confirm the selection, and close the dialog box. To configure a snapshot
publication (which, incidentally, is also employed by other replication types),
right click on the Local Publishing subfolder (located under the Replication
node) and choose New Publication… from its context sensitive menu, triggering
the New Publication Wizard. Pick AdventureWorks from the list of databases on
the Publication Database page, followed by Snapshot publication on the list of
publication types (other possibilities listed on the Publication Type page
include Transactional publication, Transactional publication with updateable
subscriptions, and Merge publication). Next (on the Articles page) you will be
presented with a hierarchical view of objects that can be published (including
tables with their individual columns, stored procedures, views, indexed views,
and user-defined functions). From the same interface, you also have access to a
variety of options that can be individually enabled or disabled for each of
them using the Article Properties dialog box (accessible via appropriately
labeled command button). Select the Address(Person) table and (for the sake of
simplicity) accept the default filtering options. Mark "Create a
snapshot immediately and keep the snapshot available to initialize subscriptions
checkbox on the Snapshot Agent page. (Note that it is possible to schedule
snapshot agent to be initiated at a later time). When prompted for security
settings of the snapshot agent process, specify a Windows domain account with
sufficient privileges (which include Write permissions to the snapshot share we
created earlier and the db_owner fixed database role in the publication and
distribution databases), along with its password. Finally, accept the default
option on the Wizard Actions page that will lead to creation of the publication
at the completion of the wizard. After you assign it an arbitrary name, its
entry should appear under the Local Publications folder in the Object Explorer

At this point, you are ready to configure the subscriber that will receive
the publication you just created. To accomplish this, launch the SQL Server
Management Studio Express on the target Windows XP system, expand Replication
node in its Object Explorer window, right click on the Local Subscriptions
subfolder, and select the New Subscription… item in its context sensitive
menu, invoking the New Subscription Wizard. On the Publication page, pick entry
in the Publisher listbox. This will trigger the Connect to Server dialog box,
where you need to type in the name of the server hosting our publisher
database. Provided that your connection is successful, you will be presented
with the list of databases and their publications, including the one you just
created based on the Address(Person) table of the AdventureWorks database. Once
you select it and click on Next, you will have to make the decision (important
in the context of our discussion) regarding placement of the Distribution
Agent. One possibility involves running it at the distributor, which results in
a push subscription. This approach is easier to implement since it leverages
functionality built into any of the full-featured SQL Server 2005 editions, but
it might result in a significant burden on performance of the computer serving
as the distributor and is typically not appropriate in situations where its
connectivity to subscribers is restricted or intermittent. These drawbacks are
eliminated by employing the other option labeled "Run each agent at its
Subscriber (pull subscription)". This, however, in the case of SQL Server
2005 Express Edition, requires implementing a mechanism that would compensate
for the agentless nature of its replication mechanism. Considering that the
first of these two options is fairly straightforward and does not involve any
configuration changes to the subscriber, we will focus on the second one.

Following the selection of the Distribution Agent location, you need to
either point to an existing subscription database or create a new one dedicated
to this purpose. Next, on the Distribution Agent Security page, you must
specify the security context in which connections to the distributor will be
established. Accept the default ("By impersonating the process
account") option on this as well as the following ("Run on demand
only" Synchronization Schedule) page (although note that you would need to
adjust it accordingly if a SQL Server login was used to connect to the
distributor). Since our subscription database needs to be populated with a
snapshot of the publication data and schema, ensure that the checkbox in the
Initialize column is enabled on the Initialize Subscriptions page (forcing this
activity during the first synchronization). Finally, verify that the
"Create the subscription(s)" checkbox is marked on the Wizard Actions
page and complete configuration by clicking on the Finish command button. As
the result, you should see an entry corresponding to the newly created
subscription under the Local Subscriptions folder in Object Explorer of the SQL
Server Management Studio Express on our subscriber (as well as its equivalent
listed under the source publication on the Publisher).

To generate a snapshot, use the Replication Manager interface or View
Snapshot Agent Status dialog box, both of which you can invoke from the context
sensitive menu of the source publication subnode of the Replication node in
Object Explorer of SQL Server Management Studio running on the publisher. (You can
confirm the outcome of this method by examining the content of the snapshot
share we created earlier). However, synchronizing our subscription cannot be
accomplished using any of the traditional methods (available with full-featured
editions) that depend on SQL Server Agent-specific features, such as, using the
View Synchronization Status dialog box. Missing also are options allowing you
to "View Job History" or "Launch Replication Monitor". In
order to initiate synchronization of the subscriber, you will need to resort to
one of several workarounds we have mentioned in our previous article.

The most straightforward approach involves the use of Windows
Synchronization Manager, invoked via Synchronize shortcut in All
Programs->Accessories menu, where the newly created subscription should be
displayed (provided that its "Use Windows Synchronization Manager"
property is set to "Enabled"). Included as part of the operating
system since the release of Windows 2000, it offers an interface consisting of
"Items to Synchronize" dialog box, from which you can:

  • trigger synchronization (with the Synchronize command button) for
    selected items (in our case, this applies to our sample subscription, which
    appears under Microsoft SQL Server 2005 node),
  • modify their properties (such as authentication type used for
    subscriber login),
  • reinitialize or remove them (using Reinitialize Subscription…
    and Remove Subscription… command buttons in the Properties dialog box),
  • define synchronization settings (via Setup… command button in
    Items to Synchronize interface).

These options allow you to not only schedule synchronization tasks (by
leveraging Windows built-in Tasks Scheduler service) but also specify whether
they should be automatically executed when the computer is idle for an arbitrarily
defined period of time or during such events as logon or logoff. To verify the
outcome of synchronization, check the status of the subscription in its
Properties window in SQL Serer Management Studio Express or simply examine the
content of the replicated publication.

Unfortunately, this functionality appears to be dependent to a large extent
on the version of the operating system. While it performs as expected on
Windows Server 2003-based implementations, the number of entries displayed in
the "Items to Synchronize" dialog box in SQL Server 2005 Express
Edition installations on Windows XP Professional systems seems to be limited to
a single subscription only. (This means that in order to synchronize additional
subscriptions, it is necessary to remove the original one from the listing,
which, at the very least, requires disabling its "Use Windows
Synchronization Manager" property). Furthermore, SQL Server 2005
synchronization task is not integrated into the Vista’s Sync Center (successor
of Windows Synchronization Manager). In order to deal with these
inconsistencies, you will have to resort to other methods that provide
equivalent functionality. We will review them in the next article of our


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.

Latest Articles