Citrix Messes With Sql Always On
XenDesktop 7.9 FMA has issues with SQL Always On….
Databases has been a source of controversy since Citrix released XenDesktop. With the merger of XenApp and XenDesktop the main solution for database availability is SQL Always On. With SQL Always On you have the benefit of a cluster for OS and SQL protection while still having the benefits of the standalone SQL Server. I have deployed XD 7.x countless times using these technologies for many customers and have never had an issue with SQL Always On and Citrix technologies until 7.9
Using SQL Always On, I have been able to fail my SQL server, configure and manage my XD environment without issues. I have recently discovered with 7.9 you are unable to extend the environment while utilizing SQL Always On. The symptoms are simple:
- Add a new Delivery Controller to an existing XD/XA 7.9 deployment utilizing SQL Always on
- Receive an innocuous error, stating unable to connect to the SQL server
- Datastore is now corrupt
The error received, with unable to connect to the SQL server, shows an error of unable to connect to a SQL Server….. when you read the error, it is trying to connect to a SQL server directly in your Always On cluster. The error details state it is unable to update the security in the database. This is to be expected since the individual node it is trying to connect to is a secondary node in the Always On cluster. Weird…..
Run the connect to a site wizard again, and it will give an error stating that the database cannot be updated again, this time showing the correct Always On name.
What has happened is the Datastore is now corrupt. The tables housing the information regarding your Delivery Controllers is the only part effected. The following screen shot is shows the Controller node of Citrix Studio:
Once this has occurred, all aspects of XD/XA continue to work, however you will be unable to get information regarding your delivery controllers. To resolve this issue, you will need to clear out the database regarding any information of the new controller that was added.
Citrix has this handy article (https://support.citrix.com/article/CTX139505/) to remove Delivery Controllers manually. The simple explanation is:
- Open powershell and run Get-BrokerController
- Make note of the SID of the offending Delivery Controller
- Run the script provided in the article on a delivery controller.
- Populate the $DBName with your Site Database name
- Populate the $EvictedSID with the offending Deliver Controller SID
- This script will create a SQL script the will need to be run against the Datastore
The way to avoid all this hassle is to simply remove your XD/XA DB’s from the SQL Always On group. Leave the DB’s on the primary server and extend your delivery controllers. After you have extended your site, put the DB’s back in the Always On Availability Group
I have submitted detailed information and logs to Citrix Technical Support and am working with them toward a permanent resolution- Stay Tuned!