disaster recovery using alwayson availability group scenario 1 · -> on sql server instance...
TRANSCRIPT
![Page 1: DISASTER RECOVERY USING ALWAYSON AVAILABILITY GROUP SCENARIO 1 · -> On SQL Server Instance JBSERVER2\IN2014, we will resume the data movement. -> On SQL Server Instance JBSERVER1\IN2014,](https://reader035.vdocuments.us/reader035/viewer/2022070100/6002363b3ef001632970f27c/html5/thumbnails/1.jpg)
DISASTER RECOVERY USING ALWAYSON AVAILABILITY GROUP – SCENARIO 1
https://jbswiki.com/2017/08/03/disaster-recovery-using-alwayson-availability-group-scenario-1/
Environment:
-> 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,
![Page 2: DISASTER RECOVERY USING ALWAYSON AVAILABILITY GROUP SCENARIO 1 · -> On SQL Server Instance JBSERVER2\IN2014, we will resume the data movement. -> On SQL Server Instance JBSERVER1\IN2014,](https://reader035.vdocuments.us/reader035/viewer/2022070100/6002363b3ef001632970f27c/html5/thumbnails/2.jpg)
-> 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,
![Page 3: DISASTER RECOVERY USING ALWAYSON AVAILABILITY GROUP SCENARIO 1 · -> On SQL Server Instance JBSERVER2\IN2014, we will resume the data movement. -> On SQL Server Instance JBSERVER1\IN2014,](https://reader035.vdocuments.us/reader035/viewer/2022070100/6002363b3ef001632970f27c/html5/thumbnails/3.jpg)
-> 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.
-> Implementing Force Quorum on JBSERVER2.
-> Checking the SQL Server Instance after Force Quorum,
![Page 4: DISASTER RECOVERY USING ALWAYSON AVAILABILITY GROUP SCENARIO 1 · -> On SQL Server Instance JBSERVER2\IN2014, we will resume the data movement. -> On SQL Server Instance JBSERVER1\IN2014,](https://reader035.vdocuments.us/reader035/viewer/2022070100/6002363b3ef001632970f27c/html5/thumbnails/4.jpg)
-> I will suspend the data movement now,
![Page 5: DISASTER RECOVERY USING ALWAYSON AVAILABILITY GROUP SCENARIO 1 · -> On SQL Server Instance JBSERVER2\IN2014, we will resume the data movement. -> On SQL Server Instance JBSERVER1\IN2014,](https://reader035.vdocuments.us/reader035/viewer/2022070100/6002363b3ef001632970f27c/html5/thumbnails/5.jpg)
![Page 6: DISASTER RECOVERY USING ALWAYSON AVAILABILITY GROUP SCENARIO 1 · -> On SQL Server Instance JBSERVER2\IN2014, we will resume the data movement. -> On SQL Server Instance JBSERVER1\IN2014,](https://reader035.vdocuments.us/reader035/viewer/2022070100/6002363b3ef001632970f27c/html5/thumbnails/6.jpg)
-> Right-click the availability group to be failed over, and select Failover.
![Page 7: DISASTER RECOVERY USING ALWAYSON AVAILABILITY GROUP SCENARIO 1 · -> On SQL Server Instance JBSERVER2\IN2014, we will resume the data movement. -> On SQL Server Instance JBSERVER1\IN2014,](https://reader035.vdocuments.us/reader035/viewer/2022070100/6002363b3ef001632970f27c/html5/thumbnails/7.jpg)
![Page 8: DISASTER RECOVERY USING ALWAYSON AVAILABILITY GROUP SCENARIO 1 · -> On SQL Server Instance JBSERVER2\IN2014, we will resume the data movement. -> On SQL Server Instance JBSERVER1\IN2014,](https://reader035.vdocuments.us/reader035/viewer/2022070100/6002363b3ef001632970f27c/html5/thumbnails/8.jpg)
-> Checking the SQL Server Instance after the failover with data loss.
![Page 9: DISASTER RECOVERY USING ALWAYSON AVAILABILITY GROUP SCENARIO 1 · -> On SQL Server Instance JBSERVER2\IN2014, we will resume the data movement. -> On SQL Server Instance JBSERVER1\IN2014,](https://reader035.vdocuments.us/reader035/viewer/2022070100/6002363b3ef001632970f27c/html5/thumbnails/9.jpg)
-> 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
-> The PRIMARY DATA CENTRE comes online. Database Server JBSERVER1 and File witness are online now.
-> Connecting to JBSERVER1\IN2014 and Suspending the data movement,
![Page 10: DISASTER RECOVERY USING ALWAYSON AVAILABILITY GROUP SCENARIO 1 · -> On SQL Server Instance JBSERVER2\IN2014, we will resume the data movement. -> On SQL Server Instance JBSERVER1\IN2014,](https://reader035.vdocuments.us/reader035/viewer/2022070100/6002363b3ef001632970f27c/html5/thumbnails/10.jpg)
![Page 11: DISASTER RECOVERY USING ALWAYSON AVAILABILITY GROUP SCENARIO 1 · -> On SQL Server Instance JBSERVER2\IN2014, we will resume the data movement. -> On SQL Server Instance JBSERVER1\IN2014,](https://reader035.vdocuments.us/reader035/viewer/2022070100/6002363b3ef001632970f27c/html5/thumbnails/11.jpg)
-> On SQL Server Instance JBSERVER2\IN2014, we will resume the data movement.
![Page 12: DISASTER RECOVERY USING ALWAYSON AVAILABILITY GROUP SCENARIO 1 · -> On SQL Server Instance JBSERVER2\IN2014, we will resume the data movement. -> On SQL Server Instance JBSERVER1\IN2014,](https://reader035.vdocuments.us/reader035/viewer/2022070100/6002363b3ef001632970f27c/html5/thumbnails/12.jpg)
-> On SQL Server Instance JBSERVER1\IN2014, we will resume the data movement.
![Page 13: DISASTER RECOVERY USING ALWAYSON AVAILABILITY GROUP SCENARIO 1 · -> On SQL Server Instance JBSERVER2\IN2014, we will resume the data movement. -> On SQL Server Instance JBSERVER1\IN2014,](https://reader035.vdocuments.us/reader035/viewer/2022070100/6002363b3ef001632970f27c/html5/thumbnails/13.jpg)
-> Checking the SQL Server Instance now,
![Page 14: DISASTER RECOVERY USING ALWAYSON AVAILABILITY GROUP SCENARIO 1 · -> On SQL Server Instance JBSERVER2\IN2014, we will resume the data movement. -> On SQL Server Instance JBSERVER1\IN2014,](https://reader035.vdocuments.us/reader035/viewer/2022070100/6002363b3ef001632970f27c/html5/thumbnails/14.jpg)
-> We will failback the Alwayson Availability group to JBSERVER1\IN2014,
![Page 15: DISASTER RECOVERY USING ALWAYSON AVAILABILITY GROUP SCENARIO 1 · -> On SQL Server Instance JBSERVER2\IN2014, we will resume the data movement. -> On SQL Server Instance JBSERVER1\IN2014,](https://reader035.vdocuments.us/reader035/viewer/2022070100/6002363b3ef001632970f27c/html5/thumbnails/15.jpg)
![Page 16: DISASTER RECOVERY USING ALWAYSON AVAILABILITY GROUP SCENARIO 1 · -> On SQL Server Instance JBSERVER2\IN2014, we will resume the data movement. -> On SQL Server Instance JBSERVER1\IN2014,](https://reader035.vdocuments.us/reader035/viewer/2022070100/6002363b3ef001632970f27c/html5/thumbnails/16.jpg)
![Page 17: DISASTER RECOVERY USING ALWAYSON AVAILABILITY GROUP SCENARIO 1 · -> On SQL Server Instance JBSERVER2\IN2014, we will resume the data movement. -> On SQL Server Instance JBSERVER1\IN2014,](https://reader035.vdocuments.us/reader035/viewer/2022070100/6002363b3ef001632970f27c/html5/thumbnails/17.jpg)
-> Checking the row count for object Table5 on JBSERVER1\IN2014 after failback,
Thank You, Vivek Janakiraman
https://jbswiki.com/2017/08/03/disaster-recovery-using-alwayson-availability-group-scenario-1/
![Page 18: DISASTER RECOVERY USING ALWAYSON AVAILABILITY GROUP SCENARIO 1 · -> On SQL Server Instance JBSERVER2\IN2014, we will resume the data movement. -> On SQL Server Instance JBSERVER1\IN2014,](https://reader035.vdocuments.us/reader035/viewer/2022070100/6002363b3ef001632970f27c/html5/thumbnails/18.jpg)
DISASTER RECOVERY USING ALWAYSON AVAILABILITY GROUP –
SCENARIO 2 https://jbswiki.com/2017/08/03/disaster-recovery-using-alwayson-availability-group-scenario-2/
Environment
-> 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,
![Page 19: DISASTER RECOVERY USING ALWAYSON AVAILABILITY GROUP SCENARIO 1 · -> On SQL Server Instance JBSERVER2\IN2014, we will resume the data movement. -> On SQL Server Instance JBSERVER1\IN2014,](https://reader035.vdocuments.us/reader035/viewer/2022070100/6002363b3ef001632970f27c/html5/thumbnails/19.jpg)
-> 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,
![Page 20: DISASTER RECOVERY USING ALWAYSON AVAILABILITY GROUP SCENARIO 1 · -> On SQL Server Instance JBSERVER2\IN2014, we will resume the data movement. -> On SQL Server Instance JBSERVER1\IN2014,](https://reader035.vdocuments.us/reader035/viewer/2022070100/6002363b3ef001632970f27c/html5/thumbnails/20.jpg)
-> 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.
-> Implementing Force Quorum on JBSERVER2.
-> Checking the SQL Server Instance after Force Quorum,
![Page 21: DISASTER RECOVERY USING ALWAYSON AVAILABILITY GROUP SCENARIO 1 · -> On SQL Server Instance JBSERVER2\IN2014, we will resume the data movement. -> On SQL Server Instance JBSERVER1\IN2014,](https://reader035.vdocuments.us/reader035/viewer/2022070100/6002363b3ef001632970f27c/html5/thumbnails/21.jpg)
-> I will suspend the data movement now,
![Page 22: DISASTER RECOVERY USING ALWAYSON AVAILABILITY GROUP SCENARIO 1 · -> On SQL Server Instance JBSERVER2\IN2014, we will resume the data movement. -> On SQL Server Instance JBSERVER1\IN2014,](https://reader035.vdocuments.us/reader035/viewer/2022070100/6002363b3ef001632970f27c/html5/thumbnails/22.jpg)
-> Right-click the availability group to be failed over, and select Failover.
![Page 23: DISASTER RECOVERY USING ALWAYSON AVAILABILITY GROUP SCENARIO 1 · -> On SQL Server Instance JBSERVER2\IN2014, we will resume the data movement. -> On SQL Server Instance JBSERVER1\IN2014,](https://reader035.vdocuments.us/reader035/viewer/2022070100/6002363b3ef001632970f27c/html5/thumbnails/23.jpg)
![Page 24: DISASTER RECOVERY USING ALWAYSON AVAILABILITY GROUP SCENARIO 1 · -> On SQL Server Instance JBSERVER2\IN2014, we will resume the data movement. -> On SQL Server Instance JBSERVER1\IN2014,](https://reader035.vdocuments.us/reader035/viewer/2022070100/6002363b3ef001632970f27c/html5/thumbnails/24.jpg)
![Page 25: DISASTER RECOVERY USING ALWAYSON AVAILABILITY GROUP SCENARIO 1 · -> On SQL Server Instance JBSERVER2\IN2014, we will resume the data movement. -> On SQL Server Instance JBSERVER1\IN2014,](https://reader035.vdocuments.us/reader035/viewer/2022070100/6002363b3ef001632970f27c/html5/thumbnails/25.jpg)
-> Checking the SQL Server Instance after the failover with data loss.
-> 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
![Page 26: DISASTER RECOVERY USING ALWAYSON AVAILABILITY GROUP SCENARIO 1 · -> On SQL Server Instance JBSERVER2\IN2014, we will resume the data movement. -> On SQL Server Instance JBSERVER1\IN2014,](https://reader035.vdocuments.us/reader035/viewer/2022070100/6002363b3ef001632970f27c/html5/thumbnails/26.jpg)
-> The PRIMARY DATA CENTRE comes online. Database Server JBSERVER1 and File witness are online now. -> Connecting to JBSERVER1\IN2014 and Suspending the data movement,
![Page 27: DISASTER RECOVERY USING ALWAYSON AVAILABILITY GROUP SCENARIO 1 · -> On SQL Server Instance JBSERVER2\IN2014, we will resume the data movement. -> On SQL Server Instance JBSERVER1\IN2014,](https://reader035.vdocuments.us/reader035/viewer/2022070100/6002363b3ef001632970f27c/html5/thumbnails/27.jpg)
-> We will failback the Alwayson Availability group to JBSERVER1\IN2014,
![Page 28: DISASTER RECOVERY USING ALWAYSON AVAILABILITY GROUP SCENARIO 1 · -> On SQL Server Instance JBSERVER2\IN2014, we will resume the data movement. -> On SQL Server Instance JBSERVER1\IN2014,](https://reader035.vdocuments.us/reader035/viewer/2022070100/6002363b3ef001632970f27c/html5/thumbnails/28.jpg)
![Page 29: DISASTER RECOVERY USING ALWAYSON AVAILABILITY GROUP SCENARIO 1 · -> On SQL Server Instance JBSERVER2\IN2014, we will resume the data movement. -> On SQL Server Instance JBSERVER1\IN2014,](https://reader035.vdocuments.us/reader035/viewer/2022070100/6002363b3ef001632970f27c/html5/thumbnails/29.jpg)
![Page 30: DISASTER RECOVERY USING ALWAYSON AVAILABILITY GROUP SCENARIO 1 · -> On SQL Server Instance JBSERVER2\IN2014, we will resume the data movement. -> On SQL Server Instance JBSERVER1\IN2014,](https://reader035.vdocuments.us/reader035/viewer/2022070100/6002363b3ef001632970f27c/html5/thumbnails/30.jpg)
![Page 31: DISASTER RECOVERY USING ALWAYSON AVAILABILITY GROUP SCENARIO 1 · -> On SQL Server Instance JBSERVER2\IN2014, we will resume the data movement. -> On SQL Server Instance JBSERVER1\IN2014,](https://reader035.vdocuments.us/reader035/viewer/2022070100/6002363b3ef001632970f27c/html5/thumbnails/31.jpg)
-> On SQL Server Instance JBSERVER1\IN2014, we will resume the data movement.
![Page 32: DISASTER RECOVERY USING ALWAYSON AVAILABILITY GROUP SCENARIO 1 · -> On SQL Server Instance JBSERVER2\IN2014, we will resume the data movement. -> On SQL Server Instance JBSERVER1\IN2014,](https://reader035.vdocuments.us/reader035/viewer/2022070100/6002363b3ef001632970f27c/html5/thumbnails/32.jpg)
-> On SQL Server Instance JBSERVER2\IN2014, we will resume the data movement.
![Page 33: DISASTER RECOVERY USING ALWAYSON AVAILABILITY GROUP SCENARIO 1 · -> On SQL Server Instance JBSERVER2\IN2014, we will resume the data movement. -> On SQL Server Instance JBSERVER1\IN2014,](https://reader035.vdocuments.us/reader035/viewer/2022070100/6002363b3ef001632970f27c/html5/thumbnails/33.jpg)
-> Checking the SQL Server Instance now,
![Page 34: DISASTER RECOVERY USING ALWAYSON AVAILABILITY GROUP SCENARIO 1 · -> On SQL Server Instance JBSERVER2\IN2014, we will resume the data movement. -> On SQL Server Instance JBSERVER1\IN2014,](https://reader035.vdocuments.us/reader035/viewer/2022070100/6002363b3ef001632970f27c/html5/thumbnails/34.jpg)
-> Checking the row count for object Table5 on JBSERVER1\IN2014 after failback,