SQL Server High Availability in a Microsoft Dynamics AX 2009 Environment

If your organization wants to implement high availability for Microsoft SQL Server 2008 R2 in a Microsoft Dynamics AX 2009 environment, you should first understand some of the typical approaches and the risks and benefits of each one as it relates to your overall backup strategy.  If you don't have a backup strategy, it is important to figure one out before choosing which high availability approach is right for you. 

In this article I will explain how database mirroring, failover clustering and, log shipping are used and whether or not those models are viable for a Dynamics AX environment.

Database mirroring

Database mirroring keeps a single mirror database in recovery mode, ready to failover almost instantaneously. The fact that the database is in recovery mode means it is not accessible by clients. The two possible modes for database mirroring are synchronous (high-security) and asynchronous (high-performance). Synchronous means the transaction is completed on the mirror database first, and then completed on the principal database. Because it is applied to the mirror database first, it makes sure that the mirror is as up to date as possible which makes it safer but slower. Asynchronous means the transaction is completed on the principal database first and then on the mirror database. Because it doesn't have to wait for the mirror to complete first, it is faster but less safe. To have automatic failover, you need a witness server in your configuration that monitors the principal database and the mirror database. Unfortunately, in an AX environment, automatic failover is not possible because the AOS is not failover-aware. To do the manual failover, you would need to change the AX server configuration on your AOS to point to the mirror database. When the principal goes offline, you would ...