Database Replication – Part II

In this post I will discuss multiple-server replication strategies. These strategies are meet more of the real world requirements.  These scenarios can be very useful for load balancing, updating common information, providing a central repository and allowing multiple locations to have the same data. Other articles in this series are listed below.

• Part I – Overview and Server to Server strategies
• Part II – Multiple server strategies
• Part III – Replication filtering
• Part IV – Replication conflicts

Let me start with a bit of background. I refer to the source database as the publisher and the target database as a subscriber. Although these strategies can apply to database replication in general some of the information I discuss is specific to Advantage Database Server. You can get an overview of Advantage replication in our online help files.

Multiple One-Way Scenario

There are essentially two ways that this scenario can be implemented. First a single publisher can have multiple subscribers or multiple publishers can replicate to a single subscriber. This scenario applies if the changes made at the subscribers is not needed by the central server.

In the first case a central server pushes changes out to multiple subscribers. For example the corporate office sends catalog updates to individual stores. This can also be used for load balancing with all changes being applied to the single publisher and then queried at the subscribers. This ensures that the users get the same data regardless of which subscriber they are connected to.

The second case could be used as a central backup or reporting server. Each of the publishers push their changes up to a central server. This provides a consolidated database of all activity across all locations. For instance a group of retail stores replicate their sales data to a corporate office who can now run sales reports which include data from all locations.

Multiple Two-Way Replication

When all locations need to have the same data then two-way replication is required. Keep in mind that the more servers involved in the replication scenario the more likelihood of conflicts. There are two basic approaches to this situation; Each server is a publisher/subscriber to every other server or each server is a publisher/subscriber to a central server.

When a central server is not used each server will need to have n-1 replication partners. With 4 servers each server will have 3 subscribers and be a subscriber to 3 publishers. This solution can get very complicated quickly since a connection between these servers is necessary to push changes. Additionally an additional user license is required for each publisher the server is subscribed to.

You can simplify this scenario by using a Spoke and Hub model. In this model each location replicates to a central server which uses forwarding to push changes to all the other locations. Although the spoke and hub requires an additional server you get the benefit of having a single consolidated database for reporting as well as a central location for common updates.

 

Hybrid

These models can be combined to provide additional functionality. For instance you may be using a spoke and hub model to ensure that all your locations have the most current data. In addition you want to have another server at the central office to use as a real-time backup and reporting solution. To do this you simply add another one-way replication to the central server.

In the next post I will be discussing using replication filters to limit the data that is replicated.