SQL Server 2008 R2 transactional replication over VPN
- by enashnash
I'm having difficulty setting up replication over a VPN.
I have a SQL Server 2008 R2, Enterprise Edition database on a Windows 2008 R2 Server.
SQL Server is running on a non-standard port. I have set it up so that it is acting as its own distributor and have configured a publisher on this server. It is set as an updatable transational publication (yes, this is necessary).
On this server, I have Routing and Remote Access enabled in order to be able to establish VPN connections. It is configured with a static IP address pool, of which the first in the range is always assigned to the server. I have assigned a test user a static address within this range (I don't know if this is necessary or not).
All clients will be 2008 R2 versions, but could be SQL Express or standalone developer instances of the full product.
I can establish a VPN connection from the client without problems and can see that the correct IP addresses are allocated.
After connecting to the database to test that I can establish a connection, I realised that I needed to be able to connect to the database using the server name rather than an IP address - required for replication - which wouldn't work initially. I created an entry in the hosts file for the server on the client using the NETBIOS name of the server, and now I can connect to the server, from the client, using the SERVER\INSTANCE, PORT syntax, over the VPN. As it is the default instance on the server, I can also connect with simply SERVER, PORT syntax.
After all that, I still get the following dreaded error: SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, 'SERVER\INSTANCE'. (Replication.Utilities).
What have I missed? How do I get this to work?
TIA
