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

[MicrosoftSecurityandComplianceTeam]

First published on CloudBlogs on Oct, 16 2012

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!