7 questions found
What is a linked server in SQL Server, and why would you set one up?
Beginner
A linked server lets your SQL Server instance connect to and query data from another database server, whether it is another SQL Server instance or a different type of database entirely, allowing you to write queries that combine data from multiple separate database servers as if they were part of the same system.
EXEC sp_addlinkedserver
@server = 'RemoteServer',
@srvproduct = '',
@provider = 'SQLNCLI',
@datasrc = 'RemoteServerAddress';
Real-world example
A company with separate databases for sales and inventory sets up a linked server so their reporting team can write a single query combining data from both systems without manually exporting and merging data by hand.
Common follow-ups: What types of databases can a linked server connect to besides SQL Server?;Does setting up a linked server require special permissions?
Query Optimization & Plans;SQL Server Security & Permissions
How do you write a query that retrieves data from a table on a linked server?
Beginner
You reference the remote table using a four part naming convention consisting of the linked server name, the remote database name, the schema, and the table name, letting you query that remote table directly within your SQL statement just as you would query a local table.
SELECT * FROM RemoteServer.SalesDB.dbo.Customers;
Real-world example
A finance report combines local order data with customer information stored on a separate linked server, using the four part naming convention to seamlessly query both sources in a single statement.
Common follow-ups: What does each part of the four part naming convention represent?;Can you join a local table with a table on a linked server in the same query?
Joins;Query Optimization & Plans
What are the security considerations when configuring authentication for a linked server connection?
Intermediate
You need to decide how the local server authenticates to the remote server, either by passing through the current user's own credentials, using a fixed set of credentials for all connections, or mapping specific local logins to specific remote logins, and each approach has different implications for security and auditing that should be carefully considered.
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'RemoteServer',
@useself = 'FALSE',
@locallogin = NULL,
@rmtuser = 'ReportingUser',
@rmtpassword = 'SecurePassword';
Real-world example
A company configures its linked server to use a dedicated, limited permission reporting account rather than passing through individual user credentials, ensuring consistent and auditable access to the remote data source.
Common follow-ups: What is the risk of using a single shared account for all linked server connections?;How do you audit which local users are actually using a linked server connection?
SQL Server Security & Permissions;Data Types & Schema Design
What performance issues commonly arise when querying data through a linked server, and how can you address them?
Intermediate
Queries against a linked server can perform poorly if SQL Server has to pull large amounts of data across the network before it can apply filters or joins locally, so it often helps to push as much filtering as possible to the remote query itself, use OPENQUERY to send a native query directly to the remote server, and avoid joining very large tables across the linked server connection.
SELECT * FROM OPENQUERY(RemoteServer, 'SELECT CustomerId, Name FROM Customers WHERE Region = ''West''');
Real-world example
A slow report joining a large local table with a linked server table is significantly improved by rewriting the remote portion using OPENQUERY, letting the filtering happen on the remote server before the smaller result set is sent back.
Common follow-ups: What is the difference between using OPENQUERY and the standard four part naming convention?;How do you identify whether a slow query is actually caused by linked server overhead?
Query Optimization & Plans;Data Types & Schema Design
How would you troubleshoot a linked server query that is running much slower than expected?
Advanced
You would examine the execution plan to see how much of the query is being processed remotely versus locally, check whether appropriate indexes exist on the remote table being queried, consider rewriting parts of the query using OPENQUERY to push more processing to the remote server, and verify that network latency between the two servers is not itself a significant bottleneck.
-- Check the execution plan for remote query operations
SET STATISTICS PROFILE ON;
SELECT * FROM RemoteServer.SalesDB.dbo.Orders WHERE OrderDate > '2026-01-01';
Real-world example
A database administrator discovers that a slow linked server query was pulling an entire remote table across the network before filtering it locally, and fixes the issue by rewriting the query with OPENQUERY to filter the data on the remote server first.
Common follow-ups: What role does network latency play in linked server query performance?;How do you check if the remote table involved has appropriate indexes?
Query Optimization & Plans;Indexes
What are alternatives to linked servers for integrating data from multiple different database systems, and when might they be a better choice?
Advanced
Alternatives include using SQL Server Integration Services to build scheduled extract, transform, and load processes that copy data between systems on a regular basis, using PolyBase for querying large external data sources more efficiently, or building a dedicated data warehouse that consolidates data from multiple sources, all of which can offer better performance and reliability than querying live across a linked server for large or frequent data needs.
-- Instead of querying live across a linked server every time,
-- schedule a nightly SSIS job to copy relevant data locally
-- for faster, more reliable reporting
Real-world example
A company replaces a slow, unreliable linked server connection used for daily reporting with a scheduled nightly data synchronization process, resulting in faster, more consistent report performance for their team.
Common follow-ups: When is a live linked server connection still the right choice over a scheduled synchronization process?;What is PolyBase, and how does it compare to a traditional linked server?
SQL Server Agent & Job Scheduling;Query Optimization & Plans
How do you test and verify that a linked server connection is working correctly after it has been configured?
Intermediate
You can use the sp_testlinkedserver stored procedure to quickly verify that SQL Server can successfully establish a connection to the linked server, and run a simple test query against a known table on the remote server to confirm that both connectivity and permissions are properly configured.
EXEC sp_testlinkedserver 'RemoteServer';
SELECT TOP 1 * FROM RemoteServer.SalesDB.dbo.Customers;
Real-world example
A database administrator runs sp_testlinkedserver immediately after configuring a new linked server connection, quickly confirming that the connection details and credentials are all correctly set up before rolling it out to the reporting team.
Common follow-ups: What does it mean if sp_testlinkedserver succeeds but an actual query still fails?;How do you troubleshoot a linked server connection that fails this basic test?
SQL Server Security & Permissions;Error Handling with TRY CATCH