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)?


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).
|improve this answer|||||

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 ;)

|improve this answer|||||

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.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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