Force local SQL Server processes to use Shared Memory

· Uncategorized
Authors

Current setup: we have 2 instances (one default on 1433 and one named on 1500) and a number of local processes (CDC & SSIS packages). The local processes linked to the default instance use Shared Memory as connection mechanism. The local processes linked to the UA instance use TCP.

Problem: we recently got errors like “TCP Provider: An established connection was aborted by the software in your host machine.” So we want to avoid using TCP until we find a sustainable solution for the TCP issue.

Question: how can I prevent the local processes connecting to the named instance to use TCP, and force it to use Shared Memory, just like the default instance does?

Solution:

Step 1: Assure local processes are using TCP

  • Get the SPID from a local process (for example with application name = “SQLAgent – Generic Refresher”
  • Inspect the net-transport value for this SPID (“TCP” or “Shared Memory”)
SELECT [session_id]
      ,[connect_time]
      ,[net_transport]
      ,[protocol_type]
  FROM [sys].[dm_exec_connections]
 where session_id in (<SPID>);

Step 2: Disable TCP as Server network protocol for the SQL Server instance
Step 3: Restart the SQL Server instance
Step 4: Check the protocol used by a local SQL Server process

  • Use the same procedure as under step 1
  • This should be forced to use Shared memory.

Step 5: Enable TCP as Server network protocol for the SQL Server instance

  • This is required by another service that can only connect thru TCP

Step 6: Restart the SQL Server instance
Step 7: Check the protocol used by any local SQL Process.

  • This should remain Shared memory

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: