Implementing Microsoft SQL Server 2016 Standard Basic Availability Groups for Use in Citrix XenApp and XenDesktop 7.9

On a recent project for my employer, Choice Solutions, I needed to setup high availability for the XenApp/XenDesktop databases. The customer wanted to use Microsoft SQL Server 2016 Standard and I was tasked with implementing the new Basic Availability Groups. This article details the steps I took to implement Microsoft SQL Server 2016 Standard Basic Availability Groups for Citrix XenApp and XenDesktop 7.9.

I would like to thank Choice Solutions for allowing me the time to lab this process, document it and now share it with the community.

Update 13-Sep-2016: The article from Dell that I reference is no longer available. I found a cached copy, saved the relevant information and put that information in place of the Dell support link.

The SQL database provides the foundation for all configuration information in the XenDesktop Site. Configuration, Logging and Monitoring information are stored in the databases. The databases contained within a SQL server infrastructure are crucial to the continuous operation of the XenDesktop architecture and if it fails, affects ranging from loss of the ability to administer the XenDesktop site to the inability to connect to XenDesktop virtual desktops. SQL Server can be made highly available through a number of technologies.

Citrix supports various levels of database high availability for XenApp/XenDesktop 7.x:

  • SQL Server database mirroring
  • Using the hypervisor’s high availability features
  • SQL Clustering
  • AlwaysOn Availability Groups

Those were the preferred order for versions 7.0, 7.1, 7.5 and 7.6. Starting with version 7.7, Citrix changed the order to:

  • AlwaysOn Availability Groups
  • SQL Server database mirroring
  • SQL Clustering
  • Using the hypervisor’s high availability features

Beginning with SQL Server 2016, Microsoft offers a new form of AlwaysOn Availability Groups (AAGs) named Basic Availability Groups (BAGs).

Overview of AlwaysOn Availability Groups

What are the differences between AAGs and BAGs?

  • BAGs are only supported on SQL Server 2016 Standard Edition
  • BAGs cannot be upgraded to AAGs
  • A BAG is limited to one database
  • You cannot add or remove a replica to an existing BAG
  • Replicas can only run SQL Server 2016 Standard Edition (no prior versions)
  • No backups on the secondary replica
  • No read access on the secondary replica
  • Limit of two replicas

There are, as expected, some Microsoft licensing guidelines you should pay attention to. Download the SQL Server 2016 License Guide for detailed licensing information.

The important financial part from page 20 of the license guide:

Failover Basics
For each server licensed with SQL Server 2016 and covered by active SA, customers can run up to the same number of passive failover instances in a separate, operating system environment to support failover events. A passive SQL Server instance is one that is not serving SQL Server data to clients or running active SQL Server workloads. The passive failover instances can run on a separate server. These may only be used to synchronize with the primary server and otherwise maintain the passive database instance in a warm standby state in order to minimize downtime due to hardware or software failure.
• The secondary server used for failover support does not need to be separately licensed for SQL Server as long as it is truly passive, and the primary SQL Server is covered with active SA.

What this means is that if you have Microsoft SA on your SQL Server 2016 Standard software, the second SQL Server used for the BAG’s replica does not need a separate license if it meets the criteria. I am no Microsoft (or Citrix) license expert so please check with Microsoft licensing if you have any questions.

Citrix has tested XenApp and XenDesktop 7.9 with SQL Server 2016 Standard Edition Basic Availability Groups and fully supports using the two together.

This article describes, in detail, the process I used to implement BAGs for use with a new XenDesktop 7.9 Site.

High Level Overview

  • Create a Windows Failover Cluster
  • Give the new cluster object the right to create computer objects in the container or OU the cluster object resides in
  • Give the new cluster object the right to create child objects in the DNS forward lookup zone
  • Install two independent Microsoft SQL Server 2016 Standard Edition servers using Latin_100_General_CI_AS_KS Collation
  • On the first SQL server, install the SQL Management Studio
  • Add the second SQL server to the SQL Management Studio
  • Create a share for the BAG database seeding
  • Enable AlwaysOn Availability Groups on the SQL Server service on both SQL servers
  • Create the three databases needed for XenDesktop 7.9
  • Do a full backup of the three databases
  • Create three BAGs and Listeners
  • Verify on the second SQL server that the BAGs have replicated
  • Now you are ready for the XenDesktop 7.9 Site creation

