SQL Server Sistem Veritabanları

Windows işletim sistemi üzerine herhangi bir SQL Server Sürümü yüklediğinizde; sunucunun etkin bir şekilde çalışması için her birinin mevcut olması gereken beş adet birincil sistem veritabanı vardır.

Master, Model, MSDB vb. veritabanlarını SQL Server Management Studio’da ve çeşitli scriptlerde gördüm. Şu anda bu veritabanları hakkında sadece bu komut dosyalarını incelemeye dayanan oldukça basit bir bilgi setine sahibim. SQL Server bilgimi artırdıkça, SQL Server sistem veritabanları ve bunları bana günlük olarak yardımcı olması için nasıl kullanabileceğim hakkında daha fazla şey öğrenmekle ilgileniyorum. Bu veritabanlarıyla çalışırken pek çok tavsiyeleri olduğundan eminim ve bunlarla da ilgileniyorum.

Bir sorunu gidermeniz, SQL Server’dan veri toplamanız vb. gerektiğinde SQL Server veritabanlarını sağlam bir şekilde anlamak oldukça önemlidir. Bu makalemde SQL Server sistem veritabanlarının ana hatlarını verecek, ilişkisel veritabanlarında kullanılan temel nesneleri açıklayacak ve bazı yapılması gerekenlerin ana hatlarını verecektir. ve sistem veritabanlarıyla çalışırken yapılmaması gerekenlere değineceğim.

Microsoft SQL Server mimarisinde ,bir sunucu üzerinde çalışırken, kendi içinde ek bir takım veritabanlarına ihtiyaç duyar. Siz bilgisayarınıza SQL Server yükledikten sonra, otomatik olarak yüklenen bu veritabanları SQL Server’in çalışmasını devam ettirebilmesi için gereklidir. Yani bir veritabanı sunucusu kullanıcı veritabanlarını kontrol edebilmesi ve yönetebilmesi için de kendisi birtakım veri tabanlarına ihtiyaç duyar. Bu veritabanları genel olarak SQL Server Management Studio’nun açıldığında Databases -> System Databases bölümünden görüntüleyebilirsiniz.

SSMS arayüzünde yer alan bu veritabanlarından 5.’si olan Resource veritabanını direk bu arayüzden görüntüleyemezsiniz.

1.      Resources Database

Resource database, SQL Server’da bulunan tüm sistem nesnelerini içeren salt-okunur bir veritabanıdır. sys.objects gibi SQL Server sistem nesneleri, Resource veritabanında fiziksel olarak kalıcıdır, ancak mantıksal olarak her veritabanının sistem şemasında görünürler. Resource veritabanı, kullanıcı verilerini veya kullanıcı meta verilerini içermez.

Resource veritabanı, SQL Server’ın yeni bir sürümüne yükseltmeyi daha kolay ve daha hızlı bir prosedür haline getirir. SQL Server’ın önceki sürümlerinde, yükseltme gerekli bırakma ve sistem nesneleri oluşturma. Resource veritabanı dosyası tüm sistem nesnelerini içerdiğinden, yükseltme artık yalnızca tek resource veritabanı dosyasını yerel sunucuya kopyalayarak gerçekleştirilir.

Resource veritabanının fiziksel dosya adları, mssqlsystemresource.mdf ve mssqlsystemresource.ldf’dir. Bu dosyalar C:\Program Files\Microsoft SQL Server\MSSQL<sürüm>.<örnek_adı>\MSSQL\Binn\ konumunda bulunur ve bu dizinden başka bir yere taşınmamalıdır. Her SQL Server Instance içn bir ve yalnızca bir ilişkili mssqlsystemresource.mdf dosyası vardır ve çoklu SQL Server Instance yapısında bu dosyayı paylaşmaz.

SQL Server sürümünde yapılan yükseltmeler ve Service Pack ‘ler ile bazen BINN klasörüne yüklenen yeni bir resource veritabanı oluşabilir. Resource veritabanının konumunun değiştirilmesi desteklenmez veya önerilmez.

SQL Server’da resource veritabanını yedekleyemez. mssqlsystemresource.mdf dosyasını bir veritabanı dosyası yerine binary (.EXE) dosyası gibi ele alarak kendi dosya tabanlı veya disk tabanlı yedeklemenizi gerçekleştirebilirsiniz, ancak yedeklerinizi geri yüklemek için SQL Server’ı kullanamazsınız. mssqlsystemresource.mdf dosyasının yedek kopyasını geri yüklemek yalnızca manuel olarak yapılabilir ve güncel olmayan veya güvenli olmayan bir sürümü mevcut resource veritabanının üzerine yazmamaya dikkat etmelisiniz.

Not : mssqlsystemresource.mdf dosyasının bir yedeğini geri yükledikten sonra, sonraki güncellemeleri yeniden uygulamanız gerekecektir.

2.      Master Database

• SQL Server için en önemli veritabanıdır. SQL Server ile ilgili temel veriler bu veri tabanında muhafaza edilir. SQL Server Instance’leri yönetmek için çekirdek sistem veritabanıdır.
• Bu veritabanı Resource veri tabanı aksine üzerinde değişiklik yapmaya uygundur.
• SQL Server üzerinde master database’in bozulması yada düzgün çalışmaması durumunda SQL Server stabil çalışmaz ve servisi başlamayacaktır.
• Her bir SQL Server Instance için yapılandırma bilgilerini barındırır. Instance bulunan veritabanlarının listesi, veritabanlarının dosya konumu, veri tabanı içerisinde bulunan dosyaları, veritabanına login olan kullanıcı ve sistem bilgileri Linked ve Remote (Uzak) sunuculara ait bilgiler, sunucu yapılandırma bilgileri de bu veri tabanında muhafaza edilir.
• Ayrıca master veritabanı SQL server başlatılma sürecindeki ilk veritabanıdır.
• Stored procedure’lerin başlatılması bilgisi bu veritabanı üzerinde barındırılır.

Master veritabanın dosyaları ve özellikleri aşağıdaki gibidir;

Aşağıdaki tablo, SQL Server ve Azure SQL managed Instance için master veritabanındaki her bir veritabanı seçeneği için varsayılan değeri ve seçeneğin değiştirilip değiştirilemeyeceğini listeler. Bu seçenekler için geçerli ayarları görüntülemek için sys.databases katalog görünümünü kullanabilirsiniz.

Master Database Kısıtlamaları

Master veritabanında aşağıdaki işlemler gerçekleştirilemez:

• Dosya veya dosya grupları ekleme.
• Yedeklemeler, yalnızca ana veritabanı üzerinde tam bir veritabanı yedeklemesi yapılabilir.
• Harmanlamayı değiştirme. Varsayılan harmanlama, sunucu harmanlamasıdır.
• Veritabanı sahibini değiştirme. master sa’ya aittir.
• Tam metin kataloğu veya tam metin dizini oluşturma.
• Veritabanındaki sistem tablolarında tetikleyiciler oluşturma.
• Veritabanını düşürme.
• Konuk kullanıcıyı veritabanından çıkarmak.
• Değişiklik verilerinin yakalanmasını etkinleştirme.
• Veritabanı yansıtmaya katılmak.
• Birincil dosya grubunu, birincil veri dosyasını veya günlük dosyasını kaldırma.
• Veritabanını veya birincil dosya grubunu yeniden adlandırma.
• Veritabanını ÇEVRİMDIŞI olarak ayarlama.
• Veritabanını veya birincil dosya grubunu READ_ONLY olarak ayarlama.

Master Database için Öneriler

Master veritabanıyla çalışırken aşağıdaki önerileri göz önünde bulundurun:

• Daima master veritabanının güncel bir yedeğini bulundurunuz.
• Aşağıdaki işlemlerden sonra ana veritabanını mümkün olan en kısa sürede yedekleyin:
  • Herhangi bir veritabanı oluşturma, değiştirme veya bırakma
  • Sunucu veya veritabanı yapılandırma değerlerini değiştirme
  • Oturum açma hesaplarını değiştirme veya ekleme
  • Master’da kullanıcı nesneleri oluşturmayın. Bunu yaparsanız, master daha sık yedeklenmelidir.
• Ana veritabanı için TRUSTYWORTHY seçeneğini ON olarak ayarlamayınız.

Master Database kullanılamaz hale gelirse yapılması gerekenler;

Master kullanılamaz hale gelirse, veritabanını aşağıdaki yollardan biriyle kullanılabilir duruma döndürebilirsiniz:

• Master’ı geçerli bir veritabanı yedeğinden geri yükleyin. Sunucu SQL Server Instance başlatabiliyorsanız, Full (tam) bir veritabanı yedeklemesinden master database üzerine geri yükleyebilmeniz gerekecektir.
• Master’ı database’i tamamen yeniden oluşturunuz. Master database ciddi hasar verilmesi SQL Server’ın servislerinin başlatmanızı engelliyorsa, master’ı yeniden oluşturmanız gerekir.

3.      Model Database

Model veritabanı, bir SQL Server Instance’de oluşturulan tüm veritabanları için şablon olarak kullanılır. SQL Server her başlatıldığında tempdb oluşturulduğundan, model veritabanı her zaman bir SQL Server sisteminde bulunmalıdır.

Veritabanı seçenekleri de dahil olmak üzere model veritabanının tüm içeriği yeni veritabanına kopyalanır. Modelin ayarlarından bazıları, başlatma sırasında yeni bir tempdb oluşturmak için de kullanılır, bu nedenle model veritabanı her zaman bir SQL Server sisteminde bulunmalıdır.

Bir CREATE DATABASE ifadesi yayınlandığında, model veritabanının içeriği kopyalanarak veritabanının ilk kısmı oluşturulur. Yeni veritabanının geri kalanı daha sonra boş sayfalarla doldurulur. Model veritabanını değiştirirseniz, daha sonra oluşturulan tüm veritabanları bu değişiklikleri devralır. Örneğin, izinleri veya veritabanı seçeneklerini ayarlayabilir veya tablolar, işlevler veya saklı yordamlar gibi nesneler ekleyebilirsiniz.

Model veritabanının dosya özellikleri bir istisnadır ve veri dosyasının ilk boyutu dışında yok sayılır. Model veritabanı verilerinin ve günlük dosyasının varsayılan başlangıç boyutu 8 MB’dir.

Aşağıdaki tablo, model verilerinin ve günlük (log) dosyalarının ilk yapılandırma değerlerini listelenmektedir.

Aşağıdaki tablo, model veritabanındaki her bir veritabanı seçeneği için varsayılan değeri ve seçeneğin değiştirilip değiştirilemeyeceğini listeler. Bu seçenekler için geçerli ayarları görüntülemek için sys.databases katalog görünümünü kullanabilirsiniz.

Model Database Kısıtlamaları

Model veritabanında aşağıdaki işlemler gerçekleştirilemez:

• Dosya veya dosya grupları ekleme.
• Collation ayarı değiştirme yapılamaz. Varsayılan collation seçeneği sunucu üzerindeki dil ayarı ile aynıdır.
• Veritabanı sahibini değiştirme (database owner) model sa’ya aittir.
• Veritabanını düşürme (drop) yapılamaz
• Guest kullanıcı hesabı veritabanından çıkarılmaz.
• Change data capture (Değişiklik verilerinin yakalanmasını) etkinleştirmesi yapılamaz
• Veritabanı mirroring ‘e dahil edilemez.
• Primary filegroup, primary data file ve log dosyası silinemez.
• Veritabanını veya primary filegroup yeniden adlandırma yapılamaz.
• Veritabanını Offline olarak ayarlanamaz.
• WITH ENCRYPTION seçeneğini kullanarak prosedürler, view veya triger oluşturma. Şifreleme anahtarı, nesnenin oluşturulduğu veritabanına bağlıdır. Model veritabanında oluşturulan şifreli nesneler yalnızca modelde kullanılabilir.

4.      MSDB

msdb veritabanı, uyarıları ve job’ları zamanlamak için SQL Server Agent ve SQL Server Management Studio , Service Broker ve Database Mail gibi diğer özellikler tarafından kullanılmaktadır.

Örneğin, SQL Server, msdb’deki tablolarda eksiksiz bir çevrimiçi yedekleme ve geri yükleme geçmişini otomatik olarak tutar. Bu bilgiler, yedeklemeyi gerçekleştiren tarafın adını, yedeklemenin zamanını ve yedeklemenin depolandığı cihazları veya dosyaları içerir. SQL Server Management Studio, bir veritabanını geri yüklemek ve herhangi bir işlem günlüğü yedeklemesini uygulamak için bir plan önermek için bu bilgileri kullanır. Veritabanları için yedekleme ve geri yükleme geçmişi bu veritabanı üzerinde saklanmaktadır.

Tüm veritabanları için yedekleme olayları, özel uygulamalar veya üçüncü taraf araçlarla oluşturulmuş olsalar bile kaydedilir. Örneğin, yedekleme işlemlerini gerçekleştirmek için SQL Server Management Object (SMO) nesnelerini çağıran bir Microsoft Visual Basic uygulaması kullanıyorsanız, olay msdb sistem tablolarına, Microsoft Windows application log ve SQL Server error log’larına kaydedilir. msdb’de depolanan bilgileri korumanıza yardımcı olmak için, msdb işlem günlüğünü hataya dayanıklı depolamaya yerleştirmeyi düşünmenizi öneririz.

Bu veritabanı SQL Server Agent tarafından bire bir ilişkisi vardır.

Aşağıdaki tablo, msdb verilerinin ve log dosyalarının ilk yapılandırma değerlerini listeler. Bu dosyaların boyutları, SQL Server Database Engine’in farklı sürümleri için biraz değişebilir.

Aşağıdaki tablo, msdb veritabanındaki her bir veritabanı seçeneği için varsayılan değeri ve seçeneğin değiştirilip değiştirilemeyeceğini listeler. Bu seçenekler için geçerli ayarları görüntülemek için sys.databases katalog görünümünü kullanabilirsiniz.

MSDB Kısıtlamaları

msdb veritabanında aşağıdaki işlemler yapılamaz:

• Collation ayarı değiştirme yapılamaz. Varsayılan collation seçeneği sunucu üzerindeki dil ayarı ile aynıdır.
• Veritabanını düşürme (drop) yapılamaz.
• Guest kullanıcısı veritabanından çıkarılamaz.
• Change data capture (Değişiklik verilerinin yakalanmasını) etkinleştirme.
• Veritabanı mirrorlama yapılamaz.
• Primary filegroup, primary data file ve log dosyası silinemez.
• Veritabanını veya primary filegroup yeniden adlandırma yapılamaz.
• Veritabanını Offline olarak ayarlanamaz.
• Primary filegroup READ_ONLY olarak ayarlanması yapılamaz.

5.      TEMPDB

SQL Server üzerindeki kullanıcı verilerinin geçici olarak saklandığı veritabanıdır. Tempdb, diğer şeylerin yanı sıra geçici nesneler, çalışma tabloları, çevrimiçi dizin işlemleri, imleçler, tablo değişkenleri ve anlık görüntü yalıtım sürüm deposu için kullanılır.

Bu veri tabanı SQL Server her yeniden başlatıldığında içerisindeki verileri silerek yeniden boyutlandırılır. Tempdb kalıcı olmayan bir depolama olduğundan, bu veritabanı için yedekleme ve geri yüklemelere izin verilmez.

Tempdb, SQL Server’da tüm kullanıcılar tarafından kullanılan paylaşılan bir kaynaktır.

Tempdb içindeki işlemler, işlemlerin geri alınabilmesi için minimum düzeyde log dosyasına kaydedilir. tempdb, SQL Server her başlatıldığında yeniden oluşturulur, böylece sistem her zaman veritabanının temiz bir kopyasıyla başlar. Bağlantı kesildiğinde geçici tablolar ve saklı yordamlar otomatik olarak bırakılır ve sistem kapatıldığında hiçbir bağlantı etkin değildir. tempdb hiçbir zaman bir SQL Server oturumundan diğerine kaydedilecek hiçbir şeye sahip değildir. Tempdb’de yedekleme ve geri yükleme işlemlerine izin verilmez.

Aşağıdaki tablo, SQL Server’daki tempdb verilerinin ve log dosyalarının ilk yapılandırma değerlerini listeler. Buradaki değerler, model veritabanı için varsayılanları ile temelde aynıdır. Bu dosyaların boyutları, SQL Server’ın farklı sürümleri için biraz farklılık gösterebilir.

Secondary veri dosyalarının sayısı, makinedeki (mantıksal) işlemcilerin sayısına bağlıdır. Genel bir kural olarak mantıksal işlemcilerin sayısı sekizden az veya buna eşitse, mantıksal işlemcilerle aynı sayıda veri dosyasını kullanın. Mantıksal işlemcilerin sayısı sekizden fazlaysa, sekiz veri dosyası kullanın.

Aşağıdaki tablo, tempdb veritabanındaki her bir veritabanı seçeneği için varsayılan değeri ve seçeneğin değiştirilip değiştirilemeyeceğini listeler. Bu seçenekler için geçerli ayarları görüntülemek için sys.databases katalog görünümünü kullanabilirsiniz.