February 13, 2025
replication

Snapshot Replication in SQL Server

The simplest type of replication is snapshot replication. In snapshot the entire copy of the publisher is copied from the Publisher to the Subscriber’s. This article explains how to configure snapshot Replication in SQL Server 2008 R2?

Before start we need to learn some prerequisite knowledge about SQL server replication and snapshot replication. Some key point of snapshot replications are given bellow:

  • Every time the entire Publisher is replicated.
  • Primary key is not mandatory.

Configuration of Snapshot Replication
To configure snapshot replication three steps are required:

  1. Configuring Distribution
  2. Creating Publisher
  3. Creating Subscriber

Configuring Distribution
At first we need to configure a distributor database which manages the total replication process.

Creating Publisher
The steps to create publisher are given bellow:

Step1:
Connect to the server with actual server name.

Step2:
Expand the Replication folder and right click on Local Publications. Select New Publication.

Snapshot replicaion

Step3:
New Publication Wizard window will be open. Click next

New-Publication-Wizard

Step4:
Select the database that contains database objects for publications. For example select “TestDB” and click next.

Publication database

Step5:
Choose the appropriate publication type according to requirements. For now select Snapshot publication.

publication type

Step6:
Select the tables or others object to publish. We can select tables, stored procedures, views, and functions according to our choice. Here we will select only tables. Click next.

publication-article

Step7:
We can set filters to exclude unwanted rows. If required please add. Otherwise click next.

Filter-table-rows

Step8:
Specify the time when Snapshot Agent will run. Select “Create a snapshot immediately and keep the snapshot available to initialize subscriptions” and click next.
Also we can select a schedule to run the snapshot agent. We can modify it any time from the jobs.

Snapshot agent

Step9:
Need to provide agent security. Specify the account under which the agent will run. Click Security Settings tab.

Agent-security

Stept10:
Set the domain/machine account under which the snapshot agent process will run.
Select “Run under the SQL Server Agent service account”
Select “Using the following SQL Server login” and provides the credentials.
Click Ok.

snapshot-agent-security

Step11
Click next.

Step12
Click next.

Step13
Give a suitable name to the publisher and click finish.

Complete-the-wizard

Step14:
If every thing is ok publication creation will be completed.

Creating-Publication

Creating Subscriber
After the creation of publisher the next step is to create the subscriber for it. The steps to create subscriber are given bellow:

Step1:
Right click on created publisher and select New Subscriptions.

Subscription

Step2:
New Subscription Wizard window will be open. Click next

New-Subscription-Wizard

Step3
Click next.

New-Subscription-Wizard

Step4:
Choose distribution agent location. Select the first one and click next.

Distribution-Agent-Locatio

Step5:
Choose subscribers and specify each subscriptions database. We can use same or remote SQL server as subscriber. We can set multiple subscribers for one publisher. In the subscriber list the default server is local. If we want to use different server, we need to click Add Subscriber and provides the credentials. Remember that for connection, the actual server name is mandatory. We need to create subscriber database. We can do that either restoring the publisher database or create new database. If we restore the database, then the database name will appear in the dropdown. Here we will use remote server (SDEV). After that, click next.

Subscribers

Step6:
Provide the process account and connection options for each distribution agent. Click the button highlighted in the bellow image.

distribution-agent-security

Step7:
Provide distribution agent security. Specify the domain/machine account under which distribution agent will run.
Select “Run under the SQL Server Agent service account”
Provides the credential of SQL server
Click ok

distribution-agent-security

Step8:
Click next.

Step9:
Specify synchronization agent schedule as Run continuously and click next.

synchronization-schedule

Step10:
Specify initialize subscriptions “Immediately” and click next.

initialize-subscriptions

Step11:
Choose “Create the subscription” and click next.

Wizard-Actions

Step12
Click finish. If everything is ok subscription creation will be completed. Expand local publications node and subscriber will be displayed. If we log on to subscriber database we will find the publisher.

Creating-Subscriptions-finished
Snapshot-Replication

In this way we can create snapshot replication in SQL Server 2008 R2.

Rashedul Alam

I am a software engineer/architect, technology enthusiast, technology coach, blogger, travel photographer. I like to share my knowledge and technical stuff with others.

View all posts by Rashedul Alam →

3 thoughts on “Snapshot Replication in SQL Server

  1. Thanks for sharing a nice article about snapshot replication.
    I am searching for this. Lot of information are here. Also have picture which makes clear the text.

    Wish u all the best
    thanks..

Leave a Reply

Your email address will not be published. Required fields are marked *