Share via

Set configuration and organization databases for SQL Server 2012 AlwaysOn failover

  • Article

 

Applies To: Dynamics CRM 2015

Microsoft SQL Server 2012 AlwaysOn Failover Cluster Instances (FCI) and AlwaysOn Availability Groups provide a comprehensive high availability and disaster recovery solution. This topic describes how to configure an existing on-premises deployment of Microsoft Dynamics CRM Server so that the configuration and organization databases are part of a two-node SQL Server 2012 AlwaysOn failover cluster instance that uses availability groups.

Important

If you’re upgrading Microsoft Dynamics CRM configuration and organization databases that already have Microsoft SQL Server 2012 AlwaysOn FCI configured, you don’t have to perform this procedure again.

For more information about these SQL Server 2012 high availability solutions, see AlwaysOn Failover Cluster Instances and AlwaysOn Availability Groups.

In this Topic

Prerequisites

Enable SQL Server 2012 AlwaysOn

Create the availability group

Create the SQL logins for the Microsoft Dynamics CRM security groups on all secondary replicas

Update the configuration database connection strings in the Windows registry

Update the connection string in the configuration database

Restart IIS and Microsoft Dynamics CRM services

Monitor the availabity group and test for failover

Prerequisites

In this topic, a simple two-server instance failover cluster will be configured that requires the following:

  • Two Windows Server 2012 servers running in a two-node failover cluster configuration with shared storage, such as a SAN, NAS, or Windows Storage Server.

  • Two servers running SQL Server 2012 on Windows Server 2012 that will be replicas in the SQL Server 2012 AlwaysOn instance failover cluster. The SQL Server primary replica instance has both the configuration (MSCRM_CONFIG) and organization (OrganizationName_MSCRM) databases.

    For more information about what versions of SQL Server 2012 support AlwaysOn, see High Availability (AlwaysOn). For more information about how to create a SQL Server 2012 failover cluster, see, Create a New SQL Server Failover Cluster (Setup).

SQL Server 2012 2-node failover cluster instance

Enable SQL Server 2012 AlwaysOn

The following steps for establishing the AlwaysOn features use Microsoft SQL Server 2012 Management Studio. Alternatively, you can use Transact-SQL or SQL Server PowerShell. More information: Getting Started with AlwaysOn Availability Groups (SQL Server)

  1. In SQL Server Configuration Manager, enable AlwaysOn Availability Groups on the primary and secondary replica. For detailed steps, see Enable AlwaysOn Availability Groups.

  2. In SQL Server Management Studio, create an availability group; specify the configuration and organization databases, and primary and secondary replica. More information: Creation and Configuration of Availability Groups (SQL Server)

Create the availability group

The following steps create a single SQL Server 2012 availability group that includes a Microsoft Dynamics CRM configuration database and a single organization database. Additionally, a listener will be added to the availability group.

Create the availability group for the configuration and organization databases

  1. On the SQL Server that will be the primary replica, start SQL Server Management Studio.

  2. In Object Explorer, right-click AlwaysOn High Availablility, and then click New Availability Group Wizard.

  3. When the wizard appears, click Next.

  4. On the Specify Name page, type a name for the availability group, such as Dynamics CRM, and then click Next.

  5. On the Select Databases page, select the MSCRM_CONFIG and OrganizationName_MSCRM databases, and then click Next.

    Important

    Notice the following requirements for adding a database to an availability group:

    • The recovery model must be set to Full. By default, organization databases (OrganizationName_MSCRM), are set to Simple.

    • A full back up of the database is required.

  6. On the Specify Replicas page, click Add Replica, type the name of your replica SQL Server in the Server name box (such as CRMNode2 in this example), and then click Connect. The replica server instance appears in the Server Instance list.

  7. Click the Endpoints tab and verify the both replicas are in the list. Scroll right to the SQL Server Service Account and make sure a domain user account is specified. A domain user account for the SQL Server service is required for SQL Server failover clustering.

  8. Click the Listener tab. An availability group listener enables faster client connection failover in multi-subnet scenarios. Enter the following information.

    • Listener DNS name: Type a name for the listener.

    • To complete the listener creation, specify a TCP port, and set the Network Mode to Static IP. Click Add to enter the IP address, and click OK. For more information about these properties, click Help in the New Availability Group wizard.

      Click Next.

  9. Determine what, if any, additional changes to make to the replicas, and then click Next. For more information, click Help in the New Availability Group wizard.

    1. On the Select Data Synchronization page, select the data synchronization method. If you select Full, you must add a network share that can be accessed by all replicas. Notice that the SQL Server service account requires read\write permissions to the share. For more information, click Help in the New Availability Group wizard.

      Click Next.

  10. The Validation page appears that runs the validation checks for the new availability group. Errors must be resolved before the availability group can be created. Click Next.

  11. On the Summary page, verify that the choices are correct, and then click Finish.

