How do I rename my local SQL development server?

Question

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

Answer 1

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

Answer 2

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.

Answer 3

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