Dedicated or Shared SQL for HA Connection Broker?

Question

I am adding a new server cluster to an environment that already has a high availability cluster present.

The old HA RDS setup is Windows Server 2016 based and connects to an MSSQL server that's SQL 2016. The new servers are Windows Server 2019 and I've installed the same version of SQL Native Client as the existing 2016 RDS servers. I am ready to configure HA for the Connection Broker but can't find the best answer for this question. I am using the same SQL Server however I was planning on creating a new DB on said server. When I configure HA it's asking me to choose Dedicated or Shared SQL server and I cannot find a great answer for this question.

Answer 1

Hello all,

I would like to make it clear for everybody who are seeking for answers:

1. Dedicated - this basically means, for any server located on premise. The database is created during the setup, and you do not have an option to pre-stage the database. Your computer account will need temporary the dbcreator role and there is no way how to bypass it (the wizard will remove the database and create it from the scratch. I have open the MS case, and they told me that there is no other way
2. Shared - according the name would be logical, that this means for database located on shared SQL server. But I spent many hours to figure out what should be the connection string used for On-premise shared server. I gave up with the conclusion, that this is used for any Shared database hosted in Azure (or any other provider). Basically the connection string expecting some string which is typical for Azure SQL. I asked the Microsoft and they were not able to give me the appropriate answers.

So if somebody will figure out, what connection string could be used for Shared on-premise SQL server, I will be more then happy

Pavel

Answer 2

Thanks. We're moving in a different direction with the SQL server and upgrading to an MSSQL 2019 server. Should we also upgrade the Native client to something newer? And what would that do to the connection string?

Edit:
Why has my username changed?

Answer 3

I am getting the following error and can't figure out how to resolve it:

The database specified in the database connection string is not available from the RD Connection Broker server Server.company.com. Ensure that the database server is available on the network, the Database Server Native Client is installed on the RD Connection Broker server, and the RD Connection Broker has write permissions to the database.

I've made sure in AD Users/Computers the two new servers have the same permissions as the current, old RD HA cluster.
I am using the same SQL Native Client as the old HA cluster. The driver reports success when testing the connection.

Here is the string I am using:
DRIVER=SQL Server Native Client 11.0;SERVER=DBServer;Trusted_Connection=Yes;APP=Remote Desktop Services Connection Broker;DATABASE=RDSConnectionBroker2

What am I missing?

Answer 4

Thanks for your answer.

I have a question regarding the "Dedicated" option. If I went with that option I am assuming the setup would create a new DB for the new HA Cluster and leave the existing one alone, correct?

Answer 5

Hi PaulH-1052,

The dedicated database server option is for a dedicated machine with only the instance of SQL Server dedicated for RD Services and it will create a database automatically; the Shared database server option is for a new SQL Instance on an existing install with other Instances and you need to manually create an existing database on SQL Server. In addition, dedicated database server option used windows authentication, but shared database server option used SQL Server authentication.

For more details, please refer to:

Remote Desktop HA Connection Broker

RD Connection Broker High Availability

Best Regards,

Amelia

=======================================
Please remember to click " Accept Answer" and upvote the responses that resolved your issue.