MS SQL high availability support for vRealize Automation
With a vRealize Automation Distributed setup, we are able to create a highly available vRealize Automation environment with redundant vRA appliances, IaaS Servers and Orchestrators behind load balancers. But what about the MS SQL high availability for the Iaas Server(s)? Lately I had some discussion on the Microsoft SQL Server high availability configuration needed to support vRealize Automation IaaS, 6.2.2 in this case.
First of all, when you’re not sure if your Microsoft SQL Server setup is supported by vRealize Automation check the latest vRealize Automation 6.2 Support Matrix (page 3).
The Host Databases supported today (Sept 20, 2015) are:
- MS SQL 2008 R2 Service Pack 3;
- MS SQL 2012;
- MS SQL 2012 Service Pack 1;
- MS SQL 2014.
But even when your Microsoft SQL Server setup shows up as supported, there are many variables left. What about SQL clustering and AlwaysOn Availability Groups?
SQL Failover Clustering
We can be brief about this. VMware supports a SQL Server Failover Cluster Instance as the basis of a vRealize Automation IaaS configuration when using the Microsoft SQL Server version mentioned above.
You should use a SQL Server Failover Cluster Instance. vRealize Automation does not support AlwaysOn Avalability Groups due to use of Microsoft Distributed Transactions Coordinator.
Actually this statement is a bit off, vRealize Automation uses the Microsoft Distributed Transaction Coordinator Service (MSDTC) to communicate with its SQL database. Microsoft however, does not support the use of the MSDTC with Microsoft SQL Server AlwaysOn Availability Groups.
The Microsoft MSDN site states:
Cross-database transactions and distributed transactions are not supported for database mirroring and for some configurations of AlwaysOn Availability Groups. There is no support for cross-database transactions for AlwaysOn Availability Groups on SQL Server 2014 or earlier.
A note on the same page however states:
Using Database Mirroring or Availability Groups together with DTC does not result in an unsupported SQL Server installation. If, however, a database is part of a Database Mirroring session or an Availability Group and DTC is also used in the database, support issues will be investigated by Microsoft only if unrelated to the combined use of Database Mirroring or Availability Groups with DTC.
Huh? There is no support for cross-database transactions but this does not result in an unsupported SQL Server installation? Must be Microsoft logic. Bottom line, VMware does not support AlwaysOn Avalability Groups on SQL Server 2014 or earlier.
However, Microsoft is planning to support this configuration in the future. On SQL Server 2016 Community Technology Preview 2 (CTP2), cross-database transactions are supported for AlwaysOn Availability Groups running on Windows Server 2016 Technical Preview 2.
Other articles in the series vRealize Automation:
- Infoblox & vRealize Automation, Setup IPAM endpoint
- Infoblox & vRealize Automation, Install Infoblox IPAM plugin
- Setup provisioning of vCloud Air workloads
- Setup a vCloud Air Endpoint in vRealize Automation
- Setup Postgres database high availability for vRealize Automation
- Setup vRealize Automation Cost profiles & currency
- Speed up vRealize Automation life cycle updates
- Configure vRealize Automation not to delete virtual machines
- Track infrastructure changes with vRealize Automation Data Collection
- MS SQL high availability support for vRealize Automation
- Setup the vRealize Automation prerequisites for IaaS
- vRealize Automation: How to …
- Downgrade the vRealize Automation license
- vRealize Automation 7 – New Architecture & Installation
- vRealize Automation 7 – Improved Authentication
- vRealize Automation 7 – Converged Blueprints
- vRealize Automation 7 – LifeCycle Extensibility
- How to transfer vRealize Automation payload to Orchestrator
- Automation is not difficult with vRealize Automation 7
- vRA custom host name using Orchestrator and Event Broker
- How to deliver Database-as-a-Service with vRealize Automation
- Prepare Windows for vRA software deployment
- Missing endpoints after upgrading to vRealize Automation 7.3
- How to run Containers as a Service – Part 1
- How to run Containers as a Service – Part 2
- Infoblox & vRealize Automation, IP Address Management (IPAM) made easy
- How to run Containers as a Service – Secure Docker communications
- Create a new database user using vRealize Automation XaaS
- Infoblox & vRealize Automation, Infoblox NIOS setup
- Infoblox & vRealize Automation, vRA setup
- vRealize Automation failed requests monitoring
- vRealize Automation 7.2 released
- How to specify a deployment target when using vRealize Automation
- vRealize Suite just became a lot easier with Lifecycle Manager
- LAMP Stacks made easy with VMware and Puppet
- How to: Deploy vRealize Automation 8
- Create Custom Names with vRealize Automation 8
- Kubernetes as a Service