I 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.
Great post – really could have used the advice about keeping the existing Content databases about a month ago 🙂
Ha!! That means we need to chat more Matt…
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.
Yes, the process to upgrade from SSRS 2008 R2 to 2012 would be the same.
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.
Have a look at the following article, which addresses your scenario: http://technet.microsoft.com/en-us/library/bb326407(v=SQL.105).aspx
In short, you will have to install the 2012 instance in native mode first, and then redeploy all your reports. It is not possible to migrate content databases from integrated to native mode, because of the differences in stucture.
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.
I have a lot of data-driven subscriptions. Will they be created automatically after upgrade? What about those “12as23ct67kgk0sk9h7s9xzy” type SQL jobs?
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.
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
As far as I know, there is no direct upgrade path from SSRS 2000 to SSRS 2012. You would have to upgrade from 2000 to 2005 first.
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!
Glad you found the post helpful. Yes, absolutely…you can use any method you prefer for the backup/restore step. Just remember to stop the SSRS service on the target server.
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?
Hi Sam, glad it helped. Once the content databases are restored, the SSRS service should take care of the creation of those SQL Agent jobs via a back-end process.
Thanks Martin, You are a life saver 🙂
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…
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.
Hi Martin, I am migrating and upgrading SSRS 2005 to SSRS 2014 (native), is the process any different? Thank you.
Hey Paul. Yes, as long as you are on SP4 an upgrade to SSRS 2014 is possible. The following TechNet article lists the possible upgrade paths: https://technet.microsoft.com/en-us/library/ms143393(v=sql.120).aspx
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!
Yes, SP4 has to be applied to the 2005 instance before moving the content databases. And yes, all data sources will be transferred to the new instance when following this method.
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)
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).
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.
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.
It shouldn’t be a problem, provided that your 2005 instance is on SP4 at least.
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
Are you just upgrading Visual Studio and staying on the same version of SSRS? If so, then there shouldn’t be any issues.
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
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.
Will do in the future, thank you for your help!
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
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.
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
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.
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
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.
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
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.
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 ?
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
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
What method did you use to migrate the reports and subscriptions?
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.
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?
I have done both of those things and it still is not working. When I try to go to the Subscription portal it tells me something went wrong. The funny thing is that it worked on a Developer version of Sql2016. I have also found this on the internet.
http://www.sqldataplatform.com/Blog/Post/70/Migrating-SSRS-to-2016—Subscriptions-and-Suppressing-Errors-in-the-Error-Log
Does this sound like it will solve my issue?
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
Thanks for the article Martin which seems so encouraging to do migration.
I have a few doubts which are rounding in my head,
1)My 2005 reports are developed in 32-bit and new SQL Server 2012 and SSDT are 64-bit. Is migration possible?
2)If reports are successfully migrated for now, if i need to change the structure of the migrated reports in future. Will i be able to do changes to .rdl files in 2012(SSDT/BIDS) and re-deploy the reports in the target server URL or does the .rdl file throws the compatibility issues?
Hi Bhanu, yes to both of your questions. Don’t confuse the software architecture of SSDT (32 or 64-bit) with the structure of the rdl files. When opening up the 2005 rdl files in a newer version of SSDT (and depending on the target version of the Report project in SSDT), it should prompt you whether you’d like to upgrade the report definitions (rdl) to the new version.
Thanks Martin for the reply…
For me the confusing step is 2. Backup encryption keys is to be taken for 2005 r 2012?
Could you pls elaborate me the 2nd step.
Thanks
Bhanu
You’ll need to backup the encryption keys in your existing SSRS instance (2005 I assume), and restore it in the new SSRS instance. The following link explains the process in detail: https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms157275%28v%3dsql.105%29
Hello Martin,
I am trying to restore Encryption keys from SQL 2005 to SQL 2012. however i am facing this error:
________________________________________________
Microsoft.ReportingServices.WmiProvider.WMIProviderException: The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is ‘C.0.8.40’. The expected version is ‘162’. (rsInvalidReportServerDatabase)
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)
_________________________________________________
Any solution to this?
Thanks in advance.
I haven’t seen this error before, but have a look at the following as it may help: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a63b64b4-4811-4a97-b1c3-2dedfb8b188f/database-upgrade-problem-migration-from-ssrs-2005-e-to-ssrs-2008r2-e?forum=sqlreportingservices
Martin, this is a great article. My question is would this work for SQL 2005 SP4 SSRS to SQL 2016 SSRS, as well. Or, would we need to do a double hop migration/upgrade; from 2005 to 2012 and then use SSRS migration tool (or rs.exe SSRS migration script https://docs.microsoft.com/en-us/sql/reporting-services/tools/sample-reporting-services-rs-exe-script-to-copy-content-between-report-servers?view=sql-server-2017) from 2012 to 2016?
TIA for any thoughts you might have.
Haven’t tried it to be honest, and I also don’t know of anybody who has. I think it would be safer to go to 2012 first, as that is a supported upgrade path. Would be interesting to know the outcome if you give it a try though 🙂