Always On Availability Groups

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…

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. https://www.youtube.com/watch?v=G1URY5M09ic And here’s the questions we couldn’t…

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