Breaking SSRS, scariest thing to not-do this week

Friday afternoon, I was TOTALLY freaking out because I broke SSRS (SQL Server Reporting Services) at work.  I was trying to configure SSRS so it could email reports to people.  I changed a config file (rsReportServer.config).  It didn’t seem to have any effect, so I restarted SSRS on the server.  After that I got a message:

The feature: “Scale-out deployment” is not supported in this edition of Reporting Services. (rsOperationNotSupported) (rsRPCError)

No problem.  I made a backup of the config file, so I restored the config file and restarted SSRS.  The same error came up.  Uh oh.

I remembered that I had copied a row of data from our old SSRS server to the new one.  It was from the scheduling table of the ReportServer database.  Okay, I deleted that row and restarted SSRS.  Same problem.  Then I restored a backup of ReportServer and ReportServerTempDB from yesterday.  Still no luck! 

So, I googled the problem and found some stupid crap about Visual Studio team services (skipped) and the rest were messages saying that I was trying to do something that would only work on Enterprise Edition of SQL Server.  No hints beyond that!  No clue what could be causing this problem.

It was at that moment that I realized that I had run through my whole rollback plan plus all typical rollback procedures, with no luck.  Then I started freaking out.  Zomg.  It was just a tiny config file change.  How could I have killed SSRS so that a restore wouldn’t even fix it?!

I moped over to the IT Admin’s office and confessed what I had done.  I explained everything that I had done. (Did I mention this was on a Friday afternoon, around 4:30 pm).  He suggested that I uninstall SSRS and reinstall it over the weekend.  Ah hah!  That is something I hadn’t thought of.

Okay.  Fast-forward through a weekend of guilt, to Monday morning.

In the dept meeting, I confessed that I broke everything and I am a total dumbass for not having a backout plan that would actually work.  I hate myself and I want to die.

After the meeting, I keep working on it, restoring files, rolling back old tape, etc.

I decide to google the original error message again and at the top of the list is that stupid message about Visual Studio Team services.  Well, I decide to read the whole article (after all, it was #1 on google for some odd reason).  At the very bottom of the discussion chain, is this information:

Sunder Raman [MSFT]Moderator – Posted on Friday, March 17, 2006 8:36:20 PM

The documentation does not cover moving a single server to a different server yet. This documentation will be available post RTM.

When using SQL Standard Edition, the message you are getting is expected. This is because after you restore the databases and your old encryption key there will be two instance id’s listed under Reporting Services and this is not supported for the Standard Edition. You can check this by running the RSKeyMgmt tool using the -l switch as follows: “%ProgramFiles%\Microsoft SQL Server\90\Tools\Binn\RSKeyMgmt.exe” -l

For fixing your problem, after you move to the different server, you can remove the old instance id from Reporting Services.

Before you restore the databases onto your new server note down the instance id using the command mentioned above on the new installation. Then after restoring the databases and the old encryption key, if you run the same command you will see two instance id’s printed out. Remove the old instance id using the -r switch on the same tool as follows: “%ProgramFiles%\Microsoft SQL Server\90\Tools\Binn\RSKeyMgmt.exe” -r <oldinstanceid>

On the Enterprise Edition, since scale-out deployment is supported you wont see this problem. But in this scenario, you can click on the Encryption Keys section on the Reporting Services Configuration Tool (note: this section will be accessible when you are using Enterprise Edition of SQL) and remove the old instance id.

About your other post, I will look at it now and post a response on that thread.

Thanks,

Sunder

Bingo!
I run RSKeyMgmt.exe -L to find the ID of the offending key. 
Sure enough, there is a key in there that refers to an old SSRS server (now retired). 
I run RSKeyMgmt.exe -R (GUID of old instance ID)
Restart SSRS and IT WORKS!

Okay, here is how this mess actually happened!

Two friggin months ago, we were moving from our old SSRS server onto a newer beefier box.  On my first attempt (at moving/porting), I ran a bunch of queries to copy the reports, settings, everything from the old server to the new one.  Well, I ran into problems with encrypted settings and stuff like that.  So, I wound up doing most of it via a more manual process (Reporting Services Scripter from SqlDbaTips.com). That utility worked like a charm.

The kicker: I had already tried using SSIS to copy everything (including the encryption key) from the old server, onto the new server.  It didn’t seem to work and there were no side-effects, so I just left it there.  No biggie, right?  Well, nobody had rebooted that server or restarted SSRS in the several months since I did that.  So, when I restarted SSRS, it actually tried to (re)load the encryption keys (that I copied-in several months ago) and it choked.  Can you believe that?!

Anyway, its fixed now.

Advertisements

About Tim Golisch

I'm a geek. I do geeky things.
This entry was posted in Database, Errors, Reporting. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s