Wondering how to Configure Reporting Services after Renaming SQL Server Instance? We can help you!

Often after renaming SQL Server Instance that has SQL Server reporting services, the reporting services stop working if we simply change the SQL Server instance name of the report server.

We will have to reconfigure the reporting services to fix this. Here at Bobcares, we often fix similar issues as a part of our Server Management Services.

Today let’s see the steps for renaming SQL server instance along with the steps our Support Engineers follow to configure reporting services.

Configuring Reporting Services after Renaming SQL Server Instance

Before going into the steps for configuring reporting services we will see the steps for renaming SQL server instances.

Steps for Renaming SQL Server Instance

SQL Server instance name and other system metadata is stored in sys.servers system object.

If we change the SQL server name, we have to update details in the system metadata to reflect the new name.

If we change the name of our database server we have to update the name of the default SQL Server instance as well in sys.servers system table.

The steps to follow for renaming SQL server is given below:

1. First, we will drop the existing SQL Server instance name, then we will add a new name as we wish using the following command:

--Drop existing name
sp_dropserver [old_Instance_name];
GO

--Update New name
sp_addserver [new_Instance_name], local;
GO

If we install a named instance on our database server we can run the below command to change the SQL Server Instance name.

sp_dropserver [old_name\instancename];
GO

sp_addserver [new_name\instancename], local;
GO

Finally, we will restart SQL Server services post running the above commands to reflect the changes made.

[Need assistance to rename your SQL Server? We are happy to help you!]

Steps to Configure Reporting Services

The steps to make reporting services accessible after renaming the SQL server is given below:

1. First, launch the SQL Server Reporting Services Configuration tool.

2. Then connect to the report server which uses the report server database on the renamed server.

3. Next we will take the Database Setup page.

4. And Enter the SQL Server name in Server Name and then click Connect.

5. When Reporting Services is configured using the server name, we must update the database connection information as well.

6. Click Apply and close the SSRS Configuration tool.

7. Then open the RSReportServer.config in a text editor and modify the UrlRoot setting to reflect the new server name.

8. After that we will update the UrlRoot setting so that subscriptions continue to deliver reports as expected.

9. We can also modify the ReportServerUrl setting to reflect the new server name it can be ignored if not set previously.

[Need assistance to configure reporting services? We are happy to help you!]

Some Common Errors and their fix

The following are some of the errors encountered often:

An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database.
(rsReportServerDatabaseError) Get Online Help Invalid object name ‘ReportServerTempDB.dbo.TempCatalog‘. Could not use view or function ‘ExtendedCatalog’ because of binding errors.

This can be fixed with the following steps:

1. First, backup the existing reporting server databases.

2. Then stop SQL Server Reporting Services

3. Rename the report server database as required.

4. Add a script with drop and create functionality.

6. Then search for the old report server tempdb database and replace it with a new report server tempdb database.

7. Execute the script and start SQL Server reporting services.

Conclusion

To conclude, we saw how to rename the SQL server instance. Also we saw the steps that our Support Techs follow to configure reporting services after renaming the SQL server.