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.
- Install the same SQL server version in the new server, use the same accounts as in mirror server.
- 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.
- Added firewall exceptions to all SQL servers.
Step 01 – Configure SQL mirror
- Please refer the following technet articles when crating the mirror
- 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.
- Change the principle server’s RD database to full recovery mode.
- Please refer the technet article, I’m just only giving the steps that you have to perform.
- Backup the principle database and restore it in mirror server using NO RECOVERY mode
- Backup the transactions and restore it in 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, 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
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
- Login to a Broker server and open PowerShell in run as a administrator.
- Type following commands
To check the current configurations type following command –
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, 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.
Hope this is helpful, cheers.