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.

We’ve been on the road the last couple of weeks at MVP Summit, SQLBits and Fabric Con, connecting with customers talking about the value of migration and modernization. We want to dig into specifically, how Azure can deliver real business value through cost optimization and streamlined productivity for their Windows Server and SQL Server deployments when they migrate to Azure. 

We’ve helped countless organizations migrate their SQL Server and Windows workloads to Azure a critical 1^st step in any transformation initiative. The move can help improve cybersecurity posture and business continuity, boost productivity, and lay the foundation for AI and other highly scalable data innovations, while automating updates, backups, and other time-consuming IT tasks. 

Modernize and lower total cost of ownership (TCO) 

Migration is a business strategy that pays off. In The Business Value of Microsoft Azure SQL Database and Azure SQL Managed Instance Workload,¹ organizations that migrated to Azure SQL Managed Instance and Microsoft Azure SQL Database can get up to 406 percent return on investment over 3 years and can expect a 30-percent reduction in TCO over 5 years, protecting an additional $6.85 million in annual revenue.

A separate study found that customers that migrated both Windows Server and SQL Server workloads to Azure generated more value. According to The Business Value of Microsoft Azure for SQL Server and Windows Server Workloads,² by optimizing costs, operations, and business opportunities, companies gained $15.85 million in total annual benefits while also increasing IT security efficiency by 43 percent with cloud tools and automation.

Azure SQL

Migrate, modernize, and innovate with the modern SQL family of cloud database services.

Start today

A smooth path to migration, 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 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. And as we shared with customers at SQLBits, there’s now an even more powerful option available for customers looking to leverage the full PaaS experience. Azure SQL Managed Instance Next-gen GP  brings significantly improved performance and scalability to power up your existing Azure SQL Managed Instance fleet, and help bring more mission-critical SQL workloads to Azure. With close to 100 percent feature compatibility with SQL Server, Azure SQL Managed Instance is the recommended choice to migrate and modernize SQL apps at scale and at your own pace.

Another option many of our customers start with is by running their Windows Server workloads on Azure Virtual Machines, benefiting from a simplified, managed experience and cloud-native support for SQL Server, .NET apps, and Remote Desktop Services. Or you can modernize your entire Windows Server estate, choosing from more than 200 Azure services and capabilities, including support for hybrid environments. 

Take the first step or the next: You have choices

When it comes to migration, Azure meets you where you are with options for moving on-premises workloads and for developing new cloud solutions. For example, many organizations start by moving Windows Server workloads to Azure Virtual Machines, enabling them to easily scale to support new developments and more efficiently manage peak loads. Hokkoku Bank took this step, migrating its Windows Server–based estate to Azure as part of a cloud-first initiative. Azure supports the bank’s modernization plans and helps provide a disaster recovery solution in an earthquake-prone region.  

Correios de Portugal, the country’s postal service, migrated its Windows Server workloads to Azure Virtual Machines backed by Azure SQL, which provides a smooth path to a cost-effective, highly scalable, fully managed PaaS database. It’s the best choice for modernizing your apps and getting the most out of your existing investments.

Many of our database customers move to SQL Server on Azure Virtual Machines for the cost benefits on top of the scalability and resilience of Azure. As an example, healthcare software manufacturer Allscripts migrated on-premises applications to Azure SQL Database Managed Instance when possible, but another 600 on-premises VMs needed a different migration approach. Allscripts moved them to SQL Server on Azure Virtual Machines, a quick, low-risk step for workloads it plans to optimize and modernize later. The lift-and-shift approach can be an easy first   step in your cloud journey.

Azure also offers hybrid solutions that bridge your on-premises and cloud resources. For example, you can move or extend on-premises VMware environments using Azure VMWare Solution. You can even use the free Windows Admin Center tool to manage across Windows Server environments—physical, virtual, on-premises, in Azure, or in a hosted environment—at no additional cost. To get started with a Windows Server migration, start discovering and assessing on-premises resources using the free Azure Migrate tool.

Watch the Migrate to Innovate digital event on demand and learn the business benefits of migrating to Azure.

Try it for free 

If you want to know how your workload will perform before migrating, try these Azure offers and get started building that proof-of-concept.  

• Try Azure SQL Managed Instance for free. For 12 months, you can get up to two instances per Azure subscription, 750 vCore hours of compute per month, and 32 GB data storage and 32 GB backup storage per month. 

• Try Azure SQL Database for free. Test and develop applications or run small production workloads for free. This offer provides the first 100,000 vCore seconds, 32 GB of data, and 32 GB of backup storage per month at no charge for the lifetime of your subscription. 

• Try Windows Server in the cloud with an Azure free account. Extend your existing Windows Server datacenter and try out popular services free for 12 months. 

Learn more about Azure SQL

Stay tuned for more migration announcements in the coming months. To get started now: 

• 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.

• Review the benefits of migration in Better on Azure: The benefits of using Azure for Windows Server and SQL Server workloads. 

• Discover why cloud economics make sense and get greater return on your investment. 

1. IDC report, The Business Value of Microsoft Azure SQL Database and Azure SQL Managed Instance Workloads, IDC #US51073123, August 2023. 
2. The Business Value of Microsoft Azure for SQL Server and Windows Server Workloads

The post Why migrate Windows Server and SQL Server to Azure: ROI, innovation, and free offers appeared first on Microsoft SQL Server Blog.

SQL Server Developer Edition

SQL Server comes in several editions and Developer Edition has for many years been the choice of many developers to build applications and test SQL Server with no license costs and the full functionality of SQL Server. As the documentation describes SQL Developer Edition, it “includes all the functionality of Enterprise edition but is licensed for use as a development and test system, not as a production server.” Microsoft has had some questions about exactly what the term means “as a development and test system.” Let’s turn to the SQL Server Licensing Guide for the details.

The licensing guide states that SQL Server Developer Edition is “…licensed for development, test, and demonstration purposes only. SQL Server Developer Edition may not be used in a production environment”. Notice the addition of the word “demonstration purposes.” This means, for example, that you can use SQL Server Developer Edition to do demos at your favorite conference as a presenter.

There are some other points in the licensing guide worth noting, including:

• You can run Developer Edition for development, testing, and demonstration purposes on an unlimited number of devices.
• You cannot use Developer Edition to build test data and move that same data into production. But you can restore a production set of data backup for testing purposes. The intention here is to prevent users from using Developer Edition as a production server but calling the data test data.
• Production environments include any system that is accessed by end-users for anything more than acceptance testing, environments that connects to production systems (such as Linked servers), disaster recovery or backups of production systems, and environments that are “rotated” into production at any point in time.

One of the questions Microsoft often gets is how to find previous versions of the SQL Server Developer Edition. Therefore, you can use the following shortcuts to help you find versions back to SQL Server 2016:

• SQL Server 2022 Developer Edition
• SQL Server 2019 Developer Edition
• SQL Server 2017 Developer Edition
• SQL Server 2016 Developer Edition

SQL Server containers

One of the most amazing stories about SQL Server for developers is containers. SQL Server containers are pre-installed versions of SQL Server on Linux that can be used for developers on macOS, Linux, or Windows for a consistent experience. Furthermore, if you just run a SQL Server container by default it is configured for SQL Server Developer Edition. Now your organization can use a distributed development approach using a consistent SQL Server deployment instead of relying on development servers. SQL Server containers provide a convenient method to test your application against multiple versions of SQL Server.

You can find out more about how to use SQL Server containers. This includes details about how to find all container images for SQL Server 2017, 2019, and 2022 from the Microsoft Artifact Registry.

Visual Studio Subscriptions

Visual Studio Subscriptions provides developers options to use licenses for SQL Server Standard and Enterprise editions to develop and test applications for non-production scenarios. One scenario that can help developers is the ability to develop and test applications against SQL Server Standard Edition.

One of the benefits of a Visual Studio Subscription is Azure credits. Azure credits allows developers to receive free credits towards use of Azure services such as SQL Server (any edition) in Azure Virtual Machine or Azure SQL Database for development and testing purposes. No credit card is required to use these credits and it provides an option to develop or test SQL in Azure. While the number of monthly credits may feel limited, developers may find this benefit of their Visual Studio subscription as a method to get started in the cloud.

Azure Dev/Test pricing

If Azure credits are not enough for your development or testing needs, Visual Studio subscribers have a benefit available to them called Azure Dev/Test pricing. Azure Dev/Test pricing, offered exclusively to Visual Studio subscribers, allows developers to save money to develop and test applications with Azure services.

Azure Dev/Test pricing offers three plans:

• Enterprise Dev/Test—offered exclusively for customers with a Microsoft Enterprise Agreement (EA).
• Azure Plan for DevTest—offered exclusively to customers with a Microsoft Customer Agreement.
• Pay-As-You-Go Dev/Test—an option for customers without an agreement with Microsoft.

Each plan has different requirements and offers different benefits per your needs. Azure credits are not designed to be used in combination with Azure Dev/Test pricing.

Azure Dev/Test pricing not only provides significant cost savings for dev/test scenarios for Azure Services such as Azure SQL Database or Azure SQL Managed Instance, but also allows customers to use software that is included in their Visual Studio subscription for free in an Azure Virtual Machine.

For example, if your Visual Studio subscription includes licensing for SQL Server, you can deploy SQL Server Standard or Enterprise Edition in an Azure Virtual Machine and not incur licensing charges from Azure. This could provide a method for you to test your application against SQL Server Standard Edition in a cost effective manner.

You can also optimize your cost savings with both Azure Dev/Test pricing and Azure Hybrid Benefit licensing. You can use Azure Dev/Test pricing for your dev/test workloads and Azure Hybrid Benefit licensing for production workloads. Azure Hybrid Benefit is a licensing offer that helps you migrate and save to Azure by using your existing SQL Server licenses.

It is important to note that Azure Dev/Test labs is not related to Azure Dev/Test pricing. Azure Dev/Test labs is a free Azure service used to help you optimize provision of development and test environments. You use whatever Azure subscription pricing model is available for Azure services within your Dev/Test lab deployments.

Try Azure SQL Database for free (preview)

Microsoft has recently announced a new free offer for Azure SQL Database now in preview. This offer is a major milestone for developers or any user of Azure SQL. This free offer provides a monthly set of cores and storage for free for a single Azure SQL Database per subscription for the lifetime of the Azure subscription.

The most significant aspect of this offer is that it is not limited for development and testing purposes. While the number of free core and storage usage may not make it viable for medium or large production workloads, it can be an excellent option for developers to have a “developer edition” of Azure SQL Database or for small workloads.

The free Azure SQL Database offer is based on the General Purpose service tier using Serverless compute so it carries all of the capabilities and limits that go with this deployment choice.

Provisioning an Azure SQL Database for the free offer is easy.

1. Go to the Azure portal provisioning page for Azure SQL Database.
2. On the Basics tab, look for the banner that says, “Want to try Azure SQL Database for free?“, select the Apply offer button. If you do not see this banner, you may already have a free database provisioned for your subscription.

Try Azure SQL Managed Instance for free (preview)

In some cases, developers need the full managed instance capabilities of SQL Server in the cloud so they choose Azure SQL Managed Instance. Developers can now save costs to develop and test applications with Azure SQL Managed Instance with a new free offer.

This offer, now in preview, includes the ability to try Azure SQL Managed Instance free of charge for the first 12 months to get:

• A General Purpose service tier instance with up to 100 databases
• 750 vCore hours of compute every month
• 64 GB of storage

