Репликация исполнения хранимых процедур

По материалам статьи Muthusamy Anantha Kumar: Replicating Stored Procedure Execution
Перевод Маргариты Баскаковой

В среде OLTP (on-line transaction processing), Вы часто встречаете пакеты заданий (batch jobs), которые перемещают хронологию данных в архивные таблицы. Кроме того, часто встречаются пакетные задания, которые выполняют очистку OLTP таблиц от устаревших данных.. Задания такого типа могут порождать много транзакций и создавать дополнительную нагрузку на OLTP систему, снижая общую производительность, особенно если операции по переносу в архив или очистки данных выполняются над базой данных, участвующей в репликации транзакций SQL Server.
Существует способ, помогающий снизить такую нагрузку - это репликация исполнения хранимых процедур, он состоит в том, чтобы реплицировать информацию о том, как должна быть выполнена хранимая процедура, вместо того, чтобы реплицировать все транзакции от издателя подписчикам при очистке или архивировании данных.
Давайте рассмотрим два сервера. "EBONY" - основной сервер, и "MAK", которые являются дистрибутором и подписчиком в репликации таблиц базы данных, используя PULL подписку. Предположим, что мы реплицируем все колонки двух статей "Orders" и "Order_Details" из базы данных "Sales" от первого сервера "EBONY" в базу данных "Sales" на втором сервере "MAK". Мы не делаем никакого горизонтального или вертикального разделения таблиц.
Кроме того, мы предполагаем, что из этих двух таблиц будут изыматься устаревшие данные, и затем из них эти данные будут удалены, после чего относящиеся к очистке транзакции будут реплицированы подписчику. Чтобы уменьшить нагрузку, давайте создадим хранимую процедуру с именем "USP_Archive_Purge", которая архивирует данные в таблицах "Orders_Archive" и "Order_Details_Archive", и затем производит очистку данных.
Для демонстрации этого давайте пошагово смоделируем такую OLTP среду с репликацией.

[В начало]

Шаг 1

Предположим, что в базе данных "Sales" на первичном сервере "EBONY" есть таблицы "Orders" и "Order_Details".


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

Теперь предположим, что таблицы "Orders" и "Order_Details" уже реплицируются с первого сервера "EBONY" второму серверу "MAK" (используя репликацию транзакций).
Здесь, база данных "Sales" - публикация, "EBONY" - издатель, "MAK" - подписчик, и база данных "Sales" на "MAK" - подписка. [Рис. 1.1]

[Рис. 1.1]

[В начало]

Шаг 2

Теперь, давайте добавим какие-нибудь данные в описанные выше таблицы, и тиражируем их от первого сервера второму.


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

Вскоре после того, как мы добавим вышеупомянутые строки, все транзакции тиражируются в базу данных "Sales" подписчика "MAK" [Рис. 1.2]

[Рис. 1.2]

[В начало]

Шаг 3

Теперь, давайте создадим таблицы для архива и процедуру очистки на издателе "EBONY" [Рис. 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

[В начало]

Шаг 4

Теперь, давайте создадим таблицы для архива и процедуру очистки на Подписчике "MAK"


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

[В начало]

Шаг 5

Теперь, давайте добавим хранимую процедуру "USP_Archive_Purge" к репликации.

На издателе "EBONY", выполните следующее:


-- Замените на имя своей базы данных
use Sales
go
sp_addarticle
-- Замените на имя своей публикации
@publication ='Sales',
@pre_creation_cmd='none',
@article = 'USP_Archive_Purge' ,
@destination_table = 'USP_Archive_Purge' ,
@type ='proc exec',
@schema_option=0x01,
@destination_owner ='dbo',
@source_owner ='dbo',
@source_object='USP_Archive_Purge' ,
@force_invalidate_snapshot =0,
@status=0
go

exec sp_addsubscription
-- Замените на имя своей публикации
@publication = N'Sales',
@article = N'USP_Archive_Purge',
-- Замените на имя своего подписчика
@subscriber = N'MAK',
-- Замените на имя своей базы данных
@destination_db = N'Sales',
@sync_type = N'none', 
@update_mode = N'read only' ,
-- Замените на PUSH или PULL в зависимости от своего типа подписки
@subscription_type = 'PULL'
go
--Замените на имя своего издателя
sp_refreshsubscriptions 'Sales'
go

[В начало]

Шаг 6

Выполните хранимую процедуру "USP_Archive_Purge" на издателе "EBONY" как показано ниже.


Use Sales
Go
Exec USP_Archive_Purge
Go

Результаты

Как вы видите, некоторые строки были перемещены из исходной таблицы в архивную таблицу и потом были удалены.


(5 row(s) affected)
(8 row(s) affected)
(8 row(s) affected)
(5 row(s) affected)

Анализируя хронологию сеансов репликации, посмотреть которую можно в Мониторе Репликации из состава Enterprise Manager ,Вы заметите, что была доставлена только одна транзакция. [Рис. 1.3]

[Рис. 1.3]

Если на дистрибуторе выполнить хранимую процедуру "sp_browsereplcmds" Вы увидите, что была выполнена всего одна команда, которая запустила на исполнение процедуру.

{call "dbo"."USP_Archive_Purge" }

Теперь, проверьте архивные таблицы на издателе и подписчике.

На Издателе "EBONY"


Use Sales
go
select count(*) from orders
result: 0
select count(*) from order_details
result: 0
select count(*) from orders_archive
result: 5
select count(*) from order_details_archive
result: 8

На Подписчике "MAK"


Use sales
go
select count(*) from orders
result: 0
select count(*) from order_details
result: 0
select count(*) from orders_archive
result: 5
select count(*) from order_details_archive
result: 8

Обратите внимание, что после выполнения процедуры публикуемая и подписанная базы будут синхронны. Вы можете изменить процедуру "USP_Archive_Purge" и наметить её исполнение по отвечающему Вашим бизнес-правилам расписанию.

Заключение

Как было сказано ранее, цель этой статьи состоит в том, чтобы продемонстрировать пошаговую инструкцию применения репликации исполнения хранимых процедур, если используется репликация транзакций. Применение описанного выше метода, уменьшит нагрузку и повысит производительность вашего SQL Sever.

[В начало]