Archive for October 23rd, 2013

SQL Server AlwaysOn-Find my Primary October 23rd, 2013

Vinod Kumar

This week is full of useful scripts that I thought is worth sharing. The more I talk with customers the more there is to talk about AlwaysOn. As the basics, AlwaysOn technologies is an scale out technology when working with SQL Server. This scale out is for Read Only workloads and it is important to understand this fundamental difference. In SQL Server 2012 we can have upto 5 replica’s and in SQL Server 2014 we can have as many as 8 replica’s in an AlwaysOn deployment.

As in many customer interactions, one of the common and simple question asked is – what is my current Primary replica NOW? Because I have set up Automatic failover etc and I am using listener to connect and I am not aware about my Primary. So here is a simple query with DMVs that will get you the required information.

SELECT AGS.name             AS AGName,

       HAGS.primary_replica AS PrimaryInstance

FROM   sys.dm_hadr_availability_group_states HAGS

INNER JOIN sys.availability_groups AGS

ON HAGS.group_id = AGS.group_id

This is a very basic query but the most useful query to start. For most cases, running queries on the Primary gives us more information than when run on secondary. More of this will be discussed in future posts.

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).

Continue reading...