SQL Server Data Compression for Dynamics AX Part 4 – Actions post compression

In part 1 of the series we discussed Costs and Benefits of data compression together with some Best Practices to successfully compress your database. In part 2, we discussed the planning for Data Compression including prerequisites validation and steps to identify the best candidates for compression. In part 3, we showed you two different approaches of data compression, through Dynamics AX and SQL Server. In this blog post we will discuss some actions required after Data Compression.

The actions listed below apply only when compressing through SQL Server. These actions are necessary to prevent the application from removing the "Compression" property of each object while the application synchronizes, which usually occurs during patch of code.

To prevent this issue, we are going to leverage the DynamicsPerf tool.

Note: When compressing through AX move directly to Reclaiming the space released by data compression part of this blog post.

1. Create and populate the AOT metadata tables AOTTABLEPROPERTIES, AOTINDEXPROPERTIES, and AOTINDEXFIELDS

This is done by executing one of the below XPO files (depending on your AX version):

PrivateProject_AOTExport2009AX4.xpo

Or

PrivateProject_AOTExport2012.xpo

Both files are part of the DynamicsPerf tool located here.

Download the tool and unzip it in a new folder. XPO files will be in the “.\DynamicsPerf\Scripts - Dynamics AX” folder. You need to import and run the XPO file in the Dynamics AX environment you are compressing. When executed, the XPO will create the following objects in the AOT:

These three tables will then be populated by running the Class AOTExport.

2. Execute the post process SQL script

Execute the Post compression process SQL AX script to make AOT aware of the compressed objects by populating SQLSTORAGE table with the compression property of all the objects previously indentified.

After the execution of this script, you can make a full AOT synchronization without losing the compression settings.

Reclaiming the space released by data compression

The compression releases space inside the data file, but this space is not seen as free from Operating System perspective.

You can keep the free space in the data file for the future growth of the data, or you can reclaim the free space. To do so, you need to shrink the datafile to release the unused space to the operating system. You will use the DBCC SHRINKFILE command described here.

Note1:Be aware that DBCC SHRINKFILE is single-threaded and may take a long time to complete. In some circumstances, DBCC SHRINKFILE can terminate without shrunk the data file to the specified target size. In most of time, this is caused by defragmented heap tables present in the data base. That is why we recommend clustered index for all tables.

Note2:DBCC SHRINKFILE will increase indexes fragmentation. We recommend you run your usual index maintenance plan after the SHRINKFILE operation.

Note3:This step is relevent no matter how the compression was enabled through SQL Server or Microsoft Dynamics AX.

Additional references:

Data Compression for SQL 2016, 2014 and 2012:

https://technet.microsoft.com/en-us/library/cc280449.aspx

Data Compression for SQL 2008:

https://technet.microsoft.com/en-us/library/dd894051(v=sql.100).aspx

Regards,

Anna Mohib-Januszewska
Premier Field Engineer Dynamics AX

&

Jean-Bosco Bavugilije
Senior Premier Field Engineer SQL Server