數(shù)據(jù)庫(kù)的建立與維護(hù)

第2章

數(shù)據(jù)庫(kù)的建立與維護(hù)系統(tǒng)數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù)的存儲(chǔ)結(jié)構(gòu)數(shù)據(jù)庫(kù)的創(chuàng)建和管理查看數(shù)據(jù)庫(kù)狀態(tài)數(shù)據(jù)庫(kù)操作數(shù)據(jù)快照的創(chuàng)建系統(tǒng)數(shù)據(jù)庫(kù)

所謂系統(tǒng)數(shù)據(jù)庫(kù)指的是隨安裝程序一起安裝，用于協(xié)助SQLServer2008共同完成管理操作的數(shù)據(jù)庫(kù)，它們是SQLServer2008運(yùn)行的基礎(chǔ)。

在SQLServer2008中，默認(rèn)有5個(gè)系統(tǒng)數(shù)據(jù)庫(kù)：master、model、msdb、tempdb和resource數(shù)據(jù)庫(kù)。Master數(shù)據(jù)庫(kù)是SQLServer系統(tǒng)最重要的數(shù)據(jù)庫(kù)，它記錄了SQLServer系統(tǒng)的所有系統(tǒng)信息。在SQLServer2008中，系統(tǒng)對(duì)象不再存儲(chǔ)在master數(shù)據(jù)庫(kù)中，而是存儲(chǔ)在Resource數(shù)據(jù)庫(kù)中。model數(shù)據(jù)庫(kù)用作在SQLServer實(shí)例上創(chuàng)建的所有數(shù)據(jù)庫(kù)的模板。如果修改model數(shù)據(jù)庫(kù)，之后創(chuàng)建的所有數(shù)據(jù)庫(kù)都將繼承這些修改。

Msdb數(shù)據(jù)庫(kù)是代理服務(wù)數(shù)據(jù)庫(kù)。Tempdb是一個(gè)臨時(shí)數(shù)據(jù)庫(kù)，它為所有的臨時(shí)表、臨時(shí)存儲(chǔ)過(guò)程及其他臨時(shí)操作提供存儲(chǔ)空間。Tempdb數(shù)據(jù)庫(kù)由整個(gè)系統(tǒng)的所有數(shù)據(jù)庫(kù)使用。SQLServer每次啟動(dòng)時(shí)，tempdb數(shù)據(jù)庫(kù)被重新建立。當(dāng)用戶與SQLServer斷開(kāi)連接時(shí)，其臨時(shí)表和存儲(chǔ)過(guò)程自動(dòng)被刪除。mastermodeltempdbmsdbAdventureWorksAdventureWorksDW系統(tǒng)數(shù)據(jù)庫(kù)用戶數(shù)據(jù)庫(kù)User1StuInfo數(shù)據(jù)庫(kù)是表、視圖、存儲(chǔ)過(guò)程、觸發(fā)器等數(shù)據(jù)庫(kù)對(duì)象的集合，是數(shù)據(jù)庫(kù)管理系統(tǒng)的核心內(nèi)容。為了更好地理解數(shù)據(jù)庫(kù)，應(yīng)該首先了解數(shù)據(jù)庫(kù)的物理文件和邏輯文件、事務(wù)日志、文件組、數(shù)據(jù)庫(kù)的物理空間、數(shù)據(jù)庫(kù)狀態(tài)、數(shù)據(jù)庫(kù)快照等基本概念。

數(shù)據(jù)庫(kù)：用于存儲(chǔ)特定格式的關(guān)系數(shù)據(jù)的總集。

文件：用于存放由數(shù)據(jù)構(gòu)成的表。

文件組：用于將多個(gè)文件組織起來(lái)進(jìn)行管理。

架構(gòu)：用于管理數(shù)據(jù)表等對(duì)象的命名。

快照：用于建立數(shù)據(jù)庫(kù)的快速還原點(diǎn)，方便測(cè)試和故障恢復(fù)。

