Replicate production data for reporting (2)–By instead of triggers

–First we need to configure the publisher,distribution database, and allowed subscriber etc. Then create the database to publish on publisher:
CREATE DATABASE PubTest ON
 PRIMARY ( NAME = TestData, FILENAME = ‘D:\ReplData.mdf’, SIZE = 10MB, MAXSIZE = 100, FILEGROWTH = 5)
 LOG ON (NAME=TestLog, FILENAME=’D:\ReplData.ldf’, SIZE = 5MB, MAXSIZE = 20,FILEGROWTH = 5)
GO
USE PubTest
GO
CREATE TABLE PubTable (C1 int not null, C2 int not null, RecordTime datetime not null CONSTRAINT PK_PubTable PRIMARY KEY CLUSTERED(C1))
GO
— Populate necessary data:
SET NOCOUNT ON
DECLARE @i int,@Now datetime
SELECT @i=1,@Now=getdate()
WHILE @i<=60
BEGIN
 INSERT PubTable (C1,C2,RecordTime) VALUES (@i,@i*10,dateadd(day,-(@i-1),@Now))
 SET @i=@i+1
END
–Create the subscription database on the subscriber:
CREATE DATABASE PubTest ON
 PRIMARY ( NAME = TestData, FILENAME = ‘D:\ReplData.mdf’, SIZE = 10MB, MAXSIZE = 100, FILEGROWTH = 5)
 LOG ON (NAME=TestLog, FILENAME=’D:\ReplData.ldf’, SIZE = 5MB, MAXSIZE = 20,FILEGROWTH = 5)
GO
/*
Set up the pull subscription on the subscriber.
After snapshot is applied and two databases are syncronized, add the following triggers on the subscription. To keep it easy to test, the sample keeps the last 15 days data on production server and last 45 days data on reporting server
*/
ALTER TRIGGER TR_CheckDelete ON PubTable INSTEAD OF DELETE
AS
DECLARE @St datetime, @Et datetime
SELECT @St=dateadd(day,-45,getdate()),@Et=dateadd(day,-15,getdate())
IF NOT EXISTS(SELECT 1 FROM deleted WHERE RecordTime<@St OR RecordTime>=@Et)
RETURN
DELETE A FROM dbo.PubTable A, deleted B WHERE A.C1=B.C1 AND (B.RecordTime<@St OR B.RecordTime>=@Et)
GO
CREATE TRIGGER TR_CheckInsert ON PubTable INSTEAD OF INSERT
AS
DECLARE @Et datetime
SELECT @Et=dateadd(day,-15,getdate())
IF NOT EXISTS(SELECT 1 FROM inserted WHERE RecordTime>=@Et)
RETURN
INSERT dbo.PubTable (C1,C2,RecordTime) SELECT C1,C2,RecordTime FROM Inserted WHERE RecordTime>=@Et
GO
— To simply the modify scenario, suppose PK and Timestamp can’t be changed.
CREATE TRIGGER TR_CheckUpdate ON PubTable INSTEAD OF UPDATE
AS
DECLARE @Et datetime
SELECT @Et=dateadd(day,-15,getdate())
IF NOT EXISTS(SELECT 1 FROM deleted WHERE RecordTime>=@Et)
RETURN
UPDATE A SET C2=B.C2 FROM dbo.PubTable, deleted B WHERE A.C1=B.C1 AND B.RecordTime>=@Et
GO
— On the publisher, run the following tests:
— remove expired data
DELETE dbo.PubTable WHERE C1=30
— The data is deleted from publisher but not subscriber
GO
— remove worng live data
DELETE dbo.PubTable WHERE C1=5
— The data is deleted from publisher and subscriber
GO
— insert live data
INSERT dbo.PubTable (C1,C2,RecordTime) VALUES (-1,-100,dateadd(day,1,getdate()))
— The data is inserted in publisher and subscriber
GO
— update live data
UPDATE dbo.PubTable SET C2=1000 WHERE C1=-1
— The data is updated in publisher and subscriber
GO
— On the subscriber database, run the following test:
— remove expired data:
DELETE dbo.PubTable WHERE C1=50
— Data is removed from the subscription database.
 
Advertisements
This entry was posted in SQL server. Bookmark the permalink.

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