We often get questions if Microsoft Dynamics AX 2009 supports multiple database data files; or multiple file groups. Also we are asked if table partitioning using multiple file groups is supported with Microsoft Dynamics AX 2009 and what is the performance gain? Let’s try to clarify it.
Multiple Microsoft Dynamics AX database data files within same file group
I want to clarify something regarding Microsoft Dynamics AX 2009 and the use of multiple Microsoft Dynamics AX database data files within the same file. First question; is it supported or not? yes it is supported as there is no difference for Microsoft Dynamics AX.
Second question; do we recommend it? No; we do not recommend it as a best practice. However if the database size increases it can be an option to create additional Microsoft Dynamics AX database data files (.ndf files) so you will not end up with one extremely big data file (.mdf file). Make sure that all data files always have the same Autogrowth parameter setup. Creation of additional data files (.ndf files) should only be done for very large databases.
Table partitioning (using multiple file groups)
With Microsoft SQL Server 2005 and 2008 (R2), you can partition tables across multiple file groups. Microsoft Dynamics AX 2009 recognizes the backend partitioning and throws a warning during synchronize. This was not the case in Microsoft Dynamics AX 4.0 where you risk losing the backend partition setup during the synchronize operation. In Microsoft Dynamics AX 2009, you have to setup partitioning using SQL Server tools. We just make sure the setup is disturbed during synchronization within Microsoft Dynamics AX 2009 with user knowing.
But is it recommended? The message is that the business partner or customer should definitely test this before undertaking the effort to deploy Microsoft SQL Server table partitioning. There are many query patterns that will not benefit from table partitioning, and some can be negatively affected. This feature is generally positioned as an improvement for data management (e.g., “sliding window”) more than as an improvement for performance.
In the absence of empirical evidence to substantiate a measurable performance improvement, you are risking imposing unnecessary costs on the customer and making a SQL product feature appear ineffective. For this reason always recommend to implement first the advice of Microsoft Dynamics AX 2009 White Paper: Planning Database Configuration (http://www.microsoft.com/download/en/details.aspx?id=13647) and other query and index tuning techniques before even start considering table partitioning.
Additional resources
Files and Filegroups Architecture
http://msdn.microsoft.com/en-us/library/ms179316.aspx
Partitioned Table and Index Concepts
http://msdn.microsoft.com/en-us/library/ms190787.aspx
White paper: Partitioned Table and Index Strategies Using SQL Server 2008
http://download.microsoft.com/download/D/B/D/DBDE7972-1EB9-470A-BA18-58849DB3EB3B/PartTableAndIndexStrat.docx
|
–author: |
Davy Vliegen |
|
–editor: |
Davy Vliegen |
|
–date: |
03/Oct/2011 |