Create the SQL logins for the Microsoft Dynamics CRM security groups on all secondary replicas

Create the SQL logins for the Microsoft Dynamics CRM security groups on the secondary replica. To do this, follow these steps:

  1. Start SQL Server Management Studio and connect to the secondary SQL Server replica (CRMNode2).

  2. Expand the Security node, right-click Logins and then click New Login.

  3. In the Login name box, click Search, make sure that Groups is included in the Select this object type (if not click Object Types and add it). Type DomainName\PrivReportingGroup, click Check Names, and when the name resolves correctly, click OK.

    Note

    The actual PrivReportingGroup name may be different. Verify the name as it exists in Active Directory.

  4. Repeat steps 2 and 3 for the following security groups.

    • ReportingGroup

    • SQLAccessGroup

Create the MSCRMSqlClrLogin SQL Login

  1. Create the asymmetric key for the MSCRMSqlClrLogin SQL Login

    On all secondary replicas, execute the following SQL statement against the master database to create the asymmetric key, where <path> is the path to the Microsoft.Crm.SqlClr.Helper.dll file that is located on the server where the Deployment Tools server role is installed. For example, specify a UNC path on a share similar to \\crmdeploymenttools\CRMshare\Tools\. By default, Microsoft.Crm.SqlClr.Helper.dll is located on the Deployment Tools server in the C:\Program Files\Microsoft Dynamics CRM\Tools\ folder.

    IF NOT EXISTS (SELECT * FROM sys.asymmetric_keys WHERE name = 'MSCRMSqlClrKey') BEGIN CREATE ASYMMETRIC KEY MSCRMSqlClrKey FROM EXECUTABLE FILE = ’<path>\Microsoft.Crm.SqlClr.Helper.dll’; 
END

  2. Create the MSCRMSqlClrLogin SQL login

    Execute the following SQL statement against the master database to create the MSCRMSqlClrLogin SQL login.

    IF NOT EXISTS (SELECT * FROM sys.syslogins WHERE name = 'MSCRMSqlClrLogin') BEGIN CREATE LOGIN MSCRMSqlClrLogin FROM ASYMMETRIC KEY MSCRMSqlClrKey GRANT UNSAFE ASSEMBLY TO MSCRMSqlClrLogin END

Update the configuration database connection strings in the Windows registry

