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