Monitor SQL Server enabled by Azure Arc 

Simplifying deployment and cost management in multi-cloud environments.

Learn more

The traditional method of paying for SQL Server is buying license and software assurance. Now, you can connect your SQL Server to Azure Arc and pay only for the hours when your VM with SQL Server is online and your SQL instance is active. For the pricing information, see SQL Server 2022—Pricing | Microsoft.

While SQL Server 2022 integrates a pay-as-you-go billing option in the setup, the older versions require a product key to install any production edition. This blog provides instructions on how to install a pay-as-you-go image of any SQL Server version starting from SQL Server 2012 without purchasing a license and providing a product key.

NOTE: if you have access to a SQL Server image that does not require a product key, for example from a hosting provider, you should install it, follow the documented Azure Arc onboarding process for SQL Server and then set the License type to pay-as-you-go in SQL Server configuration.

The process in a nutshell

The deployment of a pay-as-you-go image on a virtual or physical machine running in any cloud, edge, or on-premises datacenters consists of three steps:

1. Installing the prerequisites on the target machines
2. Creating a support ticket with Microsoft Azure to request and download a pay-as-you-go image
3. Running the installation script

Prerequisites

Make sure your target machine meets the following requirements.

On target VMs:

• Windows Server instance is running, and you have a local administrator account.
• Azure PowerShell is installed and updated.
• For SQL Server version 2014, you will need to install .NET. For more information on this prerequisite, visit here.
• You have a remote desktop connection to connect to Windows Server as a local administrator.
• You copied the script from here to a local folder. Follow the download instructions in the readme file.
• Important: If your target VM runs Windows Server 2016, you must make sure that your Transport Layer Security (TLS) configuration is compatible with Azure. Follow the instructions in the readme file on how to mitigate the TLS version issue on Windows Server 2016.

On Microsoft Azure:

• You have an Azure account, subscription, and a target resource group.

Opening a support ticket

You can open the support ticket using a Support + Troubleshooting entry from the subscription which the newly installed SQL Server instance will be connected to. The following screenshots illustrate the flow. Make sure to use the answers as highlighted in the screenshots.

After you complete the support ticket creation process, Azure support will email you a private link to a workspace with the zip file you need to download.

Download the file to the VM where you want to install SQL Server, and unzip it to the same folder that contains the installation script.

Note: For SQL Server 2012 and SQL Server 2022 images, the keys are not required, and the .zip file will contain only the ISO file. For other SQL Server versions, the .zip file includes a product key and the ISO file. The script will recognize these differences.

Running the script

Follow the instructions in the readme file on how to launch the script. An Azure sign-in screen will prompt you to enter your Azure credentials or identify the account you’re already signed into. After this, the script proceeds unattended to carry out the following steps:

• Installing Azure PowerShell modules if they’re not already installed
• Logging into Azure with your assistance
• Onboarding the VM to Azure Arc
• Installing SQL Server on the Windows Server from the file you identified in the previous step
• Mounting the ISO file as a volume
• Installing SQL Arc Extension with a pay-as-you-go license type
• Displaying the status of the Azure resource for the connected SQL Server Instance

Disclaimer: The script has been tested on several combinations of VM products available on AWS and Google Cloud Platform with different versions of Windows Server and SQL Server, but it is not specific to these platforms, and you are welcome to try it on other clouds.

Supercharge SQL Server with Azure Arc

You can use this process to streamline the installation of pay-as-you-go images of SQL Server versions and editions of your choice to machines across different clouds, on-premises datacenters, and edge. By doing so, you can maintain control over the SQL Server compliance and optimize the cost based on the resource’s utilization.

The post Save money on Microsoft SQL Server licensing with Microsoft Azure Arc appeared first on Microsoft SQL Server Blog.

“With our migration to PaaS, we got what we wanted: greater scalability, reliability, security, agility in managing our IT infrastructure—and greater peace of mind—all without the cost and hassle of doing this ourselves,” 

Daniel Engberg, Head of AI, Data, and Platforms at Scandinavian Airlines System  

Migrate to Microsoft Azure

Boost productivity and enable innovation.

Read more

This blog post will guide you through several best practices our customers employed when faced with the SQL Server end-of-support moment. Customers have three choices for handling their out-of-support SQL Server workloads: moving or updating to Azure, upgrading to SQL Server 2022, or getting Extended Security Updates (ESUs) for additional preparation time. 

Migrate and modernize to Azure, a smooth path, a more powerful destination 

Migrating to a cloud platform is an essential step on the journey to modernization, and there are many choices. What makes SQL Server and Microsoft Azure SQL unique is that it’s built on the same engine, no matter where you deploy, which means you can build on your existing SQL experience while gaining the layered security, intelligent threat detection, and data encryption that Azure provides. 

Modernizing to Microsoft Azure SQL Managed Instance offers cost savings, scalability, security, seamless migration, productivity, and always up-to-date features. Some of the recent product highlights include Azure SQL Managed Instance Next-gen General Purpose, now in public preview, which supports twice as many Azure VMs configurations, making migration and modernization faster and easier than ever before for a larger number of customer scenarios. Customers can experience the full capabilities of managed SQL Server in the cloud at no cost for the initial 12 months with access to a General Purpose instance capable of accommodating up to 100 databases, along with 720 vCore hours of compute per month (non-accumulative) and 64 GB of storage through Azure SQL Managed Instance Free Tier, now in public preview. 

Modernizing your SQL Server workloads to Azure also presents a chance to utilize cutting-edge innovation like Microsoft Copilot. Microsoft Copilot in Azure has extended its capabilities to Microsoft Azure SQL Database with new skills designed to enhance the management and operation of SQL-based applications.  

Extending end-of-support time

If you are ready to move to the cloud but feel challenged to upgrade or modernize before the end of the support timeline, Extended Security Updates are available for free in Azure for SQL Server 2014 and 2012 and Windows Server 2012. Secure your workloads for up to three more years after the end of the support deadline by migrating applications and SQL Server databases to Microsoft Azure Virtual Machines. Free Extended Security Updates are available for Azure Virtual Machines including Microsoft Azure Dedicated Host, Microsoft Azure VMWare Solution, Nutanix Cloud Clusters on Azure, and Microsoft Azure Stack (Microsoft Azure Stack Hub, Microsoft Azure Stack Edge, and Microsoft Azure Stack HCI). Combining Extended Security Updates in Azure with Azure Hybrid Benefit further reduces your costs. With these pricing advantages, AWS is up to five times more expensive than Azure for SQL Server and Windows Server end-of-support workloads. 

In-place upgrade to SQL Server 2022 

Another way to stay protected is to upgrade your SQL Server to SQL Server 2022, the most Azure-enabled release yet. Get more out of your data with enhanced security, industry-leading performance and availability, and business continuity through Azure. 

SQL Server 2022 is the most Azure-enabled release of SQL Server, with continued innovation across performance, security, and availability. Gain deeper insights, predictions, and governance from your data at scale. Take advantage of enhanced performance and scalability with built-in query intelligence. 

Stay protected on-premises or in multi-cloud environments with Azure Arc 

Just as with SQL Server 2012, Extended Security Updates for SQL Server 2014 offers an enhanced cloud experience through Microsoft Azure Arc. First year coverage from Extended Security Updates started on July 10, 2024. With this more customer-centric approach, security updates will be natively available in the Microsoft Azure portal through Azure Arc. This also provides Azure benefits and flexible subscription billing for SQL Server 2014 workloads on-premises or in multi-cloud environments. 

We’re continuing to enhance the capabilities Azure Arc offers to Extended Security Updates. Just recently, physical-core licensing with unlimited virtualization was released for SQL Server 2012 and 2014 ESUs. For customers who need to maximize database performance or require security isolation and better resource management, physical core licensing provides a more cost-effective way to leverage Extended Security Updates via Azure Arc. 

Also, if you enabled ESU subscription in your production environment managed through Azure Arc, you can enable SQL Server ESU subscription in the non-production environment for free, through SQL Server Developer Edition or an Azure dev/test subscription. 

We encourage all our customers running SQL Server 2014, Windows Server 2012, and Windows Server 2012 R2 to start planning for the end of support. We have migration resources, best practices, and more, as well as a rich ecosystem of partners ready to help. To get started, please visit the following pages to learn more. 

Learn More 

• Why migrate Windows Server and SQL Server to Azure: ROI, innovation, and free offers 
• Get the full ROI and TCO reports from IDC: The Business Value of Microsoft Azure SQL Database and Azure SQL Managed Instance Workload and The Business Value of Microsoft Azure for SQL Server and Windows Server Workloads 
• Previous announcement on SQL Server 2014 End-of-Support moment: Protect SQL Server 2014 workloads with Azure flexibility – Microsoft SQL Server Blog

The post Modernize Microsoft SQL Server 2014 workloads with Azure appeared first on Microsoft SQL Server Blog.

During recent years, Azure has undergone significant evolution, marked by groundbreaking innovations like Microsoft Fabric and Azure Data Lake Storage. As we continue this journey, it remains imperative to keep evolving our approach on hybrid data storage, ensuring optimal empowerment for our SQL Server customers in leveraging the best from Azure.

Retirement of SQL Server Stretch Database 

On November 16, 2022, the SQL Server Stretch Database feature was deprecated from SQL Server 2022. For in-market versions of SQL Server 2019 and 2017, we had added an improvement that allowed the Stretch Database feature to stretch a table to an Azure SQL Database. Effective July 9, 2024, the supporting Azure service, known as SQL Server Stretch Database edition, is retired. Impacted versions of SQL Server include SQL Server 2022, 2019, 2017, and 2016.  

In July 2024, SQL Server Stretch Database will be discontinued for SQL Server 2022, 2019, 2017, and 2016. We understand that retiring an Azure service may impact your current workload and use of Stretch Database. Therefore, we kindly request that you either migrate to Azure or bring their data back from Azure to your on-premises version of SQL Server. Additionally, if you’re exploring alternatives for archiving data to cold and warm storage in the cloud, we’ve introduced significant new capabilities in SQL Server 2022, leveraging its data virtualization suite. 

The path forward 

SQL Server 2022 supports a concept named CREATE EXTERNAL TABLE AS SELECT (CETaS). It can help customers archive and store cold data to Azure Storage. The data will be stored in an open source file format named Parquet. It operates well with complex data in large volumes. With its performant data compression, it turns out to be one of the most cost-effective data storage solutions. Using OneLake shortcuts, customers then can leverage Microsoft Fabric to realize cloud-scale analytics on archived data.  

Our priority is to empower our SQL Server customers with the tools and services that leverage the latest and greatest from Azure. If you need assistance in exploring how Microsoft can best empower your hybrid data archiving needs, please contact us.

New solution FAQs

What’s CETaS? 

Creates an external table and then exports, in parallel, the results of a Transact-SQL SELECT statement. 

• Azure Synapse Analytics and Analytics Platform System support Hadoop or Azure Blob Storage.
• SQL Server 2022 (16.x) and later versions support CETaS to create an external table and then export, in parallel, the result of a Transact-SQL SELECT statement to Azure Data Lake Storage Gen2, Azure Storage Account v2, and S3-compatible object storage. 

What is Fabric? 

Fabric is an end-to-end analytics and data platform designed for enterprises that require a unified solution. It encompasses data movement, processing, ingestion, transformation, real-time event routing, and report building. Fabric offers a comprehensive suite of services including Data engineering, Data Factory, Data Science, Real-Time Analytics, Data Warehouse, and Databases. 

With Fabric, you don’t need to assemble different services from multiple vendors. Instead, it offers a seamlessly integrated, user-friendly platform that simplifies your analytics requirements. Operating on a software as a service (SaaS) model, Fabric brings simplicity and integration to your solutions. 

Fabric integrates separate components into a cohesive stack. Instead of relying on different databases or data warehouses, you can centralize data storage with Microsoft OneLake. AI capabilities are seamlessly embedded within Fabric, eliminating the need for manual integration. With Fabric, you can easily transition your raw data into actionable insights for business users. 

What is OneLake shortcuts?  

Shortcuts in OneLake allow you to unify your data across domains, clouds, and accounts by creating a single virtual data lake for your entire enterprise. All Fabric experiences and analytical engines can directly connect to your existing data sources such as Azure, Amazon Web Services (AWS), and OneLake through a unified namespace. OneLake manages all permissions and credentials, so you don’t need to separately configure each Fabric workload to connect to each data source. Additionally, you can use shortcuts to eliminate edge copies of data and reduce process latency associated with data copies and staging. 

Shortcuts are objects in OneLake that point to other storage locations. The location can be internal or external to OneLake. The location that a shortcut points to is known as the target path of the shortcut. The location where the shortcut appears is known as the shortcut path. Shortcuts appear as folders in OneLake and any workload or service that has access to OneLake can use them. Shortcuts behave like symbolic links. They’re an independent object from the target. If you delete a shortcut, the target remains unaffected. If you move, rename, or delete a target path, the shortcut can break. 

Learn more 

• Stretch Database
• Deprecated database engine features in SQL Server 2022
• Install an on-premises data gateway
• SQL Server in Microsoft Fabric? Use CETaS to move data!
• OneLake shortcuts
• CREATE EXTERNAL TABLE AS SELECT (CETaS)

Microsoft Fabric

Bring your data into the era of AI

Explore solutions

The post Announcing the retirement of SQL Server Stretch Database appeared first on Microsoft SQL Server Blog.

To generate a production-ready schema-only copy of a database, it’s highly recommended to utilize tools such as Microsoft SQL Server Data Tools (SSDT); the Generate and Publish scripts Wizard; or data-tier application extraction, which includes either the Extract Data-tier Application (DAC) Wizard or a PowerShell script. These tools provide a reliable way to create a copy of a database for use in production environments.

Learn more about

• DBCC CLONEDATABASE (Transact-SQL) – SQL Server
• Clone an Existing Database – SQL Server Data Tools (SSDT)
• Generate and Publish Scripts Wizard – SQL Server Management Studio (SSMS)
• Create a statistics-only database using a generated statistics script – SQL Server
• Extract a DAC from a Database – SQL Server

