Configuring Dynamics GP's (SQL Server) Disks

Question

I am an accidental DBA at my work, and when I took it over, it was quiet a mess. Fortunately, we have the green light to build new systems for an upgrade to our accounting system: Microsoft Dynamics GP. I would like to follow or exceed all recommendations from Microsoft. Our current environment has significant performance issues. I would like to get this forum's opinion of our SA's plan for our new system's configuration, compared to what is recommended by Microsoft.

As far as disk configuration, Microsoft recommends:

• OS/APP RAID 1
• SQL Log RAID 1
• SQL Data RAID 5
• SQL TempDB RAID 1
• SQL Backups RAID 0.

What our SA has available is: A VMware 6 datacenter with an VNXe 3200 SAN. That SAN has 11 x 600 GB 10k RPM drives and 9 x 2TB 7.2k RPM drives. It also has 6 x 200GB flash drives to be used as very fast cache of the most frequently accessed data. So the recommended hard disk specs don’t seem to take into account a datacenter that is as virtualized as ours. He has pooled all the 10k RPM drives into a RAID 6 + 1 hot spare and all of the 7.2k RPM drives into a second RAID 6 + 1 hot spare and labelled them Performance Pool and Capacity Pool, respectively. The more platters he puts into a volume, the more performance he can get out of it, plus all the redundancy that comes with a RAID 6 and a hot spare. However, he would like to house a significant portion of the database in that cache.

Given that our accounting system and its SQL Server installation will not be alone in this configuration, but mixed together with other company systems (such as the exchange server), is this satisfactory or should I push that the accounting system have its own separate RAID configuration more in line with Microsoft's recommendations (except the RAID 5 part...I heard a puppy dies every time RAID 5 is used)?

Answer 1

While I understand you want to follow Microsoft guidelines, in my opinion a lot of the Dynamics best practices are outdated.

I personally don't work with GP but with AX and I tend to follow the SAN vendor guidelines for SQL Server rather than the guidance that Microsoft defined in the days of direct attached storage.

I haven't worked with EMC a lot but they clearly have a SQL Server best practices document which states:

Most SQL server database environments can benefit from storage pool based configurations

I'd just run some benchmarks before migrating to the new hardware and double-check with the SAN vendor and go with their recommendations.

For example, the recommendation says to use RAID1 for tempdb, but if you can get your hands on some nice NVMe PCI express cards those will perform lots and lots faster even if they aren't the "recommended" setup.