Understanding replication with Implementation of Snapshot Replication in SQL Server 2012 (Part 1)

Understanding of replication with Implementation of Snapshot Replication in SQL Server 2012 (Part 1)

As per MSDN, replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Using replication, you can distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet.

There are four types of replication, excluding subtype, available in SQL Server, such as:

  • Merge Replication
  • Snapshot Replication
  • Transaction Replication
  • Pee-to-peer replication

We will cover Snapshot Replication in this article; remaining type of replication will be available in follow up blog post.

Before we start configuring replication, it is mandatory to understand some of the terminology we will use in replication.

Publisher: Publisher is a server which hosts the database which acts as a source of data to distribute among other databases.

Distributor: Distributor is a server which hosts the database which acts as a mediator between publisher and subscriber. It receives the data from publisher database and sends it to subscriber database. You can set distributor server same as publisher server but it is HIGHLY recommended that you keep distributer server/database in separate server so that publisher server doesn’t be heavily loaded.

Subscriber: Subscriber is the server which hosts the databases where publisher/distributer sends the data to.

You can set replication between any server(s) which can be accessible to each other via network, VPN, dial up or anything else. In production environment, I would prefer to have three different servers (if budget permits) one host publisher, second host distributor and third host subscriber but here for this article, I have installed two different instance of SQL Server 2012. You can even host two different virtual machines in your computer to test this article.

My first instance is WIN-9H6QATRKY81\SQL2K12DEV and I will refer it as ServerA in this article.

My second instance is WIN-9H6QATRKY81\SQL2K12DEVTest and I will refer it as ServerB in this article.

Snapshot Replication:

Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. When synchronization occurs, the entire snapshot is generated and sent to Subscribers. This is one of the very simple replication and easy to setup/maintain. In simple words, Snapshot replication creates snapshot of publisher database and send it to subscriber via distributor.

You should select Snapshot replication if your database has infrequent changes, your database is small in size or your database is having very large volume of data change in short duration. If these are not the case for you, you have to choose another replication not snapshot.

Before you actually proceed towards replication, you have to check few things.

  • List table, view, Stored procedure and any other SQL Server objects which you want to replicate
  • You have to have one shared folder which can be accessible from any of the servers which are part of replication in any capacity (Publisher, Distributer, and Subscriber). My shared folder is at \\win-9H6QATRKY81\ReplicationShare.

Now let us implement snapshot replication on AdventureWorks2012 database. If you don’t, already, have this database, kindly download it from here or use your own database.

1.) Connect to you SQL Server 2012 which you want as a publisher and right click “Replication” then click on “Configure Distribution” option. I connected to “ServerA

2.) Select the distributor server. If you want the current SQL Server 2012 instance to be act as distributor server, left the default selection as it is. That is what I did here for this article. If you wish, you can change selection via radio button and click on “Add” button to select another SQL server 2012 instance to work as distributor.

3.)    Give the path of snapshot folder where the snapshot will be created. This path should be accessible from all servers which are part of replication.  In my case, it is \\win-9H6QATRKY81\ReplicationShare.

4.)  You have to create or select distributor database. I have created new database with “Adv2012Distributor” name.

5.)    Select check box “Configure Distributor” and click on Finish.

though screen capture is not showing script check box selected above but if you would have selected it, it would have generated script to add distributor. script would look something like this:

[sourcecode language=”sql”]

/****** Scripting replication configuration. Script Date: 10/21/2012 10:19:49 AM ******/
/****** Please Note: For security reasons, all password parameters were scripted with either NULL or an empty string. ******/

/****** Installing the server as a Distributor. Script Date: 10/21/2012 10:19:49 AM ******/
use master
exec sp_adddistributor @distributor = N’WIN-9H6QATRKY81\SQL2K12DEV’, @password = N”
exec sp_adddistributiondb @database = N’Adv2012Distributor’, @data_folder = N’E:\Data’, @log_folder = N’E:\Data’, @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 1

use [Adv2012Distributor]
if (not exists (select * from sysobjects where name = ‘UIProperties’ and type = ‘U ‘))
create table UIProperties(id int)
if (exists (select * from ::fn_listextendedproperty(‘SnapshotFolder’, ‘user’, ‘dbo’, ‘table’, ‘UIProperties’, null, null)))
EXEC sp_updateextendedproperty N’SnapshotFolder’, N’\\win-9H6QATRKY81\ReplicationShare’, ‘user’, dbo, ‘table’, ‘UIProperties’
EXEC sp_addextendedproperty N’SnapshotFolder’, N’\\win-9H6QATRKY81\ReplicationShare’, ‘user’, dbo, ‘table’, ‘UIProperties’

exec sp_adddistpublisher @publisher = N’WIN-9H6QATRKY81\SQL2K12DEV’, @distribution_db = N’Adv2012Distributor’, @security_mode = 1, @working_directory = N’\\win-9H6QATRKY81\ReplicationShare’, @trusted = N’false’, @thirdparty_flag = 0, @publisher_type = N’MSSQLSERVER’

6.)Let us confirm whether our distributor database is created or not in ServerA. You can find it under “System Databases” Group. Refer following screen capture

7.)Once you are done with “Distributor” configuration, you should proceed towards “Publication”.  Connect to the server which you want to make as publisher, in my case, it is ServerA. Follow the given screen capture to know the option to click

8.) You will have to select the publisher database, in our case, it is AdventureWorks2012

9.)    Now this is the time to choose which replication you wanted create. At the point, we will create “Snapshot Replication”

10.) Now, you have to select table, view, stored procedure, indexed view and/or user defined functions. You can select all if you want. As of now, I have selected only two tables.

11.) Now if you don’t wish to replicate some data based on filter, you can have filter by click on “Add” button but I didn’t choose any filter and clicked on “Next” button in following screen or wizard.

12.) we are instructing that snapshot of publisher database should be created at the moment by first check box. scheduling is VERY IMPORTANT for replication as whatever change you will make in data in your publisher database, it will take effect in subscriber database(s) once the schedule time reach. Schedule should be very from case to case, it shouldn’t be too low otherwise before your snapshot applies to subscriber, your second run of snapshot will be reached. As against, it shouldn’t be too high that subscriber doesn’t have point-in time data. You can click on “Change” button and run it at every 10 minutes for now.

13.) this step is very important. You have to give proper login credential so that replication doesn’t meet with any permission issue. I would suggest you give windows authentication there in form of domainName\Login and Password.

14.) select both the check box given in following screen.

15.) give the proper path so that you will have .SQL file of publication script.

16.) Now give publication name here. one publisher database may have few different publication.

Once you will click on “Finish” button in above given screen, your publisher will be created and you will have publication script as follow:

[sourcecode language=”sql”]
use [AdventureWorks2012]
exec sp_replicationdboption @dbname = N’AdventureWorks2012′, @optname = N’publish’, @value = N’true’
— Adding the snapshot publication
use [AdventureWorks2012]
exec sp_addpublication @publication = N’AdvertureWorks2012AddressObjects’, @description = N’Snapshot publication of database ”AdventureWorks2012” from Publisher ”WIN-9H6QATRKY81\SQL2K12DEV”.’, @sync_method = N’native’, @retention = 0, @allow_push = N’true’, @allow_pull = N’true’, @allow_anonymous = N’true’, @enabled_for_internet = N’false’, @snapshot_in_defaultfolder = N’true’, @compress_snapshot = N’false’, @ftp_port = 21, @ftp_login = N’anonymous’, @allow_subscription_copy = N’false’, @add_to_active_directory = N’false’, @repl_freq = N’snapshot’, @status = N’active’, @independent_agent = N’true’, @immediate_sync = N’true’, @allow_sync_tran = N’false’, @autogen_sync_procs = N’false’, @allow_queued_tran = N’false’, @allow_dts = N’false’, @replicate_ddl = 1

exec sp_addpublication_snapshot @publication = N’AdvertureWorks2012AddressObjects’, @frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 8, @frequency_subday_interval = 1, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1

use [AdventureWorks2012]
exec sp_addarticle @publication = N’AdvertureWorks2012AddressObjects’, @article = N’Address’, @source_owner = N’Person’, @source_object = N’Address’, @type = N’logbased’, @description = null, @creation_script = null, @pre_creation_cmd = N’drop’, @schema_option = 0x000000000803509D, @identityrangemanagementoption = N’manual’, @destination_table = N’Address’, @destination_owner = N’Person’, @vertical_partition = N’false’

use [AdventureWorks2012]
exec sp_addarticle @publication = N’AdvertureWorks2012AddressObjects’, @article = N’AddressType’, @source_owner = N’Person’, @source_object = N’AddressType’, @type = N’logbased’, @description = null, @creation_script = null, @pre_creation_cmd = N’drop’, @schema_option = 0x000000000803509D, @identityrangemanagementoption = N’manual’, @destination_table = N’AddressType’, @destination_owner = N’Person’, @vertical_partition = N’false’

17.) Now, we will create subscription. please connect with the SQL Server 2012 which you want to be subscription. In my case, it is ServerB. follow the steps given in following screen capture.

18.) For creating subscriber, you have to connect with publisher server.

19.) after connecting to publisher server, select the publication we have created.

20.) I don’t want to run SQL Server Agent job of replication at Publisher server because publisher server suppose to have load from various sources so I would go for PULL subscription so that replication job will run from ServerB and fetch the data from ServerA.

21.) Select or Create subscription database. since this is first time we are setting this subcription, we have no chance to have already created database so we will create new DB with name “AdventureWorks2012Subs”.

22.) This steps is VERY IMPORTANT, You must give proper login credential here. I would suggest to make sure that credential you are giving here, is having proper access of distributor and subscriber server. You can give two separate login credential here. I would like to go for windows authentication here too. Highly recommend, don’t go with “Impersonate Process account” as shown in following screen capture.

23.) select “Run Continuously” for “Agent Schedule” so that it keeps running at the interval we have selected.

24.) now select “Immediately” for “Initialize When” drop down list so that your configuration of snapshot agent takes effect immediately. if your credential provided in above screen is not proper, replication will not be able to move data between server.

25.) select both the check box same as we did two times before.

26.) give the proper path to save .SQL file for your Subscription script for future use.

Subscription scrip would look something like this:

[sourcecode language=”sql”]—————–BEGIN: Script to be run at Publisher ‘WIN-9H6QATRKY81\SQL2K12DEV’—————–
use [AdventureWorks2012]
exec sp_addsubscription @publication = N’AdvertureWorks2012AddressObjects’, @subscriber = N’WIN-9H6QATRKY81\SQL2K12DEVTEST’, @destination_db = N’AdventureWorks2012Subs’, @sync_type = N’Automatic’, @subscription_type = N’pull’, @update_mode = N’read only’
—————–END: Script to be run at Publisher ‘WIN-9H6QATRKY81\SQL2K12DEV’—————–

—————–BEGIN: Script to be run at Subscriber ‘WIN-9H6QATRKY81\SQL2K12DEVTEST’—————–
use [AdventureWorks2012Subs]
exec sp_addpullsubscription @publisher = N’WIN-9H6QATRKY81\SQL2K12DEV’, @publication = N’AdvertureWorks2012AddressObjects’, @publisher_db = N’AdventureWorks2012′, @independent_agent = N’True’, @subscription_type = N’pull’, @description = N”, @update_mode = N’read only’, @immediate_sync = 1

exec sp_addpullsubscription_agent @publisher = N’WIN-9H6QATRKY81\SQL2K12DEV’, @publisher_db = N’AdventureWorks2012′, @publication = N’AdvertureWorks2012AddressObjects’, @distributor = N’WIN-9H6QATRKY81\SQL2K12DEV’, @distributor_security_mode = 1, @distributor_login = N”, @distributor_password = null, @enabled_for_syncmgr = N’False’, @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20121021, @active_end_date = 99991231, @alt_snapshot_folder = N”, @working_directory = N”, @use_ftp = N’False’, @job_login = null, @job_password = null, @publication_type = 0
—————–END: Script to be run at Subscriber ‘WIN-9H6QATRKY81\SQL2K12DEVTEST’—————–

Finally we have successfully implemented Snapshot replication. You can confirm whether replication is working or not by insert/update few records in “Person.Address” table which was one of our two articles (table) we have added in this replication.

Please not that insert/update you have made, will not be immediately replicated. It will be replicated when you

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah



Sharing IT tips at “Quick Learn

Note: Microsoft Books online is a default reference of all articles.

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.