A Fine Slice Of SQL Server
Andy Yun wraps up a series on backup performance:
Welcome back to Part 5 of my Backup Internals Series. In this blog, I want to explore some thoughts around maximizing the performance of your SQL Server backup operations.
This entire series has been a must-read for any DBA.
Comments closedPicture this, you’re happily backing up your database to a Azure blob storage until suddenly it starts mysteriously failing with the error…
Write to backup block blob device https://****** failed. Device has reached its limit of allowed blocks.
What’s going on, nothing’s changed?!
Read on to learn the cause of this issue as well as three ways to fix it.
Comments closedAnthony Nocentino has a public service announcement:
SQL Server 2022 introduces a new feature to enable application-consistent snapshot backups. TSQL Snapshot Backups enable the SQL Server to control the database quiesce without external tools. Using TSQL Snapshot backups enables instantaneous restores, independent of the size of data, for a database, group, or server backups, including point-in-time recovery.
When you use this feature, it freezes I/O. You’ll see a record like this in your error log when you execute the command
ALTER DATABASE TestDB1 SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON. This blog post will show you that the I/O freeze is just for write operations. You can continue to read from the database during this frozen state.
Read on to understand what’s going on, what “frozen” really means, and why this is a huge improvement over the classic behavior of the volume snapshot service.
Comments closedSabrin Alsahsah keeps backups around:
In this blog article, we will cover how to prevent and deny the automated backup retention days for Azure SQL database to be lower than X number of days.
We have received some service requests from customers that would like to ensure that they have for example 14 days as a retention period for their automated backup and would like to do this using a custom policy since they have multiple databases.
You can follow the steps below to create a custom policy and ensure this:
Click through for that process.
Comments closedAndy Yun continues a series on how backups work in SQL Server:
Welcome back to Part 4 of my Backup Internals series. Today, I’d like to spend a little time exploring backup compression.
When you take a regular FULL BACKUP, SQL Server is literally taking a byte-for-byte copy of your data files. Don’t believe me? Then go read this, then come back. Additionally, Microsoft architected BACKUP operations such that the resource utilization and impact would be minimal (when using default parameters).
This post taught me a few things about the practical impact of enabling compression. Even after reading this, however, I would almost always enable it for two reasons. First, storage is usually the bottleneck for organizations, so actions which reduce storage utilization can improve overall performance. Second, there are limits to how much we can store, so compressing backups may let me get away with holding more backups on a given LUN or drive.
Comments closedChad Callihan knows your last backup is only as good as your last restore:
Is the process of testing your backups something you know you should do but never get around to? Do you find yourself assuming all is well with backups while putting out other fires? Test-DbaLastBackup, part of the beloved dbatools, can solve your dilemma.
There are many options available when using Test-DbaLastBackup. Let’s explore a few of these options and see some examples of how to use them.
Click through to learn more about this. And you could easily put together Powershell scripts to stagger your restorations over a time frame (such as, 15% of your databases each day, so that you get to 100% by the end of the week).
Comments closedStrahinja Rodic announces a new feature going to GA:
In September last year SQL server 2022 introduced new feature – backup and restore to simple storage service (S3) – compatible object storage that grants the user the capability to back up or restore their databases using S3-compatible object storage, whether that be on-premises, or in the cloud.
To provide this integration Azure SQL MI is enriched with a new S3 connector, which uses the S3 REST API to connect to Amazon S3 storage. It extends the existing RESTORE FROM URL syntax by adding support for the new S3 connector using the REST API.
Click through to see what you need to have set up for it to work, as well as the restoration process.
Comments closedSteve Jones stands by for station identification:
Sometimes you want to restore part of your data, but you still want the option to continue restores. A classic example of this is when you are restoring a number of transaction logs and want to check the data to find a place where certain values haven’t been changed.
Suppose someone deletes a bunch of data between 10am and 11am from the supplier table. You know that they added “Acme” to this table before the delete. You might restore up to 10am and check the supplier table for the old data and look for Acme. If it’s not there, maybe you restore the 10:05am log backup and check again. If it’s not there, then the 10:10am log, etc.
Click through to see how you can do that.
Comments closedWelcome back to Part 3 of my SQL Server Backup Internals Series.
In Part 1, I introduced the “parts” of a BACKUP Operation and in Part 2, we delved into Backup Buffers. Today, we’re going to talk about what manipulates those Backup Buffers = CPU Threads. This’ll be a longer blog, so go refill your coffee now.
Andy did an outstanding job explaining what reader and writer threads do and how SQL Server picks the numbers of each.
Comments closedMeagan Longoria runs into a problem:
I have a client who was upgrading some servers from pre-2022 versions of SQL Server to SQL Server 2022 CU7. They had some multidimensional SSAS cubes that were to go on the new server. But they ran into an issue after the upgrade. After restoring a backup of an SSAS database to the new server they found that they could no longer modify the data source using SSMS.
Read on to see what the problem was, as well as how to fix it.
Comments closed