Press "Enter" to skip to content

Category: Administration

Backup to URL via Managed Identity in SQL Server 2022

Published 2025-02-18 by Kevin Feasel

Joey D’Antoni doesn’t trust user logins:

Backing up databases to the cloud is not a new thing. Microsoft introduced the BACKUP TO URL functionality to SQL Server 2012 SP1 CU2. I’m not going to tell you how long ago. Still, it wasn’t last month, and Microsoft recently celebrated the 15th anniversary of Azure so that you can get an idea. When the feature started—it was minimal; you could only backup a database of up to a single terabyte and couldn’t stripe over multiple files. Additionally, you had to use the access key to the storage account, which gave complete control over the storage account—that wasn’t a good thing.

Read on for a quick overview of the feature and guidance on how it all works.

Leave a Comment

Migrating or Copying a Semantic Model across Microsoft Fabric Workspaces

Published 2025-02-18 by Kevin Feasel

Sandeep Pawar makes a move:

Here is a quick script to copy a semantic model from one workspace to another in the same tenant, assuming you are contributor+ in both the workspaces. I tested this for a Direct Lake model but should work for any more other semantic model. This just copies the metadata (not the data in the model) so be sure to set up other configurations (RLS members, refresh schedule, settings etc.). That can also be changed programmatically, thanks to Semantic Link Labs, but I will cover that in a future post.

Read on for the script, as well as an update from Sandeep on how you can do this even more easily.

Leave a Comment

Migrating a SQL Server Password without Knowing the Plaintext Value

Published 2025-02-18 by Kevin Feasel

Vlad Drumea rebuilds a machine:

Export-DbaLogin does a great job of exporting all logins, but it doesn’t offer a way to migrate the sa password to another instance.
In this case, I didn’t have the sa password and was required to ensure that the rebuilt instance is a 1:1 copy of the original one (edition excluded).

This also meant that the existing password used for sa had to be transferred to the rebuilt instance.

Read on to see how you can accomplish this.

Leave a Comment

Checking out the mssqlsystemresource Database

Published 2025-02-17 by Kevin Feasel

Stephen Planck goes into single-user mode:

Have you ever wondered where SQL Server actually stores the definitions for system objects like built-in stored procedures, system views, and functions? Enter the mssqlsystemresource database—often just called the “Resource” database. While this database remains hidden from everyday use, it plays a pivotal role in the internal workings of SQL Server. In this post, we will explore what the Resource database is, why it’s necessary, and what every DBA should know about it.

Read on for a primer on what’s in this database and why we typically shouldn’t mess with it.

Leave a Comment

Scaling with PostgreSQL

Published 2025-02-11 by Kevin Feasel

Shayon Mukherjee shares some tips about scaling in PostgreSQL:

“Postgres was great when we started but now that our service is being used heavily we are running into a lot of ‘weird’ issues”

This sentiment is frequently echoed by CTOs and senior engineers at high-growth startups when I speak with them.

Scaling PostgreSQL successfully doesn’t always require a full team of DBAs and experts. The beauty of PostgreSQL is that solutions often lie within the database itself – by rethinking your data access patterns from first principles, you can solve many business problems at scale.

In this post, I’ll address some common “weird” issues I’ve encountered and explore solutions that work at scale.

I like some of them, though I’m not a fan of eliminating or delaying foreign key constraints, as those are important for data quality. I’m not knowledgeable enough in PostgreSQL administration to have a strong opinion on these, however.

Leave a Comment

Kusto Query Performance in Microsoft Fabric

Published 2025-02-07 by Kevin Feasel

Dennes Torres checks some stats:

We already discovered how to investigate Kusto query history. Let’s discover how to analyse query performance considering the information on this history.

The query history returns 3 fields we can use to make a more detailed analysis of the queries: CachedStatistics, ScannedExtentsStatistics and ResultsetStatistics.

Disclaimer: There are low to no documentation about this content. In this way, the content below may not be 100% precise but will give you good guidance.

Click through to learn more about these three.

Leave a Comment

SQL Server Migration via Distributed AG

Published 2025-02-06 by Kevin Feasel

David Fowler makes a move:

Because it doesn’t require a common cluster, a distributed availability group allows us to link servers in situations were a cluster isn’t possible. Servers could be in remote locations, members or different domains, different OS levels or even different operating systems (yes, we can link Windows and Linux based AGs).

The ability to link servers in this way gives us a very nice and easy way to replicate data between servers when thinking about a migration.

David’s scenario involves a SQL Server upgrade. I’ve seen this work really well in practice for a 2017 to 2019 upgrade. With applications pointing to the listener agent and everything in place, you can have as little as a few seconds of downtime for that upgrade, which is amazing when you think about how we’ve historically migrated to new versions of SQL Server.

Leave a Comment

Moving SQL Server Database Files

Published 2025-02-06 by Kevin Feasel

Vlad Drumea makes a move:

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.

Leave a Comment

Care and Feeding of System Databases in SQL Server

Published 2025-02-03 by Kevin Feasel

Stephen Planck reminds us to look at the primary system databases in SQL Server every once in a while:

It’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.

Leave a Comment

Multi-Storage Array Database Snapshots in SQL Server

Published 2025-02-03 by Kevin Feasel

Anthony 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.

Leave a Comment