One factor that anyone considering CRM Online for their business would need to address is determining the amount of data you are intending to bring across into the CRM and how you expect this to expand in the long term. CRM Online includes 5GB of database storage for all of your CRM instances on an Office 365 tenant, which is included as part of the base plan (i.e. 5 Professional Licenses). If you run out of storage, there are only two ways in which you can increase this:
- By purchasing 20 Professional user licenses, you will be granted an additional 2.5GB storage. This is cumulative up to a maximum of 50GB (e.g. if you add an additional 20 Professional licenses to your existing 400, then your storage will not increase further)
- Additional storage can be purchased, at a whopping £6.20 per GB
Based on the above costs, a careful analysis needs to be performed to ensure that a migration does not end up costing more than expected; either in the immediate or long term. In any event, administrators will need to keep a careful eye on their database storage levels over time by frequently reviewing the CRM Online Administration Center on their deployment:
I was recently investigating why a particular CRM Online deployments storage had ballooned from a rather modest 0.75GB all the way up to nearly 4GB - all over the space of a few months! After doing some further digging, I determined that the Attachment entity was consuming the most amount of data in the CRM instance. This was not spotted readily, due to the rather strange way that this entity works within CRM, which I wanted to highlight and discuss further as part of this weeks blog post:
Why should administrators regularly check Attachment Entity records?
Every time an email is automatically synced into CRM, either with Server-Side Synchronisation or via CRM for Outlook, all the attachments are also brought into CRM. This will include your typical files (a .pdf document, an Excel worksheet etc.); but may also include images from your email signature or any other content on the email that is not stored in plain text. Over time and, in particularly large deployments, this can start to eat away at your database storage very quickly. Ignore the impact that this entity can have on your CRM deployment at your peril…
Why administrators may struggle to regularly check Attachment Entity records…
The attachment entity cannot be searched using Advanced Find. It’s just not there on the list of entities available to query:
The only way within the CRM interface to get a rough indication of how many attachment records are stored within the database is by running an Advanced Find Query against the Email entity, filtering out only email records that have a related attachment record:
(You can then customise the File Size (Bytes) filter to search for emails where the attachment is over a certain size - then, drill down in the email record to see the actual Attachment records).
So, what other options are available to us? You can use FetchXML to query the data on an Online/On-Premise deployment, but you are restricted in what information is returned. Only the following fields will be returned by a FetchXML; even if you specify, for example, the activityid field, it will never be returned in your request:
- mimetype
- filename
- attachmentid
- body
- filesize
So with these restrictions in mind, you can run the below FetchXML query to return all attachment records where the file size is over 50MB:
<fetch>
<entity name="attachment" >
<attribute name="attachmentid" />
<attribute name="filename" />
<attribute name="filesize" />
<filter type="and" >
<condition attribute="filesize" operator="gt" value="10485760" />
</filter>
<order attribute="filesize" descending="true" />
</entity>
</fetch>
To actually delete the record, you would have to either locate the record within CRM, a potentially laborious task. Unfortunately, there is no way to navigate to the record using query parameters appended to your CRM URL or to even use a Delete request in the SDK to remove the records. If you are running On-Premise CRM, then you could always run this manual query against the CRM organisation database:
USE MyCRM_MSCRM
GO
DELETE FROM dbo.ActivityMimeAttachment
WHERE AttachmentID = 'B18FBFCA-046D-E611-80CB-00155D02DD0D'
I am not sure whether this a supported way of using the application, so use at your own risk 😉
Be Proactive: 2 Tips to reduce the Attachment Data Footprint
To avoid a situation where you are having to go through the above to delete unnecessary email attachments, there are 2 things you can do to try and avoid this entity from swelling up unexpectedly:
- In Settings -> Administration -> System Settings, on the General tab, you can specify the list of blocked file extensions for attachments. Out of the box, CRM automatically populates this with a list of the most harmful file types, as a semi-colon delimited list:
You are able to freely modify this list to include/remove the list of attachments that you want CRM to automatically strip out. So if for example, your organisation uses high resolution .png images as part of your email signatures, this may be a good candidate to add to this list. 2. Staying within the System Settings area, but this time jumping across to the Email tab, you have an option where you can specify the maximum file size for all attachments saved in the system:
By default, this is set to 5.1MB - I would not recommend increasing this to any large degree, and there may be a case to reduce this further to around the 2-3MB mark.
Consider all things as well - how essential is it that any attachment is stored within CRM in the first place?
Certain organisations may use separate email archiving solutions, that automatically backup and store and all emails sent across an organisation into an archive that can then be accessed by end users and (most importantly) system administrators. Examples of these solutions may include Exchange Online Archiving, MailStore, Mimecast or Metalogix. If you know that your organisation has one of these solutions in place and that emails are being backed up/archived with all of their appropriate attachments, then it may be prudent to block CRM from storing any type of attachment file altogether. On the flip-side, if your organisation does not have such a solution in place, then this can work in reverse - CRM could act as an excellent way of implementing an email archiving-lite solution for your business. Although, I would expect that this is only practical for an On-Premise deployment, where your storage costs will be typically more cost-effective compared to CRM Online.