Upgrading SSRS 2005 to 2012

La Sorpresa del Científico LocoI was recently involved in a project to upgrade a SQL Server 2005 environment to 2012. The upgrade wasn’t in place though, because the client decided to buy new hardware as well.
 
You will be amazed at how easy it is to migrate/upgrade an SSRS instance to 2012, and we really only encountered one minor snag (and not with the upgrade itself) which I will detail in a different blog post. Here’s what you have to do in order to move your SSRS 2005 environment to a 2012 instance (new/different hardware):
 

Step 1: Install & Configure SSRS 2012

The installation and configuration of the new 2012 instance goes without saying, and should be the first step in the process.
 
 

Step 2: Backup Encryption Keys

Encryption keys are used to encrypt (no surprise) the contents of your Report Server databases. This is required for various reasons, but mostly due to the fact that credentials of data sources can be stored within Reporting Services. If it wasn’t encrypted, anybody with access to the ReportServer database would be able to see all your data source account and password information.
 
If you are going to restore/move the SSRS databases (ReportServer and ReportServerTempDB) on/to the 2012 instance, you will need the encryption keys from the old environment. A very important fact to remember is that you will lose all your connection and subscription information should you delete the encryption keys (more info here). The Reporting Services Configuration Manager can be used to back up encryption keys.
 
 

Step 3: Restore ReportServer and ReportServerTempDB databases

If your SSRS databases are small enough, you could actually opt to detach (from 2005 instance), copy (to new 2012 server) and attach (to 2012 instance) them rather than backup and restore. An important point to here is to stop the SSRS service on the new instance before attaching or restoring the content databases.
 
After the content databases are restored/attached, you simply need to change the compatibility level (of both) to 110 for SQL Server 2012, and start the service. The upgrade happens at this point…and yes, it is as simple as that!!!
 
 

Step 4: Restore Encryption Keys

You should receive an error if you try to access the Report Manager on the new 2012 instance at this point. The reason for that is because the encryption keys (automatically generated when SSRS 2012 was installed) is no longer able to decrypt the information in the newly restored/attached content databases.
 
To resolve the issue you simply have to restore the encryption keys that you backed up from the old SSRS 2005 instance, and restart the services. Use the Reporting Services Configuration Manager for this task.
 
 

Step 5: Update Data Sources

Don’t forget to verify all your data sources. You may have to update some of these to point to the right database server…
 
 

Why Should I Restore The SSRS Content Databases?

We all like to start with a new (and clean) environment. You will be tempted to re-deploy all your reports and recreate security, but there are a few very good reasons why you may not want to go down that road:

  • Subscriptions & Snapshots – If users create their own report subscriptions and/or snapshots, it will be a very tedious task to recreate it in the new environment. Asking end users to recreate their own subscriptions etc. may also not be acceptable.
  • Folder-level Permissions – If you have many sub-folders with different permissions, it will be very cumbersome to reproduce in the new environment.
  • Deploying Reports Manually – Deploying reports through BIDS/SSDT is quick and easy, but if you for some reason (it happened in our case) do not have the latest version of your reports in a BIDS/SSDT project for deployment, it may take a lot of time to extract the existing reports from the 2005 instance before re-deployment.

 
 

Advertisements
Upgrading SSRS 2005 to 2012

