How do I rename my local SQL development server?
How to: Rename a Computer that Hosts a Stand-Alone Instance of SQL Server
Rename the computer.
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 runSELECT @@SERVERNAME
to confirm it).Run the
sp_dropserver
andsp_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.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
andweb.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).
While this sounds hard it's actually very easy.
- Rename the machine and reboot.
- Connect to SQL as a sysadmin
- Execute: EXEC sp_dropserver 'xxx'; --where xxx is the old hostname
- Execute: EXEC sp_addserver 'yyy', 'LOCAL'; --where yyy is the new hostname
- Restart the SQL Server service
- 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 ;)
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.