[Video] SQL Server Always On Availability Groups 101
I got a few closely related Availability Groups questions at https://pollgab.com/room/brento and decided to do a half-hour introduction to AGs:
Here’s what we covered:
- 00:00 Start
- 00:24 Paul Hunter: My company decided to move to AZURE. The person in charge of moving the SQL Server decided to put up an AG (I voiced my concerns over management of an AG). Now I am going to be in charge of managing an AG. Do you have any advice on how to quickly learn to manage an AG?
- 30:14 Bandhu: What’s the max number of DB’s you would not exceed for a single AG?
- 33:59 Dopinder: What is the top issue you see with clients running SQL Server AG?
- 35:29 Bandhu: Is there a good way to determine which databases a given app connects to (for the purpose of designing AG’s)?
5 Comments. Leave new
“Witnesses reported unidentified person running naked whole night through Vegas horrendously laughing, weeping and mumbling about ‘eich id are crazy’ and ‘hate availability troupe’. LVMPD found no suspects whatsoever.”
Been running AGs since 2016… Have dealt with the various issues Brent mentioned in this office hours. In particular, Brent forgot to mention Azure quorum services which we went to several years ago. Since both of our datacenters (1.5 miles apart with dedicated redundant fiber between them) have independent connections to the internet (different carriers) and our critical app servers are also done in each datacenter making a “preferred” datacenter an irrelevant question as the “business” is entirely a one-campus thing by definition. And our AGs are synchronously mirrored as we have sub 3ms latency between our servers.
With regards to the worker thread issue, we dealt with it a few years ago (and have increased the number of databases 50%). Strangely enough, if you have a good storage system (we are also a Pure shop) with solid networking, your performance will get your through things. You can increase the number of worker threads if you so wish, but understand that you also have to play memory games within SQL Server to accommodate those worker threads… A book could be written on the subject. BTW, running 140 databases (mostly lightly used vendor applications) on 8 cores in the largest AG.
I’ve also solved the automation of patching failovers through Powershell scripts that test AG and cluster health and reboot required settings. Additionally, I’ve also solved mirroring jobs between SQL Servers…
I’ve also mastered the art of upgrading AGs and it is far less painful (for impact to the organization that seems to never want to give you an hour for anything) than single server upgrades. You also get the server upgrade as you roll on new servers…
One last thing… You can (and we did once upon a time) have MS consultants coach you through building things once you THINK you know what you are doing… Learned a couple things including the tip that got me mirroring the jobs…
If Brent is monitoring and is interested in corresponding, he can contact me through the required info…
Regarding the question of backup, etc for Express Edition: we are using Ola Hallengrens Solution, starting the respective commands using Scheduled Tasks
Bingo! We do the same thing for all maintenance on express editions. However, in the batch file for each process (e.g. full backup, log backup,…) , I have it setup to email the log when it finishes. Yes, I have to scan to the bottom of the log for completion or failure (instead of having an agent job alert on failure) but at least I get them all in my inbox.
Or you can use a Powershell script with dbatools.io’s Backup-DbaDatabase.
E.g. something like this to backup all databases with a retention of 7 days:
Backup-DbaDatabase -SqlInstance server\instance -Path e:\Backup\SQL\ -CreateFolder -Type Full
Get-Childitem -recurse e:\backup\SQL -filter *.bak | Where-Object { $_.LastWriteTime -lt (get-date).adddays(-7) } | Remove-Item -Confirm:$false
Add Log and Diff backup jobs as needed, and schedule to run as a Windows user which can perform backus.