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?
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”)
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