Upgrade and Migrate SSRS Report Server and Retain Server Name

By:   |   Comments (8)   |   Related: > Reporting Services Migration


There comes a time when you need to upgrade a SSRS report server to new hardware with the newest Windows version, newest SQL Server version and newest SSRS version. To help minimize downtime, we can do a side by side install, then do the database migrations and finally rename the servers, so the front end applications do not need to change anything such as connection strings or URLs. In this article, we walk through how you can do this for SQL Server Reporting Services.


We will walk through the steps of this process. In this example we upgrade and migrate a report server that hosts SQL Server 2017 and SSRS 2017 to a new server with the newest Windows version and SQL Server 2019 and SSRS 2019 standard editions. Most steps can be done ahead of time without affecting production use. The process should work with other versions of a report server upgrade and migration as well.

Step by Step to Upgrade and Migrate SSRS Reporting Server 2017 to 2019

For convenience in this article we will call the old server name MyServer and new server MyServerB.

Step 1 - On new server MyServerB, we will install the newest Windows version, newest database engine 2019 standard edition and the newest cumulative update. Since these are common steps frequently done by DBA, I will not describe the details here.

Step 2 - On new server MyServerB, we will download and install Microsoft SQL Server 2019 Reporting Services.

Step 3 - On new server, we will configure the reporting service. Open Report Server Configuration Manager, enter server name and click Connect.

report server configuration manager

Go through each tab on left hand menu:

  • On Service Account tab, either use a virtual account or a domain account, then click apply.
  • On Web Service URL tab, I use the default values on the screen and click apply.
  • On Database tab, click change database create a new report server database, for database server name use the default: ReportServer, then click next, next again on credentials screen, next on summary screen. This process will create the new databases ReportServer and ReportServerTempDB on the local database server.
    • Note: the reason I chose to create the new databases here the first time on the new server is that it will create automatically SSRS related database roles and permissions in master and msdb database, as well as in the two new report server databases. You can also restore databases from old server the first time, but you will need to manually add the RSExecRole and grant permissions. See here for more detail.
report server configuration manager
  • On E-mail Settings tab, fill in your FTP server and sender address, click apply.
  • On Execution Account tab, I fill in a domain account that is different with service account and hit apply.
  • On Encryption Keys tab, Subscription Keys tab, PowerBI Service tab I just use default and apply.

Now the initial reporting service configuration is completed on the new server and the service is running without any reports yet.

Step 4 - On the old server MyServer, do a backup of the two databases: ReportServer and ReportServerTempDB.

Step 5 - On the old server MyServer, open Report Server Configuration Manager, do a backup of the Encryption Keys, save to a file and give it a secure password.

report server configuration manager

Step 6 - On the new server MyServerB, restore the two databases ReportServer and ReportServerTempDB from the backups of previous step. Then change the database compatibility to 2019 in database property options.

Now if you open SSMS and run a query like this in the ReportServer database, you will see something like below:

SELECT * FROM ReportServer.dbo.Keys
query results

I want to delete the key of the old server MyServer, so that we only keep the key of the current server MyServerB.

So I run this:

DELETE FROM ReportServer.dbo.Keys
WHERE MachineName='Myserver'

You can also use the rskeymgmt.exe tool to manage and remove the key. You can find more details here. This step is important, if you don’t do it you will get an error when you open the web portal later.

Step 7 - On the new report server we will do some re-configuration of the SSRS service. This is because the database has changed.

On the database tab, click change database choose an existing reporting server database, click next Server Name: MyServerB click next in the Report Server Database, choose ReportServer click next until you finish.

report server configuration manager

Step 8 - On the Encryption Key tab, click restore, find the path of the backup file of the encryption key from the old server, fill in the password and click OK.

Now the migration is complete and you can open the web portal and test your reports. In my example the URL is http://MyServerB/reports

You can do all above steps on the new server ahead of time without the need of an outage window of the current production server.

You can repeat step 4-8 as many times as you want before outage time is required to test your reports on the new server and keep the new reports up to date.

Make New Server the Production Server

The downtime only occurs for the real migration, since we want to reuse the old server name, we need to swap the server name on both the OS and SQL Server level.

Step 9 - Swap the Windows server name and reuse the old server IPAddress. This step is usually done by Network admins.

After the server name swap, the old server MyServer is changed to MyServerC and the new server is changed to MyServer from MyServerB. The old server can still be kept for a while in case we need to do some comparison or if we need to roll back. You can also turn off the services on the old server.

Step 10  - Change SQL Server name on the new server. In SSMS connect to the new server and run this:

-- The result is: MyServerB

--drop old server
EXEC SP_DROPSERVER 'MyServerB' --old server name

--add new server, make sure local is specified.
EXEC SP_ADDSERVER 'MyServer', 'local' --new server name

--Restart SQL Server services

-- The result is: MyServer 

Step 11 - We need to reconnect the database again since the server name has been changed.

Login to new server MyServer, open Report Server Configuration Manager, you will see the server name has been changed to MyServer. Click Connect.

report server configuration manager

On the database tab, click change database and choose an existing reporting server database, then click Next.

For Server Name make sure it is MyServer. Note the server name should be changed from MyServerB to MyServer. Select ReportServer in the dropdown and click next through screens and click finish.

Step 10-11 may also need to do done on the old server in case you want to also use the old server, otherwise you can turn off the services on the old server.

Test the Final Migration

Step 12 - Go to the reporting server web portal and test your reports.

The entire migration process has been completed and we can reuse the server name without any changes on the application end. All the steps can be done ahead of time except the steps where we need to swap the server names. This greatly reduces system down time and also gives you plenty of time for testing the migration and reports.

At this time the new report server should be ready for production use.

Next Steps

Read more articles about reporting service

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Xiaowei Cao Xiaowei Cao has been a SQL DBA and developer for 15+ years, supporting varies environments and has a passion for all aspects of SQL server including the DB engine, SSIS, SSRS, and SSAS.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, April 12, 2022 - 11:16:25 AM - Xiaowei Cao Back To Top (89996)
For upgrading SQL server SSRS 2012 to 2017, you need SQL server 2012 SP2 or later. There should be no much difference regarding the migration. The jobs and schedules for subscriptions should also be restored once you migrate and restore the database. Sometimes you need to restart the SQL agent for the job to show up.

Monday, April 11, 2022 - 7:55:59 PM - Kevin S Back To Top (89992)
I just found this article and have a couple of questions -- we need to migrate from a 2012 SSRS server to one running 2017 version.

1) Are there no differences in the ReportServer schema between the 2012 and 2017 that cause problems when you restore from the older version's backup and reference it with the new version?
2) Regarding subscriptions/schedules, while these references in the ReportServer database itself would carry over to the new server via restore, when you initially create an SSRS subscription a corresponding SQL Agent job/schedule is created (in msdb). I'm not sure if there is a way to migrate all of those existing jobs cleanly from the 2012 instance to the new server running 2017. Perhaps there is a way to re-trigger the job creation that I haven't thought of.

Tuesday, May 4, 2021 - 7:21:54 PM - Jonathan M Back To Top (88643)
Thanks, a fabulous document. The extra step I did with the last SSRS migration was to set up a DNS Alias (CName) of SSRS that points to the server so users browse to a reporting link based on SSRS rather than the server name. This means I don't need to rename the servers
and just need to update the Alias to point to the new server and all favourites should continue to work.

Tuesday, March 16, 2021 - 9:31:18 PM - Mike Lintel Back To Top (88422)
This is a very helpful guide. Thanks for laying out all the steps - easy to follow.

Tuesday, March 16, 2021 - 2:54:27 PM - Xiaowei Back To Top (88418)
Thanks, good recommendation, I will look into it.

Tuesday, March 16, 2021 - 2:12:39 AM - Vineet Goyal Back To Top (88408)
Very nice article Xiaowei Cao, can you please share the Migration document for SSIS as well.

Monday, March 15, 2021 - 2:43:14 PM - Xiaowei Back To Top (88399)
Yes, the reporting service upgrade should be the same, but if your database engine is at SQL server 2012, the upgrade path should be at least SP4 of SQL server 2012 to 2019.

Monday, March 15, 2021 - 10:24:47 AM - Ken M. Back To Top (88398)
Will this work for upgrading 2012 to 2019 or is that more difficult? Thank you.

get free sql tips
agree to terms