Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm a dev who inherited a mostly functioning box doing most of what I need. Except for the machine name is still that of the old dev (we name it "{username}-dt" or "{username}-lt" for ease of id on the network) and I want to rename it from old-username to my-username.

Naturally this will affect SQL as well, so I thought I would ask for more experienced advice on what I need to do before I rename my machine. I know there are some "sp_" sprocs to be run, but when do I run them? Do I need to restart my box before or after, and do I need a certain level of privilege? Will it destroy any existing windows-based auth on the box (those accounts are all AD accounts anyways)?

share|improve this question
add comment

3 Answers

up vote 6 down vote accepted

How to: Rename a Computer that Hosts a Stand-Alone Instance of SQL Server

  1. Rename the computer.

  2. Restart the computer - SQL Server will recognize the new name during startup, but the sys.servers table will still contain the old name (you can run SELECT @@SERVERNAME to confirm it).

  3. Run the sp_dropserver and sp_addserver procedures to update this table.

    According to BOL, the former requires the ALTER ANY LINKED SERVER permission and the latter - membership in the setupadmin server role; however, brief sp_helptext and Google investigation suggests that actually they both require this ALTER ... permission.

  4. Restart SQL Server and use SELECT @@SERVERNAME to verify that the previous step has been successful.


Apart from technical considerations explained in the How to: Rename a Computer ... article (e.g. computers involved in replication cannot be renamed), the thing I would worry most about is finding and changing all the connection strings:

  • the ones in app.config and web.config files,
  • the ones hard-coded in some prototype applications,
  • the ones hidden somewhere deep in configuration of SharePoint, Analysis Services and other systems,
  • the ones embedded in Excel files or Access databases).
share|improve this answer
    
So the more explicit question was a question of ordering. When do I run those? Are there any other considerations I may have missed? –  jcolebrand Feb 15 '11 at 17:25
    
@jcolebrand I updated my answer. –  Marek Grzenkowicz Feb 15 '11 at 18:25
    
Thanks @Marek ~ Much appreciated ;) ~ Fortunately mine is for my local dev machine so none of those count towards my little endeavour. Fortunately. –  jcolebrand Feb 15 '11 at 21:47
add comment

Drachenstern, rename it as you want, you'll be able to create an alias on your local machine - SQL Server configuration tool - to point you wherever you want. Or do more work (but the proper one), as the first answer tells you.

share|improve this answer
add comment

While this sounds hard it's actually very easy.

  1. Rename the machine and reboot.
  2. Connect to SQL as a sysadmin
  3. Execute: EXEC sp_dropserver 'xxx'; --where xxx is the old hostname
  4. Execute: EXEC sp_addserver 'yyy', 'LOCAL'; --where yyy is the new hostname
  5. Restart the SQL Server service
  6. Connect to SQL and execute; SELECT @@SERVERNAME;

Step six should indicate the name you put in where yyy is in step 4.. Success!

If you use Veritas/Symantec clustering you'll get very used to doing this ;)

share|improve this answer
    
See? That's why I said to trust aliases. Lazy people get faster workarounds :). –  Marian Feb 15 '11 at 21:26
    
Grr, I can't accept multiples :\ Many thanks! –  jcolebrand Feb 15 '11 at 21:49
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.