Backup and Recovery

It’s easy to get caught up in daily incidents, tickets, and special projects. Like a good scout, though, a core task in a DBA’s job is to be prepared. You have to set time aside in your schedule to: 1. Practice responding to corruption. Gail Shaw’s “Help, my database is corrupt, now what?” post should…

How to Make SQL Server Backups Go Faster

December 10, 2015

At its core, backup speed boils down to just two numbers. Question 1: How fast can SQL Server read data from disk? You can measure this by doing a full backup to disk, but use the NUL: option: Transact-SQL BACKUP DATABASE MyDb TO DISK='NUL:' 1 BACKUP DATABASE MyDb TO DISK='NUL:' This does a backup, but doesn’t…

SQL Server 2016 CTP2.4: Maintenance Plan Changes

September 30, 2015

You can download the next preview of SQL Server 2016 today, and the announcement blog post touted a few improvements to maintenance plans. Oddly, the screenshots in the announcement were apparently done with Microsoft Paint, so I fired up a VM to see how things really look. The DBCC CHECKDB step in the wizard now has…

Why RPO and RTO Are Actually Performance Metrics Too

September 10, 2015

Architecture, Backup and Recovery, CHECKDB and Corruption, SQL Server

Most companies come to us saying, “The SQL Server isn’t fast enough. Help us make it go faster.” They’re kinda surprised when one of the first things we fill out together is a variation of our High Availability and Disaster Recovery Planning Worksheet: Download the full PDF in our First Responder Kit They say things like, “Wait,…

Synchronous Always On Availability Groups Is Not Zero Data Loss

September 3, 2015

Always On Availability Groups, Backup and Recovery

always on, availability groups

In theory, when you configure AlwaysOn Availability Groups with synchronous replication between multiple replicas, you won’t lose data. When any transaction is committed, it’s saved across multiple replicas. That’s the way it works, right? I mean, except when you restart your synchronous replicas, or patch them, or they just stop working for any number of reasons.…

Getting the last good DBCC CHECKDB date

August 5, 2015

Erik Darling

dba

whether it’s a new job or a new (old) server If you’re reading this, you’ve at some point in your career stared at a server, for the first time, with great trepidation. The smarter you are, the greater your trepidation is. The 2nd century mathematician Trepidatius the Wimpy had an equation that described this, but…

Should You Put Things in the master Database?

June 27, 2015

Always On Availability Groups, Backup and Recovery, SQL Server

Growing up as a young boy, my elementary teachers always warned me about the perils of putting stuff in master. But what’s the real deal? Why is this such a bad idea? System databases are often located on space-limited drives. The default installation parameters for SQL Server throw master, model, and msdb all on the…

How to Configure Always On AG Backups with Ola Hallengren’s Scripts

June 25, 2015

Always On Availability Groups, Backup and Recovery, Ola Hallengren's Database Maintenance Scripts

always on, backup, ola hallengren

With SQL Server AlwaysOn Availability Groups, you can offload backups to a replica rather than running them on the primary. Here’s how to do it: 1. Install Ola Hallengren’s utility scripts on all of the replicas. During the install, it creates a laundry list of SQL Agent jobs, but doesn’t set up schedules for any of them.…

How Do You Manage DBAs? Part 1: Measuring Backups

June 22, 2015

Backup and Recovery, Processes and Practices

When the DBA reports to you, ask yourself two questions: “What would I fire the DBA for?” “How can I prove that they’re not doing that?” There’s a lot of possible answers to #1, many of which involve cocaine and the office copier, but let’s focus on one I hear a lot: an inability to restore…

Who’s Backing Up That Database?

June 2, 2015

Kendra Little

Backup and Recovery

A question came up on Twitter recently– how you can you figure out where a rogue backup is coming from? I’ve run into this question with clients before. In an enterprise environment where backup tools come and go, it’s not unusual to find backups being run by some mysterious account. By the way, when’s the…

Find Corrupted Indexed Views with DBCC CHECKDB

April 4, 2015

Kendra Little

If you’ve got indexed views, or are thinking of using them, this is important. First, go read the blog post, “An Indexed View Bug with Scalar Aggregates” by Paul White. It will take a minute, but it’s important to understand. Paul shows a scenario where the data in a certain kind of indexed view can…

9 Ways to Lose Your Data

February 19, 2015

backup, corruption, recovery

Every time someone tells me, “This database is mission critical – we can’t have data loss or downtime,” I just smile and shake my head. Technology is seriously difficult. To illustrate, here’s a collection of client stories from the last few years: The DBCC CHECKDB job ran every week just like it was supposed to…

How Many Databases Can I Put on One SQL Server?

February 4, 2015

Architecture, Backup and Recovery, ISV and SaaS Database Design, SQL Server

backup, dr, ha, recovery, rpo, rto

I’ve seen servers with thousands of databases on a single SQL Server, and it works. Sure, opening the databases list in SQL Server Management Studio is painful, and a lot of third party monitoring tools fall over, but it’s not so bad once you know how to work around these issues. But there’s two issues…

How to Restore a Page in SQL Server Standard and Enterprise Edition

November 11, 2014

Jes Schultz Borland

One of the many restore features in SQL Server is the ability to restore one or more pages of data. This can be very convenient in some narrow situations – for example, corruption occurs on one page or an oops update is made to one record. The page restore process is not straightforward, however, and,…

How to Back Up Terabytes of Databases

July 17, 2014

Backup and Recovery, CHECKDB and Corruption

When you weren’t looking, your databases went and grew up. Now your backup window has grown so large that you’re about ready to open it and jump. Time to make a choice. The Native Way: Tuning SQL Server Backups You can theoretically pull this off by using a combination of tactics: Back up as infrequently as the…

DBCC CheckDB FAQ: Check for Corruption in SQL Server

May 27, 2014

Kendra Little

DBCC CheckDB: Frequently Asked Questions It’s eating our logo. Will it stop at nothing??? Q: Do I really need to run DBCC CheckDB? Yes. If you’re even partially in charge of a SQL Server and you’d like to keep your job, you should make sure CheckDB is being run regularly. (Seriously!) Q: But WHY do…

Backups, Restores, Corruption, and You: How Strong are You?

May 21, 2014

Jes Schultz Borland

Backup and Recovery, CHECKDB and Corruption, SQL Server

You and your databases have a strong relationship. You back them up, and check them for corruption. They provide the business with information day after day. Don’t let your relationship come to this! Then, one day, your relationship is rocked. The backups are too slow. The restores are too slow. There is…gasp…corruption. Are you prepared…

Doug Broke It: Reporting Services Encryption Keys [Video]

May 19, 2014

Doug Lane

Backup and Recovery, Videos

In this 5-minute video, Doug tells about the time he deleted the Reporting Services encryption key, and what it took to fix the damage. You’ll learn about what the SSRS encryption key protects, as well when to: Back up the encryption key Restore the encryption key Change the encryption key Delete the encryption key

New High Availability Planning Worksheet

May 14, 2014