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 Files\Microsoft SQL Server\90\COM 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 [OMEGA\SQLEXPRESS]
 -SubscriberSecurityMode 1 -SubscriberDB [AdventureWorksRepl] 

Similarly, you can initiate transactional replication by utilizing the distribution agent executable distrib.exe (also stored in the Program Files\Microsoft SQL Server\90\COM 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 [OMEGA\SQLEXPRESS] 
 -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.