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.
Ryan A. Anderson
Follow the conversation: @pfedynamics | http://www.pfedynamics.com
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?
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.
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?
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.
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?
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.
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..
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.
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
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?
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?
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!
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 :)
How do you handle the ReportServer databases, are they in the AG or you install SSRS on one of the primary/secondary node?
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.