locked
How to rename instance of SQL Server 2008 R2 RRS feed

  • Question

  • Hi there

    I have installed SQL Server R2 on Windows Server 2008 R2 in a development environment.  The name of the server is SQL2008 and the instance name of the SQL Server is SQL2008\DATA. Now, I wanted to change the server name to ‘earth’ so that the instance of SQL Server has to become ‘earth’, not ‘earth\DATA’.

    I can change the Windows Server to ‘earth’. But to change the instance of SQL Server, I wanted to follow proper procedure so that I don’t break the SQL Server.

    Any ideas and help will be highly appreciated.

    Regards,

    Puskal



    • Edited by Puskal Tuesday, August 14, 2012 6:43 AM
    Tuesday, August 14, 2012 6:38 AM

Answers

  • Hi Puskal, the general recommendation is that you can change an instance name if it is the default instance (http://msdn.microsoft.com/en-us/library/ms143799.aspx ), but modifying an existing named instance is not recommended without uninstall/reinstall.  Your scenario is a bit tricky going from a named instance to renamed default instance.  You may want to install a default instance before renaming the machine, copy/migrate your DBs to the new instance, then follow the steps in the link to rename your default instance.  After that, you can uninstall your old instance to clean up.

    Thanks,
    Sam Lester (MSFT)


    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    • Marked as answer by Shulei Chen Wednesday, August 22, 2012 9:53 AM
    Tuesday, August 14, 2012 6:53 AM
  • Hello Puskal,

    You would have to create the alias on every client machine, there is no kind of server based alias available, like a DNS alias for a machine.

    Start CliConfg.exe (part of MDAC, is on every machine installed) and goto tab "Alias", there you can create an aliase like "earth" pointing to "SQLServer2008\DATA"


    Olaf Helper
    Blog Xing

    • Proposed as answer by Shulei Chen Tuesday, August 21, 2012 9:35 AM
    • Marked as answer by Shulei Chen Wednesday, August 22, 2012 9:53 AM
    Thursday, August 16, 2012 4:12 AM

All replies

  • Hi Puskal, the general recommendation is that you can change an instance name if it is the default instance (http://msdn.microsoft.com/en-us/library/ms143799.aspx ), but modifying an existing named instance is not recommended without uninstall/reinstall.  Your scenario is a bit tricky going from a named instance to renamed default instance.  You may want to install a default instance before renaming the machine, copy/migrate your DBs to the new instance, then follow the steps in the link to rename your default instance.  After that, you can uninstall your old instance to clean up.

    Thanks,
    Sam Lester (MSFT)


    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    • Marked as answer by Shulei Chen Wednesday, August 22, 2012 9:53 AM
    Tuesday, August 14, 2012 6:53 AM
  • Here you are having a named instance of SQL Server... your requirement is of to have the database instance name as only 'EARTH' which means the default instance which is possible only by installing fresh database instance if its not installed

    else you can achieve some level of it by below code.

    SELECT @@servername
    
    EXEC master.dbo.sp_dropserver 'MACHINENAME\OLDINSTANCENAME'
    go
    EXEC master.dbo.sp_addserver 'MACHINENAME\newINSTANCENAME', 'local'
    go

    Tuesday, August 14, 2012 7:29 AM
  • You can do this as follow:

    1) Right-click My Computer, then choose Properties and then activate Computer Name.

    2) Type new Computer Name and then Choose OK.

    3) Launch SSMS and then execute the following code:

    USE [master]
    GO
    SELECT @@SERVERNAME AS [OLD_SERVER_NAME]
    GO
    EXEC [master]..[sp_dropserver] 'SQLServer2008\Data'
    GO
    EXEC [master]..[sp_dropserver] 'Earth\Data', 'local'
    GO
    SELECT @@SERVERNAME AS [New_SERVER_NAME]
    GO


    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    • Proposed as answer by Basit Farooq Tuesday, August 14, 2012 9:50 AM
    Tuesday, August 14, 2012 9:50 AM
  • As Simon Lester referred to (http://msdn.microsoft.com/en-us/library/ms143799.aspx) you can rename the computer name portion of a SQL Server installation.  But you cannot change the InstanceName.   If it were that easy, everyone would be doing it.  (And yes, it might be nice if it were that easy.  But it is not.)

    Why?  When the instance is installed there are many registry settings  (many, many) made during the installation.  None of those are changed by running system stored procedures.  

    In theory you could comb through the computer finding references in the registry, in configuration files, and who knows where else, to try to change each one of those references.   But if it were that easy someone would have posted instructions by now.

    RLF

    • Proposed as answer by Shulei Chen Wednesday, August 15, 2012 2:46 AM
    Tuesday, August 14, 2012 1:02 PM
  • the only way I can think you could connect to your existing named instance as 'earth' would be to define an alias using the SQL Server client utility via the Configuration Manager.  You wouldn't have to do anything on the server, but each client would need one of these.



    Thanks, Andrew


    Tuesday, August 14, 2012 1:26 PM
  • Thanks everyone for your valuable comments. Samuel Lester 's instructions seems to be more appropriate in my situation.

    By the way, I liked Andrew's idea as well. Andrew could you please give me more instruction.

    Regards,

    Puskal

    Thursday, August 16, 2012 3:05 AM
  • Hello Puskal,

    You would have to create the alias on every client machine, there is no kind of server based alias available, like a DNS alias for a machine.

    Start CliConfg.exe (part of MDAC, is on every machine installed) and goto tab "Alias", there you can create an aliase like "earth" pointing to "SQLServer2008\DATA"


    Olaf Helper
    Blog Xing

    • Proposed as answer by Shulei Chen Tuesday, August 21, 2012 9:35 AM
    • Marked as answer by Shulei Chen Wednesday, August 22, 2012 9:53 AM
    Thursday, August 16, 2012 4:12 AM
  • If you have no default instance installed, you can change the named instance port to 1433 using the SQL Server Configuration utility.  This will allow remote clients to connect using only the server name (earth), even though the instance technically remains a named one (earth\data).  You'll still need to specify the instance named for local non-tco/ip connections.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Thursday, August 16, 2012 4:21 AM
  • Thursday, August 16, 2012 12:47 PM
  • You are lucky since there's no replication or log shipping in place. Good luck.

    OOXML will surely take us to places ...

    Tuesday, March 5, 2013 12:02 PM
  • How about this for a solution... If there is no current "default" instance listening on port 1433, simply change the dynamic port reference of the named instance to 1433. This is illustrated in another thread in this forum. Search for: "sql-named-instance-port-1433"

    It would seem to better fit what you're attempting to do. If you connect using only the server name, the Instance listening on port 1433 is the one connected to. This way, you can avoid having to run through the "uninstall/reinstall" route.

    Thanks!

    Jonathan

    Thursday, August 21, 2014 1:54 AM
  • Hi Puskal

    if you want to connect using "earth"   (not "earth\earth"), just set the port of this instance to 1433. This will not change the instance name, but will permit the connection without the instance name.

    Regards,

    Nilson

    Wednesday, November 30, 2016 6:57 PM