Storage requirements for Microsoft Dynamics AX

Overview

There are many misunderstandings about storage requirements for Microsoft Dynamics AX. Microsoft Dynamics AX is an ERP system with mixed workload that mostly looks like typical OLTP with wide range of query types.

Any mistakes in storage implementation increase risks to performance and cost a lot. Microsoft Dynamics AX has an intense use of storage , besides the queries it utilizes Read Committed Snapshot Isolation feature, and may have temporary tables stored in SQL Server TempDB. In addition, the queries can contain complex joins due to the table inheritance that exists in Microsoft Dynamics AX starting from 2012 version.

It is a common business practice that Microsoft provides storage hardware requirements to the hardware vendor, and then the vendor produces the detailed hardware specifications. Microsoft is not responsible for the third party hardware setup and configuration.

Usually Microsoft and hardware vendor engineers tune the disk storage working together on an implementation phase. The tuning is one of the points of the possible storage re-configuration.   

Microsoft approach in I/O calculation and Best Practices are described in the article available at technet.microsoft.com/ru-ru/library/cc966412(en-us).aspx (see requirements for OLTP data and log described in ‘I/O related performance counters and their meanings’ table).

General description

Having no specific storage model to be considered, the storage sizing and suggestions are based on the requirement to utilize RAID 10 or similar RAID type that is recommended in a write-heavy environment. It is recommended to use separate disk array for testing environment and other workloads such as reporting or analysis to minimize an influence on production environment.

SQL Server database files should be located on physical disks fully dedicated for SQL Server. For better performance, it is recommended to avoid sharing with other applications, Locally attached storage (DAS) may be faster, SANs are usually easier to manage.

Dynamics AX database data file(s), Dynamics AX database log file and TempDB should be on own volumes.

For the AX database data file(s) you can create multiple data files within a single file group. Table partitioning is supported in Microsoft Dynamics AX 2012 but it is not a general recommendation. If you decide to implement table partitioning for large and heavily used tables you also need to be sure that the underlying infrastructure (disk/controller setup) is capable of giving you the additional performance gains.

For the sector (stripping) size you should consider 8 to 64 KB or any other value your SAN vendor recommends. The number should be dividable by 8 as SQL Server works with pages of 8 KB in size.

It is recommended to move analysis and reporting data to dedicated disk set, so that this data has no influence on SQL Server operations and I/O channels.

Microsoft highly recommends using disk alignment. Disk alignment is a required optimization and must be applied by storage vendor during setup. Disk alignment provides a significant increase in system performance. Failure to perform disk alignment can decrease performance by 10 to 15 percent in RAID array systems. See the article on Microsoft site support.microsoft.com/default.aspx?scid=kb;en-us;923076&sd=rss&spid=3198 for the details and updated version of the Diskpart tool.

It is recommended to use maximum number of spindles for I/O operations; for example, for 2 Tb set in RAID 10 it is better to use 30 disks x 144 Gb instead of 4 disks x 1 Tb. SATA disks are not recommended.

RAID trade offs

We would like to reference whitepaper "Performance Tuning Guidelines for Windows Server 2008" available at www.microsoft.com/whdc/system/sysperf/perf_tun_srv.mspx

Configuration	Performance	Reliability	Availability	Cost, capacity, and power consumed
RAID 0+1 (striped mirrors) Requirements: Two-disk minimum.	Pros: Two data sources for every read request (up to 100% performance improvement). Balanced load. Potential for better response times, throughput, and concurrency. Cons: Writes must update mirrors. Difficult stripe unit size choice.	Pros: Single loss and often multiple losses (in large configurations) that are survivable.	Pros: Single loss and often multiple losses (in large configurations) that do not prevent access.	Cons: Twice the cost of RAID 0 or JBOD. Up to 2X power consumption.
RAID 5 (rotated parity) Requirements: One additional disk required. Three-disk minimum.	Pros: Balanced load. Potential for better read response times, throughput, and concurrency. Cons: Up to 75% write performance reduction because of RMW. Decreased read performance in failure mode. All sectors must be read for reconstruction; major slowdown. Danger of data in invalid state after power loss and recovery.	Pros: Single loss survivable; “in-flight” write requests might still become corrupted. Cons: Multiple losses affect entire array. After a single loss, array is vulnerable until reconstructed.	Pros: Single loss does not prevent access. Cons: Multiple losses that prevent access to entire array. To speed reconstruction, application access might be slowed or stopped.	Pros: Only one more disk to power. Cons: Up to 4X the power for write requests (excluding the idle power).
RAID 6 (two separate erasure codes) Requirements: Two additional disks required. Five-disk minimum.	Pros: Balanced load. Potential for better read response times, throughput, and concurrency. Cons: Up to 83% write performance reduction because of multiple RMW. Decreased read performance in failure mode. All sectors must be read for reconstruction: major slowdown. Danger of data in invalid state after power loss and recovery.	Pros: Single loss survivable; “in-flight” write requests might still be corrupted. Cons: >2 losses affect entire array. After 2 losses, an array is vulnerable until reconstructed.	Pros: Single loss that does not prevent access. Cons: >2 losses prevent access to entire array. To speed reconstruction, application access might be slowed or stopped.	Pros: Only two more disks to power. Cons: Up to 6X the power for write requests (excluding the idle power).

 

The following are sample uses for various RAID levels:

