Recently I had a requirement to run a script on all the databases that are either AG primary or standalone in SQL instance. Although for a single database we can easily determine of that database belongs to rpimary replica or not using function “sys.fn_hadr_is_primary_replica” but challenge for me was to get a list of all DBs which were either primary or standalone on the particular replica instance.
When I searched through internet, I found below useful queries for my case which was giving me output as DBName, AGName and AGType
Query 1:
select
sd.name,
coalesce(grp.ag_name,'N/A'),
case
when reps.is_primary_replica = 1 then 'PRIMARY'
when reps.is_primary_replica = 0 then 'SECONDARY'
else 'NOT REPLICATED'
end as AGType
from sys.databases as sd
left outer join sys.dm_hadr_database_replica_states as reps
on reps.database_id = sd.database_id
and reps.replica_id = sd.replica_id
left outer join sys.dm_hadr_name_id_map as grp
on grp.ag_id = reps.group_idQuery 2:
select DISTINCT sd.name,
(
case
when
hdrs.is_primary_replica IS NULL then 'NOT REPLICATED'
when exists ( select * from sys.dm_hadr_database_replica_states as irs where sd.database_id = irs.database_id and is_primary_replica = 1 ) then
'PRIMARY'
else
'SECONDARY'
end
) as AGType,
COALESCE(grp.ag_name,'N/A') as AGName
from sys.databases as sd
left outer join sys.dm_hadr_database_replica_states as hdrs on hdrs.database_id = sd.database_id
left outer join sys.dm_hadr_name_id_map as grp on grp.ag_id = hdrs.group_idAs I wanted to filter the output from above queries to get list of databases that are either AG primary or standalone in SQL instance, I used common table expression (CTE) to achieve it.
CTE of Query 1:
;WITH DBLIST as (select
sd.name,
coalesce(grp.ag_name,'N/A') as AGNAME,
case
when reps.is_primary_replica = 1 then 'PRIMARY'
when reps.is_primary_replica = 0 then 'SECONDARY'
else 'NOT REPLICATED'
end as AGType
from sys.databases as sd
left outer join sys.dm_hadr_database_replica_states as reps
on reps.database_id = sd.database_id
and reps.replica_id = sd.replica_id
left outer join sys.dm_hadr_name_id_map as grp
on grp.ag_id = reps.group_id
)
SELECT @@SERVERNAME as SERVERNAME,* from DBLIST where AGType <>'Secondary' and name NOT IN ('master', 'model', 'msdb', 'tempdb')CTE of Query 2:
WITH DBLIST as (
select DISTINCT sd.name,
(
case
when
hdrs.is_primary_replica IS NULL then 'NOT REPLICATED'
when exists ( select * from sys.dm_hadr_database_replica_states as irs where sd.database_id = irs.database_id and is_primary_replica = 1 ) then
'PRIMARY'
else
'SECONDARY'
end
) as AGType,
COALESCE(grp.ag_name,'N/A') as AGName
from sys.databases as sd
left outer join sys.dm_hadr_database_replica_states as hdrs on hdrs.database_id = sd.database_id
left outer join sys.dm_hadr_name_id_map as grp on grp.ag_id = hdrs.group_id)
SELECT @@SERVERNAME as SERVERNAME,* from DBLIST where AGType <>'Secondary' and name NOT IN ('master', 'model', 'msdb', 'tempdb')Once CTE expression was ready I used it to execute the desired query on all databases through cursor.
-------------------------------------------------------------------
-- Declare a cursor to loop through all the databases on the server
-------------------------------------------------------------------
DECLARE csrDB CURSOR FOR
WITH DBLIST as (
select DISTINCT sd.name,
(
case
when
hdrs.is_primary_replica IS NULL then 'NOT REPLICATED'
when exists ( select * from sys.dm_hadr_database_replica_states as irs where sd.database_id = irs.database_id and is_primary_replica = 1 ) then
'PRIMARY'
else
'SECONDARY'
end
) as AGType,
COALESCE(grp.ag_name,'N/A') as AGName
from sys.databases as sd
left outer join sys.dm_hadr_database_replica_states as hdrs on hdrs.database_id = sd.database_id
left outer join sys.dm_hadr_name_id_map as grp on grp.ag_id = hdrs.group_id
where sd.state_desc = 'ONLINE')
SELECT name from DBLIST where AGType <>'Secondary'
and name NOT IN ('master', 'model', 'msdb', 'tempdb')
---------------------------------------------------
-- Open the cursor and get the first database name
---------------------------------------------------
OPEN csrDB
FETCH NEXT
FROM csrDB
INTO @DBName
-- Loop through the cursor
WHILE @@FETCH_STATUS = 0
BEGIN
---------------------------------------
<Desired Code to run on all databases>
---------------------------------------
-- Get the next database name
FETCH NEXT
FROM csrDB
INTO @DBName
-- End of the cursor loop
END
-- Close and deallocate the CURSOR
CLOSE csrDB
DEALLOCATE csrDBHope you will find this helpful!
Happy learning!!
References:
https://dimitri.janczak.net/2016/07/08/database-part-availability-group/