Press "Enter" to skip to content

Category: Availability Groups

Query Store And Availability Groups FAQ

Published 2017-11-08 by Kevin Feasel

Erin Stellato has a few follow-up questions from her Query Store sessions:

Q: Can you enable Query Store for a read-only replica?

A: No.  Because the replica is read-only, and Query Store inherently writes data TO the database, you cannot enable it to capture queries that are executed against that read-only copy.  I did create a Connect item for this request.  If this is of interest to you, please up-vote it: Enable Query Store for collection on a read-only replica in an Availability Group.  The more votes this has, the better the possibility that Microsoft will implement it, so feel free to share with your friends and have them vote too!

Read on for more questions and answers, and if you’re interested in it, vote on the Connect item above.

Comments closed

Modifying Availability Group Endpoint URLs

Published 2017-10-18 by Kevin Feasel

Ronald Dameron shows how to change the endpoint URLs for Availability Group replicas:

I recently had to modify the Endpoint URLs on our SQL Server Availability Group replicas.

The reason for this blog post is that I could not answer the following questions:

Do I need to suspend data movement prior to making this change? 

Would this change require a restart of the database instance?

Now Ronald can, and after reading his post, you’ll be able to as well.

Comments closed

Synonyms And Availability Groups

Published 2017-09-15 by Kevin Feasel

Allen McGuire shows how to maintain synonyms between instances in an Availability Group:

I’m a big fan of using synonyms to point to objects in other databases, but if you’ve tried to use them in conjunction with Availability Groups you may have encountered a slight issue: upon failover, the synonyms are no longer valid because the instance name is part of the synonym definition.  In order for the synonyms to work again, they have to point to the instance they now reside in – assuming the databases were in the same instance, respectively.

The next challenge was to automate the of detection of when the primary has changed, therefore triggering an update of the synonyms.  I put the following tSQL into a SQL Agent job and set it to run every five seconds.

Click through for the SQL Agent job details.  I have only created a couple synonyms and don’t really like them that much, but if they’re your bag, then at least you know you can use them safely with Availability Groups.

Comments closed

Synchronizing Availability Group Objects

Published 2017-09-13 by Kevin Feasel

Derik Hammer has a process to maintain logins, backup devices, linked servers, SQL Agent details, and more between Availability Group nodes:

A notable limitation of this process is that it does not update existing objects. Jobs which already exist but were updated, will not be altered. I chose to omit that functionality because it presents merge complications and problems. For example, the cleanest way to handle the process would be to drop and create the object each time the synchronization runs. If that happened, however, there would be gaps when logins didn’t exist and applications would fail to connect, SQL Agent jobs would lose history, and/or the processing of a job would fail because it was dropped part way through executing.

With that limitation aside, this is a very interesting process and I recommend giving it a careful read.  Derik also includes the Powershell script at the end.

Comments closed

Checking Availability Group Status With Powershell

Published 2017-09-13 by Kevin Feasel

Tracy Boggiano shows off a script which checks Availability Group status of selected servers:

My favorite thing to automate using PowerShell is checking on the status of things on multiple servers.  For example, after patching your environment running a quick query to make sure the version number is the same.  In this example, we will use a cmdlet my coworker wrote in combination in my cmdlet to check the health of all the Availability Groups across our landscape or you could use it just check one.  After all I do consider myself to be an HA/DR nut.

I’ve blogged about my coworker’s Get-CmsHost cmdlet before but now he has and shared it on github so you can read more about here.

In my cmdlet I use the same code that used in the SSMS AG dashboard to check the status of my Availability Groups.

Tracy includes her cmdlet as well as several example calls.

Comments closed

Performance Problems Due To Readable Secondaries

Published 2017-08-17 by Kevin Feasel

Paul Randal describes a problem when you create a readable secondary on an Availability Group:

Yesterday I blogged about log shipping performance issues and mentioned a performance problem that can be caused by using availability group readable secondaries, and then realized I hadn’t blogged about the problem, only described it in our Insider newsletter. So here’s a post about it!

Availability groups (AGs) are pretty cool, and one of the most useful features of them is the ability to read directly from one of the secondary replicas. Before, with database mirroring, the only way to access the mirror database was through the creation of a database snapshot, which only gave a single, static view of the data. Readable secondaries are constantly updated from the primary so are far more versatile as a reporting or non-production querying platform.

But I bet you didn’t know that using this feature can cause performance problems on your primary replica?

Definitely read the whole thing.

Comments closed

TDE + AG = Higher CPU Utilization

Published 2017-07-14 by Kevin Feasel

Ginger Keys has an analysis stress testing CPU load when Transparent Data Encryption is on and a database is in an Availability Group:

Microsoft says that turning on TDE (Transparent Data Encryption) for a database will result in a 2-4% performance penalty, which is actually not too bad given the benefits of having your data more secure. There is even more of a performance hit when enabling cell level or column level encryption. When encrypting any of your databases, keep in mind that the tempdb database will also be encrypted. This could have a performance impact on your other non-encrypted databases on the same instance.

In a previous post I demonstrated how to add an encrypted database to an AlwaysOn group in SQL2016. In this article I will demonstrate the performance effects of having an encrypted database in your AlwaysOn Group compared to the same database not-encrypted.

The results aren’t surprising, though the magnitude of the results might be.

Comments closed

Availability Group Extended Events

Published 2017-07-12 by Kevin Feasel

Tracy Boggiano shows how to enhance the built-in Extended Events session for Availability Groups:

Now that the extended events session is setup we can use some queries to query information about our AGs and error messages happening on our servers.  First to query when the server failover and becomes your primary server query the event availability_replica_state_change.  The first 10 lines just read in the files for the extended event session so you don’t have to identify the exact filenames.  Then we parse the xml event for get the timestamp, previous state, current state, repliace name, and group name for the event FROM the filenames we collected before.  In the WHERE clause were are looking for when the state has changed to PRIMARY_NORMAL indicating a failover to the server.

Read on for scripts showing what she extends and also how to query this data.

Comments closed

Using Availability Groups For Upgrades

Published 2017-07-03 by Kevin Feasel

Adrian Buckman has a fun post on upgrading to SQL Server 2017 (CTP) using Availability Groups to minimize downtime:

Don’t panic, this is still going as planned as this is totally expected and this is the reason why:

We are now in a situation where we have the Primary server running 2017 but one (for us) or possibly more than one for you running on 2016 , its not possible for the 2016 server to synchronize as its databases have not been upgraded yet, they will therefore be stuck in recovery but we are about to fix that very soon.

This is a viable upgrade option:  we did it when upgrading from 2014 to 2016.  There are a lot of steps, but in the end, it worked fine.

Comments closed