VMGuru

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.

SQL AlwaysOn

The [VMware vRealize Automation Reference Architecture for version 6 and higher](http://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.) states:

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.