AlwaysOn Availability Group’a yeni bir database eklerken Automatic Seeding ile database taşıma opsiyonu seçildiğinde, database taşıma işleminin ilerleyişini takip edebileceğimiz bir script yazdım.
Buraya tıklayarak Github üzerinden erişebilirsiniz.
/*----------------------------------------------------------------------- Written by Yunus UYANIK, yunusuyanik.com Version 1.0 Date : 28.04.2020 (c) 2020, yunusuyanik.com. All rights reserved. ------------------------------------------------------------------------*/ SELECT database_size_bytes/1024/1024 AS TotalDBSize_MB, transferred_size_bytes/1024/1024 AS TotalTransferSize_MB, database_size_bytes/1024/1024-transferred_size_bytes/1024/1024 AS TotalTransferRemainingSize_MB, (DATEPART(HOUR, DATEADD(HOUR,-3,GETDATE())-has.start_time) * 3600) + (DATEPART(MINUTE, DATEADD(HOUR,-3,GETDATE())-has.start_time) * 60) + (DATEPART(SECOND, DATEADD(HOUR,-3,GETDATE())-has.start_time)) AS TotalTransferSeconds, ((CONVERT(DECIMAL(18,2),transferred_size_bytes/1024/1024))/(CONVERT(DECIMAL(18,2),database_size_bytes/1024/1024)))*100 AS TransferingPercent, (database_size_bytes/1024/1024-transferred_size_bytes/1024/1024)/( (transferred_size_bytes/1024/1024)/ ((DATEPART(HOUR, DATEADD(HOUR,-3,GETDATE())-has.start_time) * 3600) + (DATEPART(MINUTE, DATEADD(HOUR,-3,GETDATE())-has.start_time) * 60) + (DATEPART(SECOND, DATEADD(HOUR,-3,GETDATE())-has.start_time)))) AS TransferingRemainingSeconds, DATEADD(HOUR,-3,GETDATE())-has.start_time AS TotalTransferTime FROM sys.dm_hadr_physical_seeding_stats hpss JOIN sys.dm_hadr_automatic_seeding has ON has.operation_id=hpss.remote_physical_seeding_id
NOT : sys.dm_hadr_automatic_seeding dmv’si üzerinde ilgili aktarımın start_time kolonu, aktarımı başlattığınız zamanınkinden (sunucu saatine göre) farklı olabiliyor. Bu durumda GETDATE() fonksiyonuna saat ekleme çıkarma yaparak kontrolleri sağlayabiliyoruz.