This is a major leap forward for developers who are looking to test a migration strategy with Azure SQL Managed Instance.

It is easy to get started with this free offer. Use the Azure portal to create the new free Azure SQL Managed Instance. To create your free instance, follow these steps:

• Go to the provisioning page for Azure SQL Managed Instance in the Azure portal.
• On the Basics tab, look for the “Want to try Azure SQL Managed Instance for free?” banner and select the Apply offer button. Check the Estimated costs per month option to validate the free offer has been applied to your instance.

When you are ready to migrate, our Managed Instance Link feature provides a true online migration path to modernize your SQL Server investments.

Other options for developers to save costs

As you can see SQL developers have several options to develop and test applications with SQL for free or substantial savings both on-premises and in the cloud. Microsoft wants all developers using SQL to have the most cost-effective options to build applications that work with SQL, edge to cloud, and the latest innovations.

Here are a few other tips for you to save costs with Azure SQL:

• Shutdown any virtual machine when you are not using it to save costs. You can use Azure Dev/Test labs to help automate this process. Be sure to use the right shutdown methods to save costs.
• Use Azure SQL Database Serverless with the autopause feature to save costs when the database is idle.
• Take advantage of a new pricing tier for Azure SQL Database Hyperscale.
• Use the Start/Stop feature for Azure SQL Managed Instance to pause compute and only pay for storage costs.

Learn more about SQL Server developer experiences

To learn more about some of the exciting new innovations for developers for SQL check out these resources:

• Discover more about the SQL Developer Experience: Beyond RDBMS.
• Increase your knowledge about intelligent applications with Azure SQL Database.
• Learn more about SQL Server for Developers.

The post How SQL developers can maximize savings 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.

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.

Time series data is a set of values organized in the order in which they occur and arrive for processing. Unlike transactional data in SQL Server, which is not time-based and may be updated often, time series data is typically written once and rarely, if ever, updated.

Some examples of time series data include stock prices, telemetry from equipment sensors on a manufacturing floor, and performance metrics from SQL Server, such as CPU, memory, I/O, and network utilization.

Time series data is often used for historical comparisons, anomaly detection and alerting, predictive analysis, and reporting, where time is a meaningful axis for viewing or analyzing data.

Time series capabilities in SQL Server were introduced in Azure SQL Edge, Microsoft’s version of SQL Server for the Internet of Things (IoT) which combines capabilities such as data streaming and time series with built-in machine learning and graph features.

SQL Server 2022

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

Learn more

With SQL Server 2022 and Azure SQL, we’ve brought time series capabilities to the entire SQL Server family. Time series capabilities in SQL Server consist of enhancements to existing T-SQL functions for handling NULL values, plus two new functions that make working with temporal-based data easier than ever.

Create contiguous ranges with GENERATE_SERIES

When analyzing time series data, it’s common to create a contiguous set of datetime values in evenly spaced intervals (for example, every second) within a specific range. One way to accomplish this is by creating a numbers table, also known as a tally table, which contains a set of consecutive numbers between a lower and upper bound. The numbers in the table can then be used in combination with the DATEADD function to create the range of datetime values.

Prior to SQL Server 2022, creating a numbers table usually involved some form of common table expressions, CROSS JOIN of system objects, looping, or some other creative T-SQL. These solutions are neither elegant nor efficient at scale, with additional complexity when the step between interval values is larger than 1.

The GENERATE_SERIES relational operator in SQL Server 2022 makes creating a numbers table simple by returning a single-column table of numbers between a start and stop value, with an optional parameter defining the number of values to increment/decrement between steps in the series:

GENERATE_SERIES (start, stop [, step ])

This example creates a series of numbers between 1 and 100 in steps of 5:

SELECT value
FROM GENERATE_SERIES(1, 100, 5);

Taking this concept one step further, the next example shows how GENERATE_SERIES is used with DATEADD to create a set of values between 1:00 PM and 2:00 PM in 1-minute intervals:

