SQL server replication connection problem

SQL Server Replication

danrostron


Hi

I have a problem when trying to setup replication between two SQL servers on different networks.

My publication server is SQL Server 2005 Standard Edition. My subscription server is SQL server 2005 Express Edition

I have setup the publication no problem but am now trying to create my subscriber. However, when trying to connect to the server, I receive the following 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, 'W2M-RDC-01'. (Microsoft.SqlServer.

However, because the servers are not on the same network, I cannot use the server name.

Why does it not allow you to connect to an IP

Is there a way to create an alias I have read that you can but am unsure how to do this. Do I need to change the hosts file If so, where can I find this and what exactly do I need to change

Kind Regards

Dan Rostron

Top

Re: SQL server replication connection problem

Michael Hotek


The engine interrogates the server registrations within the instance (sys.servers) and looks for the name that you are specifying. If it doesn't find this, you'll see this message. You can possibly add the entry into the subscriber using sp_addserver.

You could also put an entry in the hosts file pointing at it. The hosts file is located at ..<windows>\system32\drivers\etc. The format is pretty straightforward and documented within the file. I would caution doing this those, because if the IP address of the subscriber changes, you will need to edit the hosts file on the server for replication to be able to find it.






Top

Re: SQL server replication connection problem

danrostron

Thanks for the above Michael.

So I've taken your advice and gone for the first solution. However, where do I define the IP of my remote server

For example, I've executed the command,

sp_addserver 'W2M-RDC-01';

and has completed successfully.

However, I now need to add my subscriber. Using the 'New Subscription Wizard' within SQL Server Management Studio, if I use the 'Find SQL Server Publisher' and enter the local server (which is now 'W2M-RDC-01') it does not know where to look - and gives me an error. If I use the IP and then \W2M-RDC-01 (ie. 81.123.456.789\W2M-RDC-01) - then it gives me an error saying 'The remote server does not allow remote connections'. However, I know it does - because I can connect fine to the database server using SSMS.

So how do I reference the SQL server when trying to add a new subscription Do I still need to change the hosts file But this will go against your previous comments.

Any thoughts would be much appreciated.

Kind Regards

Dan Rostron



Top

Re: SQL server replication connection problem

Greg Y

try adding a server alias. You can also search this forum, this question has been asked many times.


Top