31 Ekim 2018 Çarşamba

Kişisel Verilerin Korunması Kanunu Kapsamında SQL Server ile Neler Yapılabilir?

Verilerimizi hem afet durumlarından, hem kişisel veya uygulama kaynaklı hatalardan, hem de içerideki veya dışarıdaki kötü niyetki kişilerden zaten korumak durumundaydık; fakat artık müşterilerimizin veya potansiyel müşterilerimizin verilerini gerek Avrupa Birliğinin GDPR'ı gerekse 6698 sayılı Kişisel Verilerin Korunması Kanunu düzenlemelerini de dikkate alarak korumamız gerekiyor. Aksi durumda şirketlerin çok büyük yaptırımlar ve cezalarla karşı karşıya kalması ve bunların neticesinde iflasa kadar gitmesi maalesef abartılı bir tahmin değil.

Bu tür düzenlemeler ve kanunlar vatandaşlar olarak hepimizin yararına olmakla birlikte gerek bilinçlendirme, gerekse varolan güvenlik düzeneklerimizi tekrar gözden geçirip daha da sağlamlaştırma fırsatı sunma açısından bir bakıma iyi de oluyor bence.

Malum güvenlik ciddi bir konu, fakat çok da maliyetli olabiliyor. Elbette her şirketin şartları, imkanları ve bütçesi farklı. Bazı çözümler çok güzel de olsa, gerek lisanslama gerekse yetkin personel maliyetleri açısından "güzelden soğutuyor". Bu nedenle çözümler zaman zaman şartları zorlayarak, ama genelde de eldeki imkanları kullanarak oluşturuluyor. Şahsen ben eldeki imkanları kullanarak bir şeyler üretmekten çok keyif alırım, bu yazımda özetle bahsedeceğim çözümler de bu bakış açısıyla üretilmiş çözümler. Ayrıca vurgulamakta fayda var, güvenlik sadece teknik önlemlerden ibaret değildir, buna da değineceğim.

Bu konularda hala hiçbir adım atmayan birçok şirket var, ne yazık ki bunlar çoğunlukta. Bazılarının kısmen de olsa bir şeyler yaptığını, azınlık bir kesimin ise iyi hazırlandığını görüyorum.

Bu yazımda başlık başlık ve kısa özetlerle benim yönettiğim Microsoft SQL Server ortamlarında ne gibi önlemler aldığıma, ilgili yöneticilere neler önerdiğime değineceğim. 


1- Ortamlarınızı ayırın:
Canlı (live/prod), test (pre-prod/QA/UAT), geliştirme (dev) ortamlarınızı mümkünse ayırın. Geliştirme ortamlarınızdaki verileri tazelerken muhakkak kritik verileri karartın/maskeleyin. Yazılımcılar geliştirme ortamlarında istedikleri testleri yapabilsinler, ki azami oranda canlı ortamda yetkiye ihtiyaçları olmasın. 


2- Kod yaygınlaştırma:
Veritabanına kod taşıma işini uygulamalar aracılığıyla gerçekleştirin, eğer bütçeniz yoksa da birinci öncelikli ve ikinci öncelikli kişiler belirleyin, test ve canlı ortamlara kod taşımalarını sadece bu arkadaşlar gerçekleştirsin.


3- Tüm uygulama/vekil kullanıcı bilgilerini kasaya koyun:
Uygulama/vekil kullanıcı bilgileri sağda, solda gezmemeli; Excel dokümanlarında veya postit'lerde veya metin dosyalarında saklanmamalı. Hem güvenlik açısından, hem de operasyonel açıdan kullanıcı adı, şifre ve diğer ayrıntılarının çok güvenlikli bir uygulama ile saklanması gerekiyor. Bu uygulamaya erişim yetkisinin çok sınırlı, ama yeterli sayıda kişide bulunması gerekiyor. Bu kişilerin kimler olduğu da ilgili yöneticiler tarafından bilinmeli, ki gerektiğinde kime ulaşılacağı net olsun.


4- Görevler ayrılığı ilkesi:
Sistem Yöneticisi veya Ağ Yöneticisi bir arkadaşın veritabanında ne işi var? Nasıl ki evimizin anahtarının sadece ilgili kişilerde olmasını istiyorsak, veritabanımıza giriş hakkının da sadece ilgili kişilerde olmasını isteriz.


5- Uygulama/vekil kullanıcılara sınırlama:
Uygulamalarımız için oluşturduğumuz ve adına uygulama/vekil kullanıcı diyebileceğimiz kullanıcıları gerçek kullanıcılar kullanamamalı. Örneğin cep telefonu uygulamaları için oluşturup kullandığımız "mobile_user" kullanıcısını yazılımcı bir arkadaş SQL Server Management Studio uygulamasını kullanarak veritabanına bağlanamamalı. Uygulama/vekil kullanıcıları veritabanına sadece belirli uygulama sunucularından bağlanabilmeli.


6- Anonim kullanıcıya hayır:
Birçok ortamda şirketteki tüm çalışanların "sa", "master", "admin" gibi anonim kullanıcıları kullandıkları kimse için bir sır olmasa gerek? Eğer bilgisayar isimleri de tanımlanabilir bir kodlama ile oluşturulmamışsa, ortamda bir Domain yoksa hangi işlemi hangi kullanıcı gerçekleştirmiş bulmak neredeyse imkansız oluyor. Özellikle kritik işlemlerin kimler tarafından, nasıl gerçekleştirildiğinin izini bulabilmek için tüm işlemleri birbirinden ayırt edebilmeniz gerekiyor. Bunun için de her gerçek kullanıcının hesap bilgilerini sadece kendisinin bildiği eşsiz kullanıcıları olması gerekiyor. Veritabanına erişimi olacak tüm şirket çalışanlarına kendi kullanıcısının mesuliyetinin kendisine ait olduğu net olarak anlatılmalı ve bu madde ilgili yaptırım ifadeleriyle işe giriş sözleşmesine de eklenmeli.


7- Sadece gerektiği kadar yetki:
Ne denirse densin, bazı ortamlarda çeşitli sebeplerle 1. maddede bahsini ettiğim ortamlar ayrılığı gerçekleştirilmiyor. Durum her ne olursa olsun örneğin bir yazılımcının yeni veritabanı oluşturmaya, varolan veritabanını veya tabloları silmeye ne sıklıkta ihtiyacı olur? Neredeyse senede 1-2 kere. Bir yazılımcının temel ihtiyaçları SP/Function/Trigger/Table gibi nesneleri oluşturmak ve değiştirmektir, ayrıca Select ve DML yetkisine ihtiyaç duyar; yani temel olarak ihtiyaçları aslında veritabanı seviyesindedir, sunucu seviyesinde değil. Gereğinden fazla gücünüz olması durumunda, hata yapma şansınız da artmış olur. Misal son model bir Ferrari ile mi hız kazası yapmanız daha olasıdır, yoksa 1.0 motorlu bir Kia Picanto ile mi? Eğer çalışanın ihtiyacı Kia Picanto ise, ona Ferrari vermeyin, ki kaza yapabilemesin.

Özellikle yeni çalışmaya başladığımız şirketlerde yetki kısıtlaması çalışması yaparken bazı arkadaşlar tarafından ilk önce ciddi tepkilerle karşılaşabiliyoruz, fakat daha sonra gerçekten ne yapmaya çalıştığımızı fark ettiklerinde onlar da çok memnun oluyorlar. Araç sürerken emniyet kemerinin takılması gerektiği gibi, maksat hem veriyi korumak, hem de çalışanı.


