Snapshot Replication in SQL Server 2008: Part1

Snapshot Replication in SQL Server 2008: Part1

Replication is one of the headstone features of SQL Server to keep data synchronize among various server. There are basically three types of replication present in SQL server Transactional,Snapshot and Merge Replication .In this article I’ll cover Snapshot Replication. As name implies “Snapshot” means it takes a snapshot of publisher database when any occurrence such as Insert, update, delete and merge (Part of DML) or Create, Alter, Drop, Truncate (part of DDL command) happens on a publisher database resides on server(e.g.S1) and needs to be reflected/replicate on the database residing on another server (e.g. S2). This change happens within fraction of seconds. In this article, I will demonstrate the step by step approach to configuring snapshot replication in SQL Server 2008 R2.

There are three musketeers of Replication and you will become familiar in the series Replication. These musketeers are shown below:

1.       Publisher
2.       Subscriber
3.       Distributor Database

Publisher:

The Publisher can be denoted as origin of database on which the schema or records amendments take place. Publisher is a point which manages data for replication and sends command to distributor for further process.e.g. S1

Subscriber:

The Subscribers is a point, where subscriber database incurs the notification and a copy of data from distributor about the changes happened at publisher database. The subscriber database generally live on a different e.g. S2

Distribution Database:

A database which contains all the Replication commands. Whenever any occurrence such as Insert, update, delete and merge (Part of DML) or Create, Alter, Drop, Truncate (part of DDL command) are executed on the publisher, the corresponding commands generated by SQL Server are stored in the Distribution database. Note: it’s best if we can maintain this database at shared path in such a way that the distributor is responsible for sharing data among the subscriber, I have also observed that if you keep the distribution database on the shared path than I would be convenient for another server to access that path easily otherwise it may prompt an security denied access error. Note: Distribution database can be placed on the same server as the publisher however its best practice to keep it on different server e.g. S3.

Let us now begin with the snapshot Replication and will cover Setting up the Distribution Database.

There are 3 steps involved for Configuring the Transactional Replication:

1.       Setting up the Distribution Database.

2.       Creating the publisher.

3.       Creating the subscriber

Setting up the Distribution Database:

Step1. Connect to the Microsoft SQL Server 2008 R2 Management Studio.

Step2. Right Click on the Replication and Select Configure Distribution as demonstrated in the screen below:

Step3. After Clicking on Configuration wizard following wizard will appear

Step4. Click Next. And choose Ditributor is a point to in respect to Replication which is responsible for sharing data among the subscribers or server on which the Replication will be configured will be Hosting the distribution database and second option is states that another server can be used as another host for distribution database as depicted in image below:

Step5: A new window appears as shown in the screen capture below:

Step6: Create a folder on any other drive/server to hold the Snapshot folder and Click on the Next button as shown in the screen depicted below. Note: Snapshot folder should not be in C drive or in that drive which has OS.

E.g. SnapShot Folder- -> \\servername\path to respective folder.

Step7: A new window appears as shown in the screen capture below and display information to store data and log file.

Step8:   A new window appears as shown in the screen below and Click on the Next button.

Step9: Click on the Next button as shown in the screen capture below:

Step10. Click on Finish button.

As soon as I click on Finish button it takes few seconds to completes its process and give you success wizard .After completing this step you will see an entry in master database for distribution DB as depicted below.

Step11: A following window represent that distribution point has been created in master.

Thanks for reading this. I’ll post other part soon.

To learn more about MVC please go to the following link.
MVC Articles

Enjoy Coding and Reading

Categories: SQL Server