“Oops, I Lost My Indexes in Azure SQL DB. ” (2023-09-16 DBW)

Servicing SQL Server

I've been traveling in Europe this week, partially for holiday and partially for work. It's been a nice break, and it's always interesting to experience the world in different places, away from the United States where I've spent most of my life. I get to come often, and I enjoy the experience, but I'm sometimes annoyed.

For example, I often find service at restaurants to be very different in Europe, and somewhat annoying as well. I worked in restaurants for many years and learned to be attentive to my customers, checking on them and being ready to provide more food and drink or present the bill in a timely manner. I know European customers prefer a more hands off approach and will call a server over when they want something or are ready to pay. The servicing model isn't better or worse, just different.

I thought about this when I saw a new Cumulative Update for SQL Server was released this week: CU #8 for SQL Server 2022 came out. As I was updating the build list, I saw the last few updates were August and July, which seemed like the proper pace. The servicing model from Microsoft is an update every month for the first year of a new release.

However, as I dug deeper, it appears they haven't quite met that target. The RTM was in November, but there wasn't a CU until February (along with a security update). Then we had updates in March and April, but nothing in May. Two in June, which I assume the first of which was a delayed May update. Since then, we've had monthly updates.

I can understand the delays, as software development isn't always clean. Perhaps tackling some patch in May didn't go smoothly in test and there was a delay in ensuring some bug was fixed. However, I'd hope that with an embracing of DevOps, Microsoft would be able to release some update around 15 May and if needed, release another one on 2 June. The goal is repeatable, reliable software deployments, with releases, or at least deployments, being an everyday occurrence, not a big event. Certainly patch releases for large customer bases still require some coordination for publication, documentation updates, etc., but this should still be easy to add in an extra release if necessary.

My approach hasn't been to update as soon as releases are available. Instead, I've typically lagged by one or two releases, depending on workload. I don't want to have breakage, but I want to keep somewhat current. I have found most of these patches are non-issues, but once in awhile that's not the case, and I don't want to have to unwind a patch from my environments. Perhaps if I used containers more, I'd feel differently, but for other instances, I want to ensure no one else encounters major issues before I install something.

For the software I've built and maintained, especially inside a company, I do think that a regular servicing model is a good idea. Many of the customers I work with release changes to their database every week, with a regular expectation set with their customers that their application might change. They sometimes release application changes more often, but a weekly cadence for databases seems to provide a good balance of meeting the needs of software developers and providing some amount of stability to ensure that customers aren't impacted by too many changes at once.

If you build and manage software, it might be worth considering some sort of regular cadence of deployments. I often find that many applications don't need to chance the database every week, but there are index changes or other tuning enhancements that DBAs might want to ensure are deployed in lieu of schema alterations. There might be patches from Microsoft or even some cleanup (add/removing FKs, archiving data, etc.) that could be made when there aren't other changes needed.

I am a big fan of DevOps in general, with repeatable, reliable deployments that are easy to make. I like regular deployments, after good modeling, testing, and review by multiple people that ensure we are constantly delivering value to customers, even if some of this isn't obvious. I'm also a fan of rolling forward when we find mistakes, because we will. Having a smooth deployment process for my servicing model means I can meet those goals.

Steve Jones - SSC Editor

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

More organizations are migrating their data to the cloud, but the majority are likely to end up with a hybrid model, with some data managed through a cloud provider and some managed locally. Find out about the benefits and challenges of managing hybrid estates and how a single monitoring tool can help maintain performance.

This article demonstrates how to run a preliminary check that issues a warning, or throws an error, if the conditions aren't met for a Flyway migration to succeed. It provides some example checks for PostgreSQL databases that use Flyway SQL callbacks to ensure the server is running the correct PostgreSQL version, or that the database has a required extension installed.

Learn about Large Language Models (LLMs) and how t...

AI is getting better at passing tests designed to measure human creativity. In a study published in Scientific Reports today, AI chatbots achieved higher average scores than humans in...

AI chatbots can enhance customer service, automate mundane tasks, and provide personalized recommendations. Here are X of the best AI chatbots to try. The post 10 Best AI Chatbots 2023...

Once you reach a certain level of problem complexity, you’ll want to look under the SQL Server’s hood. And we, mere mortals, have access to only a limited number of tools. I can think of only Trace Flags, DBCC and Extended Events.

Another One This month, Grant has chosen the form ...

In honor of TSQL Tuesday for September, the mother...

Foreword This month’s invitation is from Grant Fritchey (t|b), aka the Scary DBA, about Extended Events (XEs). If you look at the Tags section of my blog, you can see that... The...

Running a bit late this month for T-SQL Tuesday (it’s not exactly Tuesday is it!) but that’s Grant Fritchey’s (blog|twitter) ... Continue reading The post Making Extended Events a bit...

Running a bit late this month for T-SQL Tuesday (it’s not exactly Tuesday is it!) but that’s Grant Fritchey’s (blog|twitter) ... Continue reading

As we did last week in our post on Creating a new function in Snowflake, I want to talk about some of the scripts and languages I use to make my life easier in Snowflake.

Yet another thing that has bitten me working in Sp...

If I had to embed a single quote in a query in TSQL, I would double it. In SparkSQL, I escape it like a classic C style string. So,...

Configure load testing in your CI/CD pipeline from...

I saw someone struggling with getting started with a Visual Studio project and Azure DevOps. They got a conflict, which I’ll show and then get you started with an... The...

This is the second of three posts dedicated to HADR solutions in Azure SQL Database. In HADR in Azure SQL Database (Part I) I presented the “Scale out read-only Database” feature. In this one, I’ll talk about Active Geo-Replication and in HADR in Azure SQL Database (Part III), I’ll introduce “Auto-failover groups”.

Today, we got a service request where our customer...

SQL performance is often a critical aspect for mos...

In Azure SQL Database, performance tuning often re...

I got an interesting request for consulting, and I...

I’m doing a webinar next week with Bob Ward, a p...

I hate picking favorites, but I gotta be honest: SQLBits is my favorite conference. Sure, like any conference, it has workshops, training days, and even a free community day,...

It can be a daunting task for new users to get to grips with PostgreSQL documentation, particularly if you are not from a developer background. If you are not sure where to start, we are here to help break down the different resources available so you can make the most of them. In the latest webinar of our PostgreSQL 101 series, join Ryan Booz, PostgreSQL Advocate at Redgate, and Grant Fritchey, Microsoft Data Platform MVP, and discover: - How to navigate PostgreSQL documentation - The essential things you need to know about PostgreSQL documentation - Other useful learning resources to help make your journey to using PostgreSQL easier Register to join us on September 19th at 11am CT (5pm BST)

Join Microsoft's Bob Ward and Redgate's Steve Jones on September 18th for this free webinar, The Impact of the Cloud on Databases. They'll discuss the cloud, the future, and how you might learn about the direction that best suits your organization. You'll get a preview of what to expect from Bob’s Pre-Con at PASS Data Community Summit.

Explore techniques for filtering SQL Server data using both PowerShell and SQL queries.

I’m going to focus on the core behaviors of SELECT, FROM and WHERE with an emphasis on what’s different from SQL Server. This won’t be a fundamental how-to on querying PostgreSQL, but instead an exploration of the gotchas you’re likely to experience coming in with existing knowledge of how you think these things should work.

In this article, I cover the SELECT…INTO OUTFILE statement, a type of SELECT statement that includes the INTO OUTFILE clause. The clause lets you export data from one or more MySQL tables into a plain text file, providing a counterpart to the LOAD DATA statement.

THE VIDEO THE SYNOPSIS In this video, we set up a ...

It's scary how much of the world's data lives in C...

ADF, DB2 and unexpected token query error In yet a...

In this article, we look at using SQL Server datab...

the Query Store for secondary replicas. This feature extends the Query Store functionality from primary replicas to secondary replica workloads. First appeared on SQL SERVER – Query Store Feature for...

When Microsoft Fabric was born, the only method to convert files to tables was using notebooks. Nowadays we have an easy-to-use UI feature for the conversion.

Fabric is Microsoft’s recently announced SaaS all-in-one analytics platform. It brings together Azure Data Factory, Azure Synapse Analytics and Power BI into a single cohesive platform without the overhead of setting up resources, maintenance, and configuration. Fabric wouldn’t be an end-to-end data analytics platform without data science, so in this blog we will explore the data science and machine learning capabilities of Microsoft Fabric and assess where the platform fits in the completive data science landscape.

Introducing the Fabric 5 from Data on Wheels Microsoft Fabric went to public preview this past summer. There has been content created around using the various cool features that...

Microsoft Fabric introduced a new concept called Domains. Domains are more than just a separation of Fabric data items. They come with a whole lot of security, administration, and...

The varied responses led me to write a detailed bl...

Using SQL Server functions in the WHERE clause of ...

Power BI is one of the leading platforms for incorporating Artificial Intelligence and advanced analytics into applications. With regular updates, it’s easy to miss out on features that can enhance data analysis and insight generation. Our goal here is to explore these advanced features, focusing on time series analysis and how a moving average can be your secret weapon for more insightful decision-making.

Lately, I found myself struggling using the Power BI REST API to add a service principal to the (Fabric) workspace. After I engaged with some other folks, I managed to succeed. In this blog I will elaborate on the mistake I made and how I got it to work. For some this might be a less useful blog. Though, I still wanted to blog this even if it is for my own memory on how to do this.

Ho to use DAX expressions to control and customize...

Video by: Reid HavensLearn how to query the XMLA e...

In this blog, we explore Power BI’s Data Groups ...

This book aims to make it easier to manage your SQL Server estate, how does it fare?

General availability enhancements and updates released for Azure SQL in mid-September 2023

We are adding the “Selective Disk Backup and Restore” capability in Enhanced Policy of Azure VM Backup.

The 8th 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...

The Old Joke Goes A security engineer walks into a...

Vital medical equipment was unaffected, but attack...

Hospitality behemoth struggles to recover followin...

The "MrTonyScam" has a surprisingly high success r...

Lengthy code reviews are stalling innovation: Here...

View the entities of all accessible cluster databases with the new `.show databases entities` command for Kusto, and customize the output with various options.

Triggering Going Further If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers...