HOW TO SAVE DYNAMICS CRM ONLINE STORAGE SPACE

“Save CRM storage space for cost cuttings” is quite a common hurdle faced by most of the businesses across the world. This might not be a threat for newly configured CRM systems, but as our business grow and time pass by, we realize at some point of time that our CRM database size has grow exponentially than expected growth and one of your managers might ask you to optimize it (Everyone knows it’s a costly bet to buy storage space from Microsoft $10/GB/Month). I did face the same issue and this blog will help and guide you through the best practices that needs to be implemented step by step for saving storage space which in turn saves money!

The first and foremost thing is to evaluate which table is occupying the highest storage space in our online CRM. Unfortunately, CRM online doesn’t let you to directly access the backend SQL tables to fetch size of each table. But with CRM 2016 Update 1.1, a new managed solution called “Organization Insights” can be found from AppSource. Once installed, open it and try to click storage icon (2^nd from bottom) on the left most toolbar. The msdn link to know more about organization insights:

https://docs.microsoft.com/en-us/dynamics365/customer-engagement/admin/use-organization-insights-solution-view-instance-metrics

This takes us to a dashboard from where we can easily evaluate which CRM table occupies the highest space. Sample screenshot attached.

If you are not familiar with the SQL table name (because online users may not be familiar with but on-prem users does) please do investigate about the table before even trying to optimize it. I will be explaining about the top 5 tables in this blog:

1. Activitypointerbase table: The activity pointer table stores any activity or task that is performed or to be performed by a user. Whenever you create an activity record in CRM, a corresponding activity pointer record is created in this table.
2. Activitypartybase table: This table only stores all the CRM activity related objects information. For example, for one email activity record, this table creates 4 rows to store the from, to, Cc, Bcc fields and it can be inner joined with above pointer base table using the regarding objectid. In order to optimize both the tables, you must permanently delete some activity records from CRM to save disk space.
3. Attachment table: As the name implies, this table stores all the email attachments (can be either .doc, .pdf, .xlsx and other file formats supported by CRM). All the data in those files will be stored in CRM as Base64 format.
4. ActivityMineAttachment table: This table stores all the configuration related info of the attachment. Whenever a new email activity with attachment is created, it first creates a new record in this table. This table has columns like file size(bytes), file name, minetype, regardingobjectid, objecttypecode, objecttypecodename etc. It also has attachmentid column which stores the GUID of respective attachment table record. The actual file data will be stored in Attachment table and it is joined with ActivityMineAttachment table. That is the primary reason Attachment table occupies the most space and not the ActivityMineAttachment table.
5. AnnotationBase table. All the notes record created across various entities will be stored in this table. If a file is attached to the notes, it will stored as Base64 format in this table. In order to optimize this table’s space, we need to store the attachments in either azure blob or sharepoint.
6. Workflowlogbase table: This table stores all the process sessions of out of box workflow. This process sessions include workflow logs with status successful, failure, in-progress, pending, cancelled etc.
7. Auditlog table: This table stores the audit history of all enabled entities & fields. In CRM, all the audit history is stored as a partition of 3 months audit history. You can browse to audit settings and delete a partition to optimize auditlog table space.

Apart from optimizing the top 10 tables to reduce storage space, it is always a best practice to regularly delete unwanted logs, system jobs, mailbox traces, plug-in trace logs etc. You can schedule bulk deletion jobs which can be run daily/weekly to get rid of the log records. The following are few examples of the bulk deletion jobs:

1. Process sessions entity: This entity stores all the workflows related process sessions.

2. System job: All the asynchronous/background activities that are running in CRM will create a log record in system jobs. Though it is not recommended to delete system jobs which are created by internal CRM processes because those are taken care by Microsoft, it is good to identify system jobs created due to custom activities that we perform in CRM and get rid of them in scheduled manner by scheduling bulk delete jobs.

3. Trace entity: This entity stores all the mailbox alert/error messages when we do a server side sync of any CRM queue with exchange online mailbox.

Apart from above 3 entities, you can always create your own bulk deletion rules on out of box and custom CRM entities to delete older transaction data.

Practically speaking, implementing above methods will hardly save you CRM storage space. Our focus is to find an optimized storage platform and process for storing email/notes attachments (which occupy majority of storage space) instead of storing them in CRM.

MOVING EMAIL/NOTES ATTACHMENTS TO AZURE BLOB

Its very simple and no strenuous labor is involved in real time synchronization of CRM with azure blob as well as moving existing attachments from CRM into azure blob. There is a free product in AppSource “Attachment Management” using which we can perform real time integration of CRM with azure blob. If you want a step by step explanation of how to perform this integration, please refer to below blog:

https://readyxrmblog.wordpress.com/2017/10/01/dynamics-365-attachment-storage-revisited/

First create a new azure storage account and create 2 azure blob containers: One to store email attachments and another for note attachments. Copy the SAS (shared access signature) key by providing valid start & end date for the container. Download the managed solution “Attachment Management” into any one of the CRM instances. Browse to azure blob storage settings custom entity and enter respective azure storage details here. Then you can test by creating a new email/note attachment in CRM and checking if you see the same attachment in respective blob containers in azure.

