Windows Server 2012: RD Connection Broker high availability with SQL Server Mirror
Overview
This Document described how to create High available SQL server for high available connection broker set up. To address this we have checked HA for SQL server and found SQL server mirror is supported with RD broker HA. We have implemented this in test and production environment and it works really fine.
Deployment Prerequisites
- Install the same SQL server version on the new server, use the same accounts as in mirror server.
- Witness server – this is for automatic failover purpose. It can be another SQL server or Install SQL Express version on a server. No need for a dedicated server this can be a server that uses for another purpose.
- Added firewall exceptions to all SQL servers.
Steps
1. Configure SQL mirror
Please refer the following TechNet articles when creating the mirror
http://technet.microsoft.com/en-us/library/ms189053.aspx
http://technet.microsoft.com/en-us/library/ms189047(v=sql.105).aspx
Login to principle SQL server and connect to mirror database and witness database server from that management console. Perform this step to identify the connectivity to all principle, witness and mirror database servers.
Change the principal server’s RD database to full recovery mode.
Please refer the TechNet article as we are just only giving the steps that you have to perform.
http://technet.microsoft.com/en-us/library/ms189047(v=sql.105).aspx
Backup the principal database and restore it to mirror server using NO RECOVERY mode
Backup the transactions and restore it to mirror server using NO RECOVERY mode
Note – all SQL servers SQL service should run as a domain user. And check in mirror server side, Database should have sysadmin, owner, the public right to the security group of RD brokers.
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.
Click Configure Security.
If the mirror successful, check the failover
2. 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
- Log in to a Broker server and open PowerShell in a run as an administrator.
- 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.
To test the configurations, failover the principal database to mirror using SQL management console. And refresh the connections from Server manager – Remote desktop services- collections – connections and refresh. If mirror successful this connection remain as same.
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.
Reference