SELECT DATEADD(minute, s.value, 'Dec 10, 2022 1:00 PM') AS [Interval]
FROM GENERATE_SERIES(0, 59, 1) AS s;

If the step argument is omitted, a default value of 1 is used when computing interval values. GENEATE_SERIES also works with decimal values, with a requirement that the start, stop, and step arguments must all be the same data type. If start is greater than stop and the step is a negative value, then the resulting series will be a decrementing set of values. If start is greater than stop and the step is positive, an empty table will be returned.

Finally, GENERATE_SERIES requires a compatibility level of 160 or higher.

Group data in intervals with DATE_BUCKET

Time series data is often grouped into fixed intervals, or buckets, for analytical purposes. For example, sensor measurements taken every minute may be averaged over 15-minute or 1-hour intervals. While GENERATE_SERIES and DATEADD are used to create the buckets, we need a way to determine which bucket/interval a measurement belongs to.

The DATE_BUCKET function returns the datetime value corresponding to the start of each datetime bucket for an arbitrary bucket size, with an optional parameter to define the origin from which to calculate each bucket. If no origin is provided, the default value of Jan 1, 1900, will be used as the origin date:

DATE_BUCKET (datepart, number, date, origin)

The following example shows the buckets for Dec 10, 2022, for several date parts with a bucket size of 1 and an origin date of Jan 1, 2022:

DECLARE @date DATETIME = 'Dec 10, 2022 12:05 PM';
DECLARE @origin DATETIME = 'Jan 1, 2022 12:00 AM';

SELECT 'Now' AS [BucketName], @date AS [DateBucketValue]
UNION ALL
SELECT 'Year', DATE_BUCKET (YEAR, 1, @date, @origin)
UNION ALL
SELECT 'Quarter', DATE_BUCKET (QUARTER, 1, @date, @origin)
UNION ALL
SELECT 'Month', DATE_BUCKET (MONTH, 1, @date, @origin)
UNION ALL
SELECT 'Week', DATE_BUCKET (WEEK, 1, @date, @origin)
UNION ALL
SELECT 'Day', DATE_BUCKET (DAY, 1, @date, @origin)
UNION ALL
SELECT 'Hour', DATE_BUCKET (HOUR, 1, @date, @origin)
UNION ALL
SELECT 'Minutes', DATE_BUCKET (MINUTE, 1, @date, @origin)
UNION ALL
SELECT 'Seconds', DATE_BUCKET (SECOND, 1, @date, @origin)

Notice how the date bucket value for the Week date part is Dec 10, 2022, which is a Saturday. That’s because the provided origin date (Jan 1, 2022) is also a Saturday. (Note the default origin date of Jan 1, 1900, is a Monday). Therefore, when working with the Week date part, if you want your Week bucket to begin on a Sunday then be sure to use a known origin that falls on a Sunday.

Where DATE_BUCKET becomes especially useful is for bucket sizes larger than 1, for example when grouping data in 5-minute or 15-minute buckets.

SELECT 'Now' AS [BucketName], GETDATE() AS [BucketDate]
UNION ALL
SELECT '5 Minute Buckets', DATE_BUCKET (MINUTE, 5, GETDATE())
UNION ALL
SELECT 'Quarter Hour', DATE_BUCKET (MINUTE, 15, GETDATE());

DATE_BUCKET provides an easy way to determine which time-based interval a timestamped measurement belongs to using any arbitrary-sized interval.

Gap analysis with FIRST_VALUE and LAST_VALUE

FIRST_VALUE and LAST_VALUE are not new functions to SQL Server 2022; what is new is how NULL values are handled. In previous versions of SQL Server, NULL values are preserved.

When working with time series data, it’s possible to have gaps between measurements. Ideally, gaps are filled in with an imputed value. When using FIRST_VALUE and LAST_VALUE to compute the value corresponding to an interval, preserving NULL values isn’t ideal.

In the following example, a series of sensor readings taken at 15-second intervals has some gaps:

