SQL Server 2008 Replication to SQL Express 2005 via web sync

Question

I have a SQL 2008 R2 server. I've created a publication using the wizard which seemed to go ok. There is a 'distribution' database in the 'system databases' section that has not a lot in it. (Not sure if this was there already or whether the "Publication Wizard" created it.) I have setup web sync and have access to relisapi.dll via IIS7 and a self signed certificate.

My setup program for my ASP.NET website installs SQL Express 2005. I have written a little site to test the creation of a subscription and initial sync. I do not "create an initial" database as I'm presuming the first sync will pull everything down from the server.

The following bit of code seems to work because a subscription is created in SQL Express & in the SQL 2008 server.

' 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
            subscription.CreateSyncAgentByDefault = True

            ' Specify the Windows login credentials for the Merge Agent job.
            subscription.SynchronizationAgentProcessSecurity.Login = winLogin
            subscription.SynchronizationAgentProcessSecurity.Password = winPassword

            ' 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 = winLogin
            subscription.InternetPassword = winPassword

            If Not subscription.LoadProperties() Then
                ' Create the pull subscription at the Subscriber.
                subscription.Create()

Then I run this bit of code:

        If Not subscription.PublisherSecurity Is Nothing Or _
               subscription.DistributorSecurity Is Nothing Then

                '0: Only error messages are logged.
                '1: All progress report messages are logged.
                '2: All progress report messages and error messages are logged.
                subscription.SynchronizationAgent.OutputVerboseLevel = 2
                subscription.SynchronizationAgent.Output = "c:\createmerge.txt"

                ' Synchronously start the Merge Agent for the subscription.
                subscription.SynchronizationAgent.Synchronize()

but the syncronize throws the error:

The subscription to publication 'My publication' could not be verified. Ensure that all Merge Agent command line parameters are specified correctly and that the subscription is correctly configured. If the Publisher no longer has information about this subscription, drop and recreate the subscription.

On the server, using "Replication Monitor" it is showing my subscription as "Unitialized".

I think one problem is that my subscription.HostName is wrong. The Microsoft examples on MSDN say

"adventure-works\garrett1"

but it isn't clear whether adventure-works is the server, instance or database and who is garrett1 (a login or something else). So what should this actually be?

Since I don't know anything about replication and I've just been following MSDN and some books, I'd apreciate some pointers as to where to go next.

Sorry this is so long!

score 1 · Answer 1 · answered Nov 26 '10 at 16:30

Based on the documentation, the setting of Subscription.Hostname isn't critical to getting the publication working - it's the value the subscriber supplies for HOST_NAME when the merge pubication is partitioned. See here for more information about filtered publications.

If you're confident that you have correctly configured your environment to support web sync, you're going to have to debug this one piece at a time.

It might be worth starting by setting up a non-web based subscription for another database on the publisher to prove that the publication is working as expected. Assuming that works, try setting up a non-web subscription on a SQL 2005 Express instance, and then move on to testing web sync.

DomBat · Accepted Answer · 2010-12-06T09:22:18.457

0

Ok, there were two reasons I was having trouble.

Firstly on my development PC, the machine had been renamed at some point, so the connection to my local subscriber SQL was wrong.

Secondly, the "self-signed certificate" on IIS7 was resolving to an internal name and not the real name that could be seen via HTTPS from the outside world. We got a test certificate for the correct domain and it worked fine!

edited Dec 06 '10 at 09:22

answered Dec 02 '10 at 11:50

DomBat

1,981
5
27
42

score 0 · Answer 3 · answered Dec 22 '10 at 16:08

Ok, heres some more info about HOST_NAME() if you're interested.

http://msdn.microsoft.com/en-us/library/ms152478%28v=SQL.110%29.aspx

Basically you use it to pass a variable to the publisher with a value of your choice (which overloads HOST_NAME function at the server). This allows you to filter rows, such as Employee.ID = CONVERT(HOST_NAME() as int) to only get the rows you require at the subscrption.

SQL Server 2008 Replication to SQL Express 2005 via web sync

3 Answers3