Snapshot Replication On SQL Server

Snapshot technology is used during the first synchronization in other types of replication. Below you can find more articles about the types of replication.

“Transactional Replication On SQL Server“,

“Peer to Peer Transactional Replication On SQL Server“,

“Merge Replication On SQL Server”

With Snapshot Replication, the current copy of the data is transferred to the other node and no changes are replicated to the second server/instance. When the synchronization is running again, all data is transferred again. This cycle continues every time.

It is wise to use when regularly to transfer small tables to another server/instance.

Step-by-step Snapshot Replication:

If you did not configure the distributor on the first server, you can do this as described below. If you have previously configured the distributor, you can skip the section below and pass to the section we define publication.

First, in the AdventureWorks2014 database, let’s create a table that we will replicate with the help of the following script.

Then click Configure Distribution as below.

On the next screen, click Do not show this page again and click next.

On the next screen, we make our selections as below and we create the Distributor on the server where we install.

As a result of these operations, the distributor database will be created.

Click Next to proceed.

On the next screen It wants a share to hold snapshot files. I explained how to create a shared folder in the article named “How To Define a Share and Map This Share To Windows“.

On the shared folder, you must authorize the SQL Server service account and the SQL Server Agent service account that is used by two instances, and the user you are installing.

SQL Server Agent service accounts must be windows account. A local user cannot see the shared folder from different server.

I created a folder named Replication on Sunucu1 and gave the necessary privileges and I wrote the share address as Snapshot folder. We’re moving forward by clicking Next.

On the next screen, we write the path information for the data and log files of the distribution database as follows and we click next.

On the next screen we specify Publisher that this distribution database will use. We will install Publisher and distributor on the same instance, so we select the same instance and click Next.

On the next screen, click Next and Finish to complete the distributor configuration when Configure Distribution is selected.

After the installation is complete, you should see the distribution database in the system databases as follows.

After our Distributor configuration is complete, we click New Publication on the first instance by right-clicking Local Publications.

We select the database that we will replicate as follows.

Select Snapshot Publication as follows and click Next.

Since we are going to transfer only the dbo.SnapshotReplication table, we select the following and click next.

In the next step, we can add a filter by clicking Add from the following screen to replicate a certain part of the table. We proceed by clicking next without any filtering.

The next screen asks whether the snapshot of the data is to be taken now or later. By selecting “Create a snapshot right now and keep the snapshot available to initialize subscription”, we specify that we want the snapshot to be taken now, and we’re proceeding by clicking next.

The next screen aks the user accounts that the Snapshot Agent will use. Click Security Settings to set related users.

The minimum rights of the user you set for Snapshot Agent:

• Must be db_owner in the Distribution database.

• Must be db_owner in publication database.

• Must have write right on the snapshot share.

In my own test environment, two sql server agent instances use the same windows sql server agent service account and have the necessary privileges. I have previously authorized this user on the snapshot share. So I’m going to set it as follows.

On the next screen, click “Next” while selecting “Create the publication”.

On the next screen, we give a name to Publication, and we click finish to complete the publication installation.

In the next step, right click on Local Subscription on the first instance and click New Subscriptions.

On the next screen, select the SnapshotPublication that we defined earlier and click Next.

The next screen asks how to transmit data to subcriptions. So the data will be transferred from the Distributor to the subcscriber or the subscriber will get the data from the distributor. These methods are called Push or Pull Subscription.

If you have read the article “SQL Server replication Types“, I will continue with the example there.

The question is:

Will the stock tracking staff distribute the newspapers to the subscribers? ,

or

Subscribers go to the newspaper store and get the newspaper of theirself?

You can find detailed information about push and pull subscription in the article named “SQL Server replication Types”

As Microsoft recommends, for easy management, we select push subscriptions and set the distrubution agent to run in the instance where the distributor is located, and proceed by clicking next.

The next screen will display the first instance as Subscriber.

We want to select the second instance as subscriber, click Add Subscriber and add the second instance. And we make our choice as follows.

The next screen asks which user “Distribution Agent” will work with. Click the “….”

The minimum rights of the user you set for the Distribution Agent:

• Must be db_owner in the Distribution database.
• If you will use Pull Subscription, must be db_owner in the subscription database.
• Must have read rights in the snapshot share.
• Must be a member of Publication Access List (PAL).

To make the user a member of the PAL, right-click the on publication  in the publisher’s instance and click Properties.

As follows, we go to “Public Access list”to see if our own user is listed.

If not, click Add to add our user to this list. If the user does not appear when we click Add, we will see a warning like below.

The alert says that the user must be defined in publisher and Distributor instance to be listed here and that we must have access to the AdventureWorks2014 database that we will replicate.

If your user is not listed here, you must define your user on the instance of publisher and distributor and authorize it on AdventureWorks2014.

Then go back to Distribution Agent Security. I continue by selecting “Run under the SQL Server Agent service account” and “By impersonating the process account”.

I have given the necessary privileges to my SQL Server Agent Service account.

The next screen asks how to do the synchronization.

If we choose “Run continuous”, it will be synchronized continuously and will be almost real-time.

If we select “Run on demand only,” it only works when we trigger.

If we choose “Define schedule” we will ensure that it runs regularly at certain intervals.

Because we want to replicate it once a day, we choose “Define schedule” and set it as follows to run once a day.

On the next screen, select Immediately to transfer the data immediately to the subscriber and click next.

On the next screen, select “Create the subscription (s)” and click Next and Finish to complete the process.

The job we defined will transfer the SnapshotReplication table in the AdventureWorks2014 database in the first instance to the AdventureWorks2014 database in the second instance.

As we mentioned at the beginning of the article, the job will transfer the whole table every day. If you want to run the snapshot at any time, you can right-click on SnapshotPublication via SSMS and click on Reinitialize All Subscriptions.

Click “Mark For Reinitialization” on the following screen. After a while, the process will be completed and we will be able to see that the data is transferred to second instance.