When deciding on a backup strategy for a database, there are various things we must consider: Does this database need point-in-time recovery? What are the RPO (data loss) and RTO (downtime) goals? No one likes losing data or encountering unplanned downtime, but the business must decide on these goals so that we can setup an…
You've got this database that you swear doesn't need to be backed up, like:
Read-only databases
Schema-only databases with no data in 'em
Archive databases that haven't changed in years
Maybe even system databases like master/model/msdb
Disclaimer: I love Azure SQL Managed Instances (MIs) so far. They're really cool, and I'm having a fun time playing with them. I'm about to describe a bug, and I'm sure this bug will be fixed soon, so it shouldn't stop you from previewing Managed Instances. This post is not "MIs are t3h suxxors" -…
You've built your first Managed Instance, and now you wanna play around with real live data. You're going to need a backup in the cloud to do it - SSMS doesn't have a drag-and-drop option.
Big picture, here's what we're going to do:
So much, so often
Ah, backups. Why are they so tough to get right?
You start taking them, you find out you're not taking enough of them, or that they're not the right kind, or that you're not using checksums or compression, or that you're not storing them in the right place, or that the storage isn't redundant.
Clientele
Most of out customers are on slightly (ahem) older (AHEM) hardware, and they have big questions:
How do we know what kind of hardware we need?
How do we load test it?
How do we know what to change to make it better?
You've been such a good database administrator.
You followed the setup checklist in our First Responder Kit. You ran sp_Blitz. You set up email alerts for common issues. You run CHECKDB as frequently as practical - weekly, or maybe even daily.
Normally, when you use Transparent Data Encryption (TDE) to secure your database at rest, you can't get backup compression. Encrypted data basically looks like random data, and random data doesn't compress well.
SQL Server 2016 introduced the ability to compress your TDE databases. Yay!
Cash Rules
Most people, when they get through paying for Azure, and SQL Server Enterprise Licensing, are left with a hole in their wallet that could only be filled with something that says "Bugatti", and has a speedometer with an infinity sign at the end.
So far in this series, I've shown you how to corrupt a clustered index, then how nonclustered indexes can be corrupted independently. If you haven't read those, you should start there first.
Let's start again with our 50Ways database, but this time we're going to be good and make sure that we've got checksums enabled, and that we're in full recovery model:
Carry on
It turns out that the only thing harder than checking for new databases restored to a SQL Server, is checking a folder for a backup of a database that doesn't exist on another SQL Server.
Building stuff
I sometimes really hate coming up with ideas, and much prefer someone to just say "I want this" so I can go out into the world and figure out how to do that. Occasionally though, I realize that I'm not going to get all that much direction. That's what happened with sp_BlitzQueryStore.
Phantom of the Nopera
Let's say you have a server. Let's go one step further and say SQL Server is running on it. Crazy, I know.
But hey, we specialize in crazy, here.
In our Faux PaaS project, we need a backup plan - or rather, a restore plan.
On each SQL Server instance, clients can create as many databases as they want, anytime they want, with no human intervention. We need those databases covered by disaster recovery as quickly as practical.
Brent had an idea that we should set aside a day per month where we could build things in the cloud and blog about our experiences. I was tasked with Amazon RDS for SQL Server: create an instance, configure backups and test point-in-time restores. After grabbing some coffee, I logged into the AWS console. We…
We're trying something new at the company: Builder Day. We define a slightly-out-of-the-norm task, and then the team splits up and tackles the task on different cloud platforms, writing it up as we go. These posts aren't going to make you an expert on the topic - they're just meant to let you skim through technologies you might not otherwise get the chance to play with. The cloud changes fast, so some of this is just about seeing what providers are up to these days.
For some of you, the headline is self-explanatory, and you just want the URL, so here it is: https://github.com/olahallengren/sql-server-maintenance-solution For the rest of you, here's what it means. What are Ola Hallengren's maintenance scripts? Ola Hallengren's maintenance scripts are a widely used replacement for maintenance plans. His backup, integrity check, and index optimization stored procedures are…
We got an email!!! It was weird. It was from an Australian, and it was right side up. I could hardly believe it. Normally we encourage people to ask us questions during Office Hours, or over on Stack Exchange. That way lots of people can benefit from the answer. But since this reader is on…
You use Ola Hallengren's maintenance scripts to do your database backups on SQL Server 2016 or newer, and you want to do things like:
Restore the most recent database backup
Test it for corruption
Initialize restores to get ready for log shipping or replication
In part 1, I showed you how to corrupt a database - not because you'd need to do it, or because SQL Server has some kind of vulnerability. I just wanted to show you how SQL Server reacts when it finds corruption in a data file, why you should check your databases for CHECKSUM settings, and set up alerts so you can react fast.
