Challenge of SSRS server upgrade/migration to a new server using the same name

Question

We are going to upgrade all SQL servers and SSRS from 2017 to 2019.
This is going to be side-by-side upgrade. The hard part is we want to reuse the server name after migration. Also db engine and SSRS service will be the same host.

It means: to build a new server that hosts 2019 database engine and SSRS report service. then migrate db and reports, then rename the new server the same name of the old server. Old server will be renamed to something old then decomission after a week.

I plan to do in these steps, the challenge part is when we want to swap and reuse the servername.

1. Install the SQL server engine and SSRS service on this new server ServerA with newest release.
2. Backup the old report dbs and encryption key on old server MyServer ,stop SSRS service there and restore on the new server.
3. do reporting server configuration and restore encryption key on new server
4. It is possible that I got a scale out error, I may need to go to key table to delete the key of original server.
5. Test reports
6. Swap OS server name,rename MyServer to ServerC and Rename ServerA to MyServer.
   What to do next, here is what I plan to do but need your help:
7. On the old server, I will go to db engine using sp_drop sever and sp_add server to change the SQl sever name, then reconnect database to ServerC. Do I need to restore encryption key again or not since I am on the same sever?
8. On the new server, I rename the SQL server name from its original name ServerB to Myserver and reconnect the datatabase from report configuration manager, do I need to restore encryption key again?
9. Is there going to be another SSRS instance showing up in key table?

Thanks,

Answer 1

Hi @Anne ,

You may access the web urls for a try to see if you could open the report server and run the reports.

If there is something wrong, you could restore the key for help.

Reporting Services uses encryption keys to secure credentials and connection information that is stored in a report server database.

We'd suggest that you restore the encryption for ensure cause the restore action only takes a little time.

More details for your reference:

https://learn.microsoft.com/en-us/sql/reporting-services/report-server/rename-a-report-server-computer?view=sql-server-ver15

https://learn.microsoft.com/en-us/sql/reporting-services/install-windows/migrate-a-reporting-services-installation-native-mode?view=sql-server-ver15

Regards，
Zoe

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

What can I do if my transaction log is full?--- Hot issues November

How to convert Profiler trace into a SQL Server table -- Hot issues November

Answer 2

I managed to do some testing on a sandbox first.
On the sand box restore the report databases from my old prod server to this new sandbox server.
Then restored the encryption key, and delete the old key in the key table on the new server.

Now I can see the reports Ok in the web portal and also the subscription related job showing in SQL agent.
All is good now.
What I cannot test now is for the server name change.
For next step we will do is to change the new server computer name the same name as the old production server. The server name will be changed by our network engineer, then I will change SQL server name at the local sql server using sp_drop_server and sp_add_server. And I also need to reconnect the database point to new server name and databases. then restore the encrypted key again.

My question is in below picture after he changed the server name, will the highligted part be automatically change to the new server name, or it still is the old computer name?

Also we want to rename the old computer and its reporting service too so that we can keep it a little while and to compare some reports if needed with the new server reports.

Answer 3

Thank you!

So when you say "After you rename the server name, when you open the SSRS configuration manager it will prompt to connect to the server which you may choose the renamed server."
Is the prompt showing up the first thing I open the configuration manager? I know on the database tab I need to reconnect to the new renamed SQL server instance name and database, but how about the highlighted part on above screen, when I first open the report configuration manager, does it automatically change to the new computer name or it wait after I am done with the database tab?
This is something hard to test for I cannot rename the server name and has to wait the network people change it.

The other approach I am thinking is: first stop the old server reporting service, and then the network people change the computer name first, then I install SSRS service, create a new reporting db, this will add roles to the master and msdb dbs. And then restore the databases and encryption key that are from old server. This way I don't need to rename the SQL instance name and reconnect, but I don't know if it will work or not, since the restored db has the same server name and the key as the old server name.

Answer 4

Thanks I tried that and I see it first thing pops up is to let me find the server name and connect, I guess from here I can put in the new server name, and then in database tab to reconnect to databases and then restore the encrypted key.

Hopefully that will work, this all will has to be tested after the server name change