A Fine Slice Of SQL Server
Allan Hirt explains that high availability isn’t disaster recovery:
I need to address a common misconception in this blog post. To paraphrase, this is what I have heard from many for the past 20+ years:
If I have a Windows Server Failover Cluster/Availability Group/Failover Cluster Instance, I do not need backups.
This is very much not the case, as Allan explains.
Comments closedJose Manuel Jurado Diaz troubleshoots a problem:
As you could see in this public documentation Auto-failover groups overview & best practices – Azure SQL Database | Microsoft Learn about sp_wait_for_database_copy_sync “sp_wait_for_database_copy_sync prevents data loss after geo-failover for specific transactions, but does not guarantee full synchronization for read access. The delay caused by a sp_wait_for_database_copy_sync procedure call can be significant and depends on the size of the not yet transmitted transaction log on the primary at the time of the call.”
Our customer asked about several scenarios to understand this behaviour and also, verify if there is possible to identify the rows that have not been synced. For this, I developed a POC to test it:
Read on to see what you’d need to do.
Comments closedDavid Fowler troubleshoots an issue:
Checking the logs on the secondary, it was littered with ‘Database Mirroring login attempt failed with error: ‘Connection handshake failed. An OS call failed: (80090302) 0x80090302(The function requested is not supported).’ messages. The primary server wasn’t able to authenticate with the secondary, but why? Everything looked ok as far as I could see.
Click through for the fruits of David’s labor.
Comments closedAllan Hirt looks at contained SQL Server Agent Jobs:
I previously wrote about Contained AGs in SQL Server 2022 and demonstrated how to create a contained login. In this blog post, I’m going to talk about contained SQL Server Agent jobs because just like logins, they are a bit confusing from an administative standpoint in their current pre-release implementation (this blog post was written using SQL Server 2022 RC0 using SSMS 19 Preview 3).
It sounds like there’s still a ways to go on the tooling side of things.
Comments closedEitan Blumin does some digging:
Notice that all of the highlighted databases and server objects belong to the contained availability group, and all other databases and objects are not visible anymore. This is because our “master” and “msdb” databases are now the contained system databases which are separate from the actual instance system databases.
For more details about contained availability groups, such as interoperability support with other SQL Server features and more, check out the official Microsoft documentation at:
But there are several things which are not included with contained Availability Groups. click through for that list.
Comments closedMicrosoft supports SQL Server on Linux, and it has many of the same features as the Windows version. You can restore databases from Windows to Linux SQL or vice versa. The Linux SQL works with Red Hat, Ubuntu, SUSE enterprise, Kubernetes containers, and Docker.
Windows-based SQL instance supports SQL Server Always On Availability Groups for high availability and disaster recovery. If you are not familiar with Windows AG configuration, refer to the extensive series on Always on Availability Group (Toc at the bottom).
If you have both Windows and Linux SQL Server, is it possible to configure an availability group between them? Let’s explore this in this article.
This example uses async mode, which is the easier one to set up. With synchronous, you’re probably looking at using Pacemaker to sort out AG status.
2 CommentsAllan Hirt is quite happy with contained Availability Groups:
All SQL Server availability features except Always On Failover Cluster Instances (FCIs) have a “problem”: when a secondary replica/warm standby/mirror (the term is different for each feature …) takes over as the new boss, some items such as SQL Server Agent jobs, instance level logins, etc., are not there. Going back to the early days of SQL Server when log shipping was not even in the product, this was always a manual process. There are multiple ways to approach this challenge and I am not going to detail them. This “problem” is a longstanding pain point with those who are responsible for managing SQL Server.
Contained AGs solve this issue by having their own master and msdb databases synchronized as part of the AG mechanism.
Click through to see how.
Comments closedAnthony Nocentino is excited about using storage snapshots in SQL Server 2022:
But what if I told you that you could seed your Availability Group from a storage-based snapshot and that the re-seeding process can be nearly instantaneous?
In addition to saving you time, this process saves your database systems from the CPU, network, and disk consumption that comes with direct seeding and using backups and restores to seed.
This process described in this post is imlemented on Pure Storage’s FlashArray and works cloud scenarios on Pure’s Cloud Block Store.
Click through to see how.
Comments closedSQL server 2022 (SQL Dallas) has added what is called “Contained Availability Groups”, coming as a nice surprise to many DBAs or other admins out there (incidental DBAs, small shops, etc.) which *could* severely remove a large chunk of administrative overhead and challenges. There’s quite a lot to like about them, but like all things in life there are also some drawbacks that one needs to be aware exist, while these are a giant and amazing step forward for AGs, much like any other tool it has its place and time.
Definitely read the pros and cons of this before giving it a try in the 2022 preview.
Comments closedBrendan McCaffrey offers up some guidance:
With Availability Groups there has always been a common theme across the industry to offload your database backups to a secondary replica. It sounds great in theory. I know people that have done this. I also know people that “thought” they were doing this. But the question is, should you be doing this?
Your scientists spent so long figuring out if they could that they forgot to ask if they should.
Comments closed