Recent comments

None


İçerik Ara











Yasal Uyarı
Bu sitede sunulan tüm bilgi ve dökümanlar Turgay Sahtiyan tarafından yazılmaktadır. Yazıların kaynak göstermek şartıyla kullanılması serbesttir.

© Copyright 2009-2013
Takvim
<<  Ağustos 2017  >>
PaSaÇaPeCuCuPa
31123456
78910111213
14151617181920
21222324252627
28293031123
45678910
Keywords

SQL Server’a windows authentication yani windows account’lar ile bağlantı kurulurken önerilen bağlantı protokolü KERBEROS’tur. Bu yazımda güvenlik açısından oldukça önemli olan bu best practice üzerine konuşuyor olacağız.

[more]

Kerberos Nedir?

Kerberos Windows 2000 ile beraber gelmiş bir güvenlik protokolüdür. NTLM’e oranla daha güvenli olduğu için windows authentication’ların KERBEROS üzerinden bağlantısı kurması best practice’dir.

Kerberos bağlantı protokolü authentication esnasında SPN’leri kullanır. SPN (Server Principal Name), bir servis ya da instance’ı tanımlayan unique bir addır. Eğer SQL Server service account’u için uygun bir SPN oluşturulmadıysa Kerberos bağlantı isteği düzgün bir şekilde karşılanamayacak ve bağlantı NTLM’e dönecektir.

Bağlantılarım Kerberos mu?

SQL Server’a kurulmuş windows authentication bağlantıların Kerberos protokolünü kullanıp kullanmadığı sys.dm_exec_connections DMV’sinden sorgulanabilir.

select es.login_name, ec.auth_scheme, *
from sys.dm_exec_connections ec
left join sys.dm_exec_sessions es on es.session_id = ec.session_id
where auth_scheme <> 'SQL' and net_transport = 'TCP'

Bu sorgunun benim sistemimdeki örnek çıktısı aşağıdaki gibidir.

image

Sorgu sonucundaki auth_scheme değerlerinin KERBEROS olmasını bekliyoruz. Eğer değil ise SPN problemimiz olduğunu düşünebiliriz.

SPN’ler Nasıl Sorgulanır?

Bir service account’una tanımlanmış SPN’leri sorgulamak için aşağıdaki komut kullanılabilir.

SetSPN -L <domain>\<account>

Bir SPN’in olup olmadığına ise aşağıdaki komut ile bakılabilir.

setspn -Q <SPN>

Örneğin MySQLServer isimli sunucumuzun default instance’ında MyDomain\MyServiceAccount service account’unun kullanıldığını düşünelim. Bu durumda aşağıdaki komutlar ile olması gereken SPN’lerin olup olmadığını sorgulayabiliriz.

setspn -Q MSSQLSvc/MySQLServer

setspn -Q MSSQLSvc/MySQLServer.MyDomain.com

setspn -Q MSSQLSvc/MySQLServer.MyDomain.com:1433

Eğer default değil de named instance (örneğin INST1) kullanılıyorsa bu durumda SPN kayıtları şu şekilde olacaktır.

setspn -Q MSSQLSvc/MySQLServer:INST1

setspn -Q MSSQLSvc/MySQLServer.MyDomain.com INST1

SPN’lerin olması kadar doğru service account’u üzerinde olması da önemlidir. Örneğin şöyle bir senaryo düşünelim. SQL Server “A” service account’u ile çalışıyordu ve gerekli SPN’leri create etmiştik. Daha sonra service account’unu “B” yapıyoruz. Bu durumda SPN kayıtları var gözükecek ama yanlış service account’u üzerinde olduğundan dolayı Kerberos protokolü kullanılamayacaktır. Bu yüzden hem SPN’leri kontrol etmeli hem de aşağıdaki komut ile ilgili service account’una bağlı SPN’ler sorgulanmalı ve SQL Server service SPN’lerinin bu listede olduğu teyit edilmelidir.

setspn -L MyDomain\MyServiceAccount

SPN Nasıl Oluşturulur?

SQL Server service’i için SPN oluşturmanın 2 yolu vardır.

  • İlk yöntem SQL Server service account’una active directory üzerinden “read servicePrincipalName” ve “Write servicePrincipalName” yetkilerinin verilmesidir. Bu yetkiler verildikten sonra service restart edilir. Service açıldığında otomatik olarak SPN kaydı oluşturulur. Service kapandığında ise ilgili SPN kaydı otomatik olarak silinir.

    SQL Server service account’una nasıl yetki verileceği ilgili şu dokümandan bilgi alabilirsiniz. (http://support.microsoft.com/kb/319723 - Step 3)

    Örnek bir ekran görüntüsü aşağıdaki gibidir.

    image
  • İkinci yöntem ise SPN’lerin manuel olarak yönetilmesi yani create, drop edilmesidir. Bu yöntem her ne kadar best practice olsa da sıkı bir şekilde yönetilmesi gereken bir konudur. Yanlış SPN’lerin olması ya da SPN’lerin hiç olmaması Kerberos kullanımını imkansız hale getirecektir.

 

Manuel SPN Oluşturma

Manuel olarak SPN oluşturmak için aşağıdaki komutu kullanabilirsiniz.

setspn -a mssqlsvc/MySQLServer.MyDomain.com:1433 MyDomain\MyServiceAccount

Bu komutun aynı zamanda MySQLServer ve MySQLServer.MyDomain.com için de çalıştırılması gerekmektedir.

Daha önce de dediğim gibi security açısından servis account’una SPN create/drop hakkı verilmektense SPN’lerin yukarıdaki şekilde manuel olarak create edilmesi best practice’dir

SPN Silme

SPN’leri manuel olarak silmek için aşağıdaki komutu kullanabilirsiniz.

setspn -d mssqlsvc/MySQLServer.MyDomain.com:1433 MyDomain\MyServiceAccount

Gene gerekiyorsa bu komutu MySQLServer ve MySQLServer.MyDomain.com için de çalıştırabilirsiniz.

Sonuç

SQL Server’da windwos authentication bağlantıların Kerberos protokolü üzerinden kurulması önemli bir security best practice’idir. Bağlantılar periyodik olarak kontrol edilmeli, eğer Kerberos yerine NTLM protokolü görülüyorsa SPN’ler kontrol edilmelidir.

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Bugün uzun zamandır yazmayı düşündüğüm çok önemli bir konu & best practice üzerine konuşuyor olacağız.

[more]

Virtual Log File (VLF) Nedir?

Virtual Log File transaction log dosyası içerisindeki transactionları saklayan mantıksal parçalardır. Bir database’in sahip olduğu VLF’lere DBCC LOG_INFO() komutu ile bakılabilir.

image

Örneğin benim sistemimde bulunan AdventureWorks veritabanı 1 transaction log dosyası içerisinde 444 virtual log file içermekte.

Yeni Bir VLF Nasıl Eklenir?

Transaction log dosyası üzerinde yapılan her auto growth ya da manuel büyütme işleminde yeni 1 ya da 1’den fazla VLF eklenir. Kaç tane VLF’nin ekleneceği büyümenin boyutuna bağlıdır. Buna göre;

  • Büyüme 64 MB’a kadar ise 4 VLF
  • 64 MB ile 1 GB arasında ise 8 VLF
  • 1 GB’dan fazla ise 16 VLF eklenir

Örneğin eğer transaction log file’ı 512 MB büyütürseniz (yukarıdaki listede 2. sıraya denk gelmekte) bu durumda 8 VLF eklenecek ve her VLF’nin boyutu 512/8 = 64 MB olacaktır.

Dolayısıyla Auto growth ya da manuel büyütme boyutlarınız ne kadar küçükse (bildiğiniz gibi default auto growth değeri log dosyaları için 1 MB’dır) VLF sayınız o kadar fazla olacaktır.

 

Çok Fazla VLF’ye Sahip Olmanın Veritabanına Etkisi Nedir?

Transaction log dosyasındaki VLF sayısı 2 farklı açıdan bizi etkiler;

  • Her database recovery işleminde (örneğin restart ya da failover sonrası) veritabanı recovery edilirken redo ve undo operasyonları işletilir. Redo operasyonu başlamadan transaction log dosyasındaki bütün VLF’ler okunur. VLF sayısı arttıkça bu okuma süresi uzayacak ve dolayısıyla recovery süresi yani veritabanın ayağa kalkma süresi uzayacaktır
  • VLF sayısı arttıkça database üzerinde yapılan DML (insert-update-delete) işlemlerinin performansı etkilenir. Şu blog post’ta bu konu çok güzel bir şekilde analiz edilmiş. Buna göre diğer özellikleri aynı ama birinde 20.000 diğerinde 16 VLF bulunan 2 veritabanı üzerinde yapılan insert-update-delete işlemlerinin süresi aşağıdaki gibidir.

    image

    Tablodan da rahatça anlaşılacağı gibi VLF sayısı arttıkça DML performansı kötü yönde etkilenmektedir.

 

VLF Best Practice’i Nedir?

VLF sayısı için genel kabul görmüş best practice değeri her transaction log file için 1000’dir. Fakat veritabanı boyutuna göre bu best practice değeri azaltılabilir. Örneğin 1000 best practice değeri 300 GB’lık bir transaction log’a sahip veritabanı için anlamlıyken 2 GB transaction log dosyasına sahip bir veritabanında çok daha az sayıda VLF’e sahip olunması gerekir.

Tabii burda göz önünde bulundurulması gereken bir başka best practice ise çok büyüt boyutlarda VLF’lere sahip olunmamasıdır. Bu konuda Kimberly L. Tripp (blog | twitter) şu blog post’ta VLF dosyaları için maksimum 512 MB boyutuna sahip olunmasını tavsiye etmektedir. (512 MB’lık VLF’lere sahip olmak için büyümeler 8 GB’lık chunk’lar halinde yapılmalıdır.)

Bu bölümde son olarak SQL Server 2012 ile beraber gelen VLF kontrolünden bahsetmek istiyorum. Database’in her recovery işleminde (örneğin restart) bütün database’lerin VLF sayıları kontrol edilir ve eğer 1000’den fazla VLF’e sahip olan bir veritabanı var ise SQL Error Log’da aşağıdaki şekilde loglanır.

Database VLF_DB has more than 1000 virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.

VLF Sayısına Nasıl Bakılır?

Makalemin başında da bahsettiğim gibi bir database’in sahip olduğu VLF’lere ve sayısına DBCC LOGINFO() komutu ile bakılabilir.

 

VLF Sayısı Nasıl Azaltılır?

Daha önce de bahsettiğim gibi çok fazla VLF’e sahip olmanın en büyük sebeplerinden biri uygun bir auto growth değerinin kullanılmıyor olmasıdır. Bu yüzden ilk yapılması gereken daha sonraki büyümeleri kontrol altına almak adına uygun bir auto growth değeri verilmesidir. Log file’lar için 512 Mb ya da 1 GB’lık auto growth değerleri kullanılabilir.

Peki şu anki VLF sayısını nasıl düşürebiliriz. Bunun için benim uyguladığım adımlar aşağıdaki gibi;

  • İlk olarak log file ihtiyaç analizi yani ne kadarlık bir log file ihtiyaç olduğu belirlenmelidir. Diyelim ki 100 GB’lık bir log file’a ihtiyacımız olsun.
  • Daha sonra log file olabildiği en küçük değerine kadar shrink edilip küçültülmelidir. (Tabii ki bu operasyon mesai saatleri dışında yapılmalıdır.)
  • Akabinde log file büyük chunk’lar halinde büyütülmelidir. Daha öncede söylediğim gibi büyütmeyi maksimum 8 GB’lık chunk’lar halinde yapabilirsiniz. Dolayısıyla database 8 – 16- 24 olacak şekilde büyütülmelidir. (Daha küçük log file’a sahip olan database’ler için daha küçük chunk değerleri kullanılabilir)
  • Büyütme işlemi 100 GB’a erişilene kadar devam ettirilir.

 

VLF konusu oldukça az bilinen ama çok önemli best practice’lerden biridir. Bu yüzden periyodik olarak database’lerin VLF sayılarını kontrol edip best practice’lerin üzerinde VLF’e sahip olan database’lerde yukarıdaki işlemleri yapmanızı kesinlikle tavsiye ederim.

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan