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

Database Weekly for September 16, 2023 Problems displaying this newsletter? [View online](. [Database Weekly]( The Complete Weekly Roundup of SQL Server News by SQLServerCentral.com Hand-picked content to sharpen your professional edge Editorial  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](  The Weekly News All the headlines and interesting SQL Server information that we've collected over the past week, and sometimes even a few repeats if we think they fit. Vendors/3rd Party Products [The benefits and challenges of managing hybrid estates]( 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. [Running Flyway Pre-Migration Checks on the Database]( 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. AI/Machine Learning/Cognitive Services [Large Language Models (LLMs) to train artificial intelligence (AI) tools such as ChatGPT]( From MSSQL Tips Learn about Large Language Models (LLMs) and how t... [AI just beat a human test for creativity. What does that even mean?]( From Technology Review Feed - Tech Review Top Stories 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... [10 Best AI Chatbots 2023]( From Past News - RSS Feeds 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... Administration of SQL Server [Extended Events and I]( 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. [You Should Use sp_HumanEvents To Make Extended Events Easy #tsqltuesday]( From Erik Darling Data Another One This month, Grant has chosen the form ... [TSQL Tuesday – One of my favorite Extended Events]( From Ben Miller's World In honor of TSQL Tuesday for September, the mother... [Extended Events and I (T-SQL Tuesday #166)]( From SQLServerCentral Blogs 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... [Making Extended Events a bit more usable : T-SQL Tuesday #166]( From SQLServerCentral Blogs 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... [Making Extended Events a bit more usable : T-SQL Tuesday #166]( From SQLStudies 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 Azure Databricks, Spark and Snowflake [Making Python work with Snowflake For Fun and Pleasure]( 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. [Difference between SparkSQL and TSQL casts]( From World of Whatever Yet another thing that has bitten me working in Sp... [Databricks sparksql escaping quote/tick]( From World of Whatever 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,... Azure DevOps [Configure load testing in your CI/CD pipeline from Azure portal]( From Azure Updates Configure load testing in your CI/CD pipeline from... [Creating Empty Repos in Azure DevOps–#SQLNewBlogger]( From SQLServerCentral Blogs 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... Azure SQL [HADR in Azure SQL Database (Part II)]( 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”. [Lesson Learned #425:Azure SQL Database Failover Group Endpoint: A Deep Dive into DNS Resolution]( From Azure Database Support Blog Today, we got a service request where our customer... [Lesson Learned #426:Identifying and Mitigating Query Regressions in Azure SQL using Query Data Store]( From Azure Database Support Blog SQL performance is often a critical aspect for mos... [Lesson Learned #427:BUFFER IO in Azure SQL Database (Query Data Store): An Expert's Guide]( From Azure Database Support Blog In Azure SQL Database, performance tuning often re... [“Oops, I Lost My Indexes in Azure SQL DB.”]( From Brent Ozar Unlimited I got an interesting request for consulting, and I... Career, Employment, and Certifications [Fear of Being Made Obsolete Grips College-Educated US Workers]( From IT Pro - Microsoft Windows Information, Solutions, Tools  Conferences, Classes, Events, and Webinars [Webinar: The Cloud, the Future, and Bob Ward at the Summit]( From SQLServerCentral Blogs I’m doing a webinar next week with Bob Ward, a p... [Announcing SQLBits 2024: 19-23 March, Near London]( From Brent Ozar Unlimited 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,... [PostgreSQL 101: A guide to PostgreSQL documentation & useful resources]( 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) [The Impact of the Cloud on Databases]( 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. [Minnesota (MN) SQLSaturday 2023 #1054 and Pre-Con Events! Sep 29-30]( From Dan English's BI Blog  DMO/SMO/Powershell [How To Filter SQL Server Data in Windows PowerShell]( From IT Pro - Microsoft Windows Information, Solutions, Tools Explore techniques for filtering SQL Server data using both PowerShell and SQL queries. Database Design, Theory and Development [Querying PostgreSQL: Learning PostgreSQL with Grant]( 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. [Exporting data from a MySQL database using SELECT…INTO OUTFILE]( 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. DevOps and Continuous Delivery (CI/CD) [Video: Preparing for MLOps with GitHub Actions and Azure ML]( From 36 Chambers – The Legendary Journeys THE VIDEO THE SYNOPSIS In this video, we set up a ... ETL/SSIS/Azure Data Factory/Biml [Azure Data Factory (ADF) – Issues with parsing strings in CSVs]( From The Bit Bucket It's scary how much of the world's data lives in C... [ADF, DB2 and unexpected token query error]( From World of Whatever ADF, DB2 and unexpected token query error In yet a... HA/DR/Always On/Clustering [SQL Server Database Snapshots for Rapid Restores]( From MSSQL Tips In this article, we look at using SQL Server datab... [SQL SERVER – Query Store Feature for Secondary Replicas]( From Journey to SQL Authority with Pinal Dave 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... Microsoft Fabric ( Azure Synapse Analytics, OneLake, ADLS, Data Science) [Microsoft Fabric: Using notebooks and table partitioning to convert files to tables]( 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. [Exploring Machine Learning in Microsoft Fabric: Should Data Scientists care?]( 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. [About 5 Minutes with Microsoft Fabric]( From Data on Wheels (Steve Hughes) 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 Domains: Stepping Stones for Data Mesh]( From RADACAD 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... Performance Tuning SQL Server [SQL SERVER – Performance: OR vs IN – A Summary and Further Reading]( From Journey to SQL Authority with Pinal Dave The varied responses led me to write a detailed bl... [SQL SERVER – Avoid Functions in the WHERE Clause for Performance]( From Journey to SQL Authority with Pinal Dave Using SQL Server functions in the WHERE clause of ... PowerPivot/PowerQuery/PowerBI [Power BI Time Series Analysis: How to create a Moving Average on time series data and enhance the Decision-Making process]( 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. [Add service principal to Fabric workspace using Power BI REST API]( 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. [Using DAX to control a chart range in Power BI]( From Sqlbi Ho to use DAX expressions to control and customize... [Extracting SQL Database/Table Names From Your Power BI Dataset!]( From Havens Consulting Video by: Reid HavensLearn how to query the XMLA e... [Power BI: Grouping & Binning using Data Groups]( From Purple Frog Systems In this blog, we explore Power BI’s Data Groups ... Product Reviews and Articles [Learn dbatools in a Month of Lunches]( This book aims to make it easier to manage your SQL Server estate, how does it fare? Product Upgrades and Releases [General Availability: Azure SQL updates for mid-September 2023]( From Azure Updates General availability enhancements and updates released for Azure SQL in mid-September 2023 [General availability: Selective Disk Backup and Restore in Enhanced Policy for Azure VM Backup]( From Azure Updates We are adding the “Selective Disk Backup and Restore” capability in Enhanced Policy of Azure VM Backup. [Cumulative Update #8 for SQL Server 2022 RTM]( From MS SQL Server Blog 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... SQL Server Security and Auditing [Pen Testing SQL Server Stored Procedures]( From Erik Darling Data The Old Joke Goes A security engineer walks into a... Security News and Issues [Israeli Hospital Hit By Ransomware Attack, 1TB Data Stolen]( From Dark Reading: Dark Reading News Analysis Vital medical equipment was unaffected, but attack... [MGM Resorts Cyberattack Hobbles Las Vegas Strip Operations]( From Dark Reading: Dark Reading News Analysis Hospitality behemoth struggles to recover followin... [Millions of Facebook Business Accounts Bitten by Python Malware]( From Dark Reading: Dark Reading News Analysis The "MrTonyScam" has a surprisingly high success r... Software Development [Code Review Bottlenecks: Best Practices to Speed the Process]( From IT Pro - Microsoft Windows Information, Solutions, Tools Lengthy code reviews are stalling innovation: Here... T-SQL and Query Languages [New in Kusto: `.show databases entities` command for viewing tables, functions, and more]( From Azure Updates View the entities of all accessible cluster databases with the new `.show databases entities` command for Kusto, and customize the output with various options. [How To Get SQL Server Triggers To Selectively Fire (Video Edition)]( From Erik Darling Data 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...  [RSS Feed]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. Note: This is not the SQLServerCentral.com daily newsletter list, and unsubscribing to this newsletter will not stop you receiving the SQL Server Central daily newsletters. If you want to be removed from that list, you can follow the instructions on the daily newsletter. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -