Always On Availability Groups

Always On Availability Groups

No Comments

Availability Groups are all the rage right now, especially since they’re included with SQL Server 2016 Standard Edition. Our Availability Groups blog post category is one of the most popular on the site, and in my 4-day Senior DBA class, people have always been asking for more in-depth coverage of clustering and AGs. Let’s get together for a…

Direct Seeding: I’ve Got A Wizard And You Don’t

May 31, 2017

Always On Availability Groups, High Availability

always on, availability groups, dba, directseeding, sql server

A commenter commented That the “New AG Wizard” in SSMS 2017 had surfaced the Direct Seeding mode for AGs. I was pretty psyched about this because I think it’s a great feature addition to AGs that can solve for a pretty big hump that people run into when they create databases regularly. It used to…

New White Paper: How to Build an Always On Availability Group in Google Compute Engine

March 8, 2017

Always On Availability Groups, Google Compute Engine, SQL Server

always on, alwayson, availability groups, gce, google compute engine

You’re a database administrator, Windows admin, or developer. You want to build a Microsoft SQL Server environment that’s highly available, and you’ve chosen to use Always On Availability Groups. Our newest white paper – download In this white paper we built with Google, we’ll show you: How to build your first Availability Group in Google…

Always On Availability Groups Now Supported in Google Compute Engine

February 1, 2017

Always On Availability Groups, Cloud Computing, Google Compute Engine

I’m excited to finally be able to talk about something Erik, Tara, and I have been working on for the last few months. Here in the SQL Server community, when I mention cloud, you probably think of two companies: Microsoft and Amazon. We’ve been blogging about SQL in AWS for years, and Microsoft throws a…

When Always On Isn’t: Handling Outages in Your Application

January 24, 2017

Always On Availability Groups, Stack Overflow

always on, availability groups, often on, stack overflow

Today’s brief Stack Overflow outage reminded me of something I’ve always wanted to blog about: Stack Overflow is in limp-home mode There’s a gray bar across the top that says, “This site is currently in read-only mode; we’ll return with full functionality soon.” That’s not a hidden feature of Always On Availability Groups. Rather, it’s a…

Availability Groups: More Planned Downtime for Less Unplanned Downtime

December 7, 2016

Always On Availability Groups, Breaking News, SQL Server

alwayson, patching

I often hear companies say, “We can never ever go down, so we’d like to implement Always On Availability Groups.” Let’s say on January 1, 2016, you rolled out a new Availability Group on SQL Server 2014. It’s the most current version available at the time, and you deploy Service Pack 1, Cumulative Update 4 (released…

Asynchronous Database Mirroring vs. Asynchronous Availability Groups

September 20, 2016

Tara Kizer

Always On Availability Groups, Database Mirroring, SQL Server

When Database Mirroring came out in SQL Server 2005 Service Pack 1, we quickly dropped Log Shipping as our Disaster Recovery solution. Log Shipping is a good feature, but I can failover with Asynchronous Database Mirroring faster than I can with Log Shipping. When Always On Availability Groups (AG) came out in SQL Server 2012,…

Updated High Availability and Disaster Recovery Planning Worksheet

July 22, 2016

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

July 7, 2016

Always On Availability Groups, Backup and Recovery, SQL Server

alwayson, availability groups, dba, directseeding

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

July 1, 2016

alwayson, availability groups, dba, directseeding, sql server

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

June 30, 2016

alwayson, availability groups, dba, directseeding

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

June 29, 2016

Always On Availability Groups

alwayson, availability groups, dba, directseeding

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…

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

June 14, 2016

Always On Availability Groups

alwayson, availability groups, dba

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…

Training Plan for a SysAdmin to Manage Availability Groups

October 13, 2015

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

October 8, 2015

always on, availability groups

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

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

Why Nobody Ever Patches Their SQL Servers

August 6, 2015

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?

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