Share
Report
25Hi folks
I apologize in advance for a long post, but wanted to give as much background as possible. Plus, I am really, really new to the AX realm, so please bear with me.
I am a SQL DBA supporting / inherited an installation of AX 2009. Yay me...
I looked up several combinations of keywords from the title, few posts came up, but none had a clear and definitive answer and the only one that came close was the one regarding the DOCUVALUE table. I am looking for such an answer to back up my case with the business.
My scenario is as follows:
- AX 2009 (and there are no upgrade plans whatsoever, nor for AX nor for SQL underneath it)
- SQL Server 2008 R2 Enterprise clustered running on Windows 2008 R2 Enterprise (512GB, 80 cores)
- SAN-based storage, combination of SSDs and SATA disk pools
- Database size reached 6TB, has only the PRIMARY FG
- Database in FULL recovery model, compat level 100
- FULL backup taken daily (business requirement, I would have done it weekly + daily DIFFs)
My problems:
- storage usage is heavily unbalanced and database file sizes are out of control and unmanageable if I have to relocate the database at the file level (DB is 6TB and has 2 files + transaction log)
- FULL backup runs in close to 4rs, want to shorten it. Already using 5 stripes and a combination of MAXBUFFERS and other BACKUP parameters as suggested in one of the posts here. During my tests, the magic number of stripes was 5. Over 5, the duration stayed pretty much the same (Before this approach, the full backup ran for 12-13hrs and it often failed after this amount of time)
- Database maintenance creates an I/O hotspot because all tables and indexes are on the same FG and same drive
- Restore runs in 7hrs 55mins. The RTO for this database is 3 hrs, so I have to come up with a solution to shorten the restore time.(We run an automated backup testing process every week)
My suggestion for the business were as follows:
- To improve the I/O, remove I/O hotspots and improve storage utilization:
- Group AX tables based on functional criteria (LEDGER, SALES, etc) and assign each group to a filegroup (FG)
- Add sufficient storage for each table groups and place each group and corresponding filegroup to dedicated storage
- Relocate data out of PRIMARY FG
- Split data from indexes (move data to dedicated FGs and all nonclustered indexes to separate FGs)
- For FGs, will end up with something like: LEDGER_DATA, LEDGER_INDEX, SALES_DATA, SALES_INDEX and so on
- Each FG will have its own set of files (NDF files)
- To address the long backup and even longer restores,the second main reason for having multiple FGs:
- Switch to a FG level backup (aka partial backups)
- In case of an incident, do a piecemeal restore instead of full database restores
- To further improve I/O efficiency and optimize storage utilization
- Add data compression
And my questions
- is the multiple, dedicated filegroups (and not counting the PRIMARY FG) and data files design supported in AX2009? All posts I came across doesn't explicitly say NO, but they doesn't say YES either. I read the article from MS that says YES, but it refers to table partitioning and to having multiple files within the existing, PRIMARY FG. I don't want to use table partitioning, at least not yet.
- is data compression supported in AX2009?
Any pointers, links or jokes welcome
Thanks in advance.
Live long and prosper
Alin
All responses (
Answers (