8-Hassas verilerinizi maskeleyin:
Gerek gerçek kişilerin kimliklerini ifşa edebilecek, gerekse şirket için hassas olan verileri doğrudan veritabanı seviyesinde maskeleyin. Bunu SQL Server 2016 ile birlikte gelen Dynamic Data Masking özelliği ile gerçekleştirebilirsiniz. SQL Server'ın Standard Edition'ında bile var bu özellik. Hiç bir kesinti veya uygulama tarafında kod değişikliği gerektirmeyen bir işlem bu. Sadece öncesinde ilgili yöneticiler ve çalışanlarla birkaç toplantı düzenleyip, hassas verileri tanımlayıp, belirleyip, tüm çalışanları bilgilendirip, öyle aksiyon almak gerekiyor.


9-Verilerinizi yedekleyin, fakat yedeklerken güvenliği ihmal etmeyin:
Mümkünse veritabanlarınızı şifreleyerek yedekleyin. Örneğin bunun için Transparent Data Encryption da kullanabilirsiniz. Böylece kötü niyetli kişiler bir şekilde yedek dosyalarınıza erişse bile, elde ettikleri veritabanlarını açamazlar.

Ayrıca Disaster Recovery (DR) Site'ınızda da benzer güvenlik önlemlerini aldığınızdan emin olun. Örneğin bir keresinde bir müşterimin canlı ortamına erişemeyen saldırganların DR ortamına saldırdıklarını fark etmiş ve ilgili kişilere bilgi verip açıkları kapattırmıştım.


10-Veritabanı sunucunuza art arda yapılan hatalı giriş denemelerini denetleyin ve bu denemelerden haberdar olun:
Eğer veritabanı sunucunuza art arda erişilmeye çalışılıyorsa ve bol bol hata alınıyorsa ya bir veritabanınız herhangi bir sebeple erişilemez duruma gelmiştir, ya bir kullanıcınızın hesabı bloke olmuştur ya da sunucunuza içeriden veya dışarıdan "brute force" yöntemiyle erişilmeye çalışılıyordur.

Ben bu kontrolü yaparak son 2 sene içerisinde en az 3-4 kere kötü niyetli saldırı girişimini fark ettim ve bu sayede gerekli önlemleri alarak ilgili sunucuların açıklarını kapatabildik.

Kangal: Çoklu hatalı giriş denetimi


11- Veritabanı sunucunuza yapılan başarılı girişleri denetleyin:
Sadece başarısız giriş denemelerini değil, başarılı girişleri de kontrol etmemiz gerekiyor, ki eğer bir çalışanın veya uygulama/vekil kullanıcının bilgileri kötü niyetli birisinin eline geçerse ve veritabanına bağlanırsa  haberimiz olsun. Aksi durumda bağlantı kurulan veritabanı sunucusundaki erişim sağlanan tüm veriler peyderpey çalınır ve ruhunuz duymaz.

Bu kontrol için örneğin Microsoft Azure'da Advanced Threat Detection diye bir yöntem var, fakat on-prem sunucular için henüz benzer bir teknik sunmuyor bize Microsoft. Microsoft'un Güvenlik Zirvesi'nde Advanced Threat Detection ile ilk karşılaştığımda on-prem'e bunu nasıl uyarlarım diye düşündüm. İlk aklıma gelen prototip verimli çalışmıyordu, fakat o prototipin son versiyonu şu anda tüm müşterilerimin ortamlarında çok verimli bir şekilde çalışıyor. Aşağıda bir örnek paylaşıyorum:


Kangal: Anormal bağlantı denetimi


12-Özellikle hassas verilerinizdeki değişiklikleri kayıt altına alın:
Eğer bu işe ayırabilecek ciddi bir bütçeniz varsa zaten bir güvenlik ekibiniz de vardır ve IBM Guardium veya Imperva gibi milyon dolarlık seçeneklere bakıyorsunuzdur. Fakat bütçeniz nispeten dar ise SQL Server 2016 Service Pack 1 ile artık Standard Edition'da bile kullanılabilen SQL Server Database Auditing özelliğinden faydalanabilirsiniz. Database Auditing'i tek başına kullandığınızda raporlama açısından ciddi zorluklar yaşarsınız, hiç pratik değildir ve log yönetimi çok çok sıkıntılıdır; fakat Database Auditing'i oldukça uygun bir lisans maliyeti olan CryptoLog gibi bir uygulama ile birleştirdiğinizde çok daha verimli bir sonuç elde edersiniz.


13- Toplu veri sorgulamalarından ve değişikliklerinden haberdar olun:
Kullanıcılarınız veritabanı sunucunuzdaki verileri toplu bir şekilde sorguladığında veya toplu bir değişiklik (Update/Delete gibi) yaptığında bundan haberdar olmak istemek için birden fazla nedeniniz var. Tabii ki öncelikli nedenini güvenlik. Özellikle finansal verilerde yapılacak toplu bir değişiklik çok can yakabilir veya kötü niyetli biri verilerinizi şifreleyip fidye isteyebilir*.

* Bu gibi senaryoların geri dönüş seçenekleri mevcut, fakat uzun bir konu.

Toplu sorgulamalardan da haberdar olmak istersiniz, çünkü mesela müşteri verilerinizin toplu bir şekilde şirket dışarısına çıkarılmasını istemezsiniz. Evet, olan olmuştur; fakat ne kadar kısa sürede ayrıntılardan haberdar olursanız, o kadar kısa sürede aksiyon alabilirsiniz.

Kangal: Toplu sorgulama denetimi


14- Eğer zorunda değilseniz SQL Authentication değil, Windows Authentication kullanın:
Microsoft'a göre SQL Authentication uzun süredir "Deprecated" bir özellik, yani bir süre sonra kaldırılacak; fakat bu durum uzun yıllardır böyle ve ben yakın zamanda da kaldırılacağını düşünmüyorum. Microsoft'un bu özelliği kaldırmak istemesinin nedeni de güvenlik. Fikir de şu, zaten bir Domain/Local hesabımız var, veritabanına bağlanırken de bunu kullanalım. Ayrı ayrı kullanıcıların ve şifrelerin olması, insanların kullanıcı hesaplarının ayrıntılarını kolay ulaşılabilecek şekilde sağa sola not etmesine neden oluyor. Haliyle bu da ciddi bir güvenlik açığına neden oluyor.

Tabii SQL Authentication ihtiyacı birçok senaryo için hala bir gerçek, fakat olabildiğince bilinçli olmakta ve Windows Authentication kullanmakta fayda var.


15-Yerel Güvenlik Politikası'nı kullanın:
Hem yeni Login'ler oluştururken, hem de varolan Login'leriniz için Windows işletim seviyesindeki Yerel Güvenlik Politikalarından faydalanın. Bu sayede kullanıcılarınızın Login şifrelerinin belli bir uzunlukta ve zorlukta belirlenmesini sağlayabilir, kullanıcılarınıza belli süreler içerisinde şifrelerini değiştirmesini zorlayabilirsiniz.

Not: Şifrelerin sıklıkla değiştirilmesinin de kendi başına bir güvenlik açığına neden olduğuna dair bazı görüşler var, şahsen ben de bu görüşlerin kısmen de olsa doğruluk payı olduğunu düşünüyorum. Örneğin 30 günde bir şifre değiştirmek zorunda kalan sıradan bir kullanıcının her 30 günde bir değişen bu karışık şifreyi nerede tutması bekleniyor? Aklında tutamayacağı aşikar. Ya telefonuna not ediyor, ya da bilgisayarının masaüstünde duran bir metin dosyasına. Yani güvenlikte aşırıya kaçıp, güvenlik önleminin bir başka güvenlik açığına dönüşmemesini sağlamak gerekiyor.


