How to set network adapter used by SQL Server Replication?

Question

I have 2 servers running SQL Server 2005 with merge replication set up between them.

Each server is connected to the building network, but they also have a cross-over cable connecting them creating a private network.

How do I configure merge replication so it uses the private network and not the building network?

The private network is not part of any domain, but the building network may or may not have a domain depending on the client.

I tried setting up replication using the private network IP addresses, but some of the stored procedures only work with a windows server name. I imagine that server name could resolve over either network, and I want to prevent replication traffic from being sent on the building network.

Answer 1

I don't know if this will work, but it's worth a try.

• Make sure the crossover cable is set up as a private subnet between the machines. The IP addresses associated with each endpoint must have a common prefix which is different from the building network's. For example, set one endpoint to 192.168.24.1 and the other one to 192.168.24.2, and the subnet mask on both to 255.255.255.0.

• Add these IP addresses to the hosts file \WINDOWS\system32\drivers\etc\hosts. Make sure you use different server names than the existing ones. I've used server1 and server2 below.

  192.168.24.1 server1

  192.168.24.2 server2

Test using

   ping server1
   ping server2

to make sure they can talk to each other.

Use the server1 and server2 names to configure replication.

Answer 2

Here is a guess. Maybe SQL isn't listening on that IP.

Go to SQL Server configuration manager > SQL2005 network configuration > TCP/IP properties > Add IP(x) for your local network

SQL will also say what IP/ports it is listening on in the log on startup.

Answer 3

thinkcube's answer was correct except for a detail I want to share here in case people don't read the comments:

Make sure you use different server names than the existing ones.

I needed to use the same server name in the hosts file. This is because the publisher required the windows server name, while the subscriber was okay with something else.

Since windows looks in the hosts file first, the IP address (on the private network) will be used, but the publisher will still see the correct name.