sql 2016 alwayson question about failover

geradmin 1 Reputation point
2021-03-15T13:19:44.48+00:00

Hi guys, i have this question. we have 2 sql 2016 standard with some databases replicated with basic alwayson. So last night the primary server crashed (BSOD and reboot) and the secondary sql has started the failover. This morning i have done a manual failover to move again the DB on primary server. i have a doubt, the manual failover need to be done from the sql with primary replica? or from the sql with secondary replica? or doesn't matter? thanks Andrea

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,330 questions
0 comments No comments
{count} votes

4 answers

Sort by: Most helpful
  1. Robbie Varn 346 Reputation points
    2021-03-15T14:07:08.033+00:00

    You can initiate a manual failover from either the Primary or Secondary. I prefer the Primary in order to see the AG dashboard show correct information.

    0 comments No comments

  2. geradmin 1 Reputation point
    2021-03-15T14:12:56.647+00:00

    hi, thanks for reply

    from your experience after the failback (with option "replica with no data loss") is useful run a checkdb for this database?

    cheers

    Andrea


  3. CathyJi-MSFT 21,126 Reputation points Microsoft Vendor
    2021-03-16T03:24:02.867+00:00

    Hi @geradmin ,

    > i have a doubt, the manual failover need to be done from the sql with primary replica?

    You can do manual failover on primary replica or secondary replica. Before manual failover, please make sure the primary replica and the target secondary replica are running in synchronous-commit mode and are currently synchronized.

    > after the failback (with option "replica with no data loss") is useful run a checkdb for this database

    As Shashank mentioned, suggest you do not do that. DBCC CHECKDB is an IO intensive operation, this operation will have some performance implications. After failover, suggest you using AG dashboard to check the AG state.


    If the response is helpful, please click "Accept Answer" and upvote it, thank you.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  4. geradmin 1 Reputation point
    2021-03-16T08:15:30.717+00:00

    Hi, yes when i did the failback on wizard's windows the state of replica was green. To be sure I ran the integrity check anyway, the DB the wa have is small, the biggest is 50GB and the checkdb has took 10 minutes.

    thanks

    Andrea