SQL Server - sys.servers

Linked servers

Linked servers are recorded in sys.servers with is_linked = 1:

select
   server_id,
   name,
   product,
   provider,  -- OLE DB provider
   data_source,
   is_remote_login_enabled,
   modify_date
from
   sys.servers
where
   is_linked = 1;

Github repository about-MSSQL, path: /administration/schemas/sys/objects/views/servers/linked-servers.sql

TODO

Dropping and adding servers

Prior to SQL Server 2014, servers could be dropped and added with sp_dropserver and sp_addserver:

sp_dropserver 'srv_foo'
go

However, starting with SQL Server 2014, these statements result in the error message *Feature "sp_addserver" is no longer supported. Replace remote servers by using linked servers.*

Adding linked servers

exec sp_addlinkedserver
    @server     = 'SrvName\InstName',
    @srvproduct = 'SQL Server';
go

exec sp_addLinkedSrvLogin
    @rmtSrvName  = 'SrvName\InstName',
    @useSelf     =  false,
    @rmtUser     = 'theGreatToby',
    @rmtPassword = 'ENTER PASSWORD'
go

select * from [SrvName\InstName].db.sch.tab;

sp_linkedservers

sp_linkedservers also shows basic information about linked servers