Create Windows Failover Cluster

If you plan to create your Windows Failover Cluster using multiple subnets or spanning different physical locations, there are some important considerations. Please read this Microsoft article before you start building such a cluster.

Building True Multi-Subnet Windows Server Failover Clusters

From the advice given by several SQL MVPs, I created an OU named SQL2016Cluster that contains the two SQL servers.

On both SQL servers, install the Failover Clustering Feature.

Figure 1

Figure 1

Add the Failover Clustering management tools.

Figure 2

Figure 2

Before creating the failover cluster, install all Windows Updates and restart the server.

After the server restarts, login on the first SQL server, go to Administrative Tools and select Failover Cluster Manager.

Figure 3

Figure 3

In the middle pane, under Management, select Validate Configuration…

Figure 4

Figure 4

Click Next.

Figure 5

Figure 5

Enter the FQDN of the two SQL servers by typing in the names or by clicking Browse and searching for the servers and click Next.

Figure 6

Figure 6

Select Run all tests and click Next.

Figure 7

Figure 7

Figure 8

Figure 8

Validation testing begins.

Figure 9

Figure 9

Click View Report… and leave Create the cluster now using the validated nodes… selected.

Figure 10

Figure 10

Analyze the report for warnings and errors. In a lab or small environment, some warnings can be safely ignored. In a production environment, you will need multiple paths to the network. It is also important that both SQL servers have the same Windows Updates.
Click Finish to create the cluster.

Figure 11

Figure 11

Click Next.

Figure 12

Figure 12

Enter a Cluster Name, an IP Address and click Next.

The Cluster Name is the name of a computer account created in Active Directory and a DNS “A” record is created with the IP address entered. The Cluster Name is used to refer to the cluster instead of using the name of the two SQL servers.

Figure 13

Figure 13

Unselect Add all eligible storage to the cluster and click Next.

Figure 14

Figure 14

The cluster creation process starts.

Figure 15

Figure 15

When the cluster creation is complete, click Finish.

Figure 16

Figure 16

The Failover Cluster Manager console shows the new cluster.

Figure 17

Figure 17

The console shows there is no Witness configured for the cluster.

Figure 18

Figure 18

Right-click the Cluster name, select More Actions and click Configure Cluster Quorum Settings…

Figure 19

Figure 19

Click Next.

Figure 20

Figure 20

Select Select the quorum witness and click Next.

Figure 21

Figure 21

A Basic Availability Group requires two independent Microsoft SQL Server 2016 Standard servers built on top of a Windows Failover Cluster. There is no need for shared storage for the cluster or the two SQL servers. A Windows Failover Cluster requires a Witness to determine if the two nodes of the cluster are online and communicating. For this cluster, a simple file share is used. In a production environment, this file share should be on a highly available server and highly available storage. For this lab, I am using my single domain controller.

Note: If this is a multiple subnet cluster or a cluster spanning different physical locations, make sure the quorum witness is on a third subnet or location.

Select Configure a file share witness and click Next.

Figure 22

Figure 22

Click Browse.

Figure 23

Figure 23

Enter the name of the server and click New Shared Folder…

Figure 24

Figure 24

Configure the share as required and click OK.

Dell had a very good article on configuring the file share and NTFS permissions but it has since been removed. Here is the relevant infomation from the original Dell article:

Quorum File Share Witness

Both NTFS permissions and File Share permissions need to be assigned to the Cluster Host Name Object Account. For permissions, the Cluster Host Name Object is an Active Directory Computer account. Assign both NTFS and File Share identical permissions.

Click on the share permissions and clear out the previous inherited entries and add the following permissions:

Cluster Name Object (CNO) Account – Full Control

SYSTEM – Full Control

SQL Database Engine Service Account – Full Control

Local Server Administrators Group – Full Control

