I am pleased to announce the release of the SQL Server Database Compression Analyzer for Dynamics. This tool was created to help Dynamics partners and customers leverage the benefits of SQL Server Data Compression and to make the processing of analyzing and implementing compression quicker and easier. I know there are several generic “Compression Tools” available on the web and I am sure they are all fine, but none of these tools take into account the design eccentricities of the Dynamics database, causing many of these tools to error out or not run at all and none of them have been extensively tested on all of the Dynamics products. The Dynamics Compression Analyzer was written to handle these eccentricities and has been extensively tested on all of the Dynamics Products; AX, NAV, CRM, GP, and SL. This tool will walk you through analyzing your index usage helping you make the decision of which indexes to compress and which indexes not to compress and what level of compression ROW or PAGE to use. Once you have analyzed the data and decided what to compress and how to compress, the tool will do the actual compression for you and let you know how much space you’ve have reclaimed.
You can get the Microsoft SQL Server Data Compression Tool for Dynamics here: http://archive.msdn.microsoft.com/MSSDCTD
Some of the Benefits of SQL Server Database Compression:
- Reduced storage requirements
- Increased performance
- Data is loaded into cache (BufferPool) in a compressed state using less of the cache allowing for data to be cached. The more data cached, the less SQL has to go to disk, the faster the response times.
- Data is read from the disk in a compressed state meaning it takes less physical IOs to read compressed data which puts less stress on the disk subsystem allowing it to handle more IO requests in a timely fashion.
NOTES:
- Compression is only available on Microsoft SQL Server 2008 and later, Enterprise Edition or higher.
- Compression can increase overall CPU utilization on the SQL Server by 10 – 30% depending on what you compress and how you compress it. So if your CPUs are averaging 60 – 70% utilization without compression enabled you may not want to implement it as it could cause serious performance issues.
- If you like to engage assistance from Microsoft to implement compression for your Dynamics deployment, please contact mbsprosv@microsoft.com (billable server through Professional Services)
Known Issues with Compression Tool:
- The tool is not Partition Aware. This would require a significant rewrite of the code and may be added in the future.
Senior Premier Field Engineer
Microsoft Dynamics
Microsoft Certified Master – SQL Server 2008