November 21, 2023 at 8:24 am
I've setup the following situation:
Server has a SQL Server 2019 Standard edition and is the Publisher. Local laptop has a SQL Server 2019 Express edition and is the Subscriber.
Server and laptop are connected via VPN.
I created a publication on the server and a subscription on the local laptop. So far so good.
The synchronisation is setup as a Merge Sync. The subscription method is Pull.
I've created a batch file in orde to start the synchronisation on my local laptop.
It contains the following code: "C:\Program Files\Microsoft SQL Server\150\COM\replmerg.exe" -Publisher Publication-Server\TEST -PublisherDB TEST -Publication TESTPub -Subscriber Subscription-Local\TEST -SubscriberDB TEST-SubscriptionType 1 -SubscriberSecurityMode 1 -Distributor Publication-Server\TEST -OutputVerboseLevel 2 -Validate 3 -Continuous
Whenever I execute the batch file, it starts and directly stops with the following:
Message: The process could not connect to Subscriber 'Subscription-Local\TEST'.
Number: 20084.
Then it follows by disconnecting from OLE DB Subscriber 'Subscription-Local\TEST'.
I'm running the batch file with a Windows user that has db_owner permissions on the Subscription database TEST.
I'm lost about what to do, to get the batch file to work properly.
If you need more information, shoot!
So I tried changing from Windows Authentication to SQL Server Authentication. Same issue.
I tried running the batch file as Administrator. Same issue.
I tried deleting the Publication and Subscription and recreate them. Same issue.
November 21, 2023 at 3:16 pm
I would start by checking the logs on the publisher and subscriber as those will tell you what is wrong. Likely a permission issue on either the publisher or subscriber, but the logs will tell you exactly what is wrong.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 21, 2023 at 3:18 pm
Are we talking about the SQL error logs?
November 21, 2023 at 7:12 pm
I generally look for all logs that I am able to access. In this scenario, the SQL logs (not error logs, but the current SQL Server Logs, or possibly a historical one if your log has cycled over). But in general, when I have issues, I check the logs as my first starting point. I tend to look at the log closest to the problem first, in your case the error message from the app. Unfortunately, that doesn't tell me enough to have an action plan, so next I'd look into the SQL Logs. If they didn't give me enough to go on, go over to the OS logs (system, application, security, and firewall for your specific use case) and since this is on a VPN, I'd be asking for the IT dept to review the VPN logs to see if something was blocking the connection.
Start as close to the problem as possible and expand the net until I find something actionable. With the error message of "could not connect", I suspect the SQL logs will tell you what you need, but you may need to go further and see if maybe the firewall logs or VPN logs are useful. I suspect that the system, application, and security logs will not be very helpful, but doesn't hurt to look at the logs too just in case.
Also, probably a dumb question, but you said that the server and laptop are connected over VPN - any chance the firewall (hardware or software) is blocking connections between the two machines?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
February 20, 2024 at 10:58 pm
Took me a day to find out...
Important missing requirement for Snapshot Agent to work is that you need to download and install Microsoft OLE DB Driver for SQL Server if you don't already have it.
Download it from here: https://learn.microsoft.com/en-us/sql/connect/oledb/download-oledb-driver-for-sql-server?view=sql-server-ver15
You will after installing the OLE DB driver find file msoledbsqlr.rll in directory C:\Windows\SysWOW64\1033. Add this folder as a Windows PATH Environment Variable, so it can be found by snapshot.exe.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply