<

SQL Server Replication: Snapshot step by step

Published on
10,914 Points
7,614 Views
3 Endorsements
Last Modified:
Snapshot type is one of four popular types of SQL Replication techniques. In this article,  I will not only demonstrate how we configure this type but also when we need it in real situations.
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 components and data flow


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.

Snapshot-Replication.png



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)

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

Snapshot_Distributor1.PNG
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

Snapshot_Distributor1_SnapshotFolder.PNGFrom 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

Snapshot_Distributor_Name.PNG5- Add current server as Publisher

Snapshot_Distributor_Publiser.PNG6- Finish

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

Snapshot_Distributor_Database.PNG 


Setting up Publisher

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

Snapshot_LocalPublication.png
2- Choose Snapshot Replication type -> Next

Snapshot_SnapshotType.PNG3- Choose all columns of ProductPrice table -> Next

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

Snapshot_Schedule.png5- 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

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

Snapshot_Publication.PNG 

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

Snapshot_Subscription1.PNG3- 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.
 Snapshot_Subscription2.PNG4- 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.

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

Snapshot_Subscription4.PNG8- Finish. You can see a new subscription created

Snapshot_Subscription5.PNG 

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'

Open in new window


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

Snapshot_Reinitialize.PNGBecause 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.

Snapshot_Schedule_Job.pngOK... 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'

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,.... 
 
3
Comment
Author:Dung Dinh
3 Comments
LVL 66

Expert Comment

by:Jim Horn
Out {expletive deleted} standing article on a very complex subject.  Voting Yes.
0
LVL 8

Expert Comment

by:Yashwant Vishwakarma
Hi Dung,
Thank you for the article , nicely explained.
Can we take three instances also for this practical one for distributor, second for publisher and third for subscriber or is it necessary to distributor and publisher on same server.

Voted YES !!
0
LVL 9

Author Comment

by:Dung Dinh
Yes, we can. Actually, Replication has two architectures in overview.

The first architecture, we can install Publisher and Distributor on the same instance ( same server).

The second one, we can install three components on three different instances even three servers. Obviously, those servers must communicate together.

In real situations, the architecture can be more complicated. A Publisher is also Subscriber or otherwise.
1

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Join & Write a Comment

Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month