Section 19.1. Programming Replication

19.1. Programming Replication

SQL Server Replication Management Objects (RMO) is a collection of namespaces introduced in SQL Server 2005 for programming all aspects of SQL Server 2005 replication. RMO supersedes replication functionality in SQL-DMO. RMO is

Agents Supporting Replication

Replication uses a set of programs called agents to perform tasks associated with replication . A description of these agents follows:

Replication Distribution Agent

Applies the initial snapshot to the subscriber and moves transactions from the distribution database to a subscriber. Runs at the distributor for push subscriptions and at the subscriber for pull subscriptions. Used with snapshot and transactional replication.

Replication Log Reader Agent

Moves transactions marked for replication from the transaction log on the publisher to the distribution database. Used with transactional replication.

Replication Merge Agent

Applies the initial snapshot to the subscriber and reconciles and updates both the subscriber and publisher with changes that occur. Runs at the distributor for push subscriptions and at the subscriber for pull subscriptions. Used with merge replication.

Replication Queue Reader Agent

Moves changes made at the subscriber back to the publisher. Runs at the distributor and is used with transactional replication with the queued updating option.

Replication Snapshot Agent

Prepares snapshot files that contain the schema and initial data for published tables, stores the files in the snapshot folder, and records synchronization jobs in the database. Runs at the distributor and is used with all types of replication.

compatible with SQL Server 2000 and SQL Server 7.0, which lets you manage replication in environments having a mix of different SQL Server versions.

The following subsections provide examples that show how to use the replication classes and include descriptions of the classes. The examples use merge replication, but transactional replication is similar. You need a reference to the following assemblies to compile and run the examples:

• Microsoft.SqlServer.ConnectionInfo

• Microsoft.SqlServer.Replication .NET Programming Interface

Additional assembly references are indicated for examples in which they are required.

The ReplicationServer object described in is the top-level class in the RMO class hierarchy. It represents a SQL Server instance involved in replication. The server can take the role of distributor, publisher, subscriber, or a combination of those roles.

19.1.1. Prerequisites

Most of the examples in this chapter build on each other. There are a few things you need to do before you start.

Disable replication if it is enabled. This will let you run the first two examples, which install a distributor and create a publisher. To disable replication, right-click the Replication node in Object Explorer in SQL Server Management Studio, select Disable Replication from the context menu, and follow the instructions in the wizard.

Create a database named ReplicationDestination by right-clicking the Databases node in Object Explorer and selecting New Database from the context menu. In the New Database dialog box, set the Database name listbox to ReplicationDestination, accept the defaults, and click OK to create the database.

Ensure that the setup is correct by following these steps:

1. Right-click the Databases node in Object Explorer and select Refresh from the context menu.

2. Expand the Databases node in Object Explorer by clicking the plus sign next to it.

3. Right-click the Replication node in Object Explorer and select Refresh from the context menu.

4. Expand the Replication node in Object Explorer by clicking the plus sign next to it.

5. Right-click the Replication node in Object Explorer.

The context menu should appear as shown in Figure 19-1. You should also see the new ReplicationDestination database.

19.1.2. Installing a Distributor

This example shows how to install a distributor onto the local SQL Server instance. It instantiates a ServerConnection object representing the local machine, and then creates a ReplicationServer object based on this ServerConnection object.

The next object created is a DistributionDatabase object, called distribution and linked to the ServerConnection object named sc. A distribution database stores replication information on the distributor.

Finally, the InstallDistributor( ) method of the ReplicationServer class installs a distributor onto the currently connected or remote SQL Server instance. The distribution database is created as a system database. The password for the InstallDistributor( ) method must conform to your password policy.

The source code for the example follows:

     using System;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Replication; 

