Share via

Set configuration and organization databases for SQL Server AlwaysOn failover

  • Article

 

Applies To: Dynamics 365 (on-premises), Dynamics CRM 2016

Microsoft SQL ServerAlwaysOn 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 365 Server so that the configuration and organization databases are part of a two-node AlwaysOn failover cluster instance that uses availability groups.

Important

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

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

In this topic

Prerequisites

Enable SQL Server AlwaysOn

Create the availability group

Create the SQL logins for the Microsoft Dynamics 365 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 365 services

Monitor the availability group and test for failover

Prerequisites

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

  • Two SQL Server servers running in a two-node failover cluster configuration with shared storage, such as a SAN, NAS, or Windows Storage Server. For a list of supported versions of SQL Server, see SQL Server editions.

  • Two servers running SQL Server on Microsoft Windows Server that will be replicas in the SQL ServerAlwaysOn 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 support AlwaysOn, see High Availability (AlwaysOn). For more information about how to create a SQL Server failover cluster, see, Create a New SQL Server Failover Cluster (Setup).

SQL Server 2012 2-node failover cluster instance

Enable SQL Server AlwaysOn

The following steps for establishing the AlwaysOn features use Microsoft SQL Server Management Studio. You can also use Transact-SQL or SQL ServerPowerShell. More information: Getting Started with AlwaysOn Availability Groups (SQL Server)

Note

Although the steps here are specific to Microsoft SQL Server 2012, you can use similar steps for other supported versions of 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 availability group that includes a Microsoft Dynamics 365 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 365, and then click Next.

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

    Important

    Requirements to add a database to an availability group:

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

    • Full backup of the database.

  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 that 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, which runs validation checks for the new availability group, appears. Resolve any errors before you create the availability group. 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 365 security groups on all secondary replicas

Create the SQL logins for the Microsoft Dynamics 365 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, and then 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 servers that are running a Microsoft Dynamics 365 Server role, including the Microsoft Dynamics 365 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 365. 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 365 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 365 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 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 shown here:

      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 365 Server role, including the server running Microsoft Dynamics 365 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 365 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 365 organization database is located.

  • Replace OrganizationName_MSCRM with the name of the organization database.

Additional parameters available

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 365 services

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

Warning

All active Microsoft Dynamics 365 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 365 services in the deployment on all servers where Microsoft Dynamics CRM Server 2013 features are installed.

Monitor the availability group and test for failover

Microsoft SQL Server 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 365, and then click Show Dashboard.

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

Test for failover

  1. Verify that a Microsoft Dynamics 365 can connect to the availability group. For example, start Internet Explorer, and connect to the Microsoft Dynamics 365 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 365 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
Upgrading Microsoft Dynamics CRM Server

© 2016 Microsoft. All rights reserved. Copyright