3

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

enashnash
  • 131
  • 1
  • 3

5 Answers5

1

Try this command:

select name from sys.servers

See what gets retured. You need use that name to connect to the serve while setting up replication.

Or change it to name you want to use:

sp_dropserver 'currentname'
go
sp_addserver 'changedname' , 'local'

db engine then needs to be rebooted

Frederik
  • 3,359
  • 3
  • 32
  • 46
Z man
  • 11
  • 1
0

Change the port back to the default port number. You aren't doing anything by changing the port number besides making your life harder.

mrdenny
  • 27,174
  • 4
  • 41
  • 69
  • Okay, I've done that, and now I am getting the original error if just using `SERVERNAME` or this one if attempting `SERVERNAME\INSTANCE`: `A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)`. Very odd, any ideas?! – enashnash Apr 14 '11 at 22:24
  • Can you ping the server? Can you telnet to TCP port 1433 on the SQL Server from the remote machine? Are you sure that the SQL Server is setup for remote connections? – mrdenny Apr 15 '11 at 01:14
0

Just a rambling, probably way too late for you but useful for the googlefiers: Based on what I read I guess you need to add an alias to the local server as it's a bit hard to find a named instance. SQL Server client will go to the SQL Browser for info about named instances - which seems unavailable via VPN. But before that, it checks local aliases whether the host name is specified there. You can add it at SQL Server Configuration Manager - Aliases. A life-saver advice: keep your 32-bit and 64-bit aliases in sync. Period.

user9517
  • 115,471
  • 20
  • 215
  • 297
Erik Bitemo
  • 1
0

I have solved this in the past by creating a server side alias (cliconfig) with the same name that the remote server shows in sys.servers, but the server name and port point to the IP address and SQL port since there was no name resolution.

Jason Cumberland
  • 1,579
  • 10
  • 13
-2

A little unrelated, but I have used Double Take to get an additional layer SQL Server replication at the entire server level. Depending on your DR plan requirements, it's a very good option to consider. Fail over can be manual or fully automated and can be less expensive than a VM-Ware implementation.

MDMarra
  • 100,734
  • 32
  • 197
  • 329
Dave
  • 42
  • 2