Troubleshooting Availability Groups

A new system function has been added that will help you identify if the database and instance you are currently working on is the primary replica. The function is called sys.fn_hadr_is_primary_replica and the syntax for the is function is as follows:

sys.fn_hadr_is_primary_replica ( 'dbname' )

Here, 'dbname' is the name of the database you want to check. The procedure returns a 1, if it evaluates to true, that is, it is the primary replica. You can combine this with some logical code to determine which section of the code to run depending on whether the replica is currently the primary replica.

The following script will back up your database if the current instance that it runs is the primary instance:

declare @dbname varchar(30)
declare @backuplocation varchar(80)

set @dbname = 'DB1'
Set @backuplocation = 'c:\BACKUP\'
set @backuplocation = @backuplocation + @dbname
If sys.fn_hadr_is_primary_replica ( @dbname ) <> 1 
begin 
SELECT 'Nothing to backup...