I've received some questions via e-mail on one of my previous posts on how set up High Availability on the Connection Broker in Windows Server 8 (Beta)
The questions were related to preparing the correct (SQL) permissions to be able to enable High Availability using the Server Manager wizard. In this blog post I’ll dive a little deeper in the SQL permissions part. But before we get started, since we’re dealing with beta product and since there is no official Microsoft documentation on this exact configuration yet, this blog post contains my personal thoughts on what I think the configuration should be.
The prerequisite that refers to the SQL permissions is explained by the setup as: “A Microsoft SQL Server with write permissions granted to all RD Connection Broker servers that will be part of the deployment.” But what exactly does that mean?
When you would not prepare any SQL permissions except opening port 1433 for your RD Connection Broker, you will receive the following error in the wizard:
“The database specified in the database connection string is not available from the RD Connection Broker server <servername>…”
Assuming that, 1. you did install the SQL Native Client on the RD Connection Broker and 2. traffic on port 1433 to the SQL Server is possible, you will see a log entry generated on the SQL Server that looks something like this:
The wizard to configure HA is trying to connect to the SQL Server using the computer account of broker.
So, basically what we need to do is create a group in Active Directory, and place all RD Connection Broker computer objects in there.
Then we add this group as a SQL Login in SQL Server manager.
If we then try the wizard again, it will try start to configure HA. However, without any further preparations, the following error will be raised
The wizard is unable to create a database.Why? Because we didn’t assign the AD group any roles or permissions. We open up SQL Server manager again, open the group and specify the role dbcreator.
When try the wizard again, it will succeed.
The database is created and exists in the folder we specified.
Note that when we add a second (or any new) RD Connection Broker to the HA setup, that new server also needs permissions to the database. If those permissions are not in place you will receive the following error in the wizard:
And the SQL Server log will raise the following events:
We open up SQL Server Manager again, open up the properties of the group we created select server mappings, select our database and we (although datareader and datawriter will probably also be enough) give our group owner permissions on our database.
This will allow us to successfully add new RD Connection Brokers to out HA environment, as long as they are a member of the created group.
- create a new AD group that contains your RDCB servers participating in the HA
- Give that group dbcreator permissions to be able to create the database during the wizard
- Give that group owner permissions (or at least dbwriter and dbreader) on the newly create database to ensure all RDCB servers are able to contact the database.
A final FYI to be aware of, if you created a database before starting the RDCB HA wizard the wizard will delete this database and create a new one during the setup.