31 Ocak 2017 Salı

Ocak ayında oluşturduğum Microsoft SQL Server hata kayıtları

Sizlerle Ocak ayında oluşturduğum SQL Server'a ait hata (bug) kayıtlarını paylaşmak istiyorum. Ocak ayı hata kayıtları açısından biraz yoğun geçti.

Hata 1:
Aşağıdaki hata kaydı, SQL Server Management Studio'nun yeni versiyonlarında Task List'in hatalı oluşuyla ilgili. Hatalı derken kastettiğim şu, mesela yeni görev oluşturma düğmesi ve görevin türünün belirlendiği aşağı açılır kutu Task List penceresinde görünmüyor.

https://connect.microsoft.com/SQLServer/feedback/details/3118202/task-list-seems-incomplete


Hata 2:
Aşağıdaki diğer hata kaydı da veritabanınızı yerel sunucunuzda oluşturup, dosyalarını Microsoft Azure Blob Storage'da sakladığınızda oluşuyor. Hata, veritabanınız için oluşturduğunuz Database Snapshot'tan dönüş yapmaya çalıştığınızda oluşuyor. Aynı senaryo, veritabanı dosyaları yerel sunucuda konumlandığında oluşmuyor. İpucu: Database Snapshot'tan nasıl geri dönüş yapılacağı da ayrı bir blog konusu.

https://connect.microsoft.com/SQLServer/feedback/details/3117785/reverting-from-a-database-snapshot-when-database-files-on-azure-blob-storage


Hata 3:
Bu hata da Transparent Data Encryption özelliği etkinleştirildiğinde ve kapatıldığında SQL Server 2014 SP2 ve SQL Server 2016 SP1'deki sys.databases ve sys.dm_database_encryption_keys catalog view'lerinin farklı sonuçlar döndürmesiyle ilgili.

https://connect.microsoft.com/SQLServer/feedback/details/3118734/tempdb-looks-encrypted-but-it-shouldnt-have-been

Güncelleme 01.02.2017: Ben 13 Ocak tarihinde bu konuda yukarıdaki hata kaydını açtıktan sonra Microsoft'tan Bob Ward yine bu konuda 27 Ocak tarihinde bu konuyu incelemiş. Merak edenler yazıya buradan ulaşabilir.

Eğer açtığım hata kayıtlarına oy vermek isterseniz, bir Microsoft hesabı açmanız yeterli.


Ekrem Önsoy

23 Ocak 2017 Pazartesi

Başvuru kaynağı: SQL Server bağlantı sorunları ve açıklamaları

Yazının ilk yayınlandığı tarih: 23 Ocak 2017
Son güncellenme tarihi: 2 Şubat 2017, v1.1
Yazının konusu: SQL Server bağlantı sorunları ve açıklamaları
Yazının kapsamı: Bu dokümanın kapsamı Database Engine servisiyle sınırlıdır, versiyon bağımsızdır ve Microsoft Azure bağlantı sorunlarını da kapsar.
Yazar: Ekrem Önsoy


SQL Server Bağlantı Sorunları ve Açıklamaları 


Microsoft SQL Server Forumlarında ve daha birçok farklı platformda bazı sorular tekrar tekrar soruluyor. Böyle bir yazı yazarak sık soru gelen konu başlıklarından biri olan SQL Server'a bağlantı ile ilgili sorunların nasıl çözüleceğine dair bilgi ve tecrübeleri derli toplu bir doküman ile paylaşmak istedim. SQL Server'ın farklı versiyonlarındaki değişiklikler, çeşitli güncellemeler ve yeni sorunlar için bu yazıyı zaman zaman güncelleyeceğim. Bu sayede bu yazımın SQL Server'a bağlantı sorunlarının çözümü için farklı platformlarda genel bir referans kaynağı olmasını hedefliyorum.

SQL Server'a bağlanamamakla ilgili birçok sorun senaryosu ve hata mesajı mevcut. Birçok farklı mesaj, farklı bir sorunun sonucunda oluşuyor. Bu nedenle SQL Server'a bağlanma sorununu tek bir senaryoyla veya hata mesajıyla değil, farklı senaryo ve hata mesajlarına göre ayrı ayrı açıklayacağım.

Hataların açıklamalarına ve sorunların çözümüne geçmeden önce, bu süreçte kullanılacak bazı temel kavram ve araçlardan bahsetmek istiyorum. Bu kavram ve araçları sorun çözümlerinde başvuru kaynağı olarak göstereceğim ve bunu yaparken madde numaralarını da kullanacağım.

Notlar: 

  • Zaman zaman bazı terimleri İngilizce kullanacağım, çünkü kullanacağınız uygulamalarda karşınıza çıkacak olan ilgili terim bu dilde olacak, bir de çevirme ve tahmin işleriyle boğuşmak istemezsiniz diye düşünüyorum.
  • Hata mesajları şu anda sadece İngilizce, zamanla Türkçelerine ulaştıkça ilgili senaryoya hata mesajının Türkçesini de ekleyeceğim.
  • Güncelleme 03.02.2017: Bu konuda 27 Ocak tarihinde Microsoft tarafından da kapsamlı bir makale (İngilizce) yayınlandı. Bu makaleye de buradan ulaşabilirsiniz.


1.KAVRAMLAR

1.1 Instance / Database Engine

Bu yazıda sık sık "Instance" ve "Database Engine" kavramları kullanılacaktır. Bir Database Engine Instance'ı, bir işletim sistemi altında çalışan "sqlservr.exe" uygulamasının bir kopyası demektir. Her Database Engine Instance'ı birkaç sistem veritabanını, bir veya daha fazla kullanıcı veritabanını yönetir. Her sunucuda Database Engine servisinin birden fazla Instance'ı, yani kopyası olabilir. Uygulamalar veritabanlarında işlem yapabilmek için ilgili Database Engine Instance'ına bağlanırlar. 

Database Engine servisi 1.2 numaralı maddedeki ekran görüntülerinde de gösterilen ve etrafı kırmızı ile çizilen servistir. Bundan hariç olarak aynı veya farklı Instance adıyla Analysis Services, Reporting Services, Integration Services ve benzeri başka servisler kurulabilir. SQL Server 2000'den itibaren bir işletim sistemine birden fazla SQL Server Instance'ı kurulabilir.

Instance tipleri ikiye ayrılır ve farklarının ayrıntıları aşağıda 1.2 numaralı maddede açıklanmıştır.

1.2.Default Instance / Named Instance

SQL Server ürününü kurulum esnasında iki şekilde, Default Instance veya Named Instance olarak kurabilirsiniz. Her bir sunucuda, yani işletim sistemi düzeyinde sadece 1 tane Default Instance olabilir. Eğer aynı sunucuya birden fazla SQL Server kurulumu yapılacaksa ve zaten bir Default Instance kurulu ise diğer Instance'ların Named Instance olması gerekir.

Bir SQL Server Instance'ı kurulduktan sonra sunucu adı değiştirilebilir, fakat Instance adı değiştirilemez. Bir kurulumu Default Instance olarak yaptıysanız bunu Named Instance'a çeviremezsiniz veya tam tersini yapamazsınız. Instance adını değiştirmek için kurduğunuz Instance'ı silmeniz ve tekrar kurmanız gerekmektedir.

Default ve Named Instance tipleri arasındaki temel farklar şunlardır:
  • Default Instance varsayılan olarak Static Port kullanır, yani kullandığı port numarası sabittir, birisi elle değiştirmediği sürece değişmez.
  • Default Instance'ın varsayılan port numarası 1433'tür.
  • Default Instance'ın servis adı MSSQLSERVER'dır. SQL Server Configuration Manager'da (2.1) aşağıdaki gibi görünür:
  • Bir Default Instance'ın SQL Server Configuration Manager'daki (2.1) görünümü.
    Not: Bu ekran görüntüsünde Default Instance olan bir Database Engine servisi çalışır (Running) durumdadır.
  • Bir Default Instance'a bağlanmak için aşağıdakileri "sunucu adı" olarak kullanabilirsiniz:
    • Bilgisayar adı,
    • Bilgisayarın yerel IP'si,
    • localhost (eğer Instance'a bağlantı, kurulumla aynı sunucu üstünden yapılacaksa),
    • 127.0.0.1 (eğer Instance'a bağlantı, kurulumla aynı sunucu üstünden yapılacaksa).
  • Named Instance varsayılan olarak Dynamic Port, yani servis başlangıcında müsait olan herhangi bir port numarasını kullanır.
  • Named Instance'lar her yeniden başlatıldıklarında port numaraları değişir.
  • Bir Named Instance'ın adının biçimi şöyledir: Instance adı>. 
SQL Server Configuration Manager'da (2.1) aşağıdaki gibi görünür:
"SQL2014_DEV" adındaki bir Named Instance'ın SQL Server Configuration Manager'daki (2.1) görünümü.
Not: Bu ekran görüntüsündeki SQL2014_DEV isimli Instance'a ait Database Engine servisi durmuş (Stopped) durumdadır.

  • Named Instance'lara bağlanmak için aşağıdakileri "sunucu adı" olarak kullanabilirsiniz.
    • Bilgisayar adı ve Instance adını, örnek: CONTOSO\SQL01. Bu örnekte "CONTOSO" bilgisayarın/sunucunun adı, "SQL01" de Instance'ın adı.
    • Bilgisayarın/sunucunun IP'si + Instance adı, örnek: 192.168.1.205\SQL01.
Güncelleme 02.02.2017: SQL Server'a bağlanmak için kullanılabilecek birçok yönteme dair örnek kaynağa (İngilizce) buradan ulaşabilirsiniz.

1.3.TCP/IP Protokolü - Port

TCP/IP protokolü etkin olan her SQL Server Instance'ı ayrı bir port numarası kullanır. Bu port SQL Server Instance'ı ile istemci arasındaki veri alışverişi için kullanılır. İster Default Instance ister Named Instance olsun, port numaraları kurulum sonrasında SQL Server Configuration Manager (2.1) kullanılarak değiştirilebilir. Bu değişiklik SQL Server Database Engine servisinin yeniden başlatılmasını gerektirir, aksi takdirde değişiklik devreye girmez.

SQL Server Express Edition kurulumlarında varsayılan olarak TCP/IP protokolü devre dışı bırakılır.

Herhangi bir Edition'dan olan SQL Server Instance'ı için TCP/IP protokolünü etkinleştirmek isterseniz, ki uzaktan bağlanmak istediğiniz bir SQL Server Instance'ı için TCP/IP veya Named Pipe protokolü etkin olmalıdır, aşağıda gösterildiği gibi SQL Server Configuration Manager (2.1)'ı kullanabilirsiniz.

SQL Server Configuration Manager (2.1) ile TCP/IP protokolünü etkinleştirme. Değişikliğin devreye girebilmesi için Database Engine servisinin yeniden başlatılması gerekmektedir.

Protokoller hakkında daha fazla bilgi için SQL Server 2005: Bağlantı Protokolleri başlıklı yazımı okuyabilirsiniz. Şu anda kullandığınız SQL Server'ın versiyonu muhtemelen daha yeni olacaktır, ama merak etmeyin protokoller hala aynı.

İlgili Database Engine servisinin o anda hangi portu kullandığını öğrenmek isterseniz Error Log (2.3) dosyasını veya Windows Application Event Log (2.4)'u kullanabilirsiniz.

  • Error Log (2.3):
  • Database Engine Instance'ının Error Log dosyasının içeriğinden port numarasını bulma.


2.ARAÇLAR

2.1.SQL Server Configuration Manager 

Bu araç, SQL Server kurulumuyla birlikte gelir. SQL Server 2005'ten beri vardır. Bu araç ile daha birçok işlemin yanısıra özetle SQL Server servislerinin durumları gözlemlenebilir ve değiştirilebilir, Database Engine servisinin başlangıç parametreleri değiştirilebilir, servis kullanıcı ayrıntıları değiştirilebilir, bağlantı protokolleri (TCP/IP, port gibi) ayarlanabilir ve takma isimler oluşturulabilir.

2.2.SQL Server Browser Service

Bir uygulamanın Connection String'inde port numarası belirtilebilse de, eğer bir SQL Server Instance'ı Dynamic Port kullanmak üzere ayarlanmışsa SQL Server Database Engine servisi yeniden başlatıldığında port numarası değişecektir. Eğer Browser Service çalışıyorsa, başlatıldığında UDP port 1434'ü sahiplenir, kayıt defterindeki (Registry) ilgili anahtarları okur ve sunucu üstündeki tüm SQL Server Instance'larını kullandıkları portlarla birlikte belirler. Bir sunucuda bir veya daha fazla ağ kartı varsa SQL Server Browser servisi SQL Server için karşılaştığı ilk etkinleştirilen portu seçer.

Bir SQL Server istemcisi (mesela bir uygulama) SQL Server kaynaklarına erişmek istediğinde, istemci ağ kütüphanesi 1434 portunu kullanarak sunucuya mesaj yollar. SQL Server Browser servisi ulaşılmak istenen Instance'ın TCP/IP portuyla karşılık verir. İstemci uygulamasındaki ağ kütüphanesi ulaşılmak istenen SQL Server Instance'ının portunu kullanarak sunucuya talebini göndermek suretiyle bağlantıyı tamamlar.

2.3.SQL Server Error Log

SQL Server Database Engine servisinin kendi Error Log dosyası vardır. Bunu bu şekilde belirtmemin nedeni, SQL Server Agent servisinin de kendi Error Log dosyasının olmasıdır, bunlar birbirine karıştırılmamalı. Bu yazı kapsamında ilgili olanı SQL Server Database Engine servisinin Error Log dosyasıdır. Bu dosyanın konumu, yaptığınız kuruluma göre değişir. Aşağıda belirtilen yol biçimi SQL Server Instance'ınızın adına göre (Default Instance mı, Named Instance mı olduğuna göre) ve SQL Server'ın versiyonuna göre değişir.

Error Log dosyasının yolunun biçimi şöyledir:
Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG

Her SQL Server Database Engine servisinin yeniden başlatılmasıyla Error Log dosyası yenilenir. Varsayılan olarak geriye dönük 6 tane Error Log dosyası tutulur. O anki en güncel Error Log dosyası, uzantısı olmayan dosyadır, sadece ERRORLOG adıyla oluşturulur. Bundan sonraki en güncel dosyanın adı ERRORLOG.1, sonraki ERRORLOG.2 diye gider. Error Log dosyaları düz metin dosyalarıdır ve mesela Notepad ile birlikte açılabilir.

2.4.Event Viewer (Olay günlüğü) 

Windows işletim sisteminde System ve Application Event Log'ları (daha başkaları da var, ama bu yazı kapsamında ilgili dosyalar bunlar) vardır. Kural olarak şu unutulmamalıdır, SQL Server Error Log'a işlenen tüm hatalar Windows Application Event Log'una da işlenir. Bazı hatalar vardır ki, bunlar System Event Log'a işlenir. Bu nedenle bir hata soruşturması yürütürken iki log dosyasına da bakmak iyi bir yöntemdir.

2.5Ping komutu (Komut İstemcisi)

Ping komutu, TCP/IP protokolünü kullanan diğer sunucuya Internet Control Message Protocol (ICMP) Echo Request mesajları göndererek IP seviyesinde erişim olup olmadığını kontrol eder.

Hem karşı bilgisayar adına, hem de IP'sine karşı Ping komutunu çalıştırabilirsiniz. Eğer Ping komutu karşı bilgisayarın IP'si için başarılı sonuç döndürüyorsa, fakat bilgisayar adı için başarısız sonuç döndürüyorsa, o zaman isim çözme ile ilgili sorun yaşıyor olabilirsiniz. 

Ping komutu Komut İstemcisi'den (Command Prompt) aşağıdaki biçimde çalıştırılır:

C:\>ping

veya

C:\>ping

Örnek:

C:\>ping SQL01.contoso.com

veya

C:\>ping 192.168.1.66

2.6Telnet (Komut İstemcisi)

Telnet komutunu, uzaktaki bir sunucudaki bir portun açık olup olmadığını kontrol etmek için kullanabilirsiniz.

Bazı sistemlerde Telnet istemcisi kurulu olmayabilir, böyle durumlarda önce Telnet istemcisini kurmanız gerekir.

Telnet komutu Komut İstemcisi'den (Command Prompt) aşağıdaki biçimde çalıştırılır:

C:\>telnet

veya

C:\>telnet

Örnek:

C:\>telnet SQL01.contoso.com  

veya

C:\>telnet 192.168.1.66  


3.SORUN SENARYOLARI

3.1.Senaryo

Alınan hata mesajı:

"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Shared Memory Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

The system cannot find the file specified"

veya

"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 67)

The network name cannot be found"

Açıklama:

İlk hata, yerel bir SQL Server Instance'ına bağlanmaya çalıştığınızda, ikinci mesaj da uzaktaki bir SQL Server Instance'ına bağlanmaya çalıştığınızda oluşur.

Eğer yukarıdaki gibi hata mesajları alıyorsanız ve bağlanmaya çalıştığınız sunucu uzaktaysa ilk yapmanız Ping (2.5) komutuyla sunucuya ulaşıp ulaşamadığınızı kontrol etmektir. Çünkü bırakın SQL Server servisini, sunucunun kendisi de kapalı olabilir. Bununla birlikte, bazı sunucular çalışıyor olsa da Ping komutuna cevap vermeyecek şekilde ayarlanmış olabilirler, bu bilgiyi de bir kenarda tutmakta fayda var.

Eğer sunucunun açık olduğundan eminseniz ve yukarıdaki gibi hata mesajları alıyorsanız SQL Server Configuration Manager (2.1)'ı açın ve SQL Server Database Engine servisinizin çalışıp çalışmadığını kontrol edin.

Database Engine servisinizin çalışır durumda (Status = Running) olduğundan emin olun.

Eğer SQL Server Configuration Manager (2.1)'ı açtığınızda "SQL Server Services" penceresinde hiçbir servis adı görmüyorsanız ya SQL Server Configuration Manager (2.1)'ın başka bir versiyonuyla SQL Server Database Engine'in farklı bir versiyonuna bakmaya çalışıyorsunuz ya da bu sunucuya henüz bir SQL Server Instance'ı kurulmamış demektir.

SQL Server Configuration Manager (2.1)

3.2.Senaryo

Alınan hata mesajı:

"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)"

Açıklama:

Bu hata mesajı 3.2'de anlatılan hata mesajına benzerdir, ama sizi yanıltmasın, hata mesajının sonundaki parantezlerin içerisindeki mesajlar farklıdır.

Eğer böyle bir hata mesajı alıyorsanız bağlanmaya çalıştığınız SQL Server Instance adını yanlış yazıyorsunuz demektir. Örneğin eğer varolan Instance adı "CONTOSO\SQL01" ise ve ben Connection String'ime veya SQL Server Management Studio'daki "Server name"e "CONTOSO\SQL2" yazarsam o zaman bu hata mesajını alırım.

Kullandığınız Instance adının doğruluğunu teyit edin. Bunun için SQL Server Configuration Manager (2.1)'ı kullanabilirsiniz.

3.3.Senaryo

Alınan hata mesajı:

"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - The remote computer refused the network connection.) (Microsoft SQL Server, Error: 1225)"

Açıklama:

Bu sorun da ilk bakışta önceki sorunlara benzer, fakat yine hata mesajının sonundaki parantez içerisindeki ayrıntılarla farklılaşır.

Eğer böyle bir hata mesajıyla karşılaşıyorsanız ilk yapmanız gereken kullandığınız veya kullanmanız gereken ama kullanmadığınız port (1.2) numarasından emin olmaktır. Misal Database Engine Instance'ınız 1533 numaralı portu kullanıyorsa ve Browser Service (2.2) çalışmıyorsa ve Connection String'inizde veya SQL Server Management Studio'daki "Server name"de bunu belirtmiyorsanız bu hata ile karşılaşırsınız.

Olması gereken Connection String biçimi (Server kısmına odaklanın lütfen)

"Server=, ; Database=; Trusted_Connection=True;"

Örnek:
"Server=CONTOSO\SQL01, 1533; Database=Veritabanim; Trusted_Connection=True;"

Port'un açık olup olmadığını Telnet (2.6) komutuyla da kontrol edebilirsiniz.

3.4.Senaryo

Alınan hata mesajı:

"A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)"

Açıklama:

Diyelim ki uzaktaki bir SQL Server Instance'ına SQL Server Management Studio veya başka bir uygulama ile bağlantı kurdunuz ve işlemlerinizi gerçekleştiriyorsunuz. Eğer bağlantınız aşağıdaki gibi nedenlerden ötürü sonlandırılırsa, bu hata ile karşılaşırsınız:

  • Oturumunuz SQL Server Instance'ında KILL komutu ile sonlandırılırsa,
  • Ağ bağlantınız veya uzaktaki SQL Server Instance'ının ağ bağlantısı koptuysa,
  • Oturumunuz SQL Server Database Engine tarafından sunucudaki yoğunluk ve kaynak yetmezliği nedeniyle sonlandırılırsa.

Böyle bir sorun ile karşılaştığınızda hizmet almaya çalıştığınız SQL Server'ın yöneticileriyle irtibat kurup oturumunuzun kim tarafından ve neden sonlandırıldığını sorgulayabilir veya ağ bağlantılarınızı kontrol edebilirsiniz.

3.5.Senaryo

Alınan hata mesajı:

"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 5)

Access is denied"

Açıklama:

Şayet bu hata mesajıyla karşılaştıysanız, bağlanmaya çalıştığınız uzaktaki SQL Server Instance'ının TCP/IP protokolü devre dışıdır. Bu protokolü etkinleştirme hakkında daha fazla bilgi için lütfen 1.2TCP/IP Protokolü - Port başlıklı maddeye başvurun.

Port'un açık olup olmadığını Telnet (2.6) komutuyla da kontrol edebilirsiniz.

3.6.Senaryo

Alınan hata mesajı:

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)

Açıklama:

Böyle bir sorun yaşıyorsanız muhtemelen sunucuyla bağlantı kurmuş ve işlem yapıyorsunuzdur, fakat bu sürecin bir aşamasında bu hata ile karşılaşmışsınızdır.

Şahsen ben bu hata ile bugüne kadar hep ağ bağlantısı kopmalarında karşılaştım. Örneğin farklı ağlardaki iki sunucu arasında Log Shipping ile dosya kopyalama işlemi yapıyorsunuz diyelim, eğer işlem sırasında sunucular arasındaki ağ bağlantısı koparsa böyle bir hata ile karşılaşabilirsiniz.

Eğer sunucularınızdan birisi buluttaysa veya ayrı bir bölgedeyse, örneğin bir sunucu Hollanda'da, diğeri Amerika'da gibi ve uygulamalarınızın bu mantıkla hata almadan çalışmaya devam ediyorsa ve uygulamayı siz geliştiriyorsanız o zaman uygulamanızı bağlantı koptuğunda kaldığı yerden devam edecek bir mantıkla yazmanız gerekiyor. Bağlantı gittiğinde ve bu hatayı aldığınızda bunu handle edebilmeniz gerekiyor. Çünkü böyle yapılarda bağlantı kopma olasılıkları çeşitli nedenlerle (bakım nedeniyle, ağdaki gel gitler nedeniyle gibi) kaçınılmazdır ve tekrarlar. Bu sorunla nasıl yaşayacağınızı öğrenmeniz gerekiyor.

Şayet yukarıdaki paragrafta bahsettiğim gibi farklı bölgeleri barındıran bir yapı yoksa ve birbirine yakın veya dahili ağlarsa bunlar, o zaman (varsa) ağ uzmanlarınızla bu konuyu konuşmalısınız ve ağınızın istikrarlı çalışmadığını kendilerine bildirmelisiniz. Muhtemelen ilk etapta itiraz edebilirler, ama pes etmeyin, bu sorun ağ bağlantı kopmasıyla ilgili bir sorundur.

3.7.Senaryo

Alınan hata mesajı:

Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. (Microsoft SQL Server, Error: 18452)

Açıklama:

Uzaktaki SQL Server Instance'ınıza bağlanırken böyle bir hata alabilirsiniz. Bu bir bağlantı hatası değil, kullanıcı yetkilendirme hatasıdır. Hata mesajında da belirtildiği gibi, bağlantı için kullandığınız Windows Login'i güvenli bulunmamaktadır.

Bu sorun, SQL Server'ın bulunduğu karşı Domain ile yerel bilgisayarınızdaki Domain veya Windows ortamı ile bir güven ilişkisi bulunmadığında oluşur. Bir güven ilişkisi olmadığı için, karşı tarafta bulunan SQL Server Instance'ı, yerel bilgisayarınızdan karşı SQL Server'a giriş yapmak için kullanmaya çalıştığınız kullanıcıyı tanımayacaktır.

Bu sorunu çözmek için ya bağlantı için SQL Login kullanmalısınız ya da Domain'ler arasında güven ilişkisini oluşturmalısınız.

3.8.Senaryo

Alınan hata mesajı:

Login failed for user 'test'. (Microsoft SQL Server, Error: 18456)

Açıklama:

SQL Server Instance'ınıza bağlanırken böyle bir hata alabilirsiniz. Bu bir bağlantı hatası değil, kullanıcı yetkilendirme hatasıdır. Bu hata mesajı güvenlik nedeniyle çok yüzeysel olarak oluşturulmaktadır. Kastettiğim şu, bu hata mesajından bu şekilde yalnızca "test" isimli kullanıcının SQL Server Instance'ına bağlanırken hata aldığı sonucuna varabiliriz. Fakat bunun binbir çeşit türlü nedeni olabilir.

Bu farklı nedenleri incelemek için 2.3 ve 2.4 numaralı maddelerde bahsedilen Log dosyaları incelenmelidir. Log dosyalarında bu hatanın hemen altında hataya dair ayrıntı bulunacaktır. Örneğin Login'in şifresinin yanlış girilmesi, Login'in Disabled rudumda oluşu gibi.

SON

Kaynaklar:

Ping, https://technet.microsoft.com/en-us/library/bb490968.aspx
Telnet, https://technet.microsoft.com/en-us/library/bb491013.aspx

16 Ocak 2017 Pazartesi

Transparent Data Encryption ile tempdb ilişkisi

Transparent Data Encryption (TDE) özelliğini sanırım tüm SQL Server veritabanı yöneticileri duymuştur. SQL Server 2008 ile gelen bir özellik. Veritabanı dosyalarını ve yedek dosyalarını şifrelemek için kullanılan bir güvenlik yöntemi.

Geçenlerde ingilizce SQL Server Database Engine Microsoft forumunda bir kullanıcı test ortamındaki bazı veritabanları için TDE'yi etkinleştirdiğini fakat testlerden sonra TDE özelliğini ilgili veritabanlarından kaldırdığı halde tempdb'nin hala şifreli olduğunu ve bunu nasıl kaldıracağını sordu. Ben de test ortamımda bunun testini yaptım, kullanıcıya cevabını verdim ve bu konuda kısa bir blog yazısıyla sizlerle de paylaşmak istedim. Bu yazımda TDE'nin nasıl etkinleştirileceğine veya kullanılacağına değil, tempdb ile olan ilişkisine değineceğim.

"TDE veritabanı bazında etkinleştirilen bir özellik, tempdb ile ne alakası var?" diye soracak olursanız "sonuç itibariyle tempdb tüm veritabanları tarafından ve SQL Server tarafından kullanılan ortak bir alan, eğer bir veritabanını TDE ile şifreliyorsanız o zaman demek ki kayıtlar hassastır ve yanlış ellere geçmesini istemiyorsunuzdur, bu nedenle de verinin işlenme sürecinin bir kısmının şifresiz yapılmasını da istemezsiniz" derim. Bu iş bir bütün nihayetinde, bu nedenle TDE ile şifrelediğiniz bir veritabanındaki bir kod ile tempdb veritabanında temp bir tablodaki kayıtların da şifreli olması gerekir ve sonuç itibariyle SQL Server Instance'ında tek bir tane tempdb vardır. Bu nedenle bir SQL Server Instance'ında eğer bir tane veritabanını bile TDE ile şifrelerseniz, tempdb de otomatik olarak şifrelenir.

Peki SQL Server Instance'ınızda TDE ile şifrelenmiş veritabanlarını nasıl görebilirsiniz?

SELECT [is_encrypted], [name] FROM sys.databases WHERE [is_encrypted] = 1;
GO

Eğer SQL Server 2014 SP2 (ve muhtemelen bazı başka versiyonlar da) kullanıyorsanız "Ama sen daha az önce 'bir veritabanı TDE ile şifrelendiyse tempdb de şifrelenir' demiştin? Bu listede tempdb'yi göremiyorum?" diyebilirsiniz diye tedbirliyim, çünkü benim test ortamımda SQL Server 2014 SP2 var ve bende tempdb veritabanı yukarıdaki sorgu ile [is_encrypted] = 1 olarak görünmüyor... Bu hata için de açılan hata kaydı burada

Peki diyelim ki önceden TDE'yi etkinleştirdiğiniz, ama sonra herhangi bir sebeple bundan vazgeçip kaldırdığınız bir üretim ortamınız var. TDE'yi ilgili tüm canlı veritabanlarından kaldırdınız, peki ya tempdb? tempdb'nin şifrelemesi de kalktı mı dersiniz? Eğer SQL Server Database Engine servisinizi henüz kapatıp açmadıysanız kalkmadı efendim! "Peki yukarıdaki sorgu da işe yaramıyor, nereden anlayacağım bunu?" diye soruyorsunuz haliyle "aşağıdaki sorguyu kullanabilirsiniz" diyorum.

SELECT DB_NAME(database_id) AS [database_name], [encryption_state] FROM sys.dm_database_encryption_keys WHERE [encryption_state] = 3;

Eğer yukarıdaki sorgu sonucunda tempdb yoksa, o zaman demektir ki bu SQL Server Instance'ında TDE ile şifrelenmiş hiçbir veritabanı yoktur ve tempdb'niz de şifreli değildir. Eğer [encryption_state] = 1 olan veritabanınız varsa ise o zaman veritabanında Database Encryption Key var, ama veritabanı TDE ile aktif olarak şifrelenmemiş demektir. Eğer olur da daha farklı rakamlar görürseniz o zaman daha fazla bilgi için sizi dokümantasyona davet ediyorum.

Bunca curcunadan sonra gelgelelim "TDE'yi veritabanları için kapattıysak neden tempdb için bu kadar kaygılanalım ki?" sorusunun cevabına. Efendim baştan da dediğim gibi, tempdb ortak bir alan ve siz başka veritabanları için TDE'yi etkinleştirmemiş olsanız bile, hatta tüm veritabanlarından TDE'yi kaldırmış olsanız bile tempdb hala şifrelenmeye devam edecek, şayet SQL Server Database Engine servisi yeniden başlatılana kadar. Yani artık o SQL Server Instance'ında kaç tane veritabanınız varsa, tempdb'de ne kadar işlem yapılıyorsa, tüm o işlemler şifrelenerek yapılacak. Bunun tabii ki bir performans bedeli var. Durduk yere, TDE kullanmıyorken bu bedeli neden ödeyesiniz? Bu yazı da işte bu konuda sizleri uyarmak içindi.

Bu yazıyı yazdıktan sonra Microsoft Azure'da test amacıyla bulunan ve üstünde SQL Server 2016 SP1 olan sanal makinemde de aynı testi yaptım. Aynı test, SQL Server 2014 SP2 ve SQL Server 2016 SP1'de ayrı ayrı hataları (Bug) ortaya çıkardı. Microsoft'tan yapılan yorumlara göre bu hatalar "metadata" düzeyindeki hatalar. Yani [is_encrypted]'ın sonucu 0 olacağına 1 oluyor, ama aslında arkaplanda her şey düzgün çalışıyor şeklinde oldu. Bu konuda Microsoft'a açtığım hata kaydının ayrıntıların buradan ulaşabilirsiniz.

Sevgiler,
Ekrem Önsoy

10 Ocak 2017 Salı

Yedek alırken bütünlük kontrolü yapıyor musunuz?

Aralık ayı içerisinde 2 farklı yazı ile (biri ve diğeri) yedek almanın önemine değinmiştim. Bu yazı ile de, SQL Server'da yedek alırken kullanılabilecek önemli bir parametreye değinmek istiyorum.

Biliyorum, birçok kurumun maalesef hala belirlenmiş RPO ve RTO değerleri yok ve yedek alma işi sadece "yapılması gereken standart işlerden bir diğeri" gibi görülüyor. Bir felaket durumunda da tabiri caizse elde harddisk "verilerimizi nasıl kurtarabiliriz?" diye kapı kapı dolanan arkadaşları görüyoruz. Bu yazım ise tabii ki RPO ve RTO değerlerinin anlamını bilip, yedek almaya gerekli özeni gösteren kurum ve çalışanları için.

Konuya giriş yapmam için önece bazı terim ve kavramları izah etmem gerekiyor.

SQL Server'da veriler en küçük depolama birimi olan Page'lere kaydedilir. Page'ler diskte 8 kilobaytlık bir alan kaplar. Bir Page tablo yapınızda kullandığınız veritipleriniz ve verinize göre bir veya daha fazla kayıt içerebilir. SQL Server verileri Page'lere kaydettikten sonra otomatik ve rutin bir şekilde tekrar tekrar dönüp Page'lerin sağlık durumunu kontrol etmez ve Page'lerin sağlık ve bütünlük durumu fiziksel disk yapısındaki fiziksel bir sıkıntıdan, SQL Server'daki bir Bug'tan veya SQL Server'ın sağlıklı bir şekilde kapatılmaması gibi sebeplereden dolayı bozulabilir.

SQL Server 2005 ile birlikte Page'lerin veri bütünlüğünü kontrol seçenekleri arasına Checksum da katıldı. Öncesinde ise 2 seçenek vardı, ya bu kontrol hiç yapılmasın ya da Torn Page, bu seçenekler yeterince koruma sağlamıyordu, bu nedenle Checksum seçeneği geldi ve SQL Server 2005 ile birlikte varsayılan seçenek oldu. 

Page bütünlüğü için Checksum seçeneği kullanıldığında SQL Server tüm Page'in bütünlüğünü dikkate alarak bir Checksum hesaplar ve bunu da Page diske yazılırken Page'in içerisindeki Page Header'a "m_tornBits" etiketiyle kaydeder. Bu Page diskten okunurken Checksum tekrar hesaplanır ve "m_tornBits"teki değer ile (örnek değer: -1433260509) karşılaştırılır. Eğer eşleşme sağlanamazsa, o zaman SQL Server Error Log ve Windows Application Event Log'a 824 hata numarasıyla kaydedilir. Bu sayede üst seviye bütünlük kontrolü sağlanmış olur.

Torn Page'in yeterince koruma sağlamadığını söylemiştim, Checksum ile farkını belirginleştirmek için bir örnek vereyim. Sanırım birçoğunuz hayatınızın bir döneminde bir Hex Editor kullanmıştır. Örneğin Page doğrulama özelliği Torn Page olan bir sayfayı, ilgili veritabanını Offline duruma alarak bir Hex Editor ile değiştirebilirsiniz ve DBCC CHECKDB ile bütünlük kontrolü yapmadığınız sürece bu değişikliği kimse fark etmez. Fakat aynı Page Checksum doğrulamasıyla ayarlansaydı, Hex Editor ile değişiklik yapıp veritabanını tekrar Online duruma getirip ilgili kayıtları sorguladığınızda aşağıdaki gibi bir hata alırdınız:

Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x34bf84e5; actual: 0x31268142). It occurred during a read of page (1:654) in database...

"Peki tüm bunların yedekleme ile ne ilgisi var kardeşim?" deme noktasına getirdiysem sizi, elbet bir nedeni var. Açıklayayım efendim.

SQL Server 2014 ile birlikte yedek alırken Checksum kontrolü seçeneği de geldi. Bu özellik 2 şekilde kullanılabilir.

1- Instance düzeyinde, "sp_configure" ile aşağıdaki gibi etkinleştirilebilir.

EXEC sp_configure 'backup checksum default', 1;
RECONFIGURE
GO

2- Her bir yedek alma komutuyla birlikte aşağıdaki gibi kullanılabilir:

BACKUP DATABASE X TO DISK = N'xxx' WITH CHECKSUM

Eğer birinci seçeneği kullanırsanız, veritabanı yedekleriniz varsayılan olarak Checksum kontrolü yapılarak alınır ve ilgili sistem tablolarında da kayıtları aşağıdaki gibi görürsünüz:


Bu kullanım size pratikte yukarıda bahsini ettiğim örnekte olduğu gibi bütünlük uyuşmazlığı yaşanan durumlarda haberdar olmanızı sağlayacaktır.

Örneğin test ortamımda bir sayfasını kasten bozduğum bir veritabanım var. Bu veritabanımın yedeğini aşağıdaki komut ile aldığımda hiçbir hata ile karşılaşmıyorum:

USE [master]
GO
BACKUP DATABASE [AdventureWorks2012_corruptionTest] TO DISK = N'C:\temp\corrupt_db.bak';
GO

Fakat aynı yedeği aşağıdaki komut ile alırsam (veya yukarıda 1. seçenek olarak belirttiğim gibi bu özelli Instance düzeyinde etkinleştirirsem)

USE [master]
GO
BACKUP DATABASE [AdventureWorks2012_corruptionTest] TO DISK = N'C:\temp\corrupt_db.bak' WITH CHECKSUM;
GO

O zaman aşağıdaki gibi bir hata alıyorum:

Msg 3043, Level 16, State 1, Line 6
BACKUP 'AdventureWorks2012_corruptionTest' detected an error on page (1:11984) in file 'C:\temp\AdventureWorks2012_Data.mdf'.
Msg 3013, Level 16, State 1, Line 6
BACKUP DATABASE is terminating abnormally.

Sonuç itibariyle, bu tür sorunlardan en kısa sürede haberdar olmak için:
- Tüm veritabanlarınızın Page Verification özelliğinin CHECKSUM olduğundan,
- SQL Server 2014 ve üzeri versiyonlardaki Instance'larınızda "backup checksum default" özelliğini etkinleştirdiğinizden,
- Tüm kritik veritabanlarınız için düzenli olarak DBCC CHECKDB kontrolü yaptığınızdan emin olun.

