MSSQL Server AlwaysOn Read-Only Routing ve Readable Secondary  Yapısı

Bu makalede AlwaysOn Read-Only Routing ve Readable Secondary   işlemini ele alacağız. Bu işlem raporlama ve select işlemlerinde primary makinamızı yormadan raporlama ve okuma işlemlerinin ikinci makinaya yönlendirilmesi işlemidir.

Bu sayede sunucumu gereksiz bir şekilde yormayacağız ve yük devretme işlemini başarılı bir şekilde yapmış olacağız. İlk başta AG üzerine sağ tıklanır. Properties ekranından konfigürasyon yapacağımız ekrana geliriz.

SQL Server Always On Availability Groups (AG) özelliklerinden biri olan Readable Secondary ayarı, Secondary Replica’ların okunabilir olup olmadığını belirler. AG Properties bölümünde “Readable Secondary” için üç seçenek bulunur:

Bu kısımda 3 seçenek karşımıza çıkmaktadır. Bunlar NO,YES ve READ-INTENT ONLY seçenekleridir.

NO: Secondary Replica’ları yalnızca yedekleme veya felaket kurtarma (DR) amaçlı kullanmak istiyorsanız, bu seçeneği kullanabilirsiniz.

YES: Secondary Replica’ya doğrudan bağlanıp okuma amaçlı sorgular çalıştırabilirsiniz. ApplicationIntent=ReadOnly kullanmadan bile bağlantı kurulabilir.Genellikle raporlama ve analiz amaçlı kullanılır. Eğer read-heavy (okuma ağırlıklı) işlemleri Secondary Replica’ya yönlendirmek istiyorsanız, bu ayarı açabilirsiniz.

READ INTENT ONLY: Bu seçenek, sadece “ApplicationIntent=ReadOnly” bağlantıları için Secondary Replica’ya erişimi açar. Eğer bir istemci doğrudan bağlanmaya çalışırsa bağlantı reddedilir. Yani, bağlantının “Read-Only Routing” üzerinden gelmesi gerekir. Eğer okuma sorgularını kontrollü bir şekilde yönlendirmek istiyorsanız, bu en iyi seçenektir. Primary Replica’ya yazma yükü bindirmeden okuma trafiğini Secondary Replica’ya dağıtabilirsiniz. Bu işlemin Yes olarak seçilmesindede herhangi bir sakınca yoktur.

Aşağıdaki ekran görüntüsündede read-intent only seçeneğini seçtikten sonra database üzerinde  bir işlem yapılamadığınıda görmüş oluyoruz. Çünkü bu seçeneğin sadece ApplicationIntent=ReadOnly ile bağlantı sağlayacağını dile getirmiştik. Ama  Readable Secondary seçeneğini yes deseydik secondary database’e bağlandığımızda tabloları veya diğer ayarları da görmüş olacaktık. Yes değerini verip gözlemleyelim.

Secondary sunucusunda veritabanımızın açıldığı görülmektedir.

Not: Availability Mode asynchronuos failover edilecek sunucuda database’ler ikinci sunucuda otomatik senkron olmaz. Failover olunan AG altında  tüm databaseler aktif edilmelidir. Yeni primary sunucusunda resume edilmelidir. Çünkü veri kaybı ihtimali olduğu için son kullanıcıya sormaktadır.

İki sunucumuzda Read-intent only  seçeneğini seçmiş olduk çünkü primary olan secondary  veya secondary sunucu primary olabilir. ilerde sorun yaşamamak için.

Bu durum seçildikten sonra SSMS üzerinden veritabanımızın açılmadığını görmüş oluyoruz.

Ok deyip bu değişikliği yaptıktan sonra tekrardan AlwaysOn AG’mizin üzerine gelip sağ tıklayıp properties ekranını açıyoruz.

Gelen ekranda Read-Only Routing bölümene gelip gerekli olacak konfigürasyonları yapıyoruz.

Yukarıdaki ekranda S1\TEST olan yerin karşısına read only URL kısmına sunucumuzun URL’ini yazıyorum bunu da General kısmında öğrenebiliriz.

Bu URL’leri alıp  read only routing bölümüne yapıştırıyorum sadece farklı olacak kısım Endpoint URL son kısmında bulunan  endpoint port numarası değil’de Sql Server TCP Port numarasını  yazacağız.

İlgili bilgileri aldıktan sonra Read-Only Routing bölümüne yapıştırıyorum.

Read-Only Routing kısmına yazdıktan sonra sol alt tarafta Available replicas kısmına sunucularımız geliyor. Burada yapmamız gereken yukarıda Server İnstance’da ilgili sunucuyu seçip aşağıda bulunan available replicas kısmından read only routing olacak sunucuyu seçme işlemini yapmamız.

NOT: URL yazılmadan Available replicas kısmına sunucu isimleri gelmez. Boş görünür. Öncelikle yazılması gerekmektedir.

Add deyip S1\TEST primary’se read only routing işlemi yapacağı sunucu S2 \TEST sunucusu olmuş olacak.

Daha sonra S2\TEST sunucusu primary olursa S1\TEST sunucusu Read-Only Routing List kısmına almamız gerekiyor.

Add dedikten sonra işlemlerimi tamamlıyorum ve OK tuşuna basıyorum. Aşağıdaki ekran resmi şu ifadeye karşılık gelmektedir. Kullanıcı listener üzerinden gerekli parametreleri sağladıktan sonra bağlantı gerçekleştirirse. Kullanıcı ilk olarak primary(S1\TEST) sunucuya gelir. Daha sonra read-only routing ifadesi ile bağlantıyı secondary sunucuya yapmayı dener. Bu bağlantıyı ise TCP://S2.yunusyucel.com:1433 url üzerinden gerçekleştirilir.

Daha detaylı açıklamak gerekirse:

1. Kullanıcı bağlantısı:
   • Kullanıcı, ApplicationIntent=ReadOnly parametresiyle bağlanırsa, listener önce bu isteği mevcut primary (S1\TEST) sunucusuna iletir.
2. Read-only yönlendirme:
   • Primary sunucu (S1\TEST), bu isteğin read-only olduğunu görür ve kendisine tanımlı read-only routing listesine bakar.
   • Tabloya göre, S1\TEST’in read-only yedek sunucusu S2\TEST olarak ayarlanmıştır.
   • Bu nedenle, bağlantı TCP://S2.yunusyucel.com:1433 üzerinden S2\TEST‘e yönlendirilir.
3. Secondary sunucuya erişim:
   • Eğer S2\TEST erişilebilir durumdaysa, kullanıcının sorguları bu sunucuda çalıştırılır.
   • Eğer S2\TEST ulaşılamazsa, bağlantı hatası alınır (çünkü alternatif bir read-only sunucu tanımlanmamış).

NOT: Bağlantı yapılacak sunucudan hedef sunuculara erişim yetkisi olması gerekmektedir. İlgili yetkinin hedef sunucularda bulunan sql server portona bağlanıyor olunması gerekmektedir. Belirtilen url’e yetki verilmesi gerekmektedir.

S2\test sunucusunda read only routing url kısmı null değeri geliyordu. Aşağıdaki komut ile bunu öğrenmiş oldum.

SELECT replica_server_name
       , read_only_routing_url
       , secondary_role_allow_connections_desc
FROM sys.availability_replicas

Konfigürasyonu yaptıktan sonra OK deyip işlemi tamamlıyorum.

Yukarıdaki arayüzün cerate script’i;

USE [master]
GO
ALTER AVAILABILITY GROUP [SQLAlways]
MODIFY REPLICA ON N'S1\TEST' WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL = N'TCP://S1.yunusyucel.com:1433'))
GO
---------------------
---------------------
USE [master]
GO
ALTER AVAILABILITY GROUP [SQLAlways]
MODIFY REPLICA ON N'S2\TEST' WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL = N'TCP://S2.yunusyucel.com:1433'))
GO

Aşağıdaki şekilde yönlendirme işlemi yapılırsa 1. sunucu kullanılmıyorsa 2. sunucu kullanılır. Bu şekilde sıralı bir yönlendirme işlemi yapılabilir.

Not: Eğer Read-Only Routing List kısmında 3 node varsa iki node parantez içerisine alınırsa önceliği o nodlarda kullanmaktadır.

Yukarıdaki ekran resminin create script’i:

USE [master]
GO
ALTER AVAILABILITY GROUP [AGG]
MODIFY REPLICA ON N'S1\TEST23' WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST = (N'S2\TEST23',N'S1\TEST23')))
GO
USE [master]
GO
ALTER AVAILABILITY GROUP [AGG]
MODIFY REPLICA ON N'S2\TEST23' WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST = (N'S1\TEST23',N'S2\TEST23')))
GO

Başarılı bir şeklide oluşmuş oldu. Şimdi gelelim test işlemine.

Eğer uygulama bazı noktalarda secondary sunucudan read yapacaksa veritabanına bağlanmak için, sadece read yapacak sorguların kullanacağı connection string’in sonuna “ApplicationIntent=ReadOnly” ifadesini eklemelisiniz. Eğer SSMS üzerinden bağlanacaksa öncelikle Options’a tıklayarak Connect To Database kısmına erişeceğiniz veritabanı ismini, Additional Connection Parameters kısmına da “ApplicationIntent=ReadOnly” ifadesini eklemelisiniz. Bu şekilde secondary sunucu o anda hangi node ise read only connection’lar oraya yönlenecektir. Bu şekilde select yükünü dağıtabilir ya da rapor sorgularınızı secondary sunucunuza yönlendirebilirsiniz.

SQL Server 2016 ile beraber read işlemi secondary sunuculara load balancing mantığında dağıtılarak yapılabilir.

İlk başta  SQL Server’ıma listener ismiyle bağlanıyorum bağlanmadan önce options kısmından bazı parametreler girmem gerekiyor. İlgili listener port numarasınında yazılması gerekmektedir.

Additional Connection Parameters kısmına aşağıdaki parametreyi yazıyorum. ApplicationIntent=ReadOnly;

Daha sonra üste bulunan Connection Properties kısmında sorgu çekeceğimiz database’i seçmeliyiz. İlgili veritabanının kullanıcı adı ve şifresi girilirse daha doğru bir sonuçla karşılaşmış oluruz.

Bağlantı sağladıktan sonra Begin Tran komutuyla açık olan bağlantının secondary sunucusuna gittiğini görmekteyiz.

Aşağıdaki komut ile bağlantımızın secondary veya primary sunucusuna gittiğini görebiliriz.

SELECT SERVERPROPERTY('IsHadrEnabled') AS IsHadrEnabled,
       SERVERPROPERTY('HadrManagerStatus') AS HadrStatus,
       SERVERPROPERTY('IsClustered') AS IsClustered,
       SERVERPROPERTY('ServerName') AS ServerName;

Ayrıca cmd komut satırıylada bu işlem yapılabilir.

sqlcmd -S LISTENER_Name  -E -KReadOnly

GENEL OLARAK YAPILAN İŞLEMLERİN SCRİPTİ AŞAĞIDAKİ GİBİDİR.

ALTER AVAILABILITY GROUP AGG
MODIFY REPLICA ON
N'S1\TEST23' WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)); --READ INTENT ONLY YAPAR

ALTER AVAILABILITY GROUP AGG
MODIFY REPLICA ON
N'S1\TEST23' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://S1.yunusyucel.com:1433'));

ALTER AVAILABILITY GROUP AGG
MODIFY REPLICA ON
N'S2\TEST23' WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

ALTER AVAILABILITY GROUP AGG
MODIFY REPLICA ON
N'S2\TEST23' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://S2.yunusyucel.com:1433'));


ALTER AVAILABILITY GROUP AGG
MODIFY REPLICA ON
N'S1\TEST23' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('S2\TEST23')))); --İkinci replica olacak sunucu yazılabilir.

ALTER AVAILABILITY GROUP AGG
MODIFY REPLICA ON
N'S2\TEST23' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('S1\TEST23'))));

Not: İnstance altında yeni bir AG eklenirse kullanıcılar linked server üzerinden yeni AG yapımızda bulunan veritabanına bağlanamadığını dile getirirse bu readable read ifadesinin kapalı olmasından dolayıdır. Ayrıca yeni kurulan AG ilk AG’nin read only routing konfigürasyonunu kullanırlar. Secondary bilgisini ilk AG den aldıktan sonra kendi üzerinde bulunan veritabanı okumaya kapalı olduğu için okuyamaz.

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

Nefsini maddî ve mânevî kirlerden temizleyen kesinlikle kurtuluşa erecektir. (Şems suresi, 9. ayet)