Configure a Microsoft Dynamics CRM organization for database mirroring

  • Article

 

Applies To: Dynamics CRM 2013

Note

The previously mentioned computers (MSCRM_Primary, MSCRM_Mirror, and MSCRM_Witness) must all have a network connection and must be running an edition of SQL Server that supports database mirroring.

Mirror the organization database

  1. On the instance of Microsoft SQL Server 2008 that stores the primary database (MSCRM_Primary), start SQL Server Management Studio.

  2. Expand Databases, right-click OrganizationName_MSCRM, where OrganizationName is the name of your organization, and then click Properties. Verify that the Microsoft Dynamics CRM organization database recovery model is set to Full. This is required for a database that will be mirrored.

    1. To verify the recovery model, on the Database Properties window under Select a page, click Options. The recovery model appears in the Recovery model list. Select Full.

    2. Click OK to close the Database Properties window.

  3. Make a full backup of the organization database. For more information about how to do this, see the Microsoft SQL Server Management Studio Help.

  4. Restore the full backup of the primary (MSCRM_Primary) to the mirror instance (MSCRM_Mirror) by using the RESTORE WITH NORECOVERY option, which is required for database mirroring. For more information about how to do this, see the SQL Server Management Studio Help.

  5. Configure database mirroring. To do this, start SQL Server Management Studio, connect to the instance of SQL Server where the Primary database (MSCRM_Primary) is located, and then follow these steps:

    1. Expand Databases, right-click the Microsoft Dynamics CRM organization database that you want to mirror, point to Tasks, and then click Mirror.

    2. Click Configure Security, and then, on the new window, click Next.

    3. On the Include Witness Server window, select Yes, and then click Next.

    4. On the Choose Servers to Configure window, select Witness server instance, and then click Next.

    5. On the Principle Server Instance window, in the Principal server instance list, select the instance of SQL Server (MSCRM_Primary) where the Microsoft Dynamics CRM organization database is located. In addition, you can change the default settings for the listener port, encryption, and endpoint name. Click Next.

    6. On the Mirror Server Instance window, in the Mirror server instance list, select the instance of SQL Server (MSCRM_Mirror) where you restored the Microsoft Dynamics CRM organization database in the previous step. You can change the default settings for the listener port, encryption, and endpoint name, if it is necessary. Click Next.

    7. On the Witness Server Instance window, in the Witness server instance list, select the computer that you want to designate as the Witness (MSCRM_Witness). At this point, you can change the default settings for the listener port, encryption, and endpoint name. Click Next.

      If you are connecting to an instance of SQL Server that is running Microsoft SQL Server 2008 Express Edition, verify that remote connections are supported. To do this, on the computer where Microsoft SQL Server 2008 Express Edition is installed, start SQL Server Configuration Manager and make sure that the remote-connections feature is enabled.

    8. On the Service Accounts window, enter the Active Directory service account for each instance. We recommend that you specify the same service account for all three partners in the mirroring session. Click Next.

    9. Click Finish. The wizard runs through a list of verification checks. Upon completion, click Start Mirroring.

  6. If you want to mirror the configuration database (MSCRM_CONFIG), you must complete similar steps to establish the mirror and update the SQL Server connection string in the registry. To do this, follow these steps:

    Warning

    Serious problems might occur if you modify the registry incorrectly by using Registry Editor or by using another method. These problems might require you to reinstall the operating system and Microsoft Dynamics CRM. We can’t guarantee that these problems can be resolved. Modify the registry at your own risk.

    1. On the computer that is running Internet Information Services (IIS), where the Microsoft Dynamics CRM web application is installed, start Registry Editor and locate the following registry subkey:

      HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM\

    2. Right-click configdb, and then click Modify. Insert Failover Partner=MSCRM_Mirror; into the connection string, after the DataSource value. The complete connection string will appear similar to the following example, where MSCRM_Primary is the primary SQL Server\instance name and MSCRM_Mirror is the mirrored SQL Server\instance name:

      Data Source=MSCRM_Primary\SQL1;Failover Partner=MSCRM_Mirror\SQL2;Initial Catalog= MSCRM_CONFIG;Integrated Security=SSPI

    3. Click OK, and then close Registry Editor.

  7. Create the SQL logins for the Microsoft Dynamics CRM security groups on the Mirror server (MSCRM_Mirror). To do this, follow these steps:

    1. On the SQL Server (MSCRM_Primary) start SQL Server Management Studio, and connect to the Primary database.

    2. Expand Databases, expand the OrganizationName_MSCRM database where OrganizationName is the name of your organization, expand Security, and then double-click Users.

    3. Copy and paste the names, including the domain name and the GUIDs, (the GUIDs are located at the end of the name) for the following groups:

      • PrivReportingGroup

      • ReportingGroup

      • SQLAccessGroup

      To do this, right-click the SQL user name, click Properties, in the User name box, right-click the whole value, and then click Copy. Then, in a text editor such as Notepad, paste the contents. Repeat these steps for each group until you have the contents for all three groups.

    4. In SQL Server Management Studio, connect to the SQL Server Mirror instance (MSCRM_Mirror).

    5. Expand Databases, expand Security, right-click Logins, and then click New Login.

    6. Switch to the text editor in which the SQL users were previously pasted, and copy the contents of one of the groups.

    7. Switch to the Login –New form that was opened in step e. In the Login name box, paste the contents into the box, and then click OK. The SQL user contents should resemble the following example:

      DomainName\SQLAccessGroup {859409f6-c4a5-4cb6-86f2-af264520ea10}

    8. Repeat steps e through g to create SQL logins for the remaining groups.

  8. Update the configuration database to specify the mirror. To do this, follow these steps:

    Important

    Back up the Microsoft Dynamics CRM configuration database (MSCRM_CONFIG) before you run these update statements.

    1. Run the following update statement against the configuration (MSCRM_CONFIG) database:

      Update Organization set ConnectionString = 'Provider=SQLOLEDB;Data Source=MSCRM_Primary\SQL1;Failover Partner=MSCRM_Mirror\SQL2;Initial Catalog=Organization_MSCRM;Integrated Security=SSPI' where DatabaseName = 'Organization_MSCRM'

      • Replace MSCRM_Primary\SQL1 with the name of the Primary instance of SQL Server. If you are using the default instance, do not include an instance name, such as in this example, which is \SQL1.

      • Replace MSCRM_Mirror\SQL2 with the name of the Mirrored instance SQL Server. If you are using the default instance, do not include an instance name, such as in this example, which is \SQL2.

      • Replace Organization_MSCRM with the name of the organization database.

    2. Run the following update statement against the configuration (MSCRM_CONFIG) database:

      Update Organization set MirroredSQLServerName = 'MSCRM_Mirror\SQL2' where DatabaseName = 'Organization_MSCRM'

      • Replace MSCRM_Mirror\SQL2 with the name of the Mirrored instance of SQL Server. If you are using the default instance, do not include an instance name, such as in this example, which is \SQL2.

      • Replace Organization_MSCRM with the name of the organization database.

  9. After you make these changes, reset IIS on the computer that is running Microsoft Dynamics CRM. To do this, click Start, and then click Run. In the Open box, type iisreset, and then click OK.

See Also

Configure an organization database for SQL Server 2008 database mirroring
Monitor and test for failover