Configure Remote Desktop Connection Broker in Windows Server 2012 with SQL Server 2012 High Availability

Configure Remote Desktop Connection Broker in Windows Server 2012 with SQL Server 2012 High Availability

Rate This
  • Comments 6

Hello, this is Jingyuan, Munindra and Sriram from the Remote Desktop Virtualization team. In our previous blog post, we described how to configure RD Connection Broker high availability in Windows Server 2012. One requirement for RD Connection Broker high availability is the use of SQL Server. In this blog post, we are going to take a step further, and show you how to leverage SQL Server 2012 high availability solutions to make data highly available for RD Connection Broker servers. The SQL Server high availability solutions we are going to cover in this blog post are: AlwaysOn Failover Cluster Instances, AlwaysOn Availability Groups and Database Mirroring.

Before diving into data high availability, let’s first briefly touch on data protection. According to SQL Server 2012 recommended solutions for using SQL Server to protect data:

  • For data protection through a third-party shared disk solution (a SAN), we recommend that you use AlwaysOn Failover Cluster Instances.
  • For data protection through SQL Server, we recommend that you use AlwaysOn Availability Groups.

Database Mirroring falls into the category of “data protection through SQL Server”. It is a high availability solution that exists in both SQL Server 2008 R2 and SQL Server 2012. According to SQL Server team, database mirroring will be removed in a future version of Microsoft SQL Server. If you are deploying SQL Server 2012, it is recommended that you use AlwaysOn Availability Groups instead. However, if you have existing investments in database mirroring, you can continue to use it for high availability with SQL Server 2012.

To determine the minimum SQL Server Edition required in order to use the SQL Server high availability solution of your choice, please refer to Features Supported by the Editions of SQL Server 2012.

Now that we’ve described the different ways you can achieve high availability with SQL Server 2012 and how to choose among the options, we’ll provide a set of configuration steps for each.

Configure RD Connection Broker in Windows Server 2012 with SQL Server 2012 AlwaysOn Failover Cluster Instances:

  1. Create a SQL Server 2012 Failover Cluster with at least 2 Failover Cluster Instances
  2. Configure RD Connection Broker high availability with Windows Server 2012 using the following database connection string:
    DRIVER=SQL Server Native Client 11.0;SERVER=<my_sql_server_cluster>; Trusted_Connection=Yes;APP=Remote Desktop Services Connection Broker;DATABASE=<my_database>;

Configure RD Connection Broker in Windows Server 2012 with SQL Server AlwaysOn Availability Groups:

  1. Create a Windows Server 2012 Failover Cluster, install 2 instances of SQL Server on different Windows Server 2012 Failover Cluster nodes: Instance1 (Primary), Instance2 (Secondary)
  2. Configure RD Connection Broker high availability in Windows Server 2012 using SQL Server Instance1
  3. Configure SQL Server 2012 AlwaysOn Availability Groups using SQL Server Instance1 (Primary) and Instance2 (Secondary), put SQL Server in synchronous-commit mode and enable automatic failover
  4. Use the Windows PowerShell command Set-RDConnectionBrokerHighAvailability method to update RD Connection Broker database connection string to the following:
    DRIVER=SQL Server Native Client 11.0;SERVER= <my_sql_server_availability_group_listener>;MultiSubnetFailover=True;Trusted_Connection=Yes;APP=Remote Desktop Services Connection Broker;DATABASE=<my_database>;

Configure RD Connection Broker in Windows Server 2012 with SQL Server SQL Server 2012 Database Mirroring:

  1. Install 3 instances of SQL Server 2012: Instance1 (Principal), Instance2 (Mirror) and Instance3 (Witness)
  2. Configure RD Connection Broker high availability in Windows Server 2012 using SQL Server Instance1
  3. Configure SQL Server 2012 Database Mirroring using SQL Server Instance1 (Principal), Instance2 (Mirror) and Instance3 (Witness), put database mirroring in high-safety mode with Witness, and enable automatic failover
  4. Use the Windows PowerShell command Set-RDConnectionBrokerHighAvailability method to update database connection string to the following:
    DRIVER=SQL Server Native Client 11.0;SERVER=<my_sql_server_instance1>;Failover_Partner=<my_sql_server_instance2>;Trusted_Connection=Yes;APP=Remote Desktop Services Connection Broker;DATABASE=<my_database>;