• RAID 0+1: A general-purpose combination of performance and reliability for critical data, workloads with hot spots, and high-concurrency workloads.
• RAID 5: Web pages, semi critical data, workloads without small writes, scenarios in which capital and operating costs are an overriding factor, and read-dominated workloads.
• RAID 6: Data mining, critical data (assuming quick replacement or hot spares), workloads without small writes, scenarios in which cost or power is a major factor, and read-dominated workloads.

 Storage configuration

The number of spindles required is highly dependent on the hardware configuration and vendor chosen. Since there is no industry standard for the storage subsystem and different vendors choose different disk system architectures, we highly recommend you consult with your hardware vendor to provide suggestions around the disk subsystem such as the number of spindles. 

For optimal database performance, we suggest you utilize smaller number of spindles per group. In this way, we can achieve higher levels of disk access and thus better read/write times. With a specific vendor’s RAID implementation, however, this suggestion may not be always valid. 

The following graph illustrates an example where amount of IOs is not well supported by spindles. TempDB is the most prominent example. Its high activity (both database files and log) is supported by only 8 spindles.

With today’s vendor specific RAID implementations, various other factors come into play such as the amount of disk drive cache, the cache controller, the particular vendor specific RAID implementation and others. You need to ensure your hardware vendor is involved in the storage design to provide you with a configuration that is specific to the hardware chosen and that provides you with the best cost/performance ratio. 

Additional disk space is required for SSRS/SSAS. If you cannot calculate the disk space required for SSAS/SSRS at the project starting, consider adding about 10-20% of the production database size for the cubes and temporary data.

Examples

Now you may say that the theory is not enough to get a clear vision of the requirements.

We can refer you to the official benchmark reports where storage configurations are listed, and you may correspond it to the tested functionality and workload. The benchmark whitepapers are available at TechNet technet.microsoft.com/en-us/library/hh536205.aspx, the detailed results can be downloaded from CustomerSource or PartnerSource.

Another example illustrates a storage configuration dependency on performance rather than actual data size, one disk selected by data size cannot support a workload. We may have mid-size company with no specific operations and verticals such as Manufacturing or Retail and without internal CRM module implemented. To calculate the database size we use an assumption that 1 million transactions (in Unicode) in General Ledger may take about 5 GB of storage space in a case of typical environment (including corresponding transactions related to Bank, Account Receivable and Payable, etc.). Next, data growth in accordance to the company strategy should be applied, say 30%. SQL Server 2008 Data Compression feature will be also taken into consideration. Then a suggestion may have approximately 500-600 GB external storage for database server based on RAID 10, 20-24 Disks For data at 180 IOPS per disk, 4 disks for log, 8-10 disks for TempDB. Note that every environment is unique and the example values should not be applied to a real implementation without analysis of the planning functional and technical specifics.

Summary

It is recommended:

• To have any storage LUN that should have response time not higher than 10-20 Msec.
• To do a storage check according to "Predeployment I/O Best Practices" whitepaper technet.microsoft.com/en-us/library/cc966412.aspx
• To use RAID 10 if possible for all LUNs or at least for LUNs that host log files, Tempdb, and main database. For example, HP whitepaper "HP StorageWorks 4000/6000/8000 Enterprise Virtual Array configuration best practices white paper" and other documents from Microsoft and storage vendors provide the following guidance: "RAID5 sets need twice as many writes to store data (four writes vs. two for RAID10)" and "RAID10 typically performs better than RAID5 under OLTP or intense write I/O activity scenarios".
• To support LUNs with as many physical spindles as possible. If it is not possible to configure all disks under RAID 10, create two sets of disks: RAID 5 for data and backup and RAID 10 for logs and TempDB. If several database files are created and LUNs are not spread across all available disk drives, create as many files as the number of LUNs and distribute files evenly across all LUNs.
• Pay as much attention to TempDB performance as to performance of main database.

 

 

Alexei is a Technical Architect for the Centre of Excellence EMEA team

Comments

• Anonymous
  February 11, 2013
  If you are distributing the AX database files among multiple files, it is a good practice to create these files when you do your installation. A common mistake is to add a new db file once previous file is full. That will result in worse than optimal IO performance as the current (and most probably the most frequently used) records are stored in one file. If there is place in more than one file the SQL server will fill the files parallel with the new records so the most up-to-date data is likely to be distributed among all files.

• Anonymous
  May 21, 2013
  Is it make any sence to put tempdb file into RAM Drive(using some 3 party software) instead putting it to hard disks?

• Anonymous
  May 21, 2013
  It would be simpler to have SSD for TempDB. RAM drive is not recommended and supported. In a case of the business critical application such as ERP it is too risky to go with RAM drive.

• Anonymous
  July 22, 2013
  Very interesting post. Thank you for sharing your experience on that kind of subject. I am looking for some help on doing estimation of the database size based on  Dynamics AX entities. A bit like the example given in the post: "That assumption 1 million transactions (in Unicode) in General Ledger May take about 5 GB of storage space in a case of typical environment (including Corresponding transactions related to Bank Account Receivable and Payable , etc.)." Thnx for your help!

• Anonymous
  July 22, 2015
  What is the minimum required IOPS capacity per disk to install Dynamics and this for a production use.

• Anonymous
  November 30, 2016
  For a test environment can you use SATA RAID 10 successfully to run CRM Dynamics 2016 server?If the server is not for production, will the software still load correctly and run or does the software detect the difference between SATA and SAS and refuse to load?