Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jan 28, 2008

SQL Server 2005 Express Edition - Part 15 - Snapshot Replication

By Marcin Policht

In the previous installment 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 Files\Microsoft SQL Server\MSSQL.1\MSSQL\ReplData) and provide its UNC notation (in the format \\servername\sharename). 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 interface.

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

» See All Articles by Columnist Marcin Policht

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM