Accessing Remote Data using Linked Server

In this lesson, assume that you are starting from ServerA and need to access a stored procedure that is located on ServerB. The specifications of this stored procedure are as follows:

Take the following steps to access and run this stored procedure:

1. Add the link to ServerA from ServerB:
   sp_addlinkedserver ServerB
2. Create your query from ServerA:
   EXECUTE ServerB.Accounting.dbo.usp_TrimSpace

The statement above will execute the usp_TrimSpace stored procedure from ServerA. The result will be exactly the same as running usp_TrimSpace from ServerB. Note that a fully qualified path was specified in the above example.

In SQL Server 2019, joining remote tables involves a series of methodical steps that leverage the functionality of linked servers or distributed queries. The process of joining remote tables is akin to joining local tables, with the added complexity of cross-server communication. Here's a structured approach to joining remote tables in SQL Server 2019:

1. Establish Linked Servers (If Not Already Configured):
   • Before joining tables from different servers, ensure that linked servers are properly configured. A linked server in SQL Server is a definition that allows for connectivity to another SQL Server instance or another database product.
   • Use the `sp_addlinkedserver` stored procedure to create a linked server, specifying the remote server's name, product name, data source, and other parameters as required.
2. Verify Permissions:
   • Ensure that the SQL Server instance has the necessary permissions to access both the local and remote servers. This involves configuring security accounts and permissions that allow for data retrieval across servers.
3. Use Fully Qualified Names in Queries:
   • When writing a SQL query to join tables from different servers, use fully qualified names. This includes the linked server name, the database name, the schema, and the table name.
   • Syntax: `[LinkedServerName].[DatabaseName].[SchemaName].[TableName]`
4. Constructing the Join Query:
   • Write the SQL join query as you would for local tables, but replace the table names with fully qualified names.
   • Example Syntax:
     

     SELECT A.column1, B.column2 
     FROM [LocalServer].[LocalDB].[dbo].[LocalTable] A
     INNER JOIN [RemoteServer].[RemoteDB].[dbo].[RemoteTable] B 
     ON A.KeyColumn = B.KeyColumn
     

   • This example demonstrates an inner join between a local table and a remote table, where `A` and `B` are table aliases for clarity.
5. Optimizing Query Performance:
   • Queries involving remote joins can be subject to network latency and might have performance implications.
   • Consider using filters (WHERE clauses) to limit the amount of data transferred over the network.
   • Query optimization techniques like indexing can be beneficial, especially on join and filter columns.
6. Handling Data Consistency and Transaction Management:
   • Ensure data consistency when performing write operations (INSERT, UPDATE, DELETE) across linked servers.
   • Use transactions cautiously, as distributed transactions can be complex and may require MS DTC (Microsoft Distributed Transaction Coordinator).
7. Testing and Validation:
   • Thoroughly test the join queries in a development or staging environment before deploying to production.
   • Ensure that the joins return the expected results and that the performance is acceptable.
8. Security and Compliance:
   • Be mindful of data security and compliance, especially when joining and transferring data across servers that might have different security protocols or are in different geographical locations.

By following these steps, SQL Server 2019 users can effectively join remote tables, ensuring data integrity, performance efficiency, and compliance with security standards.

Now, Assume that you are on ServerA, and that you have already linked to ServerB. You need to join the sales table on ServerA with the customers table in the Accounting database on ServerB. Now, Assume that you are on ServerA, and that you have already linked to ServerB. You need to join the sales table on ServerA with the customers table in the Accounting database on ServerB.

You should also know that you can use transactions with your linked servers by using the BEGIN DISTRIBUTED TRANSACTION Transact-SQL statement. Before issuing this statement, you must have the MSDTC Service running.

SQL Server is also capable of establishing a link to any other data source that is ODBC- or OLE DBcompatible. The link can be created using Management Studio or T-SQL code. A link to another SQL Server can be established by means of Management Studio or code. Within Management Studio's Object Explorer, linked servers are listed under the Server Objects node.

Selecting Linked Servers -> context menu 
-> New Linked Server opens the New Server Properties form 

(see Figure 6-6).

In the General tab of the Linked Server Properties form, enter the name of the external SQL Server in the Linked Server field, and click the SQL Server button in the Server Type section. To link to a named instance of SQL Server, enter the instance name as server\instance without square brackets. In Figure 6-6, the linked server is MAUI\COPENHAGEN. SQL Server 2008 can link to any other SQL Server 2000, 2005, or 2008 instance, or to a SQL Server 7 server, but SQL Server 2008 will not link to a SQL Server 6.5 server without going through an OBDC driver.

Click the Exercise link below to practice adding a linked server and accessing a remote stored procedure.
Accessing Remote - Data - Exercise
In the next lesson, you will learn about using two special functions available in SQL Server 2012 for accessing remote data within your queries.