Replicating Stored Procedure Execution

In OLTP environments, you often find batch jobs that move historical data to archive tables. In addition, there are often some batch jobs that purge historical data from the OLTP tables. These types of jobs can perform a lot of transactions and create additional overhead for the OLTP system, hurting overall performance, especially if you combine archiving and purging with SQL Server Transaction Replication.

One way to help avoid this overhead is to replicate stored procedure execution, instead of replicating all of the transactions from the publisher to the subscribers when archiving or purging data.

Let’s consider two servers. “EBONY” is the primary server and “MAK” is the distributor and subscriber that replicates the database tables using a PULL subscription. Let’s consider that we are replicating all columns of two articles “Orders” and “Order_Details” from the database “Sales” from the primary server “EBONY” to the database “Sales” in the secondary server “MAK”. We are not doing any horizontal or vertical partitioning of the tables.

In addition, if we are assuming that we are moving historical data from these two tables, and then purging the data, the purged transaction gets replicated to the subscriber. In order to reduce that overhead incurred, let’s create a procedure called “USP_Archive_Purge” that archives the data to “Orders_Archive” and “Order_Details_Archive,” and then purge the data.

To demonstrate this, let’s simulate this OLTP replicated environment step-by-step.

Step 1

Let’s assume that the tables “Orders” and “Order_Details” exist in the database “Sales” on the primary server “EBONY”

Use master
go
Create database Sales
Go
use Sales
go
Create table Orders (Order_id int constraint Orders_PK primary key , custid int,
Date datetime, Total_Amount money, ship_address varchar(300))
Go
Create table Order_Details (order_details_id int constraint order_details_PK primary key,
Order_id int constraint Orders_FK foreign key references Orders(Order_Id), item_id int,
quantity int, unit_price money, discount decimal (5,2))
Go

Now, let’s assume the tables “Orders” and “Order_Details” are already being replicated from the primary server “EBONY” to the secondary server “MAK” (using Transactional replication).

Here, “Sales” is the publication, “EBONY” is the publisher, “MAK” is the subscriber, and “Sales” in “MAK” is the subscription. [Fig 1.1]

[Fig 1.1]

Step 2

Now, let’s insert some data to those tables and replicate it from the primary server to the secondary server.

use Sales
go
insert into Orders select
1,1,'11/14/2004',100,'xyz st, NJ'
insert into Orders select
2,1,'11/15/2004',2100,'xyz st, NJ'
insert into Orders select
3,6,'11/14/2004',100,'sesame st, NY'
insert into Orders select
6,4,'11/14/2004',100,'Main st, SC'
insert into Orders select
9,4,'11/15/2004',100,'Main st, SC'
insert into Order_details select
1,1,12,3,5,0
insert into Order_details select
2,1,16,3,25,0
insert into Order_details select
3,1,66,1,10,0
insert into Order_details select
4,2,112,2,1000,0
insert into Order_details select
5,2,117,1,100,0
insert into Order_details select
14,3,112,1,100,0
insert into Order_details select
15,6,112,1,100,0
insert into Order_details select
19,9,112,1,100,0

Soon after we insert the above given rows, all the transactions gets replicated to the subscriber “MAK” and on to the subscribed database “Sales” [Fig 1.2]

[Fig 1.2]

Step 3

Now, let’s create the archive tables and the purge procedure on the publisher “EBONY” [Fig 1.3]

Use sales
go
Create table Orders_Archive (Order_id int constraint Orders_PK1 primary key,
custid int, Date datetime, Total_Amount money, ship_address varchar(300),
archivedate datetime constraint Orders_archive_default default getdate())
Go
Create table Order_Details_Archive (order_details_id int constraint
order_details_PK1 primary key, Order_id int constraint Orders_archive_FK1 foreign
key references Orders_Archive(Order_Id), item_id int, quantity int, unit_price money,
discount decimal (5,2), archivedate datetime constraint Order_details_archive_default
default getdate())
Go
Create procedure Usp_Archive_Purge as
Insert into Orders_Archive (Order_id, custid, Date, Total_Amount, ship_address)
select Order_id, custid, Date, Total_Amount, ship_address from Orders
where convert(varchar(8),date,112)=convert(varchar(8),getdate()-1,112)
Insert into Order_Details_Archive
(order_details_id, Order_id, item_id , quantity , unit_price, discount )
select order_details_id, Order_id, item_id , quantity , unit_price, discount from Order_details
where order_id in (select order_id from orders where convert(varchar(8),date,112)=convert(varchar(8),getdate()-1,112))
Delete from Order_Details where
order_id in (select order_id from orders where convert(varchar(8),date,112)=convert(varchar(8),getdate()-1,112))
Delete from Orders where convert(varchar(8),date,112)=convert(varchar(8),getdate()-1,112)
Go