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...