A Fine Slice Of SQL Server
Andy Yun notes a nice feature in SQL Server 2022:
Using QAT compression for your native SQL Server backups will give you better compression with less CPU overhead, than “legacy” compression. So I get smaller backup files, faster, with less CPU burn. What’s there to not like?
There’s a lot to dig into with this post and a special note on Transparent Data Encryption at the end.
Leave a CommentGlenn Berry makes some recommendations:
Does making your SQL Server database backups twice as fast sound interesting? SQL Server 2022 has new options to help you improve SQL Server database backup performance. If you are on an older version of SQL Server, you still have options for Improving SQL Server Database Backup Performance.
In order to improve your database backup performance, you need to understand what is happening during a database backup and what your bottleneck(s) are.
Read on for an overview of the key considerations.
Leave a CommentGarry Bargsley restores a backup:
My post for T-SQL Tuesday #154 is going to be a demo of the new SQL Server feature that allows you to restore an Azure SQL Managed Instance backup to a SQL Server 2022 instance. Actually, I am not sure if this is a feature or just an enhancement that allows for this behavior to work.
Current versions of SQL Server do not allow the restoration of backups taken on Azure SQL Managed Instance. Managed Instances are considered “vNext” and runs a different version that is beyond anything on-premises could keep up with due to the frequent changes being applied to Managed Instance. There has always been a disconnect with the restorability between the two platforms, so it is good that Microsoft has found a solution to this limitation.
I’d still like to see the ability to fail from SQL MI to on-prem (or VM) SQL Server—you can go from on-prem to Azure SQL MI, though it sounds like right now, failback is a database restore.
Leave a CommentManvendra Singh takes a backup:
This article will explore backup options available in the Azure Cosmos DB service. Backups are very important to safeguard our data in case of data corruption, data deletion, system failure, or any unforeseen circumstances like DR. We have planned, configured, and managed it for our on-prem databases whether it is SQL Server, Oracle, DB2, or system files on various machines. DBAs and Infrastructure admins have ensured to keep a backup of all these systems to safeguard their data. Similarly, we must also secure our data hosted in a cloud environment for any services whether it is Azure VMs, Azure SQL, Cosmos Db accounts, or any other services. Today we will talk about backup options available to secure cosmos DB databases and their contents.
Click through for those two options.
Leave a CommentNiko Neugebauer reports on some performance improvements:
There are multiple phases of the SQL MI database restore process, which, aside from the usual SQL Server restore operations, includes registering databases as an Azure asset for visibility & management within the Azure Portal. Additionally, the restore process forces a number of specific internal options, and some property changes such as forcing the switch to the FULL recovery model and forcing the database option PAGE_VERIFY to CHECKSUM, as well as eventually performing a full backup to start the log chain and provide full point-in-time- restore options through the combination of full and log database backups.
The restore operation on SQL MI also includes log truncation, and the execution time for the truncation has been vastly improved, which means that customers can expect their entire database restore process to become faster on both service tiers.
Click through to see what kind of performance gains you can expect.
Comments closedEtienne Lopes performs a migration:
In this post I’m going to show a very simple way to migrate a database between two SQL Server managed Instances in Azure. I’m not a big fan of bacpac files (although I work with it when necessary) so I’ll use a different approach here. Besides, when creating a bacpac file using SSMS there are some schema validations that occur at the beginning that will abort the bacpac generation for example if the database holds three-part names inside stored procedures. While not supported in SQL Azure DB it is supported in SQL Managed Instances (as are cross-database queries), and it can be quite frustrating to experience this show stopper when using bacpac’s to migrate or copy databases between Managed Instances.
Click through for the demo. And yeah, I’ve run into limiting factors with bacpacs, such as having certificates for encrypting data (even if you back those up separately).
Comments closedChad Callihan reasons through a use case:
I’m a big fan of Ola Hallengren’s SQL Server maintenance scripts and would recommend that anyone working with SQL Server check them out. They have served me well over the years. As it relates to today’s blog post, maybe too well…
I recently ran into a strange situation with the DatabaseBackup stored procedure that had me scratching my head: a backup job completing successfully for a database that didn’t exist.
Confused? So was I. Let’s take a look at how it happened.
Click through for the scenario.
Comments closedTom Collins enumerates several reasons for a transaction log backup chain breaking:
The SQL Server transaction log backup chain aka log chain is the series of sequential transaction log backups related to a database. The log backups are related to each other and are represented through LSN . Breaking the transaction log chain will limit the restore point of the backups.
Click through for four such reasons as well as a scenario explaining how it could happen.
Comments closedLee Markum reviews backup logs:
As data professionals responsible for SQL Server, it is drilled into our heads that we need to take backups. But, how do we know we actually have backups available to us when we need them? How can we verify that a backup has been taken? Some types of auditing that an employer has to undergo might require proof of backups. How will you provide it?
Read on for three options. Note that this post is about finding the existence of backups, not checking to see if the backups are any good.
Comments closedKevin Chant comes at us with important advice:
In reality, not being able to restore a database can damage your company’s reputation and you can end up losing colleagues for various reasons. For example, if they ask for restores to be tested and it never happens.
To save your colleagues from a database restore nightmare you must test restoring your database backups at some level. Otherwise, it can lead to serious issues.
As the saying goes, you don’t have a backup until you’ve successfully restored it.
Comments closed