Changing SQL Server Instance Name Using a SQL Server Alias

Mostly SQL Server developers have installed default instances (local) but some developers have used named instances, such as <My Machine>\SQLStd using the same alias on all developer machines.

Suppose we have an application that has specified a database connection but it can't or doesn't need to change. But I want to move the database to a new server. How can I do this simply, without breaking it, using the same connection.

By using an alias, you can use the same database connection. There are the following two simple methods to create an alias:

  1. SQL Server Configuration Manager
  2. SQL Server Client Network Utility

1. SQL Server Configuration Manager
 
Start -> All Programs -> Microsoft SQL Server2012 -> Configuration tool -> SQL Server Configuration manager.

SQL Server Configuration manager
 
Now expand the SQL Native Client 11.0 Configuration folder, there is a sub-folder called  Aliases, right-click Aliases and choose New Aliases.

New Aliases
 
Now you can specify the new instance name that you want for the application.

new instance name
 
You can see the new alias list in the right pane.

new alias list
 
Now test the new connection.

New connection
 
If the alias is pointing correctly to the new location then a successful connection should be established.

In SSMS, I was opening a new connection for Object Explorer.

Object Explorer

2. SQL Server Client Network Utility
 
If you haven't installed the SQL Server client tools, you can still create an alias using the SQL Server Client Network Utility. This has come installed automatically on every operating system from Windows 2000 on.

Use the following to use the network utility:
 
Start -> Run then type "cliconfg.exe" then click OK.

Client Network
 
You can see in the following screen that aliases created by the SQL Server Configuration Manager can be seen by the SQL Server Client Network Utility.

SQL Server Client Network Utility
 
Now create a new alias using the network utility then click on the Add button. This will bring up a new dialog window as you can see in the following screen.

window
 
Click the OK button to create the new Alias.
 
I was opening a new connection for Object Explorer.

connection for Object Explorer