To totally unlock this section you need to Log-in
Login
Transaction Replication is used when DML or DDL schema changes performed on an object of one database on one SQL Server Instance/server needs to be replicated immediately on the database residing on another SQL Server Instance/server.
Change in schema as well as in data will be reflected in replicated database immediately but this is depends on the network/internet setup you have between both SQL Server Instance/Server.
You can select any number of articles (object) as a candidate of replication no matter whether it is function, stored procedure or table. Just keep one thing in mind that any table you choose as a candidate of transaction replication, supposed to have Primary Key. Table without primary key will not be a part of transaction replication.
You can select two different SQL Server installation located in two different Server may or may not be in same premises but in same network. I have had two different instance of SQL Server 2012 in one server while writing this article.
Instance 1: “WIN-9H6QATRKY81\SQL2K12DEV”, we will call this instance as ServerA in this article.
Instance 2: “WIN-9H6QATRKY81\SQL2K12DEVTest”, we will call this instance as ServerB in this article.
If you are ready with AdventureWorks2012 database or any other database of your choice in one server and have another server available for subscriber, let us start creating replication.
Replication Entities
SQL Server replication is based on the “Publish and Subscribe” metaphor. Let us look at each of the individual components in detail.
- Publisher: it is a source database where replication starts. It makes data available for replication. Publishers define what they publish through a publication.
- Article: they are the actual database objects included in replication like tables, views, indexes, etc.
An article can be filtered when sent to the subscriber. - Publication: a group of articles is called publication. An article can’t be distributed individually. Hence publication is required.
- Distributor: it is intermediary between publisher and subscriber. It receives published transactions or snapshots and then stores and forwards these publications to the subscriber. It has 6 system databases including distribution.
- Subscriber: it is the destination database where replication ends. It can subscribe to multiple publications from multiple publishers. It can send data back to publisher or publish data to other subscribers.
- Subscription: it is a request by a subscriber to receive a publication. We have two types of subscriptions - push and pull.
- Push Subscriptions: with this subscription, the publisher is responsible for updating all the changes to the subscriber without the subscriber asking those changes. Push subscriptions are created at the Publisher server.
- Pull Subscriptions: with this subscription the subscriber initiates the replication instead of the publisher.
The subscriptions are created at the Subscriber server.
Replication Agents
Replication process works in the background with the help of jobs: these jobs are also called as agents. These jobs internally uses respective .exe files present in ...\110\COM folder.
All the agents information is present in Distribution db in the following tables.
dbo.MSxxx_agents dbo.MSxxx_history
Snapshot Agent
It is an executable file that prepares snapshot files containing schema and data of published tables and db objects.
It stores the files in the snapshot folder, and records synchronization jobs in the distribution database.
Distribution Agent
It is used with snapshot and transactional replication. It applies the initial snapshot to the Subscriber and moves transactions held in the Distribution db to Subscribers.
It runs at either the Distributor for push subscriptions or at the Subscriber for pull subscriptions.
Log Reader Agent
It is used with transactional replication, which moves transactions marked for replication from the transaction log on the publisher to the distribution db.
Each db has its own Log Reader Agent that runs on the Distributor and connects to the Publisher.
Merge Agent
It is used with merge replication. It applies the initial snapshot to the Subscriber and moves incremental data changes that occur.
Each merge subscription has its own Merge Agent that connects to both the Publisher and the Subscriber and updates both. It captures changes using triggers.
Queue Reader Agent
It is used with transactional replication with the queued updating option. It runs at the Distributor and moves changes made at the Subscriber back to the Publisher.
Unlike Distribution Agent and Merge Agent, only one instance of the Queue Reader Agent exists to service all Publishers and publications for a given distribution db.
The Steps
Create Distributor by login to ServerB (Subscriber) as we want to create Distributor in Subscriber server. If budget permits, it is good to have third dedicated server for distributor.
Select or add the server where you wanted to create distributor database, since we are already logged into to ServerB, we can directly select first radio button from following screen capture.
If your SQL Server Agent not set to start automatically at startup, it will ask you to start it as SQL Server Agent is responsible to run the job which will transmit data.
Give shared network path where replication can create snapshot and use that location as a primary source for data. Note that both or all three server should have proper access to this shared folder as we are going to save all script file along with distributor database in this path.
Give path where SQL Server can create “Distributor” database which is responsible to receive data from publisher and send it to subscriber.
Select Publisher server for distribution database. By default it has selected ServerB but we want ServerA to be the publisher so add ServerA instance here.
Login to publisher database and it will be selected.
Now deselect ServerB and select ServerA where we have just logged in from above screen.
We have provided password for our SA login password so that distributor can use this password while connecting to publisher. You can use any other login with sufficient permission.
Select “Configure Distribution” checkbox along with “Generate Script” check box.
Give the path for saving a script file. We have used the same shared path we have used before.
Here is the common template script we have received after clicking on the “Finish” button from above screen.
/****** Scripting replication configuration. Script Date: 11/25/2012 3:49:05 PM ******/
/****** 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: 11/25/2012 3:49:05 PM ******/
use master
exec sp_adddistributor @distributor = N'WIN-9H6QATRKY81\SQL2K12DEVTEST', @password = N''
GO
exec sp_adddistributiondb @database = N'distribution', @data_folder = N'E:\ReplicationShare', @log_folder = N'E:\ReplicationShare', @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 1
GO
use [distribution]
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'E:\ReplicationShare', 'user', dbo, 'table', 'UIProperties'
else
EXEC sp_addextendedproperty N'SnapshotFolder', N'E:\ReplicationShare', 'user', dbo, 'table', 'UIProperties'
GO
exec sp_adddistpublisher @publisher = N'WIN-9H6QATRKY81\SQL2K12DEV', @distribution_db = N'distribution', @security_mode = 0, @login = N'sa', @password = N'', @working_directory = N'E:\ReplicationShare', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'
GO
Now manually confirm that you have received “Distributor” database under “System Databases” in the ServerB or not.
Go to ServerA, this is the time to create publication now.
Select ServerA as a publication.
Give the same shared path to the replication to save and keep snapshot of publication.
Select the database you wanted to replicate. We have selected “AdventureWorks2012” from ServerA.
Select “Transactional Replication” from following screen:
Select Table, SP, Functions etc. which you want to replicate. Confirm the table you select for replication, supposed to have primary key. Select other supported objects of table too. For example you have one computed column populated from UDF in your table so you have to select that function along with table.
Click on next from following screen as we don’t want to specify any criteria for data. We want to transfer all data from our table.
Select checkbox for “Create a snapshot immediately”.
Click on “Security Settings” from following screen shot.
Provide sufficient credential here so that replication works smooth without any security issues.
Once you provide security credential and click on “Ok” button, you will get screen something like this:
Select both check box from following screen.
Give proper path to save script for your publication.
Here is the script generated for publication:
/****** Scripting replication configuration. Script Date: 11/25/2012 4:01:52 PM ******/
/****** 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: 11/25/2012 4:01:52 PM ******/ use master
exec sp_adddistributor @distributor = N'WIN-9H6QATRKY81\SQL2K12DEV', @password = N''
GO
exec sp_adddistributiondb @database = N'distribution', @data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12DEV\MSSQL\Data', @log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12DEV\MSSQL\Data', @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 1
GO
use [distribution]
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'
else
EXEC sp_addextendedproperty N'SnapshotFolder', N'\\win-9H6QATRKY81\ReplicationShare', 'user', dbo, 'table', 'UIProperties'
GO
exec sp_adddistpublisher @publisher = N'WIN-9H6QATRKY81\SQL2K12DEV', @distribution_db = N'distribution', @security_mode = 0, @login = N'sa', @password = N'', @working_directory = N'\\win-9H6QATRKY81\ReplicationShare', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'
GO
use [AdventureWorks2012]
exec sp_replicationdboption @dbname = N'AdventureWorks2012', @optname = N'publish', @value = N'true'
GO
use [AdventureWorks2012]
exec [AdventureWorks2012].sys.sp_addlogreader_agent @job_login = N'win-9H6QATRKY81\Administrator', @job_password = null, @publisher_security_mode = 0, @publisher_login = N'sa', @publisher_password = N' ', @job_name = null
GO
-- Adding the transactional publication
use [AdventureWorks2012]
exec sp_addpublication @publication = N'AdventureWorks2012', @description = N'Transactional publication of database ''AdventureWorks2012'' from Publisher ''WIN-9H6QATRKY81\SQL2K12DEV''.', @sync_method = N'concurrent', @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'continuous', @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, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO
exec sp_addpublication_snapshot @publication = N'AdventureWorks2012', @frequency_type = 1, @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 = 0, @active_end_date = 0, @job_login = N'win-9H6QATRKY81\Administrator', @job_password = null, @publisher_security_mode = 0, @publisher_login = N'sa', @publisher_password = N''
use [AdventureWorks2012]
exec sp_addarticle @publication = N'AdventureWorks2012', @article = N'Customer', @source_owner = N'Sales', @source_object = N'Customer', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'Customer', @destination_owner = N'Sales', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_SalesCustomer', @del_cmd = N'CALL sp_MSdel_SalesCustomer', @upd_cmd = N'SCALL sp_MSupd_SalesCustomer'
GO
Provide the name for your publisher. We have provided "AdventureWorks2012":
Now connect to ServerB and add “New Subscription”:
Subscriber needs publisher to get data so select out publisher we have created.
Our publication name was “AdventureWorks2012” which we have selected from following screen:
Select “Pull Subscription” as we want to run all replication related job in ServerB and pull the data from ServerA. In Push method, replication related job runs from my ServerA which is primary server for our application and we don’t want to add overhead of replication job.
If you already have Subscription database then select it otherwise create a new one. We have created new database named “AdventureWorks2012Subscriber”:
Our newly created subscription database “AdventureWorks2012Subscriber” is selected now.
SQL Server Agent would need proper credential to connect with Distributor database and subscription database. Give proper credential to keep your replication smooth.
Select “Run Continuously” for agent.
Select “Immediately” so that subscription gets populated immediately.
Select both the checkbox as usual.
Give the path for script of subscription.
Here is the script generated for subscription:
-----------------BEGIN: Script to be run at Publisher 'WIN-9H6QATRKY81\SQL2K12DEV'-----------------
use [AdventureWorks2012]
exec sp_addsubscription @publication = N'AdventureWorks2012', @subscriber = N'WIN-9H6QATRKY81\SQL2K12DEVTEST', @destination_db = N'AdventureWork2012Subscriber', @sync_type = N'Automatic', @subscription_type = N'pull', @update_mode = N'read only'
GO
-----------------END: Script to be run at Publisher 'WIN-9H6QATRKY81\SQL2K12DEV'-----------------
-----------------BEGIN: Script to be run at Subscriber 'WIN-9H6QATRKY81\SQL2K12DEVTEST'-----------------
use [AdventureWork2012Subscriber]
exec sp_addpullsubscription @publisher = N'WIN-9H6QATRKY81\SQL2K12DEV', @publication = N'AdventureWorks2012', @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'AdventureWorks2012', @distributor = N'WIN-9H6QATRKY81\SQL2K12DEV', @distributor_security_mode = 0, @distributor_login = N'sa', @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 = 20121125, @active_end_date = 99991231, @alt_snapshot_folder = N'', @working_directory = N'', @use_ftp = N'False', @job_login = N'win-9H6QATRKY81\Administrator', @job_password = null, @publication_type = 0
GO
-----------------END: Script to be run at Subscriber 'WIN-9H6QATRKY81\SQL2K12DEVTEST'-----------------
So finally we have implemented Transaction Replication. You can confirm whether replication is working or not by inserting few records in one of the table in ServerA which supposes to take effect immediately in ServerB’s database.
 |  |