Can't identify which server is primary and which is secondary in a SQL Server Availability group



  • I'm writing an application that queries the backup history for a number of databases on a number of SQL Server 2017 servers.

    Assuming a pair of servers (SQL001 and SQL002) and a database called "foo", how can I write a SQL query to tell me which server is the primary and which server is the secondary in the availability group?



  • One way you could approach this is to utilise the function https://docs.microsoft.com/en-us/sql/t-sql/functions/databasepropertyex-transact-sql?view=sql-server-ver15 with the property 'Updateability'.

    For example:

    DECLARE @STATE VARCHAR(100);
    SET @STATE = (select CAST(DATABASEPROPERTYEX('foo', 'Updateability') as varchar(100)));        
    

    IF(@STATE) <> 'READ_ONLY'
    BEGIN
    Select @@servername as [ServerName]
    End;




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2