SQL Server Replication - Transactional Replication type step by step

Dung DinhData Platform Consultant
CERTIFIED EXPERT
Published:
Updated:
In previous article, I talked about basic concepts and terms of replication using SQL Server. Now, we're going to investigate one of replication types that is called transactional replication. How do we configure this type? When will we use it?
 

Definition and mechanism

Transactional replication copies data uni-directionally from the source database to the target database. This replication type uses the log files associated with the source database to keep data in sync. If a change is made to the source database, that change can be synched to the target database immediately, or the synchronization can be scheduled. 

The Log Reader Agent scans the transaction log of the publication database and examines each committed transaction to determine whether any changes affect the replicated articles. If they do, those changes are logged to the distribution database. The Distribution Agent then replicates those changes to the Subscriber (source: Microsoft TechNet).
Transactional-Replication.gif

Transactional Replication Configuration

As you know,  replication architecture has three components: Publisher, Distributor and Subscriber that we need to configure.
 

Scenario

In a retail environment, management wants to know how many items are sold hourly. To avoid impacting the Sales database, they process reports and statistics on another server.
 

Architecture

Basically, we put Publisher and Distributor on the same SQLServer2012 Instance and Subscriber on another SQLServer 2008R2 Instance.
TransactionalReplication.png


Setting up Sales Database


CREATE DATABASE Sales_Retail
                      ON PRIMARY
                      (
                      NAME=Sales_Retail,
                      FILENAME='D:\DataSQL2012\Sales_Retail.mdf',
                      SIZE=5MB,
                      MAXSIZE=UNLIMITED,
                      FILEGROWTH=10MB
                      )
                      LOG ON
                      (
                      NAME = Sales_Retail_log,
                      FILENAME='D:\DataSQL2012\Sales_Retail_log.mdf',
                      SIZE=10MB,
                      MAXSIZE=UNLIMITED,
                      FILEGROWTH=10MB
                      );
                      GO
                      USE Sales_Retail
                      GO
                      CREATE TABLE OrderDetail
                      (
                          [SalesOrderID] [int] NOT NULL,
                      	[CarrierTrackingNumber] [nvarchar](25) NULL,
                      	[OrderQty] [smallint] NOT NULL,
                      	[ProductID] [int] NOT NULL,
                      	[SpecialOfferID] [int] NOT NULL,
                      	[UnitPrice] [money] NOT NULL,
                      	[UnitPriceDiscount] [money] NOT NULL,
                      	[LineTotal]  AS (isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty],(0.0))),
                      	[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
                      	[ModifiedDate] [datetime] NOT NULL,
                      )
                      GO
                      -- Insert data
                      INSERT INTO OrderDetail(SalesOrderID,CarrierTrackingNumber,OrderQty,ProductID,SpecialOfferID,UnitPrice,UnitPriceDiscount,rowguid,ModifiedDate)
                      VALUES (1,'4911-403C-98',1,776,1,2024.994,0,'B207C96D-D9E6-402B-8470-2CC176C42283','2015-01-01'),
                             (1,'4911-403C-98',3,777,1,2024.994,0,'7ABB600D-1E77-41BE-9FE5-B9142CFC08FA','2015-01-01')
                      GO

Open in new window

 

Setting up Distributor

1- Right lick on Replication -> Configure Distributor -> Next
Distributor1.png2- Since Distributor and Publisher are on the same server, we will choose the current server-> Next
Distributor2.png3- Choose No, I will start SQL Server Agent service manually -> Next
4- Enter folder for Snapshot folder. This folder will be used for Snapshot Replication type->NextDistributor3.PNG5- Enter Distributor name, folder to store Distribution Database file and log file -> Next
Distributor4.PNG6- Add current server as Publisher
7- Finish

Distributor5.PNGNow, you can see Distribution Database in the System Databases folder
Distributor7.PNG

Setting up Publisher

1- Expand Replication folder, right-click on Local Publication -> New Publication -> Choose Sales_Retail
Distributor8.PNG2- Choose the transaction publication type
Distributor9.PNG3- Choose tables, views or objects need to been replicated. Somtimes, there is an error message if your tables do not have PRIMARY KEY
Distributor10.PNG4-Choose Create snapshot immediately and keep the snapshot available to initialize subscriptions
Distributor11.PNG5- Snapshot Agent Security -> Use SQL Server Agent service account, depending on your requirements. This option is not recommended; you should use the domain Window account as best practice -> Next
Distributor12.PNG6- Enter publication name Sales_Retail
Distributor13.PNG7- Finish
Distributor14.PNG8- Expand Local Publication folder, you can see the new publication which was just created.
Distributor15.PNG

Setting up Subscriber

The next step is to set up the Subscriber instance to receive the replicated OrderDetail table from the Sales_Retail publication instance.
1- Connect to the SQLServer2008R2 Instance, expand Replication folder, right-clik on Local Subscriptions and create New Subscriptions -> Next
2- Find SQL Server Publisher -> Connect to the publisher that was created on SQLServer2012 -> Next
Distributor16.png3- Choose the Distribution Agent Location. Depending on your system, you determine on which server to run the agents. For the desmontration purposes, I chose Run all agents at the Distributor, meaning that all agents will run on SQLServer2012.
Distributor17.PNG4- Choose Subscription Database  Sales_Retail. If the database does not exist, you can use above script to create it.
Distributor18.PNG5- Distribution Agent Security -> Choose to use SQL Server Agent service account (not recommend)
Distributor19.PNG
6- Synchronization Schedule. You can schedule to synchronize data using
  • Run continuouly: run synchronization immediately
  • Run on demand only: only run if required
  • Define a specific schedule
Distributor20.png
7- Finish
Distributor21.PNG
8- Query the data from OrderDetail table on SQLServer2008R2. There are two rows:
Distributor22.PNGWe will insert two new rows into OrderDetail table on SQLServer2012 and re-run replication job to see the result.
INSERT INTO OrderDetail(SalesOrderID,CarrierTrackingNumber,OrderQty,ProductID,SpecialOfferID,UnitPrice,UnitPriceDiscount,rowguid,ModifiedDate)
                      VALUES (3,'4911-403C-98',1,778,1,2024.994,0,'B207C96D-D9E6-402B-8470-2CC176C42283','2015-01-01'),
                             (4,'4911-403C-98',3,779,1,2024.994,0,'7ABB600D-1E77-41BE-9FE5-B9142CFC08FA','2015-01-01')
                      GO

Open in new window


Expand Local Publication-> right-click Sales_Retail publication database -> Reinitialize. After that, query data from OrderDetail table on SQLServer2008R2 again. The result is there is 4 rows (two new rows inserted).

Distributor23.pngConclusion

Transaction Replication is appropriate in each of the following cases:

  • You want incremental changes to be propagated to Subscribers as they occur.
  • The application requires low latency between the time changes are made at the Publisher and the changes arrive at the Subscriber.
  • The application requires access to intermediate data states. For example, if a row changes five times, transactional replication allows an application to respond to each change (such as firing a trigger), not simply the net data change to the row.
  • The Publisher has a very high volume of insert, update, and delete activity.
  • The Publisher or Subscriber is a non-SQL Server database, such as Oracle.


Consider to use Transactional Replication

However, whenever you want to use Transaction Replication you should consider some factors:

  • Transaction log space: because Transactional Replication is associated with log files, if size of the log files is large then it will be impact to performance
  • Disk space for the distribution database
  • Primary keys for each published table: this type always requires that each table must have a primary key
  • Trigger
  • Large Object data types
For more detail, you can refer this TechNet article although it is about SQL 2008R2, but I think that it's still useful this case.
4
25,353 Views
Dung DinhData Platform Consultant
CERTIFIED EXPERT

Comments (4)

CERTIFIED EXPERT

Commented:
Hi Dung Dinh :)
You wrote very nice article on Merge replication and transaction replication.
Could you please write articles for snapshot and peer to peer replication.
It would be a great help from you.
Voted Yes this article :)
Dung DinhData Platform Consultant
CERTIFIED EXPERT

Author

Commented:
@Yashwant Vishwakarma:  I have just written a new article for Snapshot. You can read it from https://www.experts-exchange.com/articles/28397/SQL-Server-Replication-Snapshot-step-by-step.html
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Voted Yes.  Thanks for publishing this Dung.  I just started a Data Architect position with a new employer and have to dig into Replication for the first time since SQL Server 7.

Commented:
Hi Dung,

While creating Subscriber-
i got the error               : Transaction (Process Id xxx)  was deadlocked on lock resources with another process and has been chosen as deadlock victim
I also got the warning : Request to run job....refused because the job is already running  from user

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.