Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

Set configuration and organization databases for SQL Server 2012 AlwaysOn failover

Applies to: Microsoft Dynamics CRM 2011

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

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 string 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

Known issues

Prerequisites

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

  • Two Windows Server 2008 R2 or 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 2008 R2 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).

  • Microsoft Dynamics CRM 2011 with Microsoft Dynamics CRM 2011 Update Rollup 6 or later update rollup deployed.

  • For multi-subnet failover support, you must apply Update 4.0.2 for Microsoft .NET Framework 4 – Runtime.

SQL Server 2012 2-node failover cluster instance
ImportantImportant
Microsoft Dynamics CRM 2011 Update Rollup 6 or later update rollup is required to use Microsoft SQL Server 2012.

Microsoft Dynamics CRM 2011 databases are supported with SQL Server on Windows Server 2012. Microsoft Dynamics CRM Server 2011, Microsoft Dynamics CRM Reporting Extensions, and Microsoft Dynamics CRM E-mail Router require Microsoft Dynamics CRM 2011 Update Rollup 13 or a later update rollup to run on Windows Server 2012. For more information, see Supported Windows Server 2012 editions.

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. For more information, see 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. To enable SQL Server 2012 AlwaysOn running on Windows Server 2008, see A hotfix is available to let you configure a cluster node that does not have quorum votes in Windows Server 2008 and in Windows Server 2008 R2.

  2. In SQL Server Management Studio

    SQL Server Management Studio, create an availability group; specify the configuration and organization databases, and primary and secondary replica. For more information, see 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 2011 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.

    ImportantImportant
    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 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 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.

  4. Click the User Mapping page and select the MSCRM_CONFIG database and all the OrganizationName_MSCRM databases, and then click OK on the Login - New dialog box to close and save the new login.

  5. Repeat steps 2 through 4 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 folder path to the Microsoft.Crm.SqlClr.Helper.dll file. By default, this is located in the \\<serverName>\Program Files\Microsoft Dynamics CRM\Tools\ folder, where the Deployment Tools server role is installed.

    IF NOT EXISTS (SELECT * FROM sys.asymmetric_keys WHERE name = 'MSCRMSqlClrKey') BEGIN EXECUTE ('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 string in the Windows registry

Update the SQL Server connection string in the Windows registry on the computer running IIS where the Web Application Server role is installed. To do this, follow these steps.

WarningWarning
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 cannot 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 Web Application Server role 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.

    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

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

    3. If the Microsoft Dynamics CRM deployment is running in a multiple subnet, add ;multisubnetfailover=true to the end of the configdb value to form the connection string.

      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.

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.

WarningWarning
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' where DatabaseName = 'MSCRM_CONFIG'
  • 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

If you are running on a multi-subnet, add the following parameter to the end of the connection string before you run the statement:

;multisubnetfailover=true

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 = 'MSCRM_CONFIG'

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.

WarningWarning
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 2011 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.

Known issues

This section describes issues that may occur when you configure Microsoft Dynamics CRM databases for SQL Server AlwaysOn failover cluster instance and availability groups.

Some Deployment Manager organization features do not work after failover

After a SQL Server 2012 failover that uses availability groups for one or more organization databases, certain Microsoft Dynamics CRM Deployment Manager organization operations do not work, such as the following:

  • Edit Organization. An error message is returned when you try to change an organization.

  • Organization properties. An error message is returned when you try to view the properties for an organization.

  • Organization Browse and Update feature functionality does not appear.

This issue occurs because Deployment Manager uses the SQLServerName column in the organization table of the configuration database to locate and connect to the SQL Server. The value contains the original SQL Server primary replica before the failover and is not updated to the new primary replica when a failover occurs.

To work around this issue, you can manually update the SQLServerName value to reference the new primary replica after a failover has occurred. To do this, run the following SQL query where New_Primary is the instance of SQL Server that is the new primary replica and Old_Primary is the name of the instance of SQL Server that was the original primary replica before the failover.

WarningWarning
The following SQL query will set all organization databases in the deployment to the same instance of SQL Server.


Update MSCRM_CONFIG.dbo.Organization set SQLServerName='New_Primary' where SQLServerName='Old_Primary'

See Also

Send comments about this article to Microsoft.

© 2013 Microsoft Corporation. All rights reserved.

Community Additions

Show:
© 2014 Microsoft. All rights reserved.