Скачать или смотреть Seamless Database Migration using Distributed Availability Groups in SQL Server (Part 2)

Description: Dive into the game-changing technique of Distributed Availability Groups in SQL Server, revolutionizing database migrations between data centers having "Different Data and Log File Paths". Say goodbye to hours of downtime and hello to seamless platform switches in seconds, all with easy rollback options at your fingertips. Join me on SQLHELPLINE to unlock the power of agile database management. #SQLServer #DatabaseMigration #DistributedAvailabilityGroups #DataCenterMigration #DBA #SQLHELPLINE

:Connect SQL01
CREATE AVAILABILITY GROUP [SQLAG01]
FOR DATABASE DB01,DB02
REPLICA ON N'SQL01' WITH (ENDPOINT_URL = N'TCP://SQL01.SQLHELPLINE.LOCAL:5022',
FAILOVER_MODE = AUTOMATIC,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
BACKUP_PRIORITY = 50,
SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),
SEEDING_MODE = AUTOMATIC),
N'SQL02' WITH (ENDPOINT_URL = N'TCP://SQL02.SQLHELPLINE.LOCAL:5022',
FAILOVER_MODE = AUTOMATIC,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
BACKUP_PRIORITY = 50,
SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),
SEEDING_MODE = AUTOMATIC);
GO
/*Repeat the below 2 commands for all replica (shortened the script to fit in Description) */
:Connect SQL01
CREATE ENDPOINT [hadr_endpoint]
STATE=STARTED
AS TCP (LISTENER_PORT=5022)
FOR DATABASE_MIRRORING (ROLE=ALL);
GO
:Connect SQL01
ALTER ENDPOINT [hadr_endpoint]
AS TCP (LISTENER_IP = ALL)

:Connect SQL02
ALTER AVAILABILITY GROUP [SQLAG01] JOIN
ALTER AVAILABILITY GROUP [SQLAG01] GRANT CREATE ANY DATABASE
GO

:Connect SQL01
ALTER AVAILABILITY GROUP [SQLAG01]
ADD LISTENER N'SQLLSN01' (WITH IP((N'192.168.1.22', N'255.255.255.0')),PORT=1433);
GO


:Connect SQL03
CREATE AVAILABILITY GROUP [SQLAG02]
FOR
REPLICA ON N'SQL03' WITH (ENDPOINT_URL = N'TCP://SQL03.SQLHELPLINE.LOCAL:5022',
FAILOVER_MODE = MANUAL,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
BACKUP_PRIORITY = 50,
SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),
SEEDING_MODE = AUTOMATIC),
N'SQL04' WITH (ENDPOINT_URL = N'TCP://SQL04.SQLHELPLINE.LOCAL:5022',
FAILOVER_MODE = MANUAL,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
BACKUP_PRIORITY = 50,
SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),
SEEDING_MODE = AUTOMATIC);
GO


:Connect SQL04
ALTER AVAILABILITY GROUP [SQLAG02] JOIN
ALTER AVAILABILITY GROUP [SQLAG02] GRANT CREATE ANY DATABASE
GO
:Connect SQL03
ALTER AVAILABILITY GROUP [SQLAG02]
ADD LISTENER N'SQLLSN02' ( WITH IP ( (N'10.10.10.22',N'255.0.0.0') ) , PORT = 1433);
GO

:Connect SQL01
CREATE AVAILABILITY GROUP [DISTRIBUTED_AG]
WITH (DISTRIBUTED)
AVAILABILITY GROUP ON
'SQLAG01' WITH
(
LISTENER_URL = 'tcp://SQLLSN01.SQLHELPLINE.LOCAL:5022',
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL,
SEEDING_MODE = AUTOMATIC
),
'SQLAG02' WITH
(
LISTENER_URL = 'tcp://SQLLSN02.SQLHELPLINE.LOCAL:5022',
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL,
SEEDING_MODE = AUTOMATIC
);
GO

:Connect SQL03
ALTER AVAILABILITY GROUP [DISTRIBUTED_AG]
JOIN
AVAILABILITY GROUP ON
'SQLAG01' WITH
(
LISTENER_URL = 'tcp://SQLLSN01.SQLHELPLINE.LOCAL:5022',
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL,
SEEDING_MODE = AUTOMATIC
),
'SQLAG02' WITH
(
LISTENER_URL = 'tcp://SQLLSN02.SQLHELPLINE.LOCAL:5022',
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL,
SEEDING_MODE = AUTOMATIC
);
GO

:Connect SQL01
ALTER AVAILABILITY GROUP [DISTRIBUTED_AG]
MODIFY
AVAILABILITY GROUP ON
'SQLAG01' WITH
(
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT
),
'SQLAG02' WITH
(
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT
);
GO

:Connect SQL03
ALTER AVAILABILITY GROUP [DISTRIBUTED_AG]
MODIFY
AVAILABILITY GROUP ON
'SQLAG01' WITH
(
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT
),
'SQLAG02' WITH
(
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT
);

GO





:Connect SQL01
ALTER AVAILABILITY GROUP DISTRIBUTED_AG SET (ROLE = SECONDARY);





-- Run this query on the Global Primary and the forwarder
-- Check the results to see if the last_hardened_lsn is the same per database on both the global primary and forwarder
-- The availability group is ready to fail over when the last_hardened_lsn is the same for both availability groups per database
--
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;
GO


SELECT *
FROM sys.dm_hadr_availability_group_states


:Connect SQL03
ALTER AVAILABILITY GROUP DISTRIBUTED_AG FORCE_FAILOVER_ALLOW_DATA_LOSS;