Are you trying to Configure Reporting Services after Renaming SQL Server Instance?

This guide is for you.

Sometimes 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.

Here at Ibmi Media, we regularly help our Customers to reconfigure the reporting services as part of our Server Management Services.

In this context, we shall look into the the steps for renaming SQL server instance as well as how 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 have changed 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 have installed 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 urgent assistance to rename your SQL Server? We are happy to help you!]

Steps to Configure Reporting Services in SQL server?

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 urgent assistance to configure reporting services? We are happy to help you!]

Some Common SQL 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.

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

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