DAS or SAN for SQL Server?

Performance issues is always complicated, but a common approach is possible (in fact the only way to work through this is to define the problem area and structure the different factors into groups). In my approach the operation platform is one key group while the database is another. Based on the work I have done since September, I have found it very hard to get real and trustworthy information about the utilization of SAN storage when this is used for SQL Server. And as a advisor I don't have deep knowledge about the solutions from the indivdual SAN vendors (expertice and experience required). In my mind the old rules about separation of physical I/O from the I/O characteristics still applies despite the fact that SAN's normally have high end controllers capable of handling huge I/O loads. DAS (Direct Attached Storage) as the traditional way of providing storage, seems to be much easier to work with when analysing performance issues at the database level mainly because good old Performance Monitor will tell you all you have to know to conclude and defined possible counter measures. This is NOT the same for SAN's! Since a SAN mainly is used for storage provision and consolidation, you have to work your way through the hole SAN setup and identify every piece of software that is generatig I/O against the SAN. Add some interconnect (normally fiber switches, HBA's etc.) in between and you have multiplied the complexity by at least PI.

Add another overhead for hosted solutions where several customers normally share the same infrastructure and the picture starts to be rather though to control. During my search I found another blog discussing the same issues and his summary resembles much of my own experience.

So this is yet another example of how developments in technology actually complicates the everyday work for both customers and consultants, and this is clearly something to account for when doing the TCO matrix.

Based on this, customers should evaluate SQL Server storage and consider to implemented database storage as DAS. Do you agree?

Current challenges and issues

In my new role I have been busy working with a couple of hosted solutions experiencing various stability and performance issues. It’s easy to conclude that hosting AX solutions externally is demanding and that it requires a lot of attention from the customer to establish a good working relation between the hosting partner and the AX partner.

Even more important is the ability to regulate the responsibilities between the different parties. Simple things like for instance database maintenance must be defined in a way that separates regular maintenance not requiring AX knowledge and the opposite. Typical maintenance tasks like reorganizing and re indexing falls into the first category since it can be performed by any DBA (no changes of the definitions or design). When it comes to maintenance requiring AX knowledge (requiring changes in tables node in the AOT), it’s as important to makes sure the AX partner has the necessary access to SQL Server to be able to utilize all the valuable information provided in the form of Dynamics Management Views (I’m not mentioning Oracle here, since it seems like SQL Server is the dominating RDBMS for AX at least here in Norway).

SQL Server 2005 brought a lot of good news in this area and SQL Server 2008 took this even further with the introduction of a brand new Activity Monitor and the Performance Data Collection. Developers should in my opinion pay more attention to the load that their customizations put on the database server and examination of query plans, should be obligatory before releasing changes in the production solution.

In addition to the simple examples mentioned above, my experience says that even if each solution has different characteristics, some common areas can be defined to guide the approach. Without going into the details, the following summarizes what I normally define as the key sources for each category:

Stability
• Network related issues (AOS -> database, AOS -> application share)
• Operational knowledge (description of the relationships between the different server roles in the AX solution and routines describing how to perform a controlled stop – start sequence)
• Pro active maintenance of the AX application and kernels (implementing hot fixes and roll ups for current SP level, new SP)

Performance
• Physical disk I/O at the database level (separation and isolation, Raid levels, sector alignment, block size) regardless of DAS, SAN, NAS heads etc. (general rules apply)
• General load on database instance and utilization of system resources (both OS and SQL Server internals)
• Database configuration, usage and maintenance (best practice configuration, indexes, transactions, space allocation, index maintenance)
• Customizations (caching, run on, query width and selectivity)
• Identify and implement hot fixes related to performance issues (both application and kernels)

While talking about database configuration, I would very much recommend the Check lists that the AX Performance Team has compiled and published on their blog. This is vital information for everyone involved in installing and configuring AX, but also as a general source for hosting partners and customers. The best thing is that this is based on experience and best practice, and in my opinion the value of their recommendations goes beyond AX (general for OLTP applications).

In addition to working with troubled solutions (time consuming, but very motivating), I have implemented “my first” AX solution in a pure virtualized environment including SQL Server 2008 Enterprise. I’m excited about this solution mainly because it’s always interesting to make new experiences. Many people have probably walked this path already, but it’s a first time for everything.
This finalizes my first blog entry in several months, but I will be paying more attention to my blog from now on.

Happy weekend!