Frequently asked questions

What is SSDT?

SQL Server Data Tools (SSDT) is a modern development tool that integrates with Microsoft Visual Studio and provides design, debugging, and deployment capabilities for building SQL Server relational databases, databases in Azure SQL, Analysis Services (AS) data models, Integration Services (IS) packages, and Reporting Services (RS) reports. With SSDT, developers can perform necessary tasks without needing the admin-focused SQL Server Management tool on their developer computer. Essentially, Visual Studio removes unnecessary features like admin tools, and focuses on tools that are useful for developers, like database and schema comparison tools.

What is the Generate and Publish Scripts Wizard?

The Generate and Publish Scripts Wizard is a feature in SQL Server Management Studio (SSMS) that allows you to create scripts for transferring a database between instances of the SQL Server Database Engine or Azure SQL Database. You can generate scripts for a database on an instance of the Database Engine in your local network, or from SQL Database. The generated scripts can be run on another instance of the Database Engine or SQL Database. You can also use the wizard to publish the contents of a database directly to a Web service created by using the Database Publishing Services. You can create scripts for an entire database or limit it to specific objects.

What is a data-tier application (DAC)?

A data-tier application (DAC) is a logical database entity that defines all of the SQL Server objects—such as tables, views, and instance objects, including logins—associated with a user’s database. A DAC is a self-contained unit of the entire database model and is portable in an artifact known as a DAC package, or .dacpac. Tooling support for data-tier applications enables developers and database administrators to deploy dacpacs to new or existing databases. Deployments to an existing database update the database model from the existing state to match the contents of the dacpac. Developers build DACs from SQL database projects, a declarative development concept for building SQL objects that enables source control on the database schema.

A .bacpac is a related artifact that, by default, encapsulates the database schema and the data stored in the database. The primary use case for a BACPAC is to move a database from one server to another—or to migrate a database from a local server to the cloud—and archiving an existing database in an open format.

What is DBCC CLONEDATABASE command?

DBCC CLONEDATABASE creates a new database that contains the schema of all the objects and statistics from the specified source database. Cloned databases copy all schema and metadata of the source database without copying any data.

How do I use Schema Compare to compare different database definitions?

SSDT includes a Schema Compare utility that you can use to compare two database definitions. The source and target of the comparison can be any combination of connected database, SQL Server database project or snapshot, or .dacpac file. The results of the comparison appear as a set of actions that must be taken with the target to make it the same as the source. Once the comparison is complete, you can update the target directly (if the target is a project or a database) or generate an update script that has the same effect.

The differences between source and target appear in a grid for easy review. You can drill into and review each difference in the results grid or in script form. You can then selectively exclude specific differences.

You can save comparisons either as part of an SQL Server Database project or as a standalone file. You can also set options that control the scope of the comparison and aspects of the update. Then you can save the comparison so that you can easily repeat the same comparison later or use it as the starting point for a new comparison.

Why do I need to generate a schema-only clone of a database with statistics in SQL Server?

You will need to generate a schema-only clone of a database with statistics to investigate performance issues.

The query optimizer in SQL Server uses the following types of information to determine an optimal query plan:

• Database metadata
• Hardware environment
• Database session state

Typically, you must simulate all these same types of information to reproduce the behavior of the query optimizer on a test system.

Microsoft Customer Support Services might ask you to generate a schema script of the database along with statistics to investigate a query optimizer issue.

The post Update on the support of DBCC CLONEDATABASE for production use appeared first on Microsoft SQL Server Blog.

We are excited to get back in front of customers at Microsoft Ignite 2023 and PASS Data Community Summit to announce powerful product enhancements across Microsoft Azure databases designed to help customers take the next step or the first step in their transformation journey, with databases that are intelligent, trusted, and ready for developers to build, without limits.   

Limitless innovation for cloud native applications

If you are an application developer looking for a flexible relational cloud database solution with performance and scalability to support your most demanding applications, you’ll want to check out Microsoft Azure SQL Database Hyperscale. Built on a unique architecture that splits the storage and compute nodes, these resources scale independently to meet the unique requirements of your apps. Plus, you can eliminate the need to pre-provision storage resources, as the storage automatically scales to meet demand, with support of up to 100 TB. We are thrilled to announce that we are introducing lower compute pricing on SQL Database Hyperscale, saving customers up to 35 percent on their compute bill. Effective December 15, 2023, customers will have competitive pricing on the resources they need to build scalable, secure, AI-ready applications. 

We’re also excited to share that the Microsoft Azure SQL Managed Instance feature wave has reached general availability. This set of features improves Azure SQL Managed Instance’s performance, reliability, and security. The latest release will deliver deeper integration with Microsoft SQL Server on-premises and the wider Azure service platform. And soon, customers will be able to start testing Azure SQL Managed Instance for free. Landing in December 2023, customers will be able to run proof of concepts, test applications or simply learn more about the operational benefits of a fully managed database-as-a-service. This is in addition to the free Azure SQL Database offer that launched in October 2023.  

Microsoft is also excited to share the newest updates for our fully managed community based open-source databases. These services help you manage your database and database infrastructure with automation, freeing you from the routine database management tasks so you can concentrate on what matters most.

Enhanced performance and scalability for Microsoft Azure Database for PostgreSQL

The latest enhancements for Azure Database for PostgreSQL deliver advanced storage and compute capabilities that enable optimal price-performance for enterprise production workloads. Customers can expect enhancements for advanced storage, compute capabilities, and flexibility for managing performance and cost.  

Azure Database for PostgreSQL extension for Azure AI

The PostgreSQL extension for Azure AI allows developers to use large language models (LLMs) and build rich PostgreSQL generative AI applications, meaning PostgreSQL queries on Azure can now power Azure AI applications. It enables calling into Microsoft Azure OpenAI Service to generate LLM-based vector embeddings that allow efficient similarity searches, which is particularly powerful for recommendation systems, as well as calling into Azure AI Language for a wide range of scenarios such as sentiment analysis, language detection, entity recognition, and more.

New performance enhancements in Microsoft Azure Database for MySQL Business Critical 

New performance enhancements in Azure Database for MySQL Business Critical service tier makes it ideal for high-performance transactional or analytical applications. In fact, a recent performance benchmark study by Principled Technologies shows that Azure Database for MySQL Business Critical service tier is up to 50 percent faster than MySQL on Amazon Web Services Relational Data Service and up to 2.26 times faster than Google Cloud Platform Cloud SQL for MySQL. These key innovations help make Azure Database for MySQL Business Critical the perfect option to run mission-critical, Tier 1 MySQL workloads.

Extend Azure to your entire data estate

For all the innovation that customers are driving in the cloud, we recognize much of the customer’s data remains on-premises. This is why Microsoft continues to invest heavily in ensuring that customers can get the most from their entire data estate with Microsoft Azure Arc. The latest monitoring capabilities from SQL Server-enabled by Azure Arc are designed to deliver critical insights across your entire SQL Server environments, optimizing database performance and delivering fast diagnostic times.  

