Migrating SQL Server with minimal downtime from On premise to Azure using DAG (Distributed Availability Group)

We have to migrate the SQL server from 2016 to 2019 with minimal downtime by configuring DAG. See the below process how to migrate with minimal downtime

I have a single standalone server having version SQL 2016 and need to migrate the server to 2019 version. The user is not giving downtime to migrate the server having multiple database more than 200GB each.


We have tried on lower version to migrate using DAG with minimal downtime.

1. We have to create different Cluster on both servers.

2. If we are migrating the server on Azure cloud then distributed availability groups are not allowed for DNN, SQL should not be DNN (DNN Listener).

creating windows cluster on both server, select the server those need to configure always on and do validations and give cluster name and IP


   
if  we are creating the WFCS on Azure cloud there will be no option to provide cluster IP, it will take ip of one of the server those are in always on. Hence we have to use script to create cluster using particular IP. In Azure it will work with DHCP protocol

if we want to create cluster with IP, instead windows cluster DNN, use below script on Azure VM

New-Cluster -Name ClusterName -Node Nodename -staticAddress IpAddress -NoStorage -AdministrativeAccessPoint ActiveDirecoryAndDns -ManagementPointNetworkType singleton


after creating the windows cluster it will be look as below image
after creating windows cluster on both nodes then check the box on the SQL services Windows Service failover cluster and restart SQL services.

We having single node to create always on hence we cann't create Always on with wizard. Next option is greyed out until two nodes will add
We have to create manually avaialability group on server 

after creating AG it will visible like below with node AG. Create same on other node. if we create AG manually we will not get permissions appropriately, we have to give manually on end points, we have to create  end points and provide permission on service account.

In Azure we have to create load balancer for listener







after creating load balancer we have to run the script and offline the resources and online again
Run below script after creating load balancer
WHEN WE CONFIGURE LOAD BALANCER FOR DAG, THEN WE HAVE TO ADD ANOTHER RULE FOR LOAD BALANCER ALLOWING 5022 PORT
The query should run on global primary server

CREATE AVAILABILITY GROUP [distributedag]  
   WITH (DISTRIBUTED)   
   AVAILABILITY GROUP ON  
      'ag1' WITH    
      (   
         LISTENER_URL = 'tcp://ag1-listener.contoso.com:5022'or'tcp://IPADDRESS:5022' ,    
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,   
         FAILOVER_MODE = MANUAL,   
         SEEDING_MODE = AUTOMATIC   
      ),   
      'ag2' WITH    
      (   
         LISTENER_URL = 'tcp://ag1-listener.contoso.com:5022'or'tcp://IPADDRESS:5022' ,   
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,   
         FAILOVER_MODE = MANUAL,   
         SEEDING_MODE = AUTOMATIC   
      );    
GO   

below query should run on Global forwarder
 
ALTER AVAILABILITY GROUP [distributedag]  
   JOIN   
   AVAILABILITY GROUP ON  
      'ag1' WITH    
      (   
         LISTENER_URL = 'tcp://ag1-listener.contoso.com:5022'or'tcp://IPADDRESS:5022' ,    
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,   
         FAILOVER_MODE = MANUAL,   
         SEEDING_MODE = AUTOMATIC   
      ),   
      'ag2' WITH    
      (   
         LISTENER_URL = 'tcp://ag1-listener.contoso.com:5022'or'tcp://IPADDRESS:5022' ,   
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,   
         FAILOVER_MODE = MANUAL,   
         SEEDING_MODE = AUTOMATIC   
      );    
GO 

After executing the above queries if databases are not added at Global forwarder run below query forwarder
ALTER AVAILABILITY GROUP [ag2] GRANT CREATE ANY DATABASE  

Follow the below process, if you want to failover on DAG

Modify availability mode of the Distributed Availability Group (Dist_AG01_AG02) in the primary Availability Group (AG01)
:CONNECT TDPRD071
--Modify availability mode of the primary and secondary AGs in a Distributed Availability Group
--Run this on the primary replica of the primary AG – TDPRD071
 ALTER AVAILABILITY GROUP [Dist_AG01_AG02]     
 MODIFY 
 AVAILABILITY GROUP ON
'AG01' WITH 
( 
   AVAILABILITY_MODE = SYNCHRONOUS_COMMIT 
), 
'AG02' WITH     
( 
   AVAILABILITY_MODE = SYNCHRONOUS_COMMIT 
);

Modify availability mode of the Distributed Availability Group (Dist_AG01_AG02) in the secondary Availability Group (AG02)
:CONNECT TDDR071
--Modify availability mode of the primary and secondary AGs in a Distributed Availability Group
--Run this on the primary replica of the secondary AG – TDDR071
ALTER AVAILABILITY GROUP [Dist_AG01_AG02]     
 MODIFY 
 AVAILABILITY GROUP ON
