HobbitCloud has been enjoying somewhat of an infrastructure refresh recently, with the entire estate being renamed and Windows Servers migrating to 2016 and 2019. Part of this project was to increase resilience in my on-premise data warehouse by implementing a Microsoft SQL Server 2016 AlwaysOn Availability Group (AAG) running on Windows Server Core.
On of the key services using SQL is vRealize Automation. With the release of version 7.5, I have decided to rebuild my test and production environments, while keeping my existing development one until all content is checked-in to source control. However as I’m decommissioning my old SQL server, the IaaS database needed to be moved.
Thankfully VMware has done an excellent job of documenting the procedure for this with KB 2074607.
When I created my AAG I selected “Per database DTC support” (with SQL 2016 SP2 you can enable this after the event), and after migration, everything seemed to go well. Unfortunately this was not the case, and the usual IaaS problems started to occur, like data collections being stuck in progress and this familiar error appearing in the log:
After consulting the font of knowledge that is Jon Schulman, he reminded me of one of life’s facts:
At this point it became obvious what I’d forgotten – to configure DTC on each SQL node in the cluster. So to do this in Powershell (these are Server Core boxes after all):
Set-DtcNetworkSetting -InboundTransactionsEnabled $True -OutboundTransactionsEnabled $True -RemoteClientAccessEnabled $True -RemoteAdministrationAccessEnabled -Confirm:$false Stop-Dtc Start-Dtc
Once I had configured this then IaaS started behaving normally and my migration was marked as a success!