• 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 103
    Figure 103

    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

    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