Dell: Quorum File Share Witness Permissions

 

Figure 25

Figure 25

Click Yes.

Figure 26

Figure 26

Click OK.

Figure 27

Figure 27

Click Next.

Figure 28

Figure 28

Click Next.

Figure 29

Figure 29

Click Finish.

Figure 30

Figure 30

Next, I went to the server with the share to verify Share and NTFS permissions according to the Dell document.

Share permissions:

Figure 31

Figure 31

NTFS permissions:

Figure 32

Figure 32

The Failover Cluster Manager now shows the Witness configured.

Figure 33

Figure 33

Configure Cluster Object

Before continuing, the cluster computer object requires the rights to create computer object and DNS child objects.
Because I moved my two SQL server computer accounts to a specific OU, the cluster computer account is in the same OU.

Figure 34

Figure 34

The cluster computer account needs rights to create computer accounts in the Container or OU it resides in. The process of creating the Basic Availability Groups creates computer accounts for the Listeners.
In Active Directory Users and Computers, click View and then Advanced Features.

Figure 35

Figure 35

Right-click the Container or OU and click Properties.

Figure 36

Figure 36

Click the Security tab.

Figure 37

Figure 37

Click Add…

Figure 38

Figure 38

Click Object Types…

Figure 39

Figure 39

Select Computers and click OK.

Figure 40

Figure 40

Enter the name of the cluster computer account, click Check Name and click OK.

Figure 41

Figure 41

Click Advanced.

Figure 42

Figure 42

Select the cluster computer account and click Edit.

Figure 43

Figure 43

Scroll down, select Create Computer objects and click OK.

Figure 44

Figure 44

Click OK.

Figure 45

Figure 45

Click OK.

Figure 46

Figure 46

Exit Active Directory Users and Computers, open the DNS Manager and expand to the domain’s Forward Lookup Zone. The SQL cluster computer account needs rights to create DNS records. The process of creating the Basic Availability Groups creates DNS “A” records for the Listeners.

Figure 47

Figure 47

Right-click the domain’s zone and click Properties.

Figure 48

Figure 48

Click the Security tab.

Figure 49

Figure 49

Click Add…

Figure 50

Figure 50

Click Object Types…

Figure 51

Figure 51

Select Computers and click OK.

Figure 52

Figure 52

Enter the name of the SQL cluster computer account, click Check Names and click OK.

Figure 53

Figure 53

Select the SQL cluster computer account, select Allow for Create all child objects and click OK.

Figure 54

Figure 54

Exit the DNS Manager.

Install Microsoft SQL Server 2016 Standard

I created two SQL related user accounts in AD. A service account, svc_sql, that is used for the SQL services and sqldba, that is a full SQL sysadmin and local admin. I created a GPO Preference to place the sqldba account in the local administrators group.

On both SQL server VMs, mount the ISO for SQL Server 2016 Standard.
When the SQL Server Installation Center appears, click Installation.

Figure 55

Figure 55

Click New SQL Server stand-alone installation or add features to an existing installation.

Figure 56

Figure 56

Click Next.

Note: This step could be different for you as I am using MSDN media for my lab. I believe the commercial version of the SQL Server installation may have you select the licensing method.

Figure 57

Figure 57

Select I accept the license terms and click Next.

Figure 58

Figure 58

Select Use Microsoft Update to check for updates and click Next.

Figure 59

Figure 59

Resolve any Warning and Failures and click Next.

Figure 60

Figure 60

Select Database Engine Service and SQL Server Replication and click Next.
Do not (and you cannot) put any of the root directories on the cluster shared drive.

Figure 61

Figure 61

Click Next.

Figure 62

Figure 62

Enter the SQL service account name and password and click the Collation tab.

Figure 63

Figure 63

Click Customize…

Figure 64

Figure 64

Select Windows collation designator and sort order, Latin1_General_100 for Collation designator, Accent-sensitive, Kana-sensitive and click OK.

Figure 65

Figure 65

