About Me

My photo
I am an MCSE in Data Management and Analytics, specializing in MS SQL Server, and an MCP in Azure. With over 19+ years of experience in the IT industry, I bring expertise in data management, Azure Cloud, Data Center Migration, Infrastructure Architecture planning, as well as Virtualization and automation. I have a deep passion for driving innovation through infrastructure automation, particularly using Terraform for efficient provisioning. If you're looking for guidance on automating your infrastructure or have questions about Azure, SQL Server, or cloud migration, feel free to reach out. I often write to capture my own experiences and insights for future reference, but I hope that sharing these experiences through my blog will help others on their journey as well. Thank you for reading!

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.



Labels: