SQL Server Replication (SSQA.net)

SQL Server Peer-to-Peer replicaton - how to achieve scalability and availability

Replication will be very handy to distribute the load of application when you have large scale of data management, as it evolved from SQL Server 6.0 version the transactional replication is first baby that was primarily used to support reporting applications. The primary resource of replication satisfies a variety of other scenarios, about eighty percent of reporting needs whether or not they use other replication functionality. Ok here I got much of talking about what best this SQL Server feature can offer to accomplish the task.

So now from SQL Server 2005 version brings the beast of replication, peer-to-peer replication. This IS designed to provide high availability and scalability in one solution, and to expand a support for more complex topologies. When you talk about topology you need to ensure the efficient delivery of changes to each server. Such as all servers communicate with each other; this is called a ring topology and is appropriate if you have three or four servers. If you have a larger number of servers, consider configuring replication so that each server communicates with at least two other servers. The availability of platform can be achieved without the complexity of many servers being fully-connected. So this is what peer-to-per to offer whether the servers are all fully-connected, peer-to-peer replication routes changes in such a way that a change is applied only one time at each server, and changes are sent over the fastest route possible!

As a DBA (by heart) my concern is availablity and scalability of database(s) and server(s) of SQL world! This means data should be available even if a server is down or being upgraded, and the databases serving that data should respond to user queries in a timely manner, even when those requests are at a peak. Though we can talk hours together about what are best practices and how to accomplish this based on database to database, size varies and also the involvement of vendors for hardware etc. In my experience I see that most common approach in providing availability and scalability is to copy data to one or more servers and to have that data available for users to query. To some extent Log Shipping and Database Mirroring offers the flexibility, but I believe peer-to-peer transactional replication is typically the best way to copy data to support both availability and scalability.

Peer-to-peer replication is built on the proven foundation of transactional replication, which provides a high-performance way to deliver data changes to remote servers, whether those servers are across the building or across the world. In the default read-only transactional replication you update data at only one server, and then updates are replicated to other servers. In peer-to-peer replication you can update data at any one of the servers and then updates are replicated to all the other servers. In the recent times when I had designed solution for an online shop for audio & video facilities, I have seen that peer-to-peer replication can be used. Say for the vendor with a web site that supports "online ordering" which is prone to significant more read activity than write activity. So the solution must have the ability to design the importance to scale out this read activity! So this is how the replication (peer-to-peer) provides copies of the data, so that users can be directed to different servers to spread out the load. As the data is spread out at multiple servers, if one of the servers is down because of planned maintenance or an unplanned issue, such as a disk failure, the other servers are available to handle queries until the offline server is brought back online.

One of the Microsoft technet documentation article refers on comparison of replication types that "Allow Updates at the Subscriber", as this is required to pass on the changes flow to publisher. I refer the following topics in BOL: "Peer-to-Peer Transactional Replication" and "Selecting the Appropriate Type of Replication".

No doubt that BOL is very handy on how one can find information on how to do the tasks, easily. Even I learnt many things by reading BOL and one of them is how to configure peer-to-peer replication using SQL Server Management Studio (SSMS)! As you may know TSQL stored procedures and RMO are available to achieve task but as the human tendency is we rely upon easy way easy go. Again I would like to keep the joy of reference about how to configure and monitor replication using the following tools available in SQL Server Management Studio:

• New Publication Wizard

• Publication Properties dialog box

• Configure Peer-to-Peer Topology Wizard

• Replication Monitor

Configuration and monitoring is composed of the following steps:

• Creating the sample databases

• Creating the first publication

• Enabling the publication for peer-to-peer replication

• Backing up and restoring the publication database

• Creating a publication and subscriptions for each node

• Verifying that changes are replicated to each node

• Monitoring the topology

Using the Configure Peer-To-Peer topology wizard can get you achieve the task and design of topology you will create is fully-connected: each node has a subscription to each of the other nodes. It is also possible to configure a topology that includes subscriptions only between some of the nodes: for example, you could configure a publication at each node, and enable subscriptions only between those nodes that should be connected. Also I have seen other tools in addition to SSMS that contains dialog boxes and property sheets related to replication. For more information, see "Replication Properties" in SQL Server Books Online. Such as SQL Server Replication Monitor. Replication Monitor is a graphical tool that lets you monitor the overall health of a replication topology. For more information, see "Monitoring Replication with Replication Monitor" in SQL Server Books Online. Also the tablediff utility. This command line utility enables you to identify differences between databases. For more information, see "tablediff Utility" in SQL Server Books Online.

The final piece of detecting and resolving the data conflict, peer-to-peer is not a good choice as it is optimized for throughput performance and does not incur the overhead of detecting and resolving conflicts. Custom applications that access and change data must ensure that inserts, updates, and deletes are partitioned, so that modifications to a given row originating at one node are synchronized with all other databases in the topology before the row is modified by a different node. If an application performs concurrent conflicting modifications to a given row at multiple nodes, you should use merge replication, which is designed for handling conflicts. We could make use sp_helpsubscriptionerrors to find rows with issues, also sp_browsereplcommands to view commands in the distribution database and use sp_replshowcmds to view transaction log data.

Hope I covered much of the topic with the help of Microsoft documentation and my experience to share the 'piece of' knowledge of SQL Server world!