SQL Server Replication: Snapshot step by step

Dung DinhData Platform Consultant
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.


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.


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
                      CREATE TABLE ProductPrice
                         ProductNumber nvarchar(25),
                         ProductName nvarchar(50),
                         Price money
                      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

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


Setting up Publisher

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

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.


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


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


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,.... 
Dung DinhData Platform Consultant

Comments (3)

Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Out {expletive deleted} standing article on a very complex subject.  Voting Yes.

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 !!
Dung DinhData Platform Consultant


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.

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.