Peer-to-Peer Transactional Replication in SQL Server 2005

Replication is an important technology that can be used in SQL Server. With replication, a DBA can synchronize data between two or more databases. This vital function is primarily used when a DBA needs to share server loads to improve database server performance. Replication can also help to provide high availability to TCP/IP services.

As you may be aware, SQL Server 2000 provides three types of replication:

• Snapshot replication.
• Transactional replication.
• Merge replication.

SQL Server 2005 includes a new replication method:

• Peer-to-peer transactional replication.

This article describes this new replication method.

SQL Server 2005 Editions

The different editions of previous versions of SQL Server, except SQL Server CE, all supported each of the different types of replication. This is no longer true with SQL Server 2005.

Table 1: Replication types by SQL Server edition. (Source: Books Online)

Non-SQL Server subscribers, Oracle publishing, and peer-to-peer transactional replication are new features of SQL Server 2005, but they are not available in all editions. As the table above indicates, peer-to-peer transactional replication is only available in enterprise and developer editions of SQL Server 2005.

The developer edition of SQL Server includes all of the functionality of the enterprise edition; however, it is licensed for use as a development and testing server, not for use as a production server. Therefore, you will need the enterprise edition to take advantage of peer-to-peer replication. The retail price of a processor license for SQL Server enterprise edition is about four times that of the standard edition. Obviously, cost considerations will be a factor.

Using Peer-To-Peer Transactional Replication

Let's consider a typical online e-commerce application. In order to avoid downtime and reduce the load on any single server, the database for this application is in more than one location. As it is an online e-commerce system, data needs to be changed (through inserts, updates, and deletes) at each location and all the data modifications need to be replicated to the other servers. For example, let's assume there are databases at location A, B, and C.

To accomplish this in SQL Server 2000, we would use merge replication. Location A is the publisher, and B and C are the subscribers.

Figure 1: Merge replication.

The obvious drawback of this method is that it is a single point of failure configuration. If database B is down, the A to C replication link will continue to work. Users who are connected to both servers A and C will not see any adverse effect. However, if database A fails, B and C will be isolated from the system. Modifications will not be visible on other end.

SQL Server 2000 replication uses the publisher and subscriber hierarchy method. Successful operation of this configuration requires that the publisher be present at all times.

With the peer-to-peer replication topology in SQL Server 2005, each node acts as a publisher and as a subscriber. Replication recognizes when changes have occurred on a given node but only allows those changes to cycle through the nodes one at a time.

Figure 2: Peer-to-peer replication.

If one database is down (A, for example), the other databases (B and C) can still replicate. Whenever that database (A) comes back up, it can synchronize with the others (B and C) and get the changes that took place after it went down. This is possible because the databases (A, B, and C) all act as both publisher and subscriber.