Configuring SQL Server 2012 AlwaysOn Availability Groups with Dynamics CRM


Over the past few weeks, I’ve had the need to test a Dynamics CRM environment using Always On Availability Groups (AO AG) from SQL 2012.  While SQL 2012 is fully supported for both CRM 2011 and 2013, and normally a fairly straight-forward installation, adding AO AG to that mix can cause some complexity.  This is especially true when attempting this configuration in a stand-alone test environment where you might not have easy access to shared storage such as a SAN.  In my test environment, I used Hyper-V virtual machines and Microsoft iSCSI software for shared storage.

During my setup and configuration, I kept a list of resources I used and have created a new “curation” over at the new Curah! website from Microsoft.  The curation should provide you with enough resources to get a SQL 2012 AO AG environment up and running with CRM databases.  I would suggest reading the Set configuration and organization databases for SQL Server 2012 AlwaysOn failover first.  The other links in the curation then support that article.

In the end, the diagram below roughly shows what my environment looks like.  With the two SQL Servers as nodes in a Windows Failover Cluster.  Along with AO AG enabled on these with SQL Server 1 acting as the Primary and SQL Server 2 as the secondary.  Dynamics CRM is then configured to point to the SQL AG Listener.

 

SQLAlwaysOnCRMnoFCI

 

Ryan A. Anderson

Follow the conversation:
@pfedynamics | http://www.pfedynamics.com

   


