A Fine Slice Of SQL Server
In this post I want to cover how you can create a dacpac for SQL Server 2022 databases using sqlpackage. So that you keep the new SQL Server 2022 compatibility level when you deploy new databases.
Just to clarify, a dacpac file is a special type of file which contains details about SQL Server database objects. Which you can use to deploy database updates to other SQL Server databases.
Read on for initial thoughts, a post-upgrade experience, and more.
Comments closedAnthony Nocentino is excited about using storage snapshots in SQL Server 2022:
But what if I told you that you could seed your Availability Group from a storage-based snapshot and that the re-seeding process can be nearly instantaneous?
In addition to saving you time, this process saves your database systems from the CPU, network, and disk consumption that comes with direct seeding and using backups and restores to seed.
This process described in this post is imlemented on Pure Storage’s FlashArray and works cloud scenarios on Pure’s Cloud Block Store.
Click through to see how.
Comments closedJess Pomfret doesn’t waste time:
Log shipping is a SQL Server feature used for disaster-recovery where the transaction log backups are ‘shipped’ from your production instance to a secondary instance. This enables you to cutover to this secondary server in the event of a disaster where you lose your primary instance. Log shipping is not a new feature but is still quite popular.
Recently I was tasked with setting up Log Shipping for a reasonably large production database. I didn’t want to initialize the secondary database with a new full backup as I was already taking full and log backups of the database. In this case we have the option of initialising the database by restoring the full & log backups up to the latest point in time and then configuring log shipping.
Read on to see how you can use dbatools to do this easily.
Comments closedChad Callihan tempts Betteridge’s Law of Headlines:
When you’re specifying WITH CHECKSUM as you’re backing up databases, SQL Server will use checksums to help catch any inconsistencies with pages. This seems like a setting that you should always use and would expect to be a default setting. So why doesn’t SQL Server include it by default?
Using the principle that a backup isn’t valid until it’s verified, CHECKSUM acts as a useful but not sufficient check.
Comments closedKevin Hill learns a new trick:
How long has this worked, and why didn’t anyone tell me?
A typical restore statement from a FULL backup and a LOG backup:
Click through to see what Kevin means and how you might be able to make your life a little easier.
Comments closedAndy Yun digs into a SQL Server backup file:
This first came up during my onboarding with Pure Storage. Anthony Nocentino (b|t) taught me that a SQL Server backup file is a byte-for-byte copy of your data, as materialized in SQL Server MDF files (assuming no backup compression or backup encryption). And that would make sense – how else would SQL Server store a copy of your data in a backup file? It does not make sense for SQL Server to alter your data when it writes it down to a backup file (again, with NO backup compression/encryption) – that’s a waste of compute and effort.
Well, I had a conversation with someone who was unclear about that assertion. I tried some Google-fu to present some supporting materials, but could not actually find any documentation, official or otherwise, to back it up. So here we are.
Click through to dive into a backup file with Andy.
Comments closedTaiob Ali takes us through a new DMV:
There is a new DMV currently in preview which returns information about backups of Azure SQL databases except for the Hyperscale tier. Microsoft official documentation is here.
If you run the example query as-is from the above documentation some of the columns do not make sense.
Taiob includes a better query which provides the type of information you’re used to in on-premises SQL Server.
Comments closedGreg Dodd diagnoses a problem:
I hit an error recently on a server that caused backups to fail. The database was backing up to a UNC path. Looking in the SQL Log file and Event Viewer, I found the following error:
The operating system returned the error ‘121(The semaphore timeout period has expired.)’ while attempting ‘DiskChangeFileSize’ on ‘\\uncpath\folder\databasename.bak’.
Read on to see what caused this error.
Comments closedTom Collins has a backup to restore:
I have a SQL Server with TDE enabled and the user databases are TDE configured. I need to take a backup and restore the TDE enabled database to another SQL Server Instance . Could you take me through the steps including prerequisites?
The answer is yes. And Tom is so kind as to show the answer rather than giving a flippant response, which is my modus operandi.
Comments closedJoey D’Antoni troubleshoots a problem:
We had a case over the weekend where our automated restore process at client got hung up on this wait type, for a single database. What was the unique characteristic about this relatively medium (2-300 GB) database? It had a lot of filestream data–it seemed like the file count wasn’t that high, but my guess is the filestream data was the majority of the data in that database. When the job hung up, the restore had been waiting on PREEMPTIVE_OS_FILEOPS for over a day and still had a null value for percentage complete.
Click through to see what the problem was.
Comments closed