SQL Replication - Subscriber On Different Domain

Setting up SQL Server replication with publisher / distributor on one domain and subscriber on another domain could be tricky. It could be even more interesting when these servers have the same host name. This blog post discuss issues encountered and the steps of addressing them.


Preparation
In this example, two servers with SQL Server installed are setup in Microsoft Azure. Both using different cloud service (different domain) but within the same virtual network. In Microsoft Azure, when creating a virtual machine in a cloud service, Azure assigns the VM to a subdomain of cloudapp.net. Here is the server information.

The first VM (Server A)
Host Name: SQL2014
Domain: CloudLan.d10.internal.cloudapp.net
IP address: 10.0.0.5

The second VM (Server B)
Host Name: SQL2014
Domain: MoonLan.d10.internal.cloudapp.net
IP address: 10.0.0.4

There are both hosted on the same virtual network.

A database, TEST has been previously setup with a publication (PUB_C) and configured for transactional replication. Refer to this post for setup.

Communication Verification

The FQDN (Fully Qualified Domain Name) is required to be used for these two servers communication as they are on the different domain.

Attempt to ping Server B (with its FQDN) from Server A successful. Verified that they are able to communicate to each other. Connecting to Server B default SQL server instance from Server A successful with the FQDN of Server B.

Perform the same test and verify the connecting status on Server B connecting to Server A default SQL Server instance successful. So far so good. Each server is able to communicate to the other server as long as server FQDN is used.

Replication - Adding subscription
On SQL Server A, attempt to add SQL Server B as subscriber to this publication

In this example, the Push subscription method is used

Connecting the Server B default SQL Server instance with the FQDN since it is on different domain

Error!

Cannot connect to SQL2014.MoonLan.d9.internal.cloudapp.net.

------------------------------
ADDITIONAL INFORMATION:

SQL Server replication requires the actual server name to make a connection to the server. Specify the actual server name, 'SQL2014'. (Replication.Utilities)


If we read the error message, it states that the actual server name 'SQL2014' is required. It appears that the subscriber server name (server B) used should be the host name of the server, rather than the FQDN name. Or is it?

Let's investigate. On Server B, run this command,

SELECT @@SERVERNAME;
GO
SELECT *
FROM sys.servers;
GO

The sys.servers system table stores the local SQL Server instance name (when server_id = 0) and linked servers (if any, for server_id > 0). The current result for local instance is SQL2014.

A word of caution for the steps below. You do not need to change the sys.servers usually unless the entry in sys.servers is different from the server host name. You may just need to add an alias on SQL Server A (Go to 'Add an Alias for SQL Server B' section below). More information on the conclusion section below.

To verify if SQL replication subscriber error is referring to the host name or this entry in the sys.servers, if we change this record into SQL2014B (with the B),

EXEC sp_dropserver 'SQL2014'
GO
EXEC sp_addserver 'SQL2014B', local;
GO

Notice that @@SERVERNAME does not reflect the change. Let's restart the SQL Server service on Server B. Now both @@SERVERNAME and the sys.servers return SQL2014B. A reminder that, the server name (host name) has not been changed. It is still SQL2014.

$env:COMPUTERNAME

Result returns as SQL2014

Setting up an Alias for SQL Server B
Back to Server A. We need to connect to the SQL Server B instance (SQL2014B) on Server B (SQL2014). One way to accomplish this successfully is to create a SQL Server alias for SQL Server instance (SQL2014B) on Server A. SQL Server Alias provides alternate name to be used to connect to the target SQL Server. Adding SQL Server alias can be done with SQL Native Client in the SQL Server configuration manager, and in this case on Server A.

Note that SSMS is running in 32-bit, while the SQL Server in this case is installed with 64-bit version. While the replication agent process is called from SQL Server agent, the replication process run in 64-bit. So, in this case we will setup an alias in both 32-bit and 64-bit SQL Native Client configuration.

Alias: SQL2014B     (Same as the record in sys.servers on SQL Server B)
Port: 1433   (SQL Server default port since it's default instance)
Protocol: TCP/IP
Server: SQL2014.MoonLan.d10.internal.cloudapp.net     (This is Server B FQDN)

Now back to addition the replication publication subscriber screen. Enter SQL2014B.

Now it works.

Observation
From we just observed, the server name enter during the subscriber addition needs to be same as the local server entry in the sys.servers of the subscriber. It has nothing to do with the host name of the subscriber server. The local server entry may differs from the host name due to host name (computer name) changes after SQL Server installation. Or like this case it was changed intentionally.

If you didn't setup the alias in 32-bit SQL Native Client configuration, you may continue to encounter the error during setup. Vice versa, if you didn't setup the alias in 64-bit SQL Native Client configuration on 64-bit SQL Server, you may be to complete adding the subscriber, but replication encounter error during synchronization to subscriber.

Conclusion
To regroup, the example above address two issues.

- First, as we see that during the subscriber addition, the server name entered needs to be the same as the local server entry in the sys.servers of the subscriber.
- The second issue in this example is that the host name is the same on both servers, SQL2014 with different FQDN (this issue is not common)

To address the first issue, all we have to do is to create an alias (the entry as in sys.servers, usually same as its server host name) for SQL Server B on Server A (the connecting side). Setting an alias redirect the SQL communication of the alias to the actual SQL Server instance (with FQDN).

However, to address the second issue in this example, it becomes a little tricky. We could change the record in sys.servers of the SQL Server B to be something different (like we saw, SQL2014B) without changing the host name of the server. However, having a different name for SQL Server default instance and the server host name usually is not preferable. It could cause a lot of confusion and potential break some codes and setting configured previously. This may works when it is not an option to change the host name. With that said, always try to have the default SQL Server instance name similar as the server host name.

Note. Changing the host name (compute name) of the server does not change the entry in the sys.servers. You will need to perform the sp_dropserver and sp_addserver steps as shown above to change the SQL Server instance name in sys.servers to match the host name.

This example setup the Push subscription from SQL Server A. As a result, the alias of SQL Server B is setup on the SQL Server A. If you are setting Pull subscription from SQL Server B, the alias of SQL Server A will need to be setup on SQL Server B. Just remember that the alias (of the receiving side) is setup on the connecting side (the connection initializing side).