iT邦幫忙

DAY 4
31

SQL Server 無敵手冊系列 第 4

SQL Server 無敵手冊第四篇- 介紹 SQL Server 的系統資料庫

SQL Server 把資料庫區分為兩大類型,一為系統資料庫另一類則為使用
者資料庫
,今天就為大家介紹 SQL Server的系統資料庫
什麼是系統資料庫
每個系統資料庫都有它特殊的用途,例如如用來存放使用者所定義的資料庫、SQL Server自動化作業、執行運算時所需之暫存資料庫等等,接下來我們就對SQL Server的系統資料庫一一說明。
如下圖所示,SQL Server 將系統資料庫分類到系統資料庫的資料夾中(紅色框框),而一般使用者資料庫預設則會在資料庫的資料夾。

SQL Server 中包括下列系統資料庫:
master
記錄 SQL Server 執行個體的所有系統層級資訊。例如登入資訊和組態選項設定值。
您不應在 master 資料庫中建立任何使用者物件,例如資料表、檢視、預存程序或觸發程序。
主要資料檔 :Master.mdf (以 10% 的比例自動成長,直到磁碟已滿。)
記錄檔 :mastlog.ldf (以 10% 的比例自動成長,最大至 2 TB。)
不能在 master 資料庫上執行下列作業:
1.加入檔案或檔案群組。
2.變更定序。預設定序是伺服器定序。
3.變更資料庫擁有者:master 為 dbo 所擁有。
4.建立全文檢索目錄或全文檢索索引。
5.在資料庫中的系統資料表上建立觸發程序。
6.卸除資料庫。
7.從資料庫卸除 guest 使用者。
8.啟用異動資料擷取。
9.參與資料庫鏡像。
10.移除主要檔案群組、主要資料檔或記錄檔。
11.重新命名資料庫或主要檔案群組。
12.將資料庫設定為 OFFLINE。
13.將資料庫或主要檔案群組設定為 READ_ONLY。
為避免發生master資料庫損毀導致SQL Server無法正常運作,建議在執行下列作業後,立即備份 master 資料庫:
1.建立、修改或卸除任何資料庫
2.變更伺服器或資料庫組態值
3.修改或加入登入帳戶
msdb
供 SQL Server Agent 用來設定警示和作業排程,以及供其他像 Service Broker 和 Database Mail 之類的功能使用。
主要資料檔 :MSDBData.mdf (自動成長 256 KB,直到磁碟滿了為止。)
記錄檔 :MSDBLog.ldf (自動成長 256 KB,最多到 2 TB。)
不能在 msdb 資料庫上執行下列作業:
1.變更定序。預設定序是伺服器定序。
2.卸除資料庫。
3.從資料庫卸除 guest 使用者。
4.啟用異動資料擷取。
5.參與資料庫鏡像。
6.移除主要檔案群組、主要資料檔或記錄檔。
7.重新命名資料庫或主要檔案群組。
8.將資料庫設定為 OFFLINE。
9.將主要檔案群組設為 READ_ONLY。
如果 msdb 損毀,SQL Server Agent 所使用的任何排程資訊都會遺失,因此必須以手動方式重新建立。而且也會失去備份和還原記錄資訊。
model
作為 SQL Server 執行個體上建立之所有資料庫的範本。對 model 資料庫進行的修改 (例如,資料庫大小、定序、復原模式和其他資料庫選項) 會套用到之後建立的任何資料庫。
因為每次 SQL Server 啟動時,都會建立 tempdb,所以 model 資料庫一定要存在於 SQL Server 系統中。
主要資料檔 :model.mdf (以 10% 的比例自動成長,直到磁碟已滿。)
記錄檔 :modellog.ldf (以 %10 的比例自動成長,最大至 2 TB。)
下列作業無法在 model 資料庫上執行:
1.加入檔案或檔案群組。
2.變更定序。預設定序是伺服器定序。
3.變更資料庫擁有者。model 由 dbo 所擁有。
4.卸除資料庫。
5.從資料庫卸除 guest 使用者。
6.啟用異動資料擷取。
7.參與資料庫鏡像。
8.移除主要檔案群組、主要資料檔或記錄檔。
9.重新命名資料庫或主要檔案群組。
10.將資料庫設定為 OFFLINE。
11.將資料庫或主要檔案群組設定為 READ_ONLY。
12.使用 WITH ENCRYPTION 選項來建立程序、檢視表或觸發程序。此加密金鑰會繫結至在其中建立物件的資料庫。在 model 資料庫中建立的加密物件只能用於 model。
如果 model 損毀,而且沒有目前的備份可用,則任何已加入 model 的使用者特定範本資訊都會遺失,必須以手動方式重新建立。
Resource
是一個唯讀的資料庫,其中包含 SQL Server 擁有的系統物件。系統物件實際上會保存在 Resource 資料庫中,但邏輯上會出現在每個資料庫的 sys 結構描述中。Resource 資料庫的實體檔案名稱為 mssqlsystemresource.mdf 和
mssqlsystemresource.ldf。這些檔案位於

 <磁碟機>:\Program  Files\Microsoft SQL Server\MSSQL10.<instance_name>\MSSQL\Binn\ 

中。每個 SQL Server 執行個體只有一個相關聯的 mssqlsystemresource.mdf 檔案,而且這些執行個體不共用此檔案。
SQL Server 無法備份 Resource 資料庫。您可以將 mssqlsystemresource.mdf 檔視為二進位 (.EXE) 檔案而非資料庫檔案,藉以進行以檔案為基礎或以磁碟為基礎的備份,不過您無法使用 SQL Server 來還原備份。還原 mssqlsystemresource.mdf 的備份副本只能手動完成,而且您必須小心不要使用過期或可能不安全的 Resource 資料庫來覆寫目前的資料庫。
tempdb
是保存暫存物件或中繼結果集的工作空間。在每次SQL Server啟動時,都會重新建立tempdb,所以重新啟動SQL Server後都會清空tempdb。
主要資料檔 :tempdb.mdf (以百分之 10 的比例自動成長,直到磁碟全滿。)
記錄檔 :templog.ldf (以 10% 的比例自動成長,最大至 2 TB。)
tempdb 的大小會影響系統效能。例如,若 tempdb 太小,則每次啟動 SQL Server 時,資料庫自動成長的情形都會過多地佔用系統處理的資源,而無法支援工作負載的需求。增加 tempdb 的大小即可避免這種負擔。
下列作業不能在 tempdb 資料庫上執行:
1.加入檔案群組。
2.備份或還原資料庫。
3.變更定序。預設定序是伺服器定序。
4.變更資料庫擁有者。 tempdb 的擁有者為 dbo。
5.建立資料庫快照集。
6.卸除資料庫。
7.從資料庫卸除 guest 使用者。
8.啟用異動資料擷取。
9.參與資料庫鏡像。
10.移除主要檔案群組、主要資料檔或記錄檔。
11.重新命名資料庫或主要檔案群組。
12.執行 DBCC CHECKALLOC。
13.執行 DBCC CHECKCATALOG。
14.將資料庫設定為 OFFLINE。
15.將資料庫或主要檔案群組設定為 READ_ONLY。
特別值得一提的是,在SQL Server 2008將頁面確認(PAGE_VERIFY)預設為CHECKSUM(如下圖紅色框框),如果您是由舊版升級安裝時,預設值會維持在NONE,建議可以修改為CHECKSUM,以確保資料的正確性。


上一篇
SQL Server 無敵手冊第三篇- 為SQL Server設定防火牆
下一篇
SQL Server 無敵手冊第五篇- 移動 SQL Server 的系統資料庫
系列文
SQL Server 無敵手冊30
0
james
iT邦新手 4 級 ‧ 2009-10-06 08:47:05

很棒的教學

0
mrhsieh
iT邦好手 4 級 ‧ 2009-10-06 10:11:26

嗯,介紹的很詳細,是需要來支持一下

0
Ken(Bigcandy)
iT邦大師 1 級 ‧ 2009-10-06 11:51:53

我也支持,感謝了!!

0
funkent
iT邦高手 1 級 ‧ 2009-10-07 15:22:02

感謝各位大大的支持,如果可以順便按一下[推]這個按鈕就更感謝了

1
funkent
iT邦高手 1 級 ‧ 2009-11-11 10:26:38

補上SQL Server 無敵手冊全系列的連結
SQL Server 無敵手冊

0
azel
iT邦新手 5 級 ‧ 2012-07-30 15:50:49

超棒~介紹很詳細~!

0
0
Marvel
iT邦見習生 0 級 ‧ 2016-10-01 16:38:07

感謝分享。很棒的內容

我要留言

立即登入留言