AX 2012 R3 and multiple database files/file groups

(0) Share

Report

Posted on by Brandon Wiese

17,788

Anyone have any good advice or experience with breaking an increasingly large AX business database into multiple files? It appears to be supported as of 2009.

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (0)

Bertrand on at

Like (2)

Report

Hi Brandon,

This is a recurrent question ffrom the field and I'm afraid we don't have a definitive answer.

Please read this blog post for the details:

blogs.msdn.microsoft.com/.../multiple-database-data-files-table-partitioning-using-multiple-file-groups-and-microsoft-dynamics-ax-2009

My opinion is that having multiple Database files is not the first solution when Database size is becoming an issue. I always go for:

1. Clean Up strategy for standard and customized tables

2. SQL Compression with ENT edition

3. Archiving (standard IDMF tool or customized one)

Storage is the key to secure the I/O, so there shouldn't any performance improvement with multiples data files. We have many customers with TB of data and one data file.

Regards,

Bertrand

Was this reply helpful? Yes No
Brandon Wiese 17,788 on at

Like (0)

Report

The question was more geared toward using file groups in order to have better control over the storage (putting some tables on better or different storage than others), and better control over backup/restore. We replicate environments quickly through backup/restore, and some tables (DocuRef/DocuValue/SysDatabaseLog, etc.) are not critical to a testing environment and constitute a significant portion of the database and therefore backup/restore times and storage load of test/dev environments.

Performance of SQL is not the issue.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Bertrand,

Thanks for your post. Do you have any articles on AX DB clean up strategy & IDMF?

Best regards,

Guyguy

Was this reply helpful? Yes No
Bertrand on at

Like (1)

Report

Hello,

We will publish soon a blog post on clean up procedure for Dynamics AX.

Here:

https://blogs.msdn.microsoft.com/axinthefield/

Regards,

@BertrandCaillet

Was this reply helpful? Yes No
xfmoralesy on at

Like (0)

Report

Hi Bertrand,

Do you have any updated information about this using AX2012 R2/R3? Do I get any benefit from the performance point of view if I use multiple database data files for the Dynamics AX database?

Regards,

Xavier

Was this reply helpful? Yes No
Suggested answer

A.Prasanna 8,223 on at

Like (0)

Report

For which database you are referring , For TempDB you can split into multiple files to improve performance.If you referring to OLTP database then I dont think it is a good Idea.

Have you completed general Housekeeping tasks ?, and is your database size is large?.

if you having performance issues because of Large Data-set you better try IDMF to archive your data.

How ever if you experiencing performance issues Best option to deploy DynamicsPerf and find out exact performance bottlenecks before doing other things.

Hope this will help!.

Amith Prasanna

Was this reply helpful? Yes No
xfmoralesy on at

Like (0)

Report

Thanks a lot for your advice Amith. I was reffering to the OLTP database. I guess the way to go is DynamicsPerf

Was this reply helpful? Yes No