Funny Case in Reporting Services

Hello everyone,

This week I had a funny case about Reporting Services scale-out. If you are aware of this subject you now that you can use multiple instances of SQL Server Reporting Services using the same Report Server database.

I had two Report Servers in different servers and a Report Server remote database in a SQL Server cluster:

· Server A – Instance1

· Server B – Instance2

· Server Z – SQL Server Cluster

So, the goal was to remove SSRS Instance2 from scale-out keeping only SSRS Instance1.

If we look into Reporting Services Configuration Manager we could see the two instances in the scale-out configuration section:

First step would be remove to Instance2 from scale-out and then uninstall Reporting Services bits from Server B.

But something weird happened. First the person responsible for this operation removed Instance2 using Reporting Services Configuration Manager where the following warning appeared before removing the instance from scale-out.

This warning is normal and after we click OK, we can see that Instance2 was removed from scale-out giving the information of the installation ID that was removed.

Now comes the strange part. If you look into Keys table in Report Server database or using Reporting Services Configuration Manager in Instance1, we can see that Instance2 record still exists!

If we check the status column in scale-out section we see that Instace2 is in “Waiting to Join” state. Basically the operation done by the user simple removed the symmetric key information for Instance2 but did not removed from scale-out.

So what can we do? If you try to add again Instance2 (This option does not make any sense!) you can see that it complains that SSRS instance no longer exists in the server.

Next, the logic step would be to use the rskeymgmt.exe tool with option “-r <installationID>” to remove the symmetric key information for a specific report server instance, thereby removing the report server from a scale-out deployment. The <installationID> is a GUID value that can be found in the RSReportserver.config file or in the Keys table in Report Server database.

But something went wrong. Tool return an error indicating that Report Server Windows services for instance MSSQLServer was not found. If we look closely MSSQLServer is the default named instance for SQL Server.

How can we solve this issue? Let’s recap our environemt: One server with SSRS Instance1 and another server with SSRS Instance2. Since we are running this tool in the server with Instance1 (and Instance2 was part of Instance1 scale-out) we need to specify the “–i <Instance ID>“option while running rskeymgmt.exe.

So we ran rsmgmtkey.exe with options –r and –i like the following picture specifying Instance2 installation ID and Instance1 as scale-out instance.

And here you go! Now everything is in place and Instance2 is finally off the scale-out topology.

Hope this solution helps you!

Alexandre Mendeiros Premier Field Engineer – Portugal

Reference

rskeymgmt Utility (SSRS)

https://msdn.microsoft.com/en-us/library/ms162822.aspx

Scale-out Deployment (Native Mode Report Server)

https://msdn.microsoft.com/en-us/library/ms181357.aspx

Add and Remove Encryption Keys for Scale-Out Deployment

https://msdn.microsoft.com/en-us/library/ms155931(v=sql.110).aspx