Customers can also now improve SQL Server business continuity and consistency by viewing and managing Always On availability groups, failover cluster instances, and backups directly from the Azure portal. This capability provides better visibility and an easier, more flexible way to configure critical database operations.  

In addition, with Extended Security Updates as a service and automated patching, customers can always keep their apps secure, compliant, and up to date. Learn more about these latest features.

We look forward to the week ahead and connecting with you in person.

The post Power what’s next with limitless relational databases from Azure appeared first on Microsoft SQL Server Blog.

SQL Server has brought reliability and security to mission-critical workloads for 30 years. SQL Server 2022, released November 2022, delivers performance enhancements as well as Azure connectivity for disaster recovery, analytics, and governance. Now customers like Mediterranean Shipping Company (MSC) are using SQL Server 2022 to expand their capabilities to better support a truly worldwide operation.

“Features like Query Store hints and the next generation of Intelligent Query Processing are awesome. The possibility of improving and maintaining great performance over time without code changes is getting better on every new SQL Server version“

—Javier Villegas: IT Director for DBA and BI Service, Mediterranean Shipping Company

We love delivering innovation with each new version of SQL Server. But all good things must come to an end: SQL Server 2014 has a ten-year product lifecycle, and after this time product support and regular security updates cease. SQL Server 2014 reached end of support on July 9, 2024.

The upcoming end-of-support milestones are an opportunity to evaluate your business needs and modernize your IT estate. To provide you with the flexibility to stay secure while you plan for the future, we are announcing a new option to help you manage this transition: SQL Server 2014 Extended Security Updates will offer up to three years of critical security updates. Extended Security Updates are free for workloads running in Azure. The updates will be offered for sale to use on-premises and in multicloud environments.

Just as with SQL Server 2012, Extended Security Updates for SQL Server 2014 offers an enhanced cloud experience through Azure Arc. Year 1 coverage from Extended Security Updates started on July 10, 2024. With this more customer-centric approach, security updates will be natively available in the Azure Portal through Azure Arc. This also provides Azure benefits and flexible subscription billing for SQL Server 2014 workloads on-premises or in multicloud environments.

Your options in end of support moments

Customers have multiple options for the SQL Server in their data estate that is nearing the end of support. These options include migrating or modernizing to Azure, upgrading to SQL Server 2022, or purchasing Extended Security Updates if more time is needed before the upgrade or migration.

Modernize to Azure for the latest database features

Modernize your SQL Server by migrating to the always up-to-date Azure SQL Managed Instance, which upgrades and patches automatically. Azure SQL Managed Instance is an intelligent cloud database service combining the broadest SQL Server engine compatibility—all the way back to SQL Server 2008—with the benefits of a fully managed database as a service. SQL Server licenses with Software Assurance are eligible for Azure Hybrid Benefit to help you reduce the costs of moving to Azure SQL Managed Instance.

Migrate to Azure, and get Extended Security Updates at no additional cost

If you are ready to move to the cloud but challenged to upgrade or modernize before the end of the support timeline, Extended Security Updates are available for free in Azure for SQL Server 2014 and 2012 and Windows Server 2012. Secure your workloads for up to three more years after the end of the support deadline by migrating applications and SQL Server databases to Azure Virtual Machines. Free Extended Security Updates are available for Azure Virtual Machines including Azure Dedicated Host, Azure VMWare Solution, Nutanix Cloud Clusters on Azure, and Azure Stack (Hub, Edge, and HCI). Combining Extended Security Updates in Azure with Azure Hybrid Benefit further reduces your costs. With these pricing advantages, AWS is up to five times more expensive than Azure for SQL Server and Windows Server end-of-support workloads.

Stay protected on-premises or in multicloud environments

Another way to stay protected is to upgrade your SQL Server to SQL Server 2022, the most Azure-enabled release yet. Get more out of your data with enhanced security, industry-leading performance and availability, and business continuity through Azure.

Customers who cannot meet the end-of-support deadline and have Software Assurance or subscription licenses will have the option to buy Extended Security Updates to get three more years of security updates.

For SQL Server 2012, Windows Server 2012/R2, and starting this year SQL Server 2014, customers can protect their workloads with Extended Security Updates enabled by Azure Arc. With Azure Arc, organizations will be able to activate and seamlessly deploy Extended Security Updates in on-premises or multicloud environments, right from the Azure portal.

Extended Security Updates for SQL Server enabled by Azure Arc provide visibility of your entire SQL estate, automate repeatable updates and patches, and can enhance security and data governance with Azure services. It is offered as a monthly subscription which will automatically stop when you migrate or upgrade—pay only for what you need. Planned pricing* of SQL Server 2014 Extended Security Updates enabled by Azure Arc* 

Get started planning Windows Server 2012 and SQL Server 2014 end of support

We encourage all our customers running SQL Server 2014, Windows Server 2012, and 2012 R2 to start planning for the end of support. We have migration resources, best practices, and more as well as a rich ecosystem of partners ready to help. To get started, please visit the following pages to learn more:

• Azure migration and modernization center with resources for every stage of your cloud migration.
• What are Extended Security Updates for SQL Server? Covering more details on Extended Security Updates and your options.
• SQL Server and Windows Server Extended Security Updates resource page.
• Extended Security Updates enabled by Azure Arc with details on how to get started with this option.

*Use United States as an example; Actual launch pricing might vary.

** For customers who sign up after the end of support dates  (July 9, 2024), you will receive a one-time bill-back charge from the start of the Extended Security Updates year to your actual start date.

*** Include workloads running on Azure Virtual Machines, Azure Dedicated Host, Azure VMWare Solutions, Nutanix Cloud Clusters on Azure, and Azure Stack Hub/Edge/HCI. Learn more about your options.

The post Protect SQL Server 2014 workloads with Azure flexibility appeared first on Microsoft SQL Server Blog.

Microsoft SQL Server Machine Learning Service

What is SQL Server Machine Learning Services with Python and R?

Learn more

Using the AdventureWorks sample database in a practical example

The AdventureWorks (fictitious) company uses a database that stores data about sales and marketing, products, customers and manufacturing. It also contains a view that joins information about the products, such as the product name, category, price, and a brief description.

Currently, AdventureWorks sends the product name and brief description to a marketing firm to develop a campaign to boost sales. The cost for this marketing specialist is significant, and after looking into the capabilities of Generative Pre-trained Transformers (GPT), the company is curious to know if this AI could be used to create starter-text for brochures, automating the first step of the process. GPT-4 is a powerful language model that can generate coherent and creative text from complex input, or prompts.

Instead of hiring a professional writer, they can use SQL Server Machine Learning Services with Microsoft Azure OpenAI Service to automatically generate high-quality starter ad copy based on the product description and other relevant data. This can save time and money on the marketing budget. This approach also has the added benefit of security, since the database stays local, Stored Procedures have high security granularity, and Azure OpenAI Service does not use the information sent for further training.

This process works on SQL Server Machine Learning Services platforms on Windows, Linux, Containers, and Kubernetes clusters, including virtual machines and SQL Server Managed Instance, from version 2019.

During their research, AdventureWorks discovered that it is important to be cautious when generating text using a GPT model. It is possible that the generated text may not always be appropriate or accurate, so it is important to have a system in place for reviewing and approving any changes before they are published.

The proof-of-concept (PoC) for AdventureWorks involves the following requirements:

1. Create a Stored Procedure to accept the model of a given product.
2. Generate marketing brochure text for that product, highlighting its features and benefits, using Azure OpenAI Service’s GPT-4 model.
3. Ensure that the generated text is safe and factual, and that it can be edited before publishing.

Example code

The POC project for AdventureWorks involves the following steps:

1. Install SQL Server Machine Learning Services on a Microsoft Windows, Linux, or Container environment.
2. Create and deploy a model on Azure OpenAI Service, and obtain the model’s name, keys and endpoints.
3. Install the openai Python package using Azure Data Studio or the sqlmlutils package. This enables the code to use OpenAI API in Python scripts.
4. Write a Python script that uses the sp_execute_external_script stored procedure to query the product’s view, send the product name and description as a combined prompt to the Azure OpenAI API. The script should also specify the parameters for the model, such as the engine name (such as `davinci`), the temperature (a measure of randomness), the max tokens (the maximum length of the generated text), and the stop sequence (a token that indicates the end of the text). The script should also handle any errors or warnings from the API and return the generated text as a result set.
5. Execute the Python script from SQL Server Management Studio or Azure Data Studio and review the generated text for each product. Edit the text as needed or re-run the script with different parameters to get different results.
6. Once the result is satisfactory, wrap the code in a stored procedure to take the product’s model number from the user, and ensure the security chain is verified for the Stored Procedure.
7. In the end-user application, display the result for the ad text to the user for editing, and send the edited result to the database for storage.

With that outline, sample code in an Azure Data Studio Jupyter Notebook has been developed for AdventureWorks, allowing descriptive blocks, code, and persistent results display. After testing and editing, the code can be copied to a Transact-SQL script and deployed to testing and then on to production. The end result produces the following starter text to display to the end user for editing and confirmation before being set to the database:

[sic]

Dear Mountain Bike Enthusiast, Are you looking for a bike that can deliver the performance you need without breaking the bank? Look no further than the Mountain-400-W Silver, 38.  This bike is designed to give you the high-level performance you need to tackle any terrain, without the high price tag. With its responsive and maneuverable design, you’ll be able to navigate even the toughest trails with ease.  But it’s not just about performance – the Mountain-400-W Silver, 38 also offers peace-of-mind when you decide to go off-road. With its sturdy construction and reliable components, you can trust this bike to handle anything you throw at it.  So why wait? Order your Mountain-400-W Silver, 38 today and experience the ultimate in off-road performance at a price you can afford. Don’t settle for anything less than the best – choose the Mountain-400-W Silver, 38 and take your mountain biking to the next level.

Other applications of this approach

As you can see, using Microsoft SQL Server Machine Learning Services with Microsoft Azure OpenAI Service has practical applications. In this example we used it to create a marketing brochure text for various products. This is a powerful and creative way to leverage AI for business needs. The resulting text could be sent to an image-generating service (such as DALL-E) to generate images for the brochure. Other data sets in the AdventureWorks database could be used in other applications, such as investigating sales regions and much more.

However, it is important to be cautious and responsible when using AI models, as they may not always produce accurate or appropriate results. You should always review and edit the generated text before publishing it and follow the safety and ethical guidelines of Microsoft Azure OpenAI Service.

Learn more

• What is SQL Server Machine Learning Services with Python and R?
• What’s new in SQL Server Machine Learning Services?
• The Microsoft Azure OpenAI Service.
• Understanding GPTs.

The post Secure your AI using SQL Server Machine Learning with Microsoft Azure OpenAI Services appeared first on Microsoft SQL Server Blog.

We are excited to share that there are several Accelerated Database Recovery (ADR) enhancements in SQL Server 2022 that further improve the overall availability and scalability of the database, primarily around persistent version store (PVS) cleanup and management.

Overview of Accelerated Database Recovery (ADR) 

ADR improves database availability, especially in the presence of long running transactions, by redesigning the SQL database engine recovery process. ADR is introduced in SQL Server 2019 (15.x) and improved in SQL Server 2022 (16.x). 

ADR is also available for databases in Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse SQL. ADR is enabled by default in SQL Database and SQL Managed Instance and cannot be disabled. 

The primary benefits of Accelerated Database Recovery (ADR) are

Fast and consistent database recovery 

With ADR, long running transactions do not impact the overall recovery time, enabling fast and consistent database recovery irrespective of the number of active transactions in the system or their sizes. 

Instantaneous transaction rollback 

With ADR, transaction rollback is instantaneous, irrespective of the time that the transaction has been active or the number of updates that has performed. 

Aggressive log truncation 

With ADR, the transaction log is aggressively truncated, even in the presence of active long running transactions, which prevents it from growing out of control. 

Note: For more details about ADR, please visit this page: and this video for a high-level overview of ADR and its components.

SQL Server 2022

Learn about the new features on security, platform, management, and more.

Read what's new

New ADR improvements in SQL Server 2022

Multi-threaded version cleanup

In SQL Server 2019 (15.x), the ADR cleanup process is single threaded within a SQL Server instance. Beginning with SQL Server 2022 (16.x), this process uses multi-threaded version cleanup (MTVC), that allows multiple databases under the same SQL Server instance to be cleaned in parallel.

MTVC is enabled by default in SQL Server 2022 and uses one thread per SQL instance. To adjust the number of threads for version cleanup, set ADR Cleaner Thread Count with sp_configure.

USE master;
GO
-- Enable show advanced option to see ADR Cleaner Thread Count
EXEC sp_configure 'show advanced option', '1';
-- List all advanced options
RECONFIGURE;
EXEC sp_configure; 
-- The following example sets the ADR Cleaner Thread Count to 4
EXEC sp_configure 'ADR Cleaner Thread Count', '4';
RECONFIGURE WITH OVERRIDE; 
-- Run RECONFIGURE to verify the number of threads allocated to ADR Version Cleaner.
RECONFIGURE;
EXEC sp_configure;

In the above example, if you configure the ADR Cleaner Count to be four on a sql instance with two databases, the ADR cleaner will allocate only one thread per database, leaving the remaining two threads idle.

Note: The maximum number of ADR Cleaner threads is capped at the number of cores used by the SQL Server instance. For example, if you are running SQL Server on an eight core machine, the maximum number of ADR cleaner threads that the engine can use will be eight, even if the value in the sp_configure is set to a greater value.

User transaction cleanup

This improvement allows user transactions to run cleanup on pages that could not be addressed by the regular cleanup process due to lock conflicts. This helps ensure that the ADR cleanup process works more efficiently.

Reducing memory footprint for PVS page tracker

This improvement tracks persisted version store (PVS) pages at the extent level, in order to reduce the memory footprint needed to maintain versioned pages.

Accelerated Data Recovery cleaner improvements

ADR cleaner has improved version cleanup efficiencies to improve how SQL Server tracks and records aborted versions of a page leading to improvements in memory and capacity.

Transaction-level persisted version store

This improvement allows ADR to clean up versions belonging to committed transactions independent of whether there are aborted transactions in the system. With this improvement PVS pages can be deallocated, even if the cleanup cannot complete a successful sweep to trim the aborted transaction map.

The result of this improvement is reduced PVS growth even if ADR cleanup is slow or fails.

New extended event

A new extended event, tx_mtvc2_sweep_stats, has been added for telemetry on the ADR PVS multi-threaded version cleaner.

Summary

In this blog post, we covered all the exciting ADR improvements that we are including with SQL Server 2022 that further improve the overall availability and scalability of your databases.

Stay tuned as we are currently working on further improvements of the multi-threaded version cleaner that will enable parallelizing version cleanup within databases.

Learn more

For more information, and to get started with SQL Server 2022, check out the following references: 

• Manage accelerated database recovery
• Troubleshoot accelerated database recovery
• SQL Server Accelerated Database Recovery

Read What’s new in SQL Server 2022 for all the new features on security, platform, management, and more. 

The post Accelerated Database Recovery enhancements in SQL Server 2022  appeared first on Microsoft SQL Server Blog.

In SQL Server 2008 Enterprise Edition, backup compression was introduced adding WITH COMPRESSION as an option in the T-SQL backup command. SQL Server backup compression provides the compressed backup option for all streaming SQL Server backup types—full, differential, and transaction log. Using the metadata in the backup file header, the RESTORE command decompresses compressed backup files. Compressing backup data benefits customers in two ways: first, the backup files will consume less storage, and second, since there are fewer bytes being transferred, the backups run faster than uncompressed backups.
 
The default configuration in SQL Server is to not compress backups, but many users change this default globally using sp_configure.

While customers use the current compression standard, the challenge many customers have with the current compression implementation is that the compression ratios are frequently too low to justify the additional compute overhead required to compress the backup files.
 
Additionally, customers cannot offload the compute overhead of the compression task. Many customers in high-volume online transaction processing (OLTP) environments put their business continuity and disaster recovery (BCDR) strategy at risk by delaying their regular backup schedules in order to remain operational for customer workloads.

Intel® QuickAssist (Intel QAT) technology in SQL Server 2022, solves these challenges by providing hardware acceleration and CPU offload capabilities for enhanced compression and decompression functions. This feature is designed to give tangible improvements both in terms of reduced CPU usage and backup completion times, as well as a reduction in storage consumption.

The interface for Intel^® QAT devices uses a software library called QATzip where the QATzip API maintains a built-in software fallback mechanism. This fallback mechanism allows administrators to hot-add/remove devices, better resist hardware failure scenarios, and provide software-based compression where needed.

The SQL Server 2022 feature provides integrated compression/decompression capabilities by using the QATzip library and the associated Intel^® Intelligent Storage Acceleration Library (ISA-L). Intel^® ISA-L is installed alongside QATzip where the presence and enablement of both the QATzip and Intel^® ISA-L assemblies is a prerequisite for enabling instance-level server configuration for Intel^® QAT hardware acceleration.

QATzip currently supports offloading backups to hardware devices, either through a peripheral component interconnect express (PCIe) adapter or as part of the host server chipset, and also supplying a software-based compression capability that can be used if there isn’t hardware available.

The SQL Server 2022 server-scope T-SQL syntax extends the current BACKUP DATABASE command to make it easy for database administrators to choose the desired algorithm to compress backups.

Additionally, the sp_configure configuration option is available to adjust backup compression defaults while introducing a new dynamic management view in order to query the configuration state.

The implementation of this feature surfaces appropriate error log and informational messaging to troubleshoot the Intel^® QAT configuration as well as being able to track the benefits of the Intel^® QAT compression feature over time by surfacing the compression algorithm history in the msdb database.

SQL Server 2022

Learn about the new features on security, platform, management, and more.

Read what's new

Business cases for backup compression with Intel^® QAT

The organizations that will find the most benefit from the Intel^® QAT are those that have high needs around maintaining customer workloads, such as mission critical OLTP and online analytical processing (OLAP) environments, while also ensuring their organization’s data recovery strategy maintains the organizations service level agreements (SLAs). These organizations must maintain application workloads to meet customer expectations while ensuring the organization’s data recovery strategy is never put at risk.

Often these organizations are highly virtualized and are running SQL Server instances at, or very close to, 100 percent processing time to maximize hardware and software licensing investments.

Reference:
Online transaction processing (OLTP) – Microsoft Docs
Online analytical processing (OLAP) – Microsoft Docs

Intel^® QAT driver overview

Customers should download the Intel drivers themselves directly from the Intel QuickAssist supportability page. The supportability page will give an overview of the Intel^® QAT, list driver versions per operating system, and maintain the release notes for the Intel^® QAT drivers.

Note: The official Intel^® QAT Driver for Windows, which for the writing of this documentation is version 1.8.0-0010, is required to be installed separately from the SQL Server 2022 installation.

For the initial release of QAT-enabled backups for SQL Server 2022, the feature will be available for SQL Server 2022 on Windows Server only. As a host operating system, Windows Server 2016, Windows Server 2019, and Windows Server 2022 are supported.

When the drivers are installed and server-scope configuration option is enabled for SQL Server, it is expected that the QATzip library will be available in the C:\Windows\ system32\ path for QATzip and the Intel^® ISA-L will be available in C:\Program Files\Intel\ISAL\* path.

This path is true for both hardware and software-only deployment scenarios.

Customers can install the drivers before the SQL Server 2022 installation in preparation for a disaster recovery site, for system standardization, or after the SQL Server 2022 installation, even without QAT hardware. In either case, it is required for customers to maintain the driver version. 

Preparing a disaster recovery server site

It’s possible to install the QATzip library on a server with no QAT hardware acceleration devices available (virtual or physical). In such cases, SQL Server will still load the QATZip.dll assembly provided the ALTER SERVER CONFIGURATION option for HARDWARE_OFFLOAD option is enabled for QAT.

In this scenario, users can specify the ALGORITHM for COMPRESSION as QAT_DEFLATE and rely on the software fallback provided by the Intel^® ISA-L to perform the compression operation using the same format and encoding as provided by QAT devices.

Below are links to the currently supported Windows Server driver version, the Intel^® QAT release notes, and the Intel^® QAT landing page, which will be used as the primary location for Intel^® QAT support.

Intel^® QuickAssist Technology landing page
Intel^® QuickAssist Technology Driver for Windows* (1.8.0-0010)
Intel^® QuickAssist Technology Driver for Windows release notes 

SQL Server 2022 Edition support for Intel^® QAT

SQL Server 2022 implements different levels of Intel^® QAT support based on the SQL Server edition. The SQL Server 2022 Enterprise edition will use hardware offloading if an Intel QAT device is available, otherwise, it will fall back to software-based compression if the Intel QAT device is not available for any reason.

SQL Server 2022 Standard Edition will only allow Intel^® QAT backups with software-only compression, even if an Intel QAT device is available.

SQL Server 2022 Express Edition will allow Intel^® QAT backups to be restored but will only allow backups to be performed with the default MS_XPRESS algorithm.

Note: The Intel^® QAT drivers are required to be installed in order to perform backups and restores using the Intel^® QAT feature.

SQL Server 2022 configuration for Intel^® QAT for backup compression

In order to back up databases with the Intel^® QAT compression algorithm, it is required to have the drivers installed, and the SQL Server must then be permitted to load the drivers into the SQL Server 2022 process space.

To complete this task, a new option has been added to ALTER SERVER CONFIGURATION in SQL Server 2022 with the HARDWARE_OFFLOAD syntax to enable or disable the use of hardware acceleration for a SQL Server instance. With this configuration being applied, after a restart of the SQL Server service, the Intel^® QAT hardware acceleration technology can be used to provide a new optional backup compression algorithm.
 
Note: The HARDWARE_OFFLOAD option requires a restart of the SQL Server instance to take effect.

After the feature is enabled, every time the SQL Server service starts, the SQL Server process will look for the required user space software library that interfaces with the hardware acceleration device driver API and will load the software assemblies if they are available. The Intel^® QAT user space library is QATzip, which provides a number of features. The QATzip software library is a user space software API that can interface with the QAT kernel driver API. It is used primarily by applications that are looking to accelerate the compression and decompression of files using one or more QAT devices.

In the case of the Windows operating system, there is a complimentary software library to QATzip, the Intel^® Intelligent Storage Acceleration Library (ISA-L). This serves as a software fallback mechanism for QATzip in the case of hardware failure as well as a software-based option when the hardware is not available.

Note: The unavailability of a QAT hardware device will not prevent SQL instances from performing backup or restore operations using the new QAT_DEFLATE algorithm.

Software compression or decompression using Intel^® ISA-L still requires that the QATzip software library is installed and that HARDWARE_OFFLOAD server configuration has been enabled for QAT. This is because SQL Server loads the QATzip.dll assembly provided by QATzip and it handles the fallback to Intel^® ISA-L.
 
Additionally, software-based compression will be available for SQL Server 2022 Standard Edition allowing Standard Edition customers the ability to take advantage of software-based accelerators where SQL Server Enterprise Edition 2022 customers will have the extended capability of offloading backups to an external device.

Enabling Intel^® QAT backup compression

The ALTER SERVER CONFIGURATION statement will be used to enable or disable access to QAT devices. Using the HARDWARE_OFFLOAD statement, as shown below, will persist a bit on the SQL Server boot page to load QAT binaries at startup.

ALTER SERVER CONFIGURATION
 SET HARDWARE_OFFLOAD = ON (ACCELERATOR = QAT)

After executing the statement, users would then restart the SQL Server service to have the QAT binaries loaded into the SQL Server process space.

Note: The default configuration will be to use the Microsoft default MS_XPRESS compression capabilities.

The ALTER SERVER CONFIGURATION statement below will disable the Intel^® QAT feature for SQL Server 2022.

ALTER SERVER CONFIGURATION
 SET HARDWARE_OFFLOAD = OFF (ACCELERATOR = QAT)

Hardware acceleration configuration and availability group replicas

The HARDWARE_OFFLOAD server-scope configuration option will be compatible with Availability Groups. If the setting is enabled on a primary replica, the configuration option will be a persistent server-scope property such that upon failover from a primary to a secondary replica, an attempt will be made to load the correct assemblies on the new primary replica. The recommendation will be to enable the server-scope setting on all replicas after installing the latest Intel^® QAT drivers on each replica.

Verifying Intel^® QAT accelerator configuration

In order to verify the Intel^® QAT accelerator configuration, a number of methods can be used including a new dynamic management view (DMV), sp_configure and the sys.configurations catalog view, and the SQL Server 2022 error log.

sys.dm_server_hardware_offload_config

The SQL Server sys.dm_server_hardware_offload_config dynamic management view can be used to verify the Intel^® QAT configuration state.

The addition of this dynamic management view will make it much easier to verify the SQL Server accelerators that are configured for use, and those that have been confirmed to be loaded. This dynamic management view (DMV) should be the primary method to verify the configuration state of the available accelerators.
 
Example:
SELECT * FROM sys.dm_server_hardware_offload_config

GO

sp_configure and the sys.configurations catalog view

The SQL Server backup compression default behavior can be adjusted. You can change the server default configuration as well as other options. You can enable or disable hardware acceleration, you can enable backup compression as a default, and you can also change the default compression algorithm as by using sp_configure.

The status of these options is reflected in the sys.configurations table.

As mentioned previously, you can discover the hardware offload configuration by querying the sys.dm_server_hardware_offload_config dynamic management view.

Backup compression default

To discover the backup compression default state, you can use the query below: 

SELECT * FROM sys.configurations

WHERE name = 'backup compression default';

Changing this configuration is permitted through the sys.sp_configure stored procedure.

For example:

EXEC sp_configure 'backup compression default', 1; 

RECONFIGURE;

No restart of SQL Server is required for this change to take effect. When this default option is changed to 1 (i.e., WITH COMPRESSION), MS_XPRESS will continue to be the default compression option, provided the backup compression algorithm has not been changed.

Backup compression algorithm

To discover the backup compression algorithm being used, you can use the query below:

SELECT * FROM sys.configurations 

WHERE name = 'backup compression algorithm';

The “configure backup compression algorithm” configuration allows customers to change the default compression algorithm so they can determine if they want Intel^® QAT to be the default compression algorithm for SQL Server.

Backup compression enabled

There will be a global sp_configure option to enable/disable hardware offloading.

Note: The value will be either 0 or 1 and will require a reboot to take effect.

sp_configure ‘hardware offload enabled’, 0

GO

Creating a backup using QAT compression

The T-SQL BACKUP command WITH COMPRESSION has been extended to allow for a specified backup compression algorithm. When using Intel^® QAT for backup compression acceleration, the algorithm QAT_DEFLATE will initiate an Intel^® QAT compressed backup if the drivers are available and the SQL Server configuration has been completed.

Note: The standard compression algorithm will be referred to as MS_EXPRESS and will remain the default compression option.

The ALGORITHM command will be used to specify either of these two algorithms (QAT_DEFLATE, MS_EXPRESS) for backup compression.

The example below will perform backup compression using Intel^® QAT hardware acceleration.

BACKUP DATABASE testdb TO DISK='D:\Backups\testdb.bak'
WITH COMPRESSION (ALGORITHM = QAT_DEFLATE);

Either of these statements will use the default MS_XPRESS compression engine.

BACKUP DATABASE testdb TO DISK='D:\Backups\testdb.bak'
WITH COMPRESSION (ALGORITHM = MS_XPRESS);

BACKUP DATABASE testdb TO DISK='D:\Backups\testdb.bak'
WITH COMPRESSION;

The extension to the T-SQL syntax for backup compression provides the addition of the ALGORITHM option, allowing the options MS_XPRESS (default) or QAT_DEFLATE (Intel^® QAT-based compression) in SQL Server 2022.

The table below gives a summary of the BACKUP DATABASE with COMPRESSION options in SQL Server 2022.

SQL Server 2022 RESTORE process

The RESTORE command does not include a COMPRESSION option as the backup header metadata specifies if the database is compressed and, therefore, the storage engine can restore from the backup file(s) accordingly. The backup metadata will be extended to include the compression type.

Running RESTORE HEADERONLY on a backup without compression and a backup compressed with the default MS_XPRESS algorithm will return result sets similar to the commands below:

RESTORE HEADERONLY
 FROM DISK=’C:\temp\QAT-DEFLATE.bak’
 GO RESTORE HEADERONLY
 FROM DISK=’C:\temp\MS-XPRESS.bak’
 GO

The RESTORE HEADERONLY command will display the compression algorithm along with other information such as the backup name, backup description, backup type, compression state, device type, and other columns. In SQL Server 2022, we will be adding the compression algorithm column.

Backups that have been compressed with either QAT_DEFLATE or MS_XPRESS, and uncompressed backups will all use the same T-SQL syntax RESTORE DATABASE DDL commands.

In the example below, the T-SQL restore syntax is valid for restoring from the testdb.bak backup file, regardless of whether it is an uncompressed backup, or a compressed backup created using QAT_DEFLATE or MS_XPRESS.

RESTORE DATABASE testdb FROM DISK=’E:\Backups\testdb.bak’
WITH RECOVERY;

SQL Server backups compressed using QAT_DEFLATE will support all T-SQL RESTORE operations. The RESTORE {DATABASE|LOG} statements for restoring and recovering databases from backups will support all recovery arguments, such as WITH MOVE, PARTIAL, STOPAT, KEEP REPLICATION, KEEP CDC, and RESTRICTED_USER.

Auxiliary RESTORE commands will also be supported for all backup compression algorithms. Auxiliary RESTORE commands include RESTORE FILELISTONLY, RESTORE HEADERONLY, RESTORE VERIFYONLY, and more.

Note: If the server-scope configuration HARDWARE_OFFLOAD option is not enabled, and/or the Intel^® QAT drivers have not been installed, an error will be surfaced instead of attempting to perform the restore.

To restore an Intel^® QAT compressed backup, the correct assemblies must be loaded on the SQL Server instance initiating the restore operation.

Best practices

The use of a BLOCKSIZE of 65536 (64KB) will be recommended, but options such as BUFFERCOUNT and MAXTRANSFERSIZE will continue to remain dependent on the user’s environment if changes from the default are being made.

Learn more

For more information, and to get started with SQL Server 2022, check out the following references:

Read What’s new in SQL Server 2022 for all the new features on security, platform, management, and more.

Read the Microsoft Integrated acceleration and offloading guidance to get started with Intel^® QuickAssist technology in SQL Server 2022.

Go to the Intel^® QAT portal for the latest Intel information on Intel^® QAT device drivers and application support along with technical documentation and whitepapers.

The post SQL Server 2022: Intel® QuickAssist Technology overview appeared first on Microsoft SQL Server Blog.

The SQL Server platform has been successfully driving transformative business results with Microsoft customers across industries for over 25 years, enabling breakthrough innovation through unique capabilities like industry-leading performance¹ and security built-in.

Microsoft’s continuous investments in SQL Server enable more flexibility with options for companies looking to simply save on costs, or to really transform their current SQL Server–based applications and data estate.

These options include:

• Azure SQL Managed Instance, a fully managed cloud database that is compatible with SQL Server back to version 2008.
• A serverless compute version that stops, starts, and scales with your workloads.
• Elastic pools for managing groups of databases with ease.
• Hyperscale, the flexible, high-performance service tier that scales to meet demand—all unique to Azure.

Reasons for choosing Azure over other clouds for your SQL Server applications go beyond having access to more options to meet your business and technical needs. SQL Server running in Azure also enables higher savings than AWS with faster performance¹, lower downtime^2, and AI-based, automatic tuning that continuously maintains peak database performance. Azure SQL is based on the SQL Server database platform, the least vulnerable commercial database in the NIST National Vulnerability Database for over 10 years³.

With Microsoft you can also decide where you want to run your SQL Server databases in your own datacenter, the cloud of your choice, or hybrid and multicloud deployments with Azure Arc–enabled SQL Managed Instance and the recently launched SQL Server 2022.

SQL Server 2022

The most Azure-enabled release yet, with continued performance, security, and availability innovation.

Learn more

If you’re considering moving to the cloud, besides getting the most out of a cloud-based SQL Server, you can take the first step with Azure free of charge. I’m pleased to share that the new Microsoft SQL + Apps Migration Factory initiative can support your low-friction migrations of SQL Server and Windows Server–based applications at no cost.

The Microsoft SQL + Apps Migration Factory is a delivery model offered through the Unified/Customer Success resources that gathers skilled professionals, comprehensive processes, and tools to help you plan, execute, and migrate qualifying SQL Server workloads and associated application virtual machines (VMs) to Azure with near-zero code changes. In other words, it blends the technical components of cloud migration with the business and human components—so critical to helping you achieve your goals.

With this offer, you can get specialized assistance to help choose the migration approach that suits your business, such as moving a single workload or having a migration in phases, as well as guided savings and application assessments. All with the main goal of having a seamless and efficient workload migration without business disruption. Once your environment is migrated, our team can also help ensure the SQL Server workload is properly secured and performs adequately.

Hundreds of customers have already signed up to leverage this Customer Success Factory delivery model to accelerate migrations of known low-friction scenarios that do not require refactoring. Typical use cases favored by customers have included out-of-support SQL Server and Windows Servers, dev-test environments, disaster recovery (DR) environments, and applications with a compelling business need to migrate to the cloud in the near term that do not require refactoring or rewriting. This approach also minimizes the risk of business disruption as the migration journey is jumpstarted with these low-friction scenarios and advancing your digital transformation priorities in a phased manner. We like to think of this approach as eating your cake one slice at a time and we can support slices as small or as big as you need. You can even come back for seconds and thirds at any time to advance your migration and modernization journey in an iterative and controlled manner.

So, if your SQL Server estate, including associated Windows Server–based apps, has these or other scenarios that are ready to migrate in the near term without refactoring to optimized Azure SQL and Azure VM destinations, talk to your Microsoft account team about the Factory delivery model or apply now to get started. 

Learn more

• Azure SQL | Microsoft Azure
• Extended Security Updates for SQL Server and Windows Server 2008 and 2008 R2 | Microsoft
• Azure Database Migration Guides | Microsoft Learn

¹Microsoft Azure SQL Managed Instance benchmark vs. AWS RDS for SQL Server using Azure Hybrid Benefit pricing, May 2022

²Service Level Agreements Summary | Microsoft Azure

³National Vulnerability Database (NVD), National Institute of Standards and Technology

⁴Subject to the limitations described in the full SQL + Apps Migration Factory program specifications here, and provided that the SQL Server workloads are low complexity with no code changes, Microsoft agrees to assess and migrate SQL Server databases and SQL Server-associated applications from your datacenter or AWS EC2 to Azure at no cost to customer. Migrations must be completed by June 30, 2023. 

The post Easily migrate SQL Server applications to the cloud with Azure appeared first on Microsoft SQL Server Blog.