A Fine Slice Of SQL Server
This post demos a script I put together to help move SQL Server database files to another drive and folder by generating PowerShell and T-SQL commands.
I’ve decided to make this script for situations where installing the dbatools PowerShell module wouldn’t be possible.
Otherwise, I highly recommend using dbatools’ Move-DbaDbFile command.
Click through for the script, and I second Vlad’s recommendation of dbatools for this kind of effort.
Comments closedIt’s easy to focus on user databases and overlook the system databases at the core of every SQL Server instance. However, the master, msdb, and model databases form the backbone of your environment. Understanding each database’s specific responsibilities, how to safely customize them, and how to protect them from data loss or corruption is important for maintaining a stable server. In this post we take a look at all three databases to better understand their purposes and proper maintenance.
Click through for some recommendations for each of master, msdb, and model. And please make sure you’re backing these up. Many backup jobs ignore system databases, and that’s not a great situation to be in when you’re trying to rebuild a SQL Server instance because of corruption in the master database.
Comments closedAnthony Nocentino continues a series:
In this post, the fourth in our series, I want to share an example demonstrating SQL Server 2022’s T-SQL Snapshot Backup feature in a scenario where a database spans multiple storage arrays. If you’re dealing with multi-array environments, you’ll appreciate how this technique freezes database write I/O to take coordinated snapshots across volumes on two FlashArrays. In this post, I’ll walk you through the process, point out some of the script’s key elements, and show you how long the write I/O pause takes.
Click through to learn how it all works when you have a database spanning multiple volumes.
Comments closedBrent Ozar lays out an argument:
Accelerated Database Recovery (ADR) is a database-level feature that makes transaction rollbacks nearly instantaneous. Here’s how it works.
Without ADR, when you update a row, SQL Server copies the old values into the transaction log and updates the row in-place. If you roll that transaction back, SQL Server has to fetch the old values from the transaction log, then apply them to the row in-place. The more rows you’ve affected, the longer your transaction will take.
With ADR, SQL Server writes a new version of the row inside the table, leaving the old version in place as well.
Because you’re a smart cookie, you immediately recognize that storing multiple versions of a row inside the same table is going to cause a storage problem: we’re going to be boosting the size of our table, quickly. However, the problem’s even bigger than that, and it starts right from the beginning when we load the data.
This was an interesting analysis, looking at table growth with ADR + RCSI, with ADR or RCSI alone, and with neither feature. Given that I’m all-in on RCSI, this is particularly interesting to me. And if you want to dig really deeply into index maintenance, Jeff Moden has a fantastic set of presentations, which TriPASS recorded in 2021: GUIDs vs Fragmentation and LOB data. These two presentations help provide sound footing for deciding under what circumstances it makes sense to rebuild an index, and noting that (unless you’re Brent), the answer is probably “less often than you’d think.”
Comments closedKenneth Omorodion clears out some data:
More than before, organizations now aim for a well-defined approach to manage their data storage effectively. Some reasons for this include operational efficiency, cost management, regulatory compliance, and strategic decision-making. In this article, I will describe an approach on data retention management for Lakehouse files to manage data storage when the data exists as files in the Fabric Lakehouse.
There’s nothing built in but Kenneth makes it easy.
Comments closedSakshi Jain has an announcement:
Today, when an item owner leaves the company, their credentials expire, or they lose access, many Fabric items cease to function. For example, Lakehouses and their SQL Endpoints become inoperative, Pipelines fail to execute due to user access errors. In these situations, enabling another user to assume ownership would ensure business continuity.
We are pleased to announce that Fabric users with the right permissions can now take ownership of Fabric items.
This is a big deal. for the same reason that we don’t want individual users to own databases in SQL Server, having individual users own objects in Fabric lakehouses and endpoints was always a risky play. At least now, there’s a way to handle when that person leaves the company.
Comments closedMika Sutinen builds a baseline:
I’ve recently had to work a bit more with the Microsoft Defender and the vulnerability assessment in Azure. Following those efforts, it dawned to me that the topic of security baselines is sometimes slightly misunderstood. So, in this post, we’ll look into what a security baseline should cover (and what they probably shouldn’t).
But first things first. Security baselines are provided by the Microsoft Defender for Cloud service, which I always recommend enabling for Azure workloads (unless there’s a 3rd party solution for it already). If you don’t have anything of the sorts enabled for your databases and servers, I highly recommend you go and turn Defender on. Seriously. Do it now.
Read on to learn more about why having a security baseline is so important and where to draw the cut-off between security and functionality.
Comments closedYou can’t just exec
DROP ROLE your_role_name;if it’s granted perms or other roles are granted to it. I had to go fishing to find all the grants to revoke them. Note: if you are worried about re-granting later, you can always fiddle with this to output the grants for these perms as a rollback.
Read on for a few scripts to help out with finding what that role owns, revoking rights, and reassigning ownership.
Comments closedIt’s easy to do with this query. That’s all I have to say on this topic for now.
Click through for the query, which includes examples of concatenation, size with units (e.g., KB, MB, GB), and more.
Comments closedMonitoring SQL Server databases is one of the main responsibilities of DBAs, both in on-premises and on the public cloud platforms. Continuing from my previous post, where I reviewed the various options of SQL Server PaaS offerings from major hyperscalers, I’ll now focus on the monitoring solutions they provide.
While I always prefer commercial tools that come packed with features, it’s not the only way to go. Whether you’re using AWS, Azure, or GCP, each platform offers unique tools and features to help you keep an eye on your managed SQL Server database services.
Read on for information about what’s available in each.
Comments closed