SQL Server – How to Skip ‘Read Only’ Databases with Many AOAG

sql-server-2012

For example, I want to run an index maintenance job on all databases.

My server has many AOAG, and is often playing the primary and secondary replica role for different AOAGs at the same time.

If I have a database that's secondary on that server, and it's read only, I can't find this information in sys.databases because, it shows 0.

How can I run a cursor, and skip the databases that are part of an AOAG that is a secondary replica, and not break the job?

I know I can use…
SELECT DATABASEPROPERTYEX('MyDBNAme', 'Updateability')

But is there a easier way to implement this into a cursor/query/procedure?

thanks

Best Answer

I typically use the system function fn_hadr_database_is_primary_replica. It accepts database name as a parameter. Then returns 1 if the database is part of an Availability Group and the primary replica, 0 if it’s not on the primary replica, and NULL if the database is not part of an Availability Group.

For example…

IF  fn_hadr_database_is_primary_replica('my_database') = 1
    OR fn_hadr_database_is_primary_replica('my_database') IS NULL
BEGIN
    <do index maintenance on my_database>
END

This would go inside an existing loop, where you already have the list of database names from sys.databases. Then as you move on to each new database name in the loop, you'd call this IF statement.

Related Question