When it comes to pulling data from another Microsoft SQL Server, two popular options are Linked Server and Polybase. Both technologies enable you to access and query data from remote servers, but they have distinct differences in their implementation and use cases. In this article, we’ll explore the practical applications of Linked Server and Polybase, along with T-SQL code examples, to help you determine which approach is best suited for your data integration needs.

Linked Server

A Linked Server allows you to create a connection between two SQL Server instances, enabling you to execute distributed queries and transactions across multiple servers. It’s a straightforward way to access data from a remote server without the need for additional setup or configuration. Here’s an example of how to create a Linked Server and query data from it using T-SQL:

-- Create a Linked Server
EXEC sp_addlinkedserver
    @server = 'RemoteServer',
    @srvproduct = '',
    @provider = 'SQLNCLI',
    @datasrc = 'RemoteServerName';

-- Query data from the Linked Server
SELECT *
FROM [RemoteServer].[Database].[Schema].[Table];

Linked Server is a good choice when you need to perform ad-hoc queries or occasional data integration tasks between SQL Server instances. It provides flexibility and ease of use, making it suitable for scenarios where real-time data access is required.

Polybase

Polybase, on the other hand, is a technology introduced in SQL Server 2016 that allows you to query data from various external sources, including Hadoop, Azure Blob Storage, and other SQL Server instances. It leverages the power of parallel processing and distributed computing to efficiently query large volumes of data. Here’s an example of how to create an external table using Polybase and query data from it:

-- Create an external data source
CREATE EXTERNAL DATA SOURCE RemoteServer
WITH (
    TYPE = RDBMS,
    LOCATION = 'RemoteServerName',
    CONNECTION_OPTIONS = 'Database=Database;'
);

-- Create an external table
CREATE EXTERNAL TABLE [Schema].[ExternalTable]
(
    -- Define table schema
)
WITH (
    DATA_SOURCE = RemoteServer,
    SCHEMA_NAME = 'Schema',
    OBJECT_NAME = 'Table'
);

-- Query data from the external table
SELECT * FROM [Schema].[ExternalTable];

Polybase excels in scenarios where you need to query and analyze large datasets from external sources. It offers better performance and scalability compared to Linked Server, particularly when dealing with big data. Polybase also provides additional features like pushdown computation, which allows you to offload processing to the external source, reducing network traffic and improving query performance.

Choosing Between Linked Server and Polybase

When deciding between Linked Server and Polybase, consider the following factors:

1. Data volume: If you’re dealing with large datasets, Polybase is the preferred choice due to its parallel processing capabilities.
2. Performance: Polybase offers better performance for complex queries and large data volumes compared to Linked Server.
3. Scalability: Polybase scales well with increasing data sizes and can handle big data workloads efficiently.
4. Ease of setup: Linked Server is relatively simpler to set up and use compared to Polybase, which requires additional configuration steps.

In conclusion, both Linked Server and Polybase have their strengths and use cases when it comes to pulling data from another SQL Server. Linked Server is suitable for ad-hoc queries and occasional data integration tasks, while Polybase shines in scenarios involving large datasets and high-performance requirements. By understanding the differences and considering your specific needs, you can make an informed decision on which approach to use for your data integration projects.

Visit the official Microsoft documentation to learn more about Polybase in SQL Server.