To summarize, we introduced 3 high availability solutions in SQL Server 2012 that can be used to protect data for RD Connection Broker server in Windows Server 2012. Whether you plan to protect your data through a thirty-party shared disk solution or with SQL Server itself, if data high availability is important to you, we encourage you to implement one of the SQL Server 2012 high availability solutions for RD Connection Broker in your production environment!

Leave a Comment
  • Please add 5 and 4 and type the answer here:
  • Post
  • To change database connection string you must use Set-RDDatabaseConnectionString commandlet

  • Hi Jingyuan, Munindra and Sriram,

    We are interested in using Remote Desktop Connection Broker as a way of increasing high availability to a group of RD Session Host servers without having to reconfigure the client side DNS name (by pointing all clients at the same load-balanced Connection Broker DNS name). However, Connection Broker will select the destination session host randomly (with a numeric weight factor) and not all our Session Host servers have the same applications installed. What would be ideal for us is to be able to direct users to a particular set of hosts based on the users domain username (e.g. uses in the BusinessAppA security group are directed only to TS hosts that have BusinessAppA installed).

    Is this possible?

    Alternatively, we could set up separate farms for each type of RD session host configuration we have. This later approach is harder for us to implement as users also roam between rooms, so we need to direct users based on their login identity rather than the identity of the connection client workstation (which would be configured with a specific farm DNS name).

    Thanks,

    Tim Miller Dyck

  • For the use of SQL 2012 AlwaysOn Availability groups, not only do you need to use the Set-RDDatabaseConnectionString commandlet as asukhov points out, but my experience showed that (MultiSubnetFailover=Yes is only supported, not MultiSubnetFailover=True.   Above it is shown MultiSubnetFailover=True which consistently causes an error "The database specified in the database connection string is not available from the RD Connection Broker Server"

  • Can we setup failover cluster in which nodes are split in multiiple datacenters with different IP ranges?

  • I followed your guide to configure HA with SQL Server 2012 Always On Availability Group (SQL Server is on Windows 2008 R2 Server). Steps 1-3 works fine, Database is moved to SQL Server, and Availability Group has been created and configured. Now last steps Fails when try to reconfigure the RDS Server to use Availability group listener instead of SQL Server with instance:

    Set-RDConnectionBrokerHighAvailability -DatabaseConnectionString "DRIVER=SQL Server Native Client 11.0;SERVER=AlwOnListener;MultiSubnetFailover=True;Trusted_Connection=Yes;APP=Remote Desktop Services Connection Broker;DATABASE=RDSCB" -DatabaseFilePath "C:\RDSCB\RDSCB.mdf" -ClientAccessName "Farm.domain.com"

    Error I get is:

    Set-RDConnectionBrokerHighAvailability : The RD Connection Broker server is configured for high availability.

    At line:1 char:1

    + Set-RDConnectionBrokerHighAvailability -DatabaseConnectionString "DRIVER=SQL Ser ...

    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

       + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException

       + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Set-RDConnectionBrokerHighAvailability

    Does anyone has an idea what is wrong?

  • Thanks asukhov and Todd.  I was getting Roger's error and this is solved by running the following (this should replace point 4 of Configure RD Connection Broker in Windows Server 2012 with SQL Server AlwaysOn Availability Groups):

    Set-RDDatabaseConnectionString -DatabaseConnectionString "DRIVER=SQL Server Native Client 11.0;S

    ERVER=<my_sql_server_availability_group_listener>;MultiSubnetFailover=Yes;Trusted_Connection=Yes;APP=Remote Desktop Services Connection Broker;DATABASE=<db_name>" -ConnectionBroker <RDSBroker_FQDN>

Page 1 of 1 (6 items)