Press "Enter" to skip to content

Category: Availability Groups

Waiting For Rollback

Published 2016-09-01 by Kevin Feasel

Andrea Allred ran into an issue with a long-running job on an Availablity Group:

I panicked. In this situation I would normally pull the database out of the AG and then re-add it.  I didn’t have that option because it is a HUGE database and didn’t have that much time and space to move it around. I knew a large transaction had kicked off (thank you alert email that I created to warn me about such things) but thought that surely the rollback would have cleared quickly.  That lead me to looking for rolling back transactions.

Fortunately, the issue was on a secondary not under heavy use, so she was able to recover in time.

Comments closed

Groups Of Basic Availability Groups

Published 2016-07-26 by Kevin Feasel

Russ Thomas looks into whether you can combine Basic Availability Groups in a way which mimics Enterprise Edition’s Availability Groups functionality:

For a recent project that required HA/DR but couldn’t justify Enterprise edition we decided to take the plunge on 2016’s Basic Availability Groups.

For a quick rundown of the watered down feature set – basically what you don’t get with a Basic Availability Group (BAG) – the major points are as follows:

  • You can only have 2 nodes.

  • Only one database can be in the group.

  • You can not have the secondary be in read only.

  • You can not take backups from the secondary.

The answer is “yes” but it’s not easy.  Read on for more.

Comments closed

Changing AG Endpoint Ports

Published 2016-07-13 by Kevin Feasel

Matt Gordon walks through changing endpoint ports:

Since a communication failure within an AG is usually a “hair on fire” kind of event, a quick fix may be desired. The quickest fix I’ve found is to change the port on which the AG endpoint is listening. While the Microsoft help pages on this contain enough information to put together the right script eventually, the first time this happened to me I really would have liked a blog post specific to this issue that gave me the right script to use.

In keeping with this T-SQL Tuesday’s theme, below is my SQL Server present. Please note that I used 5023 as an example but your choice can be flexible depending on what else is consuming ports on your machine. Hopefully this helps somebody (or me if I manage to travel back in time and encounter this same issue)

In an ideal world, you’d want a bit more control over what’s running on various ports to prevent this scenario, but that’s probably wishful thinking.

Comments closed

Buffer Pools And Availability Groups

Published 2016-07-01 by Kevin Feasel

Joey D’antoni answers the question, what happens to pages in the buffer pool when you have an AG failover?

Recently at SQL Saturday Philadelphia, we started discussing failover  as it relates to mirroring and Always On Availability Groups. Specifically, we were wondering what would happen if you had a relatively busy readable secondary replica (which would have a lot of pages in the buffer pool on the secondary instance) and if those pages would be flushed from cache or anything like that. So I reached out to the product group and Kevin Farlee from Microsoft was extremely helpful

The answer is not what I was expecting.

Comments closed

Auto-Seeding Availability Groups

Published 2016-06-29 by Kevin Feasel

John Sterrett is the latest smart person to take a look at automatic seeding of Availability Groups:

The 600 GB databases took about 66 minutes to seed across the network from a primary replica to the secondary replica.  I noticed 1.4 Gbps of consistent throughput during the seeding process. This makes a lot of sense as it caps out around what the storage system can deliver in this environment.

The first thing I would look at for benchmarking throughput for network activity would be the bytes sent per second from the primary replica and bytes received per second on the secondary replicas.

John also includes an extended event session statement to track seeding.  Great read.

1 Comment

More On Direct Seeding AGs

Published 2016-06-17 by Kevin Feasel

Erik Darling discusses direct seeding of Availability Groups:

This isn’t in the GUI (yet?), so don’t open it up and expect magic mushrooms and smiley-face pills to pour out at you on a rainbow. If you want to use Direct Seeding, you’ll have to script things. But it’s pretty easy! If I can do it, anyone can.

I’m not going to go through setting up a Domain Controller or Clustering or installing SQL here. I assume you’re already lonely enough to know how to do all that.

The script itself is simple, though. I’m going to create my Availability Group for my three lovingly named test databases, and add a listener. The important part to notice is SEEDING_MODE = AUTOMATIC. This will create an Availability Group called SQLAG01, with one synchronous, and one asynchronous Replica.

Mike Fal wrote about this as well.

Comments closed

Direct Seeding Availability Group Replicas

Published 2016-06-16 by Kevin Feasel

Mike Fal is excited about direct seeding of Availability Groups in SQL Server 2016:

In SQL Server 2012 and 2014, creating an Availability Group could take a significant amount of work. One of the more tedious tasks is setting up your replica databases. This is because that you need to restore your database to your replica node in a state close enough to the primary to allow synchronization to happen. It can take several log backup restores to hit that magic window where you can join the database on the secondary node. Then, you get to do it again on the next replica!

Enter direct seeding in 2016. With this feature you no longer have to do any of the restores. You simply create your replicas with direct seeding enabled, then when you add a database to the AG, SQL Server will directly seed the database to your replica nodes. It’s surprisingly simple.

This sounds pretty interesting.

Comments closed

Dueling Log Backup Jobs

Published 2016-06-14 by Kevin Feasel

Robert Davis ran into HADR_WORK_QUEUE waits recently:

Our 3rd party monitoring solution collects blocking information, but not for system threads. There was no additional information available for this blocking incident, but I could see that the system thread was a background process with the command “UNKNOWN TOKEN” and was sitting in a wait type of “HADR_WORK_QUEUE”. It was clearly the worker thread for the AG of a specific database.

A little later, we had blocking again involving that same thread, but this time, the AG worker thread was blocking the log backup thread. Seemed logical that if the worker thread could block the log backup, then the log backup could have also blocked the worker thread, but still it did not make sense to me.

This is one of those cases in which the answer makes perfect sense after the fact, but can be maddening until then.

Comments closed

Distributed Transactions With Always-On Availability Groups

Published 2016-06-07 by Kevin Feasel

Dave Bermingham looks at distributed transactions within Always-On Availability Groups in SQL Server 2016:

In SQL Server 2016, Distributed Transactions are only supported if the transaction is distributed across multiple instances of SQL Server. It is NOT supported if the transaction is distributed between different databases within the same instance of SQL Server. So in the picture above, if the databases are on separate SQL instances it will work, but not if the databases reside on the same instance which is more likely.

This seems like a half-finished job.  We’ll see if Microsoft improves on this later.

Comments closed

Introducing Timing Delays

Published 2016-05-02 by Kevin Feasel

Andrea Allred introduces a timing delay to solve a log growth problem:

We have a lovely Availability Group that holds A LOT of data that is broken into partitions.  We have 42 partitions and they are usually moving information around daily between them.   The index rebuilds on them were making our logs HUGE because the the Availability Group was taking too long to catch up, we tried both Synchronous and Asynchronous mode.  We would see all kinds of errors.  We were doing horrible things like auto shrinking our transaction log after the indexing finished and  ignoring alarms during the time the database was rebuilding.  We had requested more and more space from our storage team and sometimes the job wouldn’t even finish because it ran out of space.  Our first idea was to split out the index rebuilds so that we could do one partition at a time.

I’d call this a bit of a hack, but if it’s documented and it does the job better than any known alternative, it’s a good decision.

Comments closed