Replicate production data for reporting (1)

  Separating OLTP and reporting database on different server is a common requirement. If it’s to keep the same data on both databases, the solution is simple. You can either use transactional replication or log shipping based on your tolerance on latency.
  However, if OLTP server requires to keep live data only, e.g. last 3 month, and reporting server needs to keep longer historical data, say last 15 months, we can’t simply use replciation or logshiping to solove it. DTS is not an option becuase it’s hard to syncronize data changes.
Replication is still the way to go. However, since we need to keep different data on publisher and subscriber, we need either to change the replciation stored procedures or add instead of triggers on the subscription database in order to just replicate required commands.
The followings are the basic rules for the two solutions. The sample code are in the following two blog entries.
1. Use instead of triggers:
Need to create instead of triggers for INSERT, DELETE, and UPDATE actions.
(1) DELETE: delete actions on the reporting server can be initialized by the reporting or replicated from production server.
 o Delete data between last 3 and 15 months are NOT allowed to protect from production server purge expire data actions.
 o Delete data within 3 months are Allowed in order to synchronize live production data
 o Delete data older than 15 months are Allowed in order to remove old data from reporting server. These delete commands are initialized     from the reporting server, not replicated from production server.
 
(2) INSERT: insert actions on the reporting server can only be replicated from production server.
 o Insert data older than last 3 months are NOT allowed in order to protect reporting server from data restoring action in production server.
 o Insert data within 3 months are Allowed in order to synchronize live production data
 
(3) UPDATE: update actions on the reporting server can only be replicated from production server.
 o Update data older than last 15 months are NOT allowed because there is no data older than 15 months in the server
 o Update data within 3 months are Allowed in order to synchronize live production data
 o Update data between last 3 and 15 months are Allowed in case data is need to be restored and modified in production server.
Pros: Not need to change SQL server generated SPs; Replication can be configured to use SPs or commands;
Cons: trigger works on all the actions regardless they are replicated or from the local server so local delete action for data older than 15 month needs to be handled.
 
2. Modify SQL server generated replication stored procedures: Need to modify generated SPs for insert, delete and update.
(1) sp_MSdel_APDataTableName:
 o Delete data within 3 months are Allowed in order to synchronize live production data
 o Delete data older than last 3 months are NOT allowed to protect from production server purge expired data actions
 Note: the procedure checks @@rowcount. If @@rowcount=0 (no row is deleted), it raise errors. Don’t run the delete and check command if timestamp is not qualified.
(2) sp_MSins_APDataTableName: Same as the trigger for insert;
(3) sp_MSupd_APDataTableName: Same as the trigger for update;
Pros: Only for replicated command. Not bother local delete action for data older than 15 months;
Cons Not need to change SQL server generated SPs; Replication must be configured to use SPs;
 
The above solution is based on the following assumptions:
Data between 3 and 15 months are correct. No manual modifications occur in production server for data in this time range. (Since purge expire data may have some delay to purge data older than 3 month, this may happen).
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