When onboarding your SQL Server instance to Azure Arc, there are some networking prerequisites that need to be met. The prerequisites are documented here in detail Prerequisites – SQL Server enabled by Azure Arc | Microsoft Learn

Some common errors when the prerequisites are not met are:

SSL Errors: 

System.Net.Http.HttpRequestException: The SSL connection could not be established, see inner exception.
—> System.IO.IOException: Unable to read data from the transport connection:
A connection attempt failed because the connected party did not properly respond after a period of time, or established connection
failed because connected host has failed to respond.. —> System.Net.Sockets.SocketException (10060)A connection attempt failed
because the connected party did not properly respond after a period of time, or established connection failed because connected host
has failed to respond
The SSL connection could not be established, see inner exception.      
—> System.IO.IOException: Unable to read data from the transport connection: An existing connection was forcibly closed by the
remote host..      —> System.Net.Sockets.SocketException (10054 An existing connection was forcibly closed by the remote host.  
     — End of inner exception stack trace —        
at System.Net.Sockets.Socket.AwaitableSocketAsyncEventArgs.ThrowException(SocketError error, CancellationToken cancellationToken)
at System.Net.Sockets.Socket.AwaitableSocketAsyncEventArgs.System.Threading.Tasks.Sources.IValueTaskSource<System.Int32>.GetResult(Int16 token)
at System.Net.Security.SslStream.<FillHandshakeBufferAsync>g__InternalFillHandshakeBufferAsync|189_0[TIOAdapter](TIOAdapter adap, ValueTask`1 task, Int32 minSize)

SSL Error Causes:

1. Check if URLs listed on the prerequisites are blocked in the environment. Connectivity to URLs listed on this document Troubleshoot connectivity to data processing service and telemetry endpoints – SQL Server enabled by Azure Arc | Microsoft Learn are necessary for onboarding to succeed.

2. Check to see if there are any proxies or firewalls in the network path. Proxies or firewalls have the ability to do TLS inspection which can affect SSL/TLS connections. If  browser, curl, or openssl is showing a third party software that does TLS inspection or similar, and the client does not trust the firewall certificates, it can lead to TLS/SSL issues.

​Microsoft Tech Community – Latest Blogs –Read More 

Today we are happy to announce the public preview of Windows on AKS GPU support! This feature aims to provide customers with the options of GPU compute-intensive workloads. A few examples of where a GPU supported node would benefit workloads are video encoding, machine learning, and large simulations. Through this release we hope to increase the parity between Windows and Linux on AKS.  

What is it? 

GPU support has been accomplished by enabling Windows node pools in AKS to support GPU workloads. This release will support all AKS Windows SKUs releases. As for the GPU support, there will be NVIDIA’s CUDA and GRID drivers. The current architecture installs a specific GPU Driver for each VM size. 

Prerequisites/High level Call outs for Enabling GPU Support 

Workload and driver compatibility are essential to deploying Windows nodes with GPU support. Please verify the workload is compatible with the driver installed to the VM Size.  

VM Size 

Driver Type 

NC series 

CUDA 

NV, ND 

GRID 

Required For Setup 

Kubernetes version 1.29.0 or greater is required for set up. 
Updating an existing Windows node pool to GPU isn’t supported. 
For AKS node pools, we recommend a minimum size of Standard_NC6s_v3.
The NVv4 series (based on AMD GPUs) aren’t supported on AKS.

Optional Opt Out of Configuration 

Customer can opt out of auto driver installation by using:     

      –skip-gpu-driver-install flag 

In Closing 

To get started you can follow a detailed guide to show step-by-step instructions here. 

We would love to hear your feedback and suggestions on this new feature. Thank you for using Windows on AKS. We hope you enjoy using GPU supported nodes.  

​Microsoft Tech Community – Latest Blogs –Read More 

We’re excited to announce that in April, Azure will be offering customers the ability to optimize GPU compute costs by enabling hibernation on Virtual Machines (VMs). With this feature, users can hibernate their VMs, pausing compute usage while preserving in-memory states. During hibernation, customers will only incur costs for storage and networking resources, significantly reducing compute expenses. When needed, VMs can be resumed effortlessly, allowing applications and processes to seamlessly pick up from their last state. 

Use cases 

Specifically for GPU Virtual Machines, hibernation offers compelling use cases and is an effective cost management strategy particularly in two key scenarios: 
 

Optimizing GPU workstations: Pause GPU VMs during off-hours to conserve resources and resume seamlessly when needed, without the need to reopen applications. 
 
Efficient Workflows for long running VMs: For long-running GPU-intensive tasks, hibernating after prewarming tasks ensures quick start-up times and efficient use of GPU resources. 

Hibernation availability for GPU VMs 

Hibernation will be available for preview on NVv4 and NVadsA10v5 GPU VM series, but larger sizes in the NVadsA10v5 series will not be supported during preview. Supported sizes include:  

NV4as_v4 

NV8as_v4 

NV16as_v4 

NV32as_v4 

NV6ads_A10_v5 

NV12ads_A10_v5 

Customers will be able to leverage the hibernation feature using the Azure Portal, Azure CLI or PowerShell options on the supported virtual machines. In addition, customers can also take advantage of this feature through Azure Virtual Desktop and Citrix Desktop as a Service (DaaS) on their various offerings.

Azure Virtual Desktop 

Azure Virtual Desktop  provides a flexible cloud-based virtual desktop infrastructure (VDI) platform for securely delivering virtual desktops and remote apps. With GPU hibernation (preview) being supported, customers will be able to seamlessly integrate hibernation into their GPU-based virtual desktop environments, unlocking additional cost-saving opportunities. Learn more about  creating personal desktop scaling plan. 

Citrix Desktop as a Service (DaaS) for Azure 

Microsoft and Citrix have been collaborating for decades to provide technology solutions that streamline IT operations and optimize costs.  Citrix DaaS  offers desktop and app virtualization solutions leveraging Azure Virtual Desktop platform capabilities. With GPU hibernation (preview) support, Citrix DaaS users can efficiently manage GPU VMs, enabling significant cost savings without compromising performance or user experience. 

Getting started with hibernation 

Hibernation is currently available for General Purpose Intel and AMD VM Sizes in all public regions. Both Linux and Windows Operating Systems are supported.  Learn more about the hibernate feature  here . For more details on how to get started with hibernation, refer to the product documentation

​Microsoft Tech Community – Latest Blogs –Read More 

When working with databases, high resource usage or a query reporting a timeout could indicate that the statistics of the tables involved in the query are not up to date, that we might be missing indexes, or that there are excessive blocking as the most common elements and possible causes of performance loss. For this reason, I would like to add in this article elements that can help us determine what might be happening with our query. 

CommandTimeout and Obtaining Statistics

The inception point of our exploration is the creation of a stored procedure, sp_AnalyzeQueryStatistics. This procedure is designed to take a SQL query as input, specified through the @SQLQuery parameter, and dissect it to unveil the underlying schema and tables it interacts with.

Crafting

sp_AnalyzeQueryStatistics: The core functionality of this procedure leverages the sys.dm_exec_describe_first_result_set DMV. This invaluable tool provides a window into the query’s anatomy, pinpointing the schema and tables entwined in its execution path.

CREATE PROCEDURE sp_AnalyzeQueryStatistics
@SQLQuery NVARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @TableNames TABLE (
SourceSchema NVARCHAR(128),
TableName NVARCHAR(128)
);

INSERT INTO @TableNames (SourceSchema, TableName)
SELECT DISTINCT
source_schema AS SourceSchema,
source_table AS TableName
FROM
sys.dm_exec_describe_first_result_set(@SQLQuery, NULL, 1) sp
WHERE sp.error_number IS NULL AND NOT sp.source_table is NULL

SELECT
t.TableName,
s.name AS StatisticName,
STATS_DATE(s.object_id, s.stats_id) AS LastUpdated,
sp.rows,
sp.rows_sampled,
sp.modification_counter
FROM
@TableNames AS t
INNER JOIN
sys.stats AS s ON s.object_id = OBJECT_ID(QUOTENAME(t.SourceSchema) + ‘.’ + QUOTENAME(t.TableName))
CROSS APPLY
sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp;
END;

Diving Deeper with Table Statistics:

Identification is just the precursor; the crux lies in scrutinizing the statistics of these identified tables. By employing sys.stats and sys.dm_db_stats_properties, we delve into the statistical realm of each table, gleaning insights into data distribution, sampling rates, and the freshness of the statistics.

Informed Decision-Making:

This statistical audit empowers us with the knowledge to make data-driven decisions. Should the rows_sample significantly deviate from the total rows, or the statistics’ last update be a for example 2 months, it’s a clarion call for action—be it updating the statistics or reevaluating index strategies.

C# Implementation:

using System;
using System.Diagnostics;
using System.Data;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.Data.SqlClient;

namespace HighCPU
{
class Program
{
private static string ConnectionString = “Server=tcp:myservername.database.windows.net,1433;User Id=MyUser;Password=MyPassword;Initial Catalog=MyDb;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Pooling=true;Max Pool size=100;Min Pool Size=1;ConnectRetryCount=3;ConnectRetryInterval=10;Application Name=ConnTest”;
private static string Query = “SELECT * FROM [MSxyzTest].[_x_y_z_MS_HighDATAIOBlocks] ORDER BY NEWID() DESC”;

static async Task Main(string[] args)
{
SqlConnection connection = await EstablishConnectionWithRetriesAsync(3, 2000);
if (connection == null)
{
Console.WriteLine(“Failed to establish a database connection.”);
return;
}

await ExecuteQueryWithRetriesAsync(connection, 5, 1000, 100000,2,true);

connection.Close();
}

private static async Task<SqlConnection> EstablishConnectionWithRetriesAsync(int maxRetries, int initialDelay)
{
SqlConnection connection = null;
int retryDelay = initialDelay;

for (int attempt = 1; attempt <= maxRetries; attempt++)
{
try
{
connection = new SqlConnection(ConnectionString);
await connection.OpenAsync();
Console.WriteLine(“Connection established successfully.”);
return connection;
}
catch (SqlException ex)
{
Console.WriteLine($”Failed to establish connection: {ex.Message}. Attempt {attempt} of {maxRetries}.”);
if (attempt == maxRetries)
{
Console.WriteLine(“Maximum number of connection attempts reached. The application will terminate.”);
return null;
}

Console.WriteLine($”Waiting {retryDelay / 1000} seconds before the next connection attempt…”);
await Task.Delay(retryDelay);

retryDelay *= 2;
}
}

return null;
}

private static async Task ExecuteQueryWithRetriesAsync(SqlConnection connection, int maxRetries, int initialDelay, int CancellationTokenTimeout, int CommandSQLTimeout, Boolean bReviewQuery = false)
{
int retryDelay = initialDelay;

for (int attempt = 1; attempt <= maxRetries; attempt++)
{
using (var cts = new CancellationTokenSource())
{
cts.CancelAfter(CancellationTokenTimeout*attempt);

try
{
using (SqlCommand command = new SqlCommand(Query, connection))
{
command.CommandTimeout = CommandSQLTimeout*attempt;

Stopwatch stopwatch = Stopwatch.StartNew();

await command.ExecuteNonQueryAsync(cts.Token);

stopwatch.Stop();
Console.WriteLine($”Query executed successfully in {stopwatch.ElapsedMilliseconds} milliseconds.”);

return;
}
}
catch (TaskCanceledException)
{
Console.WriteLine($”Query execution was canceled by the CancellationToken. Attempt {attempt} of {maxRetries}.”);
}
catch (SqlException ex) when (ex.Number == -2)
{
Console.WriteLine($”Query execution was canceled due to CommandTimeout. Attempt {attempt} of {maxRetries}.”);
if (bReviewQuery)
{ await ReviewQuery(); }
}
catch (SqlException ex) when (ex.Number == 207 || ex.Number == 208 || ex.Number == 2627)
{
Console.WriteLine($”SQL error preventing retries: {ex.Message}”);
return;
}
catch (Exception ex)
{
Console.WriteLine($”An exception occurred: {ex.Message}”);
return;
}

Console.WriteLine($”Waiting {retryDelay / 1000} seconds before the next query attempt…”);
await Task.Delay(retryDelay);

retryDelay *= 2;
}
}
}
private static async Task ReviewQuery()
{
SqlConnection connection = await EstablishConnectionWithRetriesAsync(3, 2000);
if (connection == null)
{
Console.WriteLine(“Review Query – Failed to establish a database connection.”);
return;
}

await ReviewQueryWithRetriesAsync(connection, 5, 1000, 10000, 15);
connection.Close();
}
private static async Task ReviewQueryWithRetriesAsync(SqlConnection connection, int maxRetries, int initialDelay, int CancellationTokenTimeout, int CommandSQLTimeout, Boolean bReviewQuery = false)
{
int retryDelay = initialDelay;

for (int attempt = 1; attempt <= maxRetries; attempt++)
{
using (var cts = new CancellationTokenSource())
{
cts.CancelAfter(CancellationTokenTimeout * attempt);

try
{
using (SqlCommand command = new SqlCommand(“sp_AnalyzeQueryStatistics”, connection))
{
command.CommandTimeout = CommandSQLTimeout * attempt;
command.CommandType = CommandType.StoredProcedure;

Stopwatch stopwatch = Stopwatch.StartNew();
command.Parameters.Add(new SqlParameter(“@SQLQuery”, SqlDbType.NVarChar, -1));
command.Parameters[“@SQLQuery”].Value = Query;

using (SqlDataReader reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
Console.WriteLine(“TableName: ” + reader[“TableName”].ToString());
Console.WriteLine(“StatisticName: ” + reader[“StatisticName”].ToString());
Console.WriteLine(“LastUpdated: ” + reader[“LastUpdated”].ToString());
Console.WriteLine(“Rows: ” + reader[“Rows”].ToString());
Console.WriteLine(“RowsSampled: ” + reader[“Rows_Sampled”].ToString());
Console.WriteLine(“ModificationCounter: ” + reader[“Modification_Counter”].ToString());
Console.WriteLine(“———————————–“);
}
}

stopwatch.Stop();
Console.WriteLine($”Query executed successfully in {stopwatch.ElapsedMilliseconds} milliseconds.”);

return;
}
}
catch (TaskCanceledException)
{
Console.WriteLine($”Query execution was canceled by the CancellationToken. Attempt {attempt} of {maxRetries}.”);
}
catch (SqlException ex) when (ex.Number == -2)
{
Console.WriteLine($”Query execution was canceled due to CommandTimeout. Attempt {attempt} of {maxRetries}.”);
if (bReviewQuery)
{ }
}
catch (SqlException ex) when (ex.Number == 207 || ex.Number == 208 || ex.Number == 2627)
{
Console.WriteLine($”SQL error preventing retries: {ex.Message}”);
return;
}
catch (Exception ex)
{
Console.WriteLine($”An exception occurred: {ex.Message}”);
return;
}

Console.WriteLine($”Waiting {retryDelay / 1000} seconds before the next query attempt…”);
await Task.Delay(retryDelay);

retryDelay *= 2;
}
}
}

}
}

The Bigger Picture

This initial foray into CommandTimeout and statistics is merely the tip of the iceberg. It sets the stage for a broader discourse on query performance, where each element—from indexes to execution plans—plays a crucial role. Our series aims to arm you with the knowledge and tools to not just react to performance issues but to anticipate and mitigate them proactively, ensuring your databases are not just operational but optimized for efficiency and resilience.

Stay tuned as we continue to peel back the layers of SQL performance tuning, offering insights, strategies, and practical advice to elevate your database management game.

​Microsoft Tech Community – Latest Blogs –Read More 

Introduction

We all know that as security threats evolve, we must update our defenses to mitigate newer threats. Over the last few months, some customers have asked us to strengthen the way we secure passwords in SQL Server. The most often-cited reference by customers is to comply with NIST SP 800-63b.

Currently supported versions of SQL Server and Azure SQL DB use a SHA-512 hash with a 32-bit random and unique salt. It is statistically infeasible for an attacker to deduce the password knowing just the hash and the salt. It is considerably easier for an attacker to hunt for insecure storage of database connection strings that contain database credentials than it is to break the password verifier (also called a password authenticator) used by SQL Server and Azure SQL DB. But that’s a discussion for another day and is the main reason we highly recommend using Entra ID authentication rather than using uid/pwd-based connections because Entra ID authentication manages credentials and supports access policies.

Considering NIST SP 800-63b, we have added an iterator to the password verifier algorithm. Rather than just storing the SHA-512 hash of the password:

H = SHA512(salt, password)

In this update, we create an iterated hash:

H0 = SHA512(salt, 0, password);
for n=1 to 100,000
Hn = SHA512(salt, Hn-1, password)

NIST SP 800-63b requires a minimum 10,000 iterations, but we chose an iteration count of 100,000 because the Microsoft SDL minimum is 100,000. The iteration means that an attacker attempting a brute-force attack using the password verifier is slowed down by x100,000. We want to stress that there are no known weaknesses in the current SQL Server authenticator implementation, we are adding this update at the request of finance customers to help achieve their compliance goals.

Specifics

Let’s look at some details.

This feature is off by default, and it is only available in SQL Server 2022 CU12 and later. There are no plans to back-port this to older versions of the database engine.

Next, and this is critically important. This changes the on-disk format for the password verifier. This has serious repercussions because we cannot go back to the older version unless you reset users’ passwords or perform a full restore. This update only changes a password verifier to the new algorithm on password change. The database engine cannot update the password verifier automatically because the engine does not know the password.

So how do you enable this if you want to test it out?

First, make sure you’re using a non-production database. The code is production quality, but we want to limit its use until we get more coverage.

Second, make a backup of the database and test that it restores. Remember, there is no going back to the old authenticator other than a password change or a full restore. Reread this point one more time.

Third, using an account that is a sysadmin, you can enable this functionality using the following:

DBCC TRACEON (4671, -1)
DBCC TRACEON (4671)

You can check for this trace flag with:

DBCC TRACESTATUS(4671)

Below is a sample flow testing the functionality.

CREATE LOGIN JimBob WITH PASSWORD = ‘<insert strong pwd>’
DBCC TRACEON (4671,-1)
DBCC TRACEON (4671)
DBCC TRACESTATUS(4671)

CREATE LOGIN MaryJane WITH PASSWORD = ‘<insert strong pwd>’
GO
SELECT name, password_hash
FROM sys.sql_logins
WHERE name NOT LIKE ‘##MS%’

You will see output like this:

sa 0x020097E55B1EC90563A023D6785F4ADC–snip–33A34CB391510CE532B
JimBob 0x0200B378592D3BCFF9B2CD667380D66D–snip–78619048510C10C342E
MaryJane 0x0300D1FB26002DEE6615D02BD9F9F425–snip–0A9F559F3D16EF61A84

Notice the first byte of the hash for JimBob and sa is 0x02, this is the algorithm version number. If you change the password for a login after enabling the new algorithm, the version number changes to 0x03 and this is what we see for MaryJane. The use of a version number at the start of the hash output is a fantastic example of cryptographic agility. Always Encrypted in SQL Server and Azure SQL DB also supports cryptographic agility, as does Microsoft Office. Crypto-agility is a security best practice that allows applications to adopt new cryptographic algorithms over time without breaking existing applications. You can learn more about the topic here. This is especially important as we face a world of post-quantum cryptography.

The data size on disk is the same for v2 and v3 password verifiers because the salt and resulting hash are the same size.

Now, if we update JimBob’s password:

ALTER LOGIN JimBob WITH PASSWORD = ‘<insert strong pwd>’

We will see his password authenticator is something like this:

JimBob 0x0300A269192D2BCEF9A20016729FDEAD–snip–0AFFF124197B1065718

The new v3 algorithm is now applied to his account.

If you want to go back to using the old v2 algorithm, then you can turn the flag off:

DBCC TRACEOFF (4671,-1)
DBCC TRACEOFF (4671)
DBCC TRACESTATUS(4671)

However, this does not change any password authenticators. JimBob and MaryJane can still login and the SQL engine will use the v3 algorithm. However, if either of our two users changes their passwords, they will revert to v2 because the feature is turned off.

Miscellany

If you want to see what a password hash looks like, you can use PWDENCRYPT and you can compare a given hash with the hash of an existing password with PWDCOMPARE. The documentation indicates that HASHBYTES should be used rather than PWDENCRYPT, but this is not correct as HASHBYTES is a general-purpose hash function and not password-specific.

Finally, as noted, to change a password either to roll forward to v3 or roll back to v2, you can use ALTER

LOGIN xxxx WITH PASSWORD = ‘yyyy’, where xxxx is the login name and yyyy is the password, and you can add MUST_CHANGE if needed.

One last and crucial point, if you undo the update then you will have to change all passwords that use the v3 algorithm as the engine before this update cannot deal with the v3 data format.

Summary

Listening to our customers, we are excited to add this new security feature to SQL Server 2022 CU12 and later. If you want to test this, please understand the serious implications of making this change; the updated algorithm changes the on-disk format and there is no going back other than a full database restore or resetting the password for affected logins after turning off the traceflag.

We’d love to hear your feedback.

A big thanks to Steven Gott for all his hard making this happen!

​Microsoft Tech Community – Latest Blogs –Read More 

A new Account Verification will be required to access Partner Center programs. All participants in the Windows Hardware Program on Partner Center will be required to complete Verifiable Credentials or they will be blocked from accessing the Hardware Program. Verifiable Credentials (VCs) is an open standard for digital credentials.

The Primary Contact for your account will be the initial individual required to obtain Verifiable Credentials. We ask that you take action now to confirm the Primary Contact information on your account in Partner Center is accurate and current.

Please note the information you provide to obtain Verifiable Credential will be verified by Au10tix Ltd, an industry leader engaged to support this effort. You will not need to disclose the information with Microsoft.

To complete your Verifiable Credential, you will need to install the Microsoft Authenticator app on your mobile device and follow the instructions. Microsoft Authenticator will store your Verifiable Credential. You will need a current and valid government ID in the physical form. The name on the government ID must match the Partner Center Primary Contact. For security purposes there are fixed time limits in the steps to obtain Verifiable Credentials. You may need thirty (30) minutes to complete the steps.

When you receive the Verifiable Credentials request from Partner Center, we urge you to complete the steps as soon as possible to avoid losing access to the Hardware Program. If you have any questions or issues, please reach out to our support team. For details on how to contact support, see Get support for Partner Center dashboard issues – Windows drivers | Microsoft Learn.

Thank you for your cooperation.

​Microsoft Tech Community – Latest Blogs –Read More 

Windows Server 2025 and the Windows Server Annual Channel, offer a comprehensive array of enhanced features, heightened security measures, and improved overall performance, and with image portability customers can now run Windows Server 2022 based containers on these new versions. To maximize the experience for customers, not only will Windows Server 2025/Annual Channel provide the most efficient versions of Windows Server yet, but also streamline the upgrade process. In pursuit of an enhanced user experience and an unwavering commitment to safety and reliability, we will be retiring Windows Server 2022 on Azure Kubernetes Service (AKS) in 3-years time. 

What does this mean for me? 

Windows Server 2022 will be retiring on AKS in March 2027. You should prepare to upgrade to a supported Windows Server version before March 2027. 

How can I upgrade my Windows nodepools? 

You can follow the Windows Server OS migration process outlined in the AKS documentation to upgrade to Windows Server 2025 or Annual Channel when they’re released on AKS. Portability is key feature available for Windows Server 2025/Annual Channel and onwards, the host and container image no longer need to be upgraded in tandem, older images can now work on newer hosts (ex. running Windows Server 2022 image on Windows Server 2025 host).  

Kubernetes version 1.34 will be the final version where Windows Server 2022 is supported on AKS. When Kubernetes version 1.34 is at the end of life on AKS, Windows Server 2022 will no longer be supported. Upgrades to Kubernetes 1.35 on AKS will be blocked if there are any remaining Windows Server 2022 node pools in the cluster. 

Windows Server 2025 on AKS and will offer numerous advantages and enhancements. At a high level, Windows Server 2025 introduces enhanced performance and reliability and improved networking support, including density improvements. Learn more about Windows Server 2025 from our recent announcements at Containers – Microsoft Community Hub. 

Our commitment centers on customer satisfaction and success, guiding our efforts to provide ample resources and time for upgrading to our premier operating system. Our aim is to simplify the upgrade process, enabling customers to fully leverage the benefits of Windows Server 2025/Annual Channel.  

​Microsoft Tech Community – Latest Blogs –Read More 

The 12th cumulative update release for SQL Server 2022 RTM is now available for download at the Microsoft Downloads site. Please note that registration is no longer required to download Cumulative updates.
To learn more about the release or servicing model, please visit:

CU12 KB Article: https://learn.microsoft.com/troubleshoot/sql/releases/sqlserver-2022/cumulativeupdate12

Starting with SQL Server 2017, we adopted a new modern servicing model. Please refer to our blog for more details on Modern Servicing Model for SQL Server.

Microsoft® SQL Server® 2022 RTM Latest Cumulative Update: https://www.microsoft.com/download/details.aspx?id=105013
Update Center for Microsoft SQL Server: https://learn.microsoft.com/en-us/troubleshoot/sql/releases/download-and-install-latest-updates

​Microsoft Tech Community – Latest Blogs –Read More 

Learn how to use Azure Arc to monitor key performance metrics for your SQL Servers located in your data center, at the edge, or even in other public clouds.

Resources:

https://aka.ms/ArcSQLMonitoring
https://aka.ms/ArcDocs

View/share our latest episodes on Microsoft Learn and YouTube!

​Microsoft Tech Community – Latest Blogs –Read More 

Introduction

Hi folks! My name is Felipe Binotto, Cloud Solution Architect, based in Australia.

I understand and you probably do as well, that cost savings in the cloud is a very hot topic for any organization.

Believe it or not, there is a huge number of people (including me) doing their best to allow you to get the best value for your money. This is imperative for us.

The plan here is to highlight the most used cost savings artifacts as well as the most effective cost savings actions you can take for cost savings.

I will give you some personal tips, but most of the content already exists and therefore my objective is to have a consolidated article where you can find the best and latest (as of March 2024) cost optimization content.

The content will vary from teams ranging from Product Groups to Cloud Solution Architects which can cover anything from the ins and outs of people actively working on our products to the people actively deploying and implementing them in the field.

Key Strategies for Azure Cost Optimization

There are a huge number of areas where cost optimization can be applied to. These range from doing some type of remediation to just understanding and having ways to visualize your spendings.

I don’t intend to cover every single possible way to achieve cost savings but here is what I will cover:

Hybrid Benefit (both Windows and Linux)
Reservations (for several resource types)
Savings Plan
Idle Resources
SKUs
Resizing
Logs
Workbooks
Dashboards
FinOps

In my experience, if you invest some time and take a good look at the above list, you will be able to achieve good savings and be able to invest those savings in more innovative (or maybe security) initiatives in your company.

Hybrid Benefit

Azure Hybrid Benefit is a cost-saving feature that allows you to use your existing on-premises Windows Server and SQL Server licenses with Software Assurance or qualifying subscription licenses to run workloads in the cloud at a reduced cost. This benefit now also extends to Linux (RHEL and SLES) and Arc-enabled AKS clusters. You can also leverage Hybrid Benefit on Azure Stack HCI. It’s an effective way to reduce costs for organizations that have already invested in Microsoft or Linux licenses and are looking to migrate or extend their infrastructure to Azure.

@arthurclares has a nice blog post on this.

For information on Hybrid Benefit for Arc-enabled AKS clusters, check this page.

For more information on Hybrid Benefit for Azure Stack HCI, check this page.

Reservations and Savings Plans

Azure Reservations help you save money by committing to one-year or three-year plans for multiple products. This includes virtual machines, SQL databases, AVS, and other Azure resources. By pre-paying, you can secure significant discounts over the pay-as-you-go rates. Reservations are ideal for predictable workloads where you have a clear understanding of your long-term resource needs.

The Azure Savings Plan is a flexible pricing model that offers lower prices on Azure resources in exchange for committing to a consistent amount of usage (measured in $/hour) for a one or three-year period. Unlike Reservations, which apply to specific resources, the Savings Plan applies to usage across any eligible services, providing more flexibility in how you use Azure while still benefiting from cost savings.

@BrandonWilson already has a nice blog post on this.

Idle Resources

Identifying and managing idle resources is a straightforward way to optimize costs. Resources such as unused virtual machines, excess storage accounts, or idle database instances can incur costs without providing value. Implementing monitoring and automation to shut down or scale back these resources during off-peak hours can lead to significant savings.

@anortoll has already blogged about this and the post has workbooks that can be used to locate those idle resources.

@Dolev_Shor available here.

SKUs

Selecting the SKUs for your Azure resources is crucial for cost optimization. Azure offers a variety of SKUs for services like storage, databases, and compute, each with different pricing and capabilities. Choosing the most appropriate SKU for your workload requirements can optimize performance and cost efficiency.

A classic example is the over sizing of Virtual Machines. However, there are other resources to consider too. For instance, you deploy an Azure Firewall with the Premium SKU, but do you really need premium features such as TLS or intrusion detection? Another example are App Service plans which in the v3 SKU can be cheaper and enable you to buy Reservations.

Here is a blog post by Diana Gao on VM right sizing.

Here is another blog post by Werner Hall on right sizing of Azure SQL Managed Instances.

Logs

Analyzing logs can provide insights into resource utilization and operational patterns, helping identify opportunities for cost savings. For instance, log analysis can reveal underutilized resources that can be downsized, or inefficient application patterns that can be optimized. Azure offers various tools for log analysis, such as Azure Monitor and Log Analytics, to aid in this process.

However, logs can also be the source of high cloud spending. The key is to understand what you need to log, what type of logs you need and strategies to minimize the cost of storing those logs.

For example, when you are ingesting logs in Log Analytics, depending on the log you are ingesting, you can configure certain tables in the Log Analytics workspace to use Basic Logs. Data in these tables has a significantly reduced ingestion charge and a limited retention period.

There’s a charge to search against these tables. Basic Logs are intended for high-volume verbose logs you use for debugging, troubleshooting, and auditing, but not for analytics and alerts.

Depending on the amount of data being ingested, you could also leverage commitment tiers in your workspace to save as much as 30 percent compared to the PAYG price.

Furthermore, you could also structure your logs to be archived after a period of time and save on costs.

Refer to this page to learn more about these options and to this page to learn more about best practices.

Workbooks

Azure Workbooks provide a customizable, interactive way to visualize and analyze your Azure resources and their metrics. By creating workbooks, you can gain insights into your spending patterns, resource utilization, and operational health. This can help identify inefficiencies and areas where cost optimizations can be applied.

Many workbooks are available. A few examples are:

Azure Advisor workbook
Azure Orphan Resources workbook
Azure Hybrid Benefit workbook

Dashboards

Azure Dashboards offer a unified view of your resources and their metrics, allowing for real-time monitoring of your Azure environment. Custom dashboards can be configured to focus on cost-related metrics, providing a clear overview of your spending, highlighting trends, and pinpointing areas where optimizations can be made.

You can make your own dashboards, but a few are already available, and you can customize them to your needs.

@sairashaik made available two very useful dashboards available here and here.

Another one which has been available for a long time is the Cost Management Power BI App for Enterprise Agreements.

FinOps

FinOps, or Financial Operations, is a cloud financial management practice aimed at maximizing business value by bringing financial accountability to the variable spend model of the cloud. It involves understanding and controlling cloud costs through practices like allocating costs to specific teams or projects, budgeting, and forecasting. Implementing FinOps practices helps organizations make more informed decisions about their cloud spend, ensuring alignment with business goals.

Learn how to implement your own FinOps hub leveraging the FinOps Toolkit.

Additional Resources

The following are some additional resources which provide valuable information for your Cost Optimization journey.

Advisor Cost Optimization Workbook

Plan to manage costs

What is FinOps?

The Azure FinOps Guide

Cost Optimization Design Principles

Conclusion

Optimizing costs in Azure is a multifaceted endeavor that requires a strategic approach and a deep understanding of the available tools and features. By leveraging Azure’s Hybrid Benefit for both Windows and Linux, making smart use of Reservations for various resources, adopting Savings Plans, and diligently managing Idle Resources, businesses can achieve substantial cost savings.

Additionally, the careful selection of SKUs, appropriate resizing of resources, thorough analysis of logs, and effective use of Workbooks and Dashboards can further enhance cost efficiency. Lastly, embracing FinOps principles ensures that cost management is not just an IT concern but a shared responsibility across the organization, aligning cloud spending with business value. Together, these strategies form a robust framework for achieving cost optimization in Azure, enabling businesses to maximize their cloud investments and drive greater efficiency and innovation.

As always, I hope this was informative to you and thanks for reading.

Disclaimer

The sample scripts are not supported under any Microsoft standard support program or service. The sample scripts are provided AS IS without warranty of any kind. Microsoft further disclaims all implied warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample scripts and documentation remains with you. In no event shall Microsoft, its authors, or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample scripts or documentation, even if Microsoft has been advised of the possibility of such damages.

​Microsoft Tech Community – Latest Blogs –Read More