Microsoft SQL Server 2022 introduces the newest version of PolyBase, and with it the capability to query data where it lives, virtualize data, and use REST APIs. REST APIs enable SQL Server to be both more flexible and lightweight while expanding its range of supported connectors and file formats.

SQL Server 2022 now supports CSV, Parquet, and Deltafiles stored on Azure Storage Account v2, Azure Data Lake Storage Gen2, or any simple storage service (S3)–compliant object storage—the last as an on-premises offering or in the cloud. Finally, SQL Server 2022 can now use Create External Table as Select (CETAS), together with commands like OPENROWSET, Create External Table (CET), and all the new T-SQL enhancements. SQL Server 2022 is a powerful data hub.

How does it work?

Data virtualization for SQL Server 2022 is both more flexible and easier to use now that its capabilities are inside the engine itself. The figure below gives a better understanding of the architecture:

OPENROWSET: Lightweight command that allows SQL engine to access data outside SQL Server, either a file or another database. Recommended for loading data or data exploration.

CREATE EXTERNAL TABLE (CET): Creates a table where the data stays in its original location outside of SQL Server, and when selected, the SQL engine will provide the requested data to the user. External table benefits from reusability and can leverage the use of statistics for better performance.

CREATE EXTERNAL TABLE as SELECT (CETAS): It performs a combination of operations in a single command. First, it allows SQL Server to transform and convert a given data stored inside or outside the database. Second, it then exports the data to a different location, either a network location or Azure. Finally, it creates an external table targeting the newly exported data.

These operations are secured by a combination of database master key and external credentials for simplified management.

If the data is stored on Azure Storage Account v2 (abs), Azure Data Lake Gen2 (ADLs), or an S3-compliant Object Storage, SQL Server 2022 will use the REST API implementation. If not, SQL Server 2022 will use PolyBase services—PolyBase services installation is required for both cases.

For a complete list of data sources, please refer to our CREATE EXTERNAL DATA SOURCE documentation.

External file support has also increased, and SQL Server 2022 now supports CSV, Parquet, and Delta type. Please find a complete list of supported external file formats in our CREATE EXTERNAL FILE FORMAT (TRANSACT-SQL) documentation.

Benefits

Major benefits of Data Virtualization with PolyBase on SQL Server 2022:

• No data movement: Access the data where it is.
• T-SQL language: Ability to leverage all the benefits of the T-SQL language, its commands, enhancements, and familiarity.
• One source for all your data: Users and applications can use SQL Server 2022 as its single data source for all of the required data, while database administrators and data engineers have a single environment to maintain.
• Security: Leverage SQL Server security features for granular permissions, credential management, and control.
• Cost: PolyBase is available in all SQL Server 2022 editions.

Next steps

Download the latest release of SQL Server 2022 if you haven’t already done so and check out the SQL Server 2022 Overview and What’s New references.

Learn more

See the following SQL Server resources on Microsoft Learn:

• Introducing data virtualization with PolyBase.
• CREATE EXTERNAL DATA SOURCE (Transact-SQL).
• CREATE EXTERNAL FILE FORMAT (Transact-SQL).

The post Data Virtualization with PolyBase for SQL Server 2022 appeared first on Microsoft SQL Server Blog.

Backup and restore to simple storage service (S3)–compatible object storage is a new feature introduced in SQL Server 2022 that grants the user the capability to back up or restore their databases using S3-compatible object storage, whether that be on-premises, or in the cloud.

What is object storage?

Object storage, also known as object-based storage, is a relatively new storage architecture that manipulates data storage as distinct units, called objects. Object storage was built from the ground up to provide flexibility and scalability.

Unlike the traditional file system that uses a hierarchical file distribution system, object storage is kept in a single storehouse and embeds each data with its required metadata and custom identifier. This metadata customization and classification allows for benefits in data analytics and big data scenarios but is not restricted to it.

Object storage is provided either as software-defined, as hardware appliances, or as a combination for hybrid cloud scenarios. SQL Server 2022 is compatible with any object storage solution that is S3 Rest API compatible. That means that your object storage solution can run locally in your network, in the cloud, or hybrid.

How does it work?

Despite storage architectural differences for SQL Server, the backup and restore to S3 operation behaves in a very similar fashion to backup/restore to URL. the user needs to set up a credential and then execute the backup or restore operation. To differentiate from a traditional backup to URL to object storage, SQL Server 2022 uses “S3://” as an URL prefix.

For security reasons, an HTTPS endpoint must be established between SQL Server 2022 and the object storage provider, for which a certificate must be used and where self-signed certificates are supported. Access permissions must also be provided by the storage administrator.

A database backup file storage in object storage is just as compatible as any other database backup file.

Parts and file size limitations

Unlike traditional file systems, object storage splits and stores its data in blocks called parts, similar to the Azure block blobs when using Azure Blob Storage. Each part can vary its size from 5 MB to 20 MB, where the default value is 10 MB. This behavior is controlled by SQL Server through the parameter MaxTransferSize in combination with Compression.

Each backup URL can allocate up to 10,000 parts, so the file size limit for each URL is 10,000 parts * MaxTransferSize. SQL Server can split a single database backup up into 64 URLs with the final maximum supported being 10,000 parts * MaxTransferSize * 64 URLs.

It’s encouraged to test different MaxTransferSize values since it can offer different benefits based on the database and the network.

Benefits and common scenarios

There are two major benefits and use cases for backup and restore to S3-compatible object storage:

• Resource optimization: Allows the users more flexibility for planning their backup strategy, mixing different storage offerings with different costs, and guarantees the best value.
• Migration: The ability to seamlessly execute both back-ups and restore using S3 storage allows for easier database migration.

Next steps

Download the latest release of SQL Server 2022 if you haven’t already done so and check out the SQL Server 2022 Overview and What’s New references. There are many new features and improved functionalities being added to this release.

Learn more

• Read our technical blog post from the public preview launch.
• Take a deeper look with more in-depth documentation.
• Explore information about troubleshooting best practices.

The post Backup and restore to URL for S3-compatible object storage appeared first on Microsoft SQL Server Blog.