Post reply

SQL Server 2014 AlwaysOn failover

  • July 25, 2017 at 2:49 am

    #331983

    Hi everyone,

    I have 3 SQL servers 2014 Enterprise (Windows Servers 2012 R2) in a cluster AlwaysOn on 1 availability group and that are configured as follows:

    Availability Group 1

    Availability Replicas:

    •    DB1 : primary server (Availability mode : synchronous commit, failover mode : automatic) located in DC1
    •    DB2 : secondary server ( Availability mode : asynchronous commit, failover mode : manual) located in DC2
    •    DB3 : secondary server (Availability mode : synchronous commit, failover mode : automatic) located in DC1

    Availability Databases

    •     databaseNumber1, databaseNumber2, databaseNumber3

    Availability Group Listeners

    • Listener1

    I have to shutdown my main server DB1 because I need to replace 1 memory module which is in a critical state.

    In this architecture, there is a problem with the failover for the databaseNumber2.
    If DB1 goes down, all databases except databaseNumber2 are moved to DB3.

    Our previous DBA who installed the solution SQL Server AlwaysOn spent a lot of time for fixing this problem and looked at every logs:
    - in test environment (similar to production environment) with the same databases, failover for all databases was working.
    - in production environment, failover for the databaseNumber2 always failed.

    So in this post, I don't try to fix this issue.

    My problem :
    If DB1 crashes before the change of the memory module, all the databases except databaseNumber2 will move to DB3 (it is currently in synchronous automatic).
    If it is impossible to restart DB1, what should I exactly need to do in order to make the databaseNumber2 working on DB3 ?

    Thank you in advance for your help.

  • July 25, 2017 at 4:13 am

    #1952521

    are you implying that database2 fails to move to either of the partner replicas?
    If this is the case, try removing it from the AG and re initiliasing the secondarys

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • July 25, 2017 at 7:50 am

    #1952559

    Hi Perry,

    Yes, you are right. The databaseNumber2 fails to move to the replica DB3 and I didn't have the opportunity to test with the replica DB2 because SQL server is used 24h/7.  
    On the 3 SQL servers, I forgot to mention that there is also SSRS installed but it is used in another availibility group with another listener.

    Let's take an example : 

    Before stopping DB1, I will do a failover between DB1 and DB2. So I will put DB2 in synchronous first and then do the failover. 
    DB2 which was the secondary on the availability group becomes the primary.

    If the databaseNumber2 fails to move on DB2 (if i am not wrong the database appears in "Resolving" state) and if it happened that DB1 can't be restarted because of a hardware problem, I will have to connect on DB2 => Availaibility Groups = > Availability Databases => Right click on databaseNumber2 => click on Remove Secondary Database

    And then, what steps do I need to do in order to have the databaseNumber2 working on DB2?

  • July 27, 2017 at 11:37 am

    #1953076

    Hi ShawnShine,

    To setup databaseNumber2 on DB2, Restore a full backup of databaseNumber2 on DB2. (you ought to do a full backup of the databases before you start your activity)

    Once your DB1 is up, add databaseNumber2 in your Always ON Group. Perform the following steps:
    1) Do a Full backup of databaseNumber2 (in DB2)
    2) Restore the full backup of databaseNumber2 in DB1 (NORECOVERY mode)
    3) Take a transaction log of databaseNumber2 in DB2
    4) Apply the transaction log to databaseNumber2 in DB1 (NORECOVERY mode)
    5) Use the Add Database to Availability Group Wizard

    a) In Object Explorer, connect to the server instance that hosts the primary replica of the availability group, and expand the server tree.

    b) Expand the Always On High Availability node and the Availability Groups node.

    c) Right-click the availability group to which you are adding a database, and select the Add Database command. This command launches the Add Database to Availability Group Wizard.

    d) On the Select Databases page, select one or more databases. For more information, see Select Databases Page (New Availability Group Wizard and Add Database Wizard).

    e) If the database contains a database master key, enter the password for the database master key in the Password column.

    f) On the Select Initial Data Synchronization page, choose how you want your new secondary databases to be created and joined to the availability group.
     Choose JOIN ONLY.g) On the Connect to Existing Secondary Replicas page, if the instances of SQL Server that host the availability replicas for this availability group are
     all running as a service in the same user account, click Connect all. If any of the server instances are running as a service under different accounts,
     click the individual Connect button to the right of each server instance name.h) On the Summary page, review your choices for the new availability group. To make a change,
     click Previous to return to the relevant page. After making the change, click Next to return to the Summary page.If you are satisfied with your selections, optionally click Script to create a script of the steps the wizard will execute.
    Then, to create and configure the new availability group, click Finish.

  • July 28, 2017 at 3:34 am

    #1953164

    shawnshine - Tuesday, July 25, 2017 7:50 AM

    Hi Perry,

    Yes, you are right. The databaseNumber2 fails to move to the replica DB3 and I didn't have the opportunity to test with the replica DB2 because SQL server is used 24h/7.  
    On the 3 SQL servers, I forgot to mention that there is also SSRS installed but it is used in another availibility group with another listener.

    Let's take an example : 

    Before stopping DB1, I will do a failover between DB1 and DB2. So I will put DB2 in synchronous first and then do the failover. 
    DB2 which was the secondary on the availability group becomes the primary.

    If the databaseNumber2 fails to move on DB2 (if i am not wrong the database appears in "Resolving" state) and if it happened that DB1 can't be restarted because of a hardware problem, I will have to connect on DB2 => Availaibility Groups = > Availability Databases => Right click on databaseNumber2 => click on Remove Secondary Database

    And then, what steps do I need to do in order to have the databaseNumber2 working on DB2?

    Have you checked the logs?
    Are databaseNumber1, databaseNumber2, databaseNumber3 in the same AG or separate groups?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • July 30, 2017 at 8:10 am

    #1953304

    Hi everyone,
    I did the failover from DB1 to DB2 (never did it the failover in this order before) 2 days ago and it went well for all databases which are in tthe same AG @perry.
    Thank you @Ricky for the detailed steps. It is very important to know these steps if the failover doesn't work for some reasons.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply