SQL Server Sistem Veritabanları ve Taşınma Yöntemleri

SQL Server kurulumuyla beraber 4 adet sistem veritabanı kurulmaktadır. Bunlar ortak veritabanları olup SQL Server ın çalışması için gerekli olan veritabanlarıdır. Bugünkü yazımda SQL Server Sistem Veritabanlarının neler olduklarını, ne işe yaradıklarını, backup-restore strajelerini ve ne şekilde taşınabileceklerini anlatıyor olacağım.

Sistem Veritabanları ?

İlk olarak SQL Server da bulunan Sistem Veritabanları ve kısa açıklamalarına bakalım. Daha sonra bu veritabanlarını tek tek inceliyor olacağız.

Sistem Veritabanları Management Studio da System Databases bölümünde bulunurlar.

Burada Resource DB için bir parantez açmak istiyorum. Gördüğünüz gibi bu yukarıdaki listede Resource DB bulunmamakta. Daha öncede belirttiğim gibi objeler fiziksel olarak Resource DB de tutulmaktadır. Fakat bu objeler mantıksal olarak her DB nin içerisinde gösterilmektedir. Bu yüzden Management Studio da Resource DB görüntülenmez.

Sistem Veritabanlarına script ile ulaşmak için ise aşağıdaki kodu kullanabiliriz.

select * from sys.databases where database_id<=4

Sistem Veritabanlarının database_id si her server için aynıdır. Yani 1 id li veritabanı her daim master veritabanıdır. Dolayısıyla ek bir bilgi olarak; User database leri üzerinde işlem yapmak için sorgunuzda database_id>4 clause unu kullanmanız yeterlidir.

Şimdi sistem veritabanlarını daha detaylı incelemeye başlayalım. Daha sonra da Sistem Veritabanlarının nasıl taşındıklarını inceliyor olacağız.

Master Veritabanı

Master Veritabanı SQL Server ile alakalı server-level bilgileri tutar. Örneğin loginler, backup device lar, linked server lar ve Endpoint gibi bilgiler Master DB de tutulmaktadır. Ayrıca sistem de bulunan user database bilgileri ve bu database lerin file bilgileri de Master DB de tutulmaktadır. Dolayısıyla eğer Master DB erişilemez durumdaysa SQL Server açılamaz durumda olur. Bundan dolayı Master DB nin sıklıkla backuplanması önerilmektedir. Bu arada ufak bir bilgi vermek istiyorum. Master DB nin sadece full backup ı alınabilir, differential yada tlog backup ı alınamaz.

Master DB ile ilgili ek öneriler aşağıdaki gibidir.

Msdb Veritabanı

SQL Server agent tarafından kullanılan bu veritabanı; alert ve job lar ile ilgili bilgileri tutar. Örneğin aşağıdaki query i msdb üzerinde çalıştırıldığında sistem de bulunan job ların çalışma tarihçesine erişilebilir.

select job_name, run_datetime, run_duration

from

(

    select job_name, run_datetime,

        SUBSTRING(run_duration, 1, 2) + ‘:’ + SUBSTRING(run_duration, 3, 2) + ‘:’ +

        SUBSTRING(run_duration, 5, 2) AS run_duration

    from

    (

        select DISTINCT

            j.name as job_name,

            run_datetime = CONVERT(DATETIME, RTRIM(run_date)) + 

                (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,

            run_duration = RIGHT(‘000000’ + CONVERT(varchar(6), run_duration), 6)

        from msdb..sysjobhistory h

        inner join msdb..sysjobs j

        on h.job_id = j.job_id

    ) t

) t

order by job_name, run_datetime

Model Veritabanı

Model veritabanı SQL Server da yeni oluşturulacak veritabanlarının template idir. Msdb üzerinde database size,collation,recovery model gibi bilgiler değiştirilerek yeni oluşturulan her veritabanın default olarak bu bilgilerle oluşturulması sağlanabilir. TempDB her SQL Server restart ında create edildiği için, eğer model DB de problem varsa TempDB create edilemeyeceği için service start olmayacaktır.

TempDB Veritabanı

tempDB, local temporary tables, temporary stored procedures, table variables ve cursor gibi temp objeleri tutan veritabanıdır. Ayrıca result set te yapılan sorting(sıralama) işlemi tempDB de yapılmaktadır. tempDB, SQL Server’ın performanslı çalışması için en önemli etkenlerden biridir. En basitinden tempDB nin data ve log file larının diğer veritabanlarından ayrı bir fiziksel diskte, hatta mümkünse data ve log file larının farklı fiziksel disklerde tutulması best practise dir. Ayrıca tempDB data file ının core işlemci sayısı kadar file dan oluşması ve her birinin eşit boyutta verilmesi diğer bir best practise adımıdır.
tempDB data file larının küçük boyutlarda set edilmemesinin şöyle bir avantajı vardır. Örneğin çok kayıtlı bir result set execute edildiğinde tempDB data file ı bu sonucu allocate edecek boyutta değil ise auto growth olması gerekmektedir. Her auto growth işlemi de performans a negatif etki yapmaktadır.

Sistem veritabanlarını tanıdıktan sonra yazımın ikinci ana konusuna geçiyorum.

Sistem Veritabanlarının Taşınması, Yedeklenmesi ve Restore Edilmesi

Sistem Veritabanlarını taşımak ya da yedeklemek normal User veritabanlarını taşımaktan farklıdır. Bildiğiniz gibi normal User veritabanları Backup-Restore veya Detach-Attach komutları ile taşınabiliyorken Sistem Veritabanlarının özel yöntemlerle taşınması gerekir.

Veritabanı file larının bulundukları yerleri aşağıdaki sorgu ile öğrenebilir ve taşıma işlemini bitirdikten sonra yaptığımız işlemin doğruluğunu kontrol edebiliriz.

select * from sys.sysaltfiles

Şimdi SQL Server’ın 4 Sistem Veritabanın nasıl taşınacağını inceleyelim.

Master Veritabanın Taşınması

Master Veritabanı 2 farklı yöntem ile taşınabilir.

StartUp Parametresini Değiştirerek Master Veritabanını Taşımak

1.       Start >> Programs >> Microsoft SQL Server 2008 >> Configuration Tool >> SQL Server Configuration Manager ı açalım.

2.       Master Veritabanında değişiklik yapmak istediğimiz SQL Server instance ı sağ tıklayıp properties e basalım. (örneğin SQLServer(MSSQLServer)

3.       Advanced tab ında Startup Parameters te oynama yapacağız.

4.       Startup parameters deki yazı herhangi bir değişiklik yapılmadıysa aşağıdaki gibidir.

-dC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf;
-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG;
-lC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

Master DB nin Data ve Log file larını C:\DATA folder ına taşıdığımızı düşünürsek startup parameters de ki yazı aşağıdaki gibi olmalıdır.

-dC:\DATA\master.mdf;
-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG;
-lC:\DATA\mastlog.ldf

Bu yazıyı alıp startup parameters e paste edip apply diyerek ekranı kapatalım.

5.       Configuration Manager üzerinden SQL Server Service i stop edelim.

6.       Master DB nin data ve log file larını (master.mdf ve mastlog.ldf) bulunduğu yerden alıp C:\Data folder ına taşıyalım.

7.       Configuration Manager dan SQL Server service ini tekrar start edelim.

Backup-Restore ile Master Veritabanını Taşımak

1.       Master Veritabanı full backup ı alınır. BACKUP DATABASE [master] TO DISK = N’c:\masterbackup.bak’

2.       Database Engine Service i stop edilir. Bunun için command prompt ta aşağıdaki kodu kullanabilirsiniz.
net stop MSSQLSERVER

3.       Service i single-user mode da start edilir.
Default instance için command prompt ta aşağıdaki kodu kullanabilirsiniz.
sqlservr.exe –m
Named instance için ise command prompt ta aşağıdaki kodu kullanabilirsiniz.
sqlservr.exe -m -s <instancename>

4.       Şimdi restore işlemini yapacağız. Bu işlemi sqlcmd ile command prompt tan gerçekleştireceğiz. Aşağıdaki SQL script i command prompt ta sqlcmd den sonra çalıştırınız.
SQLCMD
RESTORE DATABASE master FROM DISK = ‘c:\masterbackup.bak’ WITH REPLACE;
GO

5.       Restore işleminden sonra SQL Server service i stop olup sqlcmd nin çalıştığı ekran kapanacaktır.

6.       Database Engine Service i start edilir.
net start MSSQLSERVER

TempDB Veritabanın Taşınması

Sistem Veritabanları içinde taşınması en kolay olan veritabanıdır. Aşağıdaki script i kendi TempDB veritabanınızın data ve log file bilgilerine göre ve taşınması istenen yeni url bilgilerine göre düzenleyip çalıştırdıktan sonra Database Engine service ini restart etmeniz yeterlidir.

USE master

GO

ALTER DATABASE tempdb

                MODIFY FILE (NAME = tempdev, FILENAME = ‘c:\YeniKlasor\tempdb.mdf’)

GO

ALTER DATABASE tempdb

                MODIFY FILE (NAME = tmpf2, FILENAME = ‘c:\YeniKlasor\tmpf2.ndf’)

GO

ALTER DATABASE tempdb

                MODIFY FILE (NAME = tmpf3, FILENAME = ‘c:\YeniKlasor\tmpf3.ndf’)

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = templog, FILENAME = ‘c:\YeniKlasor\templog.ldf’)

Model ve MSDB Veritabanlarının Taşınması

Model ve MSDB veritabanlarının taşınma yöntemi aynıdır. O yüzden bu 2 veritabanını aynı başlıkta inceliyor olacağız.

Ön bilgi vermek gerekirse Model ve MSDB veritabanları Database Engine özel bir flag ile start edildikten sonra detach-attach yöntemiyle taşınmaktadır. Şimdi gelin bu taşımayı adım adım gerçekleştirelim.

1.       SQL Server Service i Configuration manager dan yada bir cluster ortamı kullanılıyorsa ClusterAdmin ekranından offline yapılır.

2.       Daha sonra comand prompt açılır ve SQL Server –m –c –T3608 parametresi ile başlatılır.
……./sqlservr.exe –c –m –T3608
Burada –m parametresi single user mode u, –c parametreside command prompt tan en hızlı şekilde servisi başlatmayı ifade etmektedir.

3.       Servis istediğimiz modda açıldıktan sonra yeni bir query window vasıtasıyla model ve msdb yi detach – attach edebiliriz.

4.       Model veritabanını detach edilir.
Use Master
GO
sp_detach_db ‘model’
GO

5.       Model DB data ve log file larını yeni yerlerine kopyalanır.

6.       Model i attach edilir.
Use Master
GO
Create Database model on
    (FILENAME = ‘YeniDrive\model.mdf’)
   ,(FILENAME = ‘YeniDrive\modellog.ldf’)
For Attach;
GO

7.       4,5 ve 6. Adımlar MSDB içinde uygulanır.

8.       Command Prompt CTRL + C ile durdurulur ve daha sonra kapatılır.

9.       SQL Server Service i start edilir.

SQL Server kendi iç dinamikleri için bazı sistem veritabanlarına ihtiyaç duymaktadır. Bu sistem veritabanlarının taşınması normal user veritabanlarından farklıdır ve service downtime ı gerektirmektedir. Bugünkü yazımızda bu sistem veritabanları tanıdık. Aynı zamanda nasıl taşınacakları hakkında adım adım bilgi sahibi olduk.