Comments (19)

  1. Jason Peterson says:

    Since the CRM servers are pointing to the listener, did you have to uninstall and reinstall CRM (because it was originally pointing to a single SQL instance originally)?  Or were you somehow able to install CRM and create the config and org databases by pointing directly to the listener after the AO AG was set up?

  2. Ryan Anderson says:

    Hi Jason – take a look at the article here: technet.microsoft.com/…/jj822357.aspx

    It explains how you can update the db connection string in the registry and in the config database to point to the listener.

    Thanks!

  3. Tyler says:

    If I am performing a new install of CRM with SQL 2012 AOAG, could I not just use a temporary database to create the CRM AG and then point the CRM installer to the AG Listener during initial setup? At that point would the MSCRM databases automatically be configured for AlwaysOn replication?

  4. Ryan Anderson says:

    Hi Tyler – I haven't attempted what you mentioned, but not sure if that would work.  I'm not sure if that would update all the connection strings appropriately during the install.  And I'm not sure if the installer would even recognize the AG.  If you try it, feel free to report back any results here.

  5. Maarten Van Damme says:

    Dear Ryan,

    I have 2 SQL servers 2012 running with an always on AG with endpoints and listeners configured. However, I om unable to point to the AG listener because the SQLSERVERAGENT service is not running on the AG. I confirm that on both sql servers the agents are running. Any suggestions?

    Best regards,

    Maarten

  6. Ryan Anderson says:

    Hi Maarten – at what point are you attempting to point CRM to the AG listener?  As mentioned in the TechNet article technet.microsoft.com/…/jj822357.aspx, you will need to install CRM first without using AO AG, then configure CRM as described to update connections strings and point to the AG listener.

  7. Adam Balasa says:

    Having same issues as Maarten. Any input would be appreciated. In reply to you Ryan our install has been configured as you have made mention in your comment. However upon the reconfig to enable reporting (sitting in separate reporting instance) the setup is failing upon the agent check..

    Thanks

  8. Maarten Van Damme says:

    Dear Ryan, thank you for your feedback. I will continue today since I have several other projects running and didn't have time to continue. I'll keep you up to date.

    Best regards,

    Maarten

  9. Maarten Van Damme says:

    Dear Ryan, i have tried everything following exactly the document as described, updated the connection strings and everything else, but still i am getting the same error message when changing to the listener: sqlserveragent is not running on the server 'crmlistener'. So first I have configured everything pointing to one sql server and everything is running fine – also crm. But then in the crm deployment manager i disable the organization and switch to the listener, it doesn't work. Any feedback is greatly appreciated.

    2 x SQL server 2012

    1 x crm 2011 with update 17

    OS for all servers is 2012 server R2

    Best regards,

    Maarten

  10. Maarten Van Damme says:

    Finally got it working, actually I was confused because in the crm deployment manager it is still pointing to a sql server – not the listener, but in fact the failover is working fine. Before I was trying to change to the listener name which didn't work. Can you please confirm that this is normal?

    Thanks,

    Maarten

  11. Isil says:

    Hi Marteen & Ryan,

    First of all thanks for the article Ryan! Was great help.

    We are also still seeing Primary SQL DB when we open Deployment Manager. And again the SQL Server Agent error when pointing to the Listener through DM. Verified that failover is working fine but why cant we see the proper listener name on the DM?

  12. Ryan Anderson says:

    Hi Isil – you won't see the listener name in the Deployment Manager.  As mentioned in the article, the connection string is updated in the registry and database.  The DM will show a SQL instance name, rather than the listener.  Hope that helps!

    Ryan

  13. Isil says:

    Thanks Ryan,

    I will communicate what you've said to our customer. We should be ok as long as failover is working. It could be just a bit confusing for a new administrator though :)

    Best,

    I.

  14. Lin says:

    Hi,

    How do you handle the ReportServer databases, are they in the AG or you install SSRS on one of the primary/secondary node?

    Thanks.

  15. Mark Canning says:

    Regarding SQL Agent Error when CRM 2013 is configured to talk to Listener we have encountered this when trying to upgrade to CRM 2015. The checks run by the installer fail at SQL Agent Verification and the upgrade cannot proceed.

  16. Stephen Connell says:

    With regards to the SQL Server displayed in the Organization properties.  This is taken from the Organization table in the MSCRM_CONFIG database.  The link that Ryan supplied suggests the following SQL:

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

    What this does not do is change the field SqlServerName which is what will be displayed in the properties window of the Deployment manager.

    USE [MSCRM_CONFIG]

    GO

    UPDATE [dbo].[Organization]

      SET

         [SqlServerName] = 'STG_CRM2015SQL'–CRM2015STAGESQL01STG_SH_CRM

    GO

    In this example I'm changing the SqlServerName from the instance to the listener.  This will change the name of the connection in my DM.

    Hope this clears up the issue.

  17. Alan Wells says:

    We converted our standalone SQL server to an AlwaysOn Cluster.  We changed all the registry keys and database tables for the single existing organization and everything seems to be working.  When I tried to create a new organization I get the error SQLSERVERAGENT (SQLSERVERAGENT) service is not running on the server.   Windows firewall is disabled by default on all servers.  I had network make sure that all ports are open between the 2 server IPs and they are not seeing any blocks between them.  Searched all around and can't find anyone else reporting a fix to this.  SQL servers are Windows 2012 R2 with SQL 2012.  CRM application server is also Windows 2012 R2.

  18. Alan Wells says:

    I was finally able to get organizations created.  Some info about our setup. We use a SQL 2012 R2 SQL Cluster and it resides in a separate VLAN that is locked down so that any server wanting to talk to the SQL server has to be granted specific access through the firewall to get to it.  The application server is in another VLAN.  Our SQL servers are all configured to run as named instances and we use a custom port to connect rather than the default port.  This is all open from the app server to the SQL server but for testing we opened all tcp/udp ports.

    We were trying to create new organizations by pointing to the listener.  I tried connecting directly to the server name several different ways.  I tried putting an alias to the listener into the SQL Configuration Manager of the application server.  What finally worked was to add the following.  SQL Alias Name servernameinstancename for the primary cluster node. I put in the custom port and for the server name I just put the server name for the primary cluster node without the instance name.

    When creating the new organization we pointed to the servernameinstancename and all tests passed.  I did get 1 error during the creation process that said:  

    “Action Microsoft.Crm.Tools.Admin.GrantSrsAccessToConfigDBAction failed.  No such host known.”

    I was able to ignore and the creation finished.  Once done we updated the config database and changed the name of the SQL server for each organization to the SQL Listener name.

    Everything seems to be working fine now.  Seems odd that we would have to do all this manual process just to add an organization.  I will be doing further testing to determine if something was still being blocked on the network side even though it was supposed to be fully open.

  19. Michael says:

    Hi,

    How do you handle the ReportServer databases, are they in the AG or you install SSRS on one of the primary/secondary node?

    Thanks.