SQL AlwaysOn AG Read-Only Routing ile Raporlama İşlemlerini Hızlandırma

Kurumsal şirketlerin bünyelerinde barındırdığı ERP, CRM yazılımları ve diğer bütün iç yazılımlar için raporlama olmazsa olmazdır. Firmanın öz kaynakları ve bunların nasıl kullanıldığından sürekli haberdar olmak isteyen patron, yönetici ve dış paydaşlar söz konusu yazılımların raporlama modülleri üzerinden bu bilgileri basit, hızlı ve tek bakışta öğrenmek isterler; bir de bu işlemlerin gerçek zamanlı ve hızlı olmasını isterler. PowerBI gibi iş zekâsı tabanlı veri raporlama ve analiz araçları ve yazılım modüllerinin yaptığı iş aslında arkada bulunan veri kaynaklarından veriyi alıp pratik ve hızlı şekilde görselleştirmektir. İşte buradaki “hızlı” kısmının büyük bir kısmı verinin geldiği kaynağın bu isteklere hızlıca cevap vermesinden geçmektedir.

İster kurum içinde terzi usulü geliştirilmiş uygulamalar olsun, ister hazır yazılım setleri veya servisler olsun, veriler ilişkisel veya ilişkisel olmayan veri tabanlarında tutulmak durumundadır. Veri tabanı motorları bir yandan programı aktif olarak kullanan kişilerin yeni girdiler oluşturma, mevcut girdileri değiştirme/silme gibi isteklerine yanıt vermek gibi “kritik” ve “öncelikli” isteklere yanıt vermenin dışında bir de yöneticilerin ve dış paydaşlarının her 5 dakikada bir “yenile” butonuna basarak güncel verileri rapor panosuna getirme isteklerine de yanıt vermek durumundadır. Aynı anda yüzlerce belki de binlerce kritik isteğe cevap verirken bir yandan da raporlama gibi mevcut veriyi çekmeye yönelik işlemlere yanıt vermeye çalışmak çoğu zaman darboğazlara sebep olmaktadır. Özellikle de SQL Server gibi ilişkisel veri tabanlarında mutlak verinin tek bir sunucu üzerinde olması, sadece tek bir sunucu üzerinde dikeyde genişlemeye imkân vermesinden dolayı raporlama gibi yazılımın çalışması için kritik olmayan isteklere çeki düzen verilmesi veya bir yük dengeleme mekanizması ile farklı sunuculara isteğin dağıtılması gerekmektedir.

Peki istekleri nasıl sınıflandırmak gerekir ki arka tarafta çalışan veri tabanı sunuculara bu yükü adil bir şekilde dağıtılabilsin? Söz konusu veri tabanın SQL Server gibi ilişkisel bir veri tabanı olduğunu varsayalım. İstekleri öncelikle veri tabanının hizmet ettiği yazılımın çalışması için kritik olan ve olmayan işlemler olmak üzere ikiye ayıralım. Örneğin bir muhasebe yazılımı için yeni muhasebe kayıtlarının girilmesi, yeni cari hesapların açılması, borç/alacak kayıtlarının girilmesi ve bu saydıklarımın mevcut olanları üzerinde değişiklik yapmak, silme gibi işlemler kritik işlemlere örnek olabilir. Aslına bakarsanız o yazılımı aktif olarak kullanan muhasebe personelleri için her bir değişiklik işlemi kritiktir. Buna karşın mevcut verinin okunmasından ibaret olan raporlama işlemleri kritik değildir; bir diğer deyişle yazılımın çalışması için “temel” bir işlev değildir; lakin sadece bu işe atanmış personeller, yöneticiler veya müşterilere anlık olarak duyurusunu yaptığınız her bir veri, raporlamayı da önemli hale getiriyor.

Üzerine eğileceğimiz SQL Server AlwaysOn AG Read-Only-Routing, şu ihtiyacı adreslemeye yönelik geliştirilmiştir: Raporlama gibi sadece mevcut verilerin okunmasından ibaret olan işlemleri hızlandırmak. Peki bunu nasıl yapıyor? Öncelikle AlwaysOn AG’nin yapısından kısaca bahsedelim.

SQL Server AlwaysOn AG ile yapılandırılmış bir veri tabanı senaryosunda verinin mutlak kopyasının bulunduğu ve dolayısıyla bütün isteklere cevap veren tek bir sunucu (primary replica) ve bu verinin birden çok sunucuya yatayda dağıtılmış sadece “okuma” isteklerine cevap verebilen ikincil sunuculardan (secondary replica) oluşur. Varsayılanda istek hangi amaçla gelirse gelsin (okuma/değişiklik) primary replica cevap verir. Birden çok sayıda olabilen ikincil sunucular ise yüksek erişilebilirlik ve felaket kurtarma senaryoların hizmet etmek için kullanılır ve bu olaylar gerçekleşene kadar da atıl durumda kalırlar. Read-Only Routing (bundan sonra kısaca ROR diyeceğiz) ile bu sunuculara yeni bir amaç daha vermiş olacağız: Veri tabanına gelen ve sadece mevcut verinin okunmasını içeren istekleri ikincil sunuculara arasında yük dengeleme dağıtımını gerçekleştirmek.

Peki ROR veri tabanına gelen isteklerin bu amaçla geldiğini nasıl anlayacak? İşte burada iki senaryo söz konusu: Uygulamanın doğrudan ROR’u destekler ve raporlama gibi okumadan ibaret istekleri özel bir parametre ile SQL AG Listener’a gönderir ve Listener bunu doğrudan ikincil sunuculara aktarır. Peki uygulama ROR’u desteklemiyorsa ne yapacağız? Bu durumda uygulamanızın raporlama gibi okuma isteklerini veri tabanına gönderirken kullandığı connection string’e (bağlantı cümlesi) spesifik bir parametre eklemeniz yeterli. Örnek vermek gerekirse:

SQL Server için .NET Framework Data Provider kullanan bir uygulamanın connection string’i şu şekilde olur:

Server=tcp:AGListenerİsmi,1433;Database=Veritabanıİsmi;IntegratedSecurity=SSPI; MultiSubnetFailover=True

Eğer söz konusu bağlantı isteğine ROR için gerekli “ApplicationIntent” parametresini eklediğimizde ise amacımıza ulaşmış oluyoruz:

Server=tcp:AGListernerİsmi,1433;Database=Veritabanıİsmi;IntegratedSecurity=SSPI;ApplicationIntent=ReadOnly;MultiSubnetFailover=True

Uygulama geliştirdiğiniz dile göre bağlantı cümlesi farklılık gösterebilir fakat tür ne olursa olsun “ApplicationIntent” parametresi sabittir. Peki SQL sürümü tarafında durum nasıl? ROR, SQL Server 2016 ve üzeri bütün SQL Server sürümleriyle yapılandırılmış AlwaysOn AG’lerde desteklenmektedir.

Bu ön bilgi sonrasında ROR’u kullanabileceğimiz bir senaryo oluşturalım ve halihazırda SQL AlwaysOn AG üzerinde kurulmuş bir veri tabanı mimarisinde nasıl aktif hale getirebileceğimize bakalım. Senaryomuzda PowerBI masaüstü sürümünden bir veri tabanına bağlanıp ROR desteği ile rapor verisi çekmeyi yapılandıracağız.

Örneğimizde bir adet Primary ve bir adet Secondary Replica’dan oluşan bir yapı olacak ve PowerBI’dan gelen istekleri ROR sayesinde sadece Secondary Replica sağlayacak; Primary replica bu isteklere yanıt vermekle meşgul edilmeyecek.

AlwaysOn konfigürasyonuna girmek için SQL Server Management Studio’yu açalım ve Always On High Availability sekmesi üzerinden AG’mize sağ tıklayıp Properties kısmına girelim.

Burada Endpoint URL’lerini bir kenara not edelim, zira bunlara ROR yapılandırmasında ihtiyacımız olacak.

“Readable Secondary” sütunundaki listeye tıklayarak ayarı “Read-intent only”ye çekelim. Bu işlemi hem primary hem de bütün secondary replica’larda yapalım; zira her secondary replica olası bir failover durumunda primary rolüne geçebilir; tersi de geçerli: Her primary replica, failover durumunda secondary rolüne geçip “read-only” isteklere yanıt vermeye hazır olmalıdır.

Bu ayarları yaptıktan sonra “OK” butonuna basıp ayarları kaydedelim. Tekrar aynı “Properties” penceresini açalım ve bu sefer sol taraftaki “Read-Only Routing” sekmesine gelelim.

Burada “Server Instance” kısmında bütün replica’larımızı görüyoruz. Her replica’nın karşısına bir önceki adımda not ettiğimiz endpoint URL’lerini “Read-only Routing List” satırına yazalım.

Gelelim aşağıdaki Read-Only Routing List kısmına. Bu kısımda her bir replica’nın karşısına kendisi primary rolünde olduğu durumda okuma isteklerini yönlendireceği secondary replica’ları ekleyelim.

Bunun için Server Instance’ta birinci sunucu seçiliyken aşağıdaki “Read-only routing list for: ….” kısmında okuma isteklerini göndereceği diğer replica’ları seçelim. Toplamda iki replica’nız varken bu işlem çaprazlama olacaktır fakat 3 veya 4 replica’nız olduğunda replica’larınızın donanımsal konfigürasyonlarına göre bir dağılım yapmanız gerekecektir. Eğer sağ alttaki read-only routing list’te birden çok replica bulunursa yük dengeleme mantığı uygulanarak istekler eşit olarak dağıtılacaktır.

Bu işlemleri yapıp “OK” ile kaydedin. SQL Server tarafındaki işlemlerimiz bu kadar. Gelelim uygulama tarafına. Örneğimizde raporlama yazılımı olarak PowerBI masaüstü sürümünü kullandık. Artık Windows Store üzerinden dağıtılan bu uygulamayı mağazadan ücretsiz olarak indirelim ve kuralım. Program açıldığı zaman Giriş sekmesinden “Veri Al”ı seçip alt tarafta çıkan seçeneklerden “SQL Server”ı seçelim.

Sonrasında çıkan pencerede “Sunucu” kısmına SQL Server Always On AG Listener adresini yazalım. ROR desteğini aktif hale getirecek kritik nokta alttaki “SQL Server Yük Devretme desteğini etkinleştir” seçeneğidir. Bu kutucuğa tik attığınızda PowerBI bu kaynaktan veri çekerken ROR desteği ile istek gönderecektir ve AG Listener bu isteği secondary replica’lara yönlendirecektir.

Peki isteğin gerçekten de secondary replica’lara gittiğinden nasıl emin olacağız? Bunun ispatı da gayet kolay. Herhangi bir client makineye SQLCMD aracını kurun. Aracı aşağıdaki adresten indirebilirsiniz:

Windows için:

https://www.microsoft.com/en-us/download/details.aspx?id=53591

Linux için:

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-tools?view=sql-server-ver15

Linux için paylaştığım linkte farklı Linux dağıtımları için ayrı ayrı yönergeler bulunmaktadır. Linux dağıtımınıza uygun yönergeleri takip edin. SQL Server kurulu Windows makinelerde SQLCMD halihazırda yüklüdür.

ROR’u konfigüre ettiğimiz SQL AlwaysOn yapısına SQLCMD üzerinden Read-only olarak bağlanacağız (tıpkı PowerBI gibi) ve basit bir sorgu çekeceğiz. Sorguya cevap veren replica secondary ise konfigürasyonumuzu doğru yapmış olduğumuz anlamına gelecektir. Şayet primary replica cevap verirse ROR’un düzgün yapılandırılmamış demektir.

Client makinenizde boş bir komut satırı açın ve aşağıdaki komut ile AlwaysOn Listener’a sadece okuma isteğinde bulunacağınızı belirtin:

sqlcmd -S AGListenerİsmi -d Veritabanıİsmi -K ReadOnly

-K parametresi hangi amaçla bu yapıya bağlandığınızı belirtiyor. “ReadOnly” yani sadece veri okuma amaçlı bağlanıyoruz.

Test amaçlı olarak isteğe cevap veren sunucunun ismini öğrenmemizi sağlayan @@SERVERNAME sorgusunu çekelim:

SELECT @@SERVERNAME

GO

Her satırdan sonra Enter tuşuna basalım. Sorguya cevap veren sunucu bir secondary replica ise amacımıza ulaşmışız demektir.

Artık raporlama gibi sadece okuma amaçlı istekler Primary Replica’ya hiç ulaşmayacak, bir veya birden çok Secondary Replica arasında yük dağıtımı ile paylaştırılacaktır.

Başka bir makalede görüşmek üzere.