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.
- 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.
- Select database name as ReportServer, which was restored on DESTSERVER and click next until the last option and finally click Finish.
- Now, restore the Encryption key with password from the backup taken on SRCSERVER to DESTSERVER.
- 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.
- Check Scale-Out Deployment in Reporting Services Configuration Manager on DESTSERVER. We will only see one entry of server DESTSERVER.
- Next, if we open the report manager URL, i.e., http://destserver/Reports/Pages/Folder.aspx?ViewMode=Detail ,we will see all the reports migrated from SRCSERVER, with data sources mapped and rights assigned to users.
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!
- Column Store Index in SQL Server 2012
This post is about the new feature, i.e., Column Store Index which is available since SQL 2012 version. Microsoft has released column store index to improve the performance by 10x.…
- Working with SQL Server Database on Microsoft Azure (Part 2)
The huge volume of data generated around us, today, requires optimum storage and management to have any benefit for organizations. With a host of advantages that MS Azure possesses, especially…
- Working with SQL Server Database on Microsoft Azure (Part 3)
This is the third and the final part of the series. We started with learning to register MS Azure and create SQL database & objects. Progressed to migrating the on-premise…
- Working with SQL Server Database on Microsoft Azure (Part 1)
One of the trickiest implementations for any organization is the database. They require a dedicated server and someone to manage it. Cloud services such as Microsoft Azure give organizations the…
- Microsoft SQL Server Execution Plan
In a database environment, certain questions related to performance pop up repeatedly: why is my query running slowly? And why is SQL Server not using the index? etc. As a…
- Activating the New Power BI Embedded Reports in Dynamics 365 for Finance and Operations Part – 1
While most users interact with business applications daily, only a small section accesses analytical insights. Microsoft completely understands the different needs of the users and offers a Power BI tool, which…