Mssql Server AlwaysOn Availability Groups Yapısında Kullanılan Scriptler

Bu makalede mssql server Alwayson yapısında kullanılan sciptleri görmüş olacağız. Öncelikle herhangi bir SSMS donması ve sıkıntı anında SSMS arayüzünü kullanmak istemeye biliriz. Bunun için aşağıdaki scriptler kullanılır.

AlwaysOn Availability Groups Özelliğini Etkinleştirme bu işlem sql configuration manager ekranından ilgili sql server properties bölümünden yapılmaktadır.

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'hadr enabled', 1;
RECONFIGURE;
GO

Primary replica üzerinde End point oluşturmak için aşağıdaki komut kullanılmaktadır. Aynı instance altında tüm Ag’ler aynı end point yapısını kullanmaktadır. Eğer farklı bir instance altında endpoint oluşturmak isterseniz farklı bir endpoint number girilmesi gerekmektedir. Always on yapısı bu hadr endpoint üzerinden haberleşmektedir.

CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_PORT = 5022)
    FOR DATA_MIRRORING (
        ROLE = ALL,
        AUTHENTICATION = WINDOWS NEGOTIATE,
        ENCRYPTION = REQUIRED ALGORITHM AES
    );
GO

İlgili end point yapısının başlatılması için aşağıdaki komut kullanılmaktadır.

ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;

End point izinlerinin ayarlanması gerekmektedir.

GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [DOMAIN\SQLServiceAccount];

Aşağıdaki script ile availability group oluşturabiliriz. Group oluşturduktan sonra primary altında availability replicas kısmında secondary sunucusunun join edilmesi gerekmektedir. Yoksa secondary sunucusu üzerinde ilgili availability group oluşturulmaz.

-- Availability Group oluşturma
CREATE AVAILABILITY GROUP [AG_TEST]
WITH (
    AUTOMATED_BACKUP_PREFERENCE = PRIMARY,
    FAILURE_CONDITION_LEVEL = 3,
    HEALTH_CHECK_TIMEOUT = 30000
)
FOR 
REPLICA ON
    N'PRIMARY_INSTANCE_NAME' WITH (
        ENDPOINT_URL = N'TCP://SERVERNAME1.yunusyucel.com.tr:5022',
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        FAILOVER_MODE = AUTOMATIC,
        BACKUP_PRIORITY = 50,
        SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),
        PRIMARY_ROLE(ALLOW_CONNECTIONS = READ_WRITE),
        SESSION_TIMEOUT = 10
    ),
    N'SECONDARY_INSTANCE_NAME' WITH (
        ENDPOINT_URL = N'TCP://SERVERNAME2.yunusyucel.com.tr:5022',
        AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
        FAILOVER_MODE = MANUAL,
        BACKUP_PRIORITY = 50,
        SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),
        PRIMARY_ROLE(ALLOW_CONNECTIONS = READ_WRITE),
        SESSION_TIMEOUT = 10
    );
GO

Sql Server Alwayson yapısına yeni bir veritabanı eklemek için aşağıdaki script kullanılmaktadır. Bunun için ilk başta AG yapımızın bir veritabanı oluşturma yetkisi verilmesi gerekmektedir.

ALTER AVAILABILITY GROUP [AG_TEST] GRANT CREATE ANY DATABASE;

Aşağıdaki komut ile ilgili AG altına veritabanını ekleyebiliriz. Bu veritabanları auto seeding yapısında eklenmektedir.

ALTER AVAILABILITY GROUP [AG_TEST]
ADD DATABASE [TEST3];
-- Birden fazla veritabanı ekleme

ALTER AVAILABILITY GROUP [AG_TEST]
ADD DATABASE [TEST1], [TEST2], [TEST3];
GO

Yukarıdaki veritabanını primary AG altına ekledikten sonra secondary sunucusunda otomatik olarak oluşturulmaz. Sebebi ise secondary sunucusunda bulunan AG yapımızın veritabanı oluşturma yetkisinin olmamasıdır. İlgili komut secondary sunucusu üzerinde çalıştırılması gerekmektedir. Bu script çalıştırıldıktan sonra veritabanı primary üzerinden ekleneceği zaman availabilty properties üzerinden seeding mode automatic ise veritabanı secondary sunucusu üzerinde replica olmaktadır. Tabi senkron olması veritabanınızın boyutuna bağlıdır.

