Configuring Dynamics CRM 2016/2015 with SQL Server 2014 AlwaysOn Availability
By Shashank Chandra, Sr. Dynamics CRM Consultant
Microsoft SQL Server Availability Groups provide a comprehensive high availability and disaster recovery solution. In this blog post we will see how to configure Dynamics CRM 2016/2015 with SQL Server 2014 AlwaysOn Availability Groups.
The diagram below illustrates the environment setup:
(Source: https://blogs.msdn.microsoft.com/crminthefield/2013/11/27/configuring-sql-server-2012-alwayson-availability-groups-with-dynamics-crm/)
1. Create SQL Server Availability Group
The first step is to create the SQL Server availability group. This link talks about how you can set it up - https://technet.microsoft.com/en-us/library/jj822357.aspx
2. Create SQL Logins for Microsoft Dynamics CRM
The next step is to Create the SQL logins for the Microsoft Dynamics CRM security groups on all secondary replicas. To do this:
a. Start SQL Server Management Studio and connect to the secondary SQL Server replica. Expand the Security node, right-click Logins and then click New Login.
b. 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). TypeDomainName\PrivReportingGroup, click Check Names, and when the name resolves correctly, click OK. Repeat step for the following security groups - ReportingGroup and -SQLAccessGroup
3. Create the MSCRMSqlClrLogin SQL Login
a. 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’;
b. 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
4. 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 CRM Server role, including the Microsoft Dynamics CRM Reporting Extensions server. To do this, follow these steps.
- 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\
- Right-click configdb, and then click Modify.
- If Failover Partner=MSCRM_Mirror is present, remove this from the value.
- Change the Data Source from the SQL Server instance name to the availability group listener name.
- Click OK, and then close Registry Editor.
- 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.
5. 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.
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.
6. 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.
For more information contact today!
by
