Always On Availability Groups

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

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

Always On Availability Groups, Breaking News

Here are some of the Always On Availability Groups questions I get the most often: Q: How much network bandwidth will I need? For a really rough estimate, sum up the amount of uncompressed transaction log backups that you generate in a 24-hour period. You’ll need to push that amount of data per day across the…

The Hard Truth About Patching SQL Server Availability Groups (Hotfixes, Cumulative Updates, and Service Packs)

Last Updated September 21, 2017

Kendra Little

Whoa, be careful with that fix As a DBA, you’re responsible for identifying necessary updates to keep your SQL Servers healthy. Your business may have some mandates about the frequency of patches, but even if they don’t, you have a duty to look out for Cumulative Updates, Service Packs, and out of band hotfixes that can prevent…

Urgent AlwaysOn Availability Groups Bug

Last Updated September 21, 2017

Always On Availability Groups, Breaking News, SQL Server

If you’re using AGs, don’t apply these patches: SQL 2012 SP2 CU3 SQL 2012 SP2 CU4 SQL 2014 CU5 until you read this Microsoft post about a breaking bug. Your AG may stop synchronizing due to blocking between user queries and a system session. The fix is to disable automatic failover, restart the primary, and…

How Would You Change Always On Availability Groups?

Last Updated February 9, 2017

SQL Server 2012 introduced AlwaysOn Availability Groups, a way to achieve high availability, disaster recovery, and scale-out reads. SQL 2014 brought some improvements around higher uptime and more scale-out, and all signs point to continued improvements in the next version of SQL Server, too. (I love it when Microsoft brings out features like this and…

Need High Availability for SQL Server? Use Windows Server 2012.

Last Updated February 9, 2017

Kendra Little

SQL Server DBAs are slow to adopt Windows Server 2012 and Server 2012 R2. I frequently find teams planning new implementations of SQL Server 2012 on Windows Server 2008 R2. There seem to be two main reasons for this: DBAs want to change a minimal number of factors, and are slow to trust a new…

Careful Adding Indexes with Always On Availability Groups

Last Updated February 9, 2017

In theory, you can add indexes online with SQL Server Enterprise Edition. In theory, with AlwaysOn Availability Groups, you can add and drop indexes on the primary replica whenever you want. In theory, you can perform read-only queries on the secondaries whenever you want, and nobody gets blocked. In practice, these things don’t always add…

Auto-Scaling SQL Server Always On Availability Groups with Virtualization

Last Updated February 13, 2017

Always On Availability Groups, Amazon Web Services (AWS), Virtualization

Time for a thought exercise. Thought exercises are hard. You’ve got a database application that has bursty and unpredictable loads. Out of nowhere, you’ll suddenly get socked with a large amount of SELECT queries. Due to the way the app is written, you can’t cache the query results – the queries keep changing, and the business…

Always On Availability Groups, Backup Checksums, and Corruption

Last Updated February 9, 2017

The latest version of sp_Blitz® alerts you if you haven’t been using the WITH CHECKSUM parameter on your backups. This parameter tells SQL Server to check the checksums on each page and alert if there’s corruption. But what about corrupt backups? Books Online says: NO_CHECKSUM – Explicitly disables the generation of backup checksums (and the validation…

Update on Stack Overflow’s Recovery Strategy with SQL Server 2014

Last Updated February 9, 2017

Always On Availability Groups, Stack Overflow

Back in 2009 (wow, seems like only yesterday!), I wrote about designing a recovery strategy for Stack Overflow. Back then, I wrote: With these answers in mind, Stack Overflow’s decisions not to do transaction log backups, offsite log shipping, database mirroring, and so on make good business sense. Us geeks in the crowd may not like it,…

Common SQL Server Clustering, AlwaysOn, and High Availability Answers

Last Updated March 22, 2019

Always On Availability Groups, Clustering, SQL Server

Our live webcast topic this week was Q&A – you could bring any HA/DR questions, and we could avoid your answers. Just kidding. Here’s the webcast – we apologize for the audio, WebEx is still getting their act together. It cleans up after the first couple of minutes. And here’s the questions we couldn’t get…

AlwaysOn Availability Groups: The Average of its Parts

Last Updated February 9, 2017

Jeremiah Peschka

Business is booming; the sales team is bringing in more business and the software developers are successfully scaling out at the web server tier. There are signs of pressure on the database tier and you realize that it’s time to scale out. You’re using SQL Server 2012, you need to improve performance, and the first…

Microsoft SQL Server 2012 Always On AGs at StackOverflow

Last Updated February 9, 2017

Always On Availability Groups, SQL Server, Stack Overflow

I recently finished helping the StackExchange team migrate their SQL Server 2008 infrastructure to SQL Server 2012. These servers power StackOverflow, DBA.StackExchange.com, the new AskPatents partnership with the US Patent and Trademark Office, and hundreds of other Q&A sites that together form one of the biggest web site networks. It’s one of the most visible…

Before You Fail Over a SQL Server Always On Availability Group

Last Updated February 9, 2017

Always On Availability Groups, Amazon Web Services (AWS)

Think fast! When it’s time to do a manual failover with SQL Server 2012 AlwaysOn Availability Groups, we need to make a few decisions and perform a few tasks as quickly as possible to get the business back up and running. Here’s what you need to consider ahead of time so you can stay calm,…

AlwaysOn Availability Groups in AWS Revisited

Last Updated February 13, 2017

Jeremiah Peschka

If you’ve ever wanted to use the cloud for a DR site, there’s good news: as of this week you can use AlwaysOn Availability Groups and set up an asynchronous replica of your data into Amazon Web Services (AWS). If you’re already in the cloud, or if you were holding off, this means that you…

Why Your SQL Server’s Network Connection Matters

Last Updated February 9, 2017