2020-10-11

Cross Domain SQL Server Replication

Setting up replication with publisher on one domain and subscriber on another domain is tricky. This blog post discuss issues encountered and the steps to overcome them.

When you try to add subscription by specifying the FQDN of the subscriber server, you will get an error "Sql Server replication requires the actual server name to make a connection to the server" as shown below.

As you see on the SSMS object explorer in the above picture, the subscriber server actually can be reached from the publisher server by specifying the FQDN, but the subscription wizard denied you to create it. In order to overcome it, you need to create SQL Server Alias in the configuration manager. Alias provides alternate name to be used to connect to the target SQL Server. Note that SSMS is running in 32-bit, while most of the SQL Servers nowadays are in 64-bit version. Replication agent process is called from SQL Server agent, so the replication process run in 64-bit. So, we need to setup an alias in both 32-bit and 64-bit SQL Native Client configuration.

After the server aliases defined, you should able to add subscriber by specifying the server alias.