Post2

SQL Server – Reporting Services Migration

Posted by

A while back, I got a task in my project, which was to migrate SQL Server Reporting Services from SQL 2008 to SQL 2012. Now, I had two ways of doing this, I could either deploy hundreds of reports manually one by one or move ReportServer database to the new server. I picked up the latter and thought of writing a blog for the same that will explain how to move reporting services from one server to other by moving ReportServer database with steps.

 

Pre Migration Steps

  • Backup ReportServer and ReportServerTempDB databases on source server SRCSERVER.
  • Backup Encryption Key with password using Reporting Services Configuration Manager. If we are going to restore/move the SSRS databases (ReportServer and ReportServerTempDB) to the 2012, we will need the encryption keys from the old environment, otherwise we will lose all connection and subscription information, if the encryption keys are deleted.

  • Backup ReportServer and ReportServerTempDB databases on destination server DESTSERVER.

 

Implementation Steps

  • Stop Reporting Services on destination server DESTSERVER.
  • Restore backups of ReportServer and ReportServerTempDB databases taken on SRCSERVER to DESTSERVER with replace.
  • Start Reporting Services on DESTSERVER after reporting databases are restored.
  • Go to Reporting Services Configuration Manager and in the Navigation panel click Database, then click the Change Database button.

2.

3.

 

  • Select database name as ReportServer, which was restored on DESTSERVER and click next until the last option and finally click Finish.

4.

5.

 

  • Now, restore the Encryption key with password from the backup taken on SRCSERVER to DESTSERVER.

6.

7.

  • Run SELECT * FROM [ReportServer].[dbo].[Keys] on DESTSERVER. We will see 2 entries against the machine name. Delete entry of source, for instance, SRCSERVER from ‘Keys’ table on ReportServer database on DESTSERVER using the command below.

 Delete from [ReportServer].[dbo].[Keys]

where MachineName = ‘SRCSERVER’

 If we once again do SELECT * FROM [ReportServer].[dbo].[Keys] ,we will see the below result.

8. 

  • Check Scale-Out Deployment in Reporting Services Configuration Manager on DESTSERVER. We will only see one entry of server DESTSERVER.

9.

 

10.

 

The above approach is reasonably useful when we have to deal with SQL Reporting Services migration with numerous reports and subscriptions.

 

 

Until Next SQL Blog!!!

Xavient Information Systems!

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *