Several of my clients have asked me to look into the ever growing MSCRM Database to figure out what is taking up all of the space. With all of the knowledge base articles talking about workflows eating up the database, it is very easy to say Workflows. However, with a little digging you may sometimes be surprised. I have found that with the advent of a very easy to use Outlook to CRM interface and a one-click button to push up emails, a closer look at the emails in CRM my revile your data munching monster. Frankly, it is all of the attachments to the emails that add up quickly.
These attachments are sometimes half of the database size. This bloated table will cause issues with backup and recovery. So, for a client of mine I have proposed the following solution:
- Any mime attachment over X days old
- Move to another document repository
- Replace with a html page that redirects to Document Repository and loads the document through the web
- Overwrite the Mime Attachment in CRM to add .html to the end of the file name and load the HTML redirect page into the document data
- Document repository system
- Database
- Holds attachments
- Use CRM Attachment GUID as the Key
- Webpage
- Retrieves the Attachment GUID in the URL
- Looks up from the database the documents
- Returns the document as a file
This solution will off load the data into another database easing the restore of the CRM database as needed. The user experience will be a flicker as the html redirects to another system to return the document. The end result will be two databases and the same amount of data to be backed up and restored for a full restore. However, the large mime attachment database is not as critical as the CRM database and CRM can come up quicker.