I was recently given the nod to upgrade my monitoring server from SQL Server 2012 (SP 3) to SQL Server 2016.  This came none too soon as SQL Server 2012 (SP3) goes out of mainstream support on July 11, 2017.

We decided to go with a brand new box since the existing one was on Windows Server 2012 (not R2) and had been having issues lately.  So my SysAdmin guy stood up a brand new shiny Windows 2016 Server box for me.  This will be the first in our domain.  I get to be the guinea pig – WooHoo!

I got SQL Server 2016 installed on the new box without issue.  This box is used as my monitoring server and my personal sandbox, so it has Reporting Services (SSRS) installed on it as well as the database engine.  Since we are using a brand new box, there is no need to shut the old one off before we turned this one on, which is nice.  I can migrate things when I have time.

My first order of business was to migrate my SSRS databases to the new box and get it configured.  Now, it’s been a while since I’ve migrated an instance of SSRS, so of course I forgot something.  Otherwise you would not be reading this post.

I remembered to backup BOTH databases and the encryption key.  Once I had the databases restored on the new server I started the Reporting Services Configuration Manager so I could restore the encryption key so I wouldn’t lose all my credentials and other security sensitive information (BTW – Here’s a great reference for migrating SSRS).  After I restored my encryption key, I wanted to generate a new key, but the Backup button was not enabled on the Encryption key tab.  I didn’t think much of it, I just restarted SSRS and figured it would be available after restarting.  Of course it wasn’t.  I tried navigating to the URL for the SSRS Web Portal (replaces Report Manager) and I got an error saying Reporting Services was not configured correctly.  Interesting.  It took me about 15 minutes to realize/remember the last step in restoring the encryption key.  Do you remember what it is?  Without Googling it?

Okay, I’ll tell you since you’ve made it this far in the post.  Now I will tell you that I was not the one that installed/configured SSRS on the old server, so I was not aware that whoever did, configured the database for a scaled out deployment, even though it was not being used in a scaled out deployment <sigh>.  When you have a database that has been configured for scaled out deployment, you have to clean up the entries in the Keys table in the ReportServer database (the link above has a note about this very thing about three quarters of the way down).

Lesson learned:  Always look at the existing configuration very closely.  I totally didn’t notice (and hadn’t noticed for 3+ years) that the database had been configured for scaled out deployment, shame on me!

My shiny new SSRS 2016 instance is up and running now.