Primary’yi Yormayın: SQL Server Always On’da Read-Only Routing

SQL Server Always On Availability Groups (AG) mimarisinde, okuma (SELECT) yükünü primary replika üzerinden alıp secondary replikalara otomatik yönlendirmeyi sağlayan mekanizmaya Read-Only Routing denir.

Amaç nettir:

• Primary üzerindeki yükü azaltmak
• Raporlama ve okuma ağırlıklı sorguları secondary node’larda çalıştırmak
• Daha dengeli ve ölçeklenebilir bir mimari kurmak

Bu; özellikle raporlama, BI, dashboard ve read-heavy uygulamalar için kritik bir rol oynar.

Read-Only Routing Nasıl Çalışır?

Read-Only Routing, istemcinin niyeti üzerinden çalışır. Eğer bir bağlantı şu şekilde açılırsa:

ApplicationIntent=ReadOnly

SQL Server bu bağlantıyı Primary yerine, Read-only olarak tanımlanmış uygun bir Secondary replika üzerine yönlendirir.

⚠️ Eğer ApplicationIntent belirtilmezse bağlantı her zaman primary’ye gider. Bu yüzden çoğu ortamda Read-Only Routing “aktif sanılır” ama aslında hiç kullanılmaz.

Read-Only Routing İçin Kesinlikle Olmalı

✔ Always On Availability Group yapılandırılmış olmalı ✔ En az 1 secondary replika bulunmalı ✔ Secondary replica: Readable Secondary = Yes ✔ Listener tanımlı olmalı ✔ Client connection string içinde: ApplicationIntent=ReadOnly ✔ Read-only routing URL tanımlanmalı ✔ Routing list oluşturulmalı

Read-Only Routing İçin Kesinlikle Olmamalı

✘ Secondary üzerinde senkron modda ağır rapor çalıştırmak (log send queue etkilenebilir) ✘ Routing URL tanımlamadan routing list oluşturmak ✘ Tüm read trafiğini tek secondary’ye vermek ✘ ApplicationIntent olmadan test yapmak

Adım Adım Read-Only Routing Konfigürasyonu

Merve1 Primary Node, Merve2 Secondary Node durumundadır.

ADIM 1 : Secondary’yi Read-Intent Only Yapma Aşağıdaki komut ile secondary replikanın bağlantı davranışı READ_ONLY olarak ayarlanmıştır. Bu ayar sayesinde secondary node yalnızca ApplicationIntent=ReadOnly ile gelen bağlantıları kabul eder.

ALTER AVAILABILITY GROUP [AONTEST]
MODIFY REPLICA ON N'******01\MERVE2'
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

Yapılandırma öncesinde Read-Only Routing yapılandırması boş gözükmektedir. Adım adım yapılandırmayı gerçekleştirelim.

Adım 2 : Secondary Replika İçin Read-Only Routing URL Tanımlama

MERVE2 secondary replika için bir Read-Only Routing URL tanımlar. Yani SQL Server’a şunu söyler: “Read-intent bağlantı bu node’a yönlendirilecekse şu TCP adresini kullan.”

Neden gereklidir? Primary replika, read-only bir bağlantıyı secondary’ye yönlendireceği zaman: Hangi sunucuya? Hangi port üzerinden? gideceğini bu URL’den öğrenir. Eğer READ_ONLY_ROUTING_URL tanımlanmazsa:

⚠️ Routing list oluşturulsa bile yönlendirme çalışmaz.

ALTER AVAILABILITY GROUP [AONTEST]
MODIFY REPLICA ON N'*******01\MERVE2'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://*******01:1455'));

Adım 3 : Diğer Replika İçin Read-Only Routing URL Tanımlama

Bu komut da diğer replika (örneğin MERVE1) için routing URL tanımlar. Bu adım özellikle önemlidir çünkü: Failover durumunda roller değişir. Bugünkü primary yarın secondary olabilir. Bu yüzden her replika için READ_ONLY_ROUTING_URL tanımlamak en doğru yaklaşımdır.

ALTER AVAILABILITY GROUP [AONTEST]
MODIFY REPLICA ON N'******N\MERVE1'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://******N:1455'));

Adım 4 : Read-Only Routing List Tanımlama (Yönlendirme Kuralı) MERVE1 Primary iken Read-Only trafiği MERVE2’ye yönlendir. Ne yapar? MERVE1 primary rolündeyken, ApplicationIntent=ReadOnly ile gelen bağlantıları MERVE2’ye yönlendirecek şekilde kural tanımlar.

📌 Kısaca: Primary = MERVE1 → ReadOnly → MERVE2

ALTER AVAILABILITY GROUP [AONTEST]
MODIFY REPLICA ON N'******N\MERVE1'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = (N'*******01\MERVE2')));

Adım 5 : Read-Only Routing List Tanımlama (Yönlendirme Kuralı)

MERVE2 Primary olursa Read-Only trafiği MERVE1’e yönlendir (Failover senaryosu) Ne yapar? Failover sonrası MERVE2 primary olursa, Read-intent bağlantılar boşa düşmesin diye bu kez MERVE1’e yönlendirecek kuralı tanımlar.

📌 Kısaca: Primary = MERVE2 → ReadOnly → MERVE1

ALTER AVAILABILITY GROUP [AONTEST]
MODIFY REPLICA ON N'*********01\MERVE2'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = (N'********N\MERVE1')));

Gerçek Bir Senaryo İle Read-Only Routing

Primary Node-Merve1 üzerinde CPU artışına sebep olacak bir sorgu/rapor çalıştırıldığında sunucuda CPU değerinin %99 seviyesine kadar arttığı görülmektedir.

CPU %99 seviyesindeyken Primary Node-Merve1 üzerinde select sorgusu çalıştırdığımda sorgu 1ms de sonuç getirdi.

Aynı sorgu CPU normal seviyelerdeyken çalıştırıldığında 1 ms altında sonuç getirmektedir. Yukarıdaki durum CPU da yaşanan bu artışın diğer süreçleri olumsuz etkilediğini açıkça göstermektedir.

Bu sorun üzerine Read-Only Routing yapılandırması kuruldu.

Listener üzerinden ve ApplicationIntent=ReadOnly olarak bağlantı kuruldu ve CPU artışına sebep olan sorgu/rapor tekrar çalıştırıldı. Primary node üzerinde CPU artışı yaşanmadı.

Sonuç olarak, Always On mimarisinde Read-Only Routing yalnızca bir performans optimizasyonu değil, doğru tasarlanmış bir yük dağıtım stratejisidir. Okuma trafiğini secondary replikalara yönlendirerek primary node’u korumak; daha stabil, ölçeklenebilir ve sürdürülebilir bir veritabanı altyapısı kurmanın anahtarıdır. Doğru konfigürasyon, doğru connection string ve doğru senaryo ile uygulandığında, sistem performansındaki fark net bir şekilde gözlemlenebilir.