Applies to: 
SQL Server 2016 (13.x) and later versions
PolyBase vs. linked servers
The following table highlights the differences between PolyBase and linked server features:
| PolyBase | Linked Servers |
|---|---|
| Database scoped object | Instance scoped object |
| Uses ODBC drivers | Uses OLE DB providers |
| Supports read-only operations for all data sources and write to some external data sources | Supports both read and write operations |
| Queries to remote data source from a single connection can be scaled-out | Queries to remote data source from a single connection can't be scaled-out |
| Predicates pushdown is supported | Predicates pushdown is supported |
| No separate configuration needed for availability group | Separate configuration needed for each instance in availability group |
| Basic authentication only | Basic and integrated authentication |
| Suitable for analytic queries processing large number of rows | Suitable for OLTP queries returning single or few rows |
| Queries using external table can't participate in distributed transaction | Distributed queries can participate in distributed transaction |
What's new in PolyBase in SQL Server 2022?
SQL Server 2022 (16.x) supports CSV, Parquet, and Delta files stored on Azure Storage Account v2, Azure Data Lake Storage Gen2, or any S3-compatible object storage by using the S3 REST API, on-premises or in the cloud.
SQL Server 2022 (16.x) supports CREATE EXTERNAL TABLE AS SELECT (CETAS), along with commands like OPENROWSET, CREATE EXTERNAL TABLE (CET), and the T-SQL enhancements.
SQL Server 2022 (16.x) doesn't support Hadoop external data sources.
SQL Server 2022 (16.x) Cumulative update 2 introduces support for Oracle TNS files. For more information and samples, see CREATE EXTERNAL DATA SOURCE CONNECTION_OPTIONS.
For more information, see Data Virtualization with PolyBase for SQL Server 2022 and review SQL Server 2022 PolyBase enhancements.
What's new in PolyBase in SQL Server 2019?
PolyBase in SQL Server 2019 (15.x) can read data from multiple sources. You can store the data from these external data sources as external tables on your SQL Server instance. PolyBase also supports pushdown computation to these external data sources, excluding ODBC generic types.
Compatible data sources
- SQL Server
- Oracle
- Teradata
- MongoDB
- Compatible ODBC generic types
SQL Server 2019 (15.x) Cumulative update 19 introduces support for Oracle TNS files. For more information and samples, see CREATE EXTERNAL DATA SOURCE CONNECTION_OPTIONS.
Note
PolyBase can connect to external data sources by using third-party ODBC drivers. PolyBase doesn't include these drivers, and they might not work as intended. For more information, see Configure PolyBase to access external data with ODBC generic types.
What CONNECTION_OPTIONS can I specify for connection to third-party data sources?
Review CREATE EXTERNAL DATA SOURCE CONNECTION_OPTIONS for supported data provider connection options for SQL Server, Oracle, Teradata, the MongoDB API for Cosmos DB, and generic ODBC data providers.
How can I view the remote query execution plan of PolyBase pushdown computation?
You can view the remote query execution plan by using different methods depending on the version of SQL Server. For more information, see PolyBase Troubleshooting - To view the PolyBase query plan.
How can I tell whether PolyBase pushdown computation is happening?
For more information, see How to tell if external pushdown occurred.
Is PolyBase supported on Azure SQL Database?
No, but you can query data in files on Azure Blob Storage by using the OPENROWSET function, or use CREATE EXTERNAL TABLE to read from a serverless SQL pool in Synapse Analytics. For more information, see Azure SQL can read Azure Data Lake storage files using Synapse SQL external tables.
Is PolyBase supported on Azure SQL Managed Instance?
Azure SQL Managed Instance has its own data virtualization capabilities for Azure Data Lake Storage (ADLS) and Azure Blob Storage as data sources. For more information, see Data virtualization with Azure SQL Managed Instance.
Which ports should I allow through my firewall for PolyBase?
PolyBase itself doesn't require opening any additional ports, except when you use a PolyBase scale-out group. Without a scale-out group, the PolyBase service doesn't need any firewall rules.
On Windows servers, PolyBase uses the ports that the SQL Server instance uses in the IPAll section of the TCP/IP settings in SQL Server Configuration Manager. For more information, see TCP/IP Properties (IP Addresses tab). On Linux servers, PolyBase uses the network.tcpport setting. For more information, see TCP port.
For PolyBase scale-out groups over multiple Windows servers, see port information in the next question.
However, you must allow other ports for communication to the various external data sources that PolyBase uses. Refer to ODBC configuration, Azure resource configuration, or specific data source documentation. For example:
| Source platform | Reference |
|---|---|
| Microsoft SQL Server | Ports Used By SQL Server |
| Hortonworks HDP | Administering HDFS |
| Cloudera CDH | Ports Used by CDH Components |
What ports are used for PolyBase scale-out group?
By default, the PolyBase data movement service connects to the head node of a scale-out group over a range of ports from TCP 16450-16460 (typically 16450-1645), plus port 17001. PolyBase uses these ports only when you configure a scale-out group, so allow them through server firewalls. SQL Server Setup automatically creates a local firewall rule that allows these ports when you install the feature. For more information, see Configure PolyBase scale-out groups on Windows.
Note
SQL Server PolyBase scale-out groups were removed in SQL Server 2022 (16.x).