page contents

SQL Server Data Compression for Dynamics AX Part 3 – How to compress Dynamics AX databases via SQL Server or Dynamics AX


In part 1 of the series we discussed Costs and Benefits of data compression together with some Best Practices to successfuly compress your database. In part 2, we discussed the planning for Data Compression including prerequsites validation and steps to identify the best candidates for compression. In this post we will show you two different approaches of data compression, through Dynamics AX and SQL Server and walk you through the steps for each of them.


DATA COMPRESSION THROUGH DYNAMICS AX

Compression through Dynamics AX application was already briefly described here, however there a few comments we would like to add to this blog post:

  • What is the difference between compression through DyamicsnAX and SQL Server?

In theory, there is none. it alters the table with data compression options as through SQL Server directly.

 

  • Which Microsoft Dynamics AX version should I be running to be able to compress?

SQL Server compression option can be found in the System Administration > Periodic > Database > SQL Administration > Table and index options form

However, for customers running SQL Server 2012 or higher with some AX version, there is a need to apply AX applicative hotfix to have this option available:

  • For all AX 2012 RTM versions: KB 3093812 (released on 9/6/2015)
  • For AX 2012 R2 lower than CU7: KB 2900567 (released on 10/17/2013)

This hotfix was included in AX 2012R2 CU8 and higher and in entire version of AX 2012 R3.

The changes in this hotfix make SQL Server compression option visible:

 

11

vs.

14

 

  • What are the exact steps to compress via Dynamics AX?

Select tables/indexes to compress with the conditions you want to apply (please refer to Part 2 of this blog series – Planning Database) through:

 

AOT/forms/SysSqlSetup (right click to open it):

 

 15

 

16

 

Or through:

System Administration / Periodic / Database / SQL Administration/Table and index options 

 

17

 

18

 

19

 

Once tables/indexes to compress are chosen you need to complete below steps to allow compression takes affect:

 

1. System Administration / Periodic / Database / SQL Administration / Table actions > Apply compression

20

 

2. System Administration / Periodic / Database / SQL Administration / Table actions > Synchronize database

21

 

3. System Administration / Periodic / Database / SQL Administration / Index actions > Reindex

22

 

Note: You can compress multiple tables/indexes at this same time.

 


DATA COMPRESSION THROUGH SQL SERVER

Compression through SQL Server is implemented by rebuilding all chosen indexes, with option DATA_COMPRESSION = {NONE | ROW | PAGE} like in bellow command:

 

ALTER INDEX ['+@indexes_name+'] ON ['+@schema_name+'].['+@table_name+'] REBUILD WITH (DATA_COMPRESSION='+@mode+',MAXDOP='+@max_dop+', ONLINE = ON,SORT_IN_TEMPDB = '+@sort_in_tempdb+' )

 

If you have followed the planning methodology described in the part 2 of this blog series, at the step 4 of the sample script databaseCompressionPlanning.sql.we provided, you already have the relevant data in temporary tables to generate 2 lists of objects to compress by page and by row. Using these two lists, you can generate a script that can be executed sequentially to rebuild all selected indexes. The step 5 of the sample script databaseCompressionPlanning.sql show an example to generate such script. Finally, run the step 6 to clean up temporary objects you created.

 

 

See next part of this blog series Part 4 – Actions post compression here for next steps.

 

Regards,

Anna Mohib-Januszewska
Premier Field Engineer Dynamics AX

&

Jean-Bosco Bavugilije
Senior Premier Field Engineer SQL Server


Skip to main content