Creating Linked Servers in SQL Server Management Studio (SSMS)

Vijay Kashyap Algo Trading Expert

Linked Servers in SQL

Introduction:

Linked Servers are a powerful tool in SQL Server that allow you to connect to other database servers, such as Oracle, MySQL, or PostgreSQL, and access their data from within SQL Server. This can be especially useful when you need to combine data from multiple data sources, or when you need to perform distributed queries. In this blog post, we will discuss how to create Linked Servers in SQL Server Management Studio (SSMS) and provide examples to illustrate the process. 

What is a Linked Server in SSMS? 

  • A Linked Server is a database object that allows you to access data from other database servers within SQL Server. When you create a Linked Server, you specify the name of the remote server, the security context to use when connecting to the remote server, and the type of data source that you are connecting to. 

Creating a Linked Server in SSMS 

To create a Linked Server in SSMS, follow these steps: 

  1. Open SSMS and connect to the SQL Server instance where you want to create the Linked Server. 
  1. Navigate to the “Server Objects” folder in Object Explorer and right-click on “Linked Servers”. Select “New Linked Server” from the context menu. 
  1. In the “New Linked Server” dialog box, specify the following information: 
  • Linked server: The name of the Linked Server that you want to create. 
  • Server type: The type of data source that you are connecting to (e.g., SQL Server, Oracle, MySQL, etc.). 
  • Provider: The OLE DB provider that you want to use to connect to the remote server. 
  • Data source: The name of the remote server that you want to connect to. 
  • Catalog: The default catalog (database) to use when connecting to the remote server. 
  1. Click on the “Security” page and specify the security context to use when connecting to the remote server. You can use either the current security context (i.e., the credentials of the user who is creating the Linked Server) or a specific set of credentials. 
  1. Click on the “Server Options” page and specify any additional options that you want to use when connecting to the remote server. This includes options such as “Collation Compatible” and “RPC Out”. 
  1. Click “OK” to create the Linked Server. 

In this blog, we will explore the key features of Linked Servers in MS SQL Server and provide examples of how to create and use Linked Servers. 

Key Features of Linked Servers 

  1. Remote Server Access: Linked Servers allow you to access data from remote servers and         external data sources such as Oracle, MySQL, or DB2, and even from non-relational data  sources like Excel spreadsheets and CSV files. 
  1. Querying across multiple data sources: Linked Servers enable you to query data across multiple data sources, providing a unified view of data to the application or user. 
  1. Distributed Transactions: You can use Linked Servers to perform distributed transactions that span multiple databases and servers. This enables you to maintain data integrity and consistency across different systems. 
  1. Security: Linked Servers provide a secure way to access external data sources without compromising your database security. You can control access to external data sources by using permissions and logins. 
  1. Performance: Linked Servers can significantly improve performance by allowing you to retrieve data from external sources more efficiently. 
  • In order to configure the Linked Server using the SSMS tool, here is a sequence of steps:- 

Step 1:- Connect to the SQL Server Instance using SSMS tool. 

  • Go to Object Explorer. 
  • Expand the Server Objects. 
  • Right-click on Linked Servers. 
  • Select the “New Linked Server…” option. 


Step 2: Go to the General tab in the new Linked Server window. 

  • Choose the “Other Data Source” option under Server Type. 
  • Give a suitable name to the Linked Server in the Linked Server section. 
  • Choose “Microsoft OLE DB Provider SQL Server” in the provider drop-down. 


Creating a Linked Server: To create a Linked Server, you can use the SQL Server Management Studio (SSMS) or T-SQL statements. Here is an example of how to create a Linked Server using T-SQL


Here are the bullet points for the key features of Linked Servers in MS SQL Server: 

  • Remote Server Access: Connect to external data sources and access their data. 
  • Querying Across Multiple Data Sources: Retrieve data from multiple sources and provide a unified view of data. 
  • Distributed Transactions: Maintain data integrity and consistency across different systems. 
  • Security: Control access to external data sources using permissions and logins. 
  • Performance: Retrieve data from external sources more efficiently. 

Step3 : On the Security tab, select “Be made using this security context” and enter the login credentials for the SQL login that is already present on the Azure DB server and has access to Database1. It is important to note that this option is the least secure way to configure security for the Linked Server since any user who uses it will be authenticated on the remote server using the provided credentials. Therefore, it should only be used in a testing environment.  



Step 3: After creating the Linked Server, you can view it by expanding the Linked Server section in Object Explorer. You can also expand it further to see the list of tables. 




Step4 : Open a new query window in SSMS, switch to the Database2 context on the on-prem SQL Server, and run a SELECT query to fetch data using the Linked Server created in the previous steps. Use the following syntax: 



 Conclusion: 

Linked Servers in MS SQL Server provide a powerful tool for accessing and integrating data from external data sources. By creating a Linked Server, you can easily query data from remote servers and external data sources, perform distributed transactions, and improve performance. With the features and examples discussed in this blog, you should be able to create and use Linked Servers effectively in your SQL Server environment.