Switch between named SQL Server instances without changing the connection string
July 31, 2010 Leave a comment
SQL Server allows installing multiple copies and multiple versions on the same machine as “named instances”. These are accessed locally with the name “(local)\<instance>” or “.\<instance>” or “localhost\<instance>” where <instance> is the name of the named instance. You can also install exactly one server as the “default” instance which doesn’t require a name. You just connect to (local).
If you want to switch your app between these, you have to change the connection string to the different instance names. It also means that everyone on your team has to use the same instance names or go through some other contortions to isolate the configuration file from source control.
If you aren’t using a default instance, you can make one of the named instances respond to the syntax of a default instance in a connection string. The trick is to use the SQL Configuration Manager to enable TCP/IP on one of your named instances and further configure that instance to accept connections on TCP port 1433.
When you want to switch between instances, you change which of your SQL Server named instances is listening on TCP 1433.