SQL Server subscriptions on non standard port

Mon, Aug 25, 2008 3-minute read

When setting up replication using publications and subscriptions, it’s not easy to do if your publishing server is running on a different port number than the standard port number (1433).

If you try to enter the server name as SERVERNAME,port number you get an error stating that you cannot use ip addresses, aliases or other strange names.

So the only option you are left with is to use the REAL name of the SQL server.

So lets say your server’s SQL name is: COMPANYSQL01 you need to use that server name, but if you are running on a different port number or even with multiple instances on the server, each running on a different port, you are in BIG trouble.

Lets take the following configuration.

You have a SQL server 2005, configured with the name COMPANYSQL01.

You have two instances on that server, INSTANCE1 and INSTANCE2

INSTANCE1 runs on the standard port 1433, and you have configured the next instance INSTANCE2 to run on 1434.

So normally you would connect to the server instance2, by entering COMPANYSQL01\INSTANCE2,1434

But that’s not possible in Subscription configuration, since you are not allowed to enter a port number.

So what you need to do, is the following:

1. Connect to the instance you want to create a subscription from:

2. Open a query window and enter the following: SELECT @@SERVERNAME (Should yield COMPANYSQL01\INSTANCE2) with the example above

3. Open the Sql Server Configuration Manager on the server that wants to be a subscriber.

3. Expand the SQL Native client configuration (32bit)

4. Expand Aliases, and create a new alias:

4.1 For alias name enter COMPANYSQL01\INSTANCE2
4.2 In port number you enter the non standard port number, i.e. 1434 in this example
4.3 Protocol leave at TCP/IP
4.4 Enter COMPANYSQL01\INSTANCE2 as server name.
4.5 If server is a 64bit machine repeat steps 4 - 4.4 in the SQL Native client configuration in Sql Server Configuration Manager

If you do not have name resolution in place on your subscriber server, i.e. you cannot ping COMPANYSQL01, then you need to fix that, either by updating your DNS or by adding a host entry in your hosts file (C:\Windows\System32\Drivers\etc\hosts ). The name resolution issues can be because your SQL server is on another network than your subscriber, i.e. your subscriber is on your local LAN, and the server that you want subscriptions from is in your DMZ.

Even though the subscription configuration explicitly tells you that aliases does not work with subscriptions, it does if you use the same name for the alias as the real name.
Its strange I know, but it works, it really does.