Good evening. Today’s episode is coming to you from my home office, where I feel motivated to write a blog in the comfort of my home.
Today we will discuss migrating SSRS from one instance of SQL Server to another. Several methods are available for you, but I will show you how to backup and restore the Reporting Services components for a full migration.
Problem
Let us say that you are working on a project to upgrade all of your SQL Servers from version 2014 to 2019. What would be your approach for this project regarding those SQL Servers running SSRS? Would you find a way to export all the reports, data sources, data sets, etc., from the source server and then import them to the new destination server? Do you deploy all of the resources from source control? (I doubt many shops are that sophisticated.)
My method of choice is to do the backup and restore method, which I have performed many times with great success.
Phase I – Source Server Steps
There will be three steps to perform on the source SQL Server. In my case, I have created a SQL Server 2014 instance with SQL Server, and SSRS installed. I have also installed some example reports in SSRS to show that the resources are moved to the new server.
Massive shout out to Anthony for these great example reports. So you can see we are working with four folders, with multiple resources under each folder.
The first item on the agenda will be to backup the SSRS-related databases.
BACKUP DATABASE [ReportServer] TO DISK = 'C:TempSQL2014_ReportServer_FULL_07182022.bak' WITH COPY_ONLY , COMPRESSION , STATS = 10 BACKUP DATABASE [ReportServerTempDB] TO DISK = 'C:TempSQL2014_ReportServerTempDB_FULL_07182022.bak' WITH COPY_ONLY , COMPRESSION , STATS = 10
The second and very important step is to backup the SSRS Encryption Key. This will allow the data sources and other resources to be migrated to the new server without having to input any passwords.
From the SSRS Configuration Manager, you will navigate to the Encryption Keys tab and click Backup. When prompted, you will choose a File Location and then enter a Password you want to use to encrypt the backup.
Once that information is entered, click Ok.
The results of the encryption key backup will be displayed at the bottom of the screen. You are golden if you see the green check mark and have an SNK file in your output directory.
Finally, we will copy our backup files to the new destination SQL Server from the source server.
Phase II – Destination Server Steps
Since we are working on a new SQL Server SSRS installation, we will begin configuring SSRS from scratch with default settings.
The first thing to do is open the Report Server Configuration Manager and navigate the Database tab.
Click the Change Database button to begin the configuration.
Choose the Create a new report server database option and click Next.
At this point, we need to provide a Server Name for the Report Server databases to be installed. In our case, it is our newly created SQL Server 2019 instance that we provisioned. Once you input the proper values, click Next.
I used the default for the Database Name value and have never seen a need to change that value. Once you select the information you prefer, click Next.
For the credential information, I suggest using an Active Directory service account. However, since this is a demo on a local instance of SQL server, I am leaving this information with the default values. Click Next to proceed to the final validation of the database configuration.
Verify all the values are set as you want them and click Next to begin the installation process. If everything completes as normal, you will see the results of each step shown on the output screen. Click Finish to exit the database configuration section.
Now, if you look in your SQL Server instance and refresh the database list, you will see the newly created Report Server databases.
Several steps are required to set up the Web Service URL and Web Portal URL with the databases created. All you need to do for a default configuration is go to each tab and click Apply.
If both configurations succeed, you should have a fully functioning SSRS installation with a default web interface with no reports listed.
Phase III – Migration Steps
At this point, you have a newly installed SQL Server SSRS 2019 instance. You can see from the previous step that the web interface loads and does not have any reports deployed.
Our third and final phase will perform a database restore from the SQL 2014 server to our new SQL 2019 instance.
RESTORE DATABASE [ReportServer] FROM DISK = 'C:TempSQL2014_ReportServer_FULL_07182022.bak' WITH MOVE 'ReportServer' TO 'E:Program FilesMicrosoft SQL ServerMSSQL15.MSSQLSERVERMSSQLDATAReportServer.mdf' , MOVE 'ReportServer_Log' TO 'E:Program FilesMicrosoft SQL ServerMSSQL15.MSSQLSERVERMSSQLDATAReportServer_log.ldf' , REPLACE , STATS = 10 RESTORE DATABASE [ReportServerTempDB] FROM DISK = 'C:TempSQL2014_ReportServerTempDB_FULL_07182022.bak' WITH MOVE 'ReportServerTempDB' TO 'E:Program FilesMicrosoft SQL ServerMSSQL15.MSSQLSERVERMSSQLDATAReportServerTempDB.mdf' , MOVE 'ReportServerTempDB_log' TO 'E:Program FilesMicrosoft SQL ServerMSSQL15.MSSQLSERVERMSSQLDATAReportServerTempDB_log.ldf' , REPLACE , STATS = 10
Once the databases are restored, we will move over to the SSRS Configuration Manager to finalize the steps.
Inside Report Server Configuration Manager, choose the Encryption Keys section. Click the Restore button to begin the process.
Browse to the location of the Encryption Key that was backed up in the Phase I section. Also, you will enter the password used to encrypt the backup and click Ok.
If all goes well, you will see a success message letting you know the restoration of the encryption key has been completed.
Now for safety’s sake, I Stop and Start the SSRS service by going to the main section of the Report Server Configuration Manager.
Conclusion
We can now navigate the new SSRS website and see how the demo reports are displayed on the new server.
The post [How-To] SSRS migration appeared first on GarryBargsley.com.