Sql Server 2014 In-Memory OLTP Kullanımı, Avantaj ve Dezavantajları

Sql Server 2014 ile gelen ve Microsoftun üzerine en çok durduğu teknolojilerin başında olan in-Memory OLTP ( Online Transaction Processing ) .
Bu teknolojiyi aslında daha önce 2012 sürümünün SQL Server Parallel Data Warehouse (PDW) yeni adıyla Microsoft Analytics Platform System  iş zekası uygulamaları ve büyük veri için geliştirmişti. 
  
  Sql server 2012 de bu özelliğin OLTP sistemler için olmadığını duyduğumda açıkcası üzülmüştüm, sonrasında yeni sürüme in-memory teknolojisi geliştirilmesi biz dba ler için gerçekten heyecan verici oldu diyebilirim. 

  Gelelim işin tekniğine: bilmiş olduğunuz üzere sql server zaten sık kullanılan sorgu sonuçlarını cachede yani bellekte tutuyordu, bunu geliştirip artık manuel olarak bizimde tablo bazlı olarak memorye atabileceğimiz durumlara izin  verir hale gelmiş oldu.

   Tabi  buraya kadar her şey güzel, tablo direk memory den okunacak, diske gidip IO yapmayacak sql server, ama kritik veriyi memory de tutmamız ne derece doğru ?
  
   Kesinlikle doğru değil.

   Bu durum için aslında iki seçenek söz konusu, sql server bize memory de tuttuğumuz tablonun ne derece kritik olduğunu düşünüp, bu durumlar için de kendimizi garantiye alabilme imkanı sağlıyor, sağlıyor sağlamasına da bu durumda memory'nin hızından faydalanmamızı engellemiş oluyor.  Yani tablomuz her ne kadar memorye alınmış bir tablo olsada sorgunun normal diskten okunan tablo sonuçları ile hemen hemen aynı değerlerde sonuç dönmesine neden oluyor.


   Zaten bir çok durumda tanım tablolarımız ve diğer geçici tablolarımızı sorgularımızda kullanmak zorunda kalıyoruz. En azından bu tabloların memory based olması sorgularımız için epeyce bir performans artışına neden olacaktır. Temp table dan farklı olarak statistics oluşacak in-memory tabloda dahada  performans sağlamış olacağız.

   Özellikle rapor ortamlarını, production ortamdan henüz ayıramamış yapılar için büyük ölçüde rapor sorgularında performans artışları sağlayacaktır. Bu yapıda bir çok mimari geliştirilebilir.

   ŞUNU BELİRTMEKTE FAYDA VAR: in-memory oltp özelliği sadece SQL SERVER 2014 Enterprise 64 Bit sürümü tarafından desteklenmektedir.

Bir tablo memorye nasıl alınacak sorusunun cevabı;

1- Önce database üzerinde memory optimized data file group oluşturmamız gerekiyor:
  

ALTER DATABASE denemedb ADD FILEGROUP I_M_oltp_mod CONTAINS MEMORY_OPTIMIZED_DATA 

ALTER DATABASE denemedb ADD FILE (name='I_M_oltp_file', filename='c:\data\I_M_oltp_file') TO FILEGROUP I_M_oltp_mod

ALTER DATABASE denemedb SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON

2- Memory-Optimize tablo ve index oluşturulur.

Bu tip memory optimized tablolarda (data will be persisted) data kritiktir ve güvenlik sağlanır, performansdan kayıp vardır.

 CREATE TABLE dbo.Information (  InfoId INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,

    UserId INT NOT NULL INDEX ix_UserId NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),

    LogDate DATETIME2 NOT NULL

    ) WITH (MEMORY_OPTIMIZED=ON)

  GO

 

   Bu tip memory optimize tablolarda (Data will not be persisted) veri güvenliği yoktur dolayısıyla, sunucu beklenmedik bir şekilde kapanırsa veri kaybı yaşanır.

  diğerinden farkı DURABILITY=SCHEMA_ONLY  olmasıdır.Create sırasında durability belirtilmezse default olarak SCHEMA ve DATA

 

   CREATE TABLE dbo.Users (

   Id INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=400000),

   CreatedDate DATETIME2 NOT NULL,

   ShoppingCartId INT,

   INDEX ix_Id NONCLUSTERED HASH (Id) WITH (BUCKET_COUNT=400000)

 )

 WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY)

 GO

Bunları yaptık artık sorgularımız uçarak gelecek diye düşündüğünüzde yanılacaksınız, çünkü in-memory teknolojisinin henüz tüm gücünü kullanamadınız.

 Daha yüksek performans için Compiled Stored Procedure(makina dilinde derlenmiş sp) oluşturmamız gerekiyor.

Stored Procedureleri compiled sp yapabilmek için direk sp compiled modda oluşturabileceğimiz gibi mevcut sp mizi compiled moda çevirebiliriz. 

Ama bu işlemleri yapmamız için in-memory kısıtlarını (cursor kullanmamak ve data typelar v.b gibi) gözönünde bulundurmamız gerekecek.

Not: Bu tip bir procedure de kullanabileceğiniz tüm objeleri http://msdn.microsoft.com/en-us/library/dn452279.aspx adresinden detaylı olarak kontrol edebilirsiniz.

Varolan stored procedurelerimizin compiled moda convert edip edemeyeceğimiz konusunda microsoft kolaylık oluşturmuş ve wizard ile iki adımda sp deki uygun olmayan obje kullanımlarımızı listeleyecektir.
 
1. Adım:  (sp sağ click => Native Compilation Advisor)

2. Adım:  (Stored procedure validation tab ve Next )

NOT:

Hem tabloyu schema_only mode da create edip, aynı zamanda tabloyu kullanacak sp yi de compiled sp yaparsak bu şekilde in-memory teknolojisinin kaynaklarını dibine kadar kullanmış olur, en yüksek verimi elde etmiş oluruz.

Önemli NOT:
In-Memory teknolojisinin bir yan etkisi daha var, bu da sql servislerinin start up olma zamanları.
Şöyleki memory de tuttuğunuz tablo ve ya tablolarda ki data miktarı arttıkça sql servislerini başlatmanız daha da uzayacaktır.
Detaylı test sonuçların benim yaptığım testlerle çok paralel olmasada kaynak olarak bu adrese  bi göz atabilirsiniz. 


 

Sql Server 2014 AlwaysOn Testi ve Sonuçları

Bu metinde Ctp sürümünden beri incelediğim sql server 2014 versiyonu ile geçiş maceramız sonucu elde ettiğim deneyimleri paylaşacağım;

Microsoft'un  2012 den beri bağırdığı in-memory mevzusu  ( Pdw ile) her ne kadar beni çok heyecanlandırsa da bir türlü olgunlaşmamış meyve izleniminden kurtulmadı.
Temp tablolar yerine in memory tabloları kullanmak güzel sonuçlar versede ben caselerimde yapımıza daha ilerisini mantıklı olarak oturtamadım.
Tabi ki case'e göre kullanım durumu çok çok artırılabilir ancak sahip olduğu kısıtlar yüzünden neredeyse kullanılmayacak kadar atıl duruyor.

Asıl üzerinde en çok durduğum konu ise AlwaysOn oldu.
Bilindiği üzere ilk olarak 2012 ile doğmuş 2014 ile gelişimini sürdüren teknoloji de henüz olgunlaşmamış diyebilirim.

Sql 2000 versiyonu nu kullanmış biri olarak çok sıkıntılar yaşadığım active sencronizasyon ve replikasyon olayını bir türlü sorunsuzca yapamadığımız ms sql bu sefer replikasyon, logshipping ve diğerlerinden çok daha efektif bir teknoloji ile geldi karşımıza, kullanışı  ve kurulumu çok basit olan aynı oranda Yönetimi'de ileri derecede kolaylaştırılmış bu teknoloji hakkında izlenimlerimi paylaşacağım .

Önce kurulumuna değineceğim;
Kurulum yapmak için gereksinimizi iyi belirlemelisiniz. Şöyle ki 1 primary (yani kaynak sunucu) ve bir den çok active ve + ve ya pasif secondary sunucu (  replikalar) ile kolayca kurulum yapabilirsiniz.
Tabi iyi belirlemeniz gereken; ihtiyacınız doğrultusunda replika adedi seçiminizi ve bunların active replika = senkron kopya mı pasif replika = asenkron kopya mı olmasına karar vermek çok önemli, çünkü performansı önemli ölçüde etkileyecek bir tercih yapacaksınız.

Primary Replica   :  Kaynak database
Pasif Secondary    :  Eş zamanlı data transferi yok.
Active Secondary  : Eş zamanlı data transferi var.

     Nasıl etkiler sorusunun cevabı: commit olayı kaynak sql in (primary sunucu ) senkron kopyaların kaynaktan gelen transactionın teker teker commit dönmesini bekleyecek olması dır.
Neden ise : alwayson yapı gereği senkron kopyalara kaynakta açılan transactionı commit ederek kopya dbler ile senkronizasyonu sağlamaktadır. Bu sebeple kaynakta açılan transaction senkron sunuculardan commit yapıldı bilgisi gelene kadar açık kalıp commit olmayacak dır.  Bu sebeple senkron kopya seçimi çok dikkat arzetmektedir.
Testlerim sonucunda bir kayıtlı insert işlemi dahi standalone database e oranla 2-3 ms geç cevap vermektedir, ilerleyen zamanlarda insert ve update gecikmelerini gidereceklerini microsoft danışmanlarımızdan öğrenmiş olsamda ne kadar performansa yansıyacağını zamanla göreceğiz.

      Yapımızı her ne kadar fiber altyapı üzerine kursakta (Buarada şunuda belirtmek isterim ki, AlwaysOn log based bir teknolojidir, Attunity şirketinden log based data transfer lisansını alarak sql server'a uygulamışlardır. Bu her ne kadar fiber networkunuz olmasada aslında networkte sorgunuzun çok zaman kaybetmeyeceği anlamına geliyor. ) tek senkron kopya seçmeme rağmen özellikle insert işlemlerin de üstelik flash storage performansı altında yeterli performans yakalayamadığımı düşünmekte ve de gözlemlemekteyim.
Not; beklediğim bir durum du açıkcası.

      Ben senaryomuz gereği 1 aktif replika (senkron kopya) 1 pasif replika (asenkron kopya ) toplam 3 adet sql sunucu ile kurulum , yönetim , geliştirme ve performans testleri yaptım. (Uzun bir süre ve farklı storage artı farklı swich altyapısı altında, üstelik Firewall dan bağımsız bir yapıda)
Burada şunuda belirteyim asenkron kopyaların alwayson'un performansına hiç bir olumsuz etkisi yoktur. Yine daha önce belirttiğim transaction log dosyasından, bir nevi logshippingde olduğu gibi log dan data asenkron kopyaya iletilecektir, bu sebeple kaynak database e yük getirmeyecektir.
Dezavantajı olarak data eş zamanlı olarak asenkron kopyaya işlenmediği için fail-over durumda işlenilmemiş datalar olduğu için asenkron kopyaya failetmek zaman alacaktır (yüke göre 1-20 dakika arası bekleme değerleri gördüm.)  bu süre zarfında asenkron databaseiniz restoring durumda olacaktır.
Data kaybı ise failover sırasında iletilmeyi bekleyen loglardan kaynaklı yaşanabilir.

Pasif replikayı rapor sunucusu olarak kullanmayı aktif sunucuyu ise fail-over için saklamayı bu sebeple üzerine transaction vermeyip aynı zamanda kaynak sunucu performansının olumsuz etkilemesini en az yaşamasını sağlamayı planladım.
Çünkü aktif secondary üzerindeki yük kaynaktan gelen transactionının commit olma süresini etkileyecek hatta lock bile koyabilecektir.
Bunu unutmamanızı ve mimari seçiminizi yaparken gözönünde bulundurmanızı şiddetle tavsiye ederim.

ilk yaptığımız Testler sırasında senkron kopya üzerinde datanın olması gerektiği gibi eş zamanlı olarak eşlenmediğine bile şahitlik ettim:)  sebebine hala vakıf olamadık, ama çok yoğun transactionlar olduğu için belki senkron kopyada commit olmadan değerleri okumuş olabilirim diye de alwaysOn'a hüsnü niyyetle yaklaşmak isterim.

Yine bu yoğunluğu bağlı olarak asenkron kopyada data bazen saatlere yakın gecikmeler yaşamaya başladığını gözlemledim.
Bunların hepsinin ekran görüntüsünü alamadığım için buraya koyamamamın nedeni, testler uzun bir sürece yayıldığı için aldığım görüntülerin bir çoğu ya silindi ya da üzerine diğer görüntüler yazılması nedeniyle kayboldu.

İlk testlerimizde yaşadığımız sorunların bir tanesi de, Senkron kopyada bulunan databasein failover sırasında yine transaction yoğunluğuna bağlı olarak (çok ilginçtir) restoring durumda bir müddet kalıyor olması dır. Yaklaşık 30 dakika fail edilen database'in restoring durumda kalması bizde AlwaysOFF  terimini gündeme getirdi. Çünkü sql server eski teknolojisi olan cluster yapısı bu bekleme süresinin çok çok altında bir sürede fail etmekte olduğunu gayet yeterli derecede tecrübe etmiştik, hala daha ediyoruz. Böyle bir fail over senaryosu bence baya maliyetli olur. Bunu illa ki değerlendirip services packlerle önlemini alactırlar.
Uzmanlar tarafından olmaması gerekiyor şeklinde yorumlanan bu durumu," ancak kaynaktan gelen transactionlar henüz kaynaktan gönderildiğinde commit olmadığı için onların commit olması sırasında restoring durumdasınız dır." diye kabullendik. eve incelediğimizde gerçekten de IO yu beklediğimizi gözlemledik.
Aslında olması gereken transaction bazlı senkronizasyonda database zaten commit olan sorguları tamamlamış ve eşlenmiş durumda olmalıdır diye biliyoruz. Yanılıyor muyum ?

Sorunun kaynağının IO dolayısıyla disk olduğu varsayımından yola çıkarak flash memory disklere yönelip testlerimizi bu diskler üzerinde gerçekleştirmeye devam ettik.

Sonuçlar daha iç açıcı olsada yinede bazı uçuk rakamlara rastlamadık değil, vaad edile saniye de 1 gb değerlerine asla ulaşamadık bu bir.
sebebi için yapımız ve sunucularımız destek kısıtları hatta işletim sistemi kısıtları mazeretleri karşımıza çıktı.
Bu mazeretlerle bile saniyede 350 mb yazma garanti olarak ( altını asla görmediğim bir değerdir ) bir disk yapısında teslerimizde yukarıda belirtiğim değerlerin dahada aşağılara indiğini ama kesinlikle sıfırlanmadığını belirtmek isterim.

Bu yapıda ki mazretlerin giderildiği sunucu ve network ortamı ile gerçekleştirdiğim testlerin sonuçlarını en yakın zamanda paylaşmaya devam edeceğim.

Sql Server Master Key ve Asymetric Key Default Algoritması

    Sql server 2005 ve 2008 R2 versiyonlarında default master key algoritması 128 bit triple DES dir.
Sql server 2012 ile birlikte AES algoritması da sql server'ın desteklediği algoritmalar arasına girmiştir.


Hangi databaseler üzerinde master key olduğunu aşağıdaki sorgu ile bulabilirsiniz.

select name, is_master_key_encrypted_by_server  from  sys.databases

sorgu sonucunda is_master_key_encrypted_by_server alanı 1 olan database  üzerinde master key bulunmakta dır.


