Read-Scale availability groups are ones where we don’t want the availability group for high-availability or disaster recovery, instead, we want to use it to create multiple copies of our databases that span across multiple servers allowing for the spreading of a large read-only workload. There are various scenarios where this might be extremely valuable and in previous versions of SQL Server it was possible, though there was a requirement of using Windows Server Failover Clustering (WSFC). Read-Scale availability groups do not require the WSFC component and does not give high-availability or disaster recovery, it only acts as a mechanism (availability groups) to facilitate the synchronization of the databases across multiple servers.
To reiterate, this is not used for high-availability or disaster recovery but instead to scale your databases across multiple servers for read workloads.
The remainder of the post shows how to set up an Availability Group without the corresponding Windows Server Failover Clustering components.
Starting in Windows Server 2012 R2 you now have a way to upgrade a cluster to Windows 2016. The best part is it’s not an OS upgrade, but a rebuild. The magic is that you can join a Windows 2016 server to a Windows 2012 R2 cluster. You can upgrade your cluster with as little as one failover and thus very little down time. Everything stays in compatibility mode until all nodes are upgraded to Windows 2016 and then you upgrade the cluster functional level. This is great news for those of us running FCIs or AGs.
Click through for a listing of steps and a video.
On the plane home from PAS Summit I was sat next to someone who had also attended and when he saw on my laptop that I was part of the SQL Community we struck up a conversation. He asked me how he could compare SQL Agent Jobs across availability group replicas to ensure that they were the same.
He already knew that he could use Copy-DbaAgentJob from dbatools to copy the jobs between replicas and we discussed how to set up an Agent job to accomplish this. The best way to run an Agent Job with a PowerShell script is described here
Maybe I’m out in la-la land, but wouldn’t this be a perfect use case for MSX/TSX?
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.
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.
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.
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.
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.
Whilst I was combing through various combinations of sys.dm_hadr and sys.Availability I stumbled across a couple of gems that I thought would be nice to share.
Read on for usage examples.
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.