One of the coolest features of Microsoft CRM 4.0 is the support of the SQL Server 2005 database mirroring. If you are unfamiliar with this technology, it provides failover protection at a database level with minimal impact to the user.  When compared against other failover technologies, mirroring has some core advantages:

• Failover can be configured to be automated.
• Database downtime during failover is minimal (less than 10 seconds).
• Mirroring can be set up to use a single SQL Server with multiple instances.
• Setup is relatively quick and simple. 

Unlike clustering, mirroring does not provide server wide / OS protection. For additional details on mirroring or comparison of failover technologies, review this whitepaper.

 

We ran though a couple of simple tests to implement mirroring with CRM. Our test focused on mirroring only the MSCRM and not configuration database. Since CRM uses a different connection string per database (stored in the registry), the process for mirroring the configuration database is the same. 

Our test used the following configuration:

• Primary database – This is the MSCRM database. CRM was installed on the SQL Server default instance.
• Mirrored database – Used a new instance called “MIRROR” on the same SQL Server as the primary database.
• Witness: A secondary server running SQL Server Express

Note: Database mirroring is only supported for evaluation purposes for versions prior to SQL Server 2005 SP2 and is disabled by default. To enable, a trace flag of 1400 needs to be added to the startup parameter list. To add this value, follow these steps:

1. Open SQL Server Configuration Manager
2. Select SQL Server 2005 Instances
3. Select the SQL Server instance.
4. Right mouse click and go to properties.
5. Select the advance tab.
6. In the line of Startup Parameters, add “-T1400”. Parameters must be separated by semi-colons. To read more about trace flags, see Trace Flags (Transact-SQL).

 

Enabling database mirroring for the MSCRM database

SQL Server provides a fairly intuitive wizard for setting up mirroring. However, there are some steps outside of the wizard that need to be taken to complete the setup:

1. Open SQL Server Management Studio
   • Verify the MSCRM database’s recovery mode is set to “full” (required for all mirrored databases).
   • Take full backup of primary database.
   • Restore the full backup of the primary to mirror instance using “WITH NO RECOVERY” option.
   • CRM has several accounts and roles associated with it. These accounts need to match on the mirrored server as they do on the primary server. The best way to ensure the accounts match is to script the users from within Microsoft SQL Server Management Studio:
     • Right mouse click on the login to move, and select “Script Login as -> CREATE To…”. After creating the script, execute on the mirrored server.
     • Verify the CRM service account permission’s match between the primary and mirrored systems.
     • Configure database mirroring by running through wizard and selecting appropriate primary, mirror and witness. It is recommended to have same service account for all three instances. The CRM connection string needs to be updated to include the “Failover Partner” switch. Since we are mirroring the MSCRM database, we want to update the “database” registry value on the CRM Application server.
2. Open the CRM database connection string in organization table in the MSCRM_CONFIG database: 
   • Add “Failover Partner=[SQL Server Mirror]
   • The connection string should be similar to this:

Provider=SQLOLEDB;Data Source=CRMSQL;Failover Partner=CRMSQL\MIRROR;Initial Catalog=Org_MSCRM;Integrated Security=SSPI

Update: In the original post of this blog, the connection string reference was to the registry. We have found in a recent test that CRM 4.0 does not use the connection strings in the registry for the MSCRM databases, but uses the organization table instead.

Note : In our test, we were on the same physical SQL Server (different instance), so the CRM windows accounts / groups were already created. Moving to a different physical box will require the windows accounts / groups are set up as well.

 

Monitoring the mirror status and testing a failover 

The mirrored database can be configured to automatically failover or manually. For testing, we will force the failover. To do this, open SQL Server Management Studio, right mouse clicking the primary database and select “Mirror”.

Within the database properties / mirror dialog window, you can monitor the status of the synchronization between the primary and mirrored database. You can also force the failover by clicking on the “Failover” button. Once you failover, SQL Server will switch the roles between the primary and mirrored database making the mirror the primary and the primary the mirror.

To test the failover, I recommend entering in data into CRM, failing over the database, go back into CRM and verify the data is present and rerun the process again using the failed over database for data entry. You can also notice the behavior the end user would see in the event of a failover and the time it takes (generally less than 10 seconds).

 

Final thoughts 

CRM’s support of database mirroring offers a fast and relatively easy way to provide failover protection. However, just as with any failover protection, mirroring has its pros and cons. These options should be reviewed carefully before implementing in a production environment (review this whitepaper for more information).

 

Resources

• Database Mirroring and Log Shipping Working Together
• Database Mirroring in SQL Server 2005
• Database Mirroring: Alerting on Database Mirroring Events
• Database Mirroring: Best Practices and Performance Considerations

 

This posting is provided "AS IS" with no warranties, and confers no rights.