Database Replication – Part I

Database replication is the ability to maintain multiple copies of the same database across multiple servers. This series of posts will discuss various replication strategies and give examples on when these strategies may be used. This will be done in four parts:

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

Advantage replication is an asynchronous push implementation which minimizes the impact on applications and provide the most up-to-date information to all servers. It is important to note that replication is not synchronization. Advantage does not compare the databases and apply changes. Replication simply pushes changes from one database to another.

I will use the following terminology when talking about replication. The source database is referred to as the publisher, the target database is the subscriber. Servers can be both publishers and subscribers and any server can be a publisher to multiple subscribers or a subscriber to many publishers. With that in mind let’s discuss some replication strategies.

One-Way Replication

One-way replication is a publisher pushing changes to one or more subscribers. The subscribers do not send any changes back to the publisher. Therefore changes are not normally made at the subscriber or changes made to the subscriber are not needed by the publisher.

Although the simplest form of replication it is never the less very powerful. For example this model can be used to provide a warm standby solution to increase availability. A subscriber will receive all updates from the publisher in real time. If the publisher suffers from a catastrophic failure the subscriber can be used by the clients to continue working.

Two-Way Replication

Two-way replication allows each server to be both a publisher and subscriber. Changes made at either server will be pushed to the other server.

This model can be very useful when you have two or more locations which need to manipulate a common set of data. This method may provide better performance than requiring one location to connect to a database via a WAN connection. However, problems can arise if both locations are manipulating the same records at the same time. Advantage provides a mechanism for dealing with replication conflicts which I will discuss in part 3 of this series.

Forwarding

Forwarding, also referred to as chaining, allows subscribers to pass replicated records on to its subscribers. This eliminates the need for the first publisher to define an additional subscriber. With Advantage forwarding is specified for each subscription and is off by default.

One potential pitfall when using forwarding is creating a loop. This is where the last server in the chain replicates to the first server in the chain ( A –> B –> C –> A ). Advantage will detect this situation and the update will not be applied. There is more information in the Advantage Help File.

In part 2 I will discuss scenarios which involve two-way replication between multiple servers.