Create an Availability Group in SQL Server on Linux

In this video, we will build out two availability groups for SQL Server on Linux.
LINKS AND INFO
Catallaxy Services - www.catallaxyservices.com
Get the command line operations used in this video -- 36chambers.wordpress.com/2024...
Availability groups for SQL Server on Linux -- learn.microsoft.com/sql/linux...
Create and configure an availability group for SQL Server on Linux -- learn.microsoft.com/sql/linux...
Deploy a Pacemaker cluster for SQL Server on Linux -- learn.microsoft.com/sql/linux...
Configure a listener for an Always On availability group -- learn.microsoft.com/sql/datab...
Connect to SQL Server using Application Intent Read-Only -- techcommunity.microsoft.com/t...

[@FaizanFastian23]

Hi Kevin. Here creating an availability group for a database replicates everything to secondary nodes for that particular database. Is there an option to replicate complete instance rather than a database only? for example if i create a login for application on primary side, how can that login be automatically replicated to secondary nodes?

[@KevinFeasel]

There is no way to replicate the complete instance, no. It's just databases, either individually or in a set, if you have Enterprise Edition.
As for matching logins, dbatools has a cmdlet to do that: docs.dbatools.io/Sync-DbaAvailabilityGroup.html
That's the way I'd recommend keeping those databases in sync.
SQL Server also has the notion of contained availability groups: learn.microsoft.com/sql/database-engine/availability-groups/windows/contained-availability-groups-overview
The idea with those is that you can also replicate relevant parts of master and msdb, but there are some things they don't support, so check the "Not supported" section first.

[@FaizanFastian23]

Kevin, how can i manually failover to the other node in this case where cluster type is set to none? The standby databases are read only. So if primary goes down, how can i manually failover to the other node? Also, here listener ip is set to primary ip, so if primary fails, do i need to recreate listener with secondary ip (which is going to be the new primary) ??

[@KevinFeasel]

@@FaizanFastian23 The way to fail over when there's no cluster type is available on Microsoft Learn: learn.microsoft.com/sql/database-engine/availability-groups/windows/perform-a-planned-manual-failover-of-an-availability-group-sql-server?view=sql-server-ver16#fail-over-the-primary-replica-on-a-read-scale-availability-group
Specifically, the "Manual failover without data loss" section. Assuming nodes 1 and 2 both are in synchronous commit (like in my example), you'd start on step 4.
ALTER AVAILABILITY GROUP [BusDataAG] OFFLINE
ALTER AVAILABILITY GROUP [BusDataAG] FORCE_FAILOVER_ALLOW_DATA_LOSS
Then, on the primary:
ALTER AVAILABILITY GROUP [BusDataAG] SET (ROLE = SECONDARY);
Then restart the AG:
ALTER DATABASE [BusData] SET HADR RESUME
To your note, yes, you would need to re-create the listener because there is no mechanism (Windows Server Failover Clustering or Pacemaker) that knows about the failover. This is the big downside to using NONE as the clustering type, but I wasn't able to show Pacemaker here because I didn't have RHEL or SLES to demonstrate full failover. With WSFC or Pacemaker, your listener would actually be a separate IP address associated with the clustering service, and the clustering service would handle routing that IP address.