Is the AOS a "mirror aware" service? If so, what's the user experience when a failover happens? These are two of the questions I recently received from a customer as they were planning their database high availability solution.
When it comes to database high availability for Dynamics AX, a SQL cluster is by far the most commonly used technology. Most of the time when I see database mirroring, it's typically used as a disaster recovery solution to ensure a recent copy of the database exists at an offsite location where an entire DR environment (SQL Server, AOS, etc.) is sitting idle, and ready to be used if the primary data center goes offline. Database mirroring with automatic failover isn't something that fits with this type of a situation. You'll generally want a real person making the decision when it comes to failing over to a completely different environment in a different location. But, the question from this customer was about using database mirroring in place of a SQL cluster for database high availability where the AOS services would remain the same, and just the database would be moving from one server to another via mirroring. In this case, automatic failover would definitely be useful.
Will the AOS stay connected to the database during a database mirroring failover just as it does with a SQL cluster failover, or will it be necessary to make a change to the Dynamics AX server configuration utility and restart the AOS to get it connected after a failover? After some quick searching I found there wasn't much explicit documentation on how this works, so I decided it would be a good scenario to setup and test. Here's what I did.
Test Environment Configuration:
Test Results / Conclusions:
Based on the results of this test, database mirroring is certainly a viable option for Dynamics AX database high availability.
Tom, Thanks and very helpful informations. Did you try Automatic failover with help of Witness server? Does that work with AOS. Does AOS service recognize automatic failover and switch to mirrored server (without having to stop/restart AOS service). We are looking at doing SQL mirroring for one of our customers on AX 2012.
You installed both SQL instances on the same server. What happens when the instances are installed on different servers (as one would do in a real setup)?
I'm guessing that the automatic failover will fail, as the AOS configuration points to the first server and as a result will stall/crash/... Only a manual reconfiguration of the AOS (point it to the new principal SQL server) will fix this.
When you use failover clustering, the AOS will point to the cluster and not the individual server(s). Therefor failover clustering will allow automatic failover as nothing changes that the AOS is aware of.
Except for this issue, I realy like your article. It takes quite some time to set this up.