High Availability

Home
High Availability

Failing over an Asynchronous Mirror in SQL Server (video)

February 2, 2015

Will a long running open transaction block you from changing to synchronous mode and failing over to your SQL Server database mirror? Join Kendra to test performing a planned failover from an asynchronous database mirror. Brent Says: Often, when I’m working with developers, they’re totally surprised that SQL Server’s failover methods all break in-flight transactions. No matter…

Reporting From a Log Shipping Secondary in STANDBY mode

January 28, 2015

Kendra Little

Log Shipping, SQL Server

You can build a lot with simple tools. Carefully. I’m a fan of SQL Server’s transaction log shipping. It works in Standard Edition, it’s relatively simple to set up, and you can even make your log shipping secondary readable using STANDBY mode. I’ve worked with some pretty cool, complex log shipping environments over the years.…

Urgent AlwaysOn Availability Groups Bug

January 22, 2015

Brent Ozar

Always On Availability Groups, Breaking News, SQL Server

always on, availability groups, breaking news, bug

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?

November 25, 2014

Brent Ozar

Always On Availability Groups, SQL Server

always on, availability groups, connect

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…

Log Shipping Magic: Using A Differential Backup to Refresh a Delayed Secondary

September 4, 2014

Kendra Little

Log Shipping, SQL Server

Log: It’s better than bad, it’s good! Let’s say you’re a DBA managing a 2TB database. You use SQL Server transaction log shipping to keep a standby copy of the database nice and warm in case of emergency. Lots of data can change in your log shipping primary database: sometimes it’s index maintenance, sometimes it’s…

What Amazon RDS for SQL Server Users Need to Know about Multi-AZ Mirroring

May 28, 2014

Brent Ozar

Amazon Web Services (AWS), Database Mirroring

Amazon Relational Database Service (RDS) now supports multi-availability-zone SQL Servers. This means you can have a primary database in one data center, and a secondary replica in another data center. Those Portland folks were always a little bit backwards anyway. When your primary server goes down, OR when the entire AZ goes down, you can fail…

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

March 5, 2014

Kendra Little

Always On Availability Groups

always on

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

January 16, 2014

Brent Ozar

Always On Availability Groups, SQL Server

always on, availability groups, indexing

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

December 18, 2013

Brent Ozar

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

always on, availability groups, scalability, 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

December 11, 2013

Brent Ozar

Always On Availability Groups, SQL Server

always on, availability groups, backup, checkdb, corruption

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

November 18, 2013

Brent Ozar

Always On Availability Groups, Stack Overflow

always on, availability groups, stackoverflow

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

High Availability Doesn’t Fix Poor Performance

September 26, 2013

Jes Schultz Borland

Development, High Availability

Imagine this: you have a database, and it’s on a SQL Server instance, which is on a server. That server may be physical or it may be virtualized. The database, or the instance, or the server, has a high availability solution implemented. It might be failover clustering; it might be synchronous mirroring; it might be…

Common SQL Server Clustering, AlwaysOn, and High Availability Answers

August 31, 2013

Brent Ozar

Always On Availability Groups, Clustering, SQL Server

always on, availability groups, clustering

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…

Introduction to SQL Server Failover Clusters (video)

August 22, 2013

Kendra Little

Clustering, SQL Server

clustering

You’d love to know how to make your SQL Servers more highly available, but you’re just not sure what “clustering” is all about. We can help! In this 30 minute video Kendra Little shows you how failover clusters make your SQL Servers easier to manage. She uses diagrams and drawings to teach you the strengths…

AlwaysOn Availability Groups: The Average of its Parts

August 1, 2013

Jeremiah Peschka

Always On Availability Groups

always on, availability groups

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…

Answers to Common High Availability and Disaster Recovery Questions

April 22, 2013

Brent Ozar

Backup and Recovery, Clustering

clustering, dr, ha

In our recent webcast on HA & DR basics for DBAs, we got a bunch of questions that couldn’t be answered fast enough. Here you go: Q: Do any of these techniques work for companies with just 1-2 IT staff? To keep things simple, I’d check out virtualization replication like VMware SRM. SRM isn’t…

Log Shipping FAQ

March 21, 2013

Jes Schultz Borland

Log Shipping, SQL Server

364

I’ve been working with and talking about SQL Server Log Shipping a lot lately! One way to ship logs… I’ve gotten a lot of great questions about log shipping, so I’ve put together an FAQ. What editions of SQL Server is log shipping available in? 2012 – Enterprise, Business Intelligence, Standard, and Web 2008R2 –…

VMware HA is Not Database Server High Availability

January 22, 2013

Brent Ozar

Clustering, SQL Server, Virtualization

clustering, ha, vmware

I served with High Availability. I knew High Availability. High Availability was a friend of mine. VMware HA, you’re no High Availability. See, for us database administrators, high availability means protection when: The system drive fills up because some potato decided to download a bunch of files An operating system or database server update goes…

Why Your SQL Server Cluster Shouldn’t Be Virtualized

September 27, 2012

Brent Ozar

Clustering, Virtualization

clustering, virtualization, vmware

When people buy my virtualization training video, one of the followup questions I get most often via email is, “Can I build SQL Server clusters in VMware and Hyper-V?” In theory, yes. Microsoft’s knowledge base article on SQL Server virtualization support says they’ll support you as long as you’re using configurations listed in the Server…

Microsoft SQL Server 2012 Always On AGs at StackOverflow

September 24, 2012

Brent Ozar

Always On Availability Groups, SQL Server, Stack Overflow

always on, availability groups, stackoverflow

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…