How can I create valid connection string by using shared memory protocol to connect a named instance of sql server?

Question

How can I create a valid connection string by using shared memory protocol to connect a named instance of sql server while the sql server browser is disabled?

Answer 1

If you use [servername][instance name], (local)[instance name] or LOCALHOST[instance name] from the local machine, it will use shared memory as long as shared memory is enabled (guiding through this in the UI is different in 2005 and 2008, so I don't know which version I should assist for). Unlike with SQLCMD etc. there isn't a way to tell the provider that you only want to use shared memory - if for some reason it can't use shared memory it will default to a different protocol (named pipes or TCP/IP). You can check what protocols are in use by looking at sys.dm_exec_connections:

SELECT session_id, net_transport 
  FROM sys.dm_exec_connections;

I haven't tested this with SQL Browser turned off but I think this shouldn't have any impact. FWIW, I always use TCP/IP, but I almost always avoid having applications on the local machine that need to connect to SQL Server - this is what lower-spec'd application servers are for rather than taking memory and CPU on the database server (and therefore away from SQL Server).