Disaster Recovery Using AlwaysON Availability Group – Scenario 2

Environment

Blog25_1-> Disaster Recovery scenario is as below,

  • PRIMARY DATA CENTRE goes down.
  • The databases on Server JBSERVER2 should be made online and have the application connect to Database Server JBSERVER2.
  • Failback Availability group back to JBSERVER1 when PRIMARY DATA CENTRE comes online.
  • The changes made by the Application on JBSERVER2\IN2014 should be discarded.

-> Checking the current AlwaysON setup in SQL Server Management Studio,

Blog26_2

-> The Database JB_DB contains a table named Table5, which we will use for testing.

USE [JB_DB]
GO
CREATE TABLE [dbo].[Table5](
[sno] [int] IDENTITY(1,1) NOT NULL,
[sname] [char](2000) NULL,
[sname1] [char](2000) NULL,
[sname2] [char](2000) NULL,
PRIMARY KEY CLUSTERED
(
[sno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Set nocount on
insert into Table5 values (‘a’,’b’,’c’)
go 10002

-> Checking the row count for object Table5,

Blog26_3

-> The PRIMARY DATA CENTRE goes down. 2 votes are lost as Database Server JBSERVER1 and file share witness is down. Now that 2 votes out of 3 votes are lost, the cluster goes down.

-> Checking the SQL Server instance and Eventlogs on JBSERVER2 when the Primary Data Centre is down.

Blog26_4

-> Implementing Force Quorum on JBSERVER2.

Blog26_5

-> Checking the SQL Server Instance after Force Quorum,

Blog26_6

-> I will suspend the data movement now,

Blog26_7

Blog26_8

Blog26_9

-> Right-click the availability group to be failed over, and select Failover.

Blog26_10

Blog26_11

Blog26_12

Blog26_13

Blog26_14

-> Checking the SQL Server Instance after the failover with data loss.

Blog26_15

-> The application can now connect to SQL Server Instance JBSERVER2\IN2014 using the Listener and use it. Let us insert a single row to the table for testing and make sure if it can be seen later after the failback.

insert into Table5 values (‘a’,’b’,’c’)
go

Blog26_16

-> The PRIMARY DATA CENTRE comes online. Database Server JBSERVER1 and File witness are online now.

-> Connecting to JBSERVER1\IN2014 and Suspending the data movement,

Blog26_17

Blog26_18

Blog26_19

-> We will failback the Alwayson Availability group to JBSERVER1\IN2014,

Blog26_20

Blog26_21

Blog26_22

Blog26_23

Blog26_24

Blog26_26

Blog26_27

-> On SQL Server Instance JBSERVER1\IN2014, we will resume the data movement.

Blog26_28

Blog26_29

-> On SQL Server Instance JBSERVER2\IN2014, we will resume the data movement.

Blog26_30

Blog26_31

-> Checking the SQL Server Instance now,

Blog26_32

-> Checking the row count for object Table5 on JBSERVER1\IN2014 after failback,

Blog26_33

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Advertisements

Disaster Recovery Using AlwaysON Availability Group – Scenario 1

Environment

Blog25_1

-> Disaster Recovery scenario is as below,

  • PRIMARY DATA CENTRE goes down. 
  • The databases on Server JBSERVER2 should be made online and have the application connect to Database Server JBSERVER2. 
  • Failback Availability group back to JBSERVER1 when PRIMARY DATA CENTRE comes online. 
  • The changes made by the Application on JBSERVER2\IN2014 should be relayed to JBSERVER1\IN2014

-> Checking the current AlwaysON setup in SQL Server Management Studio,

Blog25_2

-> The Database JB_DB contains a table named Table5, which we will use for testing.

USE [JB_DB]
GO
CREATE TABLE [dbo].[Table5](
[sno] [int] IDENTITY(1,1) NOT NULL,
       [sname] [char](2000) NULL,
       [sname1] [char](2000) NULL,
[sname2] [char](2000) NULL,
PRIMARY KEY CLUSTERED
(
       [sno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Set nocount on
insert into Table5 values (‘a’,’b’,’c’)
go 10002

-> Checking the row count for object Table5,

Blog25_3

-> The PRIMARY DATA CENTRE goes down. 2 votes are lost as Database Server JBSERVER1 and file share witness is down. Now that 2 votes out of 3 votes are lost, the cluster goes down.

-> Checking the SQL Server instance and Eventlogs on JBSERVER2 when the Primary Data Centre is down.

Blog25_4

-> Implementing Force Quorum on JBSERVER2.

Blog25_5

-> Checking the SQL Server Instance after Force Quorum,

Blog25_6

-> I will suspend the data movement now,

Blog25_7

Blog25_8

Blog25_9

-> Right-click the availability group to be failed over, and select Failover.

Blog25_10

Blog25_11

Blog25_12

Blog25_13

Blog25_14

-> Checking the SQL Server Instance after the failover with data loss.

Blog25_15

-> The application can now connect to SQL Server Instance JBSERVER2\IN2014 using the Listener and use it. Let us insert a single row to the table for testing and make sure if it can be seen later after the failback.

insert into Table5 values (‘a’,’b’,’c’)
go

Blog25_16

-> The PRIMARY DATA CENTRE comes online. Database Server JBSERVER1 and File witness are online now.

-> Connecting to JBSERVER1\IN2014 and Suspending the data movement,

Blog25_17Blog25_18

Blog25_19

-> On SQL Server Instance JBSERVER2\IN2014, we will resume the data movement.

Blog25_20

Blog25_21

-> On SQL Server Instance JBSERVER1\IN2014, we will resume the data movement.

Blog25_22

Blog25_23

-> Checking the SQL Server Instance now,

Blog25_24

-> We will failback the Alwayson Availability group to JBSERVER1\IN2014,

Blog25_25

Blog25_26

Blog25_27

Blog25_28

Blog25_29

Blog25_30

Blog25_31

-> Checking the row count for object Table5 on JBSERVER1\IN2014 after failback,

Blog25_32

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.