數(shù)據(jù)庫(kù)對(duì)象及構(gòu)成：數(shù)據(jù)庫(kù)的存儲(chǔ)結(jié)構(gòu)數(shù)據(jù)庫(kù)的存儲(chǔ)結(jié)構(gòu)分為邏輯存儲(chǔ)結(jié)構(gòu)和物理存儲(chǔ)結(jié)構(gòu)兩種。1、數(shù)據(jù)庫(kù)的邏輯存儲(chǔ)結(jié)構(gòu)指的是數(shù)據(jù)庫(kù)是由哪些性質(zhì)的信息所組成，SQLServer的數(shù)據(jù)庫(kù)不僅只是數(shù)據(jù)的存儲(chǔ)，所有與數(shù)據(jù)處理操作相關(guān)的信息都存儲(chǔ)在數(shù)據(jù)庫(kù)中。SQLServer的數(shù)據(jù)庫(kù)是由諸如表、視圖、索引等各種不同的數(shù)據(jù)庫(kù)對(duì)象所組成，它們分別用來(lái)存儲(chǔ)特定信息并支持特定功能，構(gòu)成數(shù)據(jù)庫(kù)的邏輯存儲(chǔ)結(jié)構(gòu)。2、數(shù)據(jù)庫(kù)的物理存儲(chǔ)結(jié)構(gòu)則是討論數(shù)據(jù)庫(kù)文件是如何在磁盤上存儲(chǔ)的。數(shù)據(jù)庫(kù)在磁盤上是以文件為單位存儲(chǔ)的，由數(shù)據(jù)庫(kù)文件和事務(wù)日志文件組成。

在SQL2008中，一個(gè)數(shù)據(jù)庫(kù)至少有一個(gè)數(shù)據(jù)文件和一個(gè)事務(wù)日志文件。該數(shù)據(jù)庫(kù)也可以有多個(gè)數(shù)據(jù)文件和多個(gè)事務(wù)日志文件。數(shù)據(jù)文件用于存放數(shù)據(jù)庫(kù)的數(shù)據(jù)和各種對(duì)象，事務(wù)日志文件用于存放事務(wù)日志。數(shù)據(jù)庫(kù)的文件組成：（通常可以由三類文件組成）主數(shù)據(jù)文件：默認(rèn)的擴(kuò)展名為.mdf

輔助數(shù)據(jù)文件：默認(rèn)的擴(kuò)展名為.ndf

事務(wù)日志文件：默認(rèn)的擴(kuò)展名為.ldf在SQLServer2008中一個(gè)數(shù)據(jù)庫(kù)至少包含一個(gè)有數(shù)據(jù)庫(kù)對(duì)象的主數(shù)據(jù)文件和一個(gè)事務(wù)日志文件。這些物理文件具有可用于Transact-SQL語(yǔ)句的操作系統(tǒng)文件名稱和邏輯文件名稱所有的的數(shù)據(jù)文件和事務(wù)日志的默認(rèn)位置是C:\ProgromFiles\MirosoftSQLServer\MSSQL.n\MSSQL\Data最佳的實(shí)踐：

將所有的數(shù)據(jù)和對(duì)象存儲(chǔ)在輔助數(shù)據(jù)文件中，而主數(shù)據(jù)文件只負(fù)責(zé)存儲(chǔ)數(shù)據(jù)目錄，這種配置可有效的避免訪問(wèn)時(shí)的磁盤爭(zhēng)用。

存儲(chǔ)數(shù)據(jù)的方法：日志文件:.ldf數(shù)據(jù)文件:

.mdfor.ndf區(qū):8個(gè)連續(xù)的8KB頁(yè)頁(yè):8KB

配置事務(wù)日志：

