Snapshot Replication in SQL Server 2008: Part 1

Replication is one of the headstone features of SQL Server to keep data synchronized among various servers. There are basically three types of replication present in SQL Server. In this article I'll cover Snapshot Replication. As the name implies, “Snapshot” means it takes a snapshot of a publisher database whatever the occurrence, such as insert, update, delete and merge (part of DML) or Create, Alter, Drop, Truncate (part of DDL commands) that happen on a publisher database resides on server (for example S1) and needs to be reflected/replicated on the database residing on another server (for example S2). This change happens within a fraction of a second. 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 with them in this series of replication. These Musketeers are shown below:



1. Publisher
2. Subscriber
3. Distributor Database

Publisher

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

Subscriber

The Subscriber is a point where the subscriber database incurs the notification and a copy of the data from the distributor about the changes that has happened in the publisher database. The subscriber database generally lives on a different server, for example S2.

Distribution Database

A database that contains all the replication commands. Whenever an occurrence, such as insert, update, delete and merge (part of DML) or Create, Alter, Drop, Truncate (part of DDL commands) is 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 a shared path in such a way that the distributor is responsible for sharing data among the subscribers. I have also observed that if you keep the distribution database on the shared path then it would be convenient for another server to access that path easily otherwise it may cause a security denied access error.

Note: the Distribution database can be placed on the same server as the publisher, however its best practice to keep it on a different server, for example 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

Step 1

Connect to the Microsoft SQL Server 2008 R2 Management Studio.



Step 2

Right-click on the Replication and select Configure Distribution as demonstrated in the screen below:



Step 3

After clicking on the Configuration Wizard the following wizard will appear.



Step 4

Click Next and choose the distributor. That is a pointer to something in respect to Replication that is responsible for sharing data among the subscribers or servers on which the Replication will be configured and that will be hosting the distribution database and the second option states that another server can be used as another host for the distribution database as depicted in the image below:



Step 5

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



Step 6

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: the Snapshot folder should not be in the C drive or in the drive that has the OS.

For example SnapShot Folder- -> \\servername\path to respective folder.

Step 7

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



Step 8

A new window appears as shown in the screen below and click on the Next button.



Step 9

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



Step 10

Click on the Finish button.



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

Step 11

The following window indicates that the distribution point has been created in the master.



Thanks for reading this. I'll post another part soon.

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

MVC Articles

Enjoy Coding and Reading.