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

Tags: , , , , , , , , ,

This entry was posted on Wednesday, October 23rd, 2013 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

2 Responses to “SQL Server AlwaysOn-Find my Primary”

  1. Ramu says:

    Good one sir… I have a doubt sir.
    In SQL Server 2012 AlwaysOn how many replica’s we can configure 4 or 5

    AlwaysOn configuration can works in Clustering environment or not.

    • Vinod Kumar says:

      To answer your question – you can buy our book :) … hehehehe …

      In SQL Server 2012, in total 5 servers can be part of AG. 1 primary and 4 secondary.

      AlwaysOn is a high level feature name that encompasses both SQL Server Availability Groups and FCI (Failover clustering). So the short answer to your second question is, yes AlwaysOn can be deployed in Clustered Environments.

Leave a Reply