16-Rutin Login şifre testleri uygulayın:
Özellikle herhangi bir nedenle Windows Yerel Güvenlik Politikasının uygulanmadığı kullanıcılar için bu denetimin yapılmasında fayda var. Bu denetim sayesinde birçok ortamda örneğin şifresi 111111 olan kullanıcı tespit ettim.

Kangal: Güçsüz şifre denetimi


17-Veritabanı ve uygulama sunucularına RDP yetkisi:
Sadece sistem yöneticilerinin ve veritabanı yöneticilerinin veritabanı sunucusuna RDP yetkisi olabilir, o kadar. Bu yetki de rutin olarak değil, sadece gerektiğinde kullanılmalıdır. Veritabanı yönetiminin rutin olarak veritabanı sunucusuna RDP yaparak uygulanması iyi bir pratik değildir, çünkü çalıştırdığımız uygulamalar çeşitli nedenlerle "crash" olabilir, hiç beklemediğimiz şekilde yüksek CPU ve RAM tüketebilir, bunlar da işletim sisteminin kararsız çalışmasına neden olabilir ve böyle bir şeyin olmasını istemeyiz.

RDP yetkisinin sınırlandırılmasının bir başka amacı ise veritabanı dosyalarının kopyalama yöntemiyle şirket dışına çıkarılamaması ve kopyalanamamasının sağlanmasıdır.


18-Güvenlik konusunda bilinç:
Sonuç itibariyle en zayıf halkanız kadar güçlüsünüzdür. Şirket çalışanlarının veritabanı yöneticisinden çaycısına, yazılım mühendisinden şirket girişindeki danışmanlığa kadar bilinçli olması gerekiyor, ki mesela şirketin bahçesinde bulunan bir USB bellek ağdaki bir bilgisayara takılmasın, şifreler paylaşılmasın, yetkisiz kişiler yetkili olmadıkları departmanlara fiziksel olarak giremesin.


Eğer Microsoft SQL Server veritabanı sunucularınızın yeni versiyonlara yükseltilmesi ve güvenlik gibi konularda desteğe ihtiyacınız olursa bana ulaşabilirsiniz.

Ekrem Önsoy
Microsoft SQL Server Danışmanı
www.ekremonsoy.com


13 Eylül 2018 Perşembe

Eyvah! Storage'ın yazılımında "bug" çıktı!

Yönettiğim ortamların birinde disk altyapısı (storage) kaynaklı ciddi bir sorun oluştu. Şöyle bir senaryo düşünün ki, X veri merkezinde barındırdığımız canlı veritabanı ve uygulama sunucularımıza ait dosyaların ve yedeklerimizin bulunduğu disk altyapısı Y firmasının ürünü olan Storage'taki bir yazılım sorunu (bug) nedeniyle komple bozuluyor (corruption). Ne canlı ortam verilerine, ne de yedeklerine ulaşılamıyor. Tam bir kriz!

Bir süre sonra veri merkezi 1 gün öncesine ait veritabanı yedeklerine ulaşabildiğini söyledi, fakat bu bizim için 1 günlük veri kaybı demekti. Bu kritik bir sistem olduğu için tabii ki Transaction Log yedekleri alıyorduk ve bu yedek dosyaları da sistemi tamamen kaybetmeden 1 saat öncesine kadar geri getirilebildi. Bu noktada veri kaybı 1 saatti. Yine neyse ki bu ortamın BT yöneticisi felaket senaryolarının bilincindeydi ve bu konuda yatırım yapılmasını sağlamıştı, bu sayede başka bir veri merkezinde bulunan yedeklerimizi kullanarak yalnızca 1 dakikalık veri kaybı ile bu kriz atlatılmış oldu.

Not: Müşteri felaket önleme seçenekleri konusunda bilgilendirilmişti. Maliyet-fayda hesabına göre 1 dakikalık veri kaybı müşteri için kabul edilebilirdi.

Sadece son 2 senede 4 kere buna benzer durum için yedekliliğin önemine dair yazılar yazmışım (yazı1yazı2yazı3yazı4), en azından bir çırpıda bulabildiklerim bunlar. Disk altyapıları kısmen veya tamamen yazılım (firmware) veya donanım hatası nedeniyle bozulabilir. Ayrıca verileriniz disk veya sistem yöneticileri tarafından yanlışlıkla silinebilir veya bir saldırı neticesinde bozulabilir. Sistemleriniz çeşitli nedenlerle kullanılamaz, ulaşılamaz duruma gelebilir. Bu gibi olasılıklar sizin başınıza gelmeyecek sanmayın, çok yanılırsınız. Türkiye'nin veya dünyanın en büyük kurumlarının bile başlarına geliyor bu durumlar. Şahsen yönettiğim ortamlarda ister istemez, şartlar ve imkanlar dahilindeki her türlü önleme rağmen bahsettiğim çeşitli nedenlerle senede ortalama 2 kere yaşıyorum benzer senaryoları. Şimdiye kadar bunu tatmadıysanız bile, üzgünüm ama emin olun eninde sonunda tadacaksınız. En iyisi, o güne şimdiden hazırlıklı olmak!

Size tavsiyem şirketiniz içerisindeki ilgili partilerle bir toplantı yapın ve şu soruların cevaplarını arayın: 

"Ne kadar sürelik veri kaybına tahammülümüz var? 1 gün? 1 saat? 1 dakika? Birkaç saniye?"

"Sistemimizin en fazla ne kadar süre kapalı kalmasını tolere edebiliriz?" 

Tabii ki iki soruya da ilk etapta "Sıfır!" yanıtının gelmesi şaşırtıcı olmayacak, fakat bunun maliyet-fayda hesabının yapılması gerekiyor. Çünkü kullandığınız SQL Server Edition'ına göre farklı lisans ücretleri ödüyorsunuz ve yine Edition'lara göre farklı sürekli kullanılabilirlik ve felaket önleme seçenekleri mevcut. İhtiyacınıza en uygun çözümü örneğin SQL Server'ın Standard Edition'ıyla da üretebilirsiniz, böylece lisans maliyetlerinizi hele ki dövizin bu seviyelerde olduğu bu zamanlarda ciddi oranda düşürebilirsiniz. Ayrıca şunu da belirtmek gerekir ki bütçe musluklarını açsanız bile çeşitli nedenlerle tamamen kesintisiz bir ortam kuramazsınız, fakat kesintinin olabildiğince kısa sürmesini ve doğru yönetilmesini sağlayabilirsiniz.

Veri kayıpsız, az kesintili günler dilerim.

Ekrem Önsoy
Microsoft SQL Server Danışmanı
www.ekremonsoy.com

13 Ağustos 2018 Pazartesi

Her zaman sadece gerektiği kadar yetkilendirmenin önemi

Ortamlardan birinden 2-3 gündür aşağıda da paylaştığım anormal bir hata mesajı geliyordu. Açıkçası gelen hata mesajını, hatayı gönderen kaynak nedeniyle pek de önemli saymamış ve iş listemin ön sıralarına koymamıştım. 

Oluşan hata alarmı

Hata "syspolicy_purge_history" isimli SQL Server'ın SQL Server Agent Job tarihçesini silen Job'tan geliyordu. Bu standart, SQL Server kurulumuyla gelen bir Job'tır. Kısa süreli çalışmayışı kritik bir sorun yaratmaz, ama uzun süre çalışmazsa canınızı sıkabilecek sonuçlar doğurabilir. Sorunu inceleyince hatanın alındığı adımın Job'ın Powershell komutu içeren 3. adımı olduğunu gördüm. Önceden karşılaşmadığım bir hata idi, biraz derinine inmek istedim ve indikçe durum daha da ilginç bir hal alıyordu. Derken tamamen şansa işletim sistemi diski olan C: diskinin kök dizinindeki bazı "klasörlerin" uzantılarının *.exe olduğu çekti dikkatimi ve haliyle dehşete kapıldım!

Temsili ekran görüntüsü

Böyle bir manzarayı görmeyeli en az 10-15 sene olmuştur... Çok şaşırdım. Canlı bir veritabanı sunucusunun işletim sistemi diski (C:) bu!

Hemen ilgili yöneticilere ve mühendis arkadaşlara haber verdim ve hep beraber durumu incelemeye başladık. Bir süre sonra kötü niyetli bir korsanın iç ağa dışarıdan giriş yaptığı, bunu da Domain Admin grubuna üye olan bir hesabın bilgilerini ele geçirerek yaptığı anlaşıldı. Hesap Domain Admin yetkisine sahip olduğu için kötü niyetli kişi ağda istediği gibi gezinmiş, antivirüs uygulamasını kapatmış ve kim bilir daha neler yapmış... Yaptığı birçok şey çeşitli mekanizmalarla kayıt altına alınmış; fakat hesap zaten varolan ve varolması beklenen bir hesap olduğu için hareketleri göze batmamış. Hesabın bilgilerini elde eden kötü niyetli kişinin ağda hemen veritabanı sunucularına yöneldiği anlaşılıyor. Birkaç kere veritabanına bağlanmayı denemiş, fakat izlediğimiz Best Practice'ler sayesinde veritabanlarına bağlanamamış. Eğer veritabanı sunucumuzda, birçok ortamda gördüğüm gibi Domain Admin'ler ve Local Administrator'lar için yetki tanımlı olsaydı bu ortamda ne durumda olurduk düşünmek bile istemiyorum.

Şu anda neyse ki her şey kontrol altında.

Peki bu atak vakitlice fark edilmeseydi ve müdahale edilmeseydi neler olabilirdi? Aklıma gelen birkaç olasılık şöyle:
- Sisteme giren kötü niyetli kişi tüm veritabanlarını ve uygulama kodlarını ele geçirebilirdi,
- Varolan sunuculardaki dosyalar son zamanlarda popüler olan bir saldırı yöntemiyle şifrelenebilir ve fidye istenebilirdi,
- Eğer veritabanına bağlanabilseydi ve yeterince dersine çalışmış olsaydı finansal verilerle oynayabilir ve kazanç elde etmeye çalışabilirdi,
- Sadece yıkıcı bir etkiye neden olmak için verileri sonra fark edeceğimiz şekilde kirletebilirdi, ki yedeklerden geri dönebilmek için çok geç olurdu veya yedekleri de silebilirdi.

Bu hikayeden veritabanı yönetimi ve güvenliği açısından çıkarılması gereken dersler:
- Sadece gereken kişilere, gerektiği kadar yetki verme prensibinden asla şaşmayın. Mümkünse yetkileri geçici olarak verin ve sadece gerektiği sürelerde verin. Eğer geliştirme ve canlı öncesi/kalite testi ortamlarınız varsa canlı veritabanı sunucularınızda kimseye yetki vermeyin,
- Gerekli yetkilendirmeleri yaptıktan sonra eğer varsa "sa" hesabını ya Disabled duruma getirin, ya da adını değiştirin, çünkü tüm korsanlar bu hesabın varlığından haberdardır,
- SQL Server'ı Default Instance olarak kurduğunuzda Database Engine servisi 1433 portunu kullanır ve tüm korsanlar bunu bilir, bu nedenle farklı bir port numarası kullanın,
- Veritabanınıza yapılan hatalı giriş denemelerini takip edin, ortamınızın çalışma doğası dışında belli bir eşik değer geçildiğinde alarm üretilmesini sağlayın,
- Bu senaryoda olduğu gibi normal şartlarda oluşmayan hataların başka sorunların göstergesi olabileceğini unutmayın,
- * Yedeklerinizi mümkünse şifreli (encrypted) saklayın, ki yedek dosyaları kötü niyetli kişilerce ele geçirilse dahi içeriğe ulaşamasınlar,
- * Mümkünse Transparent Data Encryption (TDE) özelliğinden faydalanın, ki doğrudan veritabanı dosyalarınız ele geçirilse bile veriler ele geçirilemesin,
- SQL Server ve işletim sistemi güvenlik güncellemelerini uygulamayı ihmal etmeyin.

* Şifreleme için kullandığınız sertifikaları veritabanı yedek dosyalarının olduğu yerden çok farklı bir konumda saklayın, ki korsan bu anahtara ulaşamasın ki şifreleme önleminiz işe yarasın.

Ekrem Önsoy
Microsoft SQL Server Danışmanı
www.ekremonsoy.com

18 Haziran 2018 Pazartesi

Ve karşınızda... Çoban!

Bundan önceki yazımı yazalı 2 ay olmuş. Efendim son 2 aydır çok hummalı bir şekilde ve uzun zamandır aklımda olan bir projeye başlama kararı aldım ve gün itibariyle oldukça şekillendi, sonuçtan da oldukça memnunum. Bu heyecanımı sizlerle de paylaşmak istedim. 

Not: Baştan belirtmek isterim ki yazı ister istemez biraz pazarlama kokacak, çünkü ürettiğim ürün doğrudan verdiğim hizmet ile ilgili, yani herkese ücretsiz olarak açık bir ürün değil.

Bakım ve Destek Anlaşması kapsamında müşterilerime yıllardır geliştiriyor ve güncelliyor olduğum Kangal isimli veritabanı izleme mekanizmamı kuruyorum. Çok özetle bu mekanizma ile müşterilerimin veritabanı sunucuları güvenlik, performans, yönetim, bakım ve hata izleme gibi birçok kategoride sürekli denetleniyor ve bir sorun anında hemen alarm üretiliyor ve ilgili kişiler ayrıntılı bir şekilde bilgilendiriliyor. 

Bu sene Kangal için farklı talepler gelmeye başladı. Örneğin İzmir'de bulunan büyük bir yazılım firması ile geleneksel bakım ve destek anlaşması yapmak yerine, Kangal'ın kiralanması ve yanında danışmanlık hizmeti verilmesi üzerine anlaştık. Bir banka ile de Kangal'ın kiralanması üzerine görüştük. Fakat o zaman Kangal'ın bir arayüz uygulaması yoktu. Denetim ve izlemeyi yapan kodların her ne kadar yönetimi kolay olsa da, bu hiçbir zaman şık ve kullanışlı bir arayüzün yerini tutmaz. Ayrıca biriken istatistiklerin grafik arayüzlerle yorumlanması çok daha anlaşılır olabiliyor.

Bunun yanısıra Bakım ve Destek Anlaşmamız olan firmalardaki BT yöneticilerine Kangal'ın marifetlerini daha iyi gösterebilmem gerektiğini fark ettim. Yöneticiler bir hizmet alıyordu, alarmları görüyorlar, sorunlara da en kısa sürede müdahale edildiğini biliyorlardı; fakat bir taraftan da kendileri de bir şeyleri kurcalamak isteyebiliyorlardı.

Ben de bu yeni duruma ve talebe uyum sağlamak için Çoban'ı geliştirdim. Bu haberin özetini sizlerle burada birkaç görsel eşliğinde paylaşmak istedim. Yazı çok uzun ve sıkıcı olmasın diye sadece birkaç ekrana dair bilgi paylaşacağım. 


Çoban ile Kangal ilişkisi

Öncelikle şu temel bilgileri paylaşmak isterim:
- Verileri toplayan, raporları ve e-posta alarmlarını üreten Kangal'dır. Her bir veritabanı sunucusunda bir tane olur. Verileriniz kesinlikle dışarıya çıkmaz ve uzaktanki bir sunucuda depolanmaz. Kendi veritabanı sunucunuz üstünde tutulur. Kangal'ı bir "Agent" gibi düşünebilirsiniz.
- Çoban, Kangal'larda biriken verilerin analiz edilmesini, görsellerle daha iyi yorumlanabilmesini ve Kangal'ların kolaylıkla yapılandırılmasını sağlar. Örneğin Çoban'ı kendi ve diğer iş arkadaşlarınızın bilgisayarına kurarsınız ve tüm Kangal'ları yönetirsiniz. Çoban bir yönetim ve analiz arayüzüdür.
İki ürünün hem entegre, hem de ayrı olmasının nedeni esneklik sağlamak. Örneğin Çoban'a gerek kalmadan Kangal tek başına çalışabilir.
- Lisanslama Kangal bazında yapılır.


Çoban'ın giriş sayfası.
Her olasılığa karşın uygulamayı tamamen İngilizce dilinde tasarladım. Şu anda hala Beta sürümleri yayınlanıyor. Bununla birlikte ilk versiyonundan beri pilot müşterilerim tarafından kullanılıyor.

Sağolsunlar, birçok firmaya girip çıkma, birçok BT ve veritabanı yöneticisiyle tanışma ve ihtiyaçlarını dinleme şansım oluyor, ayrıca ben kendim de 12 senedir veritabanı yöneticisi olarak çalışıyorum ve 21 senedir kod yazıyorum. Kangal'ı tasarlarken amacım topladığı veriyi bulunduğu ortamdan çıkartmadan (güvenli) dağıtık planda çalışabilecek (esnek) bir yapı kurmaktı. Çoban ile de bu biriken veriden hem BT yöneticilerinin faydalanabilmesini istedim, hem de veritabanı yöneticilerinin hayatlarını kolaylaştırmak istedim. 


Instance seviyesindeki iyi/kötü pratik kontrolü ve durum raporu

Çoban ile bir SQL Server Instance'ına bağlanır bağlanmaz, o Instance ile ilgili sunucu düzeyindeki olası kötü pratikleri veya olası sorun noktalarını görürsünüz. Çoban size en sık karşılaşılan sorunları işaret eder.



İşlemci kullanım ayrıntıları

Ana panodaki işlemci ile ilgili sayfada tek bakışta son 30 dakika (veya seçeceğiniz farklı bir zaman dilimi) içerisinde kullanılan işlemci miktarını, ayrıca aynı zaman diliminin dünkü ve hatta geçen haftaki işlemci kullanım miktarını bir seferde görebilirsiniz. Böylece yaşanan yoğunluk normal mi, işlemci kullanımı dün veya geçen hafta da böyle miydi sorusunun cevabına hemen ulaşabilirsiniz. Ayrıca yine aynı ekranda işlemci kaynaklarının en çok hangi veritabanındaki kodlar tarafından tüketildiğini de görebilirsiniz. Önümüzdeki versiyonlarda da bu ekrana en fazla işlemci kaynağı tüketen sorguların ekleneceğini tahmin etmek zor değil sanırım.


Ayarlar

Kolay anlaşılabilir ve pratik olsun diye ayarlar ekranını yukarıdaki gibi, cümlelerle tasarladım. Çoban'dan yapacağınız tüm değişiklikler, doğrudan bağlandığınız sunucudaki Kangal'da uygulanacaktır.


Uygulama hatalarını takip ekranı
Uygulamalarınızın veritabanında ne zaman, hangi SQL komutunu çalıştırarak, ne hata aldığını bu ekrandan geriye dönük olarak takip edebilirsiniz. Bu tür verileri kaç güne kadar geriye dönük tutabileceğinizi Çoban'ın ayarlar ekranından belirleyebilirsiniz. Bu ayarlara göre Kangal, eski istatistikleri otomatik olarak silecektir. 

Özellikler ekranı
Şu anda 79 tane özellik var. Yukarıdaki ekran görüntüsünden de görebileceğiniz üzere bu özellikler Açık kalan Transaction, Blocking, kullanılabilir disk alanı, işlemci kontrolü gibi özellikler. Dilediğiniz özelliği, dilediğiniz sunucuda açıp kapatabilirsiniz. Hatta dilerseniz istediğiniz özelliği, istediğiniz zaman aralığında uyku durumuna sokabilirsiniz, böylece istediğiniz zaman aralıklarında alarm üretilmemiş olur. Yine istediğiniz alarmı, istediğiniz kişi veya gruba yönlendirebilirsiniz. Ayarlar penceresinde posta grupları oluşturabiliyorsunuz, o grupları bu ekrandaki istediğiniz özelliğe atayabilirsiniz.

Yine Özellikler'le doğrudan bağlantılı olarak, aşağıdaki ekran görüntüsünü paylaştığım İstisnalar sayfasında istediğiniz özellik için istisna tanımlayabiliyorsunuz. Örneğin istisna tanımlayarak X veritabanında Blocking oluştuğunda veya Y uygulamasından çalıştırılan ve uzun süren sorgular için alarm üretilmemesini sağlayabilirsiniz.


İstisna tanım ekranı

Yukarıdaki özellikler sayesinde aşağıdaki başlıklarla ve sorunlar hakkında ayrıntılı bilgiler içeren e-postalar alıyorsunuz.


Sistem olay kayıtları

Kangal için de, Çoban için de bakım ve destek anlaşmamız devam ettiği sürece ek bir ücret ödemeden faydalanabiliyorsunuz. İki ürün için de sık sık ve ayrı ayrı güncelleme üretilmeye, yeni özellikler eklenmeye devam edecek. Güncellemeleri de yine Çoban'ın ilgili arayüzleri vasıtasıyla kolaylıkla yapabileceksiniz. Bunun için size özel oluşturulan bir kullanıcı hesabını kullanıyorsunuz.


Kangal ve Çoban güncellemeleri

Aşağıda paylaştığım DDL Change Tracking desteğini Çoban'a 1.0.0.5 versiyonunda ekledim. Bu özelliği hangi veritabanında etkinleştirirseniz o veritabanındaki tüm tablo, prosedür, kullanıcı ve benzer değişiklikler aşağıdaki gibi kayıt altına alınır. Eğer dilerseniz kritik nesneler için tanım oluşturabilirsiniz ve o nesnelerde değişiklik gerçekleştiğinde (örneğin kritik bir tabloya yeni bir alan eklendiğinde) alarm üretilir.


DDL Change Tracking


Aşağıdaki 2 ekranda da Log Shipping ve Replication takibine dair ayrıntıları görebilirsiniz.


Log Shipping
Replication


Şikayetlerinizi, alacağınız olası hataları ve taleplerinizi aşağıdaki form vasıtasıyla doğrudan bana ulaştırabiliyorsunuz. Kaynak kodlar bizde, talebinize ve ihtiyacınıza özel yeni özellikler ekleyebilir, varolanları güncelleyebiliriz.


Geribildirim formu
Tüm iş ortaklarım için faydalı olması dileğiyle.


Ekrem Önsoy
Microsoft SQL Server Danışmanı
www.ekremonsoy.com


16 Nisan 2018 Pazartesi

"Always On" olacak diye umulan Availability Groups

Geçen hafta çok ilginç bir Always On Availability Groups (AG) sorunu ile karşılaştım, bunu yazmazsam olmazdı.

Önce ortam hakkında özet bilgi vereyim. Bu ortamda SQL Server 2014 + Service Pack 2 kurulu ve 2 Replica'dan oluşan bir Always On AG yapısı var; geçen senenin sonlarına doğru kurmuştuk. Müşteri, 2. Replica'yı sürekli kullanılabilirlik için değil, sadece raporlama için istemişti, bu nedenle Always On AG temel amacından birazcık saparak sürekli kullanılabilirlik için kullanılmıyor.

Sorun yaşandığı anda ben de başka bir şeyle meşguldüm, kesintiye dair telefon geldi ve ilk müdahaleyi telefonda yaptık. Fakat temel şeyler sonuç vermeyince kısa bir süre sonra sisteme ben bağlandım ve kontrollere başladım.

Canlı sunucuya bağlandığımda SQL Server Database Engine servisi çalışıyordu, fakat Always On AG Resource Group Offline durumdaydı. Cluster Resource'u bir türlü Online duruma gelmiyordu ve çok ilginç bir şekilde SQL Server Error Log'ta, Windows System Event Log'larında ve Cluster Log'larında aradığım ayrıntıları bulamadım. Bir taraftan da kesinti devam ediyordu, tüm Login'ler veritabanlarına erişemediğine dair hata alıyordu. En temel genel Microsoft sorun çözücü yöntem olarak sunucuyu yeniden başlattım. Çok ilginç bir şekilde sorunu bu hamle de çözmemişti (tabii ki takılıyorum)...

SQL Server Database Engine'e bağlanıp veritabanlarını kontrol edeyim dedim...




ve yukarıdaki ekranla karşılaşınca açıkçası çok şaşırdım, çünkü yazımın girişinde belirttiğim gibi bu ortamda Always On AG vardı ve bu da tek Primary Replica olabilecek makineydi, nasıl veritabanları "Restoring" durumda olabilirdi ki? Secondary Replica'daki veritabanları da "Not synchronizing" durumdaydı.

Henüz bunun şaşkınlığını atlatamadan bir de ne göreyim:



Yukarıdaki ekran görüntüsü Primary Replica'ya ait. Always On AG yapılandırmam yok olmuş!

Bunun nasıl olabileceği konusunda en ufak bir fikrim yok. Yani ne, nasıl tetiklenir de Always On AG yapısını bu şekilde siler hiç bilemedim. Secondary Replica'yı kontrol etmek geldi aklıma, bağlandım baktım, orada duruyor yapılandırma. Bulguları birleştirince Always On AG Cluster Resource'unun da neden bir türlü Online olmadığı anlaşıldı.

Sahadaki servis kesintisi sorununu gidermek için "RESTORE DATABASE" komutuyla hemen veritabanlarını Online duruma getirdim* ve servislerin ayağa kalktığını teyit ettim. Artık sahada kesinti yoktu. Bundan sonraki aşama Always On AG'yi ayağa kaldırmaktı. 

* Dikkatinizi çekerim veritabanları Restoring durumdaydı, öyle veya böyle bu duruma gelmişti, veritabanlarına ulaşmaya çalışan Login'ler de bu nedenle hata alıyordu ve Database Engine servisinin açılışında da herhangi bir sorun yoktu, bu nedenle basit bir RESTORE DATABASE ile veritabanlarını açabilir ve sahadaki kesintiyi engelleyebilirdim, temel öncelik her zaman kesintiyi güvenli bir şekilde sona erdirmektir.

Primary Replica'daki Database Engine servisinin Always On AG için Enabled durumunda olup olmadığını teyit ederek başladım işe. Daha sonra veritabanları açıldıktan sonra Primary Replica'da herhangi bir Transaction Log dosyası yedeği alınmış mı diye kontrol ettim. Eğer alınan bir yedek varsa bu yedekler Secondary Replica ile senkronizasyonun sağlanabilmesi için Secondary Replica'daki veritabanlarına da uygulanmalıydı. Komple yeniden kuruluma gerek yoktu, çünkü Secondary Replica'daki veritabanları duruyordu, sadece iki Replica'daki veritabanları arasında herhangi bir Log Sequence Number (LSN) boşluğu olmadığından emin olmak yeterliydi, eğer boşluk varsa da bu eksikler Transaction Log yedekleri Secondary Replica'da Restore edilerek giderilebilirdi.

İki Replica'daki veritabanlarının arasında LSN boşluğu olmadığından da emin olduktan sonra Always On AG'yi ilgili veritabanlarıyla tekrar oluşturdum ve tüm eksiklikler giderilmiş oldu. Artık sistem kesinti olmadan önceki gibi çalışıyordu.

Tabii bu noktadan sonra sorunun nedenini araştırmak gerekiyordu. Ne olmuştu da kesinti oluşmuştu?

Always On AG, diğer birçok Microsoft teknolojisi gibi kurulumu nispeten kolaydır, bu nedenle birçok şirkette şık arayüzler kullanılarak "ileri, ileri" denilerek kurulur; fakat birçok senaryoda ya doğru tasarlanmaz, ya doğru yönetilmez, ya da sistem işlerliği doğru takip edilmez. Bazen de bizim senaryomuzda olduğu gibi ne yaparsanız yapın, birileri bir şekilde bir şeyleri bozabilir. Bizim senaryomuzda birisi bir sorgu çalıştırmış ve sunucu bu aşırı garip sorgu nedeniyle birkaç dakika cevap veremez duruma gelmiş. Bu nedenle Cluster, SQL Server'dan cevap alamamış ve "lease" yenilenememiş ve Cluster tarafından bu sunucu sağlıksız sayılıp servis kapatılmış. (Ödev: Böyle senaryolar için de bu garip sorguları çalıştırabilecek Login'ler tespit edilip Resource Governor'ın kullanımı düşünülmelidir, bu sayede bu potansiyel tehlikeli kullanıcıların işlemci kaynağını azami kullanım miktarı sınırlanabilir, bu aşamada müşteri ile bunu görüşüyoruz.)

Yeri gelmişken tekrar vurgulamak istedim. Always On AG, Log Shipping, Replication, Database Mirroring veya Failover Clustered Instance'larınızı sadece kurup kendi hallerine bırakmayın. Yakından izleyin. Aktarımlar geride kaldığında, yapının parçalarından birinde kesinti olduğunda, uygulamalar Virtual Network Name / Listener gibi sunucu agnostik elemanlar yerine doğrudan üye sunuculara bağlandığında haberiniz olsun istersiniz. Daha sürdürülebilir, daha sağlıklı ve daha kesintisiz ortamlar için bunlar sadece en temel kontrollerden birkaçıdır.

Yazıyı yazarken eşim de o anda bir fotoğraf çekmiş, bu yazıyı sevgili Tuncel Kurtiz'in Zeytinbağı'ndayken yazmıştım, nur içinde yatsın.



Ekrem Önsoy
Microsoft SQL Server Danışmanı
www.ekremonsoy.com


12 Mart 2018 Pazartesi

Bir kesintiyi en az zararla atlatmaya örnek

Birkaç hafta önce bir müşterim gece 23:00 sularında aradı ve çok kritik bir Microsoft SQL Server ortamındaki yine çok kritik bir tabloda aşağıdaki gibi bir hata aldıklarını söyledi:

Arithmetic overflow error converting IDENTITY to data type int.

Açıklama: Eğer bu hatayı alıyorsanız, veri tipi Integer olan ve Identity özelliği olan bir tabloya yeni bir kayıt ekleyemiyorsunuz demektir. Integer veri tipinin tutabileceği veri aralığı -2.147.483.648'den 2.147.483.647'ye kadardır.

Not: Benzer bir hatayı Smallint veya Bigint veri tipiyle tanımlanmış alanlar için de alabilirsiniz, tabii ki bu durumda hata mesajının sonundaki veri tipi değişkenlik gösterecektir.

İlgili tablo kesintiyi tolere edemeyecek bir tabloydu, her ne kadar yeni kayıt alamasa da, varolan kayıtlarla da sistem çalışabiliyordu. Yani esas kesinti yeni kayıt girilememesinden değil, varolan kayıtlara ulaşılamamasından kaynaklanıyordu ve bu hata varolan kayıtlara ulaşılamamasına neden olmuyordu. Fakat bu tabloya yeni kayıt ekleme ihtiyacı da kaçınılmazdı, yani sorun muhakkak çözülmeliydi.

Müşterimin ilk aklına gelen şey veri tipini BIGINT'e çevirmekti, fakat şartları düşündüğümüzde bu kötü bir seçenekti çünkü hem Identity alanı Primary Key ve Clustered indeks idi, hem de bu değişiklik uygulama kodlarını da etkilediği için kodlarda da değişiklik yapılması gerekiyordu. Bu hem kesinti gerektirecek bir işlemdi, hem de çok temel bir tablo olduğu için uygulamanın birçok yerinde kullanılıyordu ve kodların değiştirilmesi uzun sürecekti.

Identity alanının en küçük değerine baktığımda 700 milyonlarda olduğunu gördüm. Yazılımcı ve yönetici arkadaşlarla yaptığımız fikir alışverişinde bu alanda tutulan değerlerin hiçbir anlamı olmadığını, son kullanıcıya gösterilmediğini ve hiçbir yerde kullanılmadığını öğrendim. Yani aslında temel olarak gereksiz bir alandı ve ironik olarak gereksiz bir alan yüzünden kısmen kesinti oluşmuştu. Sonra biraz düşününce bu alandaki değeri neden -2.147.483.648 yapmayalım ki dedim önce kendi kendime, sonra da ilgili yöneticilere. Sonuç itibariyle madem bu değerlerin bir anlamı yoktu, o zaman bu değişiklikle tabloya en az 2,8 milyar daha yeni kayıt konabilir olacaktı; hem de tek bir metadata işlemiyle, zerre kesinti, risk ve uygulama tarafında kod değişikliği olmadan ve zahmetsizce.

Sonuç itibariyle bu çözümü uyguladık ve sorunumuz çözülmüş oldu. Düşündüğümüz gibi hiçbir kesinti veya hata oluşmadı, kimsenin uygulama kodlarını değiştirmesine gerek kalmadı.

Tabii ki her sorun kendine has şartları barındırıyor ve her sorun aynı yöntemle çözülemez. Bu ve benzer sorunlarla karşılaştığınızda soruna aklınıza gelen ilk şeyle hemen müdahale etmek yerine, sorunu birçok açıdan ve olabildiğince resmin büyüğünü görerek değerlendirmeye çalışmanızda ve en verimli çözümü bulmaya çalışıp uygulamanızda fayda var. Özellikle kritik, 7/24 operasyonun olduğu ve kesintiyi kaldıramayacak ortamlar için bu olmazsa olmaz bir gereklilik.

Ekrem Önsoy
Microsoft SQL Server Danışmanı
Tel: +90 530 976 93 59
www.ekremonsoy.com

8 Şubat 2018 Perşembe

Veritabanı sunucularınızı izlerken canlarını yakıyor olabilir misiniz?

Dün ve bugün farklı müşterilerde ilginç 2 sorun ile karşılaştım. İkisi hakkında da yazmak istiyorum, ama bugün sanırım sadece birisi için yeterli enerjim var.

Müşteride bazı kontroller yaparken en masraflı sorguları da kontrol ettim. Bu çalışmayı yaparken kullandığım sorgulardan birisi şöyle (yer açısından kod örneğini kısalttım):


SELECT TOP(20) 
[qs].[execution_count],
[qs].[total_worker_time],
[qs].[total_worker_time] / [qs].[execution_count] AS [avg_worker_time], 
[qs].[total_elapsed_time] / [qs].[execution_count] AS [avg_elapsed_time], 
SUBSTRING([qt].[text], ([qs].[statement_start_offset] / 2) + 1,
((CASE qs.[statement_end_offset] 
    WHEN -1 THEN DATALENGTH([qt].[text])
    ELSE [qs].[statement_end_offset] END 
        - [qs].[statement_start_offset]) / 2) + 1) AS [problematic_statement_text]
FROM sys.dm_exec_query_stats AS [qs] WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text([qs].[plan_handle]) AS [qt] 
ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);


İnternette de birçok çeşitli örneğini bulabileceğiniz bir kod örneği, özel bir şey yok. Bu kodu çalıştırdıktan sonra oluşan manzaraya ait ekran görüntüsünü de aşağıda paylaşıyorum.


Ekran görüntüsünü büyütmek için üstüne tıklayın.

CPU açısından en masraflı sorgulara ait istatistikleri incelemek için yukarıdaki kodu çalıştırdıktan sonra karşılaştığım görüntüde kırmızı dikdörtgen ile işaretlediğim çok ilginç bir sorgu dikkatimi çekti. Bu ilginç sorguyu da aşağıda paylaşıyorum:


SELECT    A.request_session_id, A.request_request_id, A.resource_type, A.resource_associated_entity_id, A.resource_database_id, A.resource_subtype, convert(nvarchar(100),substring(A.resource_description,1,100)) AS resource_description  FROM sys.dm_tran_locks AS A WITH ( NOLOCK )  WHERE A.request_status = 'WAIT'


Not: Bu da kesinlikle özel veya müşterimin kendi yazılımına ait bir sorgu değil.

Sorgunun neden çok ilginç olduğunu şöyle izah edeyim:
1- Sorgu, yukarıda da belirttiğim gibi müşterimin kendi uygulamalarına ait değil,
2- Müşterimin uygulamalarından üretilen sorguların en yüksek CPU masraflısının masrafından 8 kat daha masraflı!

Daha fazla inceleyince, sorgunun 3. parti ve piyasada gayet bilinen ve yaygın olarak kullanılan bir performans izleme uygulamasından geldiğini gördüm. Bu, akıllardaki sorulardan sadece birini cevaplıyor. Bir diğer soru ise neden bu kadar sade olan ve bir Dynamic Management View (DMV)'ü sorgulayan bir sorgunun bu kadar masraflı olduğu.

Sorgunun masrafları hakkında biraz ayrıntı vereyim. Sorgu her çalıştığında 2,6~ saniye sürüyor, neredeyse hiç Read (diskten okuma) yapmıyor, fakat 2,5~ saniye CPU zamanı tüketiyordu. Ayrıca sorgu sürekli olarak çalıştırılıyordu, sanki gerçek zamanlı izleme yapılan bir ekrandan çağrılıyor gibi. 

Bu noktada ilk aklıma gelenler:
- Böyle ünlü bir firma, nasıl olur da bu kadar masraflı bir sorguyu bu sıklıkta çalıştırır ve izleyenin izlenenin durumunu bu kadar dramatik olarak değiştirmesine neden olur? (Elbette her izlemenin bir maliyeti vardır, ama bu kadar da değil!)
- Acaba bu ortamdaki SQL Server versiyonuna has bir durum, davranış veya ürün arızası mı söz konusu?

Hemen aynı kodu hiç yük olmayan bir test ortamımdaki SQL Server 2016 ve 2017'de denedim. Sorgu 0ms'de tamamlanıyordu. İlginç! Başka, ama bu sefer canlı/üretim ortamı olan SQL Server 2005, 2008R2, 2012, 2014 ve 2016'larda denedim ve bu noktada sorgunun maliyetinin SQL Server'ın versiyonuna göre değil, kodun çalıştırıldığı ortamdaki işlem yoğunluğuna göre değiştiğini fark ettim. Örneğin hiçbir işlem olmayan test / geliştirme ortamlarında 0ms CPU zamanı tüketen sorgu, ortalama işlem hacmine sahip canlı/üretim ortamlarında 150 - 350ms arasında CPU zamanı tüketiyordu.

Sonuç itibariyle ortaya çıktı ki sorgunun çok CPU zamanı tüketmesinin nedeni SQL Server'ın versiyonuyla ilgili değildi, kodun çalıştırıldığı ortamdaki işlem yoğunluğuyla ilgiliydi. Bununla birlikte ne olursa olsun, bir izleme uygulamasının sistem üstünde bu derecede yük oluşturması bence kabul edilemez. Müşteriye de bu durumu ayrıntılarıyla izah ettim.

Her türlü kritik ortamınızı 7/24 izlemek durumundasınız. Olabildiğince kesintisiz, güvenli, performanslı ve stabil bir hizmet verebilmek için, işinizi riske atmamak için bu bir seçenek değil, zorunluluktur. Fakat sisteminizi izlerken yanlışlıkla performansını kötü etkilemediğinizden, doğru kontrolleri düzgün ve yeteri kadar uyguladığınızdan emin olmanızda büyük fayda var.

Ekrem Önsoy
Microsoft SQL Server Danışmanı
Tel: +90 530 976 93 59
www.ekremonsoy.com

15 Ocak 2018 Pazartesi

Denetime Ramak Kala Bir Gizemin Aydınlatılması

Özellikle son aylarda farklı birçok şirketle oldukça hummalı bir şekilde güvenlik konulu toplantılar ve çalışmalar yapıyoruz. Bunun tek nedeni hiç durmadan ve çeşitli katmanlarda ortaya çıkan donanım ve yazılım açıklarının yanısıra, çeşitli kurumlar tarafından uygulanan harici denetimler ve bu çerçevede ortaya çıkan ihtiyaçlar oluyor. Tabii olarak veritabanları da tüm bu kaosun merkezinde oluyor.

Bakım ve Destek Anlaşması kapsamında danışmanlık hizmeti verdiğim birçok şirkette özellikle son zamanlarda Ernst & Young, KPMG, PWC, PCI gibi uluslararası firmaların yanısıra Finansbank, Abank, TEB, Akbank gibi bankaların çeşitli denetimleri gerçekleşiyor. Yıllardır çok çeşitli kurumlarda, defalarca denetime tabi tutulunca ne zaman, nereden bulgu geleceğini biliyorsunuz. Bu nedenle destek verdiğim ortamlarda varsayılan olarak bu noktalar için zaten gerekli müdahalelerde bulunurum. Fakat bazı tedbirler ancak yeri geldiğinde, gerçekten gerekiyorsa ve yönetimin de desteğiyle alınabiliyor. Örneğin veritabanına gelen işlemlerin kayıt altına alınması gibi. Çünkü bu, ek maliyetler doğurabilecek ve koordinasyon gerektiren bir hamle.

Bir müşterimin geçireceği denetim dolayısıyla Microsoft SQL Server veritabanı ortamındaki bazı kritik tablolarında gerçekleşen kayıt okuma ve değişiklik işlemlerinin kayıt altına alınması gerekiyordu. Yani mesela bir tablodaki kayıt ne zaman, nasıl ve kim tarafından okunmuş veya değiştirilmiş gibi. Bu kapsamda yapılmak istenen kayıt altına alma işleminin hacmi, kapsamı, denetim firmasının ve müşterimin beklentisi, müşterimin ortamı, olanakları ve bütçesi gibi kriterleri değerlendirerek kendilerine bazı tavsiyelerde bulundum. Sonuç itibariyle Microsoft SQL Server'ın önceden başka ortamlarda da (bir banka dahil) kullanmış olduğum bir özelliği olan Database Audit özelliğini kullanmaya karar verdik.

Database Audit özelliğini devreye aldıktan sonra bir şey dikkatimi çekti, SQL cümlecikleri (statement) nedense kırpılmış şekilde kaydediliyordu. Önceden böyle bir durumla hiç karşılaşmamıştım ve buna bir türlü anlam veremedim. 

"statement" alanındaki SQL cümleciği kırpılmış olarak kaydediliyor
Gayet farkındayım ki Windows Application Log dosyasını bu amaçla kullanmak muhteşem bir karar değil; ama Database Audit'in ve diğer benzer mekanizmaların ürettiği kayıtları toplayacak olan uygulamanın bize sunduğu seçeneklerdeki kısırlık nedeniyle Database Audit'in ürettiği kayıtları doğrudan Windows Application Log'una kaydetmek birçok açıdan en mantıklı seçenekti ve ben de bunu uyguladım. Önceki tecrübelerimde Database Audit tarafından üretilen kayıtları doğrudan dosyalara kaydettiğim için ve bir ihtimal yaşadığımız bu sorunun üretilen kayıtların Windows Application Log dosyasına kaydedilmesiyle ilgili olabileceği ihtimaline karşın test etmek için yine bir dosyaya kaydetmeyi de denedim; fakat maalesef sonuç yine aynıydı! SQL cümlecikleri kırpılarak kaydediliyordu.

Testlerden sonra hemen Microsoft'un dokümantasyonunu inceledim ve bu konuda yazılmış olabilecek olası yazıları aradım; fakat konuyla ilgili bir bilgiye ulaşamadım.

Bakım ve Destek Anlaşması kapsamındaki müşterilerimin doğrudan Microsoft ile anlaşması olsun veya olmasın senede 2 defa ücretsiz Microsoft'a çağrı (Case) açılması ve tarafımca bunun takibinin yapılması hakkı da oluyor. Bu kapsamda müşterime bunun belki Database Audit özelliği ile ilgili bir ürün hatası olabileceğini ve dilerlerse Microsoft'a çağrı açabileceğimi söyledim; fakat bunun öncesinde daha hızlı sonuç alabilme ümidiyle şansımı bir de Microsoft'un ilgili forumlarında denemek istedim ve sorumu sorup beklemeye başladım. Bir süre sonra beklediğim cevaplar geldi!

1 günlük bekleyişten sonra gelen cevap ve yanan ampul.

İyiki de sormuşum, hiç Microsoft'un çağrı süreciyle vakit kaybetmeden sağolsun Tom Philips'ten sorunumuzu aydınlatacak cevabı almıştım. Belli ki ilk incelememde dokümantasyondaki ve sorunumun cevabı olan bu kritik bu sayfayı ıskalamışım:

Microsoft dokümantasyonu
Özetlemek gerekirse öncedeki Database Audit tecrübelerimde belli ki hiç 4000 karakteri geçen SQL cümleciğine denk gelmemişim veya dikkatimi çekmemiş ve bu nedenle bu davranışı fark edememişim. Halbuki bu durum Database Audit ile ilgili bir sorun değil, özelliğin doğal çalışma mekanizmasıymış. Bir SQL cümleciği 4000 karakteri geçiyorsa Database Audit bu cümleciği "sequence_no" adı altında artan sayı değeriyle cümleciğin sonuna kadar parçalara bölüyormuş. SQL cümleciğinin tamamını okumak için "event_time", "action_id" ve "session_id" alanlarındaki değerleri takip edebilirmişim. Evet, çok pratik değil; fakat nihayetinde bu bir ürün hatası da değil!

Bu gizemi de aydınlattıktan sonra içimiz rahatladı, çünkü şirketin tabi olacağı denetime çok az bir süre kalmıştı ve sorunumuz için muhakkak bir çözüm üretmemiz gerekiyordu. Eğer bu özelliği zamanında hayata geçiremezsek ve SQL cümleciklerinin bu durumunu denetim firmasına açıklayamazsak bu konuda bulgu çıkabilir ve nahoş sonuçlara neden olabilirdi.

Ekrem Önsoy
Microsoft SQL Server Danışmanı
www.ekremonsoy.com