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.

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.

Continues…

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |