High Availability

Always On Availability Groups, Backup and Recovery, Clustering, SQL Server

One of the most popular things in our First Responder Kit is our HA/DR planning worksheet. Here’s page one: Page 1 – how our servers are doing now, versus what the business wants In the past, we had three columns on this worksheet – HA, DR, and Oops Deletes. In this new version, we changed “Oops”…

Availability Group Direct Seeding: Mind Your Backup Jobs

Last Updated February 9, 2017

Always On Availability Groups, Backup and Recovery, SQL Server

I’ll get right to the point While you’re Direct Seeding, you have to be careful with any other full or differential backup jobs running on the server. This is an artifact of the Direct Seeding process, but it’s one you should be aware of up front. In the screencap below, courtesy of sp_whoisactive there’s a…

Availability Group Direct Seeding: TDE’s Frenemy

Last Updated February 9, 2017

From the Mailbag In another post I did on Direct Seeding, reader Bryan Aubuchon asked if it plays nicely with TDE. I’ll be honest with you, TDE is one of the last things I test interoperability with. It’s annoying that it breaks Instant File Initialization, and mucks up backup compression. But I totally get the…

Availability Group Direct Seeding: Extended Events and DMVs

Last Updated February 9, 2017

As of this writing, this is all undocumented I’m super interested in this feature, so that won’t deter me too much. There have been a number of questions since Availability Groups became a thing about how to automate adding new databases. All of the solutions were kind of awkward scripts to backup, restore, join, blah…

Availability Group Direct Seeding: How to fix a database that won’t sync

Last Updated February 9, 2017

Always On Availability Groups

This post covers two scenarios You either created a database, and the sync failed for some reason, or a database stopped syncing. Our setup focuses on one where sync breaks immediately, because whatever it’s my blog post. In order to do that, I set up a script to create a bunch of databases, hoping that…

The Worst Way to Judge SQL Server’s HA/DR Features

Last Updated February 13, 2017

Architecture, High Availability, SQL Server

We love to help people plan for disasters We’re not pessimists, we’ve just seen one too many servers go belly up in the middle of the night to think that having only one is a good idea. When people ask us to help them, we have to take a lot into consideration. The first words…

SQL Server 2016: Availability Groups, Direct Seeding, and You.

Last Updated February 9, 2017

One of my least favorite things about Availability Groups Well, really, this goes for Mirroring and Log Shipping, too. Don’t think you’re special just because you don’t have a half dozen patches and bug fixes per CU. Hah. Showed you! Where was I? Oh yeah. I really didn’t like the backup and restore part. You…

A funny thing happened on my way to set up Mirroring…

Last Updated February 9, 2017

Database Mirroring, SQL Server

I’ve set up Mirroring about a billion times I’m not bragging about that. I’d rather say that I set up a billion AGs, and not one of them ever failed. But then I’d be lying to you; those things fail like government programs. One thing I’d never done, though, is set up Mirroring with a…

Training Plan for a SysAdmin to Manage Availability Groups

Last Updated April 9, 2017

Kendra Little

Always On Availability Groups

How hard is it for a systems administrator who’s used to running SQL Server on Windows Clusters to tackle Availability Groups? Our example system administrator knows a bit of TSQL and their way around Management Studio, but is pretty new to performance tuning. Well, it might be harder than you think. First, let’s look at…

AlwaysOn AG Databases Need Bigger Log Files

Last Updated February 9, 2017

Doug demonstrates what he’s going to do to the failed replica. One of your SQL Servers is going to fail. When one of your AG members goes down, what happens next is just like opening a new SSMS window and typing BEGIN TRAN. From this moment forwards, the transaction log starts growing. And growing. And growing.…

Synchronous Always On Availability Groups Is Not Zero Data Loss

Last Updated February 9, 2017

Always On Availability Groups, Backup and Recovery

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.…

Why Nobody Ever Patches Their SQL Servers

Last Updated April 17, 2018

Always On Availability Groups, Processes and Practices, SQL Server

I saw a DBA.StackExchange.com question and had to laugh out loud: “Is there a cool way of performing CU updates for SQL Server on hundreds of machines?” Patching got me like No, and it has nothing to do with technology. Which servers are mission-critical 24/7 and can’t go down? Which servers can only be taken down…

Should You Put Things in the master Database?

Last Updated May 16, 2019

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…

Why Zero Data Loss in SQL Server is Harder Than You Think

Last Updated April 9, 2017

Kendra Little

Architecture, High Availability

But Kendra, it can’t be that hard… after all, we have synchronous modes in Database Mirroring and Availability Groups, right? Synchronous Commit doesn’t mean “zero data loss” When we think about limiting data loss, the first thing we think of is a technology that lets us reduce points of failure. If every transaction must be…

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

Last Updated March 6, 2019

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

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 to Set Up Standard Edition Always On Availability Groups in SQL Server 2016

Last Updated March 5, 2017

191

SQL Server 2016 adds Availability Groups in Standard Edition. While the licensing details can obviously change right up to release date, here’s what was announced at the Ignite conference: Limited to two nodes only (a primary and a secondary) Like mirroring, you can’t read from the secondary, nor take backups of it But like database…

“Breaking” News: Don’t Install SQL Server 2014 SP1

Last Updated September 21, 2017

Always On Availability Groups, Breaking News, SQL Server

Yesterday, Microsoft announced availability of Service Pack 1, saying: As part of our continued commitment to software excellence for our customers, this upgrade is available to all customers with existing SQL Server 2014 deployments via the download links below. Yeah, about that commitment to software excellence. This morning, the download is gone: Notice: The SQL SSIS…

Always On Availability Groups FAQ

Last Updated August 23, 2021