Dynamics CRM 2011 High Availability – SQL Server Database Mirroring

Microsoft Dynamics CRM 2011 supports multiple technologies to optimize the availability of the the data store and the application layer. In my article collection on the PSO Website I want to provide an introduction and a short walkthrough to each of the methods.

The first supported technology, which was already supported in Microsoft Dynamics CRM 4.0 is SQL Server database morroring. Database mirroring is depricated by Microsoft for SQL Server versions higher than 2012. Instead Microsoft advises the use of the new AlwaysOn technology which came first with SQL Server 2012. AlwaysOn is now supported for Dynamics CRM 2011 but NOT for Dynamics CRM 4.0 as you can review in a Microsoft KB article.

If you now decide to use database mirroring, for example if you plan to use an older Version of SQL Server here are the steps, you need to follow for a successfull setup.

How does database mirroring work? In database mirroring keeps two copies of the same database in sync, whereas one copy has to reside on a different SQL Server instance. The instances of the SQL Server database engine do not have to be installed on the same physical machine, but can be setup in different locations. One of the Server instances, which is called the principal server communicates with the clients. The other instance, the mirror server keeps the copy of the database and is updated by the changes done to the principal Server.

SQL Server database mirroring
SQL Server database mirroring

The synchronization of the servers works somehow similiar like the log shipping technology of SQL Server. The changes in the transaction log are shipped from the principal to the mirror Server and then are replayed on the mirror instance.  SQL Server offers two diffrent mirroring modes:

  • high performance mode: In high performance mode the synchronization of the mirror instance is done asynchronously. This allows a transaction to comit before the mirror is updated, which is very fast but less secure as it can leave the mirror in an unsynchronized state in case of failure. Data can be lost.
  • high safety mode: In high safety the synchronisation of the mirror instance is done synchronously. This means that a transaction cannot comit as long as the mirror server has not updated all the changes. This mode is definately slower but reduces the risk of data loss to a minimum.

When you setup the SQL database mirroring session in high safety mode you can initiate an automatic failover, from the principal server to the mirroring partner. In this case all Client queries are no longer routed to the principal but to the mirror instance in case the principal instance becomes unavailible. To reastablish the state before the the failover you need to get both SQL Server instances back into sync. The automatic failover Option requiers a third computer running SQL Server (this instance can be a PC with SQL Express installed) the so called witness server. The Job of the witness server is to „ping“ the principal SQL Server database engine and to initiate the failover in case the principal SQL Server database engine does not respond.

Automatic failover with witness server
Automatic failover