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

tarafından 2 Ocak 2011 tarihinde Microsoft kategorisine yazıldı.

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 Veritabanı


Açıklama


Master


SQL Server ile alakalı server – level bilgileri tutar.


Msdb


SQL Server Agent tarafından kullanılan bu db; alert ve job lar ile ilgili bilgileri tutar.


Model


Model DB si SQL Server da yeni oluşturulacak DB lerin template idir. Msdb üzerinde database size,collation,recovery model gibi bilgiler değiştirilerek yeni oluşturulan her DB nin bu bilgiler ile oluşturulması sağlanabilir.


Resource


SQL Server sistem objelerini tutan Resource DB bir read-only DB dir. Sistem objeleri fiziksel olarak resource db de tutulurken mantıksal olarak her DB nin içerisinde gösterilir.


Tempdb


TempDB temporary objeleri tutar. Her SQL Service in restart ında tekrar create edilir.



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


image001


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


image002


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.




  • Master DB periyodik olarak backup lanmalıdır.

  • Aşağıdaki işlemlerden herhangi biri yapıldığında en kısa sürede Master db nin back up ının alınması önerilir.


    • Her hangi bir DB oluşturulduğunda, değiştirildiğinde ya da silindiğinde.

    • Server ya da DB configuration ları değiştirildiğinde.

    • Logon account larında değişiklik yapıldığında ya da yeni bir login eklendiğinde.

  • Master DB içerisinde user object create edilmesi önerilmez.

  • Master DB nin TRUSTWORTHY özelliğinin ON yapılması önerilmez.

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.

Bu yazı blog üzerinde Tayfun DEĞER tarafından paylaşılmıştır. 2009 yılında açılan blog kısa zaman içerisinde büyük bir izleyici kitlesine sahip olmuştur.Tayfun DEĞER danışmanlık ve eğitimler vermektedir. vExpert 2013-2015, VCP5, VCP5-DT, VCP-Cloud ve MCSE sertifikalarına sahiptir.Twitter 'dan @tayfundeger veya RSS ile sitedeki değişiklikleri takip edebilirsiniz.