Update the SQL Server connection string in every Windows registry on all server that are running a Microsoft Dynamics CRM Server role, including the Microsoft Dynamics CRM Reporting Extensions server. 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 the Microsoft Dynamics CRM Server role, start Registry Editor and locate the following registry subkey:

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM\

  2. Right-click configdb, and then click Modify.

    1. If Failover Partner=MSCRM_Mirror is present, remove this from the value.

    2. Change the Data Source from the SQL Server instance name to the availability group listener name.

      The complete connection string should appear similar to the following example, where MSCRMAG is the availability group listener name for the availability group that includes the Microsoft Dynamics CRM configuration database:

      Data Source MSCRMAG;Initial Catalog= MSCRM_CONFIG;Integrated Security=SSPI

      Tip

      The listener name can be located under the Availability Groups node in in SQL Server Management Studio.

    3. If you are using a listener, add ;multisubnetfailover=true to the end of the configdb value to form the connection string. More information: SqlClient Support for High Availability, Disaster Recovery

      A complete configdb value for a multiple subnet should appear as the following:

      Data Source MSCRMAG;Initial Catalog= MSCRM_CONFIG;Integrated Security=SSPI;multisubnetfailover=true

  3. Click OK, and then close Registry Editor.

  4. Repeat the previous steps to add the connection string to the config subkey for each server that is running a Microsoft Dynamics CRM Server role, including the server running Microsoft Dynamics CRM Reporting Extensions.

Update the connection string in the configuration database

Update the organization table in the configuration database to specify the availability group listener name for the client connection string. To do this, run the following update statement against the configuration (MSCRM_CONFIG) database.

Warning

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

Update Organization set ConnectionString = 'Provider=SQLOLEDB;Data Source=AG_Listener_Name;Initial Catalog=OrganizationName_MSCRM;Integrated Security=SSPI';MultiSubnetFailover=True' where DatabaseName = 'OrganizationName_MSCRM'

  • Replace AG_Listener_Name with the availability group listener name where the Microsoft Dynamics CRM organization database is located.

  • Replace OrganizationName_MSCRM with the name of the organization database.

Additional parameters available

Additionally, if you use a port number other than 1433 for the availability group listener add the following parameter where Port_Number is the network port number.

Update Organization set ConnectionString = 'Provider=SQLOLEDB;Data Source=AG_Listener_Name,Port_Number;Initial Catalog=OrganizationName_MSCRM;Integrated Security=SSPI;multisubnetfailover=true' where DatabaseName = 'OrganizationName_MSCRM'

Restart IIS and Microsoft Dynamics CRM services

After you make these changes, reset IIS on the computer that is running the Web Application Server role.

Warning

All active Microsoft Dynamics CRM users will be disconnected.

To do this, click Start, and then click Run. In the Open box, type iisreset, and then click OK.

Restart all Microsoft Dynamics CRM services in the deployment on all servers where Microsoft Dynamics CRM Server 2013 features are installed.

Monitor the availabity group and test for failover

Microsoft SQL Server 2012 Management Studio includes features that make viewing the availability group status and executing a manual failover easy.

View the availability group state

  1. Start SQL Server Management Studio and connect to the instance of SQL Server that is the primary replica in the availability group.

  2. In SQL Server Management Studio, expand Availability Groups, right-click the availability group that you created for Microsoft Dynamics CRM, and then click Show Dashboard.

The dashboard displays all replicas and the synchronization state. Notice that you can view the dashboard from any replica in the availability group.

Test for failover

  1. Verify that a Microsoft Dynamics CRM can connect to the availability group. For example, start Internet Explorer, and connect to the Microsoft Dynamics CRM organization URL.

  2. From the dashboard described in the previous procedure, click Start Failover Wizard to begin the failover process.

  3. Follow the instructions in the wizard to complete the failover.

    After a successful failover, the primary replica becomes the secondary replica and the secondary replica becomes the primary replica.

  4. Switch to the web browser connected to the Microsoft Dynamics CRM organization and verify that you can seamlessly connect to the new primary replica by clicking anywhere in the application.

To switch back to the original primary and secondary replicas, perform the manual failover procedure again on the new primary replica.

See Also

Install or upgrade Microsoft Dynamics CRM Server
Failover Cluster Step-by-Step Guide: Configuring a Two-Node File Server Failover Cluster
Upgrade Microsoft Dynamics CRM Server

© 2016 Microsoft Corporation. All rights reserved. Copyright