However, there were few limitations with this approach, as going forward it might synchronize all the newly created email/notes attachments to respective containers in azure blobs. But if you have huge number of existing attachment data, we need to use some 3^rd party tool or azure logic app to extract all CRM attachments and manually move to azure.

The following are the few procedures you can use to move existing email/note attachments to azure:

1.  Azure logic app comes as a savior to us if we want to handle bulk attachments migration from CRM to azure blob. Please refer below link to deploy it to your azure environment:

https://github.com/anilvem1/LAMoveCRMAttachmentsToBlob-API

Once deployed, we need to configure few parameters in azure logic app before running it. We can use the recurrence element to set the time period and make the logic app to run at regular intervals and move the attachments from CRM to azure in a batch of fixed number.

We need to configure below parameters in the logic app:

1) The number of attachments that must be moved when this app runs. This can be set by expanding GetActivityMineAttachments logic app element.

2) The time period of each run. It can be set in recurrence element.

3) The CRM instance to which this app is synchronized. This needs to be set in each & every CRM element in logic app.

4) The respective azure blob container to which all attachments need to be moved. This needs to be set in CreateAzureBlob element.

5) Configure the API connection for CRM by authenticating using CRM credentials.

Please feel free to make your own trial and runs to find an optimal number for number of attachments to be moved as a batch and time period of each run but I will list some practical errors that I encountered.

1.      If you set the number of attachments parameter to a larger value say 500, it will fail because the maximum buffer size of azure logic app is 104 MB. If the retrieved attachments size is more than that, the logic app will fail. The error I got is “Http request failed as there is an error: 'Cannot write more bytes to the buffer than the configured maximum buffer size: 104857600”.

 Better try to set the number of attachments to be retrieved during each run in such a way that whole size of all attachments retrieved in a batch is less than 100 MB.

 2.      Another type of error you get if you try to set the number of attachments to any higher number is “Bad Request”. Since we are downloading attachments from CRM through WebAPI, there might be chances that CRM service is momentarily disconnected. The error message is " The plugin execution failed because no sandbox hosts are currently available. Please check that you have a sandbox server configured and that is running. \r\n System.ServiceModel.CommunicationException: Microsoft Dynamics CRM has experienced an error.

Based on my practical experience, setting to a count of 50 attachments and a recursive time interval of 2 minutes worked for us without any errors (though logic app failed in very few instances). It took almost 1.5 days to move 50000 attachments to azure.

If for some reason the logic app fails, it keeps on failing until someone disables the logic app or delete it permanently. In order to avoid this, just open the logic code designer view and change the attachment count to a very smaller number say 5/10 and allow the logic app to run successfully for 2-3 times after which you can change it back to 50/100. Another thumb rule is to avoid running this package on peak working hours because the logic app frequently errors out due to bad request error message from CRM.

2. 

Alternatively, we can also use SSIS Kingswaysoft connector to achieve our goal. The actual architecture built by Microsoft labs behind the scenes is, whenever you try to open a note record or open email attachments, the synchronous retrieve plugin will trigger, and it tries to retrieve respective document from azure blob containers and display it in CRM screen. Similarly, if you try to download that document from CRM, the plugin will connect to respective azure blob through SAS key provide in settings and download the document from azure.

For the retrieve plugin to easily identify the document in azure blob container (as there might be attachments with same name), we need to append annotationid (GUID of notes record) to the prefix of every note attachment’s name to uniquely identify the note attachment in azure blob container. Similarly, we append attachmentid to the prefix of every email attachments name stored in azure blob container.

Therefore, we need to develop SSIS package (use kingswaysoft connector for CRM) and modify the expression in one of the SSIS block to download all the CRM attachments with its GUID appended as prefix to attachments name into local disk.

As shown in the above picture, you have use CRM source connector to retrieve all the note/email attachments in Base64 format and use SSIS derived column block to convert the attachment in base64 format to binary format and finally save the file into local disk by appending GUID to the file name prefix. Similarly, you can develop another SSIS package which will connect to the respective folder in local disk and upload all the attachments to azure blob container. The below link provides a sample SSIS package used to download note attachments to user’s local disk:

http://www.kingswaysoft.com/blog/2017/03/21/Extracting-CRM-Attachments-with-Ease

After completely uploading all the attachments to azure blob, the next step is to erase all the attachment data in CRM and make the file size of every document to 0. There are 2 reason for this: One is our primary goal is to reduce CRM database storage and store all the attachments in azure. Therefore, we are erasing all the data in existing attachments to make the file size 0. Second is, once retrieve plugin is triggered it will check if the CRM file size is 0 and after confirming file size is 0, it will look into azure blob container to retrieve the attachment. For any reason, if the file size is not 0 (may be some other integer greater than 0), then retrieve plugin treats that CRM attachment has some data and it will exit from looking into azure for attachment. So be cautious and double check to see if the attachment data is erased and its size is 0.

