Remove an old RDS from Server Pool (2012 R2)
First you have to install SQL Server Management studio on your RDS management/licensing server/all of your RDS connection brokers (if your have more than one, follow the below steps, rinse and repeat…):
Open up SSMS RUN AS ADMINISTRATOR and connect here:\\.\pipe\MICROSOFT##WID\tsql\query
Open a query window, run this query:
SELECT TOP 1000 [Id],[Name] FROM [RDCms].[rds].[Server]
Look at the list of servers in the NAME column. Find your dead server and note the ID number in the ID column. For this example, lets say my id is “5”. The ID for your system will most likely be different so don’t just copy and paste the below commands, make sure you change the ID to what you actually need to remove.
—-MAKE SURE YOU HAVE A DATABASE BACKUP OR SYSTEM CHECKPOINT OR SYSTEM BACKUP OR SOMETHING BEFORE PROCEEDING—-
Run the following query to remove the dead SESSION HOST server from the database:
delete from rds.RoleRdsh where ServerID = ‘5’;
I also needed to remove the server from the rds.Server table:
delete from rds.Server where Id = ‘5’;
Note, the above covers the session host role. If you have a server with other roles there are other tables you can look at and delete by id. Just right-click on the table (after drilling down in object explorer in to the RDCms database) and “Select Top 1000 rows” to see if your server ID is listed there.
rds.RoleRdcb (Connection Broker)
rds.RoleRdls (License Server)
rds.RoleRdsh (Session Host)
rds.RoleRdvh (Virtualisation Host)
rds.RoleRdwa (Web Access Host)
You can use the following powershell command from an elevated powershell prompt (before and after to confirm the server is gone) to list out RDS servers:get-RDServer
Once everything is deleted out of the DB, close SSMS and then close and re-open Server Manager. Now you can get to your Remote Desktop Services management screen and get on with your life!