The Citrix recommended SQL collation of Latin1_General_100_CI_AS_KS is created by the previous options. Click Next.
https://docs.citrix.com/en-us/xenapp-and-xendesktop/7-9/technical-overview/databases.html
“Use a collation that ends with “_CI_AS_KS”. Citrix recommends using a collation that ends with “_100_CI_AS_KS”.”

Figure 66

Figure 66

Select Windows authentication mode and click Add Current User.

Figure 67

Figure 67

Click Add…, add the sqldba account and click Next.

Figure 68

Figure 68

Click Install.

Figure 69

Figure 69

The installation of SQL Server 2016 Standard begins.

Figure 70

Figure 70

After SQL Server installation completes, click Close.

Figure 71

Figure 71

On the second SQL Server, exit the SQL Server Installation Center and eject the ISO.
On the first SQL Server, select Install SQL Server Management Tools.

Figure 72

Figure 72

Download and install the SQL Server Management Studio.

Figure 73

Figure 73

Exit your Internet browser and the SQL Server Installation Center on the first SQL Server. Eject the SQL Server ISO.

On both SQL servers, start SQL Server 2016 Configuration Manager.

Figure 74

Figure 74

Click SQL Server Services, then right-click SQL Server (instance name) and click Properties.

figure075

Click the AlwaysOn High Availability tab.

Figure 76

Figure 76

Select Enable AlwaysOn Availability Group and click OK.

Figure 77

Figure 77

Right-click SQL Server (instance name) and click Restart.

Figure 78

Figure 78

After the service restarts, exit the SQL Server 2016 Configuration Manager.

Install all Windows Updates on both SQL Servers.

Create Databases for XenDesktop

XenApp/XenDesktop 7.9 uses three databases: Site, Logging and Monitoring. Each is created and configured the same way. The only difference is the name.

Creating the Basic Availability Groups requires a network file share for the initial data synchronization. I created a folder and share name SQL2016Share with default Share and NTFS permissions.

On the first SQL Server, launch SQL Server Management Studio.

Figure 79

Figure 79

Click Connect.

Figure 80

Figure 80

Right-click Databases and click New Database…

Figure 81

Figure 81

Enter a Database name

Figure 82

Figure 82

Click Options, set Recovery model to Full, scroll down in Other options and set Is Read Committed Snapshot On to True and click OK.

https://docs.citrix.com/en-us/xenapp-and-xendesktop/7-9/technical-overview/databases.html

“For optimum performance, enable the SQL Server Read-Committed Snapshot. For details, see CTX 137161.”

Figure 83

Figure 83

Expand Databases, right-click the new database, click Tasks, and click Backup…

Before the Basic Availability Group is created, the database must have a full backup completed.

Figure 84

Figure 84

Make sure Backup type is set to Full and click OK.

Figure 85

Figure 85

Click OK.

Figure 86

Figure 86

Repeat these steps to create and backup the Logging and Monitoring databases.

Now that all three databases are created and backed up, the Basic Availability Groups can be created.

Right-click AlwaysOn High Availability and click New Availability Group Wizard…

Figure 87

Figure 87

Click Next.

Figure 88

Figure 88

Enter an Availability group name and click Next.

Figure 89

Figure 89

Select the one database to include in the availability group and click Next.

Figure 90

Figure 90

Click Add Replica…

Figure 91

Figure 91

Enter the name of the second SQL server and click Connect.

Figure 92

Figure 92

The second SQL server is added as a replica. Click the Listener tab.

Figure 93

Figure 93

Expand the size of the wizard, select Create an availability group listener, enter a Listener DNS Name, a Port and click Add…

Note: The Listener DNS Name is limited to 15 characters. The Listener name is needed when the Site is created in Citrix Studio.

Figure 94

Figure 94

Enter a unique IP address for this Listener and click OK.

Note: If this is a multiple subnet cluster, enter an IP address for both subnets.

Figure 95

Figure 95

Click Next.

Figure 96

Figure 96

Select Full, enter a file share the replicas can use for the initial data synchronization and click Next.

Note: If this is a multiple subnet cluster or a cluster spanning different physical locations, make sure the file share is on a third subnet or location.

Figure 97

Figure 97

If there are any errors reported, resolve the errors and click Next.

