Availability Groups – Page 3

Identifying Rows in sp_wait_for_database_copy)_sync

Published 2022-12-14 by Kevin Feasel

Jose 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 closed

“The Function Requested Is Not Supported” Errors on Availability Groups

Published 2022-11-18 by Kevin Feasel

David 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 closed

Contained SQL Agent Jobs in SQL Server 2022

Published 2022-09-15 by Kevin Feasel

Allan 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 closed

The Ins and Outs of Contained Availability Groups

Published 2022-09-01 by Kevin Feasel

Eitan 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:
https://docs.microsoft.com/sql/database-engine/availability-groups/windows/contained-availability-groups-overview?view=sql-server-ver16

But there are several things which are not included with contained Availability Groups. click through for that list.

Comments closed

Cross-Platform SQL Server Availability Groups

Published 2022-08-11 by Kevin Feasel

Rajendra Gupta shows how to set up an Availability Group in SQL Server which runs on both Windows and Linux:

Microsoft 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 Comments

Thoughts on Contained Availability Groups

Published 2022-05-30 by Kevin Feasel

Allan 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 closed

Seeding AG Replicas from Snapshots in SQL Server 2022

Published 2022-05-27 by Kevin Feasel

Anthony 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 closed

Contained Availability Groups in SQL Server 2022

Published 2022-05-25 by Kevin Feasel

Sean Gallardy is intrigued:

SQL 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 closed

Backups from Secondary Replicas in SQL Server

Published 2022-02-15 by Kevin Feasel

Brendan 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

Change Data Capture and Availability Groups

Published 2022-02-04 by Kevin Feasel

Jeff Iannucci raises job awareness:

If you’ve ever had to implement Change Data Capture (CDC) for a database in an Availability Group, then you know that the CDC jobs don’t really consider the Availability Group. The capture and cleanup jobs created are set up as if the database exists only on a single instance.
And that’s a problem, because I would guess quite lot of databases are in Availability Groups. Maybe even some of yours. If you have this issue, I’ve put together a step-by-step solution in this post.

Click through for the Microsoft way and the Jeff way.

Comments closed

Category: Availability Groups