Systems Engineering and RDBMS

Archive for October 27th, 2007

Peer to Peer Transactional Replication

Posted by decipherinfosys on October 27, 2007

SQL Server 2000 had bi-directional transactional replication feature and it has been greatly enhanced in SQL Server 2005. It is called P2P or Peer to Peer Replication. This is Microsoft’s answer to the scaling out solution for SQL Server. This allows the applications working against the different nodes of SQL Server to be able to do read as well as write operations against any of the nodes involved in replication. The load balancing of the read operations as well as the updates across nodes allows for high availability as well as scalability.

The basic underlying principle of replication is that the data is published by the publisher and is replicated to the Subscriber. This data is read-only. With P2P replication though, each node that is involved in this replication acts as a Publisher as well as the Subscriber to the other nodes. The way each node can modify data is by allowing 2-way transactional replication. Unlike Oracle RAC in which there is a single database and no single node owns the data, in the case of SQL Server, each node has it’s own copy of the database and has the identical schema and the identical data. So, the storage requirements are more. In addition, for the DML operations (inserts, updates and deletes), an approach that is typically adopted is the usage of partitioned updates which means that if you have a table called CUST_MASTER and updates need to be made to it, then the DMLs can be partitioned so that the updates for customers with the name of A-R happens on one node and the ones from S-Z happen on the other node (or any other combination of this set). This requires changes to the application. If updates are needed from all the nodes involved in the replication, a better choice would be to use merge replication instead.

Let’s consider a scenario where we have two nodes involved in the P2P replication. In this case, the reads can be balanced across the two nodes by application server assignment and the partitioned updates can be done as mentioned above. Another scenario would be when one would direct all the writes to one node and reads to all the nodes (though this can be achieved using the traditional one-way transactional replication as well). In order to achieve high availability, one can code the application in such a way that if one of the nodes is down, the application’s connect module re-directs the connection to the other nodes involved in P2P replication.

You can read up more on P2P replication using these links:

Posted in SQL Server | 1 Comment »