Databaseiniz üzerindeki Master ile ilgili bilgileri aşağıdaki sorgu ile öğrenebilirsiniz:

  SELECT *
  FROM master.sys.symmetric_keys AS MK
 WHERE MK.name = '##MS_ServiceMasterKey##'

Sql Server Implicit Transaction Yapısı

Bazı zamanlar delete , drop, update gibi  önemli sorguları çalıştırırken ufak bir dikkatsizlik bize çok büyük zarar verebilir. 

Bunun önüne geçmek aslında bizim elimizde olan bir şeydir.

Çoğu sql server müdavimlerinin şikayeti ORACLE ne güzel bu işlemi hemen geri alabilirdim ama sql serverda bunu yapamıyorum diye yakınıyor. 

Bu gibi yakınmalara mahal vermemek için sevgili developerlara ve sql server geliştiricilerine tam olarak hitap eden TRANSACTION tipi IMPLICIT TRANSACTION dır.

Bu transaction sql server da default olarak OFF durumdadır. Yukarıda ki gibi önemli sorgularımızı çalıştırırken manuel olarak bu transaction'ı ON duruma getirip çalıştırabilirsiniz. 

Bu şekilde yapmış olduğunuz işlemde bir hata var ise tıpkı oracle da olduğu gibi rollback yapabilecek ve yapmış olduğunuz işlemi geri alabileceksiniz. 

Burada dikkat etmemiz gereken diğer sorgulara ve sisteme zarar vermeden bu işi halletmemiz dir. Bunun için bulk işlemlerden özellikle kaçınmalısınız ve olması gerektiği gibi bu işlemleri küme küme yapmalısınız.

Bu transaction tipi Oracle'ın default transaction yapısıdır, sql server auto commiti default olarak kullanmaktadır, yani aslına baktığımızda bu olay sql server'ın bir handikapı değil bir tercihidir. Bizim sistemlerimizde de hangisi tercih edersek edelim aslında aynı özelliği kullanabileceğimiz hatırlatmak istedim.

Peki Nasıl olacak IMPLICIT TRANSACTION 'ı nasıl aktif hal getirebiliriz.

Set implicit_transactions ON     -- bu şekilde sessionımızda transaction tipimizi değişitirmiş oluyoruz artık otomatik olarak sql server sorgumuzu commit etmeyecek bizden komut bekleyecek demektir. 

Yapmış olduğumu sorgunun eğer doğru olduğunu düşünüyorsak sql server bizden bu query'i commit etmemizi bekleyecektir.

Commit komutu ile sorgumuzu işlemiz oluruz. 

Eğer çalıştırdığımız query de bir hata tespit eder ve geri almak istersek 

Rollback dememiz yeterli olacaktır. 

Tüm sorgularınızı çalıştırıp artık oturumunuzu kapatacak ken mutlaka transaction modu OFF yapmayı unutmayın !

Set implicit_transactions OFF

NOT: bu transaction olayı biraz maaliyetlidir, sakın açık unutmayın siz yinede sorgunuzun altına unutmamak için commit ve rollback komutunu yazın hatırlatmaksı açısından işinize yaracaktır. 

BİLDİĞİNİZ GİBİ AÇIK KALAN TRANSACTIONLAR DİĞER SORGULARI VE SİSTEMİ LOCKLAYACAKTIR.!

The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.


Bir database'e ait log file'ı kaybettiğiniz de yada, sql server ilginç olarak elinizdeki database log file'ını tanımıyorsa.

Sql servislerini restart ettiğiniz halde log file oluşmamış ve database ulaşılmaz durumda ise aşağıdaki scriptler işinize yarayacaktır.

 
ALTER DATABASE  DB_name  SET EMERGENCY

select name,state,state_desc from sys.databases
where  name='DB_name'


ALTER DATABASE  DB_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE


DBCC CHECKDB('DB_name',REPAIR_ALLOW_DATA_LOSS)

select name,state,state_desc from sys.databases
where  name='DB_name'


ALTER DATABASE DB_name  SET MULTI_USER