Lesson 5: Managing AlwaysOn Availability Groups

Activity 5: Manual Failover

Solution:

1. Execute the following query to failover from DPLPR to DPLHA:

   :Connect DPLHA
   ALTER AVAILABILITY GROUP [DPLAG] FAILOVER;

2. Execute the following query on DPLHA to verify that the failover is complete:

   SELECT 
     ag.name AS AvailabilityGroup,
     ar.replica_server_name AS ReplicaName,
     ars.role_desc AS Role,
     ars.operational_state_desc
   FROM 
   sys.availability_groups ag join sys.availability_replicas ar 
   on ag.group_id=ar.group_id
   join sys.dm_hadr_availability_replica_states ars 
   on ar.replica_id=ars.replica_id

   The Role column for the DPLHA replica should state Primary.

3. To fall back to DPLPR, execute the following query:

   :Connect DPLPR
   ALTER AVAILABILITY GROUP [DPLAG] FAILOVER;

   To verify the failover, execute the query from step 2 once again. The Role column for the DPLPR replica should state Primary.

Activity 6: Adding a New Database to an Existing Availability Group

Solution:

1. Execute the following query at DPLPR to add the Customer database to the DPLAG availability group:

   USE Master;
   ALTER AVAILABILITY GROUP DPLAG ADD DATABASE [Customer];  
   GO

2. Execute the following query to take a full backup of the Customer database at DPLPR:

   BACKUP DATABASE Customer TO DISK='C:\Code\Customer_FullBackup.bak' WITH INIT, STATS=10, COMPRESSION

3. Execute the following query at DPLHA and DPLDR to restore the full backup of the Customer database:

   USE [master]
   RESTORE DATABASE [Customer] FROM  DISK = N'C:\Code\Customer_Fullbackup.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 5, NORECOVERY

4. Execute the following query to take a log backup of the Customer database at DPLPR:

   BACKUP LOG Customer TO DISK='C:\Code\Customer_Logbackup.trn' WITH COMPRESSION, INIT, STATS=10

5. Execute the following query to restore the log backup at DPLHA and DPLDR:

   RESTORE LOG Customer FROM DISK='C:\Code\Customer_LogBackup.trn' WITH NORECOVERY, STATS=10

6. Execute the following query at DPLHA and DPLDR to join the Customer database to the DPLAG availability group:

   USE Master;
   ALTER DATABASE Customer SET HADR AVAILABILITY GROUP = DPLAG;

You can use the AlwaysOn dashboard to verify that the database was successfully added to the availability group.

You can also use SSMS Object Explorer to add the database to the availability group. However, you'd have to perform the backup and restore manually.