Olası bir bütünlük sorunundan ne kadar erken haberdar olursanız veri kaybı yaşama olasılığınız o kadar düşük olur. Bu nedenle uygulamalarınızın ihtiyaçlarını da düşünerek olabildiğince çok güvenlik önlemi almanız gerekiyor. Böyle derken kastettiğim şu Checksum ve DBCC CHECKDB gibi uygulamaların elbette bir kaynak bedeli var, CPU ve IO yükünü arttırırlar. Hangi işlemi ne zaman ve nasıl yaptığınız çok önemli. Operasyonlarınızı da aksatmak istemezsiniz, güvenlikten de feragat etmek istemezsiniz. Yarın üzülmemek için kendi ortamınızda, kendi uygulamalarınızın ihtiyaçlarını ve donanım kaynaklarınızı da dikkate alarak testlerinizi yapmalı ve bu uygulamaları devreye almalısınız.

Ekrem Önsoy

3 Ocak 2017 Salı

Yanlış veritipi kullanımının maliyeti

Buruk girdiğimiz 2017'nin ilk yazısı...

Bir müşterimde tablolardaki Clustered Indeksler için Int veritipi yerine Uniqueidentifier veritipinin kullanıldığını gördüm. Önce, en iyi pratiklere göre hangi alanın Clustered Indeks olarak tanımlanabileceğini tekrar hatırlayalım:

