I’m not a DBA by any stretch of the imagination. I do, however, need to provide basic infrastructure support to a number of Microsoft SQL servers.
A number of our MSSQL 2012 servers have been configured to use AlwaysOn. This presents a challenge when developers/admins need to run maintenance scripts because you can only run these against the primary database if you need to modify.
Doesn’t sound like much of an issue, but what happens if you run the script against a secondary node? Well, it fails with an error. I’d rather have something a little cleaner than that, and so came up with the below T-SQL script that you can wrap around your maintenance script and fail cleanly.
-- Useful when running scripts so that it only runs on the primary node.
IF (SELECT SERVERPROPERTY('MachineName')) = (SELECT primary_replica FROM sys.dm_hadr_availability_group_states)
PRINT 'This is the Primary Node. Running...'
-- Insert Your Script or Code Here
PRINT 'This is not the Primary Node. Stopping.'