Solution:
Execute the following query to failover from DPLPR to DPLHA:
:Connect DPLHA ALTER AVAILABILITY GROUP [DPLAG] FAILOVER;
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.
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.
Solution:
Execute the following query at DPLPR to add the
Customerdatabase to the DPLAG availability group:USE Master; ALTER AVAILABILITY GROUP DPLAG ADD DATABASE [Customer]; GO
Execute the following query to take a full backup of the
Customerdatabase at DPLPR:BACKUP DATABASE Customer TO DISK='C:\Code\Customer_FullBackup.bak' WITH INIT, STATS=10, COMPRESSION
Execute the following query at DPLHA and DPLDR to restore the full backup of the
Customerdatabase:USE [master] RESTORE DATABASE [Customer] FROM DISK = N'C:\Code\Customer_Fullbackup.bak' WITH FILE = 1, NOUNLOAD, STATS = 5, NORECOVERY
Execute the following query to take a log backup of the
Customerdatabase at DPLPR:BACKUP LOG Customer TO DISK='C:\Code\Customer_Logbackup.trn' WITH COMPRESSION, INIT, STATS=10
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
Execute the following query at DPLHA and DPLDR to join the
Customerdatabase 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.