How to Add a Linked Server

Adding a Linked server can be done by either using the GUI interface or the sp_addlinkedserver command.

Adding a linked Server using the GUI

There are two ways to add another SQL Server as a linked server.  Using the first method, you need to specify the actual server name as the “linked server name”.  What this means is that everytime you want to reference the linked server in code, you will use the remote server’s name.  This may not be beneficial because if the linked server’s name changes, then you will have to also change all the code that references the linked server.  I like to avoid this method even though it is easier to initially setup.  The rest of the steps will guide you through setting up a linked server with a custom name:
To add a linked server using SSMS (SQL Server Management Studio), open the server you want to create a link from in object explorer.

1. In SSMS, Expand Server Objects -> Linked Servers -> (Right click on the Linked Server Folder and select “New Linked Server”)

Add New Linked Server

2. The “New Linked Server” Dialog appears.  (see below).

Linked Server Settings

3. For “Server Type” make sure “Other Data Source” is selected.  (The SQL Server option will force you to specify the literal SQL Server Name)
4. Type in a friendly name that describes your linked server (without spaces). I use AccountingServer.
5. Provider – Select “Microsoft OLE DB Provider for SQL Server”
6. Product Name – type: SQLSERVER (with no spaces)
7. Datasource – type the actual server name, and instance name using this convention: SERVERNAMEINSTANCENAME
8. ProviderString – Blank
9. Catalog – Optional (If entered use the default database you will be using)
10. Prior to exiting, continue to the next section (defining security)

Define the Linked Server Security

Linked server security can be defined a few different ways. The different security methods are outlined below.  The first three options are the most common:

Option Name	Description
Be made using the login’s current security context	Most Secure. Uses integrated authentication, specifically Kerberos delegation to pass the credentials of the current login executing the request to the linked server. The remote server must also have the login defined. This requires establishing Kerberos Constrained Delegation in Active Directory, unless the linked server is another instance on the same Server.  If instance is on the same server and the logins have the appropriate permissions, I recommend this one.
Be made using this security context	Less Secure. Uses SQL Server Authentication to log in to the linked server. The credentials are used every time a call is made.
Local server login to remote server login mappings	You can specify multiple SQL Server logins to use based upon the context of the user that is making the call.  So if you have George executing a select statement, you can have him execute as a different user’s login when linking to the linked server.  This will allow you to not need to define “George” on the linked server.
Not be made	If a mapping is not defined, and/or the local login does not have a mapping, do not connect to the linked server.
Be made without using a security context	Connect to the server without any credentials.  I do not see a use for this unless you have security defined as public.

1. Within the same Dialog on the left menu under “Select a Page”, select Security
2. Enter the security option of your choice.

Linked Server Security Settings

3. Click OK, and the new linked server is created

SQL Server SPID – What is it?

A SPID in SQL Server is a Server Process ID. These process ID’s are essentially sessions in SQL Server. Everytime an application connects to SQL Server, a new connection (or SPID) is created. This connection has a defined scope and memory space and cannot interact with other SPIDs. The term SPID is synonymous with Connection, or Session.

Viewing SPIDs

In order to view all the connections in SQL Server execute the following query.

SELECT * FROM sys.dm_exec_sessions

From here we see a session_id shown in the left hand column. This is also known as the SPID.
To find the SPID for your current execution window run this.

SELECT @@SPID

Connection Settings

Each SPID can have it’s own connection settings. Connection settings can be defined by the connection string or the default values for the SQL Server instance, so if two SPIDs come in through the same connection string, then they will also have the same connection settings. This is important because execution plans that are generated for queries are dependent on the connection settings. If two different SPIDs come in with different connection settings, then they will not use the same execution plan defined for a SQL statement. In order to view the connections settings execute the SQL Statement shown above.

Transaction Isolation

SPIDs can have their own transaction isolation levels defined globally. When set within a connection, all proceeding executions maintain the same transaction isolation. For more on isolation levels, view this

Temp Table sharing

Temp tables that are created within a SPID are accessible from any proceeding execution in that SPID. This is how separate stored procedures can share the temp table because any temp table created in a spid’s session is global to the session. This comes in useful when sharing data between stored procedures.

Blocking

