SQL SERVER – Planned and Unplanned Availablity Group Failovers – Notes from the Field #031

[Note from Pinal]: This is a new episode of Notes from the Fields series. AlwaysOn is a very complex subject and not everyone knows many things about this. The matter of the fact is there is very little information available on this subject online and not everyone knows everything about this. This is why when a very common question related to AlwaysOn comes, people get confused. Let us learn about Planned and Unplanned Availablity Group Failovers.

In this episode of the Notes from the Field series database expert John Sterrett (Group Principal at Linchpin People) explains a very common issue DBAs and Developer faces in their career and is related to Planned and Unplanned Availablity Group Failovers. Linchpin People are database coaches and wellness experts for a data-driven world. Read the experience of John in his own words.

Whenever a disaster occurs it will be a stressful scenario regardless of how small or big the disaster is. This gets multiplied when it is your first time working with newer technology or the first time you are going through a disaster without a proper run book. Today were going to help you establish a run book for creating a planned failover with availability groups.

To make today’s session simple were going to have two instances of SQL Server 2012 included in an availability group and walk through the steps of doing an unplanned failover. We will focus on using the user interface and T-SQL to complete the failovers. We are going to use a two replica Availability Group where each replica is in another location. Therefore, we will be covering Asynchronous (non automatic failover) the following is a breakdown of our availability group utilized today.

Seeing the following screen might be scary the first time you come across an unplanned failover. It looks like our test database used in this Availability Group is not functional and it currently isn’t. The database status is not synchronizing which makes sense because the primary replica went down so it couldn’t synchronize. With that said, we can still failover and make it functional while we troubleshoot why we lost our primary replica.

To start we are going to right click on the availability group that needs to be restarted and select failover.

This will bring up the following wizard, which will walk you through several steps needed to complete the failover using the graphical user interface provided with SQL Server Management Studio (SSMS).

You are going to see warning messages simply because we are in Asynchronous commit mode and can not guarantee ‘no data loss’ when we do failover.

Just incase you missed it; you get another screen warning you about potential data loss because we are in Asynchronous mode.

Next we get to connect to the specific replica we want to become the primary replica after the failover occurs. In our case, we only have two replicas so this is trivial.

In order to failover, it’s required to connect to the replica that will become primary. The following screen shows that the connection has been made successfully.

Next, you will see the final summary screen. Once again, this reminds you that the failover action will cause data loss as were using Asynchronous commit mode due to the distance between instances used for disaster recovery.

Finally, once the failover is completed you will see the following screen.

If you followed along this long you might be wondering what T-SQL scripts are generated for clicking through all the sections of the wizard. If you have used Database Mirroring in the past you might be surprised. It’s not too different, which makes sense because the data is being replicated via SQL Server endpoints just like the good old database mirroring.

Now were going to take a look at how to do a failover with just T-SQL. First, were going to need to open a new query window and run our query in SQLCMD mode. Just incase you haven’t used SQLCMD mode before we will show you how to enable it below.

Now you can run the following statement. Notice, we connect to the replica we want to become primary after failover and specify to force failover to allow data loss. We can use the following script to failback over when our primary instance comes back online.

:Connect SQL2012PROD1

Are your servers running at optimal speed or are you facing any SQL Server Performance Problems? If you want to get started with the help of experts read more over here: Fix Your SQL Server.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Notes from the Field, SQL High Availability, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Iridium I/O – SQL Server Deduplication that Shrinks Databases and Improves Performance
Next Post
MySQL – Introduction to CONCAT and CONCAT_WS functions

Related Posts

5 Comments. Leave new

  • Thanks John, that was a useful write up. As I was going through the steps in the user interface, I was thinking to myself that it was unfortunate you were doing that instead of showing how to script it and that I was going to ask for that. Then you answered my question before I asked. Well done. Remarkable how much user interface there is related to such a small script. Thanks again.

  • learnersstreak
    June 8, 2014 1:07 pm

    No wonder any help is good help but you should correct the GUI screen shots & list out the cons in this approach. Add on the cluster availability & quorum details.

  • I have created a availability group with 2 nodes. Also created a listener , but the issue is that using the listener I can only connect from primary to primary ( using a RDP session). It never allow me to connect to primary from secondary node.

    Failover works fine for me.

    Not able to understand or find the cause, looking forward for a help.

  • Hi,

    Question: Our architecture is Primary to Secondary Synchronous and Primary to DR Replica with possible data loss (Asc). For every 6 months we do Force failover exercise from Primary to DR.

    This exercise runs for 4 hours, in this 4 hours Primary and DR nodes node weight is 1 only.
    but Can we replicate data from DR to Primary once we failback to Primary server?


  • Continuation from above….
    Primary data base consist of 1 secondary (Synchronous) and 1 DR (Asynchronous)

    Question is When we do forced fail over, what ever the data operations we do on DR replica. can we get the same data / is replicate to actual primary once we fail back?

    If this is not the case how I can achieve data operations happened in DR transferred to Actual Primary?



Leave a ReplyCancel reply

Exit mobile version