'AG01' WITH 
( 
   AVAILABILITY_MODE = SYNCHRONOUS_COMMIT 
), 
'AG02' WITH     
( 
   AVAILABILITY_MODE = SYNCHRONOUS_COMMIT 
);

Before proceeding with Step #3, verify that both the primary and secondary Availability Groups are in SYNCHRONOUS COMMIT and synchronization_health_desc value of HEALTHY. Use the queries below to verify.

:CONNECT TDPRD071
SELECT r.replica_server_name, r.endpoint_url,
rs.connected_state_desc, rs.role_desc, rs.operational_state_desc,
rs.recovery_health_desc,rs.synchronization_health_desc,
r.availability_mode_desc, r.failover_mode_desc
FROM sys.dm_hadr_availability_replica_states rs 
INNER JOIN sys.availability_replicas r
ON rs.replica_id=r.replica_id
ORDER BY r.replica_server_name
GO
SELECT ag.name, ag.is_distributed, ar.replica_server_name, ar.availability_mode_desc, ars.connected_state_desc, ars.role_desc, 
ars.operational_state_desc, ars.synchronization_health_desc FROM sys.availability_groups ag     
JOIN sys.availability_replicas ar on ag.group_id=ar.group_id
LEFT JOIN sys.dm_hadr_availability_replica_states ars
ON ars.replica_id=ar.replica_id
WHERE ag.is_distributed=1
GO
SELECT ag.name
       , drs.database_id
       , drs.group_id
       , drs.replica_id
       , drs.synchronization_state_desc
       , drs.end_of_log_lsn 
FROM sys.dm_hadr_database_replica_states drs,
     sys.availability_groups ag
WHERE drs.group_id = ag.group_id

The Distributed Availability Group is ready for failover when the last_hardened_lsn value for all of the databases on both Availability Groups are the same. Use the T-SQL query below to verify if both Availability Groups are ready for failover.
:CONNECT TDPRD071
 SELECT ag.name, 
        drs.database_id, 
        db_name(drs.database_id) as database_name,
        drs.group_id, 
        drs.replica_id,
        drs.last_hardened_lsn
 FROM sys.dm_hadr_database_replica_states drs
 INNER JOIN sys.availability_groups ag ON drs.group_id = ag.group_id; 
 
:CONNECT TDDR071
 SELECT ag.name, 
        drs.database_id, 
        db_name(drs.database_id) as database_name,
        drs.group_id, 
        drs.replica_id,
        drs.last_hardened_lsn
 FROM sys.dm_hadr_database_replica_states drs
 INNER JOIN sys.availability_groups ag ON drs.group_id = ag.group_id;

Set the Distributed Availability Group role on the primary Availability Group (AG01) to SECONDARY
:CONNECT TDPRD071
ALTER AVAILABILITY GROUP [Dist_AG01_AG02] SET (ROLE = SECONDARY);

 Failover the Distributed Availability Group to the secondary Availability Group (AG02)--We should run on primary of global forwarder
:CONNECT TDDR071
ALTER AVAILABILITY GROUP [Dist_AG01_AG02] FORCE_FAILOVER_ALLOW_DATA_LOSS;

if we have large size databases we have to restore on all nodes with norecovery and then we have to add in AG


















Comments

Post a Comment

Popular posts from this blog

Using PowerShell adding list of servers in CMS in SQL Server

  $path = get-location $servers = @(get-content "$path\servers.txt") { $q = use [msdb] insert into [dbo].[sysmanagement_shared_registered_servers_internal] (server_group_id,name,server_name,description,server_type) values ('45','$server','$server','','0'))" }  Invoke-Sqlcmd -serverInstance SQL2019 | -database "master" -query $q Note: Value 45 is not a default value it may vary according to server group we created in CMS. once we create a group in CMS this will save with on group id, we can see group id in "sysmanagement_shared_server_groups_internal"
Read more

Database Growth in % daily and Incremental

 USE [DailyDatabaseGrowthReport] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[DailyDatabaseGrowthReport]  AS BEGIN     SET NOCOUNT ON;     -- Check and create the table only if it does not exist     IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'DBSizeDailyReport')     BEGIN         CREATE TABLE [dbo].[DBSizeDailyReport](             [ServerName] nvarchar(100) NOT NULL,             [DbName] nvarchar(100) NOT NULL,             [SizeInMB] int NOT NULL,             [WeekID] int NOT NULL,             [Date] datetime NOT NULL,             [DayWisePercentageGrowth] decimal(18, 2),             [IncrementalPercentage] decimal(18, 2)         );      ...
Read more