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.<\/p>\n
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. <\/p>\n
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.<\/p>\n
<\/p>\n
\r\n-- Useful when running scripts so that it only runs on the primary node.\r\nIF (SELECT SERVERPROPERTY('MachineName')) = (SELECT primary_replica FROM sys.dm_hadr_availability_group_states)\r\n PRINT 'This is the Primary Node. Running...'\r\n -- Insert Your Script or Code Here\r\nELSE\r\n PRINT 'This is not the Primary Node. Stopping.'\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"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 Continue reading MS SQL AlwaysOn – Is current node the Primary?<\/span>