In the real world scenarios, you will not always work with a single database or single type of database while building a new site or providing maintenance for existing web applications. Linked Server can be useful in this kind of situation.

Before digging into its usage and when to use it, let’s first look into what is a linked server?

Linked Server is one kind of server object provided by the Microsoft SQL Server Database Engine. Linked server, allows you to connect a database with another database on the same server instance or remote servers. Using linked servers, you can query over multiple databases to perform any of the below operations such as,

• Fetch data
• Insert 
• Update
• Delete
• Commands & transactions

Microsoft SQL Server provides an easy interface to create and configure linked servers of different server types. Below are the server types supported by MS SQL linked server.

• SQL Server
• Oracle 
• ODBC
• Access

You can configure a linked server, either by using Microsoft SQL Server Management Studio UI interface or the same can be done via executing a query. In both cases, you first have to connect to the Microsoft SQL Server instance.

Components

There are 2 main components of a Linked server:

• OLEDB Provider

  OLEDB provider is a DLL that manages and interacts with a provided data source. Apart from databases, OLEDB provides support for text files, spreadsheets and the result of full-text content searches.

• OLEDB Datasource

  OLEDB data source is responsible for identifying the database that can be accessed through the OLEDB.

How to use it?

1) Setting Up Linked Server

There are 2 ways to set up a linked server:

A. Using Object Explorer

Steps:

• Open SQL Management Studio and connect to the database server instance.
• Expand Server Objects -> Linked Servers & then click on New Linked Server.
  

In the New Linked Server Dialog box, there will be 3 tabs:

i) General

• Linked Server
  Choose any name for your new linked server.
  
• Server Type
         SQL
        Choose SQL if the other DB you want to connect is SQL DB.
• Other Data Source
         Provider
        Select an OLEDB data source from the list.

         Product Name
        Enter the product name of the OLE DB data source here.

         Data Source
        Enter the name of the data source as interpreted by the OLEDB provider. If you are
        using SQL Server, provide the SQL server instance name.

         Provider String

  
  

         Catalog
        Type the database name which you want to connect with the OLEDB provider.

ii) Security
     From the Security tab, you can provide login user credentials.

• Login not defined Options
  
  • Not be made
    As the name suggests, selecting this option will deny access to the remote server for all logins except defined in the remote server login mappings.
  • Be made without using a security context
    Selecting this option will specifically tell a linked server to not pass the security context and try as anonymous for all logins not defined in the remote server login mappings.
  • Be made using login’s current security context
    Specify that connection to be made with the current login context to the remote server when login is not defined in the remote server mapping.
    
    With SQL Server authentication, it will send username and password to the remote server. In this case, the same username and password must exist on both a local and remote server. 

    With Windows Authentication, your windows credentials will be used to connect to the remote server.

  • Be made using this security context
    Specify that connection to be made with login user and password provided in the boxes when a login is not defined in the remote server mapping. 

    In this case, remote server login must be a SQL server authentication on a remote server.

  iii) Server Options
  From the Server Options tab, you can turn on/off specific settings.

  

  B. Using a Stored Procedure

  Steps:

  • Open SQL Management Studio and connect to the database server instance.
  • From the toolbar at the top, click New Query.
  • Copy below query and replace argument with square brackets (e.g. [LinkedServerName] ) with your server details

  EXEC master.dbo.sp_addlinkedserver @server=N'[LinkedServerName]’, @srvproduct=N'[DataStore]’, @provider=N’MSDASQL’, @datasrc=N'[DSNName]’

  • For remote servers with authentication use the below query.

  EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'[LinkedServerName]’, @useself=N’False’, @locallogin=NULL, @rmtuser=N'[UserName]’, @rmtpassword=N'[Password]’

  Arguments

  • @server
    Choose any name for your new linked server.
  • @srvproduct
    Type of data store that you are linking to SQL Server
  • @provider
    OLEDB provider name
  • @datasrc
    Server instance name
  • @rmtuser
    Remote login username
  • @rmtpassword
    Remote login password

  2)Executing Query Over Linked Server

  To read or fetch data from a table or view of any linked server, use four-part names to refer to an object on a linked server.

  SELECT * FROM [TESTNorthwind].Northwind.dbo.Employees

  Arguments

  • Linked Server Name
    [TESTNorthwind] is a linked server name in the above example. Square brackets are not mandatory, used as a precautionary measure in the above query.
  • Database Name
    Northwind is the database name in the above example.
  • Schema
    dbo is a schema, schema can be sys or dbo or any other custom schema name.
  • Object name
    Employees is a table name in the above example. It can be a table or view name etc.

  3) Executing Stored Procedure Over Linked Server

  Executing stored procedure over a linked server is the same as executing query as shown in #2 above.

  By default, a linked server can not execute stored procedure calls. To allow, right-click on the Linked Server -> Go to Properties -> Server Options -> RPC Out -> Select True & click on OK.

  Once enabled, now you can use the same syntax which you generally use to execute stored procedures. The only thing which will differ is the four-part names to refer to an object on a linked server. In our example, the object will be stored procedure name.

  EXEC [TestNorthwind].Northwind.dbo.GetAllEmployees

  The same syntax you generally use to execute stored procedures can be used when using a linked server. The only thing which will differ is the four-part names to refer to an object on a linked server. In our example, the object will be stored procedure name.

  4) Dropping Linked Server

  To remove a server from the list of known remote and linked servers on the local instance of SQL Server use below query.

  sp_dropserver ‘TESTNorthwind’, ‘droplogins’;

  Arguments

  • server
    Linked server name to be removed. A required argument, which must be passed when trying to drop a linked server.Needless to say, the specified linked server name must exist when trying to execute a drop query.
  • droplogins
    Indicates that remote and linked server logins to be removed when droplogins is specified.
    

  When To Use Linked Servers?

  The Linked server enables you to implement distributed databases where you can fetch / update data from other databases. They offer benefits when you have to manually load data from one database to another or want to access via code for any custom applications.

  Arguments

  • Ability to access data outside the SQL server.
  • Ability to access data from various data sources.
  • The ability to issue distributed queries, updates, commands, and transactions on distributed databases.

  Limitations

  Every method or concept has its limitations. With that said, few limitations need to be kept in mind when using a linked server.

  Arguments

  • The truncate command is not allowed when using a linked server.
  • Table-valued or scalar functions are not allowed when using a linked server.

  Conclusion

  Before deciding whether to use a linked server or not, do a brief or thorough check of your requirements, database size, and other aspects. Linked servers can also turn out low in performance when using large tables and lots of joins between local and remote servers. On the other hand, linked servers will be a very helpful and life-saving tool to connect distributed databases, and provide a facility to do bulk insert/update or fetching data from different databases. When building a large scale application with .NET Core or MVC with distributed databases, it will be very useful and a lot faster compared to SSIS in some scenarios.