Search Wiki:
SQL Server Database Compression Analyzer for Dynamics

I am proud to announce the release of the SQL Server Database Compression Analyzer for Dynamics. This tool will aid Dynamics Partners and Customers in implementing Microsoft SQL Server 2008* and later Data Compression functionality. The key to a successful implementation of Data Compression is deciding what indexes should be compressed without causing possible performance bottlenecks and with what level of compression to use to achieve the maximum storage savings. This tool will do an exhaustive analysis of all indexes in your Dynamics database calculating the read to write ratio of all indexes as to avoid compressing indexes that have a high write to read ratio which could potentially cause performance issues. The tool also estimates space savings for each index for both ROW and PAGE level compression to allow you to choose the correct level of compression to maximize space savings and minimize overhead and provides and overall theoretical space savings if data compression where to be implemented on the database. Once the appropriate indexes and level of compression have been identified the tool will run the appropriate TSQL commands to implement the chosen compression settings and will report back the actual total space savings.

*Data Compression is only available on Microsoft SQL Server Enterprise Edition and higher.

FYI – Also included in the tool is set of scripts specifically for Microsoft Dynamics AX to populate the SQLStorage table which will allow you to preserve your chosen compression settings when doing a full synchronization.

For more information on Microsoft SQL Server Data Compression click on the following link.

This is the PRIVATE internal version to be used in Services Delivery. For the Public version that can be distrubited to partners and customers please contact me

How to use this page
The tool home page is where team members and visitors go to find out about your tool. Here are some ideas about what to put on this page:

Announcements; RC1 Available Now
Support contacts;
Tool documentation; Included in ZIP file

Note See the Links and Files Help topic for information on uploading documents and linking to them from your wiki page.

!!!!!Release v3.02 RC2 is available
!!!!!Please use Latest Version

!!!Release v3.02 RC2 Notes
*Added code to handle Index Columns with special characters in the names which causes the spestimatedatacompressionsavings proc to crash (NAV)
*Added code to handle Indexed columns that use Timestamp datatypes which causes the spestimatedatacompressionsavings proc to crash (NAV, CRM)
*Added code to bypass tables with the “Text In Row” option enabled which causes the spestimatedatacompressionsavings proc to crash (CRM) *Updated the tool to be schema aware (CRM)
*Corrected errors caused by case sensitive databases (NAV)
*Corrected issue with “Refresh Compression” script causing it to miss tables
*Fixed the issue with the SQLStorage table being populated with the wrong AX Index ID (AX)
*Tested the full tool suite against the following products
**AX (v3.02 Passed with no errors)
**NAV (v3.02 Passed with no errors)
**GP (v3.02 Passed with no errors)
**CRM (v3.02 Passed with no errors)
**SL (v3.02 Passed with no errors)

!!!Known Issues
*The tool is not Partition Aware. This would require a significant rewrite of the code and maybe added in the future

NOTE: This tool is still for internal use for Service Delivery only and cannot be given to partners or customers who have not engaged us for service. There is also a “Public” version available that can be given to customers and partners. To obtain the public version please contact me.

FYI - There is many SQL Server Database Compression tools available on the web and there is nothing wrong with these tools, but they do not take into account the specific design eccentricities present in the Dynamics Products’ Databases and usually produce errors. This was the major reason for the creation of this tool.
Last edited Jul 20 2011 at 3:45 PM  by MichaelDeVoe, version 2
Page view tracker