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 http://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).
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 http://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.
We would like to reference whitepaper "Performance Tuning Guidelines for Windows Server 2008" available at http://www.microsoft.com/whdc/system/sysperf/perf_tun_srv.mspx
Cost, capacity, and power consumed
RAID 0+1 (striped mirrors)
RAID 5 (rotated parity)
RAID 6 (two separate erasure codes)
The following are sample uses for various RAID levels:
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.
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 http://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.
It is recommended:
Alexei is a Technical Architect for the Centre of Excellence EMEA team