Figure 19-1. Prerequisite configuration

     class Program     {         static void Main(string[] args)         {             ServerConnection sc = new ServerConnection("localhost");             // create the distributor             ReplicationServer dist = new ReplicationServer(sc);             // install the distributor             DistributionDatabase dDb = new DistributionDatabase(                 "distribution", sc);             dist.InstallDistributor("password1", dDb);             sc.Disconnect(  );             Console.WriteLine("Press any key to continue.");             Console.ReadKey(  );         }     } 

Once you have executed the example, confirm that the distributor has been installed by right-clicking the Replication node in Object Explorer. The context menu should appear as shown in Figure 19-2. The Configure Distribution context menu item is replaced by two new menu itemsDistributor Properties and Disable Publishing and Distribution.

Figure 19-2. Replication context menu after installing a distributor

You can see the new distribution database in Object Explorer in SQL Server Management Studio by right-clicking the Replication node and selecting Distributor Properties from the context menu. The distributor properties are displayed in the Distributor Properties dialog box, shown in Figure 19-3.

The RMO classes used to programmatically manage distribution and distributor objects are described in Table 19-1.

Table 19-1. RMO classes for managing distribution databases

Class	Description
DistributionDatabase	Represents a distribution database that stores replication information on the distributor.
DistributionDatabaseCollection	Represents a collection of DistributionDatabase objects. The DistributionDatabases property of the ReplicationServer class returns a DistributionDatabaseCollection object containing all distribution databases on the distributor.

19.1.3. Creating a Publisher

This example creates a publisher on the local SQL Server instance. The example instantiates a DistributionPublisher object and associates it with the target ServerConnection object. The DistributionPublisher class represents a computer that acts as both a publisher and a distributor.

Figure 19-3. Distributor Properties dialog box

Next, several properties of the DistributionPublisher object are set, and then its Create( ) method is called. The DistributionDatabase property links this DistributionPublisher object with the DistributionDatabase object you created in the previous example. The PublisherSecurity property accesses the security context as a ConnectionSecurityContext object. It is used by the replicating agent to connect to the distribution publisher. The ConnectionSecurityContext object specifies an authentication mode and, if SQL Server authentication is used, the login and password. This example uses Windows authentication.

Before you execute this example, replace the ServerName argument in the DistributionPublisher constructor with the name of your database server and create the directory C:\PSS2005\Replication.

The source code for the example follows:

     using System;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Replication;     class Program     {         static void Main(string[] args)         {             ServerConnection sc = new ServerConnection("(local)");             DistributionPublisher dp = new DistributionPublisher(                 "ServerName", sc);             dp.DistributionDatabase = "distribution";             dp.WorkingDirectory = @"C:\PSS2005\Replication  ";             dp.PublisherSecurity.WindowsAuthentication = true;             dp.Create(  );             sc.Disconnect(  );             Console.WriteLine("Press any key to continue.");             Console.ReadKey(  );         }     } 

Once you have executed the example, confirm that the publisher has been created by right-clicking the Replication node in Object Explorer. The context menu should appear as shown in Figure 19-4. A new context item, Publisher Properties, has been added.

Figure 19-4. Replication context menu after creating a publisher

The RMO classes used to manage publishers programmatically are described in Table 19-2.

Table 19-2. RMO classes for managing publishers

Class	Description
DistributionPublisher	Represents a computer that acts as both publisher and distributor.
DistributionPublisherCollection	Represents a collection of DistributionPublisher objects. The DistributionPublishers property of the ReplicationServer class returns a DistributionPublisherCollection object containing all publishers that use the SQL Server instance as a distributor.
PublisherConnectionSecurityContext	Represents login information when connecting to a publisher server instance. The PublisherSecurity property of the PullSubscription class returns the security context used by the synchronization agent when connecting to the publisher.

As mentioned earlier in the section, the PublisherSecurity property of the PublisherSubscriber class accesses the security context as a ConnectionSecurityContext object that is used by the replicating agent to connect to the distribution publisher. The RMO class used to programmatically manage security context information is described in Table 19-3.

Table 19-3. RMO class for managing connection security context information

Class	Description
ConnectionSecurityContext	Represents information for connecting to SQL Server replication publishers, distributors, and subscribers. The connection security context information specifies an authentication mode and, if SQL Server authentication is used, the login and password.

19.1.4. Enabling a Database for Publication

This example enables the AdventureWorks database for merge publication. It does so by creating a ReplicationDatabase object and associating it with the AdventureWorks database. ReplicationDatabase represents a replication database, either a publisher or a subscriber.

The EnableMergePublishing and EnableTransPublishing properties of the ReplicationDatabase class control whether a database is available for merge and transactional replication publication.

The source code for the example follows:

     using System;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Replication;     class Program     {         static void Main(string[] args)         {             ServerConnection sc = new ServerConnection("localhost");             ReplicationDatabase rDb = new ReplicationDatabase(                 "AdventureWorks", sc);             rDb.EnabledMergePublishing = true;             sc.Disconnect(  );             Console.WriteLine("Press any key to continue.");             Console.ReadKey(  );         }     } 

The code in this example only enables the merge publishing of the AdventureWorks database. It does not actually publish anything. Subsequent examples will show you how to publish an article.

After you run the code, confirm that the AdventureWorks database is enabled for merge publication by selecting Replication Publisher Properties in Object Explorer and then selecting the Publication Databases page. Figure 19-5 shows AdventureWorks enabled for merge publication.

19.1.5. Creating a Publication

This example creates a merge publication named AdventureWorks_MergePub for the AdventureWorks database. It does so by instantiating a MergePublication object and then setting its Name, DatabaseName, ConnectionContext, and Status properties. Finally, it invokes its Create( ) method.

The source code for the example follows:

     using System;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Replication;     class Program     {         static void Main(string[] args)         {             ServerConnection sc = new ServerConnection("localhost");             MergePublication mp = new MergePublication(  );             mp.Name = "AdventureWorks_MergePub";             mp.DatabaseName = "AdventureWorks";             mp.ConnectionContext = sc; 

Figure 19-5. Publisher Properties dialog box showing AdventureWorks database

             mp.Status = State.Active;             mp.Create(  );             sc.Disconnect(  );             Console.WriteLine("Press any key to continue.");             Console.ReadKey(  );         }     } 

After executing this example, you can view the new publication in Object Explorer by refreshing and expanding the Replication Local Publications node in Object Explorer, as shown in Figure 19-6.

The publication will not publish anything, because no articles have been defined for it. The next section,"Creating an Article," defines an article to publish.

The RMO classes used to manage publications are described in Table 19-4.

Figure 19-6. Results for creating a publication example

Table 19-4. RMO classes for managing publications

Class	Description
DistributionPublication	Represents read-only information about the distributor image of a snapshot, transactional, or merge publication.
DistributionPublicationCollection	Represents a collection of DistributionPublication objects. The DistributionDatabases property of the DistributionPublisher class returns a DistributionPublicationCollection object containing all distribution publications defined on the distribution database.
MergeDynamicSnapshotJob	Represents information about the Snapshot Agent job that generates the snapshot data for a subscription to a merge publication with a parameterized row filter. The EnumDynamicSnapshotJobs( ) method of the MergePublication class returns an ArrayList object of dynamic snapshot jobs for the merge publication.
MergePartition	Represents information about a subscriber partition for a merge publication with a parameterized row filter. The EnumMergePartitions( ) method of the MergePublication class returns an ArrayList object of subscriber partitions for the merge publication.
MergePublication	Represents a merge publication. The EnumMergePublications( ) method of the ReplicationDatabase class returns an ArrayList object of merge publications that use the replication database.
MergePublicationCollection	Represents a collection of MergePublication objects. The MergePublications property of the ReplicationDatabase class returns a MergePublicationCollection object containing all merge publications defined on the replication database. The MergePublications property of the DistributionPublisher class returns a MergePublicationCollection object containing all merge publications defined on the distribution publisher.
PublicationAccess	Represents login information in the publication access list (PAL) for a publication.
transPublication	Represents a transactional or snapshot publication.
TRansPublicationCollection	Represents a collection of TRansPublication objects. The TRansPublications property of the ReplicationDatabase class returns a TRansPublicationCollection object containing all transactional and snapshot publications defined on the replication database. The transPublications property of the DistributionPublisher class returns a transPublicationCollection object containing all transactional and snapshot publications defined on the distribution publisher.

19.1.6. Creating an Article

This example creates an article named Article_1 in the AdventureWorks_MergePub merge publication created in the preceding section, "Creating a Publication." The steps are the same as you've seen previously:

1. Instantiate the appropriate object, in this case a MergeArticle object (which represents an article in a merge publication).

2. Set the appropriate properties.

3. Call the object's Create( ) method.

The properties of interest for a MergeArticle object are as follows:

Name

The name under which SQL Server stores the article

PublicationName

The name of the merge publication through which the article is exposed for replication

DatabaseName

The name of the underlying database

ConnectionContext

The ServerConnection object representing the target machine

SourceObjectName

The name of the object in the database

SourceObjectOwner

The name of the owner (schema) of the database object being published

The source code for the example follows:

     using System;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Replication;     class Program     {         static void Main(string[] args)         {             ServerConnection sc = new ServerConnection("localhost");             MergeArticle ma = new MergeArticle(  );             ma.Name = "Article_1";             ma.PublicationName = "AdventureWorks_MergePub";             ma.DatabaseName = "AdventureWorks";             ma.ConnectionContext = sc;             ma.SourceObjectName = "Vendor";             ma.SourceObjectOwner = "Purchasing";             ma.Create(  );             sc.Disconnect(  );             Console.WriteLine("Press any key to continue.");             Console.ReadKey(  );         }     } 

After running the code, you can see the publication by right-clicking the Replication Local Publications [AdventureWorks]: AdventureWorks_MergePub node in Object Explorer and selecting Properties from the context menu. Select the Articles page in the Publication Properties dialog box to view the articles to publish, as shown in Figure 19-7.

Figure 19-7. Results for creating an article example

The RMO classes used to manage publications are described in Table 19-5.

Table 19-5. RMO classes for managing articles

Class	Description
ArticleConflict	Represents information about the merge replication conflict table. The EnumConflictTables( ) method of the ReplicationDatabase object returns an ArrayList object of conflict information for all merge publications and subscriptions.
DistributionArticle	Represents read-only information about the distributor image of a snapshot, transactional, or merge article.
DistributionArticleCollection	Represents a collection of DistributionArticle objects. The DistributionArticles property of the DistributionPublication class returns a DistributionArticleCollection object containing all distribution articles defined on the distribution publication.
MergeArticle	Represents an article in a merge publication. The EnumArticles( ) method of the MergePublication class returns an ArrayList object of articles in the publication.
MergeArticleCollection	Represents a collection of MergeArticle objects. The MergeArticles property of the MergePublication class returns a MergeArticleCollection object containing all articles in the merge publication.
MergeJoinFilter	Represents a join filter or logical record relationship between merge articles. The EnumMergeJoinFilters( ) method of the MergeArticle class returns an ArrayList object of join filters defined for the merge article.
transArticle	Represents an article in either a transactional or snapshot publication. The EnumArticles( ) method of the TRansPublication class returns an ArrayList object of articles in the publication.
TRansArticleCollection	Represents a collection of transArticle objects. The transArticles property of the transPublication class returns a transArticleCollection object containing all articles in the transactional or snapshot publication.

19.1.7. Enumerating Items Available for Replication

This example enumerates the tables and columns available for replication in the AdventureWorks database. It does so using the EnumReplicationTables( ) method on the ReplicationDatabase class. This method returns an ArrayList object of ReplicationTable objects. The example then scans this ArrayList object and calls the EnumReplicationColumns( ) method for each ReplicationTable object. For each column reported, the example displays the column's name and data type.

The source code for the example follows:

     using System;     using System.Collections;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Replication;     class Program     {         static void Main(string[] args)         {             ServerConnection sc = new ServerConnection("localhost");             ReplicationDatabase rDb = new ReplicationDatabase(                 "AdventureWorks", sc);             ArrayList ta = rDb.EnumReplicationTables(  );             for (int i = 0; i < ta.Count; i++)             {                 ReplicationTable t = (ReplicationTable)ta[i];                 Console.WriteLine(t.OwnerName + "." + t.Name);                 ArrayList ca = t.EnumReplicationColumns(  );                 for (int j = 0; j < ca.Count; j++)                 {                     ReplicationColumn c = (ReplicationColumn)ca[j];                     Console.WriteLine("  " + c.Name + " " + c.Datatype);                 }                 Console.WriteLine(Environment.NewLine);             }             sc.Disconnect(  );             Console.WriteLine("Press any key to continue.");             Console.ReadKey(  );         }     } 

Partial results are show in Figure 19-8.

Figure 19-8. Results for enumerating items available for replication example

The RMO classes used to manage replication items are described in Table 19-6.

Table 19-6. RMO classes for managing objects being replicated

Class	Description
HeterogeneousColumn	Represents a column in a table on a non-SQL Server publisher. The EnumHeterogeneouscolumns( ) method of the ReplicationServer class returns an ArrayList object of heterogeneous columns in a table that can be replicated.
HeterogeneousTable	Represents a table on a non-SQL Server publisher. The EnumHeterogeneousTables() method of the ReplicationServer class returns an ArrayList object of heterogeneous tables that can be replicated.
IdentityRangeInfo	Represents identity range management settings for a published article when the source table contains an identity column. The EnumIdentityRangeInfo() method of the ReplicationTable class returns an ArrayList object of identity range information about articles based on the table.
ReplicationColumn	Represents information about a column (in a table) required for replication. The EnumReplicationColumns( ) method of the ReplicationTable class returns an ArrayList object of columns that can be replicated.
ReplicationDatabase	Represents a replication database, either publication or subscription.
ReplicationDatabaseCollection	Represents a collection of replication databases. The EnumReplicationDatabases() method of the ReplicationServer class returns an ArrayList object of all databases enabled for replication on the replication server.
ReplicationSchemaBoundView	Represents information about a schema-bound view required for replication. The EnumReplicationSchemaBoundViews( ) method of the ReplicationDatabase class returns an ArrayList object of schema-bound views that can be replicated.
ReplicationStoredProcedure	Represents information about a stored procedure required for replication. The EnumReplicationStoredProcedures( ) method of the ReplicationDatabase class returns an ArrayList object of stored procedures that can be replicated.
ReplicationTable	Represents information about a table required for replication. The EnumReplicationTables() method of the ReplicationDatabase class returns an ArrayList object of tables that can be replicated.
ReplicationUserDefinedAggregate	Represents information about a user-defined aggregate required for replication. The EnumReplicationUserDefinedAggregates( ) method of the ReplicationDatabase class returns an ArrayList object of user-defined aggregates that can be replicated.
ReplicationUserDefinedFunction	Represents information about a user-defined function required for replication. The EnumReplicationUserDefinedFunctions( ) method of the ReplicationDatabase class returns an ArrayList object of user-defined functions that can be replicated.
ReplicationView	Represents information about a user-defined view required for replication. The EnumReplicationViews( ) method of the ReplicationDatabase class returns an ArrayList object of views that can be replicated.

19.1.8. Filtering an Article

This example partitions the article created in the "Creating an Article" section earlier in this chapter both horizontally (row-based) and vertically (column-based). It does so by using the MergeArticle class, which exposes one property and two methods of interest.

The FilterClause property of the MergeArticle class defines subsets of rows that are available for the article, similar to horizontally partitioning the data. The syntax of the filter clause follows that of a T-SQL WHERE clause without the word WHERE. In this example, the full WHERE clause is WHERE CreditRating = 1 AND PreferredVendorStatus = 'true'. Only records matching this criterion will be published.

The AddReplicatedColumns( ) and RemoveReplicatedColumns( ) methods add columns to and remove columns from the article, similar to vertically partitioning the data. Only columns that are nullable or defined with a default value can be removed from a vertical partition. This example removes the PurchasingWebServiceURL column from the article.

The source code for the example follows:

     using System;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Replication;     class Program     {         static void Main(string[] args)         {             ServerConnection sc = new ServerConnection("localhost");             ReplicationDatabase rDb = new ReplicationDatabase(                 "AdventureWorks", sc);             MergeArticle ma = rDb.MergePublications["AdventureWorks_MergePub"].                 MergeArticles  ["Article_1"];             ma.FilterClause = "CreditRating = 1 AND PreferredVendorStatus = 'true'";             ma.RemoveReplicatedColumns(new string[] {"PurchasingWebServiceURL"});             sc.Disconnect(  );             Console.WriteLine("Press any key to continue.");             Console.ReadKey(  );         }     } 

You can examine the article by right-clicking the Replication Local Publications [AdventureWorks]: AdventureWorks_MergePub node in Object Explorer and selecting Properties from the context menu. Then select the Articles page to see that the PurchasingWebServiceURL column has been removed from the article, as shown in Figure 19-9.

Figure 19-9. Publication Properties dialog box

You can examine the filter you added by selecting the Filter Rows page in the Publication Properties dialog box, selecting the Vendor (Purchasing) filtered tables, and clicking the Edit button to open the Edit Filter dialog box, as shown in Figure 19-10.

19.1.9. Registering a Subscriber

This example creates a subscriber named Subscriber_1. It does so by using the RegisteredSubscriber class in a very simple manner:

1. It instantiates the RegisteredSubscriber object, associating it with the ServerConnection object that represents the target SQL Server.

2. It calls its Create( ) method.

The source code for the example follows:

     using System;     using Microsoft.SqlServer.Management.Common; 

Figure 19-10. Edit Filter dialog box

     using Microsoft.SqlServer.Replication;     class Program     {         static void Main(string[] args)         {             ServerConnection sc = new ServerConnection("localhost");             RegisteredSubscriber rs = new RegisteredSubscriber("Subscriber_1", sc);             rs.Create(  );             sc.Disconnect(  );             Console.WriteLine("Press any key to continue.");             Console.ReadKey(  );         }     } 

The sp_helpsubscriberinfo system stored procedure returns information about registered subscribers.

The RMO classes used to manage subscribers are provided for backward compatibility, as it is no longer necessary to explicitly register a subscriber at the publisher in SQL Server 2005. These classes are described in Table 19-7.

Table 19-7. RMO classes for managing subscribers

Class	Description
RegisteredSubscriber	Represents a subscriber registered at a publisher or distributor. The EnumRegisteredSubscribers( ) method of the DistributionPublisher and ReplicationServer classes returns an ArrayList object of registered subscribers.
RegisteredSubscriberCollection	The RegisteredSubscribers property of the DistributionPublisher and ReplicationServer classes returns a RegisteredSubscriberCollection object containing all registered subscribers.

19.1.10. Creating a Subscription

This example creates a pull merge subscription for the publication AdventureWorks_MergePub created in the section "Creating a Publication," earlier in this chapter. It does so in the usual manner, but with one additional method call:

1. It instantiates a MergePullSubscription object.

2. It sets properties as appropriate.

3. It calls the object's Create( ) method.

4. It calls its Refresh( ) method.

The properties that the example sets are as follows:

ConnectionContext

The target SQL Server instance.

DatabaseName

The name of the subscription database.

PublisherName

The name associated with the DistributionPublisher object when it was created.

PublicationDBName

The name the publisher has assigned to the database.

PublicationName

The name assigned to the publication.

SubscriberType

A value from the MergeSubscriberType enumeration.

CreateSyncAgentByDefault

Setting this property of the MergePullSubscription class creates the agent job used to synchronize the subscription.

There are two additional steps you must perform:

1. Configure the MergePublication object so that it allows pull.

2. Register the merge pull subscription at the publisher.

These are the steps to configure the MergePublication object (AdventureWorks_MergePub) to allow pull, and to register the subscription:

1. Instantiate a MergePublication object and associate it with AdventureWorks_MergePub.

2. Load the publication's properties.

3. Make sure the Attributes property indicates that the publication supports pull.

4. If the publication does not support pull, change the Attributes, and then call CommitPropertyChanges( ) and Refresh( ) on the MergePublication object.

5. Call the MakePullSubscriptionWellKnown( ) method on the MergePublication object.

Before you execute this example, replace the ServerName argument used to set the MergePullSubscription.PublisherName property and used in the MergePublication.MakePullSubscription( ) method with the name of your database server.

The source code for the example follows:

     using System;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Replication;     class Program     {         static void Main(string[] args)         {             ServerConnection sc = new ServerConnection("localhost");             // create the pull subscription             MergePullSubscription mps = new MergePullSubscription(  );             mps.ConnectionContext = sc;             mps.PublisherName = "ServerName";             mps.PublicationDBName = "AdventureWorks";             mps.PublicationName = "AdventureWorks_MergePub";             mps.DatabaseName = "ReplicationDestination";             mps.SubscriberType = MergeSubscriberType.Local;             mps.CreateSyncAgentByDefault = true;             mps.Create(  );             MergePublication mp = new MergePublication(                 "AdventureWorks_MergePub", "AdventureWorks", sc);             mp.LoadProperties(  );             // allow pull if not already allowed             if ((mp.Attributes & PublicationAttributes.AllowPull) == 0)             {                 mp.Attributes = mp.Attributes | PublicationAttributes.AllowPull;                 mp.CommitPropertyChanges(  );                 mp.Refresh(  );             }             // register the merge pull subscription at the publisher             mp.MakePullSubscriptionWellKnown(                 "ServerName", "ReplicationDestination",                 mps.SyncType, mps.SubscriberType, mps.Priority);             sc.Disconnect(  );             Console.WriteLine("Press any key to continue.");             Console.ReadKey(  );         }     } 

To examine the subscription, refresh and expand the Replication Local Subscriptions node in Object Explorer, right-click the [ReplicationDestination] - [ServerName].[AdventureWorks]: AdventureWorks_MergePub local subscription, and select Properties from the context menu to display the Subscription Properties dialog box. Figure 19-11 shows the details of the new subscription.

Setting the CreateSyncAgentByDefault property of the MergePullSubscription class creates the agent job used to synchronize the subscription. Open the SQL Server Agent jobs by selecting SQL Server Agent Jobs and you will see that a new merge replication job has been created.

The RMO classes used to manage subscriptions are described in Table 19-8.

Table 19-8. RMO classes for managing subscriptions

Class	Description
DistributionSubscription	Represents read-only information about the distributor image of a snapshot or transactional subscription. Use this class to create a subscription to a heterogeneous publication.
DistributionSubscriptionCollection	Represents a collection of DistributionSubscription objects. The DistributionSubscriptions property of the DistributionPublication class returns a DistributionPublicationCollection object containing all distribution subscriptions defined on the distribution publication.
LastValidationDateTime	Represents the date and time of the last merge subscription validation.
MergePullSubscription	Represents a pull subscription to a merge publication. The EnumMergePullSubscriptions property of the ReplicationDatabase class returns an ArrayList object of all merge pull subscriptions that use the database.
MergePullSubscriptionCollection	Represents a collection of MergePullSubscription objects. The MergePullSubscriptions property of the ReplicationDatabase class returns a MergePullSubscriptionCollection object containing all merge pull subscriptions defined on the replication database.
MergeSubscription	Represents a subscription to merge publication.
MergeSubscriptionCollection	Represents a collection of MergeSubscription objects. The MergeSubscriptions property of the ReplicationDatabase class returns a MergeSubscriptionCollection object containing all merge push subscriptions defined on the replication database.
SubscriptionBackupInformation	Represents information for backup devices used for setting an "initial from backup" subscription.
SubscriberSubscription	Represents a lightweight object of limited subscription information on the subscribing server.
transPullSubscription	Represents a pull subscription to a transactional or snapshot publication. The EnumTRansPullSubscriptions property of the ReplicationDatabase class returns an ArrayList object of all merge pull subscriptions that use the database.
transPullSubscriptionCollection	Represents a collection of transPullSubscription objects. The transPullSubscriptions property of the ReplicationDatabase class returns a transPullSubscriptionCollection object containing all transactional and snapshot pull subscriptions defined on the replication database.
transSubscription	Represents a subscription to a transactional or snapshot publication.
transSubscriptionCollection	Represents a collection of TRansSubscription objects. The TRansSubscriptions property of the ReplicationDatabase class returns a transSubscriptionCollection object containing all transactional or snapshot subscriptions defined on the replication database.

19.1.11. Generating the Initial Snapshot

This example generates the initial snapshot used to initialize the subscriber for a new subscription. It uses the SnapshotGenerationAgent class, which represents the Snapshot Agent. It creates an instance of this class, and then sets the following properties:

Publisher

The name given to the distribution publisher

PublisherDatabase

The name of the database being published for replication

Publication

The name of the publication

Figure 19-11. Subscription Properties dialog box

Distributor

The name of the distributor (in this case, the same as Publisher)

PublisherSecurityMode

A value from the SecurityMode enumeration (SecurityMode.Integrated in this case)

DistributorSecurityMode

A value from the SecurityMode enumeration (SecurityMode.Integrated in this case)

ReplicationType

A value from the ReplicationType enumeration (ReplicationType.Merge in this case)

The last step is to call the GenerateSnapshot( ) method of the SnapshotGenerationAgent. In this case, GenerateSnapshot( ) runs the Snapshot Agent synchronously to generate the initial snapshot for the merge publication named AdventureWorks_MergePub.

You need to add a reference to the Microsoft.SqlServer.Replication.dll assembly, installed by default in the C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies directory, to compile and execute this example. Before you execute this example, replace the ServerName argument used to set the SnapshotGenerationAgent.Publisher and SnapshotGenerationAgent.Distributor properties with the name of your database server.

The source code for the example follows:

     using System;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Replication;     class Program     {         static void Main(string[] args)         {             SnapshotGenerationAgent sga = new SnapshotGenerationAgent(  );             sga.Publisher = "ServerName";             sga.PublisherDatabase = "AdventureWorks";             sga.Publication = "AdventureWorks_MergePub";             sga.Distributor = "ServerName";             sga.PublisherSecurityMode = SecurityMode.Integrated;             sga.DistributorSecurityMode = SecurityMode.Integrated;             sga.ReplicationType = ReplicationType.Merge;             sga.GenerateSnapshot(  );             Console.WriteLine(Environment.NewLine + "Press any key to continue.");             Console.ReadKey(  );         }     } 

Partial results are shown in Figure 19-12.

The StartSnapshotGenerationAgentJob( ) method of the MergePublication and transPublication classes generates a snapshot asynchronously.

19.1.12. Synchronizing a Subscription to an Initial Snapshot

This example uses the snapshot created in the preceding section, "Generating the Initial Snapshot," to initialize the subscriber when the data is first synchronized.

Before you execute this example, replace the ServerName argument used to set the MergePullSubscription.PublisherName property with the name of your database server.

Figure 19-12. Partial results for generating initial snapshot example

The source code for the example follows:

     using System;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Replication;     class Program     {         static void Main(string[] args)         {             ServerConnection sc = new ServerConnection("localhost");             MergePullSubscription mps = new MergePullSubscription(  );             mps.ConnectionContext = sc;             mps.DatabaseName = "ReplicationDestination";             mps.PublisherName = "ServerName";             mps.PublicationDBName = "AdventureWorks";             mps.PublicationName = "AdventureWorks_MergePub";             mps.LoadProperties(  );             mps.SynchronizeWithJob(  );             sc.Disconnect(  );             Console.WriteLine("Press any key to continue.");             Console.ReadKey(  );         }     } 

After you run this code, the table named Vendor is created in the subscriber database ReplicationDestination.

The SynchronizeWithJob( ) method of the MergeSubscription, MergePullSubscription, transSubscription, and TRansPullSubscription classes starts the Merge Agent job to synchronize the subscription. The snapshot is transferred to and applied to the subscriber when the subscription is first synchronized.

If you select View Synchronization Status from the context menu for the local subscription, the status of the last synchronization indicates something similar to the following:

     Applied the snapshot and merged 0 data change(s) (0 insert(s), 0 update(s),     0 delete(s), 0 conflict(s)). 

If you run the code a second time, the snapshot is not applied and the status of the last synchronization indicates something similar to this:

     Merge completed with no data changes processed. 

Replication allows multiple nodes to make data changes, so it is possible that changes made at one node may conflict with changes made at another.

The RMO classes used to manage merge and transactional replication conflict information are described in Table 19-9.

Table 19-9. RMO classes for managing replication conflicts

Class	Description
MergeConflictCount	Represents conflict count information for a table article in merge replication. The EnumMergeConflictCounts( ) method of the ReplicationDatabase class returns an ArrayList object of conflicts in a merge publication or subscription database.
transConflictCount	Represents conflict count information for a table article in transactional replication. The EnumtransConflictCounts( ) method of the ReplicationDatabase class returns an ArrayList object of conflicts in a transactional publication or subscription database.

19.1.13. Retrieving Agent History

This example displays status information about the last synchronization job that was run. It uses the LastAgentJobHistoryInfo( ) method of the MergePullSubscription class, which returns this information as an AgentJobHistoryInfo object. This class represents the results from the last run of the replication agent. It then shows the LastRunDateTime and Status properties of this object.

Before you execute this example, replace the ServerName argument used to set the MergePullSubscription.PublisherName property with the name of your database server.

The source code for the example follows:

     using System;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Replication;     class Program     {         static void Main(string[] args)         {             ServerConnection sc = new ServerConnection("localhost");             MergePullSubscription mps = new MergePullSubscription(  );             mps.ConnectionContext = sc;             mps.DatabaseName = "ReplicationDestination";             mps.PublisherName = "ServerName";             mps.PublicationDBName = "AdventureWorks";             mps.PublicationName = "AdventureWorks_MergePub";             mps.LoadProperties(  );             AgentJobHistoryInfo ajhi = mps.LastAgentJobHistoryInfo(  );             Console.WriteLine("Last Run Date/Time: " + ajhi.LastRunDateTime);             Console.WriteLine("Status:             " + ajhi.Status);             sc.Disconnect(  );             Console.WriteLine(Environment.NewLine + "Press any key to continue.");             Console.ReadKey(  );         }     } 

Results are shown in Figure 19-13.

Figure 19-13. Results for retrieving agent history example

The RMO classes used to manage agents are described in Table 19-10.

Table 19-10. RMO classes for managing agents

Class	Description
AgentJobHistoryInfo	Represents the results from the last run of the replication agent. The LastAgentJobHistoryInfo property of the MergePullSubscription and transPullSubscription classes returns an AgentJobHistoryInfo object with information about the last synchronization agent job that was run.
AgentProfile	Represents a replication agent profile. The EnumAgentProfiles( ) method of the ReplicationServer class returns an ArrayList object of all replication agent profiles supported on the server. Replication agent profiles define default values when agent jobs get created.
AgentProfileParameter	Represents a parameter in a replication agent profile. The EnumParameter( ) method of the AgentProfile class returns an ArrayList object of information about parameters for the replication agent profile.
AgentProfileParameterInfo	Represents information about a replication agent parameter. The EnumParameter( ) method of the AgentProfile class returns an ArrayList object of information about parameters for the replication agent profile.

19.1.14. Specifying a Replication Schedule

This example sets the subscription created in the "Creating a Subscription" section to pull replication data from the publication every five minutes. It does so by setting several properties of the MergePullSubscription object:

AgentSchedule.FrequencyType

Takes a value from the ScheduleFrequencyType enumeration (ScheduleFrequency-Type.Daily in this case).

AgentSchedule.FrequencySubDay

Takes a value from the ScheduleFrequencySubDay enumeration (Schedule-FrequencySubDay.Minute in this case).

AgentSchedule.FrequencySubDayInterval

Specifies the number of units of the AgentSchedule.FrequencySubDay. This example sets replication to occur every one hour.

The last step is to call the CommitPropertyChanges( ) method on the MergePull-Subscription object.

Before you execute this example, replace the ServerName argument used to set the MergePullSubscription.PublisherName property with the name of your database server.

The source code for the example follows:

     using System;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Replication;     class Program     {         static void Main(string[] args)         {             ServerConnection sc = new ServerConnection("localhost");             MergePullSubscription mps = new MergePullSubscription(  );             mps.ConnectionContext = sc;             mps.DatabaseName = "ReplicationDestination";             mps.PublisherName = "ServerName";             mps.PublicationDBName = "AdventureWorks";             mps.PublicationName = "AdventureWorks_MergePub";             mps.LoadProperties(  );             mps.AgentSchedule.FrequencyType = ScheduleFrequencyType.Daily;             mps.AgentSchedule.FrequencySubDay = ScheduleFrequencySubDay.Hour;             mps.AgentSchedule.FrequencySubDayInterval = 1;             mps.CommitPropertyChanges(  );             sc.Disconnect(  );             Console.WriteLine("Press any key to continue.");             Console.ReadKey(  );         }     } 

After you execute the example, confirm the new job schedule in Object Explorer by refreshing SQL Server Agent Jobs, right-clicking the ServerName-AdventureWorks-AdventureWorks_MergePub-ServerName-ReplicationDestination-0 job, and selecting Properties from the context menu to open the Job Properties dialog box, shown in Figure 19-14.

Figure 19-14. Job Properties dialog box

Select the Schedules page and then click the Edit button to display the Job Schedules Properties dialog box, shown in Figure 19-15.

Figure 19-15. Job Schedule Properties dialog box

The RMO classes used to manage replication agents are described in Table 19-11.

Table 19-11. RMO classes for managing replication agents

Class	Description
ReplicationAgentSchedule	Represents the schedule for a replication agent job. The AgentSchedule property of the MergePullSubscription and transPullSubscription classes returns an AgentSchedule object.
ReplicationStatusAndWarning	Represents replication agent status information and threshold monitor warnings.

19.1.15. Validating Subscriber Data

This example validates the subscription to the AdventureWorks_MergePub publication created in the earlier "Creating a Publication" section. It first calls the Validate-Subscription( ) method of the MergePublication class, which marks the subscription for validation in the next synchronization. It then forces synchronization by calling MergePullSubscription.SynchronizeWithJob( ).

Before you execute this example, replace the ServerName argument used in the MergePublication.ValidateSubscription( ) constructor and to set the MergePullSubscription.PublisherName property with the name of your database server.

The source code for the example follows:

     using System;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Replication;     class Program     {         static void Main(string[] args)         {             ServerConnection sc = new ServerConnection("localhost");             // mark the subscription for validation             MergePublication mp = new MergePublication(  );             mp.ConnectionContext = sc;             mp.Name = "AdventureWorks_MergePub";             mp.DatabaseName = "AdventureWorks";             mp.LoadProperties(  );             mp.ValidateSubscription("ServerName", "ReplicationDestination",                 ValidationOption.Checksum80);             // synchronize the subscription.             MergePullSubscription mps = new MergePullSubscription(  );             mps.ConnectionContext = sc;             mps.DatabaseName = "ReplicationDestination";             mps.PublisherName = "ServerName";             mps.PublicationDBName = "AdventureWorks";             mps.PublicationName = "AdventureWorks_MergePub";             mps.LoadProperties(  );             mps.SynchronizeWithJob(  );             sc.Disconnect(  );             Console.WriteLine("Press any key to continue.");             Console.ReadKey(  );         }     } 

The ValidateSubscription( ) method of the MergePublication class marks the subscription for validation in the next synchronization. To view the subscription, refresh and expand the Replication Local Subscriptions node in Object Explorer, right-click the [ReplicationDestination] - [ServerName].[AdventureWorks]: AdventureWorks_MergePub local subscription, and select View Job History from the context menu to display the Log File Viewer dialog box . The results of the validation appear in the details for the job in the bottom pane, as shown in Figure 19-16.

Figure 19-16. Log File Viewer dialog box

19.1.16. Monitoring Replication

This example displays summary and detailed merge session information using three classes:

MergeSubscriberMonitor

Represents server-side monitoring of merge publication subscriptions.

MergeSessionSummary

Represents Merge Agent session information. An array of MergeSessionSummary objects is returned by a call to the MergeSubscriberMonitor.GetSessionsSummary() method.

MergeSessionDetail

Represents information about a step in a Merge Agent session. An array of MergeSessionDetail objects is returned by a call to the MergeSubscriberMonitor.GetSessionDetails( ) method.

The example shows the StartTime, Duration, and Status properties of each MergeSessionSummary object. It also obtains details about each step by calling MergeSubscriberMonitor.GetSessionDetails( ), and displays the DetailType and Message properties.

Before you execute this example, replace the ServerName argument used to set the MergeSubscriberMonitor.Publisher property with the name of your database server.

The source code for the example follows:

     using System;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Replication;     class Program     {         static void Main(string[] args)         {             ServerConnection sc = new ServerConnection("localhost");             MergeSubscriberMonitor msm = new MergeSubscriberMonitor(sc);             msm.Publisher = "ServerName";             msm.Publication = "AdventureWorks_MergePub";             msm.PublisherDB = "AdventureWorks";             msm.SubscriberDB = "ReplicationDestination";             // display the merge session summary information             MergeSessionSummary[] mssa = msm.GetSessionsSummary(  );             foreach (MergeSessionSummary mss in mssa)             {                 Console.WriteLine(mss.StartTime + ", " + mss.Duration + ", " +                     mss.Status);                 // display the merge session detail information for the session                 MergeSessionDetail[] msda = msm.GetSessionDetails(mssa[0].SessionId);                 foreach (MergeSessionDetail msd in msda)                     Console.WriteLine("  " + msd.DetailType + ": " + msd.Message);                 Console.WriteLine(  );             }             sc.Disconnect(  );             Console.WriteLine(Environment.NewLine + "Press any key to continue.");             Console.ReadKey(  );         }     } 

Partial results are shown in Figure 19-17.

Figure 19-17. Results for monitoring replication example

The RMO classes used to manage monitors and access merge session information are described in Tables 19-12 and 19-13.

Table 19-12. RMO classes for managing monitors

Class	Description
MergeSubscriberMonitor	Represents server-side monitoring of subscriptions to merge publications.
MonitorThreshold	Represents a threshold metric used to monitor a publication. The EnumMonitorThresholds( ) method of the PublicationMonitor class returns an ArrayList object of monitor thresholds defined for the publication.
PendingCommandInfo	Represents information about pending commands for a subscription to a transactional publication. The transPendingCommandInfo property of the PublicationMonitor class returns information about pending commands for a subscription.
PublicationMonitor	Represents publisher-side monitoring of a publication.
PublicationMonitorCollection	The PublicationMonitors property of the PublisherMonitor class returns a PublicationMonitorCollection object containing information about monitors defined for the publication.
PublisherMonitor	Represents distributor-side monitoring of a publisher.
PublisherMonitorCollection	The PublisherMonitors property of the ReplicationMonitor class returns a PublisherMonitorCollection object containing information about monitors used to monitor publishers.
ReplicationMonitor	Represents a monitor for a replication server.
TRacerToken	Represents tracer token information. The EnumtracerTokens( ) method of the PublicationMonitor class returns an ArrayList object of tracer tokens that have been inserted into the monitored transactional publication.

Table 19-13. RMO classes for managing merge session information

Class	Description
MergeSessionDetail	Represents information about a step in a Merge Agent session. The GetSessionDetails() method of the MergeSubscriberMonitor class returns an array of MergeSessionDetail objects containing detailed Merge Agent information.
MergeSessionError	Represents information about errors during a Merge Agent session. The Errors property of the MergeSessionSummary class returns a MergeSessionError object.
MergeSessionSummary	Represents Merge Agent session information.

19.1.17. Business Logic Handlers

You can execute business logic in managed code assemblies during the merge synchronization process to provide custom handling for conditions during synchronization, such as data changes, conflicts, and errors. These assemblies are called business logic handlers . You can use COM-based resolverseither custom or those supplied with SQL Server 2005for the same purpose.

The RMO classes used to manage business logic handlers and COM-based resolvers are described in Table 19-14.

Table 19-14. RMO classes for managing business logic handlers and COM-based resolvers

Class	Description
BusinessLogicHandler	Represents server registration for the managed code assembly implementing a business logic handler. The EnumBusinessLogicHandlers( ) method of the ReplicationServer class returns an ArrayList object of business logic handlers registered at the server.
CustomResolver	Represents a COM-based resolver registration at a server used for merge replication. The EnumCustomResolvers( ) method of the ReplicationServer class returns an ArrayList object of custom conflict resolvers registered on the SQL Server instance.

For more information about implementing custom business logic using business logic handlers or COM-based resolvers, see Microsoft SQL Server 2005 Books Online.