ALTER AVAILABILITY GROUP [AG_TEST] GRANT CREATE ANY DATABASE;

Aşağıdaki script ile veritabanı üzerinde bulunan automatic seeding modunu değiştirebiliriz.

-- Automatic seeding modunu etkinleştirme
ALTER AVAILABILITY GROUP [AG_TEST]
MODIFY REPLICA ON N'ServerName\InstanceName' 
WITH (SEEDING_MODE = AUTOMATIC);
GO

-- Manuel seeding moduna geçiş
ALTER AVAILABILITY GROUP [AG_TEST]
MODIFY REPLICA ON N'ServerName\InstanceName' 
WITH (SEEDING_MODE = MANUAL);
GO

AG üzerinde bulunan failover mode’a manuel veya otomatik olarak geçiş yapılabilir. İlk olarak replicalar üzerinde failovor modu aşağıdaki script ile öğrenebiliriz.

SELECT top 2
    replica_server_name,
    availability_mode_desc,
    failover_mode_desc,
    session_timeout
FROM sys.availability_replicas;

Aşağıdaki scriptler ile replicalar üzerinde failover mode değiştirilebilir. Eğer failover mode değiştirilecek replicalar üzerinde availability mode uyumsuzluğu varsa hata alacaksınız. Örnek vermek gerekirse availability mode asyncronous ise failover mode otomatik yapıya geçmez hata vermektedir.

-- Automatic failover moduna geçiş
ALTER AVAILABILITY GROUP [AG_TEST]
MODIFY REPLICA ON N'ServerName\InstanceName'
WITH (FAILOVER_MODE = MANUAL);
GO

-- Manual failover moduna geçiş
ALTER AVAILABILITY GROUP [AG_TEST]
MODIFY REPLICA ON N'ServerName\InstanceName'
WITH (FAILOVER_MODE = AUTOMATIC);
GO

Availability group üzerinde senkronizasyon modu değiştirme için aşağıdaki komutlar kullanılmaktadır.

-- Synchronous commit moduna geçiş
ALTER AVAILABILITY GROUP [AG_TEST]
MODIFY REPLICA ON N'ServerName\InstanceName'
WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);
GO

-- Asynchronous commit moduna geçiş
ALTER AVAILABILITY GROUP [AG_TEST]
MODIFY REPLICA ON N'ServerName\InstanceName'
WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT);
GO

Senkronizasyon durumlarını kontrol etmek için aşağıdaki komutun çalıştırılması gerekmektedir.

SELECT top 2
    replica_server_name,
    availability_mode_desc,
    failover_mode_desc,
    session_timeout
FROM sys.availability_replicas;

Hangi sunucunun primary olduğunu anlamak için aşağıdaki komut kullanılmaktadır.

SELECT
    ar.replica_server_name,
    ars.role_desc,
    ars.connected_state_desc
FROM sys.dm_hadr_availability_replica_states ars
INNER JOIN sys.availability_replicas ar
ON ars.replica_id = ar.replica_id
WHERE ars.role_desc = 'PRIMARY';

Secondary replicayı kopartmamız gerekiyorsa primary olan sunucu üzerinde çalıştırılması gerekmektedir.

ALTER AVAILABILITY GROUP [AG_TEST] REMOVE REPLICA ON 'ServerName\InstanceName'

AG altında bulunan herhangi bir veritabanını aşağıdaki komut ile availability group altından çıkarabilirsiniz.

ALTER AVAILABILITY GROUP [AG_TEST] REMOVE DATABASE TEST3;

Aşağıdaki komut ile tekrardan ekleme işlemi yapılabilir.

ALTER AVAILABILITY GROUP [AG_TEST] ADD DATABASE [TEST3];

Aşağıdaki komut ile availability altında bulunan bir veritabanını durdurma işlemi yapılabilir.

ALTER DATABASE [TEST3] SET HADR SUSPEND;

Aşağıdaki komut ile AG altında bulunan veritabanını ve diğer Nodelarda durumunu gösteren komut verilmiştir.

SELECT 
    ag.name AS [AvailabilityGroup], 
    d.name AS [DatabaseName], 
    ar.replica_server_name AS [Node Name], 
    drs.synchronization_state_desc AS [Sync State], 
    drs.is_commit_participant AS [Is Commit Participant], 
    ar.failover_mode_desc AS [Failover Mode]
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.availability_groups ag ON drs.group_id = ag.group_id
JOIN sys.availability_replicas ar ON drs.replica_id = ar.replica_id
JOIN sys.databases d ON drs.database_id = d.database_id
ORDER BY d.name, ag.name, ar.replica_server_name;

