I use this blog to post ideas and experiences about software development with the goal to help all the technology communities around the world.

1/18/15

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)

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.