Faster Database Recovery with Piecemeal Restores (2024-03-02 DBW)

Database Weekly for March 2, 2024 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  Cyber Insurance for War Is the United States at war in cyberspace with other countries? It's the claim that a few insurers made when US-based Merck filed a claim after a 2017 cyberattack. The company filed a $700mm-ish claim, and the insurers rejected this saying that the attack was an act of war by Russian government operatives. If a factory were disrupted in the real world by actual government operatives (soldiers, spies, etc.), then it likely would be considered an act of war, but in cyberspace, who knows. Actually, in cyberspace, who really knows who is whom, and for who they work? Maybe we don't even know who "they" are in many cases. [Bruce Schneier wrote a piece on this]( talking about a possible solution of using a government backstop that would provide some assistance or coverage that insurers might not be able to cover. While that sounds good if you have a claim, as a citizen, I think this likely encourages more attacks from others, whether they are nation-states or individuals. If someone thinks they might create a spending crisis in a country, maybe they would mount many attacks that are disguised as coming from a foreign nation and cause a government spending issues. The United States has a scale issue, but this could cause similar issues in many smaller countries if this were a way of doing business. Perhaps this might also cause some economic issues if companies don't want to do business in places where they aren't covered. However, this might not be a big issue as more insurers are starting to carve out exceptions in their coverage for these types of attacks, so if your organization is hacked by a supposed nation-state, you aren't covered. I suspect this will also start to extend to other exceptions, such as having unpatched (or incompletely patched) systems, poor policies or just incompetent employees, lack of security scans, and more. In some sense, I would hope that some of these attacks will force, or at least pressure, many organizations to take security more seriously. At the same time, as someone dealing with these changes, it can be inconvenient and a blow to productivity as I struggle to adapt to changes in policy, protocol, and procedure. Some are easy, like locking my home machine. Some are more challenging and frustrating as I try to share more content inside the company. It's good, but it's frustrating. Cyberattacks and cybersecurity are increasingly a part of our lives as technology professionals and I urge you to spend a little time learning about the field. If you want a fun challenge, try the [Advent of Cyber 2023]( is still up as of this writing. I went through it and it was a fun (and scary) way to learn a few things about security, vulnerabilities, and tools out there. 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. AI/Machine Learning/Cognitive Services [Charting ethical excellence: Navigating the question phase of AI development]( From AllAnalytics As organizations infuse trustworthy practices into the fabric of AI systems, remembering that trustworthiness should never be an afterthought is important. Pursuing trustworthy AI is not a distant destination... [Key Security Considerations to Keep in Mind When Using GenAI]( From IT Pro - Microsoft Windows Information, Solutions, Tools These best practices will help safeguard organizations' generative AI implementations. Administration of SQL Server [Faster Database Recovery with Piecemeal Restores]( From Callihan Data The larger a database grows, the more difficult it... [Upgrade SQL Server on your workstation]( From Simple Talk Lately, I’ve seen many people struggling to upgrade their workstation to the latest version of SQL Server. The main source of the problem is usually the web installer/wrapper, which... Azure Databricks, Spark and Snowflake [Databricks Monitoring with System Tables]( Monitoring is important, so I’ve covered the topic a few times in the past. I’ve talked about collecting your Spark application logs and Spark metrics. These are a good way to track what is happening and what is going wrong as your code runs. In the video related to this post I focus on a different side of monitoring. The evolving capabilities offered by Databricks System Tables. Azure SQL [Azure SQL DB - Failed to add database to failover group - find out why]( From Azure Database Support Blog Symptoms: When attempting to add database to a f... Azure SQL Managed Instance [Azure SQL MI License free?]( From SQLServerCentral Blogs A common setup when leveraging the cloud is to use the cloud for Disaster recovery (DR) purposes, some say it becomes a logical extension of your on-prem / data... The... Career, Employment, and Certifications [An Eye on the Loot - Are we Techies or Accountants? Or maybe Techouncants / Accountechs?]( From SQL Rod Systems generally cost money, there is rarely any escape from that. Whether that is down to license costs or platform hosting costs... [10 reasons why IT certifications are still important]( From SQLServerCentral Blogs As someone who has spent over 20 years in the Soft... [Humble Bundle of Java Books]( From SQLServerCentral Blogs I try to keep an eye on the Humble Bundle bundles for books. The bundles serve two great purposes: I was able to grab the Cybersecurity bundle, but by... The... Cloud - AWS [Generally Available: Capacity Reservations support in AKS]( From Azure Updates You can now better manage your changing workload demands by using capacity reservation groups. [100x Faster Query in Aurora Postgres with a lower random_page_cost]( Recently I have been working with some queries in Postgres where I noticed either it has decided not to use an index and perform a sequential scan, or it decided to use an alternative index over a composite partial index. This was quite puzzling, especially when you know there are indexes in the system that can perform these queries faster. Cloud - Azure [Enable replication for added VMware VM data disks]( From Azure Updates Azure Site Recovery now supports enabling replication for data disks that you add to a VMware VM that's already enabled for disaster recovery. [The ABCs of ADX: Learning the Basics of Azure Data Explorer | Data Exposed: MVP Edition]( From Azure SQL You may have heard of Azure Data Explorer - but do you know what it does? Do we know the best ways to use it (and the ways we... [SQL Server enabled by Azure Arc, now assists in selecting the best Azure SQL target]( From MS SQL Server Blog Cloud migration is the process of moving workloads from on-premises or legacy systems to the cloud. It can be a complex and lengthy process, depending on the size and... Computing in the Cloud (Azure, Google, AWS) [6 Sneaky Cloud Budget Busters and How to Prevent Them]( From IT Pro - Microsoft Windows Information, Solutions, Tools Here are six subtle issues to keep an eye on to pr... Conferences, Classes, Events, and Webinars [PASS Data Community Summit 2024: Calls for Speakers & Volunteers are now open!]( The Calls for Speakers & Program Committee Volunteers for PASS Data Community Summit 2024 are officially open! [Introducing SQLBI Days]( From Sqlbi SQLBI Days are one-day lectures of various levels,... [Join Me in Orange County for Tuning Databases in One Day]( From Brent Ozar Unlimited You’ve got production databases in SQL Server or Azure SQL DB, and you want to make ’em faster. You need to identify the database’s bottleneck, prove the root cause,... [Sessions You Should Attend at SQLBits Online 2024]( From Brent Ozar Unlimited SQLBits 2024 is next month, and the session agenda is out, including the Microsoft-led sessions. If you’re going to get the boss to buy you an online or in-person... DMO/SMO/Powershell [Solving the PowerShell GUI Paradox (With Example Scripts)]( From IT Pro - Microsoft Windows Information, Solutions, Tools In GUI-based PowerShell scripts, a problem can arise when you combine GUI components with traditional PowerShell code. Here’s the solution. Data Science [Identifying and Understanding Business Problems for Data Scientists]( From Journey to SQL Authority with Pinal Dave Identifying and Understanding Business Problems for Data Scientists, provides a practical guide to uncovering and defining business challenges. First appeared on Identifying and Understanding Business Problems for Data Scientists [Spotting the Exception: Classical Methods for Outlier Detection in Data Science]( Outliers are unique in that they often don’t play by the rules. These data points, which significantly differ from the rest, can skew your analyses and make your predictive models less accurate. Although detecting outliers is critical, there is no universally agreed-upon method for doing so. While some advanced techniques like machine learning offer solutions, in this post, we will focus on the foundational Data Science methods that have been in use for decades. [Basic ideas in reinforcement learning]( From Statistical Odds & Ends This is my distillation of two lectures from Stanford’s CS221 Autumn 2017 (when I took it), as notes for myself to refer to. Hopefully you find them helpful too!... [Effective Code Documentation for Data Science Projects]( From Dataversity Code documentation is a detailed explanation of how the code works. It is a comprehensive guide that helps developers understand and use the code effectively. It is like a... Database Design, Theory and Development [Want to help shape the future of Redgate's Database Deployment capabilities?]( We're researching some ideas that would help to improve Redgate's database deployment capabilities and, as part of this, have created a short survey for all users. The survey has 8 questions and should take less than 5 minutes to complete. DocumentDB/Key-Value/Graph/other NoSQL Databases [Building MongoDB Aggregations]( From Simple Talk  [Introducing the MongoDB Document]( MongoDB is a document database. As such, the data is stored as individual documents. A document is a data structure made up of one or more field/value pairs. Nearly everything you do in MongoDB is either directly or indirectly related to the documents that you store in a database or move in and out of a database. The better you understand how documents work, the more effectively you can write queries and manage the data. In this article, I'll give you a sense of how documents are constructed and the different types of data they can contain, and will provide several examples that demonstrate some of the ways you can define documents to meet your specific business needs. ETL/SSIS/Azure Data Factory/Biml [Using a Proxy for SSIS Execution]( From Andy Broadsword When executing packages for SSIS the default option would be to use the SQL Server Agent service account. We might not want to share an account between our services... MDX/DAX [A First Look at DAX Visual Calculations: the Good, the Bad, and the Ugly]( From Prologika (Teo Lachev's Weblog) The February 2024 release of Power BI Desktop incl... [Where are Visual Calculations and DirectLake?]( From Sqlbi The right title for this blog post should be “Gu... [DAX limitations with row level security RLS]( From Sqlbi Learn restrictions and workaround in using the USERELATIONSHIP function with row-level security in a semantic model for Power BI and Analysis Services. Microsoft Fabric ( Azure Synapse Analytics, OneLake, ADLS, Data Science) [Apache Spark in Microsoft Fabric]( If you have used Spark in Azure Synapse, prepare to be pleasantly surprised with the compute experience in Microsoft Fabric as Spark compute starts a lot faster because the underlying technology has changed. The Data Engineering and Data Science Fabric experiences include a managed Spark compute, which like previous Spark compute charges you when it is in use [Using Spark Jobs for Multiple Lakehouse Maintenance in Microsoft Fabric]( I have published videos and articles before about Lakehouse maintenance. In this article I want to address a missing point for a lot of Fabric administrators: How to do maintenance on multiple lakehouses that are located in different workspaces. [All Code!!! Extract ZIP files with Notebooks in Microsoft Fabric]( From Guy in a Cube Who needs NOCODE? Patrick takes you on a journey w... Microsoft News [Microsoft Extends Availability of Optional Updates for Windows 11 version 22H2]( From Petri IT Knowledgebase Microsoft is backtracking on its plans to block no... Oracle/PostgreSQL/MySQL/other RDBMS [Ryan Booz: PGSQL Phriday #016: Tuning That One Big Query]( From Planet Postgres PostgreSQL continues to dominate in growth and int... [Francesco Tisiot: 11 Lessons to learn when using NULLs in PostgreSQL®]( From Planet Postgres A boolean value should only contain two values, True or False, but is it correct? Usually people assume so, but sometimes miss the fact that there could be the... [cary huang: Multiple Client Certificate Selection – a Simple POC]( From Planet Postgres Introduction I recently came across this email... [Gilles Darold: Partitioning by reference – Oracle vs PostgreSQL]( From Planet Postgres Sometimes, when you are working on a migration to PostgreSQL, you can encounter features that do not exist in PostgreSQL. This especially happens when we do not have any... [PostgreSQL Create Database Options and Settings]( From MSSQL Tips Learn about the different options and settings and how to create a database in PostgreSQL. Performance Tuning SQL Server [The How To Write SQL Server Queries Correctly Cheat Sheet: EXISTS and NOT EXISTS]( From Erik Darling Data Selectively Numb The sort of wonderful thing about... [Why Multiple Seeks Are Slower For Some SQL Server Queries]( From Erik Darling Data Why Multiple Seeks Are Slower For Some SQL Server ... PowerPivot/PowerQuery/PowerBI [Customizing Kusto (Data Explorer) Connections in Deployment pipeline]( From Simple Talk When organizing our SDLC (Software Development Lif... [Power BI Dynamic Subscription: Send Scheduled Report Filtered for Everyone’s Data Automatically]( From RADACAD If you ever want to have a general report for sale... [Creating a Dual Variance Chart w/ Overlap + Error Bars in Power BI]( From Havens Consulting Video by: Reid HavensLearn how to turn a native bar or column chart into a variance chart that compares against two values. This technique leverages the new overlap feature... [Tips+ Theme Generator Tutorial: Gallery Project Copy & Edit]( From PowerBI Tips Introduction Welcome to today’s tutorial where we dive into the powerful capabilities of the Power BI Tips+ Theme Generator. In this post, we won’t just download a project from... [Model Optimization: Available In MDX Property]( From Simple Talk Power BI Semantic Models have a property called Available In MDX. Each field in the model has this property and we can only see or change its value through... [dataCoverageDefinition: A New Optimisation For Hybrid Tables In Power BI]( From Chris Webb's BI Blog Hybrid tables – tables which contain both Import mode and DirectQuery mode partitions to hold data from different time periods – have been around for a while. They are... Product Reviews and Articles [Friday Flyway Tips–Quick Command Line Access]( From SQLServerCentral Blogs One of the things I had to do recently in a demo w... [Book Review: The AI Playbook]( From Thomas LaRock Imagine you conceive an idea which will save your company millions of dollars, reduce workplace injuries, and increase sales. Now imagine company executives dislike the idea because it seems... [5 things to look for in a database monitoring tool]( From Blog – Redgate Software Databases are the backbone of enterprise-level organizations, facilitating efficient data management, supporting critical business processes, and providing a foundation for secure innovation and growth. Effective database monitoring is critical... [A Review in Progress: 100 SQL Server Mistakes and How to Avoid Them]( From 36 Chambers – The Legendary Journeys Shill Alert Manning did provide me a free copy of this book. This came with no pre-conditions, so all opinions are my own and I’ve provided none of this... [A First Look at Redgate’s Subsetter]( From SQLServerCentral Blogs I have been a proponent of subsetting databases in dev/test for a long time. It took awhile, but as part of the Test Data Manager that Redgate launched at... The... [Foreign Keys in SQL Data Generator]( From SQLServerCentral Blogs A customer recently asked about using FKs in SQL Data Generator, and I decided to write a short post showing how these work. The Scenario I’ve got a copy... The... [How Redgate’s Test Data Manager Can Enhance Automated Testing]( From SQLServerCentral Blogs A brief overview of the benefits and challenges of automated testing and how Redgate’s Test Data Manager can help. Automated testing uses software tools to execute predefined tests on... The... Product Upgrades and Releases [Updates from 162.1 and 162.2 releases of SqlPackage and the DacFx ecosystem]( From Azure SQL Within the past 4 months, we’ve had 2 minor rele... [Azure Data Studio 1.48 is now available]( From MS SQL Server Blog Azure Data Studio is a cross-platform database too... [General Availability: Encryption at host for Premium SSD v2 and Ultra Disks is now available in more regions.]( From Azure Updates Encryption at host for Premium SSD v2 and Ultra Di... [Updated First Responder Kit and Consultant Toolkit for February 2024]( From Brent Ozar Unlimited New this month: better sp_BlitzIndex performance on databases with tens of thousands of objects, sp_DatabaseRestore can run test scripts, and David Wiseman and Sean Killeen implemented basic automated testing... [Public Preview: Zone redundancy for Azure SQL Database Hyperscale elastic pools]( From Azure SQL Since we launched Hyperscale elastic pools preview at Build 2023, many customers have adopted it to achieve significant price-performance optimization of their Hyperscale databases. Today, we are pleased to announce that... [Public Preview: Azure SQL updates for late-February 2024]( From Azure Updates Public preview enhancements and updates released for Azure SQL in late-February 2024. [General availability: Latest PostgreSQL minor versions supported by Azure Database for PostgreSQL - Flexible Server]( From Azure Updates Azure Database for PostgreSQL – Flexible Server now supports minor versions 16.1, 15.5, 14.10, 13.13, 12.17, 11.22  [General availability: Azure SDKs support for Azure Cosmos DB for PostgreSQL]( From Azure Updates Now you can use Azure SDKs including .NET SDK, Go SDK, Java SDK, JavaScript SDK, and Python SDK to manage the lifecycle of Azure Cosmos DB for PostgreSQL clusters. [General availability: Azure CLI support for Azure Cosmos DB for PostgreSQL]( From Azure Updates You can now use Azure CLI to manage the whole lifecycle of Azure Cosmos DB for PostgreSQL clusters from provisioning to scaling to configuration changes and starting/stopping cluster compute. Python [Python: How to Use a Generator Function]( Want to produce a large sequence of values in Python without storing them in memory at once? A generator function is what you need. [How to Use Loops in Python]( A loop is a portion of code that repeats a set number of times until a desired process is complete. Here's how to do loops in Python. R Language [Demystifying Data Types in R: A Beginner’s Guide with Code Examples]( Ever wondered what kind of information your data holds in R? Knowing the data type is crucial for performing the right analysis and avoiding errors. This post will equip you with the skills to check data types in R, making your coding journey smoother and more efficient. SQL Server Security and Auditing [[Some] SQL Server and Azure SQL DB Security Fundamentals | Data Exposed]( From Azure SQL Learn about SQL Server and Azure SQL Database secu... Security News and Issues [Is Passkey Authentication More Secure Than Traditional Passwords?]( From IT Pro - Microsoft Windows Information, Solutions, Tools Many organizations are interested in using passkey... [NIST Cybersecurity Framework 2.0]( From Schneier on Security NIST has released version 2.0 of the Cybersecurity... [A Cyber Insurance Backstop]( From Schneier on Security In the first week of January, the pharmaceutical giant Merck quietly settled its years-long lawsuit over whether or not its property and casualty insurers would cover a $700 million... [AIs Hacking Websites]( From Schneier on Security New research: LLM Agents can Autonomously Hack Web... T-SQL and Query Languages [Counting more efficiently]( Nearly a decade ago, I wrote a post called “Bad habits : Counting rows the hard way.” In that post, I talked about how we can use SQL Server’s metadata to instantly retrieve the row count for a table, which is great when you want to count the whole table without size-of-entire-table reads, but gets more complicated if you need to retrieve the count of rows that meet – or don’t meet – some criteria. Sometimes an index can help, but not always, depending on how complex the criteria might be. [Find and Replace Text in Strings in T-SQL]( From Simple Talk String manipulation is an inevitable task for deve... [Query Exercise: Finding Sister Locations to Help Each Other]( From Brent Ozar Unlimited For this week’s query exercise, let’s start wi... [Friendly Deletes in SQL Server]( From Simple Talk One of the first things you learn when working wit... [The How To Write SQL Server Queries Correctly Cheat Sheet: Joins]( From Erik Darling Data So Many Choices SQL Server is full of landmines options when you’re writing queries. For most queries, you don’t need much beyond the basics. Think of your standard CRUD... Tech News [IT Workers Support DEI Programs, Despite Broader Backlash]( From IT Pro - Microsoft Windows Information, Solutions, Tools The IT sector sees resilient growth in diversity, ... [Guide to Migrating From VMware: Why and How to Move to an Alternative Platform]( From IT Pro - Microsoft Windows Information, Solutions, Tools This comprehensive guide provides steps for successfully migrating from VMware to an alternative platform for running virtual machines. [FinOps Teams Prioritize Waste Reduction Amid Economic Uncertainty]( From IT Pro - Microsoft Windows Information, Solutions, Tools It's no surprise that organizations are looking to... [How Antarctica’s history of isolation is ending—thanks to Starlink]( From Technology Review Feed - Tech Review Top Stories “This is one of the least visited places on planet Earth and I got to open the door,” Matty Jordan, a construction specialist at New Zealand’s Scott Base in... [Conversational AI revolutionizes the customer experience landscape]( From Technology Review Feed - Tech Review Top Stories In the ever-evolving landscape of customer experiences, AI has become a beacon guiding businesses toward seamless interactions. While AI has been transforming businesses long before the latest wave of... The Lighter Side [The tech that helps these herders navigate drought, war, and extremists]( From Technology Review Feed - Tech Review Top Stories Hainikoye hits Accept and a young woman greets him... [A New Word: Aesthosis]( From SQLServerCentral Blogs aesthosis– n. the state of feeling trapped insid... [The world’s most famous concert pianos got a major tech upgrade]( From Technology Review Feed - Tech Review Top Stories At a showroom in a Boston suburb, Patrick Elisha s... [This company makes wood products without trees]( From Technology Review Feed - Tech Review Top Stories As she walks across Foray’s lab on the third flo... Theory and Design [Using a SQL Tokenizer]( Imagine this: you have several directories full of SQL script files, and you need to know where a certain table is used. You’d rather like the context too, so you can check the whole SQL Expression and work out why it is running so slowly. To do this, you'll need a tool that is able to recognise and return a collection of strings representing the SQL code, divided up into the respective components of the SQL language. For this article, we’ll use a PowerShell cmdlet, called Tokenize-SQLString. Tools for Dev (SSMS, ADS, VS, etc.) [Upcoming changes for SQL Server Management Studio (SSMS) - Part 2]( From MS SQL Server Blog The second post in our series about SQL Server Management Studio (SSMS) coincides with the SSMS 20 Preview 1 release – now available! However, before you download and install... [Improve Language Matching in Vscode]( From Sheldon Hull Let’s say you have the Red Hat YAML extension. T... [Vote For My Idea: SSMS Right Click To Open Execution Plans In A New Tab]( From Erik Darling Data SSMS Right Click To Open Execution Plans In A New Tab Vote for my great idea here. Sort of odd, I have no idea how this idea ended up...  [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -