Peer-to-Peer Transactional Replication in SQL Server 2005

http://www.sql-server-performance.com/articles/dba/peer-to-peer_replication_p1.aspx

 

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.

Features/Replication Enhancements	Enterprise/Developer Editions (32- or 64-bit)	Standard Edition (32-bit)	Workgroup Edition (32-bit)
Merge Replication	Yes	Yes	Yes
Transactional Replication	Yes	Yes	Yes
Snapshot Replication	Yes	Yes	Yes
Non-SQL Server Subscribers	Yes	Yes	No
Oracle Publishing	Yes	No	No
Peer-to-Peer Transactional Replication	Yes	No	No

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.

 

note: from envykok

merge replication vs. peer to peer replication

same :

any update in any note will be replicated to all other servers

different :

• for merge replication : if publisher is down, then the whole replication structure will be broken and other servers will not able to get the lastest update.
• for p2p replication: any server down will not affected the other servers.

How to Configure Peer-to-Peer Replication

Now that we have seen how peer-to-peer transactional replication works, let's look at the configuration options.

First, we need to create a publication. In SQL Server 2005, we'll use the New Publication Wizard.

Figure 3: New Publication Wizard.

As you can see (above), there are four publication types. To configure peer-to-peer transactional replication, we need to select Transactional publication .

After the publication is created, we need to change its properties. In the Publication Properties window (see below), set Allow peer-to-peer subscriptions to True . Please note that once we set this property to "true" we cannot revert to "false" until replication for the publication is dropped.

Figure 4: Publication Properties.

Now we'll go back, right click on the publication, and run the peer-to-peer transactional replication wizard (see below).

With this wizard, we can add more SQL Servers or SQL Server instances to the peer network. You will not be able to add main publication databases or the previously selected database on which we ran the wizard.

Figure 5: Configure Peer-To-Peer Topology Wizard: selecting peers.

After configuring the peer network, we need to specify how we have initialized the databases for the other servers in the peer-to-peer network.

Figure 6: Configure Peer-To-Peer Topology Wizard: initializing the new peers.

The obvious way to initialize the new peer databases is to restore the backups (making sure they are not changed before setting up peer-to-peer replication). Otherwise, we can specify the backup file used. The SQL Server engine will synchronize the databases by comparing the publication database to the backup.

 

After we provide authentication for the Log Reader and the SQL Agent, the wizard will begin building the peer-to-peer topology. For our example, the process results in the creation of three publications and six subscriptions (see below). For each publication, the wizard will create a distribution database as well.

Figure 7: Configure Peer-To-Peer Topology Wizard: building the topology.

When we have completed the wizard, this is what we'll see in SQL Server 2005 explorer:

Figure 8: The results of running the wizard.

Introducing Another Node

In today's competitive environment, businesses expand widely and frequently. What if we need to add another node? In SQL Server 2005, using peer-to-peer replication, all we need to do is configure the new node as a publisher and a subscriber and attach it to the peer-to-peer network.

Conclusion

Peer-to-peer transactional replication has added a new dimension to SQL Server database replication, giving DBAs more flexibility. As a new feature, it isn't yet widely used. But it will be. So check it out and see how your organization will benefit.