建立好數(shù)據(jù)庫(kù)之后，在進(jìn)行具體的操作之前應(yīng)當(dāng)首先配置事務(wù)日志。事務(wù)日志文件記錄SQLServer對(duì)這個(gè)數(shù)據(jù)庫(kù)的操作，以便于在系統(tǒng)出現(xiàn)故障時(shí)恢復(fù)數(shù)據(jù)庫(kù)。那事務(wù)日志又有什么樣的作用？（看個(gè)場(chǎng)景）小張?jiān)跁?shū)店買書(shū)。他和書(shū)店進(jìn)行一次性交易付款過(guò)程包括下面這些數(shù)據(jù)庫(kù)操作：更新客戶所購(gòu)書(shū)籍的庫(kù)存信息。保存客戶的付款信息——和銀行系統(tǒng)進(jìn)行互動(dòng)。生成訂單并保存到數(shù)據(jù)庫(kù)中。更新用戶相關(guān)信息。

上面的場(chǎng)景中每個(gè)數(shù)據(jù)庫(kù)操作都被稱為事務(wù)。

所有的數(shù)據(jù)庫(kù)操作都將保存到相關(guān)的數(shù)據(jù)庫(kù)日志中，發(fā)生故障后，系統(tǒng)可以根據(jù)事務(wù)日志將數(shù)據(jù)庫(kù)返回到用戶下單前的狀態(tài)。正常情況一切順利進(jìn)行，交易成功，與交易相關(guān)的所有數(shù)據(jù)也成功的更新。快速了解創(chuàng)建學(xué)校的數(shù)據(jù)庫(kù)：管理任務(wù)：

小王的學(xué)校通過(guò)數(shù)據(jù)庫(kù)管理所有老師和學(xué)生的信息，這臺(tái)服務(wù)器配有三個(gè)磁盤驅(qū)動(dòng)器。在安裝了2008之后，作為一個(gè)DBA，需要實(shí)現(xiàn)以下任務(wù)：

創(chuàng)建數(shù)據(jù)庫(kù)；

創(chuàng)建數(shù)據(jù)文件；

規(guī)劃文件大小和位置；

配置數(shù)據(jù)庫(kù)選項(xiàng)。管理操作過(guò)程：

小王分別在SQL2008中建立了老師和學(xué)生的數(shù)據(jù)庫(kù)的文件，分別用來(lái)存儲(chǔ)老師和學(xué)生的信息。并且根據(jù)在校的老師和學(xué)生數(shù)量來(lái)估計(jì)文件的初始大小，老師的數(shù)據(jù)文件初始大小為20MB，學(xué)生的數(shù)據(jù)文件初始大小為80MB，設(shè)置日志文件的總大小為數(shù)據(jù)文件的25％。

由于服務(wù)器有三個(gè)硬盤驅(qū)動(dòng)器，操作系統(tǒng)位于C盤，小王將數(shù)據(jù)文件分別放置于D盤和E盤，將日志文件放在C盤，這樣就可以實(shí)現(xiàn)最好的性能。

創(chuàng)建數(shù)據(jù)庫(kù)的示例：

可以使用SQLServerManagementStudio中的可視化工具來(lái)創(chuàng)建數(shù)據(jù)庫(kù)，圖示：創(chuàng)建數(shù)據(jù)庫(kù)時(shí)，可指定以下選項(xiàng)：

主文件：默認(rèn)文件名為：database_name.mdf，位于SQLServer實(shí)例的Data文件夾中。

輔助文件：創(chuàng)建可選的輔助文件，將其分配到主文件組和任何現(xiàn)存用戶定義的主文件組中

事務(wù)日志：默認(rèn)文件名為：database_name.ldf，位于SQLServer實(shí)例的Data文件夾中。大小：可指定每個(gè)數(shù)據(jù)文件和日志文件的大小。初始大小與model數(shù)據(jù)庫(kù)中使用的值相同。設(shè)置自動(dòng)增長(zhǎng)對(duì)話框:文件增長(zhǎng)：可指定文件是否在必要時(shí)增長(zhǎng)。該選項(xiàng)稱為自動(dòng)增長(zhǎng)，即為啟用文件增長(zhǎng)。最大值：可指定MB或百分比作為文件可增長(zhǎng)的最大值，建議指定文件增長(zhǎng)的最大值。排序規(guī)則：數(shù)據(jù)的排序次序原則，用以確定進(jìn)行數(shù)據(jù)排序時(shí)數(shù)據(jù)值的具體位置。

文件組：

如果在數(shù)據(jù)庫(kù)中存在大量的文件，如何才能方便管理，同時(shí)提高數(shù)據(jù)庫(kù)的性能呢？探討：

SQLServer2008在數(shù)據(jù)文件中存儲(chǔ)數(shù)據(jù)，數(shù)據(jù)文件可分組為一個(gè)或多個(gè)文件組。雖然數(shù)據(jù)庫(kù)只用一個(gè)文件組即可成功運(yùn)行，但創(chuàng)建多個(gè)文件組可能更有利。結(jié)論：我們要學(xué)會(huì)文件組的創(chuàng)建、設(shè)計(jì)和維護(hù)，以及利用文件組改善數(shù)據(jù)庫(kù)的性能。文件組的概念：文件組是一種邏輯結(jié)構(gòu)，它允許DBA組織數(shù)據(jù)文件，并把它們作為一個(gè)邏輯單元進(jìn)行管理。

文件組可以控制數(shù)據(jù)庫(kù)中各個(gè)對(duì)象的物理布局，這可提供大量可管理性和性能方面的好處。SQLServer支持兩種文件組：主要文件組和用戶定義的文件組。主要文件組包含主要數(shù)據(jù)文件以及任何沒(méi)有被存儲(chǔ)到另一文件組中的次要數(shù)據(jù)文件。

創(chuàng)建用戶定義的文件組的目的是對(duì)次要文件進(jìn)行分組，并將數(shù)據(jù)庫(kù)對(duì)象分配給文件組。問(wèn)：每個(gè)文件組可以有多少個(gè)日志文件？文件組

創(chuàng)建數(shù)據(jù)庫(kù)的方法有以下三種：

1、使用SQLServer管理平臺(tái)創(chuàng)建數(shù)據(jù)庫(kù)

2、使用模板創(chuàng)建數(shù)據(jù)庫(kù)使用模板創(chuàng)建數(shù)據(jù)庫(kù)，用戶根據(jù)提示操作，即可創(chuàng)建數(shù)據(jù)庫(kù)。

使用

CREATEDATABASE語(yǔ)句創(chuàng)建數(shù)據(jù)庫(kù)：數(shù)據(jù)庫(kù)的創(chuàng)建和管理Transact-SQL語(yǔ)言使用CREATEDATABASE命令來(lái)創(chuàng)建數(shù)據(jù)庫(kù)。該命令的語(yǔ)法如下：CREATEDATABASEdatabase_name[ON[PRIMARY][<filespec>[，…n][，<filegroupspec>[，…n]]][LOGON{<filespec>[，…n]}][FORRESTORE]<filespec>::=（[NAME=logical_file_name，]FILENAME=‘os_file_name’[，SIZE=size][，MAXSIZE={max_size|UNLIMITED}][，F(xiàn)ILEGROWTH=growth_increment]）[，…n]<filegroupspec>::=FILEGROUPfilegroup_name<filespec>[，…n]各參數(shù)說(shuō)明如下：

database_name：數(shù)據(jù)庫(kù)的名稱，最長(zhǎng)為128個(gè)字符。

PRIMARY：該選項(xiàng)是一個(gè)關(guān)鍵字，指定主文件組中的文件。

LOGON：指明事務(wù)日志文件的明確定義。

NAME=logical_file_name：指定數(shù)據(jù)庫(kù)的邏輯名稱，這是在SQLServer系統(tǒng)中使用的名稱，是數(shù)據(jù)庫(kù)在SQLServer中的標(biāo)識(shí)符。

FILENAME=os_file_name：指定數(shù)據(jù)庫(kù)所在文件的操作系統(tǒng)文件名稱和路徑，該操作系統(tǒng)文件名和NAME的邏輯名稱一一對(duì)應(yīng)。

SIZE：指定數(shù)據(jù)庫(kù)的初始容量大小。主文件大小至少與model數(shù)據(jù)庫(kù)中的主文件相同。ON：允許顯式定義用于存儲(chǔ)數(shù)據(jù)的文件。

UNLIMITED：指定了文件將一直增長(zhǎng)到磁盤變滿為止。

FILEGROUPfilegroup_name：這是文件組在數(shù)據(jù)庫(kù)中的唯一邏輯名稱。

DEFULT：參數(shù)制定了該文件組為默認(rèn)文件組。

FILEGROWTH=growth_increment：指定文件每次增加容量的大小，該值以當(dāng)前文件大小的百分比或KB、MB、GB和TB來(lái)表示。當(dāng)指定數(shù)據(jù)為0時(shí)，表示文件不增長(zhǎng)。

MAXSIZE=max_size：指定操作系統(tǒng)文件可以增長(zhǎng)到的最大尺寸。以KB、MB、GB和TB為單元，默認(rèn)為MB。提示：區(qū)分NAME與FILENAME。NAME是數(shù)據(jù)庫(kù)內(nèi)部處理文件的邏輯名稱，F(xiàn)ILENAME是數(shù)據(jù)庫(kù)在磁盤上存放文件的文件名稱，兩者可以不同。場(chǎng)景：你正在設(shè)計(jì)開(kāi)發(fā)一個(gè)學(xué)校的圖書(shū)館系統(tǒng)。這個(gè)圖書(shū)館系統(tǒng)帶有一個(gè)藏書(shū)記錄的數(shù)據(jù)庫(kù)。數(shù)據(jù)庫(kù)系統(tǒng)采用SQLServer2008，數(shù)據(jù)庫(kù)名為TestDB。數(shù)據(jù)庫(kù)的初始大小為20MB，日志文件的大小為5MB。由于不能確定學(xué)校將會(huì)把數(shù)據(jù)庫(kù)安裝在哪臺(tái)SQLServer服務(wù)器上，你希望在安裝這個(gè)系統(tǒng)的時(shí)候，能在服務(wù)器上按照要求自動(dòng)創(chuàng)建數(shù)據(jù)庫(kù)。CREATEDATABASETestDBON--允許顯式定義用于存儲(chǔ)數(shù)據(jù)的文件--定義數(shù)據(jù)庫(kù)主文件(NAME='TestDB_Data',--主文件邏輯名稱

FILENAME='D:\DATA\TransactTestDB.mdf',--操作系統(tǒng)文件名稱，包含路徑

SIZE=20MB,--主文件初始大小

FILEGROWTH=0)--未啟用自動(dòng)增長(zhǎng)--定義數(shù)據(jù)庫(kù)日志文件LOGON(NAME='TestDB_Log',--日志文件邏輯名稱

FILENAME='D:\DATA\TestDB_Log.ldf',--包含路徑的操作系統(tǒng)文件名稱

SIZE=5MB,--日志文件初始大小

FILEGROWTH=0)--未啟用自動(dòng)增長(zhǎng)舉例：創(chuàng)建了一個(gè)Test數(shù)據(jù)庫(kù)，該數(shù)據(jù)庫(kù)的主數(shù)據(jù)文件邏輯名稱為Test_data，物理文件名為Test.mdf，初始大小為10MB，最大尺寸為無(wú)限大，增長(zhǎng)速度為10%；數(shù)據(jù)庫(kù)的日志文件邏輯名稱為Test_log，物理文件名為Test.ldf，初始大小為1MB，最大尺寸為5MB，增長(zhǎng)速度為1MB。程序清單如下：CREATEDATAB