Partilhar via

Funny Case in Reporting Services

  • Artigo

 

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:

clip_image005

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.

clip_image009

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.

clip_image013

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!

clip_image017

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.

clip_image021

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.

clip_image025

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.

clip_image027

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.

clip_image029

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

clip_image032

clip_image035

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

Comments

  • Anonymous
    June 19, 2015
    Awesome - thank you Alexandre. I have been trying to remove a scale-out server after a TFS migration, where the old server no longer exists, and was having no luck until I found your post! Cheers Tom