axinthefield: Database Mirroring with Dynamics AX

[Blog bot]

Источник: http://blogs.msdn.com/b/axinthefield...namics-ax.aspx
==============

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 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 explicitly documented 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:

1. My starting point for the test was a fully operational all-in-one Dynamics AX environment on a virtual machine. Here's the version information for the key components tested.
   • Windows Server 2008 R2
   • SQL Server 2008 SP2
   • Dynamics AX 2009 SP1 RU6
2. Next I installed a second SQL instance on the same server to host the mirror database. I named this instance MIR. It's important to make sure both instances are running the exact same version.
3. Before setting up database mirroring between the two instances you'll need to set the recovery model for the AX database to full.
4. Perform a full backup of the AX database and restore the backup to the newly installed MIR instance of SQL Server. The full backup needs to be restored so additional transaction logs can still be applied, so you'll need to use the RESTORE WITH NO RECOVERY option.
5. Once the database has been restored to the MIR SQL instance you'll need to create a SQL Server login for the AOS account. This login needs to be the same on both instances of SQL Server so the AOS service can connect to whichever one is configured as the principle. In most cases it will likely be a domain service account. In my case I just used NT AUTHORITY\NETWORK SERVICE. Logins exist at the SQL Server instance level, so they don't come over to the new instance via the database restore or via the mirroring process. That's why you have to do this manually.
6. Configure mirroring between the two databases. In this case I used the following option (High safety without automatic failover).
7. Wait until the databases are fully synchronized.
8. Start or restart the AOS instance so it initiates a database connection to the principle database after the mirroring configuration is complete.
9. Start an AX client session and verify the AX client is operational while connected to the principal database.
10. Manually initiate a failover of the database mirror so the mirror becomes the principle and the principle becomes the mirror.

Test Results / Conclusions:

• The database mirror failover from the principle to the mirror took about 40 seconds. I considered the failover complete when the Database Mirroring Monitor showed the principle and mirror in a "synchronized" state again.
• The AX client session I left running during the failover became unresponsive and produced an infolog message stating that the database was not accessible. The client was in this state for about 50 seconds before it recovered and I was able to continue working again. No intervention was necessary, just some patience. I failed it back and forth 3 times and the failover times were pretty consistent.
• It's important to note that this was all done on a single virtual machine with limited resources and that no transactions were running in the background during the test. The goal of this test was to validate functionality not necessarily to measure how long a failover is likely to take in a real world scenario.
• Automatic failover with a witness wasn't explicitly tested here, but it's reasonable to assume that there wouldn't be any difference in the results from a Dynamics AX perspective. The AOS isn't aware of and therefore doesn't care how the failover is actually initiated (manually or automatic).

Based on the results of this test, database mirroring is certainly a viable option for Dynamics AX database high availability.

References:

• Synchronous Database Mirroring (High-Safety Mode): http://msdn.microsoft.com/en-us/library/ms179344.aspx
• How to: Prepare a Mirror Database for Mirroring: http://msdn.microsoft.com/en-us/library/ms189047.aspx
• How to: Configure a Database Mirroring Session: http://msdn.microsoft.com/en-us/library/ms188712.aspx
• How to: Manually Fail Over a Database Mirroring Session: http://msdn.microsoft.com/en-us/library/ms186348.aspx
• Reconnecting to a Database Mirroring Session: http://msdn.microsoft.com/en-us/library/ms366199.aspx

Источник: http://blogs.msdn.com/b/axinthefield...namics-ax.aspx