As Dynamics CRM consultants, one of the questions that we often get from clients is “how do we archive CRM data when it’s no longer needed”? I’ve seen companies that had to keep all of their data for a certain amount of years for legal reasons. In any case, if you are using Dynamics CRM as a service management tool for a busy call center for example, chances are you are creating a LOT of records at a very fast pace. Imagine having to retain all of the data you created in the past 5 to 10 years on the same system. The size of the database can grow to become extremely large, making every single query a long one and impacting directly the users experience and their trust in the system.
Before I go into details about the available options that I can talk to, here is a quick recap of what Microsoft officially said a while ago about archiving Dynamics CRM data.
- “Archiving implies moving records from one location, storing those records for possible retrieval in another location, and deleting the archived records from the original location.” Given the complexity of the relationships in CRM, archiving as defined in the previous sentence can be very risky and must imply extensive thought, design and knowledge of the CRM application and database.
- An arching solution should not be implemented with the goal of improving system performance.
- Instead of deleting records, it is recommended to deactivate them when they are no longer required.
- The Dynamics CRM application can support very large databases, it is important to make sure that the hardware and database are configured in order to get optimal performance before looking at Archiving.
Here are some possible approaches that can be used to archive your Microsoft Dynamics CRM data. Before going down that path, make sure this is something you really need, don’t just build an archiving solution because you want to improve your system performance. Do your homework first!
Archiving Method | How it work | Pros | Cons |
1 – Create a copy of the database | It requires a strong DBA with knowledge of the CRM database to make a copy of the DB, copy all the records that need to be archived to the new DB and finally delete them from their original location. Sounds easy for a DB expert I assume (I’m not!) |
|
|
2 – Create a new instance of Dynamics CRM |
|
|
|
3 – Deactivated unwanted records and tweak database Indexes |
This is easily the number 1 choice from Microsoft’s perspective. To me, this should be one of the drivers in the CRM application design. The lifecycle of all records that are created (except for master data) should always be to:
In many systems that I have seen, some records just remain active forever, even though they are old and on longer required in the active records view. Also, it is important to create SQL indexes to enable a faster retrieval of active records.
|
|
I could write down that the down side is that records remain in the CRM database but again, you are not archiving simply because you want to reduce the size of your database… |
4 – Create and store custom archive reports | This is an interesting one. I’ve personally recommended using this solution to capture the history of successful workflows execution prior to deleting them from the system. The idea is to create custom reports containing information from the records that are no longer needed in the system. Once the reports have been created, they can be stored in a document management system or sent by email. After that, then the records can be deleted from the CRM database. There are many ways to create reports in CRM so I won’t spend time discussion that topic here. A simple Google (or should I say Bing searchJ) will give you plenty of starting points. |
|
|
5 – Create a custom Archive Database |
This is very similar to option 2. The difference would be instead of creating a new instance of CRM, you can create a custom database where you would only keep the information that you need. This database would be populated by a custom .NET app, third party data migration tool routine or database scripting. Optionally, a custom UI can be developed to enable administrators to search through the custom database. |
|
|
I personally like solution 5 better but it’s only because I’m a developer and I’d enjoy having to design the new database and the archiving tool. Of course, every company has its own preferences and resources with different set of skills. This should all be taken into consideration prior to deciding what solution to use. These are only the ones that I came up with, there are certainly other options out there.
Hope this helps!
[…] other very popular article is the one about MSCRM and data archiving. It is something that almost every client will ask for prior to buying a CRM solution and there […]
Hi Salim,
Do you have any update on this topic? Now we are moving to CRM Online and we try to find best solution to do the data archiving, because the data volume will impact to the cost customer need to pay.
Btw you also mention about deleting the data from CRM in your post, I assume the data deletion is from CRM am I correct? If we delete data from CRM it will takes time and there will be another issue to consider for example the relationship of the record (restrict delete). This is another complexity to be considered when we delete the data. Any advise for this?
Thanks.
Hi Lily – I find migrations to CRM Online to be a good opportunity to clean up your old or unused data, and it is not a quick exercise. However, the benefits of doing it are great if done properly. I have also had a lot of customer migrating their “active” data to CRM Online while kepping the archived data in a scaled-down local infrastructure. There is no one-size fits all for this unfortunately.
Cheers