What will convince you to upgrade?

You’ve probably heard that SQL Server 2022 is on the way, and, assuming the name doesn’t change, it will be later this year. That is great news for folks in the data platform community as features and improvements they’ve been hoping for are added. I’m not currently supporting any production SQL Servers, but I’m still happy to hear about these features and will probably put together some presentations about them once there is a public preview.

Upgrades can be a lot of work. I don’t mean the actual upgrade process, but planning and testing can take time and effort. Many shops don’t upgrade every edition, skipping one or two versions each time.

I’ve always preferred to upgrade with a fresh install on a new VM over upgrading in place. That new VM (or hardware in the old days) can be used for testing until it’s time to migrate the databases and jobs. I haven’t done a SQL Server upgrade in a DevOps organization, so I hope that the process is easier than manual migrations.

Microsoft has announced quite a few exciting new features with SQL Server 2022. My favorite is the parameter sensitive plan optimization that’s part of intelligent query processing. This feature allows the cache to save more than one plan for the same query when one plan isn’t ideal for all parameter values. Yes, this takes care of some parameter sniffing issues, especially when the plan could be contain a clustered index scan or a nonclustered index seek with a key lookup. I’ve seen this scenario cause lots of performance issues for my customers back when I was consulting.

Some of the other announcements include improvements in Query Store, tempdb, and security, the ability to use an Azure Managed Instance for failover from an instance, and Azure Synapse Link for real-time analytics.

There is probably something for everyone in this upcoming release, but would one or two favorite features convince you to upgrade? Back in the early years after 2012 was released, a couple of folks told me that my T-SQL window function session convinced them to upgrade once they saw the LAG function. So, there might be some shops that need a key function to solve an issue and will adopt SQL Server 2022 soon after general availability.

In the meantime, I encourage you to watch the announcements and download the public preview once it’s available. The features in this release of SQL Server just might convince you to do the work it takes to upgrade.

Kathi Kellenberger

Join the debate, and respond to the editorial on the forums

If you can generate a file-based (JSON) model for each new version of a database, produced by a Flyway migration, then you have an easy way to run simple reports to help you search, list, and understand the structure of these databases. I'll show how to produce the models using PowerShell and then run some queries against them to generate the reports.

SQL Prompt users can now share formatting style or code snippets in designated Team spaces on the Redgate Platform. Louis Davidson explains how it works.

In this article we look at different ways to define a connection string to connect to SQL Server using SqlClient, OLDEDB and ODBC connections.

In this post I want to cover how you can keep your Azure Synapse secrets secret in Azure DevOps. Because you need to do this if you are working with production deployments...

What is Change data capture (CDC)? CDC provides historical change information for a user table by capturing both the fact that Data Manipulation Language (DML) changes (insert / update / delete) were made and the changed data. Changes are captured by using a capture process that reads changes from the transaction log and places them in corresponding change tables.

  We are excited to announce Preview of short-term backup retention up to 35 days in the Hyperscale tier of Azure SQL Database. Before this improvement, Hyperscale supported backup retention...

Today, I worked on a case that our customer is testing a bechmark script to measure the INSERT operation in an Azure SQL Database. This process will take different...

Applications are closely dependent on the database...

Microsoft Azure Storage is a cloud-based storage service that provides organizations with a variety of storage solutions...

In this blog post I’m going to show you how you can create an Azure Function that will interact with the Power BI REST API. I choose PowerShell as... The...

This article will describe Azure blob storage, its features along with its uses in SQL Server. I have explored the basics of Azure storage in my last article, “Getting...

Join Redgate's James King in conversation with Chris Yates, Senior Vice President, Managing Director of Data and Architecture at Republic Bank to discover why monitoring your database environment helps your organization gain a competitive edge

Note: "Setting Matters Straight" is a new format: I post on LinkedIn an online Q&A involving data fundamentals that I subsequently debunk in a post here. This is to...

ETL stands for Extract, Transform and Load to allow you to take source data outside of the database and load into the database. Learn more about ETL in this...

COMBIN: Returns the number of combinations for a given number of items. https://dax.guide/combin/ COMBINA: Returns the number of combinations (with repetitions) for a given number of items. https://dax.guide/combina/

CALCULATE is the most powerful and complex function in DAX. In this article, we provide an introduction to CALCULATE, its behavior, and how to use it. CALCULATE, with its...

Oracle is one of the leading DBMS in the marketpla...

Microsoft has released new patches to address crit...

A story about how a system engineer solved a MySQL performance issue written by Rajeshkumar Sasidharan.

One of the very first performance problems that developers will run into when using SQL Server is locking, and often deadlocks...

With Or Without You One thing I really hate about Standard Edition is the lack of a way to globally reduce the allowed memory grant default. Out of the box, any...

You can automate Power BI table refreshes using PowerShell, an Automation Account, and Runbook. Dennes Torres explains how in this article.

It is not common to face rounding differences in P...

Join us live as we answer your questions about Pow...

I was looking at the output of Power Query’s Que...

It’s hard to believe but Power BI has now been a...

On the article Automating table refresh in Power BI I explained many methods to automate refresh of individual objects, which could be tables or partitions. This creates the need...

Chris Webb looks into activity IDs: I was looking ...

This article will show some important questions and answers to practice SQL. Introduction SQL is the base to handle different databases. Even some of the NoSQL databases use an...

In this article we look at the similarities and differences of aggregate functions like sum, count, min, max, avg and more for SQL Server, Oracle and PostgreSQL.

In this article learn about the SQL Server DATEADD function to add and subtract various date parts from the date and time from a date.

I ran into a strange issue recently and thought it would make for a great blog post. I was notified of a standard application process failing with the following...

This tip use T-SQL to build models that specify when to buy and sell a financial security with the objective of growing an account balance through successive trades over...

Every time I set up SQL Server Management Studio, ...