Because SPIDs define an atomic operation and are independent, they can often compete with each other depending on their Transaction Isolation levels, the objects they are accessing, and the operation they are performing. The quickest way to find blocking is to use the sp_who2 procedure.

SPID Status

Because a SPID is defined as a connection, it is not always running (or executing). In order to find the status of the SPIDs, execute the following:

SELECT

    SPID                = er.session_id

    ,STATUS             = ses.STATUS

    ,[Login]            = ses.login_name

    ,Host               = ses.host_name

    ,BlkBy              = er.blocking_session_id

    ,DBName             = DB_Name(er.database_id)

    ,CommandType        = er.command

    ,SQLStatement       = st.text

    ,ObjectName         = OBJECT_NAME(st.objectid)

    ,ElapsedMS          = er.total_elapsed_time

    ,CPUTime            = er.cpu_time

    ,IOReads            = er.logical_reads + er.reads

    ,IOWrites           = er.writes

    ,LastWaitType       = er.last_wait_type

    ,StartTime          = er.start_time

    ,Protocol           = con.net_transport

    ,ConnectionWrites   = con.num_writes

    ,ConnectionReads    = con.num_reads

    ,ClientAddress      = con.client_net_address

    ,Authentication     = con.auth_scheme

FROM sys.dm_exec_requests er

OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st

LEFT JOIN sys.dm_exec_sessions ses

ON ses.session_id = er.session_id

LEFT JOIN sys.dm_exec_connections con

ON con.session_id = ses.session_id

A Better sp_who2 using DMVs (sp_who3)

The following code generates the same information found in sp_who2, along with some additional troubleshooting information. It also contains the SQL Statement being run, so instead of having to execute a separate DBCC INPUTBUFFER, the statement being executed is shown in the results.
Unlike sp_who2, this custom sp_who3 only shows sessions that have a current executing request.
What is also shown is the reads and writes for the current command, along with the number of reads and writes for the entire SPID. It also shows the protocol being used (TCP, NamedPipes, or Shared Memory).

CREATE PROCEDURE [dbo].[sp_who3]

AS

BEGIN

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT

    SPID                = er.session_id

    ,BlkBy              = er.blocking_session_id     

    ,ElapsedMS          = er.total_elapsed_time

    ,CPU                = er.cpu_time

    ,IOReads            = er.logical_reads + er.reads

    ,IOWrites           = er.writes   

    ,Executions         = ec.execution_count 

    ,CommandType        = er.command       

    ,ObjectName         = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) 

    ,SQLStatement       =

        SUBSTRING

        (

            qt.text,

            er.statement_start_offset/2,

            (CASE WHEN er.statement_end_offset = -1

                THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2

                ELSE er.statement_end_offset

                END - er.statement_start_offset)/2

        )       

    ,STATUS             = ses.STATUS

    ,[Login]            = ses.login_name

    ,Host               = ses.host_name

    ,DBName             = DB_Name(er.database_id)

    ,LastWaitType       = er.last_wait_type

    ,StartTime          = er.start_time

    ,Protocol           = con.net_transport

    ,transaction_isolation =

        CASE ses.transaction_isolation_level

            WHEN 0 THEN 'Unspecified'

            WHEN 1 THEN 'Read Uncommitted'

            WHEN 2 THEN 'Read Committed'

            WHEN 3 THEN 'Repeatable'

            WHEN 4 THEN 'Serializable'

            WHEN 5 THEN 'Snapshot'

        END

    ,ConnectionWrites   = con.num_writes

    ,ConnectionReads    = con.num_reads

    ,ClientAddress      = con.client_net_address

    ,Authentication     = con.auth_scheme

FROM sys.dm_exec_requests er

LEFT JOIN sys.dm_exec_sessions ses

ON ses.session_id = er.session_id

LEFT JOIN sys.dm_exec_connections con

ON con.session_id = ses.session_id

CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt

OUTER APPLY

(

    SELECT execution_count = MAX(cp.usecounts)

    FROM sys.dm_exec_cached_plans cp

    WHERE cp.plan_handle = er.plan_handle

) ec

ORDER BY

    er.blocking_session_id DESC,

    er.logical_reads + er.reads DESC,

    er.session_id

END