第8章 SQL Server 系統(tǒng)管理,8.1安全性管理,8.3 數(shù)據(jù)庫恢復(fù),8.2數(shù)據(jù)庫備份,8.4 數(shù)據(jù)復(fù)制,8.5 事務(wù),8.6 鎖,8.7 SQL Server 自動化管理,8.8 數(shù)據(jù)的導(dǎo)入與導(dǎo)出,主要內(nèi)容,SQL Server 2005的安全機(jī)制數(shù)據(jù)庫管理系統(tǒng)相關(guān)知識 備份與恢復(fù)數(shù)據(jù)庫SQL SERVER 2005的新特性 了解事務(wù)和鎖 掌握SQL Server 2005數(shù)據(jù)導(dǎo)入與導(dǎo)出的方法,8.1安全性管理,8.1.1 認(rèn)證模式與訪問權(quán)限 SQL Server 2005支持兩種身份驗證模式：Windows身份驗證模式和混合身份驗證模式 Windows認(rèn)證模式：采用Windows認(rèn)證進(jìn)程來確認(rèn)用戶的身份。 SQL Server認(rèn)證模式:用戶必須提供SQL Server管理員為其提供的登錄名和登錄密碼，用戶身份的驗證由SQL Server自身完成。,8.1.2 用戶管理 用戶賬號與登錄賬號:一個合法的登錄賬號只表明該賬號通過了Windows認(rèn)證或SQL Server認(rèn)證，但不能表明其可以對數(shù)據(jù)庫數(shù)據(jù)和數(shù)據(jù)對象進(jìn)行某種或某些操作。一個登錄賬號總是與一個或多個數(shù)據(jù)庫用戶賬號相對應(yīng)，這樣才可以訪問數(shù)據(jù)庫。 用戶賬號操作: 添加用戶 刪除用戶 授權(quán),8.1.3 角色管理 角色提供了一種把用戶匯集成一個單元，以便進(jìn)行許可管理的方法。一個角色包含了許多成員，這些成員都繼承了角色所擁有的許可。在SQL Server中，角色有兩種主要類型：服務(wù)器角色和數(shù)據(jù)庫角色。 1）Public角色 2）固定服務(wù)器角色 3）固定數(shù)據(jù)庫角色 4）用戶自定義的角色 5）應(yīng)用程序角色,8.1.4 架構(gòu),架構(gòu)是形成單個命名空間的數(shù)據(jù)庫實體的集合。命名空間是一個集合，其中每個元素的名稱都是唯一的。 在 SQL Server 2005 中，架構(gòu)獨立于創(chuàng)建它們的數(shù)據(jù)庫用戶而存在?？梢栽诓桓募軜?gòu)名稱的情況下轉(zhuǎn)讓架構(gòu)的所有權(quán)。 完全限定的對象名稱現(xiàn)在包含四部分： server.database.schema.object。 默認(rèn)架構(gòu)：用于解析未使用其完全限定名稱引用的對象的名稱。,8.2數(shù)據(jù)庫備份 為了防止計算機(jī)出現(xiàn)災(zāi)難事故而導(dǎo)致數(shù)據(jù)庫被破壞，對數(shù)據(jù)庫的備份工作是不可忽視的，要采取一定的備份策略來保證數(shù)據(jù)庫的安全，一旦計算機(jī)發(fā)生事故時，能夠采取及時的恢復(fù)操作。,備份就是制作數(shù)據(jù)庫結(jié)構(gòu)和數(shù)據(jù)的副本，以便在數(shù)據(jù)庫遭到破壞的時候能夠修復(fù)數(shù)據(jù)庫。會造成數(shù)據(jù)庫破壞的常見原因包括： 用戶不正確的操作破壞了數(shù)據(jù)或數(shù)據(jù)被病毒破壞 硬件故障，如磁盤損壞等 遭遇自然災(zāi)害 遭遇盜竊等意外事故 備份的內(nèi)容不僅包括用戶的數(shù)據(jù)庫內(nèi)容，還包括系統(tǒng)數(shù)據(jù)庫的內(nèi)容。,8.2.1 備份簡介,8.2.2 備份許可及介質(zhì),可以備份數(shù)據(jù)庫的角色成員有： 固定的服務(wù)器角色sysadmin 固定的數(shù)據(jù)庫角色db_owner 固定的數(shù)據(jù)庫角色db_backupoperator 用戶自定義角色，并且授權(quán)這些角色執(zhí)行備份數(shù)據(jù)庫的許可 備份介質(zhì)： 磁盤 磁帶 網(wǎng)絡(luò),8.2.3 備份類型,數(shù)據(jù)庫備份包括完整備份和完整差異備份。完整備份包含數(shù)據(jù)庫中的所有備份，并且可以用作完整差異備份的“基準(zhǔn)備份”。完整差異備份僅記錄自前一完整備份發(fā)生更改的數(shù)據(jù)擴(kuò)展盤區(qū)數(shù)據(jù)。在SQL server中，主要的備份類型有： 完整備份 完整差異備份 部分備份 部分差異備份 文件和文件組備份 文件差異備份 事務(wù)日志備份,8.2.4執(zhí)行備份,1完整備份,2. 完整差異備份 打開SQL Server Management Studio，在【對象資源管理器】中展開【數(shù)據(jù)庫】，選擇【教務(wù)管理】數(shù)據(jù)庫，右鍵單擊鼠標(biāo)，在彈出的快捷菜單上選擇【任務(wù)】、【備份】選項。進(jìn)入數(shù)據(jù)庫備份窗口。在【備份數(shù)據(jù)庫教務(wù)管理】窗口中，選擇備份類型為【差異】，在下面的【目標(biāo)】窗格中，默認(rèn)為【備份到：磁盤】，指定備份的文件位置，例如指定為D:diffBackUp，然后單擊【確定】按鈕。 3使用backup命令備份 例8-6 使用backup命令完整備份 “教務(wù)管理”數(shù)據(jù)庫。 Backup database 教務(wù)管理 To disk=f:MSSQLBACKUP教務(wù)管理備份.bak 例8-7 使用backup命令對 “教務(wù)管理”數(shù)據(jù)庫進(jìn)行差異備份。 Backup database 教務(wù)管理 To disk=f:MSSQLBACKUP教務(wù)管理差異備份1.bak with differential,在對數(shù)據(jù)庫進(jìn)行備份時可以選擇某種備份策略來進(jìn)行備份。選擇備份策略，一方面要基于數(shù)據(jù)庫本身特點來考慮，另一方面要考慮性能。 數(shù)據(jù)庫本身比較小的情況：若此數(shù)據(jù)庫的數(shù)據(jù)是只讀的或很少修改，則選用完整數(shù)據(jù)庫備份策略是合適的。 頻繁修改的數(shù)據(jù)庫：完整數(shù)據(jù)庫備份和事務(wù)日志備份相結(jié)合的策略是比較流行的備份選擇。 需要很短的時間內(nèi)恢復(fù)數(shù)據(jù)庫：可以選擇差異備份的策略。 海量數(shù)據(jù)的數(shù)據(jù)庫：選用文件或文件組備份比較適合。,8.2.5 備份策略及性能考慮,8.3.1 恢復(fù)的概念及模式 恢復(fù)是在數(shù)據(jù)庫遭遇破壞后從一個或多個備份中還原數(shù)據(jù)庫。還原的操作是在“恢復(fù)模式”下進(jìn)行的。在SQL Server 2005數(shù)據(jù)庫管理系統(tǒng)中有三種恢復(fù)模式： 簡單恢復(fù)模式 完整恢復(fù)模式 大容量日志恢復(fù)模式,8.3 數(shù)據(jù)庫恢復(fù),8.3.2 恢復(fù)數(shù)據(jù)庫注意事項 準(zhǔn)備工作： 1、恢復(fù)的權(quán)限 2、檢查系統(tǒng) 3、檢查文件 4、限制用戶對數(shù)據(jù)庫的訪問，并備份事務(wù)日志,1還原完整備份 2還原完整差異備份： 還原完整差異備份的步驟與還原完整備份的步驟大體相同，只是在進(jìn)入【還原數(shù)據(jù)庫】的【常規(guī)】頁窗口后，指定用于還原的備份集為先前進(jìn)行了備份的差異備份集,8.3.3 在SQL Server Management Studio中恢復(fù)數(shù)據(jù)庫,8.3.4 用T-SQL語句恢復(fù)系統(tǒng)數(shù)據(jù)庫 RESTORE DATABASE語句用于恢復(fù)數(shù)據(jù)庫，其語法格式為： RESTORE DATABASE database_name | database_name_var FROM ,.n WITH CHECKSUM | NO_CHECKSUM , CONTINUE_AFTER_ERROR | STOP_ON_ERROR , FILE = file_number | file_number , KEEP_REPLICATION , MEDIANAME = media_name | media_name_variable , MEDIAPASSWORD = mediapassword | mediapassword_variable , MOVE logical_file_name TO operating_system_file_name ,.n , PASSWORD = password | password_variable , RECOVERY | NORECOVERY | STANDBY = standby_file_name | standby_file_name_var ,8.3.5 建立自備份的維護(hù)計劃 SQL Server 2005的數(shù)據(jù)庫維護(hù)計劃可以指定作業(yè)運行的時間，多個作業(yè)可以根據(jù)一個計劃運行，多個計劃也可以應(yīng)用到一個作業(yè)。根據(jù)作業(yè)運行的時間的不現(xiàn)可將計劃類型分為以下如圖8.22所示的四種：,8.4.1 復(fù)制的概念 將數(shù)據(jù)和數(shù)據(jù)庫對象從一個數(shù)據(jù)庫復(fù)制和分發(fā)到另一個數(shù)據(jù)庫，然后在數(shù)據(jù)庫間進(jìn)行同步，以維持一致性。 1發(fā)布服務(wù)器 發(fā)布服務(wù)器是一種數(shù)據(jù)庫實例，它通過復(fù)制向其他位置提供數(shù)據(jù)。 2分發(fā)服務(wù)器 分發(fā)服務(wù)器也是一種數(shù)據(jù)庫實例，它起著存儲區(qū)的作用，用于復(fù)制與一個或多個發(fā)布服務(wù)器相關(guān)聯(lián)的特定數(shù)據(jù)。 3訂閱服務(wù)器 訂閱服務(wù)器是接收復(fù)制數(shù)據(jù)的數(shù)據(jù)庫實例。 4項目 項目用于識別發(fā)布中包含的數(shù)據(jù)庫對象。 5發(fā)布 發(fā)布是來自一個數(shù)據(jù)庫的一個或多個項目的集合，將多個項目分組成一個發(fā)布更便于指定一組作為一個單元復(fù)制的、具有邏輯關(guān)系的數(shù)據(jù)庫對象和數(shù)據(jù)。 6訂閱 訂閱是把發(fā)布副本傳遞到訂閱服務(wù)器的請求。,8.4 數(shù)據(jù)復(fù)制,8.4.2復(fù)制類型 Microsoft SQL Server提供3種復(fù)制類型： 1事務(wù)性復(fù)制 2合并復(fù)制 3快照復(fù)制,8.4.3復(fù)制代理 復(fù)制使用許多稱為代理的獨立程序執(zhí)行與跟蹤更改和分發(fā)數(shù)據(jù)關(guān)聯(lián)的任務(wù)。默認(rèn)情況下，復(fù)制代理作為SQL Server代理安排的作業(yè)運行。 1SQL Server 代理 SQL Server 代理保存并安排復(fù)制中使用的代理，并提供運行復(fù)制代理的簡便方法。SQL Server 代理還控制和監(jiān)視復(fù)制之外的操作。 2快照代理 快照代理準(zhǔn)備已發(fā)布表的架構(gòu)和初始數(shù)據(jù)文件以及其他對象、存儲快照文件并記錄分發(fā)數(shù)據(jù)庫中的同步信息。 3日志讀取器代理 它將發(fā)布服務(wù)器上的事務(wù)日志中標(biāo)記為復(fù)制的事務(wù)移至分發(fā)數(shù)據(jù)庫中。 4分發(fā)代理 它將初始快照應(yīng)用于訂閱服務(wù)器，并將分發(fā)數(shù)據(jù)庫中保存的事務(wù)移至訂閱服務(wù)器。 5合并代理 合并代理與合并復(fù)制一起使用。它將初始快照應(yīng)用于訂閱服務(wù)器，并移動和協(xié)調(diào)所發(fā)生的增量數(shù)據(jù)更改。 6隊列讀取器代理 隊列讀取器代理與包含排隊更新選項的事務(wù)性復(fù)制一起使用。該代理運行于分發(fā)服務(wù)器，并將訂閱服務(wù)器上所做更改移回至發(fā)布服務(wù)器。,8.4.4 發(fā)布服務(wù)器與發(fā)布 默認(rèn)情況下，分發(fā)服務(wù)器與發(fā)布服務(wù)器是同一臺服務(wù)器（本地發(fā)布服務(wù)器），也可以是與發(fā)布服務(wù)器不同的服務(wù)器（遠(yuǎn)程發(fā)布服務(wù)器）。創(chuàng)建本地發(fā)布服務(wù)器的配置步驟： 啟動【新建發(fā)布向?qū)А?選擇要發(fā)布數(shù)據(jù)的數(shù)據(jù)庫 選擇發(fā)布類型 選擇發(fā)布項目 設(shè)置篩選表行和快照代理，創(chuàng)建快照并設(shè)置快照運行時間 設(shè)置快照代理安全性 完成發(fā)布向?qū)?，?chuàng)建本地發(fā)布,8.4.5 訂閱服務(wù)器與訂閱 訂閱是對發(fā)布中的數(shù)據(jù)和數(shù)據(jù)庫對象的副本進(jìn)行請求。訂閱定義將接收哪個發(fā)布以及接收的時間和位置。創(chuàng)建本地訂閱的步驟： 通過【訂閱向?qū)А可杀镜赜嗛?選擇發(fā)布服務(wù)器中的數(shù)據(jù)庫和發(fā)布 選擇發(fā)布代理位置 選擇訂閱服務(wù)器，指定訂閱數(shù)據(jù)庫 設(shè)置分發(fā)代理安全性，指定同步此訂閱時，運行分發(fā)代理服務(wù)器的域或計算機(jī)賬戶 指定代理的同步計劃 初始化訂閱 創(chuàng)建訂閱,8.5.1 事務(wù)及工作原理 1事務(wù) 事務(wù)是用戶定義的一個數(shù)據(jù)庫操作序列，這些操作要么全做要么全不做，是一個不可分割的工作單位。 2事務(wù)特性 原子性(Atomicity)、一致性(Consistency)、隔離性(Isolation)和持續(xù)性(Durability)。這四個特性簡稱為ACID特性。 3SQL Server通過記錄事務(wù)日志保持事務(wù)的持久性。 數(shù)據(jù)庫系統(tǒng)一般采用記錄日志和恢復(fù)機(jī)制來保證事務(wù)的一致性；采用并發(fā)控制機(jī)制來保證多個事務(wù)的交叉運行不影響這些事務(wù)的原子性。 4事務(wù)的工作原理（COMMIT,ROLLBACK,UNDO,REDO）,8.5 事務(wù),8.5.2執(zhí)行事務(wù)及執(zhí)行模式 1事務(wù)的執(zhí)行 應(yīng)用程序主要通過指定事務(wù)啟動和結(jié)束的時間來控制事務(wù)。 2事務(wù)的類型 根據(jù)運行模式，SQL Server 2005將事務(wù)分為4種類型：自動提交事務(wù)、顯式事務(wù)、隱式事務(wù)和批處理事務(wù)。 1） 顯式事務(wù) 顯式事務(wù)指每個事務(wù)通過API函數(shù)或通過發(fā)出Transact-SQL BEGIN TRANSACTION 語句來顯式啟動事務(wù)，以COMMIT或ROLLBACK語句顯式結(jié)束。 2） 自動提交事務(wù) 自動提交事務(wù)是指每條語句都是一個事務(wù)。 3） 隱式事務(wù) 隱式事務(wù)是指在前一個事務(wù)完成時新事務(wù)隱式啟動，但每個事務(wù)仍以COMMIT或ROLLBACK語句顯式完成。 4） 批范圍的事務(wù) 只適用于多個活動的結(jié)果集 (MARS)，在 MARS 會話中啟動的 Transact-SQL 顯式或隱式事務(wù)將變成批范圍的事務(wù)。,8.5.3 編寫事務(wù) 控制事務(wù)用到的T-SQL語句: 1BEGIN TRANSACTION 語句 2COMMI T TRANSACTION語句 3ROLLBACK TRANSACTION 或 ROLLBACK WORK 語句 例8-12 定義一個事務(wù)，將所有外聘教師的考評成績定為良好，并提交該事務(wù)。 BEGIN TRANSACTION EX_teacher_grade UPDATE 考評 SET 得分情況=良好 WHERE 檔案類型=外聘 GO COMMIT TRANSACTION GO,8.5.4 事務(wù)保存點 保存點提供了一種機(jī)制，用于回滾部分事務(wù)。在創(chuàng)建保存點后，可以執(zhí)行 ROLLBACK TRANSACTION savepoint_name 語句以回滾到保存點，而不是回滾到事務(wù)的起點。 語法格式為： SAVE TRAN | TRANSACTION savepoint_name | savepoint_variable ; ,8.5.5事務(wù)隔離級別 隔離級別定義一個事務(wù)必須與其他事務(wù)所進(jìn)行的資源或數(shù)據(jù)更改相隔離的程度。 Microsoft SQL Server 2005的隔離級別由低到高包括： 未提交讀（隔離事務(wù)的最低級別，只能保證不讀取物理上損壞的數(shù)據(jù)） 已提交讀（數(shù)據(jù)庫引擎的默認(rèn)級別） 可重復(fù)讀 可序列化（隔離事務(wù)的最高級別，事務(wù)之間完全隔離） Transact-SQL使用 SET TRANSACTION ISOLATION LEVEL 語句設(shè)置隔離級別,8.6.1 鎖及其作用 當(dāng)多個用戶并發(fā)地存取數(shù)據(jù)庫時會產(chǎn)生多個事務(wù)同時存取同一數(shù)據(jù)的情況，如果對并發(fā)操作不加控制就可能會存取和存儲不正確的數(shù)據(jù)，破壞事務(wù)的一致性和數(shù)據(jù)庫的致性。 由多用戶并發(fā)事務(wù)執(zhí)行所導(dǎo)致的數(shù)據(jù)不一致可能會導(dǎo)致下面幾種情況： 1丟失修改 2不可重復(fù)讀 3臟讀 (Dirty Read) 鎖是防止其他事務(wù)訪問指定的資源控制、實現(xiàn)并發(fā)控制的一種主要手段。封鎖是指一個事務(wù)在對某個數(shù)據(jù)對象操作之前，先向系統(tǒng)提出請求，對其加鎖，在事務(wù)結(jié)束之后釋放鎖。在事務(wù)釋放它的鎖之前，其他事務(wù)不能更新此數(shù)據(jù)對象。,8.6 鎖,8.6.2 鎖的對象與模式 1鎖的對象 SQL Server具有多粒度鎖定，允許一個事務(wù)鎖定不同類型的資源。鎖的對象包括下表所列的資源：,8.6.2 鎖的對象與模式 2鎖模式 1） 共享鎖 共享鎖（S 鎖）允許并行事務(wù)讀取同一種資源。 2）排他鎖 排他鎖（X 鎖）可以防止并發(fā)事務(wù)對資源進(jìn)行訪問。 3） 更新鎖 更新鎖（U 鎖）可以防止常見的死鎖。一次只有一個事務(wù)可以獲得資源的更新鎖（U 鎖）。如果事務(wù)修改資源，則更新鎖（U 鎖）轉(zhuǎn)換為排他鎖（X 鎖）。 4） 意向鎖 數(shù)據(jù)庫引擎使用意向鎖來保護(hù)共享鎖（S 鎖）或排他鎖（X 鎖）放置在鎖層次結(jié)構(gòu)的底層資源上。 意向鎖有兩種用途： 防止其他事務(wù)以會使較低級別的鎖無效的方式修改較高級別資源。 提高數(shù)據(jù)庫引擎在較高的粒度級別檢測鎖沖突的效率。,8.6.3 死鎖問題 死鎖是有兩個或以上的事務(wù)處于等待狀態(tài)，每個事務(wù)都在等待另一個事務(wù)解除封鎖，它才能繼續(xù)執(zhí)行下去，結(jié)果任何一個事務(wù)都無法執(zhí)行，這種現(xiàn)象就是死鎖。 下面兩種情況下可能會出現(xiàn)死鎖： 1兩個事務(wù)同時鎖定了兩個單獨的對象，并且第一個事務(wù)要求在另外一個事務(wù)鎖定的對象上獲得一鎖； 2在一個數(shù)據(jù)庫中有若干個長時間運行的事務(wù)執(zhí)行并操作。 降低死鎖的原則： 1按同一順序訪問對象 2避免事務(wù)中的用戶交互 3保持事務(wù)簡短并在一個批處理中 4使用低隔離級別,8.6.4 查看鎖信息 Microsoft SQL Server 2005 提供的獲取有關(guān)數(shù)據(jù)庫引擎實例中的當(dāng)前鎖活動的信息的方法： 使用 SQL Server Profiler，可以指定用來捕獲有關(guān)跟蹤中鎖事件的信息的鎖事件類別。在系統(tǒng)監(jiān)視器中，可以從鎖對象指定計數(shù)器來監(jiān)視數(shù)據(jù)庫引擎實例中的鎖級別。 操作步驟： 進(jìn)入SQL Server Profiler窗口 在SQL Server Profiler窗口中，選擇【文件】菜單【新建跟蹤】。 在【跟蹤屬性】對話框中設(shè)置跟蹤，設(shè)置跟蹤名稱和使用模板，可以將跟蹤指定到文件存儲，也可以保存到指定的表。 完成跟蹤的建立后，即可在SQL Server Profiler窗口中查處跟蹤的事件。,8.7.1 作業(yè)管理 作業(yè)是一系列由 SQL S