The following is the code to erase data in note attachment:

public static void updateNotes(IOrganizationService iService, Guid noteid)

       {

           Entity Annotations = new Entity("annotation");

           Annotations.Id = noteid;

           Annotations.Attributes["documentbody"] = null;

           Annotations.Attributes["filesize"] = 0;

           iService.Update(Annotations);

       }

Where noteid in the code is the GUID of note record. Similarly, you can follow below link to erase data in email attachments and make file size equal to 0.

https://docs.microsoft.com/en-us/dynamics365/customer-engagement/developer/sample-create-retrieve-update-delete-email-attachment

Another alternative approach to using console application to erase attachments data is developing SSIS package to retrieve the GUID of all email/note attachments into SQL table or notepad and updating the respective CRM attachment or annotation record with body = null thereby making file size=0.

3. 

If you have noticed correctly, you can run a custom report (after installing attachment management solution) to see how many email/note attachments are present in CRM and in azure. At any moment of time, if you want to check the count of attachments in CRM run this report. There is a button “Move to Blob” in this report. If you happen to see email/note attachments count in that report, you can select either of them and click this button which will fire a plugin in the background and move respective attachments to azure blob.

However, it has got its own limitations. Initially, if the number of attachments in CRM is very high report might not generate/run and show exact count because the background plugin which used to calculate the attachments count in CRM will eventually time out after 2 minutes of plugin threshold time. Therefore, you don’t have the option to click Move to Blob button. Practically, in my case if the attachment count exceeds 30000, report failed to generate.

This method of migrating CRM attachments to azure is ideal only for lesser email/note attachment count. If you click the button “Move to Blob” on the report, the plugin runs in the backend and it will try to move somewhere around 200-250 records in a single go which also depends on the size of attachments, internet connectivity, time within which CRM server responds to the WebAPI call. If you have larger number of attachments in CRM, it would be wise to stick with Methods 1 or 2 and use Method 3 if you have lesser number of attachments in CRM.

I am leaving it up to the CRM developer or CRM business user to decide which Method 1 or 2 or 3 to use for your specific business to achieve your goal.

VALIDATE IF ATTACHMENT MIGRATION WAS SUCCESSFUL

Let us assume that you have used either Method 1 or 2 to migrate existing email/note attachments to azure blob. What if your manager comes and asks you this question: “How do you validate that attachment migration process was successful? Can you Show me some proofs? Obviously, you don’t have any evidence to prove it.

This shows us how important it is to prove with exact numbers and evidences that attachment migration was successful particularly in companies where email communications are critical and can impact critical business processes.

1. Before starting the attachment migration process to azure, create a new SSIS package and extract all the annotation entity records (annotationid, filename, filesize, isdocument) into an SQL table. Calculate the number of annotation records where filesize>0 and isdocument=1. Also sum up all the notes records filesize to calculate the total note attachments size in CRM. Repeat the same steps for attachment table as well to calculate total number of email attachments as well as sum up total size of email attachments in CRM.
2. After completely migrating CRM attachments to azure blob storage containers, open the storage explorer in azure and click the folder statistics drop down to see how many attachments are present as well as total size of blob container. You can then validate the numbers with previously obtained values to check if all attachments have successfully made to azure or not.

3.  Run the custom report provided by Attachment Management solution to find count of attachments in CRM and azure at any time. However, it may/may not return an accurate count because the CRM plugin running behind the scenes calculates the count of attachments whose size=0 and show that as azure blob count and it does not really browse to azure blob container to get the count of number of attachments in it (you can test deleting an attachment in azure and see if there is any reduction in count returned by report). It also displays the number of attachments in CRM by calculating attachments with size>0. So even before starting the migration process, calculate the number of attachments in CRM with filesize=0 so that your mathematical count doesn’t go wrong.

REQUIRED SECURITY PRIVILEGE

Just by installing the attachment management solution into CRM and migrating all email/notes attachments to respective azure blob doesn't allow transparency of attachment data to all business users in CRM. One should have necessary security privilege to be able to view attachment data in CRM. If you are a system admin, you might have not faced any difficulty in viewing attachment data. But if you are a business user with limited visibility to CRM data, then you might have definitely encountered an error when you open notes tab saying "Insufficient security privilege. Please contact your System Admin". The attachment management plugins on retrieve step, retrieve multiple step runs under calling user context, retrieve the SAS key under azure blob storage settings entity and connects to respective azure container to retrieve the attachment and display in CRM. Since azure blob settings entity is custom entity, we should be mindful enough to provide at least read privilege on this entity to all business users who should be able to retrieve email/note attachments from azure.

Create a new security role called "Azure Attachments". Provide organization level read access to "azure blob storage settings entity" and "notes attachment entity settings" custom entity. These are the 2 custom entities which store azure configurations and note attachments enabled entities. Also provide complete organization level access of create, read, write privilege on 2 internal custom entities: "Attachment process records" and "Attachment transaction". Assign the Azure Attachment security role to any business user whom you wish should be able to view attachments in CRM.