48 thoughts on “Upgrading SSRS 2005 to 2012

  1. Suresh says:

    Is the same procedure for upgrading from SSRS 2008 R2 to 2012 ?
    Please respond and suggest the steps to migrate all reports including subscription’s from SSRS 2008 R2 to SSRS 2012.

      1. Suresh says:

        Thanks for quick response.

        Here we need to upgrade SSRS 2008 R2 installed in sharepoint integrated mode to SSRS 2012 Native mode.

        Please can you suggest how we can migrate it ?

        Thanks and Regards,
        Suresh.

  2. Clark says:

    I recently inherited a SQL 2005 environment that I need to upgrade/migrate FAST. Thank you for your insight; This project now seems a little less daunting.

  3. woody barry says:

    I have a lot of data-driven subscriptions. Will they be created automatically after upgrade? What about those “12as23ct67kgk0sk9h7s9xzy” type SQL jobs?

    1. If you are upgrading in place, yes…everything will stay intact and you don’t have to do much.

      If you are migrating to a new server, you have to backup/restore the content databases to ensure that your subscriptions move as well. The SSRS service will take care of the SQL jobs that facilicate subscriptions…you don’t have to recreate or script/deploy those.

  4. sathish says:

    What is the process to be follow to migrate the SSRS 2000 version to SSRS 2012.
    On changing the Datasource 2000 to new version of datasource 2012 is fine.

    Thanks in advance

  5. GB says:

    Very helpful post!!..In step 3 do I need to use T-SQL to backup / restore or can it be done via MSQL Management Studio? For me the issue is that I cannot detach both data bases during the day. Thanks!

  6. Sam says:

    Excellent post! Perfect timing! Thank you! Just one question..
    I am migrating an SSRS 2005 with over 3000 reports to 2012. My contents are scattered across many other servers in the domain and they are being upgraded to 2012 gradually. Will my subscriptions still work? How will the subscriptions, all “12as23ct67kgk0sk9h7s9xzy” type SQL jobs being created after the migration?

  7. John says:

    We have a 2008 SSRS server on a Windows 2003 Server. Trying to upgrade to Windows 2012 Server and 2012 SSRS.

    Can we do an easy upgrade and keep subscriptions? Everything IT is saying is no…

    1. The process described in this blog post should work for an upgrade from SSRS 2008 to 2012. If you are planning an in-place upgrade, I’d recommend that you make sure that the new OS version is compatible with the current version of Reporting Services…especially if you plan on upgrading the OS first. Always have a back-out plan when upgrading in place.

  8. Paul says:

    Hi Martin, thank you for you reply. In regards to to SP4, just so I am clear, if I was to backup/restore or detach/reattach to the new 2014 machine on which I will start SSRS and do the upgrade, sp4 still has to have been applied to the 2005 instance right? Thank you again for your help. Just one other question, I will be reattaching them in a different test domain, assuming for a moment that the source databases have been copied across, will the data sources have been copied across within the SSRS databases? I realize these would need to be opened and corrected, but was wondering if they would be there in the first place. Thanks again!

  9. Discovery2012 says:

    Hi,
    I am trying to migrate ssrs 2005 to ssrs 2014. I have successfully restored the ReportServer and ReportServerTempDB database.

    I have created the backup of encryption key of ssrs 2005 but I cannot restore it to ssrs 2014. I get this error message:

    Microsoft.ReportingServices.WmiProvider.WMIProviderException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. (rsReportServerDatabaseUnavailable)
    at Microsoft.ReportingServices.WmiProvider.RSWmiAdmin.ThrowOnError(ManagementBaseObject mo)
    at Microsoft.ReportingServices.WmiProvider.RSWmiAdmin.RestoreEncryptionKey(Byte[] encryptedBytes, SecureString password)
    at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.RestoreEncryptionKey(Byte[] encryptedBytes, SecureString password)

    1. It almost looks like a permissions issue to your back-end database. Here’s a few things to check or keep in mind:
      1. Have you applied SP4 before migrating the databases?
      2. Are you logged in with an account that has administrative permissions on the Report Server?
      3. Is the Report Server able to connect to the back-end database? This is what the error seems to point to…a connectivity issue to the back-end database.
      4. Try to stop the service before trying to restore the keys (if that is possible).

  10. Discovery2012 says:

    Hi Martin,
    Thanks for your quick reply and I apologize for my late feedback.

    No, I did not apply the SP4 before migrating the databases. Since all the reports were being used for only a few users with same level of permission, rather than transferring report server, I just exported the SSRS files and deployed to the new server.

    I appreciate your help.

  11. WM says:

    Thanks for the great post. I am a bit of newbie when it comes to SSRS. At the moment my client has the SSRS dbs and source db on the same server (SQL server 2005 on a W2K3 box) and my clients wants to move the SSRS dbs out to a separate server and upgrade the SSRS dbs to 2014 (SQL server 2014 on a W2k12 box), but with the source db remains the same. There is no subscriptions but with heaps of report snapshots. Is it going to be a problem? Thanks.

  12. Wayne says:

    I have been using the 2008 VS free /”shell” version for a while and have created dozens of reports and subscriptions that I need to keep.

    I just purchased VS 2008 Pro to install because it has some additional features that I now need.

    What is the easiest way to ensure that my current projects, reports and subscriptions will work in the 2008 Pro version?

    Thanks

      1. Wayne says:

        I do not know any reason that we would need to change SSRS versions, I assume that SSRS normally comes with SQL Server and just installing VS 2008 Pro would not change the SSRS version? Thanks

      2. Correct, your VS version will not change the SSRS instance. This post is however about upgrading SSRS and probably not the appropriate place to have a Visual Studio discussion. It may be a good idea to post your question on a VS forum.

  13. Rp says:

    I am doing mirgation ssrs from 2005 to 2012 , the datasource for 2005 is oracle , after migration to 2012 i will recreate data source to all my reports

    1. Yes, data sources should stay in tact. If you’re using a system DSN or the Oracle drivers and you’re moving to a new server, ensure that the same drivers and/or system DSNs are also available.

      1. Rp says:

        Yes i am moving to new server 2012 , we are using the oracle drivers for connection
        one more question
        we don’t have encrypt key password? now i want to restore the key in 2012 report server conf manger , here is there any other process for restore the key for regarding the migration
        Thanks in advance

      2. There’s no way around it if you don’t have the password. You could recreate the encryption keys on your current SSRS 2005 server, but will lose all encrypted data if you do that.

  14. Rp says:

    thank you for your reply
    It seem they are 1000 reports user need some reports to migrate can we know which reports(.rdl) are using\needed and running in SSRS ?
    is there any script to find all

    1. Your Report Server content database (“ReportServer” if you’ve kept the default name) contains a view “ExecutionLog3” that has that information. Assuming that you are keeping enough history in there, you should be able to see which reports are being executed and by whom.

  15. Rp says:

    SSRS migration from 2005 to 2012 ,i am copying th .rdl from report manger and add in solution explorer in2005 while do this to view designer , but i am facing the issue and error DTD is prohibited in XLM documet please help in this

    1. Are you using Visual Studio 2005 or SQL Server Data Tools? Not exactly sure what that error means, but providing the versions and exact error message may help. Also post your question in some of the forums where you can provide screenshots etc. of the error.

  16. Rp says:

    HI Martin,,
    In migration of reports from 2005 to 2012 , we are using datasource is sharepoint list while configiuring the datasource is we getting a error connection string invalid
    please suggest me any prerequisite is they to configure in share point web server ?

    1. Hi, unfortunately the information you have provided is not enough to effectively troubleshoot or answer your question.

      I recommend posting your question with supporting information on a technical forum like SQLServerCentral.com

  17. Anonymous says:

    I have migrated from ssrs 2008 to ssrs 2016 and the Subscriptions are not showing up in the jobs folder and I am generating a lot of errors in the ssrs log folder

  18. Tim Abrams says:

    I am having an issues with a ssrs 2008 – ssrs 2016 migration. I have restored the report server,the temp db, and the encryption key on the new server. I have a couple of issues, the subscriptions are not showing up under the jobs folder and I am generating 32 mb per minute of errors in the ssrs log folder. Any help would be appreciated.

    1. Hi Tim,

      I’d recommend that you restart the SSRS service, and also verify that the SSRS service account has appropriate permissions to create SQL Agent jobs.

      Do you see the subscriptions in the portal?

      1. Interesting scenario, and one that I haven’t seen before. The SSRS service should really be creating those jobs if they don’t exist at startup, but for some reason that isn’t happening here.

        From a cursory overview, it looks like the method described in that post will work. Make sure that you test it thoroughly though.

        Another option would be to manually script all the SSRS jobs and redeploy them on the new server, assuming that you don’t have too many.

        Personally, I would try the SSRS migration tool first seeing that you’re dealing with SSRS 2008 and not 2005. More details here: https://www.microsoft.com/en-us/download/details.aspx?id=29560

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