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

 

Setting up Distributor

1- Right lick on Replication -> Configure Distributor -> Next
Distributor1.png

2- Since Distributor and Publisher are on the same server, we will choose the current server-> Next
Distributor2.png

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

 

 

 

 

 

 

 

 

 

 

 

 

5- Enter Distributor name, folder to store Distribution Database file and log file -> Next
Distributor4.PNG

6- Add current server as Publisher
7- Finish

Distributor5.PNG

Now, 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.PNG

2- Choose the transaction publication type
Distributor9.PNG

3- Choose tables, views or objects need to been replicated. Somtimes, there is an error message if your tables do not have PRIMARY KEY
Distributor10.PNG

4-Choose Create snapshot immediately and keep the snapshot available to initialize subscriptions
Distributor11.PNG

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

6- Enter publication name Sales_Retail
Distributor13.PNG

7- Finish
Distributor14.PNG

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

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

4- Choose Subscription Database  Sales_Retail. If the database does not exist, you can use above script to create it.
Distributor18.PNG

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

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

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

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.

Advertisements

One thought on “SQL Server Replication – Transactional Replication type step by step

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s