How do I move the RDS High availability database from one SQL Always On Cluster to a newer cluster?

Robert B. Conard 20 Reputation points
2023-01-27T22:05:56.92+00:00

I am in the process of upgrading our Remote Desktop Services (RDS) Highly Available Farms, and have an issue repointing the database.

The scenario is we have 4 RDS farms that have Windows 2012 r2 Servers in the farm. We need to decommission the 2012 servers in favor of Windows 2019. I have successfully replaced the Session Brokers from 2016 to 2019, I have also replaced my session hosts with 2019. The issue is moving the RDFarm database from a SQL 2016 cluster (Windows 2012r2 OS), to a SQL 2019 cluster (Windows 2019 OS).

The current Farms are configured in HA with SQL Always On Database Backend. The connection strings are:

DRIVER=SQL Server Native Client 11.0;SERVER=SQLCluster1.Domain.LOCAL;Trusted_Connection=Yes;APP=Remote Desktop Services Connection Broker;Database=RDFarm;MultiSubnetFailover=Yes

I am trying to change the connection to:

DRIVER=SQL Server Native Client 11.0;SERVER=SQLCluster2.Domain.LOCAL;Trusted_Connection=Yes;APP=Remote Desktop Services Connection Broker;Database=RDFarm;MultiSubnetFailover=Yes

I can do a backup / restore of the Database (RDFarm.mdf), the data is there, but if you attempt to set the path with PowerShell it will error out.

The command is:

Set-RDConnectionBrokerHighAvailability -DatabaseConnectionString $DatabaseConnectionString -ClientAccessName $ClientAccessName -ConnectionBroker $ConnectionBroker

I am using a script where the Variables are filled out correctly. The error I get is:

writeErrorStream      : True
PSMessageDetails      : 
Exception             : Microsoft.PowerShell.Commands.WriteErrorException: The RD Connection Broker server is configured for high availability.
TargetObject          : 
CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Set-RDConnectionBrokerHighAvailability
ErrorDetails          : 
InvocationInfo        : System.Management.Automation.InvocationInfo
ScriptStackTrace      : at Set-ConnectionBrokerHighAvailability, C:\Windows\system32\WindowsPowerShell\v1.0\Modules\RemoteDesktop\Deployment.psm1: line 1551
                        at <ScriptBlock>, {Path}\SetNewRDS_SQLClusters.ps1: line 73
PipelineIterationInfo : {0, 1}

I am unable to get the connection brokers to change their connection string to the new location.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,432 questions
Remote Desktop
Remote Desktop
A Microsoft app that connects remotely to computers and to virtual apps and desktops.
4,707 questions
0 comments No comments
{count} votes

Accepted answer
  1. LiHongMSFT-4306 30,586 Reputation points
    2023-01-30T07:15:02.66+00:00

    Hi @Robert B. Conard

    Not an expert on this issue, but I found a similar thread might help: Can't modify "Database connection string" for RDS RD Connection Broker High Availbility settings

    Best regards,

    Cosmog Hong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our **[Documentation](https://docs.microsoft.com/en-us/answers/support/email-notifications

    )** to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. TP 102.4K Reputation points
    2023-01-27T23:45:20.4933333+00:00

    Hi,

    Please use Set-RDDatabaseConnectionString cmdlet to update the connection string.

    Set-RDDatabaseConnectionString

    https://learn.microsoft.com/en-us/powershell/module/remotedesktop/set-rddatabaseconnectionstring

    If the above is helpful please click Accept answer.

    Thanks.

    -TP

    0 comments No comments

  2. Robert B. Conard 20 Reputation points
    2023-01-30T14:44:17.2566667+00:00

    Thanks for the article, that is exactly what was needed. I ended up using the following command to set the string on all connection brokers:

    Set-RDDatabaseConnectionString -ConnectionBroker $ConnectionBroker -DatabaseConnectionString $DatabaseConnectionString -RestoreDatabaseConnection -RestoreDBConnectionOnAllBrokers


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.