SQL Server 2008 Express impossible to use for merge replication?

Question

According to Microsoft, SQL Server 2008 Express should be able to participate in merge replication, as a pull subscriber. At least with the RMO-objects.

• http://msdn.microsoft.com/en-us/library/ms147890.aspx
• http://msdn.microsoft.com/en-us/library/ms151819.aspx

But other variants should also be possible to use.

However, we are not able to start the SQL Server Agent on the clients (running SQL Server 2008 Express). This seems to be a common problem, and as far as I can find, unsolved.

Am I right in thinking that merge (pull) subscriptions in reality is not supported with SQL Server 2008 Express? Has anyone successfully used this technology? I think it's pretty serious that Microsoft are claiming this to work fine, although it's not at all.

I'm hoping that someone has any experience with this!

Answer 1

I was able to synchronize a Merge pull subscription on SQL Server 2008 R2 Express - without the SQL Server Agent - via RMO with the following bit of C#:

static void SynchronizeMergePullSubscriptionViaRMO()
{
        // Define the server, publication, and database names.
        string subscriberName = "WIN8CP\\SQLEXPRESS";
        string publisherName = "WS2008R2_1";
        string distributorName = "WS2008R2_1";
        string publicationName = "TestMergePub2";
        string subscriptionDbName = "TestSubDB1";
        string publicationDbName = "AdventureWorksLT";

        // Create a connection to the Subscriber.
        ServerConnection conn = new ServerConnection(subscriberName);

        MergePullSubscription subscription;
        MergeSynchronizationAgent agent;

        try
        {
            // Connect to the Subscriber.
            conn.Connect();

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

            // If the pull subscription exists, then start the synchronization.
            if (subscription.LoadProperties())
            {
                // Get the agent for the subscription.
                agent = subscription.SynchronizationAgent;

                // Set the required properties that could not be returned
                // from the MSsubscription_properties table.
                agent.PublisherSecurityMode = SecurityMode.Integrated;
                agent.DistributorSecurityMode = SecurityMode.Integrated;
                agent.Distributor = publisherName;

                // Enable agent output to the console.
                agent.OutputVerboseLevel = 4;
                agent.Output = "C:\\TEMP\\mergeagent.log";

                // Synchronously start the Merge Agent for the subscription.
                agent.Synchronize();
            }
            else
            {
                // Do something here if the pull subscription does not exist.
                throw new ApplicationException(String.Format(
                    "A subscription to '{0}' does not exist on {1}",
                    publicationName, subscriberName));
            }
        }
        catch (Exception ex)
        {
            // Implement appropriate error handling here.
            throw new ApplicationException("The subscription could not be " +
                "synchronized. Verify that the subscription has " +
                "been defined correctly.", ex);
        }
        finally
        {
            conn.Disconnect();
        }
}

A code sample can be downloaded from the MSDN Code Gallery.

Answer 2

Windows Synchronization Center WIN7:

Assuming this replication is to be run on a 'budget server' (e.g a client OS such as Win7) for a one-man remote branch office - then this will work quite well. It has a nice GUI, gives a blow by blow messagebox during sync and can do some advanced scheduling.

CAVEATS:

WORKS WITH: Windows authentication NOT WITH: SQL authentication

WORKS WITH: one currently logged in user, that stays logged on at all times, provided that:

1. Username and password of that user have same credentials the replisapi.dll WEBSYNC page
2. They are the same as the windows authenticated user that has access the remote database
3. They are able to access the replication for the other permissions (basicaly point 1&2 above)
4. Sync center runs on its own internal scheduler

NB -- it's deceptive as its has been made to look like it supports SQL authentication, it doesn't really; it will work if you enter the passwords manually, but only once and every time after that when you enter the password. Thus it CANNOT use sql authentication, in a practical sense.

The reason for this is it can't save the password. Microsoft did this by design, and so far there isn't a workaround.

Answer 3

As mentioned in Replication Considerations (SQL Server Express), SQL Server Express can serve as a Subscriber in all types of Replication.

More importantly, SQL Server Express does not include the SQL Server Agent. This means that push subscriptions must be used so the replication agents are ran from the Distributor, or pull subscriptions can be used if synchronized via Windows Sync Manager, RMO, or batch scripts.

Answer 4

Another option is to execute the Merge agent (replmerg.exe) to synchronize an Express subscription using a batch script. This is covered in How to: Synchronize a Pull Subscription (Replication Programming). From here the batch script can be ran via the Task Scheduler on a specified schedule.

Last, Windows Synchronization Manager can be used to synchronize an Express Merge pull subscription: How to: Synchronize a Subscription Using Windows Synchronization Manager (Windows Synchronization Manager)