- Kısa olmalı,
- Eşsiz değerleri barındırmalı
- Sıralı artan değerleri barındırmalı,
- Değeri değişmemeli,

Konuştuğumuz arkadaşım bana bu tasarım kararını edindikleri kaynaklara binaen aldıklarını söyledi. Bu kaynakların neler olduğunu henüz net olarak bilmiyorum; fakat bu fikir benim bildiğim, uyguladığım ve iyi çalıştığını gördüğüm iyi pratiklerin tam tersiydi. Bu nedenle bu fikre katılamadım, bununla birlikte test etmek istedim. Bu test sonuçlarını sizlerle de paylaşıyorum.

Hatırlatmak gerekirse, Int veritipindeki bir alanın diskte kapladığı alan maliyeti 4 bayttır, Uniqueidentifier alanınki ise 16 bayttır, yani 4 kat daha fazla!

Test için AdventureWorks2014 veritabanındaki Person.Person tablosunu kullandım. Bu tabloyu 2 kere kopyaladım. Birinde, aslındaki gibi BusinessIdentifyId alanını Clustered Indeks olarak tanımladım, diğerine ise Rowguid alanını Clustered Indeks olarak tanımladım. Tablolarda bu iki indeksten başka herhangi bir indeks yoktu.

Daha sonra 2 tablodan da aşağıdaki gibi 1000'er adet kayıt sorguladım:

SELECT TOP (1000) * FROM [dbo].[Person];

BusinessIdentityId alanının Clustered Indeks olduğu tablodan 1000 kaydın okunması için 80 Read yapıldı. Rowguid alanının Clustered Indeks olduğu tablodan 1000 kaydın okunması için ise 188 Read yapıldı. 2,35 kat daha fazla!

Daha sonra iki tabloda da hem Lastname ve Firstname'den oluşan, hem de ModifiedDate için ikişer tane Nonclustered Indeks oluşturdum. Bunun akabinde BusinessIdentityId alanının Clustered Indeks olduğu tablodaki toplam indeks boyutu 1,240kb, Rowguid alanının Clustered Indeks olduğu tablodaki toplam indeks boyutu ise 1,760kb oldu, bu da %42 kat daha fazla demek. Bunun nedeni ise, Clustered olan her tablodaki Nonclustered indekslerin Cluster Key'leri içermesidir.

Clustered Indeks olarak belirlenen alanların genelde Primary Key olduğunu ve ilişkili oldukları ikincil tablolarda da aynı alanların olduğunu hatırlarsak, maliyet iki katına çıkıyor. Join işlemlerinin de bu alanlar üstünden yapıldığını hatırlamakta fayda var, bu da ciddi bir maliyet.

Kayıtların Page'lerde daha fazla yer kaplaması, Page'lerin daha az sayıda kayıt tutabileceği anlamına gelir. Bu da daha fazla kayıt okumak için, daha fazla Page'in hem diskte hem de hafızada yer tutması gerektiği anlamına gelir. Bu da kaynak kullanımını verimsiz hale getirir, performans düşüklüğüne neden olur.

Bu örneğe göre eğer verinizi Int bir alanda saklamak varken (tabii ki iş gereksinimleri buna izin verdiği müddetçe) Uniqueidentifier veritipini seçerseniz, disk ve hafıza maliyetlerinizi ortalama 4 kat arttıracaksınız demektir. Tasarım yaparken bunu bilmekte ve maliyet hesabını buna göre yapmakta büyük fayda var.

Sevgiler,
Ekrem Önsoy