Programming Replication Management Objects RMO

Eric Burgess | Microsoft SQL Server Escalation Services

You can programmatically setup and configure Merge Replication using Replication Management Objects (RMO).

How to: Create a Pull Subscription (RMO Programming)
http://msdn.microsoft.com/en-us/library/ms147314.aspx

Replication in SQL Server Express
http://msdn.microsoft.com/en-us/library/ms165700(SQL.90).aspx

SQL Server Express does not include the SQL Server Agent, which is typically used to run replication agents. If you use push subscription, replication agents run at the Distributor, which will be an instance of SQL Server 2005, so there are options for synchronizing. But if you use a pull subscription, in which agents run at the Subscriber, you must synchronize the subscription by using Windows Synchronization Manager or RMO.

Here is a working example (you will need to fill in the names of your server, databases, etc) in C# that will create a WebSync subscription, Register it with the publication, and apply the initial snapshot from an AltSnapShot location via RMO C# code. You can just create a Console C# Application and use this code. This sample also turns on Merge Agent logging so that you can trouble shoot problems during the initial synchronization.

<CODE>

using System;
using System.Collections.Generic;
using System.Text;

// These namespaces are required for RMO.
using Microsoft.SqlServer.Replication;
using Microsoft.SqlServer.Management.Common;

// Make sure to reference the following .NET objects via the Project … Add Reference option.
//      1. Microsoft.SqlServer.Replication .NET Programming Interface
//      2. Microsoft.SqlServer.ConnectionInfo
//      3. Replication Agent Library

namespace WebSyncPullSubscription
{
      class Program
{
           static void Main(string[] args)
{

           // Define the Publisher, publication, and databases.
// WebSync_Sub_RMO database must already be created on the subscriber.
           string publicationName = “WebSync_Pub”;
           string publisherName = “PubSQLServerName”;
           string subscriberName = “SubSQLServerName”;
           string subscriptionDbName = “WebSync_Sub_RMO”;
           string publicationDbName = “WebSync_Pub”;
           string hostname = @”ISSServerName”;
           string webSyncUrl = “https://” + “IISServerName” + “/WebSync/replisapi.dll”;

//Create connections to the Publisher and Subscriber.
          ServerConnection subscriberConn = new ServerConnection(subscriberName);
          ServerConnection publisherConn = new ServerConnection(publisherName);

// Create the objects that we need.
         MergePublication publication;
         MergePullSubscription subscription;

         try

          {

          // Connect to the Subscriber.
subscriberConn.Connect();

          // Ensure that the publication exists and that
// it supports pull subscriptions and Web synchronization.
publication = new MergePublication();
          publication.Name = publicationName;
          publication.DatabaseName = publicationDbName;
          publication.ConnectionContext = publisherConn;

          if (publication.LoadProperties())
               {
                    if ((publication.Attributes & PublicationAttributes.AllowPull) == 0)
                    {
                         publication.Attributes |= PublicationAttributes.AllowPull;
                    }

          if ((publication.Attributes & PublicationAttributes.AllowWebSynchronization) == 0)
                    {
                          publication.Attributes |= PublicationAttributes.AllowWebSynchronization;
                  }

          // Define the pull subscription.
subscription = new MergePullSubscription();
          subscription.ConnectionContext = subscriberConn;
          subscription.PublisherName = publisherName;
          subscription.PublicationName = publicationName;
          subscription.PublicationDBName = publicationDbName;
          subscription.DatabaseName = subscriptionDbName;
          subscription.HostName = hostname;

          // Specify the Windows login credentials for the Merge Agent job.
subscription.SynchronizationAgentProcessSecurity.Login = “northamerica\\UserName”;
          subscription.SynchronizationAgentProcessSecurity.Password = “UserPassword”

          // Enable Web synchronization.
subscription.UseWebSynchronization = true;
          subscription.InternetUrl = webSyncUrl;

          // Specify the same Windows credentials to use when connecting to the
// Web server using HTTPS Basic Authentication.
subscription.InternetSecurityMode = AuthenticationMethod.BasicAuthentication;
          subscription.InternetLogin = “Northamerica\\UserName”;
          subscription.InternetPassword = “UserPassword”;

          // Ensure that we create a job for this subscription.
subscription.CreateSyncAgentByDefault = true;

          // Create the pull subscription at the Subscriber.
// Snapshot is not synchronized at this point in time.
subscription.Create();

          Boolean registered = false;

          // Verify that the subscription is not already registered.
// Register the Subscription
foreach (MergeSubscription existing
          in publication.EnumSubscriptions())
          {
               if (existing.SubscriberName == subscriberName
               && existing.SubscriptionDBName == subscriptionDbName
               && existing.SubscriptionType == SubscriptionOption.Pull)
               {
               registered = true;
               }
          }

          if (!registered)
          {
               // Register the local subscription with the Publisher.
// This doesn’t apply the snapshot
publication.MakePullSubscriptionWellKnown(
               subscriberName, subscriptionDbName,
               SubscriptionSyncType.Automatic,
               MergeSubscriberType.Local, 0);

               // Applying a SnapShot
// Synch the database.
MergeSynchronizationAgent syncAgent = subscription.SynchronizationAgent;

               // Configure Alternate snapshot folder. Notice we only put the root of the
// Folder name (you could use a UNC path here too). The Publisher will provide
// the rest of the path for you. For instance, my final path looked like this:
//   C:\BARRAGE2_WEBSYNC_PUB_WEBSYNC_PUB\unc\BARRAGE2_WEBSYNC_PUB_WEBSYNC_PUB\20081002152888\
syncAgent.AltSnapshotFolder = “C:\\BARRAGE2_WEBSYNC_PUB_WEBSYNC_PUB”;

               // Setup Verbose MergeAgent Logging.
syncAgent.OutputVerboseLevel = 4;
               syncAgent.Output = “C:\\WebSyncMergeRMO.out”;

               // Sync
syncAgent.Synchronize();
          }
          }

               else
               {
               // Do something here if the publication does not exist.
throw new ApplicationException(String.Format(
               “The publication ‘{0}’ does not exist on {1}.”,
               publicationName, publisherName));
               }
          }

          catch (Exception ex)
          {
               // Implement the appropriate error handling here.
throw new ApplicationException(String.Format(
               “The subscription to {0} could not be created.”, publicationName), ex);
          }

          finally
          {
               subscriberConn.Disconnect();
               publisherConn.Disconnect();
          }
       }
    }
}

</CODE>