The opinions expressed herein are my own personal opinions and do not represent
my employer's view in any way.
Recently I had to configure merge replication between a configuration like this one:
It was my first experience with replication and it was a pain in the ass to have everything configured properly in order to get replication to work. So I'm going to detail what the necessary steps for this so it can help others and also it will help me in the future if I need to do this again.
The basic steps are:
1) set up the initial configuration2) set up the distributor3) set up the publisher4) set up the subscriber
I'll call the servers A and B. A will be the publisher and B the subscriber. Both servers have SQL Server 2005 installed.
I had some problems because server B initially was a named instance and was using dynamic ports. Even if I change server B to use the port 1433, more problems appear later because server A wasn't able to find the instance in server B in the replication process so I suggest you to use default instances in both servers and use the SQL Server default port.
To configure the firewalls be sure to open TCP port 1433 for incoming and outgoing traffic.
Replication doesn't work if you try to set up using the IP for a server. It needs the server name so you'll have to create an alias for each machine.
In server A, open SQL Server Configuration Manager, and add a new alias in SQL Native Client Configuration. Set the alias name to be the same as the name of server B, and the server to be the IP of server B.
Repeat the above step to create an alias in server B for server A.
If you're using a 64 bit edition of SQL Server you'll have two SQL Native Client Configuration entries, one for 32 bits and another for 64 bits. If you are going to connect to another 64 bit instance, add the alias in the 64 bit entry. Otherwise add it to the 32 bit instance.
Connect to server A using Remote desktop, open a SQL Server Management Console (SSMC) and try to connect to server B using the server name, not the IP. If the connection isn't working you have done something wrong, so verify the steps above. Repeat the same to check if you can connect to server A from server B.
Configuring the Distributor
Open a SSMC and connect to server B. Right click in the Replication node in the Object Explorer and select Configure Distribution. Choose that the server B will act as its own distributor. Then choose a location for the snapshot folder. The default is:
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\ReplData
Remember the location because it will be needed later.
The next step is to create the distribution database. After that, you have to enable the server A to use the distributor in server B to publish data, adding it to the list of allowed publishers (by default only the machine where the distributor executes is allowed).
When you have remote publishers, you have to specify a distributor password in order for the publisher to connect to the distributor.
When a new publication is created, the distributor stores a snapshot of the publication in the snapshot folder. As we're configuring a pull subscription the snapshot will be stored in the server B, where the distributor is. The snapshot agent will be started, and it will connect to the publisher, the distributor and generate a snapshot in the snapshot folder.
For this to work you have to create an user account in server B for the snapshot agent (for example, usrSnapshotAgent). The snapshot agent needs write permissions for the snapshot folder, and a windows login for SQL Server, with db_owner permissions for the distribution database.
You'll have to create a SQL server login (for example, db_usrSnapshotAgent) in the server A to allow the snapshot agent to access the publication database.
If everything goes well you'll have the distributor working. Probably you'll want to configure the distributor properties (right click in the Replication node and select Distributor Properties) to set a retention period that fits in your requirements.
Configuring the Publisher
Open a SSMC and connect to server A. Right click in the Replication->Local Publications node in the Object Explorer and select new Publication.
Select the distributor, the database to publish, the publication type (merge publication) and the articles to publish. After that the Snapshot agent wizard step is presented. It has two options: Create snapshot inmediately and Schedule the snapshot agent to run at a predefined schedule. Be sure that the first option is checked. Only check the second one if there is a chance that you'll add more subscribers in the future.
The next wizard step is where you configure the snapshot agent security. Set the process to run using the windows user account created previously (usrSnapshotAgent), and use the SQL serer login created when configuring the distributor (db_usrSnapshotAgent) to allow the snapshot agent to connect to the subscriber.
To check if the publication has been succesfully initialized and the snapshot was generated properly, right click in the publication name in the Replication->Local Publications node from SSMC of server A and select View Snapshot Agent Status. If an error surfaces, check the job history and try to find out what failed.
Be sure to select the properties of the publication in order to tune other publication parameters that are not shown in the wizard, like the subscription expiration options.
During tests I obtained the following error:
The snapshot could not be generated because the publisher is inactive.
It can be fixed typing this in a new query window for server B:
sp_changedistpublisher 'server A', 'active', 'true'
If you think you have found the problem, view the status of the snapshot agent and click restart.
Once you have the snapshot generated without problems you can start configuring the subscriber.
Configuring the Subscriber
The merge agent will execute in server B and it will need to connect to the distributor, to the publisher and to the subscriber, so we need to create a windows user account for it in server B (usrMergeAgent). This windows user account needs read permissions for the snapshot folder and db_owner permissions for the distributor and subscriber databses. A SQL server login needs to be created in server A (for example, db_usrMergeAgent) with db_owner permissions for the publication database.
Open a SSMC and connect to server B. Right click in the Replication->Local Subscriptions node in the Object Explorer and select New Subscription. Select the server A as the publisher and the publication created in the previous step.
Select pull subscription and the databse where the data will be replicated.
The next wizard page is where you configure the security for the merge agent. Select to run the merge agent under the windows account created previously (usrMergeAgent) and choose to connect to the publisher, distributor and subscriber by impersonating the process account. We'll connect to the publisher using a SQL Server login but in this wizard we can't configure the publisher and distributor differently.
The next step is to set up how often the merge agent synchronizes the data. This will depend on your replication requirements.
Finally, you have to specify if the subscription will be initialized automatically and when (select yes and inmediately), and the type of subscription (server or client).
If you view the synchronization status, the synchronization failed. This is because we configured the merge agent to connect to the publisher impersonating the merge agent account. If we modify the subscription options and change publisher connection to use the db_usrMergeAgent.
If you have done everything properly it should work. The error message:
The merge process could not connect to the Publisher 'ServerA:publication'. Check to ensure that the server is running.
usually appears if there is an error with the permissions in the merge agent.
As you can see there are a ton of steps to do and it is a very error prone process. However this is not because it is a difficult concept. It is easy to understand all the agents related to replication. However the number of accounts involved and all the security permissions needed make it difficult.
Once you have properly configured replication, it seems to work quite good. Anyway, probably it is a good idea to configure Database mail to be notified of any error in the replication process.