Rename SQL Server 2008 – Single Server Senario
Posted by Brajesh Panda on January 5, 2010
Have you ever changed SQL server name? I frequently do the same in my LAB because I use “Sysprep” OS image for my lab deployment. Read my other posting to know about how to Sysprep Windows 2008 R2 Server?
SQL Infrastructure architecture can be created in multiple ways;
– All SQL Roles are running on the same single server
– SQL Roles are in different servers i.e. Database Services, Reporting Services etc are running on different servers
– Clustered SQL Instances
There are numbers of articles are available over internet. However I will recommend to visit Microsoft TechNet to find the official supported article; Over here I am going to state some simple steps for the 1st scenario. So I have to deal with SQL Database Server renaming & Re-configuration of Report Server; Over here I am using SQL 2008 with SP1.
Let’s start operation “RE-BIRTH” of my SQL Instance. Open SQL Query Manager & do some trick to rename our SQL DB Instance;
# It should result the OLD Server Name
sp_dropserver ‘OLD SERVER NAME’
# It will drop the old server name from master database
sp_addserver ‘NEW-SERVER-NAME’, ‘LOCAL’
# It will add new server name in the master database
# Don’t forget that ‘LOCAL’ parameter, else it will se the SERVERNAME to “NULL”
Re-Configure Reporting Services
– Open “Reporting Services Configuration Manger” from Start – Microsoft SQL Server 2008 – Configuration Tools
– It will open up below window having new name of the DATABASE Server. If not find/type the new name and Click on “Connect”
– After you get connected to server, you can verify Service Account, Web Service URL. Default Virtual Directory usually set to “ReportServer” onPort “80”
– Move to Database Page
– We may see the OLD Database server name! If yes, then click on “Change Database” – Select “Chose an existing report server database” – Type Database server details in next window & Click “Test Connection” – If Test is successful you can move ahead to select the reporting Database on next window & other authentication parameters.
– I usually prefer defualt NETWORK SERVICE account for service authentication purpose.
– Here is the summary page of all configuration
– Verify & Click next to create/save same configuration
– To verify your configurations please access http://servername/reportserver
– Output should be below screen