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.
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.
In this white paper we built with Google, we’ll show you:
Today's brief Stack Overflow outage reminded me of something I've always wanted to blog about:
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."
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…
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.
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. The primary keeps right on trucking, accepting deletes/updates/inserts, without telling end users that all their eggs are in a single basket.
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. More on that in a couple of steps.
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 mirroring, you can take snapshots of the secondary for a static reporting copy
Each database is in its own Availability Group
If you're using AGs, don't apply these patches:
SQL 2012 SP2 CU3
SQL 2012 SP2 CU4
SQL 2014 CU5
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 continues to invest in them over time.)
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:
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.
Time for a thought exercise.
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 wants data from within the last couple of minutes.
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…
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,…
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
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 thought that comes to mind is the new hotness: SQL Server AlwaysOn Availability Groups.
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…
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, cool, and collected - even if you have to trust someone else to do this work.
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…
