By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
(No column name) |
---|
create function sys.fn_hadr_backup_is_preferred_replica ( @database_name sysname ) returns bit as begin declare @is_preferred bit declare @count int declare @role int declare @ag_pref int declare @ar_id uniqueidentifier declare @ag_id uniqueidentifier declare @ag_resource_id nvarchar(40) declare @database_group_id nvarchar(40) declare @primary_server_name sysname declare @local_server_name sysname declare @replica_server_name sysname declare @islocal int -- If this database is in a Database Mirroring configuration, return 1 for Primary, and 0 for Secondary/mirror, -- since Primary is the only copy where backup will work on DBM. -- select @role = mirroring_role from master.sys.database_mirroring where database_id = db_id(@database_name); if (@role is not null) begin if (@role = 2) begin select @is_preferred = 0 return 0 end else begin select @is_preferred = 1 return 1 end end -- This is not a Database Mirroring configuration. -- Get AG DB ID and local AG replica ID. -- select @database_group_id = CAST(dbs.group_database_id as nvarchar(40)), @ar_id = replica_id from sys.databases dbs where dbs.name = @database_name if (@ar_id is null) begin -- Database is not in an AG, must be a standalone DB so return 1. -- return 1 end -- Get AG ID and local replica role. -- select @ag_id = ars.group_id, @role = ars.role from sys.dm_hadr_availability_replica_states ars where ars.replica_id = @ar_id if (@ag_id is null or @role is null) begin -- Database is not in an AG, must be a standalone DB so return 1. -- return 1 end -- Get automated backup preference. -- select @ag_resource_id = ag.resource_id, @ag_pref = ag.automated_backup_preference from sys.availability_groups ag where ag.group_id = @ag_id -- Get primary AG replica server name. -- select @primary_server_name = ags.primary_replica from sys.dm_hadr_availability_group_states ags where ags.group_id = @ag_id; select @local_server_name = @@servername; declare replica_cursor cursor static local forward_only for select cast(ar.replica_id as nvarchar(36)) as replica_id, @ag_resource_id as resource_id, case ar.replica_server_name when @primary_server_name then 1 -- primary else 2 -- secondary end as role, case ar.replica_server_name when @local_server_name then 1 else 0 end as is_local from sys.availability_replicas ar where ar.group_id = @ag_id and ar.backup_priority > 0 and -- 0 is a flag that backups are never desired on this replica. convert ( char(1), ( select case ar2.replica_server_name when @primary_server_name then 1 -- primary else 2 -- secondary end from sys.availability_replicas ar2 where ar2.replica_id = ar.replica_id ) ) like case @ag_pref when 0 then '1' -- Primary preferred when 1 then '2' -- Secondary Only when 2 then '2' -- Secondary Preferred when 3 then '%' -- No Preference end order by ar.backup_priority desc, role asc, ar.replica_server_name asc; declare @resource_id nvarchar(100); declare @replica_id nvarchar(100); declare @available bit; declare @dbstate int; declare @found int; set @found = 0 open replica_cursor; fetch next from replica_cursor into @replica_id, @resource_id, @role, @islocal; while (@@fetch_status = 0) begin if (@role = 1) -- If it's a Primary, it has to be online unless everything is down. begin set @found = 1 break; end select @dbstate = RetrieveDbReplicaState(@resource_id, @database_group_id, @replica_id) if (@dbstate = 1) begin set @found = 1 break --We have a valid replica. end fetch next from replica_cursor into @replica_id, @resource_id, @role, @islocal; end close replica_cursor; deallocate replica_cursor; if (@found = 0 and @ag_pref = 2) begin -- No secondaries available, see if the primary is local. -- if (@primary_server_name = @local_server_name) begin return 1; end return 0; end if (@found = 1 AND @islocal = 1) begin return 1; end return 0; end; |