My latest explorations have been to look at what are the fine print details that got added with SQL Server 2014. Especially, I have been exploring what has fundamentally changed when it comes to SQL Server 2014 – AlwaysOn capabilities. Though at the outset this looks simple and basic, some of these enhancements are pretty cool and needs a mention. Hence you are getting these bunch of blogposts inspired by this learning.
In SQL Server 2012, we had this neat little function – sys.fn_hadr_backup_is_preferred_replica which allowed us to be used during the backup operation if the replica we are running backup against is the preferred replica as per the AlwaysOn configuration. Though it was a neat addition there was no way to figure out if the node on which the Maintenance plan was running was ever the Primary replica. To illustrate the enhancement, I am having the following configuration as shown in the figure below.
As you can see I have two servers SQLALWAYSON01 and SQLALWAYSON02 servers and the -01 server is the PRIMARY. With SQL Server 2014, there is a new function that gets introduced – sys.fn_hadr_is_primary_replica. This nifty function when passed with the database name will return 1 if it is run on the PRIMARY. Else it will return you 0, if invoked from the secondary replicas.
In my opinion this is a critical and nifty option because you might want to do certain operations ONLY on the Primary like index rebuilding, full backups or whatever. Apart from these values, if you by any chance pass a non-AG database to the function it will return you NULL as shown in the figure below. Do let me know if you will use this function and what are the scenario’s you will find this useful. It can surely be a great learning for me too to listen from you.
This post is inspired from Book content: SQL Server 2012 AlwaysOn Joes 2 Pros®: A Tutorial for Implementing High Availability and Disaster Recovery using AlwaysOn Availability Groups” (Paperback, Kindle).