locked
Web Sync with Merge Replication RRS feed

  • Question

  • Hi All,

    I'm trying to get replication setup between two remote machines over the web using web sync and merge replication, which is my first time with it!

    I have 2 db servers (sql 2008 r2 on server 2008 r2) on different domains and separated by location. I also have 1 server in one location which has IIS 7 for the web sync. 

    Currently i have 1 db setup as a publication and distribution server for merge replication and it is filling the folder on its local HD with the replication data. I also have the IIS server setup with replisapi.dll and can access it via its URL:

    https://machinename/sqlreplication/replisapi.dll

    Which returns a page displaying: SQL Server WebSync ISAPI

    These bits all look to be configured correctly, what i can not find out how to setup correctly is the remote DB? What steps do i need to take to get it replicating from the first db? Just some pointers would help:)

    Very few guides regarding this on the web that i can find:(

    How do i specify the URL to go to on the remote db?

    I cant just setup a subscription on the remote db without setting up the publication server on it again?

    Any hints would be much appreciated

    Cheers

    Craig

    Tuesday, April 3, 2012 4:06 PM

Answers

  • You'll need to enable verbose agent logging on the subscriber to get more detail.  Add the parameters -OutputVerboseLevel 4 -Output C:\TEMP\mergeagent.log to the Run Agent job step.  Re-run the merge agent job and collect the log.  The error should be found near the end of the log.

    Are you able to access the Websync diagnostic page from the subscriber?  Also, did you import the certificate for the Merge agent process account?  I suspect the problem is related to permissions and/or the certificate.  The log should reveal more.


    Brandon Williams (blog | linkedin)

    • Proposed as answer by amber zhang Thursday, April 5, 2012 2:03 AM
    • Marked as answer by amber zhang Wednesday, April 11, 2012 3:06 AM
    Wednesday, April 4, 2012 5:00 PM

All replies

  • Hi Craig,

    What happens when you connect to the Websync Diagnostic page?  The URL will be: https://machinename/sqlreplication/replisapi.dll?diag

    If each Status column on the page is SUCCESS then your publication should be configured correctly.

    Next you'll want configure a subscription to use Web Synchronization.  Details on how to do this are found in How to: Configure Web Synchronization for Merge Replication and How to: Configure a Subscription to Use Web Synchronization.


    Brandon Williams (blog | linkedin)

    Tuesday, April 3, 2012 4:12 PM
  • Hi Brandon,

    Thanks for the quick reply!:)

    I get access denied when i try ?diag at the moment... What could be causing this? i added the local user to iusr group, the app pool is running as that user and they have permission over the directory just like the article said?

    The pages you linked to are the ones i have been using, i cant figure out how to setup the subscription as it wants a connection to the remote SQL server which it cant have, only https?

    Tuesday, April 3, 2012 4:18 PM
  • In IIS, navigate to the web site's Basic Settings -> Test Connection.  This will verify if the path can be accessed.  Does it succeed?

    If you can only connect to the publisher via HTTP then you'll want to configure the subscriber using replication stored procedures.  The second example in How to: Configure a Subscription to Use Web Synchronization (Replication Transact-SQL Programming) shows how to do this.


    Brandon Williams (blog | linkedin)

    Tuesday, April 3, 2012 4:51 PM
  • Hi Brandon,

    Yup it does succeed in IIS both passed the test?

    I missed that note in that article:( but am unsure as to what the variables should be set too? For example, the below variables, should they be the local or remote names, FQDN's, instances names, etc..?

    @publication,

    @subscriber,

    @subscriber_db,

    @publisher,

    @publisher_db

    So i need to run:

    -- Execute this batch at the Subscriber. DECLARE @publication AS sysname; DECLARE @publisher AS sysname; DECLARE @publicationDB AS sysname; DECLARE @websyncurl AS sysname; DECLARE @security_mode AS int; DECLARE @login AS sysname; DECLARE @password AS nvarchar(512); SET @publication = N'AdvWorksSalesOrdersMerge'; SET @publisher = $(PubServer); SET @publicationDB = N'AdventureWorks2008R2'; SET @websyncurl = 'https://' + $(WebServer) + '/WebSync'; SET @security_mode = 0; -- Basic Authentication for IIS SET @login = $(Login); SET @password = $(Password); -- At the subscription database, create a pull subscription -- to a merge publication. USE [AdventureWorks2008R2Replica] EXEC sp_addmergepullsubscription @publisher = @publisher, @publication = @publication, @publisher_db = @publicationDB; -- Add an agent job to synchronize the pull subscription. EXEC sp_addmergepullsubscription_agent @publisher = @publisher, @publisher_db = @publicationDB, @publication = @publication, @distributor = @publisher, @job_login = @login, @job_password = @password, @use_web_sync = 1, @internet_security_mode = @security_mode, @internet_url = @websyncurl, @internet_login = @login, @internet_password = @password; GO USE [AdventureWorks2008R2] GO

    CHeers

    Craig

    Wednesday, April 4, 2012 7:23 AM
  • Right im getting somewhere, i have got a subscription on the remote machine but am now receiving the following error in the history of that job on run:

    Number:  -2147467259
    Message: The processing of the response message failed.

    Any ideas on what may cause it?

    Wednesday, April 4, 2012 8:25 AM
  • You'll need to enable verbose agent logging on the subscriber to get more detail.  Add the parameters -OutputVerboseLevel 4 -Output C:\TEMP\mergeagent.log to the Run Agent job step.  Re-run the merge agent job and collect the log.  The error should be found near the end of the log.

    Are you able to access the Websync diagnostic page from the subscriber?  Also, did you import the certificate for the Merge agent process account?  I suspect the problem is related to permissions and/or the certificate.  The log should reveal more.


    Brandon Williams (blog | linkedin)

    • Proposed as answer by amber zhang Thursday, April 5, 2012 2:03 AM
    • Marked as answer by amber zhang Wednesday, April 11, 2012 3:06 AM
    Wednesday, April 4, 2012 5:00 PM