Friday, October 27, 2006

SQL Server 2005 replication

We've been playing with SQL Server 2005 replication today. We have been testing a configuration with a single publisher and multiple subscribers. The subscribers are all in different Actibe Directory (AD) domains with no trust between them. Comms between the servers needs to be locked down with the minimum number of ports opened. All of the SQL books I have read show how to do SQL replication between servers in the same AD domain.

To test the configuration we had 5 servers running SQL server 2005 on Windows Server 2003 SR2. The firewall was turned on and a single TCP port (1433) was opened on each server. All the servers were set to communicate using TCP/IP only, all other client and server protocols were turned off.

In order to replicate between servers the following settings were required:-

1) SQL server was setup to allow SQL Authentication mode. An account was setup specifically for the subscribers to use when connecting to the publishing server.

2) In order to replicate between the servers it was necessary to setup a HOSTS file with the IP address of the remote server. Replication does not work if you use either a raw IP address or a fully qualified domein name configured in the DNS server. I suspect this is because the server name is used to establish both a TCP/IP connection and an application level connection to the SQL server.

If you use these settings you should be able to replicate sucessfully.