SQL Server Replication - Transactional Replication type step by step

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 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.

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
                      

Select all Open in new window

 

Setting up Distributor

1- Right lick on Replication -> Configure Distributor -> Next
2- Since Distributor and Publisher are on the same server, we will choose the current server-> Next
3- 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->Next 5- Enter Distributor name, folder to store Distribution Database file and log file -> Next
6- Add current server as Publisher
7- Finish

Now, you can see Distribution Database in the System Databases folder

Setting up Publisher

1- Expand Replication folder, right-click on Local Publication -> New Publication -> Choose Sales_Retail
2- Choose the transaction publication type
3- Choose tables, views or objects need to been replicated. Somtimes, there is an error message if your tables do not have PRIMARY KEY
4-Choose Create snapshot immediately and keep the snapshot available to initialize subscriptions
5- 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
6- Enter publication name Sales_Retail
7- Finish
8- Expand Local Publication folder, you can see the new publication which was just created.

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
3- 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.
4- Choose Subscription Database  Sales_Retail. If the database does not exist, you can use above script to create it.
5- Distribution Agent Security -> Choose to use SQL Server Agent service account (not recommend)

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

7- Finish

8- Query the data from OrderDetail table on SQLServer2008R2. There are two rows:
We 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
                      

Select all 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).

Conclusion

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.