Figure 98

Figure 98

Click Finish.

Figure 99

Figure 99

The Basic Availability Group is created.

Figure 100

Figure 100

Click Close.

Figure 101

Figure 101

Repeat the steps to create Basic Availability Groups for the other two databases.

All three databases and Basic Availability Groups show on the first SQL server.

Figure 102

Figure 102

Adding the second SQL server to Management Studio shows the databases and Basic Availability Groups in the Synchronizing and Secondary states.

Figure 104

Figure 104

Create XenDesktop 7.9 Site

After installing XenDesktop 7.9, select Launch Studio and click Finish.

Figure 104

Figure 104

Click Deliver application and desktops to your users.

Figure 105

Figure 105

Enter a Site name and click Next.

Figure 106

Figure 106

Enter the database names created earlier. For the location, enter the name of each Listener that matches the database. If there are additional Controllers for this Site, click Select, enter the additional Controller FQDNs and click Next.

Figure 107

Figure 107

Figure 108

Figure 108

Figure 109

Figure 109

Figure 110

Figure 110

If the logon account does not have rights to create databases on the SQL server, you are prompted for credentials for each of the three Listeners.

Figure 111

Figure 111

Figure 112

Figure 112

Figure 113

Figure 113

Continue with the rest of the Configuration Wizard steps.

On the Summary step, verify the information and if correct, click Finish.

Figure 114

Figure 114

Once the Site creation process is complete, select the Configuration node in Studio.

Figure 115

Figure 115

You can see that the three databases with the Listener names.

In my opinion, it is far easier to setup the databases, BAGs and Listeners before configuring the Site than to try to reconfigure the Site to use BAGs and Listeners later.

Thanks

Webster

About Carl Webster

Webster is a Sr. Solutions Architect for Choice Solutions, LLC and specializes in Citrix, Active Directory and Technical Documentation. Webster has been working with Citrix products for many years starting with Multi-User OS/2 in 1990.

View all posts by Carl Webster

18 Responses to “Implementing Microsoft SQL Server 2016 Standard Basic Availability Groups for Use in Citrix XenApp and XenDesktop 7.9”

  1. Pavan Says:

    Hello Carl,
    Thank you for the article. Really helpful. We can pretty much follow the same procedure for setting up Always ON Availability groups as well right?
    Thanks&Regards,
    Pavan

    Reply

  2. Engin Yoruker Says:

    Hi,
    Wondering have you tried this also for PVS database? Is that also supported?

    Reply

  3. Sebastian Says:

    got the problem solved, pre created the cno first and gave the cluster the permission.

    now it is working, but i have a strange issue.

    on the second sql server, the database was not restored, and in the group the second server has a red icon.

    could this be a problem of the german sql server? i tried this 2 times, with reinstalling both test server again, same error.

    Reply

  4. Sebastian Says:

    very nice article, get the following error on creating the cluster:

    error 5894 WSFC API

    i have installed a fresh demo setup, 2 sql server 2016 standard on windows server 2012 r2, like you describe in your article.

    any ideas?

    Reply

    • Carl Webster Says:

      ERROR_CLUSTER_NETWORK_NOT_FOUND_FOR_IP; 5894

      Check you IP address stuff.

      Webster

      Reply

    • Sebastian Says:

      strange issue, the database on the second sql server is not created, no error while creating the group.

      could this be a language problem?

      Reply

  5. Chad Says:

    Carl,
    Good detailed article. One clarification however, since you are using BAGs on Standard Edition I believe Microsoft only supports one availability database. It appears that it will allow you to create multiple BAGs and one database per BAG, but doesn’t this infringe upon the license agreement?

    Thanks,
    Chad

    Reply

  6. Benjamin Hoch Says:

    Hello Carl,

    This configuration has a problem if the group with the listener fails over, but remain the other groups on the server. Then you will not get access to the other databases on the listener.

    Benjamin

    Reply

    • Carl Webster Says:

      A Basic Availability Group and its Listener can only have ONE database. So there are no other databases on the Listener to no get access to.

      Webster

      Reply

Leave a Reply