Window server 2012 | Remote Desktop Connection Broker high available with SQL Server Mirror

 

In my previous post I have described how to create high available connection broker set up. But that setup depend on one SQL server, so SQL server became single point of failure. To address this I have checked HA for SQL server and found SQL server mirror is supported with RD broker HA. I have implemented this in test and production environment and its works really fine.

0

Deployment prerequisites

  1. Install the same SQL server version in the new server, use the same accounts as in mirror server.
  2. Witness server – this is for automatic fail over purpose. It can be another SQL server or Install SQL Express version on a server. No need of a dedicated server this can be a server that uses for other purpose.
  3. Added firewall exceptions to all SQL servers.

Step 01 – Configure SQL mirror

  1. Please refer the following technet articles when crating the mirror
    http://technet.microsoft.com/en-us/library/ms189053.aspx
    http://technet.microsoft.com/en-us/library/ms189047(v=sql.105).aspx

     

  2. Login to principle SQL server and connect to mirror database and witness database server from that management consol. Perform this step to identify the connectivity to all principle, witness and mirror database servers.
  3. Change the principle server’s RD database to full recovery mode.
  4. Please refer the technet article, I’m just only giving the steps that you have to perform.
    http://technet.microsoft.com/en-us/library/ms189047(v=sql.105).aspx

     

  5. Backup the principle database and restore it in mirror server using NO RECOVERY mode
  6. Backup the transactions and restore it in mirror server using NO RECOVERY mode
  7. Note – all SQL servers SQL service should run as a domain user. And check in mirror server side, Database should have sysadmin, owner, public right to the security group of RD brokers.
  8. Expand Databases, and select the database to be mirrored. Right-click the database, select Tasks, and then click Mirror. This opens the Mirroring Page of the Database Properties dialog box.
  9. Click Configure Security.
    http://technet.microsoft.com/en-us/library/ms175919.aspx

If the mirror successful, check the failover

Step 02 – Configure RD brokers to support SQL mirror.

When configuring HA broker you have to provide the Database connection string, this connection string is configured to point to the RD database and its SQL server. Please refer the previous blog post on this. But to support SQL mirror this connection string have to be changed. And it cannot edit in GUI mode, only PowerShell support this.

Use following PowerShell commands to edit Connection string

  1. Login to a Broker server and open PowerShell in run as a administrator.
  2. Type following commands

Import-Module remotedesktop

To check the current configurations type following command –

Get-RDConnectionBrokerHighavailablity

Set the connection string –

Set- RDDatabaseConnectionString -DatabaseConnectionString “DRIVER=SQL Server Native Client 10.0;SERVER=<Principle server name>;Failover_Partner=<Mirror Server>;Trusted_Connection=Yes;APP=Remote Desktop Services Connection Broker;Database=<DatabaseName>;

If the command succeeds, confirm it in GUI mode.

1

To test the configurations, fail over the principle database to mirror using SQL management consol. And refresh the connections from Server manager – Remote desktop services- collections – connections and refresh. If mirror successful this connection remain as same. J.

If connections are empty, that means broker servers are not connecting to the mirror server database, most common problem is database security in mirror server. So check all brokers have full access to the mirror database and mirror database saved folder.

2

Hope this is helpful, cheers.

3 thoughts on “Window server 2012 | Remote Desktop Connection Broker high available with SQL Server Mirror

  1. Is there a typo in your connection string for powershell. Quote missing from end of the whole line or just the part that states “DRIVER=SQL Server Native Client 10.0;SERVER

  2. Do you need to run the powershell command and set the connection string on both servers? If so, do I need to switch the principal and failover partner parameters for the second server?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s