ARTICLE: Linking SQL Server databases together via linked servers

This entry describes the process of linking two remote SQL Server databases via a remote SQL Server client. The process is known in SQL Server as Linked Servers. Once databases are linked, tables in the source database server can be accessed on the destination database server.

Assumptions

• The first remote server shall be referred to as source and second remote server shall be referred to as destination. Once the database linking has been completed, the source database tables can be accessed when connected to the destination database.
• That the source, destination and client database software are installed correctly and in working order.
• Readers have a basic understanding of SQL and the dialects used by the above vendor.
• SQL Server command usages coloured blue, examples coloured green.

Environment

• Source and destination server version / platform: 64-bit SQL Server 2008 Developer Edition on 64-bit Windows Server 2008 R2. 
• Client version / platform: SQL Server 2008 Client on 64-bit Windows 7 Professional.
• Settings for SQL Server are added / modified via the console in SQL Server 2008 Management Studio.
• The following is to be used in a development environment with no thought give to database security between servers - this is a factor which bears consideration in all production environments and even some development environments.

SQL Server - Linked Servers

1. Log into the destination server via the SQL Server 2008 Management Studio with user of sufficient privileges.
2. In the console, execute the following to create the linked server.
   Usage:
       EXEC sp_addlinkedserver
           @server='[unique_name_of_linked_server]',
           @datasrc='[linked_server_hostname]',
           @srvproduct='',
           @provider='SQLNCLI'
   Example:
       EXEC sp_addlinkedserver
           @server='SRCDBLINK',
           @datasrc='SRCDBSRV',
           @srvproduct='',
           @provider='SQLNCLI'
3. In the console, execute the following to create the login for the above linked server.
   Usage:
       EXEC sp_addlinkedsrvlogin
           @rmtsrvname='[unique_name_of_linked_server]',
           @useself='false',
           @locallogin='[domain]\[username]',
           @rmtuser='[username]',
           @rmtpassword='[password]'
   Example:
       EXEC sp_addlinkedsrvlogin
           @rmtsrvname='SRCDBLINK',
           @useself='false',
           @locallogin='CORP\sqlsuser',
           @rmtuser='sqlsuser',
           @rmtpassword='sqlsuser1'
4. In the console, execute the following to update for RPCs (remote procedure calls) rpc and rpc out for the link.
   Usage:
        EXEC sp_serveroption '[unique_name_of_linked_server]', 'rpc', 'true';
   Example:
       EXEC sp_serveroption 'SRCDBLINK', 'rpc', 'true';
   Usage:
        EXEC sp_serveroption '[unique_name_of_linked_server]', 'rpc out', 'true';
   Example:
       EXEC sp_serveroption 'SRCDBLINK', 'rpc out', 'true';
5. Finally, to access the source database table via a connection to destination database, use the client to connect to the destination database execute the following.
   Usage:
       SELECT * FROM [[database_link]].[[database_name]].[dbo].[[table]].
   Example:
       SELECT * FROM [SRCDBLINK].[MyDatabase].[dbo].[MyTable]