Both Linked Server and PolyBase External Table are Data virtualization techniques. Both facilitates bringing data from outside of the SQL Server instance. Both seems to offer similar functionality, but they are different the way they operate. This blog talks about the differences and helps understand which to use when.
What is Linked Server?
Linked Server is not new, and we have been utilizing the Linked Servers to execute commands against the OLD DB data sources outside of the instance of SQL Server.
SQL Server 2000 Technical documentation defines Linked Server as following –
“A linked server configuration allows Microsoft® SQL Server™ to execute commands against OLE DB data sources on different servers. Linked servers offer these advantages:
- Remote server access.
- The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
- The ability to address diverse data sources similarly.”
[Image source – Microsoft]
What is PolyBase External Table?
With the introduction of the PolyBase in SQL Server 2016, users can query relational and non-relational data together.
SQL Server 2016 White Paper defines PolyBase as following –
“PolyBase allows users to query non-relational data in Hadoop, Azure Storage blobs, and files—and combine it anytime, anywhere with their existing relational data in SQL Server. It also provides the option for users to import Hadoop data for persistent storage in SQL Server as well as export aged relational data into Hadoop.
PolyBase also lets users access and query data that is either on-premises or in the cloud and run analytics and business intelligence (BI) on that data.”
[Image source – Microsoft]
PolyBase in SQL Server 2016 started with Hadoop and Azure Blob Storage, or Azure Data Lake Store which now supports access to relational data stores like SQL Server, Oracle, Teradata and MongoDB in SQL Server 2019 CTP.
Difference between Linked Server and PolyBase External Table
| Linked Server | PolyBase External Table |
| Uses OLEDB providers
The Linked Server uses the OLEDB providers. What does it mean is, if you want to talk to Oracle or any other DBMS then you need to install the respective OLEDB provider on the SQL Server instance. | Uses ODBC drivers The PolyBase External Table uses the ODBC drivers. The ODBC drivers get installed along with the Polybase feature when you install the SQL Server. |
| Supports both read/write & pass-through statements Linked server provides both read and write operations. Also, you can invoke a stored procedure in the source connected via Linked Server given stored procedure is supported in the source. Linked Server’s Write capability is distinguishing and can be a driving factor if your business need requires updating remote data. | Supports read only operations only (as of now)
PolyBase supports read only operations as it’s geared towards Analytics. The data pulled in External tables cannot be updated as of SQL Server 2019 CTP.
|
| Instance Scoped Objects
You need to configure (like connection, security) at the instance level, and there is no configuration stored at the database level. What that means is, if you move your database which utilizes the Linked server to a different instance then you will need to reconfigure the target instance again. Linked Server configuration requires high privileges as the objects are scoped at the instance level. | Database Scoped Objects
The definition of PolyBase External table along with the permissions is stored at the database level. What this means is if you move your database with External table configured from one SQL Server instance to another SQL Server instance, then it’s going to work. PolyBase External Table configuration requires less privilege as compared to Linked Server because the objects are scoped at the database level. |
| Single-threaded execution
Unlike SQL Server’s capability to fetch data parallelly from the database tables stored locally, the Linked Server queries are single threaded. | Scaled-out queries The PolyBase scale-out groups can issue multiple reads making it faster while reading large no of data from the source. |
| Separate configuration needed for each instance in Always On Availability Group
The Linked Server is an instance scoped object, so in case of an Availability Group, the Linked Server needs to be configured on all the instances within the availability group. This is an increased overhead. | No Separate configuration needed for Always On Availability Group The PolyBase External Table objects are database scoped, so the configuration moves with the database hence no additional configuration. |
Kamal's Blog 
great post, thanks 🙂
LikeLiked by 1 person