I’ve been working on a project that involves automating tasks on SQL Server installs. Things got a bit messy when working with multiple instances on the same host.
To automate sql scripts via a VB.NET program, I ended up using shared memory connections. The code will always run locally on the host where the instances reside, so no problem there. I learned the following about SQL Server connection string behavior:
1. This will always use the default instance with a shared memory connection. Parentheses are required.
Data Source=(local); Integrated Security=True
2. This will allow a connection to a named instance via shared memory. Note there are no parentheses.
Data Source=HOSTNAME\NAMEDINSTANCE; Integrated Security=True
3. This will force a connection over TCP/IP on the port specified, provided the SQL Server Network Configuration has been set to the port indicated.
Data Source=HOSTNAME\NAMEDINSTANCE,port; Integrated Security=True
The above connection strings can be used in data access code in VB.NET and other languages.
Want to force a shared memory connection from sqlcmd? You can append the prefix lpc:
sqlcmd -S lpc:HOSTNAME\NAMEDINSTANCE -E
sqlcmd -S HOSTNAME\NAMEDINSTANCE -E
does the same thing when executed from the local host. When executed remotely, you likely will need the port to appended after NamedInstance.
For other SQL Server protocols such as Named Pipes, there must be additional abbreviations, but I haven’t dug into that…
Lastly, one can tell from within SQL Server what protocols are used by what sessions. The view sys.dm_exec_connections shows that, specifically the net_transport column. See the below query which joins to sysprocesses.
select d.net_transport, s.spid, s.program_name, s.nt_username, s.hostprocess, s.lastwaittype, d.client_tcp_port, d.protocol_type, d.protocol_version from sysprocesses s inner join sys.dm_exec_connections d on s.spid = d.session_id