Aşağıdaki script yardımıyla Secondary sunucunun primary sunucuya göre ne kadar geriden geldiğini bulabilirsiniz. 1 saat üzerinden fazla olan veritabanlarıyla ilgili kayıt dönmektedir. Aşağıdaki dönen ilgili veritabanları yukarıdaki komut ile kordineli çalıştırdığımızda mantık anlaşılıyor.

SELECT DB_NAME(drs.database_id) AS Database_Name
      ,drs.last_commit_time AS Primary_Commit
         ,drs2.last_commit_time AS Secondary_Commit
         ,CONCAT(DATEDIFF(second,drs2.last_commit_time,drs.last_commit_time)/3600 ,' Saat'
         ,(DATEDIFF(second,drs2.last_commit_time,drs.last_commit_time)%3600)/60 ,' Dakika'
         ,DATEDIFF(second,drs2.last_commit_time,drs.last_commit_time)%60 ,' Saniye') AS Senkronizasyon_Farki
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.dm_hadr_database_replica_states drs2 ON drs.database_id=drs2.database_id
WHERE drs.is_local=1 AND drs2.is_local=0 AND DATEDIFF(second,drs2.last_commit_time,drs.last_commit_time)>3600
ORDER BY DATEDIFF(second,drs2.last_commit_time,drs.last_commit_time) DESC

Aşağıdaki kod bloğunda AlwaysOn yapısındaki herhangi bir veritabanının primary ve secondary sunucusundaki senkron durumlarını görebiliriz. Aşağıdaki çalıştırdığım ikinci komutla senkron bir şekilde görülebilir.

SELECT
    ar.replica_server_name,
    adc.database_name,
    ag.name AS ag_name,
    dhdrs.synchronization_state_desc,
    dhdrs.is_commit_participant,
    dhdrs.last_sent_lsn,
    dhdrs.last_sent_time,
    dhdrs.last_received_lsn,
    dhdrs.last_hardened_lsn,
    dhdrs.last_redone_time
FROM sys.dm_hadr_database_replica_states AS dhdrs
INNER JOIN sys.availability_databases_cluster AS adc
    ON dhdrs.group_id = adc.group_id AND
    dhdrs.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups AS ag
    ON ag.group_id = dhdrs.group_id
INNER JOIN sys.availability_replicas AS ar
    ON dhdrs.group_id = ar.group_id AND
    dhdrs.replica_id = ar.replica_id
where database_name='DB_NAME'

Eğer AG yapımız resolving moduna düşerse aşağıdaki komut ile tekrar aynı sunucuyu primary sunucusu olarak ayağa kaldırabiliriz. Primary olmasını istediğimiz sunucu üzerinde komut çalıştırılır. Aşağıdaki kodumuzun çalışması için ilgili AG yapımızın resolving modunda olması gerekmektedir.

ALTER AVAILABILITY GROUP [AG_TEST] FORCE_FAILOVER_ALLOW_DATA_LOSS;

Not: Aşağıdaki komut ile alwayson yapımızda failover işlemi yapmadan önce görmemiz gereken ifadelerden birisi olarak karşımıza çıkar.

Select name,S.log_reuse_wait_desc from sys.databases s
--where log_reuse_wait_desc !='NOTHING'

Yukarıdaki komut ile DB10 database’i primary sunucusuna dahil edilir. Aşağıdaki resimde ise secondary sunucusunda senkron olduğunu gözlemlemiş oluruz. Bunun sebebi AG properties ekranından iki replicamızın seeding modunun otomatik olmasından dolayıdır.

Bu makalede mssql server üzerinde bulunan Always on availability group üzerinde bulunan tüm script komutlarını görmüş olduk.

Başka makalede görüşmek dileğiyle..

Kişinin önünde ve arkasında Allah’ın emriyle onu kayıt ve koruma altına alan takipçiler vardır. Bir toplum kendisindekini değiştirmedikçe Allah onlarda bulunanı değiştirmez. Allah herhangi bir toplumun başına bir kötülük gelmesini diledi mi, artık onun geri çevrilmesi mümkün değildir. Onların Allah’tan başka yardımcıları da bulunmaz. Rad-11

Bir yanıt yazın