Manage Learn to apply best practices and optimize your operations.

No-risk SQL Server upgrade for SSRS no problem

Worried about mishaps during your next SQL Server upgrade? What if you could perform an upgrade that is virtually risk free? It is possible, database architect Roman Rehak writes.

Any time you run an in-place SQL Server upgrade, you risk something going wrong; for example, your server or application...

could break. When this happens, you need to revert to the previous version, uninstall the software, reinstall the previous version and restore settings and databases. This can take a lot of extra time and greatly extend the window allocated for the SQL Server upgrade. Meanwhile, your software is unavailable to users. A better way to do this is to upgrade your SQL Server Reporting Services (SSRS) instance by using an SSRS server that can take over in case the upgrade fails. This method is virtually risk-free.

SSRS consists of several components that work together -- a Windows service, a .NET Web service, ASP.NET application and a back-end database. All reports, folders and other SSRS objects are stored in a database called a “report server.” A migration of SSRS from one server to another involves several steps, but the main one is backing and restoring the database on a new instance. Doing this moves all content to another server in one step. You can take advantage of this easy migration. If you’re planning an in-place upgrade, set up another server on your network to act as a standby. Once the standby server is set up, perform the upgrade on your original server. If something goes wrong with the upgrade -- for example, install scripts fail or there are unexpected errors -- instead of uninstalling and reinstalling SQL Server and SSRS, simply use the standby as your primary server.

Install SQL Server, SSRS on a standby server. The install options are fairly minimal; you just need a functional SQL Server and SSRS instance. The hardware should be somewhat similar to your current SSRS server, in case you need to use it should your upgrade fail. Also, run the Reporting Service Configuration Manager to verify that all SSRS components are in place and working the same way they are on the current SSRS instance. If your current SSRS uses local folder shares, create them using identical names and security permissions.

Copy custom assemblies, extensions and custom settings in the SSRS configuration files. If your reports used any custom code in assemblies, extensions or any third-party components, install the assemblies on the standby server. Also, copy any assembly related configuration sections from the SSRS configuration files on the current server.

For more on SQL Server upgrades

Is there ever any going back from a SQL Server upgrade or is an upgrade a one way street? Find out

Three tips for a SQL Server upgrade

Top five mistakes companies make in SQL Server upgrades

Back up the encryption key on the current SSRS server. Reporting Services uses encryption to store sensitive data. Back up and safely store your encryption key as soon as you install SSRS, as it is needed to retain your passwords, connection strings and other secure items when you migrate your SSRS instance.  Back up the encryption key in Reporting Services Configuration Manager to a password-protected file.

Back up the report server database on the current SSRS server. Next, do a full backup of the report server database. It might have a different name if you deviated from the default name when you set up your SSRS instance.

Restore the report server on the standby server. Since you’ve installed and configured SSRS, the report server database is already there. In the restore options, select the option to overwrite the existing database. After the database is restored, open the Report Manager application on the standby server. You should see the same folders and report on the SSRS instance. Encrypted content such as connection strings will no longer work, so don’t try executing reports until you complete the next step.

Restore the encryption key on the standby server. Open Reporting Services Configuration Manager on the standby server and restore the encryption key you already backed up. Your connection strings and other encrypted content should work again, and your reports should be functional.

Disable SSRS subscription jobs in SQL Agent on the standby server. Restoring the encryption keys reinitializes the SSRS instance and re-creates scheduled jobs that kick off report subscriptions. Since you’ll have two instances of SSRS -- and therefore the same jobs scheduled twice -- you’ll want to avoid sending duplicate emails or generating report files twice. The easiest way to avoid this is to disable these jobs in SQL Agent and turn them back on only if the upgrade fails and you start using the standby server as your primary. The jobs are easy to find because they have cryptic, GUI-based names.

Your standby server should be configured the same way as your primary and ready to start acting as the primary if your upgrade process doesn’t go well. At this point, you have a few options. You can leave the standby server sitting idle and use it only if something goes wrong, or you can gradually modify settings in SSRS applications and point them to the standby server. You can also redirect any direct URL requests to the new server. Once the standby server is in general use, upgrade the old primary server and move everything back there. The second option gives you the advantage of no downtime since you are upgrading an instance that is no longer used.

About the author:
Roman Rehak is principal database architect at MyWebGrocer in Colchester, Vt. He specializes in SQL Server development, database performance tuning, ADO.NET and writing database tools. He contributes to Visual Studio Magazine, SQL Server Magazine and other publications and presents at user groups and conferences in the U.S. and Canada.

Dig Deeper on SQL Server Backup and Recovery

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.