I am extending our network by placing a xenserver box at a remote location to reduce outages for our web resources. All our servers are on xenserver, with MS Server 2008r2 and SQL Server 10. I must add I am a developer, not a sysadmin, so I have never done a db replication setup before.
I have successfully got one server working as publisher / distributor(SVR-DBDISTRIB) and another working as subscriber (SVR-DBREPLICA). They are happy to update each other with no faults. As one server has to go off site, I am planning to use web synchronization from one of our ms web servers (Server 2008r2 / IIS 7.5). I have added all the repl_merge, etc passwords, self-certified certificate, installed the SQL connectivity, etc features to the IIS box and I have got replisapi.dll working with certificates saved to trusted certificate root on the subscriber (so no certificate warnings come up when I call https://server.domain/SQLReplication/replisapi.dll). I have run the replisapi.dll?diag when logged in using the web servers admin password and everything is working. I can also log in with the IISSQLRepl password I have set up to hit the web server on basic authentication from the subscriber (again with no certificate issue). There are no proxies set up on the system.
When I create a new web synchronization publication on SVR-DBDISTRIB that works with non-web replication, the subscriber on SVR-DBREPLICA can communicate with the publisher as the subscription shows up in SVR-DBDISRIB. The agents at the distributor have no error messages but no table updates appear at the subscriber, although I do get the following (rather unhelpful) error message:
The system cannot find the file specified
Here is the job history:
Date 12/12/2013 18:42:28
Log Job History (SVR-DBDISTRIB-test-web-test-web-full-SVR-DBREPLICA-test-web- repl- 0)
Step ID 1
Server SVR-DBREPLICA
Job Name SVR-DBDISTRIB-test-web-test-web-full-SVR-DBREPLICA-test-web-repl- 0
Step Name Run agent.
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
2013-12-12 18:42:28.987 Copyright (c) 2008 Microsoft Corporation
2013-12-12 18:42:28.987 Microsoft SQL Server Replication Agent: replmerg
2013-12-12 18:42:28.987
2013-12-12 18:42:28.987 The timestamps prepended to the output lines are expressed in terms of UTC time.
2013-12-12 18:42:28.987 User-specified agent parameter values:
-Publisher SVR-DBDISTRIB
-PublisherDB test-web
-Publication test-web-full
-Subscriber SVR-DBREPLICA
-SubscriberDB test-web-repl
-SubscriptionType 1
-SubscriberSecurityMode 1
-Distributor SVR-DBDISTRIB
-XJOBID 0x03CFDF8BD2749449A5E4D81AF5270A97
-XJOBNAME SVR-DBDISTRIB-test-web-test-web-full-SVR-DBREPLICA-test-web-repl- 0
-XSTEPID 1
-XSUBSYSTEM Merge
-XSERVER SVR-DBREPLICA
-XCMDLINE 0
-XCancelEventHandle 0000000000000160
-XParentProcessHandle 000000000000064C
2013-12-12 18:42:29.022 Connecting to Subscriber 'SVR-DBREPLICA'
2013-12-12 18:42:29.127 Connecting to Subscriber 'SVR-DBREPLICA'
2013-12-12 18:42:29.206 The upload message to be sent to Publisher 'SVR-DBDISTRIB' is being generated
2013-12-12 18:42:29.210 The merge process is using Exchange ID '8E8BFAE7-B280-4016- AB07-517DC4961361' for this web synchronization session.
2013-12-12 18:42:29.240 **The system cannot find the file specified.**
2013-12-12 18:42:29.242 Category:NULL
Source: Merge Process
Number: -2147221502
Message: The system cannot find the file specified.
2013-12-12 18:42:29.244 Category:NULL
Source: Merge Process
Number: -2147199373
Message: The Merge Agent failed to connect to the Internet proxy server for user 'repl_merge' during Web synchronization. Ensure that the proxy server settings are correctly configured in Internet Explorer, or specify the -InternetProxyServer parameter when starting the Merge Agent.
2013-12-12 18:42:29.245 Category:NULL
Source: Merge Process(Web Sync Client)
Number: -2147024896
Message: The Merge Agent could not connect to the URL 'https://server.domain/SQLReplication/replisapi.dll' during Web synchronization. Please verify that the URL, Internet login credentials and proxy server settings are correct and that the Web server is reachable.
I understand that there is a 'verbose' setting and I am not sure if this is the verbose output. All the MS technet documentation talks about using SQL Enterprise Manager (discontinued from server 2000) to do this.
Any help or advice on where to look would be appreciated - this is the last job I need to finish before pushing it out the door and I have spend 2 days on this now...
if your networks are connected use network share instead of ftp or web sync. Much easier to maintain and you don;t need extra web server ;)