Short note on AX and database mirroring in SQL Server 2008

The last days, I have been working with a customer running AX 2009 and SQL Server 2008 SP1 Standard (x64) on Windows Server 2008 SP2. The solution suffered from general performance issues that possibly could have a lot of sources. As usual my attention was around SQL Server and this time I started looking at Wait Stats which showed a waste amount of waits related to mirroring. After some Googling, I was a little bit confused about this beeing normal or not. The nature of database mirroring could in fact result in high Wait Stat values since the processes involved, is mostly sleeping (suspended). But I choosed to consider this as a possible source since the nature of database mirroring in my oppinion, does'nt fit the nature of AX as a classic OLTP application. And since disabling database mirroring is an isolated and low risk operation, we choosed to stop mirroring for all database (around 10). After this, the overall performance increased and the customer reported this almost immediately. It's a little bit early to conclude, but the initial responce seems pretty promising.

I will not go into the details around how mirroring was set up and the underlying infrastructure, but since AX is a OLTP application with a lot of inserts, deletes and updates within small, repeating and identical transactions during a normal business day, the processes responsible for doing the mirroring seems to have a big payload on the source server. Remember that database mirroring in fact is a repliction technology and that changes in the primary database, must be replicated to the secondary database (add requirements for low network latency and quality of service). It could of course be other applications that contributes more to the payload, but since time is important in situations like this, we only focused on the SQL Server instance and less on the individual databases.

Another lesson is to be very restrictive when deploying several databases under the same instance as your primary AX database. The payload of each database should be evaluated and categorized to avoid resource conflicts between databases (for instance queries in the wild, long running transactions and cursor intensive applications). Despite all the positive effects of consolidating databases, the complexity increases a lot when performance issues arise for AX since the source could be another application. My general advise is to prioritize a dedicated databaseserver for your AX production database since this makes it a lot easier to optimize AX. Yes, it will have an impact on the cost (hardware and licenses), but it will normally pay off in the long for customers relying on AX as a mission critical solution. This is an advice I generally bring to my customers.

If interesting, I can throw in some more details. And yes - I'm aware of database mirroring beeing one of the supported HA configurations for AX. Please add a comment to indicate further details.

So long.