Archiving Solution in Replication enviornment for SQL Server 2014

Question

Application - MS Dynamics CRM 2013

DB - SQL Server Enterprise 2014

Objective - The CRM DB has grown very large & it is proposed that we should do data archiving to improve the performance & reduce overheads.

Archival Solution proposed - Create a parallel instance of CRM & keep all the data older than 12 months in the archival instance. Make this a Read Only instance with a separate URL for users. All the data older than 12 months then can be deleted from the production DB.

Current problem/Help needed on - Mechanism for replicating the data on a ongoing basis. For first time we can backup & restore but going forward how the incremental data moves into the Archival instance?

Options explored so far - 

1) SQL replication - problem is that when data is purged from publisher, it will also be purged from subscriber

2) Third party tools - Scribe etc.. but they have license implication plus not sure which tool will fit requirement unless we do a POC

3) ETL jobs - It will be a very huge effort considering there are 350+ tables 

Answer 1

Hi Pravin,

You can go for replication to move data to other server\database. As far as DELETE is concerned, you will need to customise your Replication Delete stored procedures to not to delete records as per WHERE clause in delete statement inside SP.

There will be a corresponding delete stored procedure per table with name such as sp_MSdel_tablename..

I have implemented this solution for one my database. The most important thing to take care here not to reinitialize subscription if there is any issue/error. Reinitialization will  overwrite the data on subscriber if reinitilized from a current snapshot.

---

Kindly mark the reply as answer if they help