1

I am trying to setup SQL agent job that will only execute if it is primary replica of Azure SQL MI of failover groups.

I am trying to get primary replica using sys.dm_hadr_database_replica_states where is_primary_replica =1 but when I run it on SQL MI in different region , it also returns value 1.

I am using dbo.fn_hadr_group_is_primary(@AGName)=1 function for on-prem so looking for something similar for Azure SQL MI.

Aleksey Vitsko
  • 6,148
  • 5
  • 39
  • 70
SqlDBA
  • 171
  • 2
  • 12

1 Answers1

2

In SQL Managed Instance, in Agent Jobs you can use this:

if (select databasepropertyex('YourDBName','updateability')) = 'READ_WRITE' begin
    <paste T-SQL here>
end

On primary managed instance within Failover Group, user databases will be in READ_WRITE mode. On secondary - READ_ONLY

I am trying to get primary replica using sys.dm_hadr_database_replica_states where is_primary_replica =1 but when I run it on SQL MI in different region , it also returns value 1.

This is because single Azure SQL Managed Instance has four internal replicas underneath it. It returns 1 because you are interacting with internal replica that is primary at the moment. This is not related to Failover Groups and doesn't show MI's role in a Failover Group.

Aleksey Vitsko
  • 6,148
  • 5
  • 39
  • 70