November 8, 2022 at 11:32 am
I am setting up SQL Server replication (transaction) with below steps and configuration:
Configuration:
1. Publisher on Machine A
2. Distribution on Machine B
3. Subscriber on Machine C
Steps:
1. Created a common sql server user with same password on both machine A and B
2. On machine B set up the distributor by enabling distribution and adding a publisher (machine A using username and password created in step 1). Also specified the admin link password.
3. On Server A trying to configure distribution by using "Use the following server as distributor"-> added the IP of server B, username and password as specified in step 1. Then added the same password as the admin link password defined on server B in step 2.
However, it always says Sql server could not connect to distributor using the specified password.
I have used a complex password abiding password policy and also tried multiple solutions posted online by many users. Basically, removed the publisher from distributor and added again/password updated again and then setting up distributor server on publisher. But keep getting the same error.
Kindly suggest what is wrong here. Have also checked and enabled is_data_access_enabled properties on both machines. Remove access is also turned on for both.
Would also want to refer to any good article that sets up replication (transaction) using 3 different servers on 3 different machines.
Any inputs will be highly appreciated.
Thanks
Pawan
November 9, 2022 at 7:16 am
It is too long since I last did replication to remember what is needed to answer your question.
However, I can say your problems will not end when you get this aspect of replication to work. Compared to using Availability Groups on SQL2019 or above, replication is far more complicated to manage. AGs do have their own problems but overall far fewer than replication.
I strongly recommend you get some expertise that already knows replication. You may find a suitable training course, but it needs to focus as much on hands-on troubleshooting as on initial setup. Your best option is to hire someone from the small pool of people who can do SQL replication.
Best of all, review why you are looking at replication instead of AGs. I have done both and prefer AGs any day of the week.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
November 9, 2022 at 8:42 pm
So I set up the publisher and Distribution on the same box.. <Server> will act as its own Distributor. I have to wonder if you are running into the double hop issue. That is the only thing that stands out from reading your post.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply