Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Merge replication pros and cons in SQL Server 2000

Merge replication should typically be avoided when achieving high availability in SQL Server 2000, according to contributor Michelle Gutzait. She explains the pros and cons of using application load balancing with merge replication and offers a simpler high availability solution plus recovery scenarios.

  If you have deployed merge replication even once, you know it can cause a lot of headaches within the planning...

phase, during deployment and even after it is set up. According to Microsoft SQL Server Books Online: "Merge replication is the process of distributing data from Publisher to Subscribers, allowing the Publisher and Subscribers to make updates while connected or disconnected, and then merging the updates between sites when they are connected."

If you are not familiar with merge replication, take a look at the following sections in Microsoft SQL Server Books Online to read about its complexity:
How Merge Replication Works
Using Merge Replication
Enhancing Merge Replication Performance
Strategies for Backing Up and Restoring Merge Replication

Personally, I try to avoid using merge replication as much as possible.

As a consultant, I often witness situations where organizations consider creating a highly available environment using application load balancing jointly with multiple SQL Server 2000 instances (one on each server in the load balancing). This architecture is cheaper than the Windows Cluster setting, but it is less reliable and requires more programming. In this load-balancing model SQL Server databases must be mutually updated with merge replication.

In this tip, I describe this high-availability solution, discuss its advantages and disadvantages and suggest an alternative solution.

   Model for load balancing and merge replication
   Simplified active/passive model
   Using DB Alias to access SQL Server
   Code to change the Alias DB Registry key
   Recovery scenarios

Model for load balancing and merge replication

This model includes two or more Windows servers in a load-balancing architecture. Each server contains Internet Information Services (IIS) and SQL Server (at least the Standard Edition). The application uses the local SQL Server, so when a client connects to one of the servers through the load-balancing mechanism, it statically connects to the local SQL server on that machine.

If one server fails, the other servers continue handling the workload. The SQL Server databases have to be updated with the changes coming from the other servers as well. In most scenarios, that can be achieved only via merge replication.

This figure shows two servers using load balancing with IIS.

Figure 1: Load balancing with two servers using IIS.

For more than two servers, you can apply the same model. The advantage to using this model is that you attain high availability and load balancing altogether. The load balancing is obtained not only at the application level but at the database level as well. But is it?

With merge replication, each update to a database will cause updates in the twin database on the other servers, which means that each SQL Server handles the total workload after all. Furthermore, if you decide to configure the distribution process and database on one of the servers in load balancing, it may affect performance even more. If you configure the distribution process on a different machine, it may be more costly.
Simplified active/passive model

You can simplify the model by using one database server as an active database and the other database(s) as warm stand-by.

Figure 2: One database server active; another as warm stand-by

This model employs one-way transactional replication to update the warm stand-by database(s). This ensures that all committed transactions immediately run on the stand-by database however asynchronous to the transactions. As previously noted, SQL Server instances must be at least Standard Edition.

The applications will connect to the main database via DB Alias. When using DB Alias, the SQL Server instance gets a logical name on the machine so client applications on that machine can connect to SQL Server using the alias instead of the real name. This way, the pointer to the database can be changed without changing the connection string on the application side.

The alias can be used for high availability. An automatic process on each server will check the main SQL Server. In case of a failure, the alias will be automatically changed by this process.

The DB Alias trick is discussed in more details below.
Using DB Alias to access SQL Server

SQL Server instances listen over the network with specific network protocols on specific network addresses. An application connects to a SQL Server instance using one of these network addresses and protocols.

On the client side, an alias name can be defined for a specific network address of the SQL Server instance. This way the client can "identify" the SQL Server with a different name as well as the real name.

The alias can be defined via the Client Network Utility.

Figure 3: Defining alias via the Client Network Utility

For example, on my computer I have a SQL Server instance called ITERGYLT06. I added two aliases pointing to ITERGYLT06 SQL Server instance. Figures 4 and 5 show how I added each alias.

Figures 4 and 5: How to add alias

I can connect to aaa or bbb with Enterprise Manager, osql, Query Analyzer or another client from the machine on which the aliases were declared, such as Query Analyzer.

Figure 6: Connecting to SQL Server with Query Analyzer

Figure 7 shows I am now connected to ITERGYLT06.

Figure 7: Connecting to SQL Server instance

Aliases are defined in the Registry under the key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo

I can double aaa if I go to the Registry: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo

Figure 8: Double aaa in Registry

I can now change the "itergylt06" SQL Server instance name to a different one, such as MYLAB-SQL.

Figure 9: Changing SQL Server instance name

If I now open the Client Network Utility window again I can see the change.

Figure 10: Name change in Client Network Utility

Note: When the alias is changed, existing connections with the old alias remain connected. New connections will connect to the new name. You need a small application that tries to connect to the SQL Servers and databases (with timeout definition), which then changes the alias in the Registry as needed. This application can also send a message to the database administrators.

The login used to run this utility must have permissions to update the local machine's Registry.
Code to change the Alias DB Registry key

The following C# code changes the Alias DB Registry key as discussed above.

Recovery scenarios

The following are recovery scenarios for the alternative, simpler solution.

Scenario: Main database server or the main application database fails.

Main database server or app database fails

The automatic processes running on both machines will automatically change the alias to work with the warm stand-by server. Transactional replication will automatically stop or fail, depending on which part of the SQL Server failed.

When the failed unit is back, you have two choices:

  • Change the alias to work with the main and restart the replication.
  • Leave the current database as the main and rebuild the replication model to have the other SQL Server as the warm stand-by (recommended -- no downtime).

Scenario: Main SQL Server or database and the IIS failed on Server_2 (or Server_2 failed).

Main SQL Server or database and IIS fails

The automatic processes running on the working server (Server_1) will automatically change the alias to work with the warm stand-by database. Transactional replication will automatically stop or fail, depending on which part of the SQL Server failed.

When the failed unit is back, you have two choices, as in the previous scenario.

Scenario: Warm stand-by SQL Server or database fails but IIS is still running on that machine.

Transactional replication will automatically stop or fail, depending on which part of the SQL Server failed. This will not affect the application and the alias doesn't have to be changed.

After the failed unit is repaired, the replication has to be re-initialized.

Scenario: Warm stand-by SQL Server or database and the IIS failed on Server 1 (or Server_1 failed).

Warm standy-by fails but IIS continues to run

Transactional replication will automatically stop or fail (depending on which part of the SQL Server failed). This will not affect the application and the alias doesn't have to be changed.

After the failed unit is repaired, the replication has to be re-initialized.
Recovery Conclusion

Windows clustering is the best way to attain a high-availability environment, where recovery time is almost immediate and automatic. Application load balancing is the best way to balance application load.

Windows clustering is often too expensive for some organizations or projects. A cheaper and simple way to handle a high-availability and load-balanced environment can be achieved using application load balancing along with active/passive SQL Server architecture where the passive instance is constantly updated via transactional replication.

About the author: Michelle Gutzait works as a senior database consultant for ITERGY International Inc., an IT consulting firm specializing in the design, implementation, security and support of Microsoft products in the enterprise. Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant. For the past 10 years, she has worked exclusively with SQL Server. Her skills include database design, performance tuning, security, high availability, disaster recovery, very large databases, replication, T-SQL coding, DTS packages, and administrative and infrastructure tools development, reporting services and more.

This was last published in June 2006

Dig Deeper on SQL Server Replication



Find more PRO+ content and other member only offers, here.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.