Here’s another quick way you can query the primary server. I’ve posted about this before sure; but that was using a precise result. If you wanted to see all servers involved you can use this little number. It works by simply looking at the [ROLE_DESC] value from sys.dm_hadr_availability_replica_states. Simply put… If the PRIMARY is listed, then you are on the Primary server because the Primary will show the servers involved in the AlwaysOn configuration.
If this is run from the Secondary server then the PRIMARY value from the [ROLE_DESC] will not be listed. It’s a simple method to determine which server is which. I use this with certain automation logic as a quick check before any processes are carried out. This particular example is of an old Sharepoint 2013 environment which is using an OS of Server 2012 environment with SQL Server 2014. Ancient at this point but doesn’t hurt to mention it.
use master; set nocount on set ansi_warnings off select * from sys.dm_hadr_availability_replica_states; if exists(select role_desc from sys.dm_hadr_availability_replica_states where role_desc in ('primary')) begin print 'The Primary role was found.' end else print 'The Primary role was NOT found.'
Categories: Agent Jobs, Alerts, Automations, Backup & Restore, Big Data, Cluster, Command Prompt, Configuration, Database Mail, Database Mirroring, ELI5, Error Solutions, Failover, Failover Cluster, Featured, Folder System, Forensics & Auditing, High Availability, How To, Installations, Long Scripts, Management Studio, Mirroring, Notification, Powershell, Quick Fix, Scripts, Security, Short Scripts, SQL Stuff, Top, TSQL, Work Arounds