Here’s a quick TSQL query to show you the primary server in an always on configuration. This is helpful in writing logic which first detects if the local server is indeed the Primary server or not.
if exists(select is_local, role_desc from sys.dm_hadr_availability_replica_states where role = 1 and role_desc = 'PRIMARY') begin print 'This server [' + upper(@@servername) + '] is the primary.' end else print 'This server [' + upper(@@servername) + '] is NOT the primary.'
Categories: Agent Jobs, Alerts, Automations, Backup & Restore, Big Data, Command Prompt, Database Mail, Database Mirroring, ELI5, Error Solutions, Folder System, Forensics & Auditing, High Availability, Long Scripts, Management Studio, Mirroring, Notification, Powershell, Quick Fix, Scripts, Security, Short Scripts, SQL Stuff, Top, TSQL, Work Arounds