1/18/15

Use Sql Server named instance as local to avoid changing the connection strings

When working on a team project, we often come across the issue that the database connection string in the configuration file uses the default instance name, but our development database is actually using a named instance (see below)
Default or Named Instance
Default Instance
Named Instance
Localhost
localhost\sqlexpress

Some developers just create a default instance and continue on their work. Others would just change the configuration file or have a local copy of the configuration file to match their environment and move on.
There is another approach that we can use which would use less resources (in the case of another instance), and it is a lot more convenient than having to manage multiple configuration files. We can use named pipes configuration to change the pipe name from the instance name to the default instance.
Named Pipes Configuration
When connecting to the default instance, SQL Server uses the default pipe name of "\.\pipe\sql\query". A named instance uses a different pipe name as listed below:
Pipe Name (Default)
Pipe Name (Instance)
\\.\pipe\sql\query
\\.\pipe\MSSQL$SQLEXPRESS\sql\query

We can use SQL Server Configuration Manager to first enable the named pipes setting and update the pipe name. This can be done as follows:
Select SQL Server Network Configuration (see pic below for details)
  • Click protocols for (Instance Name)
    • Double click on Named Pipes
      •             Set Enabled to Yes
      •             Update the pipe name
      •             Apply the changes.
      •             Restart the SQL Server service - SQL Server (SQLEXPRESS)


Use Sql Server named instance as local

After making those changes, we can try to connect to our database using SQL Server Management Studio. We can try to connect to the database with both (local) and (local)\express for server name, and both connections should be successful.

Thanks for reading.

0 comments :

Post a Comment

What do you think?