Microsoft SQL Server Reporting Services is one of the most powerful reporting tools available in the Market. Many of the small scale to Large scale enterprise organisation use Microsoft SQL Server Reporting Services. I am not going to describe more on reporting services and its features as tons of resources are available online.
However for those who are using Reporting Services There will be often Business Needs to Have your Report Server Migrated Moved or Restored onto Different Environment for various purpose such as custom reporting, backup reporting, Domain Migration, upgradation etc.
Here i am going to Describe one such ReportServer Database Migration/ReportServer Database Restoration on to Different Server with Different Name. Below are the step by step instructions to have a smooth ReportServer Database Migrated/Restored.
Step1: Go to Your Destination Server Reporting Services where you want to have Migrated/Restored ReportServer Databases configured, Go to Start—> All Programs—>Microsoft SQL Server XXXX(XXXX is your Microsoft SQL Server Version)—>Configuration Tools—>Reporting Services Configuration Manager. The Below Screen Appears. Enter the ServerName and Report Server Instance(MSSQLSERVER by default) and click connect.
Step2: In Reporting Services Configuration Manager, click on Encryption Keys Tab and click backup to backup your current Encryption Keys . This is very much needed incase you want to Rollback the Migration/Restoration and Revert back to Old configuration.
Ste3: Select a safe location to Store the encryption key, provide a strong password as shown below and click ok and close the Reporting Services Configuration Manager. Make sure you wont forget this password as this password is needed if you want to restore this encrytion key back. This encryption key holds all credentials required for reports, subscriptions etc.
Step4: Go to SQL Server Configuration Manager, CLick on SQL Services, Right Click Reporting Services and Stop SQL Server Reporting Services. This can be done from Services.Msc as well.
Step5: This step is needed only if you are overwriting any existing ReportServer Databases. If you are Restoring/Migrating the ReportServer Databases on to New Server, then need not worry. Connect to SQL Server Instance where the ReportServer and ReportServerTempDB Databases are hosted. Backup both ReportServer and ReportServerTempDB Databases and keep it in safe location, where it does not get overwritten or deleted. This is needed if incase you want to Rollback the Migration/Restoration and Revert back to Old configuration.
Step6: Go to Source Server where your SQL Server Reporting Services are running from which you want to Migrate/Restore ReportServer Database to New Destination. Go to Start—> All Programs—>Microsoft SQL Server XXXX(XXXX is your Microsoft SQL Server Version)—>Configuration Tools—>Reporting Services Configuration Manager. The Below Screen Appears. Enter the ServerName and Report Server Instance(MSSQLSERVER by default) and click connect.
Step7: In Reporting Services Configuration Manager, click on Encryption Keys Tab and click backup to backup your current Encryption Keys . This is needed in later stage to restore on destination server incase needed.
Step8: Select a safe location to Store the encryption key, provide a strong password as shown below and click ok and close the Reporting Services Configuration Manager. Make sure you wont forget this password as this password is needed when you are restoring this encrytion key back on destination environment. This encryption key holds all credentials required for reports, subscriptions etc.
Step9: Connect to Source SQL Server Instance where your current ReportServer and ReportServerTempDB are hosted. Take a copy Only Backup so the LSN wont get affected of both ReportServer and ReportServerTempDB Databases and have this two backup files copied onto Destination Server or Any Network Share folder accessible from destination Server.
Step10: Connect to Destination SQL Server Intance where you want the Migrated/Restored ReportServer and ReportServerTempDB Databases to be hosted. Backup the Users,Permissions,Roles,securables etc which needs to be applied back once you restore the Database in Next Steps.
Step11: Connect to Destination SQL Server Intance where you want the Migrated/Restored ReportServer and ReportServerTempDB Databases to be hosted. Restore the ReportServer database from the backup file copied in previous step. Now restore the ReportServerTempDB from the backup file copied in previous step. Drop the orphaned users and assigned New security users or have the security replicated which was existing before Restoration which you may have backed up using previous step. Best Practice is to Retain both ReportServer and ReportServerTempDB name which is coming from source server. That is if the Source ReportServer and ReportServerTempDB Names and Destination ReportServer and ReportServerTempDB are same then no worries. If the Source ReportServer and ReportServerTempDB Names and Destination ReportServer and ReportServerTempDB names are not same, then this will be handled in later steps.
Ex: Source DB Details
MyReportServerDB and MyReportServerTempDB
Ex: Destination DB Details
MyReportServerDB and MyReportServerTempDB
If this is case nothing to worry much.
Step12: Go to SQL Server Configuration Manager on Destination Server, CLick on SQL Services, Right Click Reporting Services and Start SQL Server Reporting Services. This can be done from Services.Msc as well.
Step13: Go to Your Destination Server Reporting Services where you want to have Migrated/Restored ReportServer Databases configured, Go to Start—> All Programs—>Microsoft SQL Server XXXX(XXXX is your Microsoft SQL Server Version)—>Configuration Tools—>Reporting Services Configuration Manager. The Below Screen Appears. Enter the ServerName and Report Server Instance(MSSQLSERVER by default) and click connect.
Step14: Go to Encryption Keys Tab, Click on Restore button, select the Encryption Key which you have backed up on Source Server in Step8. Provide the password and click ok and close Reporting Services Configuration Manager.
Step15: Go to SQL Server Configuration Manager on Destination Server, CLick on SQL Services, Right Click Reporting Services and ReStart SQL Server Reporting Services. This can be done from Services.Msc as well.
Step16: Go to Internet Explorer and try to Open Reporting Services Manager GUI. Ideally it will be http://DestinationServer:80/Reports unless you have configured a different virtual Directory. You should be now able to See the Reporting Services Manger GUI with all the reports which were on source server.
Congrats. You have now migrated your ReportServer Databases to a New Server.
Now if you have encountered a error stating: The report server installation is not initialized. Not to Worry. Please follow the below link to resolve this issue.
Click Here to Resolve The report server installation is not initialized Error
Posts
No comments:
Post a Comment