If analyzing the data in 1-minute intervals (using DATE_BUCKET), the default value returned by FIRST_VALUE will include the null values:

SELECT [timestamp]
   , DATE_BUCKET(MINUTE, 1, [timestamp]) AS [timestamp_bucket]
   , SensorReading
   , FIRST_VALUE (SensorReading) OVER ( 
PARTITION BY DATE_BUCKET(MINUTE, 1, [timestamp]) 
ORDER BY [timestamp] 
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS [Default (RESPECT NULLS)]
FROM MachineTelemetry
ORDER BY [timestamp];

FIRST_VALUE and LAST_VALUE include new syntax (IGNORE NULLS or RESPECT NULLS) in SQL Server 2022 which allows you to decide how NULL values should be handled:

FIRST_VALUE ( [scalar_expression ] )  [ IGNORE NULLS | RESPECT NULLS ]
    OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )

RESPECT NULLS is the default behavior and will include null values in the result when computing the first or last value within a partition. Specifying IGNORE NULLS will cause NULL values to be excluded when computing the first or last value over a partition.

SELECT [timestamp]
   , DATE_BUCKET(MINUTE, 1, [timestamp]) AS [timestamp_bucket]
   , SensorReading
   , FIRST_VALUE (SensorReading) IGNORE NULLS OVER ( 
PARTITION BY DATE_BUCKET(MINUTE, 1, [timestamp]) 
ORDER BY [timestamp] 
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS [Default (RESPECT NULLS)]
FROM MachineTelemetry
ORDER BY [timestamp];

The new options for IGNORE NULLS and RESPECT NULLS allow you to decide how null values should be treated when analyzing your data.

Learn more

Get started with SQL Server 2022 today. For more information and additional examples, visit the following resources:

Microsoft Learn

• GENERATE_SERIES
• DATE_BUCKET
• FIRST_VALUE
• LAST_VALUE

GitHub examples

• Time Series T-SQL functions in SQL Server 2022.

The post Working with time series data in SQL Server 2022 and Azure SQL appeared first on Microsoft SQL Server Blog.

Part of the SQL Server 2022 blog series.

Traditionally, data to serve analytical systems have been extracted from operational data stores using custom-built extract, transform, and load (ETL) processes. These processes are often long-running, exert pressure on the source systems, and only run periodically in batch mode. While this kind of latency and overhead may be acceptable for some workloads, more and more companies are finding themselves in a place where they need to do analytics over operational data closer to real-time—something that traditional ETL systems cannot support.

Enter Azure Synapse Link for SQL.

Azure Synapse Link for SQL provides an automated way to extract data from source operational systems without having to build custom ETL processes. Some of the benefits of Azure Synapse Link for SQL are:

• Low code/no code solution: With Azure Synapse Link for SQL, you don’t need to build custom processes to extract the data and load it into an analytical system. You choose the tables that you want to replicate, specify how you want them stored in the target Azure Synapse Analytics dedicated SQL pool, and Azure Synapse Link for SQL takes care of the rest.
• Minimal impact on the source systems: We have strived to minimize the impact of data extraction from the source system. Where a traditional ETL process will run queries against the source tables, which can get expensive, Azure Synapse Link for SQL uses the new change feed functionality built into SQL Server 2022 and Azure SQL Database to get the data without having to run custom queries.
• Near-real-time data movement: Data is continually moved from the source systems into the Azure Synapse Analytics environment. Optionally, you can switch to “scheduled mode” if you don’t need near-real-time data movement.

How does it work?

Azure Synapse Link for SQL is powered by the new change feed functionality that has been added to SQL Server 2022 and Azure SQL Database. This functionality allows us to monitor tables for changes as they happen without the additional overhead that is brought along by a change data capture (CDC)–based data movement solution.

When a transaction is committed on a table that is being replicated by Azure Synapse Link for SQL, that transaction is written into a “landing zone,” which is a Gen2 Azure Data Lake storage (ADLS) account. From there, an ingestion service picks up the data and loads it into an Azure Synapse Analytics dedicated SQL pool. Once the data lands there, you can query the data like any other dedicated SQL pool.

Who will benefit?

Here are some examples of scenarios that would benefit from Azure Synapse Link for SQL:

• Database consolidation: Azure Synapse Link for SQL allows you to bring data from multiple source databases together into a single dedicated SQL pool for analytics. Whether you have multiple tenant databases that you want to use for market-based analytics, or you have grown by acquisition and have multiple source systems to bring together for analytics, Azure Synapse Link for SQL can bring all of that data together into a unified analytical platform.
• Hybrid on-premises/cloud: Since Azure Synapse Link for SQL supports both Azure SQL Database and SQL Server 2022, you can bring data into a common analytical system from wherever it lives.
• Near-real-time extension: If you have an ETL system that meets most of your needs but have a few tables where you want data to arrive closer to real-time, you could use Azure Synapse Link for SQL to transfer those tables from the source systems into the Azure Synapse Analytics dedicated SQL pool alongside the data that is processed in your nightly ETL system, and perform reporting an analytics tasks over all of the data.

How to learn more

• Read our technical blog post from the public preview launch.
• Check out our “getting started” documentation.
• Dig in with our How-To guides:
  • SQL Server 2022
  • Azure SQL Database

The post Azure Synapse Link for SQL appeared first on Microsoft SQL Server Blog.

This year, DPS 2022 features eight tracks focusing on Azure Data: 

• Architecture 
• Azure Data Administration 
• Azure Data Development 
• Business Intelligence & Advanced Analytics 
• Data Science (AI/ML)
• Industry Solution 
• SQL Server 2022 
• Professional Development 
• Student Track 

The virtual platform offers a live Q and A session, a networking lounge, and a community zone. Additionally, attendees will receive lifetime on-demand access to session recordings. 

DPS 2022 offers an incredible opportunity to learn directly from our engineering teams, who will share the latest advances and insights on the Azure Data platform. 

• Bob Ward and Buck Woody from the Microsoft Data Platform team will deliver the keynote together. The keynote will highlight the latest innovations across the Microsoft Azure Data platform and share customer case studies. Bob and Buck will show you how to meet the needs of developers across the spectrum to leverage the new tools, processes, and platforms in the Intelligent Data Platform to create a data culture in your organization. Bob will go deep on the newest release in the Intelligent Data Platform—SQL Server 2022. 
• Microsoft Azure Data engineering teams will deliver over 65 sessions at DPS 2022. Hear the latest from the people who develop the tools you use every day and engage in live discussions. 
• Visit the virtual expo hall where you can connect with our team across SQL Server, Azure SQL, Microsoft Learn, Power BI, and more. 

Register now for a week of free training at the Data Platform Virtual Summit and receive all the session recordings also for free (streaming access). 

The post Register today—Free Azure Data training at Data Platform Summit 2022 appeared first on Microsoft SQL Server Blog.

We are pleased to introduce you to ledger, a new technology that uses the power of blockchain to offer a tamper-proof record of data modifications. Currently in Azure SQL, this feature is also part of the SQL Server 2022 release.

We’re making the data in SQL verifiable using the same cryptographic patterns seen in blockchain technology, while keeping the flexibility and performance of a traditional database. Ledger is centrally managed, and you can cryptographically attest to other parties that your data can be trusted and hasn’t been tampered with.

The ledger and the historical data are managed transparently, offering protection without any application changes. In addition, historical data is maintained in a relational form to support SQL queries for auditing, forensics, and other applications.

Learn more

Make sure to read the full blog post, Ledger now generally available in Azure SQL Database, to learn more about ledger and download the SQL Server 2022 preview today.

The post Establish digital trust in your data with ledger in SQL Server 2022 appeared first on Microsoft SQL Server Blog.