About Me

My photo
I am MCSE in Data Management and Analytics with specialization in MS SQL Server and MCP in Azure. I have over 13+ years of experience in IT industry with expertise in data management, Azure Cloud, Data-Canter Migration, Infrastructure Architecture planning and Virtualization and automation. Contact me if you are looking for any sort of guidance in getting your Infrastructure provisioning automated through Terraform. I sometime write for a place to store my own experiences for future search and read by own blog but can hopefully help others along the way. Thanks.

Perform a Planned Manual Failover of an Availability Group 



This topic describes how to perform a manual failover without data loss (a planned manual failover) on an AlwaysOn availability group by using  Transact-SQL,  in SQL Server 2012. 
An availability group fails over at the level of an availability replica. A planned manual failover, like any AlwaysOn Availability Groups failover, transitions a secondary replica to primary role and, concurrently, transitions the former primary replica to the secondary role.

To manually fail over an availability group
  1. Connect to the server instance that hosts the target secondary replica.
  2. Use the ALTER AVAILABILITY GROUP statement, as follows:
    ALTER AVAILABILITY GROUP group_name FAILOVER
    where group_name is the name of the availability group.
    The following example manually fails over the MyAg availability group to the connected secondary replica.
Command:-

    Alter availability group SQL00XXDAG01  failover 

Sometimes  this command gets fail.. and get below error.


Error:
Msg 41122, Level 16, State 12, Line 1

Cannot failover availability group 'SQL00XXDAG01' to this instance of SQL Server.  The local availability replica is already the primary replica of the availability group.  To failover this availability group to another instance of SQL Server, run the failover command on that instance of SQL Server.  If a local instance of SQL Server is intended to host the primary replica of the availability group, then no action is required.

Resolution: -
 it looks you executed that command from primary replica.

 You have to  Execute  failover command from that secondary replica.