SQL Server Replication: Snapshot step by step

When you work with Transactional Replication and Merge Replication or Peer-to-Peer Replication, you will see the initial phase of all replication types is to run a snapshot synchronization. It means that all types of replication use a snapshot to initialize Subscriber.

Definition and Mechanism

Snapshot Replication creates an identical copy of all replicated objects of Publications that you want to replicate each time it runs. It means that there is no synchronization capacity. It always copies the entire data set and overwrites any external changes that might have been applied to the target database.

Snapshot use the snapshot folder that is a shared folder set up on Distributor. And then Snapshot use the bcp utility to copy content of each table to the snapshot folder. You can click here to understand how Snapshot Replication works.

Snapshot Replication Configuration

Replication architecture has three components: Publisher, Distributor and Subscriber that we need to configure.
 

Scenario

In the retail system, we have list of price of all products at Central database. We also have many stores. The list is updated at the end of every month. Our system is required to sync the list to all sub-databases that reside at different locations whenever it is updated.
 

Architecture

Basically, we put Publisher and Distributor on the same SQLServer2014 Instance and Subscriber on another SQLServer2014_1 Instance.

Create ProductPrice table

This step is to create an article for applying Snapshot type. If you do not have Sales database, please get the script from Transactional Replication.

USE Sales_Retail
                      GO
                      CREATE TABLE ProductPrice
                      (
                         ProductNumber nvarchar(25),
                         ProductName nvarchar(50),
                         Price money
                      )
                      GO
                      INSERT INTO ProductPrice
                      VALUES('SA-M198','LL Mountain Seat Assembly',133.34)
                      ,('SA-R430','ML Road Seat Assembly',147.14)

Select all Open in new window

 

Setting up Distributor

1- Right lick on Replication -> Configure Distributor -> Next. Since Distributor and Publisher are on the same server, we will choose the current server-> Next


2- Choose No, I will start SQL Server Agent service manually -> Next
3- Enter folder for Snapshot folder. This folder will be used for Snapshot Replication type->Next

From above window, you can see a warning message.  This is because we are using the local folder for Snapshot Folder. However, we should use a network path in our real situation. This will make sure that Distributor can push subscriptions to all Subscribers or each Subscriber can pull subscriptions.

4- Enter Distributor name, folder to store Distribution Database file and log file -> Next

5- Add current server as Publisher

6- Finish

Now, you can see Distribution Database in the System Databases folder

 

Setting up Publisher

1- Expand Replication folder, right-click on Local Publication -> New Publication -> Choose Sales_Retail


2- Choose Snapshot Replication type -> Next

3- Choose all columns of ProductPrice table -> Next

4- Filter Table Rows appears -> Next
5-Choose Create snapshot immediately and keep the snapshot available to initialize subscriptions

5- Snapshot Agent Security -> Use SQL Server Agent service account or Window account.  It depends on your requirements. However, you should use the domain Window account as best practice -> Next

6- Enter publication name Sales_Snapshot_Price -> Finish
7-Expand Local Publication folder, you can see the new publication which was just created.

 

Setting up Subscriber

The next step is to set up the Subscriber instance to receive the replicated ProductPrice table from the Sales_Retail publication instance.

1- Connect to the SQLServer2014_1 Instance, expand Replication folder, right-click on Local Subscriptions and create New Subscriptions -> Next
2- Find SQL Server Publisher -> Connect to the publisher that was created on SQLServer2014 -> Next

3- Choose the Distribution Agent Location. Depending on your system, you determine on which server to run the agents. For the demonstration purposes, I chose Run all agents at the Distributor, meaning that all agents will run on SQLServer2014 instance.
 4- Choose Subscription Database  Sales_Retail. If the database does not exist, you can use above script to create it -> Next

5- Distribution Agent Security appears, enter a Window account -> Next. Because we choose to run all agents at Distributor  (SQLSERVER2014 instance) so we need to make sure that Distribution Agent is allowed to work on SQLSERVER2014_1.

6- Choose Run Continuously -> Next
7- Choose Immediately so that our configurations of snapshot agent takes effect immediately.

8- Finish. You can see a new subscription created

 

In case, you set up Snapshot successfully, you can see data of ProductPrice table on SQLSERVER2014_1 instance. This is because we chose Immediately at step 7 above.

Update price of LL Mountain Seat Assembly product to $200
 

UPDATE [dbo].[ProductPrice]
                      SET Price = 200
                      WHERE ProductNumber = 'SA-M198'

Select all Open in new window

9- Right-click on the Publication created above, choose Reinitialize-> Use a new snapshot.

Because we choose Immediately option, so Snapshot agent is only run at initial phase. If we want to sync again, we need to set up schedule next step.

10- When we create a Local Publication successfully, a SQL Agent job is also created. In my case, it is named as DUNGDT\SQLSERVER2014-Sales_Retail-Sales_Snapshot_Price-1. This job is not set up any schedule. For the demonstration purpose, I need to schedule to allow this job to run every minute.

OK... Let's update price of LL Mountain Seat Assembly product to $300 and wait about one minute and see what will happen?
 

UPDATE [dbo].[ProductPrice]
                      SET Price = 300
                      WHERE ProductNumber = 'SA-M198'

Select all Open in new window

Conclusions

As I said above, Snapshot Replication type always copy all replicated objects so you should be carefully while using this type because we are able to face issues of bandwidth , performance even storage. In my experience, I list out some cases that we can apply Snapshot.

1. If data is not change infrequently as my example above.
2. Small volumes data.
3. If you accept data that are out of date in a period of time. It means that your system accept to tolerant latency data. 
4. A large volume of changes occurs over a short period of time. In case, you know that there is much data that updated over short period of time then you can apply Snapshot instead of Transactional Rep,....