RDS 2012 SQL AlwaysOn Considerations

The use of SQL Server 2012 Availability Groups in conjunction with RDS 2012

I  have had a few questions on RDCB HA recently so I have provided some useful information on deployments and best practices when using SQL 2012 AlwaysOn Failover Cluster Instances and AlwaysOn Availability Groups.

The RD Connection Broker role is what controls the RDS Deployment which provides connections and reconnections to active sessions to single or multiple RD Session Host Collections or Virtual Machine Pools and Personal Virtual Machine Pools.

Information about the Active/Active RD Connection broker:

Once RDCB HA is configured, you will see that under the Connection broker Options (tasks) that you can Set the Active RD Connection Broker Server.  Both RD Connection Brokers are active, this option is used to change the connection broker that is communicating with the management server (RDMS). What this means is, there can only be one active server managing the management server at one time. To summarise: (active) means Both Connection Brokers are managing the user changes but only one RDCB communicates with the RDMS at one time.

Microsoft supports the following SQL Server 2012 High availability setups in RDS 2012.

• AlwaysOn Failover Cluster Instances
• AlwaysOn Availability Groups
• Database mirroring – future releases of SQL will make this feature deprecated.

I would always recommend using AlwaysOn Availably as there is no requirement for shared storage.

AlwaysOn is supported across subnets, but does require two availability group listeners.

for information on the setup of RDCB HA have a look at the following link: http://wp.me/p2DF8F-mZ

Preparing for SQL Alwayson Availability groups

Pre-Requisites:

• The SQL Server native client must be on each RD Connection Broker. (ensure you have the correct version number 11 for SQL12 and 10 for SQL2008)
• Create the DNS Round Robin Records for each RD Connection Broker Server using the RDCB IP address’s. I recommend the use of a third party load Balancer like (KEMP Technologies) as you would have to wait for DNS if a server fails or looses connection. In addition to this, Layer 7 load balancers provide health checking of the load balanced services.
• Create a Folder on both SQL Server’s root drive c:\rdcb\ (this is where the RDCB database is going to held)
• If you are using RD Gateway, you will need to add the DNS RR to the RD RAP Policy.
• Configure the Firewall on both Servers to allow access to SQL
• Create a SQL Service account for use when installing SQL
• SQL Availability Groups require the SQL 2012 Enterprise edition.

Before installing SQL:

Ensure you have the following installed:-

• .Net Framework 3.5.1
• Windows Server Failover Clustering (WSFC)

Installing SQL:

• Select “New Standalone Installation”
• Ensure that you configure the SQL Services with the designated service accounts
• If you decide to use named SQL instances ensure that you remove all dynamic ports from the configuration manager.

Configuring SQL:

• You will need to assign the first RD Connection broker with the dbcreate permission
• Add the “NT AUTHORITY\NETWORK SERVICE” account to both RD Connection broker “RDS Management Servers Local Group”
• Add both Connection Broker Computer Accounts to “RDS Management Servers Local Group” on both servers
• Add the “BUILTIN\RDS Management Servers” group to both SQL Servers and give the group (dbcreator and public roles)
• You will not be able to add the DB Owner permission at this point as we haven’t created the Database as of yet.

RD Connection Broker HA Configuration:

When you change the connection broker to HA, you are telling the first connection broker to create a new database on SQL, and then move all the data from the windows internal database to the newly created SQL Server.

As per my Previous Guide on RDCB High availability (http://wp.me/p2DF8F-mZ) Open up RDMS and navigate to > Server Manager > Remote Desktop Services > Overview.

Then right click on the RD Connection Broker for the High Availability Wizard.

Configure High Availability:

• Before going forward, ensure the native client has been configured on the connection brokers to access the first SQL server.
• Enter the Database Connection string as per my previous post
• Enter the location of the SQL Database (c:\RDCB\)
• Enter the DNS Round robin FQDN \ third party Load balancing Virtual Service

The database will now have been created if the HA configuration was successful, you will now need to give both Connection brokers DB_owner permissions on the database so they can write data to it.

• Open SQL Server Management Studio on the server which has had the database created.
• Expand the “database” > Security > Users > BUILTIN\RDS Management Servers and choose properties.
• Under Membership, ensure that “db_owner” is selected.

Adding the second RD Connection Broker

Open up RDMS and navigate to > Server Manager > Remote Desktop Services > Overview, Then right click on the RD Connection Broker and select add RD Connection Broker

before doing this ensure that you have the second RD Connection Broker in the server pool.

Once you have added the second RDCB server, ensure you install the required SQL Certificate. You will need to use the RDCB HA FQDN

Updating the RD Connection Broker Availability DNS Round Robin name after configuration:

Set-RDClientAccessName [[-ConnectionBroker] <String>] [-ClientAccessName] <String> [<CommonParameters>] 

For example:

Set-RDClientAccessName -ConnectionBroker "RDCB1.company.local" -ClientAccessName " RDHA.Company.com"

Create a Failover Cluster:

• Ensure that you have the Failover cluster feature installed on both SQL Servers
• Open up WSFC Fail over Cluster and run the “Validate a Configuration”
• Once the validation finishes go through the report and fix any errors
• Once your happy click “Create Cluster now…”
• The cluster will require a IP address and a AD Name.
• If the cluster creation fails, you will need to setup delegate the “Create computer accounts” in Active directory.

Pre requisites for the Availability Groups:

• Ensure that TCP/IP is enabled under SQL Network Configuration in the SQL Server Configuration Manager.
• You will need to enable alwaysOn Availability Groups, this can be done in the SQL Server Configuration Manager > under the SQL Server service > right click and Properties

• Restart the SQL Services
• Create a SQL Service account
• Create a folder on a shared network for the initial backup and sync, the SQL Servers will need full control share permissions.
• Take a full backup of the RDCB database ***Important****

Creating a Availability Group:

• On the Connection broker that holds the RDCB database, navigate to the section (AlwaysOn High Availability)

• Right click on Availably Groups and click on “New Availability Group Wizard…”

• Skip the Introduction and specify a Name for the availability group
• Check the RDCB Database and click next
• Add the replica server and click connect.
• Set the Availability group as Automatic Failover and Synchronous Commit.
• Create the Availability group listener (DNS Name, Port and IP address)
• Ensure that the Create Computer Objects permission is set on the RDS Server OU, otherwise the listener will fail.

Configuring RDS to use the created Availability Group:

You now need to change the SQL Native client Server names to the Availability group listener.

You will then need to change Database connection string, This can be done using Powershell:

Set-RDDatabaseConnectionString -ConnectionBroker <active RD Connection Broker> -DatabaseConnectionString "DRIVER=SQL Server Native Client 11.0;SERVER=<Availability Group Listener Name>;Trusted_Connection=YES;APP=Remote Desktop Services Connection Broker;DATABASE=<RD Connection Broker database Name>"

Once you have run the command shown above, check the Deployment properties under High Availability Settings for the availability group Listener Name.

Testing the solution:

Testing can be achieved by disconnecting the Network interface on the primary connection broker or shutting down one of the servers. Look at the WSFC and you should see that a cluster event states “Cluster Node “***” was removed from the active cluster failover cluster membership.