Replication in SQL Server: Part Five

Overview 

Earlier, in the previous part we saw types of replication that are seen in Merge replication.

Here in part three we will be covering peer-to-peer replication.

Refer previous articles

• Replication In SQL Server: Part One
• Replication In SQL Server: Part Two
• Replication In SQL Server: Part Three
• Replication In SQL Server: Part Four

Types of SQL Server Replication

• Snapshot Replication. (Explained in Part Two)
• Transactional Replication.(Part Three )
• Merge Replication.(Part Four)
• Peer-to-Peer Replication.(Part Five)

Let’s see each type by an example so we will get a clear gist what each and every replication is doing, starting with Merge Replication.

Peer-To-Peer Replication

• A peer-to-peer replication as the name suggests replication is done across nodes. It’s like a topology Here replication might be a two or many nodes. My personal favorite peer-to-peer replication. It’s quite a challenge to implement peer-to-peer replication.
  
  
• Consider a scenario of an online Shopping market which are located at multiple places across globe. Here a Single pointed instance is not possible. Here peer-to peer replication is done. Where and particular app server A establishing and Connection with DB server A whatever changes has been done in DB Server A need to replicate that in DB server B so that App server B able to see the data .
  

Example

The Best example which I can give you is Domino’s pizza. When you give a contact number at a particular locator you can go anywhere in the world and the next time you make an order you have to spell your number and data gets fetched. Here there are multiple servers having multiple databases that are kept synced. Concept of Load balancing in SQL server.

• Peer-to-Peer replication the concept of load balancing is used. If a particular node gets failed an Application server A get points it get to point to Node B. Now the best part is Node B contains the same data which contains in Crashed Node A. That’s the best replication by far by Microsoft.
  

TIP

I have seen many clients want database to be kept synced across multiple locations. Especially in Banking Domains like Chennai, Mumbai and Kolkata. I advise them to use peer-to-peer replication. If you are having great infrastructure like servers, network bandwidth trust me you can play with replication you can setup multiple nodes multiple DB Server and every application points to different DB server even the managers will like the setup.

Architecture

NOTE : THE DIAGRAMS OR ARCHITECTURE HAS BEEN DRAWN BY ME FROM MICROSOFT VISIO

TIP 

Microsoft Visio Tool is an software mainly used for presentations or projecting ideas. I recommend you purchase it to draw diagrams pertaining to articles maintaining originality .

Just for the Info I am attaching ScreenShot of Microsoft Visio that how above diagram I had made,



Let’s Start

Logically it is advisable to multiple modes across different server. Here I am explaining Peer-To-Peer replication local server (SQL).

NOTE

To Configure and to check on your local PC you need to install SQL Server (2008, 2008R2, 2012, 2014 and so on). Express Edition will have less features in local PC.

• Open SQL Server Management Studio,
   
  
  
  
• Click on Replication,
  
  
  
  
• Configure Distribution,
  
  
  
  
• Click on Configure Distribution -> Next ->
  
  
  
  Select the Server or PC Hostname it will act as its own distributor and click next.
  
  
• Path Selection,
  
  
  
  Next
  
  
• Configure Database Name (Distributor),
  
  
  
  In this Case I had kept by default databasename as distributor .Click Next
  
  
• Configuring Instances,
  
  
  
  If you are running on multiple instances you can do it by clicking on ADD.
  
  Here, in this case using everything in local Server. Hence the checkbox is checked. Click Next and check Configure distribution Option -> Finish
  
  
• Executing
  
  
  
  
• Click on Replication ->Distributor Properties,
  
  
  
  
  
  Now here you will see transaction Retention are removed after 72 hours and agent history is removed after 48 hours. On Click of tab you will able to see the complete log path respectively .
  
  
  
  
• Go to Jobs,
  
  
  
  Here you will see the highlighted section you will see new jobs got created.
  
  
• Creating Snapshot Publisher.
  
  Click on Replication ->Local Publication ->New publication,
  
  
  
  It prompts you to select database which you want to make as a publisher.
  
  Here in this scenario, selecting Colombo database.
  
  Click Next,
  
  
  
  In this case we need to select Transactional Replication->Next,
  
  
  
  Select tables, stored procedures, and user defined functions and on...
  
  In this case, selected tables ->Next ->
  
  
• Creating Snapshot and scheduler,
  
  
  
  
  
  Click on create Snapshot immediately ->Click security Settings.
  
  
  
  Use SQL server Authentication.>OK -> Next ->Create publication ->Give publication name as “”TEST” -> Finish,
  
  
  
  
  
  You will see TEST publisher in Local Publications.
  
  Creating Subscription
  
  
  
  Click Next,
  
  
  
  Click Next ->
  
  
  
  Select subscriber database will be selection Colombo database->Next,
  
  
  
  Configure Security setting for subscriber,
  
  
  
  Click Ok ->Next,
  
  
  
  Now, it’s asking agent to run continuously, run on demand or schedule it. This depends on your business requirements how you want your data. Here am continuing with default selection run continuously. ->click Next ->Run immediately ->Create the subscription -> Finish.
  
  
  
  Se e the Local Subscriber Colombo Subscriber appeared.
  
  Job created in SQL server Jobs ->Right Click ->Start Job as Step.
  
  
  
  After Successful completion of job. You will able to see tables of Blood bank management database which we selected appeared in Colombo.
  

Advantages and Disadvantages:

Advantages

• In Peer-to-peer replication performance of a server increases . Here all the servers are taking part hence all the servers are active .
  
  
• Read and Write operations can be done smoothly as a result the data is shared or distributed as a result load balancing is possible .
  
  
• If One Server goes down due to some hardware or technical failure the server nearby server can be made live contains same data .

Disadvantages

• Peer-to-peer replication is possible only in Enterprise edition .
  
• Row and column filtering is not possible.
  
• Write operation needs to maintain at only one node otherwise it may lead to data conflicts.

Conclusion:

This sums up SQL Server replication. I have almost covered all aspects of types of replication with examples with proper screenshots attached. If you have any query regarding replication feel free to ask.