Microsoft SQL Server 2005 管理秘訣

1. Microsoft SQL Server 2005 管理秘訣 顧武雄 Jovi Ku jovi@cogate.com.tw

2. 講師介紹 • 高傑信公司-技術顧問 • Microsoft CTEC 教育中心講師 • Microsoft MVP & 特約講師 • Windows &.NET Magazine –特約作者 • Information Security Magazine -專欄作家 • 網路資訊、Run!PC、NetAdmin 電腦雜誌–專欄作家 • 旗標、文魁以及碁鋒圖書作者 • 個人著作: • Microsoft ISA Server 建置與管理 • SharePoint Portal Server徹底研究 • Microsoft Access Project with SQL Server • ISA SERVER 2004 系統安全整合實務 • Small Business Server 2003 系統整合管理實務 • Microsoft Operations Manager 2005 IT智慧整合管理實務

3. 議程 • 基本管理架構介紹 • 基本權限配置介紹 • Database Mail 使用說明 • 備份管理設定說明 • 使用MOM監控SQL Server 2005 • 其它學習補充 • Q&A

4. 基本管理架構介紹

5. SQL Server 2005 管理架構

6. 三個主要管理工具 • Management Studio • 集中管理所有執行個體資料庫、Cube、專案 • 介面區組態（Surface Area Configuration ） • 用來管理SQL Server 2005中各項功能、服務以及遠端連接的啟用、停用、啟動、停止。 • 在系統預設的狀態下會有許多功能選項是關閉的（例如Database Mail），主要目的在於減少可能遭受惡意攻擊的介面。 • SQL Server Configuration Manager • 管理各項SQL Server服務與通訊協定的啟用

7. Management Studio連線管理

8. 專用管理員連接（DAC ） • 在資料庫引擎無法回應一般的連接時，讓管理員依然可以透過查詢編輯模式成功連接到伺服器來進行管理，而不需要進行重新開機或重新啟動服務 • 同一個時間只能夠有一個DAC的連線，否則將會出現相關錯誤訊息。 • 請注意！您並無法使用此方法來開啟物件瀏覽模式

9. DAC連線的使用 • 只要在登入視窗伺服器欄位名稱的執行個體前面加入admin:字串即可，例如admin:sql2005。 • 如果是透過SQLCMD則可以搭配-A的參數來進行連線，或是使用SQLCMD –S admin:sql2005格式也是可以。 • 在預設的狀態下只唯一支援透過本機的DAC連線，如果需要經由遠端來進行DAC連線，則必須預先在SQL Server介面區組態設定中完成啟用設定。 • 不支援在DAC的連線中進行平行查詢的執行以及RESTORE或BACKUP的命令。 完成DAC連線 ! 允許遠端DAC連線！

10. Management Studio連線匯入/匯出管理

11. 混合驗證基本安全設定 • 系統預設建議採用唯一Windows驗證模式。 • 若是採用混合驗證，建議您立即變更或停用預設sa帳戶名稱與密碼強度。

12. Management Studio各類報表檢視

13. 介面區組態工具

14. Configuration Manager 服務帳號與密碼的變更，請由此設定。

15. 基本權限配置介紹

16. 基本權限配置 • 伺服器角色 • 資料庫角色

17. 伺服器角色權限配置（1/2） • Bulk Insert Administrators（bulkadmin）：賦予指定的使用者或群組能夠執行Bulk Insert的命令，來進行大量資料新增的作業。 • Database Creators（dbcreator）：賦予指定的使用者或群組能夠進行資料庫的建立、修改以及刪除作業，而能執行的SQL命令包含了：ALTER DATABASE、CREATE DATABASE、DROP DATABASE、EXTEND DATABASE、RESTORE DATABASE、RESTORE LOG以及預存程序sp_renamedb的執行。 • Disk Administrators（diskadmin）：賦予指定的使用者或群組能夠管理磁碟中的檔案，而可以執行的SQL命令包含了sp_addumpdevice、sp_diskdefault、sp_dropdevice等有關裝置管理的預存程序。 • Process Administrators（processadmin）：賦予指定的使用者或群組能夠管理SQL Server處理序，例如將執行中的特定處理序從記憶體中刪除。

18. 伺服器角色權限配置（2/2） • Security Administrators（securityadmin）：賦予指定的使用者或群組能夠管理者登入帳戶、資料庫權限以及讀取安全訊息記錄，除了可以執行Grant、Deny以及Revoke的SQL命令之外，還能夠執行下列預存程序：sp_addlinkedsrvlogin、sp_addlogin、sp_defaultdb、sp_defaultlanguage、sp_denylogin、sp_droplogin、sp_grantlogin、sp_helplogins、sp_remoteoption以及sp_revokelogin等有關使用者權限配置的預存程序。 • Server Administrators（serveradmin）：賦予指定的使用者或群組能夠管理伺服器的組態設定值，而能夠執行的SQL命令有DBCC FREEPROCCACHE、RECONFIGURE、SHUTDOWN，在預存程序部份的執行權限有sp_configure、sp_fulltext_service以及sp_tableoption等有關伺服器組態管理的指令。 • Setup Administrators（setupadmin）：賦予指定的使用者或群組能夠管理伺服器的連線以及啟動程序，例如可以刪除伺服器的目前連線。 • System Administrators（sysadmin）：賦予指定的使用者或群組能夠管理此SQL Server系統中所有的任何作業。

19. 資料庫角色權限配置（1/2） • db_accessadmin：賦予指定的資料庫使用者擁有管理所屬資料庫使用者帳戶的權限，例如新增或移除指定的使用者帳戶，在預存程序的執行權限部份則包含了sp_addalias、sp_adduser、sp_dropalias、sp_grantdbaccess以及sp_revokedbaccess等有關資料庫使用者管理的指令。 • db_backupoperator：賦予指定的資料庫使用者擁有管理所屬資料庫的備份工作，而所能執行的SQL命令包含了BACKUP DATABASE、BACKUP LOG、CHECKPOINT、DBCC CHECKALLOC、DBCC CHECKCATALOG、DBCC CHECKDB、DBCC TEXTALL、DBCC TEXTALLOC以及 DBCC UPDATEUSAGE等有關於資料庫備份的所有指令。 • db_datareader：賦予指定的資料庫使用者唯一擁有檢視資料庫資料的權限，能夠執行的SQL命令為SELECT。 • db_datawriter：賦予指定的資料庫使用者擁有變更所屬資料庫中的資料表任何資料，能夠執行的SQL命令有SELECT、DELETE、INSERT以及UPDATE。 • db_ddladmin：賦予指定的資料庫使用者擁有執行資料庫定義語言的相關工作，除了有GRANT、DENY以及REVOKE的命令執行權限之外，還能夠執行REFERENCES以及預存程序sp_changeobjectowner、sp_procoption、sp_recompile、sp_rename以及sp_tableoption。

20. 資料庫角色權限配置（2/2） • db_denydatareader：被指定為此資料庫角色的使用者將無法讀取資料庫中任何物件的資料。 • db_denydatawriter：被指定為此資料庫角色的使用者，將無法對資料庫中的資料進行DELETE、INSERT以及UPDATE命令工作。 • db_owner：賦予指定的資料庫使用者擁有所屬資料庫中的任何管理權限。 • db_securityadmin：賦予指定的資料庫使用者擁有所屬資料庫中，管理使用者與物件間的相關權限設定，而在預存程序與執行命令上可以使用的有GRANT、DENY以及REVOKE，sp_addapprole、sp_addrole、sp_addrolemember、sp_approlepassword、sp_changeobjectowner、sp_dropapprole、sp_droprole以及sp_droprolemember。 • public：系統預設所有的使用者都會屬於這個角色的成員之一，就像作業系統中的網域群組Everyone一樣，擁有最低的資料庫使用者權限。

21. 其它權限配置 • 直接設定資料表權限 • 使用Schema群組化概念的權限配置

22. 如得知自己目前的權限清單 • 程式碼範例 • SELECT * FROM fn_my_permissions (NULL, 'DATABASE');

23. Database Mail 使用說明

24. Database Mail • 所有關於SQL Server發送的Email都需要透過它 • 取代原有SQL Mail必須安裝Outlook與透過MAPI連線方式的不便 • 可以結合任何現有的郵件伺服器來使用 • 可以一次設定多組不同Email的SMTP帳戶組態並且排列優先權，如此一來即使發生了某一組的SMTP服務無法正常使用，仍然還可以繼續由下一個順位的SMTP帳戶來進行發信的作業。

25. 設定Database Mail（1/3） • 請開啟至[管理]節點下的[Database Mail]項目，接著按下滑鼠右鍵選取[設定Database Mail]。

26. 設定Database Mail（2/3）

27. 設定Database Mail（3/3）

28. 測試Database Mail

29. 設定操作員

30. 設定警示系統與作業通知

31. 中場休息

32. 備份管理設定說明

33. 備份計畫實施 • 完整備份（Full）：此備份的資料內容將包含所有其下的資料庫物件，以及交易內容。 • 差異性備份（Differential）：根據上一次執行完整備份之後，所進行過的資料異動部份來進行備份，因此備份所需花費時間的長短需依這之間的間隔時間來計算，此外還必須注意此種備份方式無法使用在master系統資料庫之上。 • 交易記錄備份（Transaction Log）：除了會將使用者所執行過的異動命令全部備份起來，並且會記錄下這一次備份開始執行時的所有交易狀態。

34. 資料庫復原模式 • 簡易（Simple）：只要確認交易資料已正確寫入，便會清除交易記錄。 • 大量記錄（Bulk-logged）：凡是透過各種大量匯入資料的方式所完成的異動，相關記錄都不會被保存。 • 完整（Full）：保留所有執行過的交易命令在記錄檔案中。

35. 常見的備份計畫 • 第一個時間點（凌晨:1:00）：完整備份 • 中間各時間點（每半小時）：交易記錄檔備份 • 最後一個時間點（晚上:12:00）：差異備份 • 依實際需求安排每一天為一個覆寫週期，如此一來第一個時間點的備份需要設定為覆寫。 • 更好的方式是以星期為週期單位，例如星期一覆寫星期一的備份.. 。

36. 可以先建立備份裝置 • 請經由[伺服器物件]\[備份裝置]節點上，按下滑鼠右鍵點選[新增備份裝置]來建立即可。

37. 開始備份設定（1/4） 請注意！此設定

38. 開始備份設定（2/4）

39. 開始備份設定（3/4） 可編輯原始程式碼！ 開始設定備份排程！

40. 開始備份設定（4/4） • 請陸續重複完成其它備份排程作業設定，這一些包含了交易記錄檔與差異性備份作業。 設定備份作業執行完成後的各類通知方式。

41. 可以手動執行備份作業

42. 作業活動監視器 滑鼠點兩下可進行編輯！

43. 檢視歷程記錄

44. 還原資料庫備份 • 請在該資料庫項目上按下滑鼠右鍵點選[工作]\[還原]\[資料庫] 。 勾選特定時間點的還原！

45. 新增支援線上還原 • 唯一在企業版提供 • 唯一提供在完整模式、大量記錄 • 在預設狀態下，還原一個檔案或一個Page是自動以線上還原方式進行 • 當任何檔案群組中的檔案進行還原時，此檔案群組是處於離線狀態。 • 當任何資料庫在進行主要檔案群組的檔案還原時，在這期間該資料庫是處於離線狀態。

46. 如何縮減交易記錄檔大小 • 進行一次完整備份 • 執行資料庫快捷選單[工作]\[壓縮]\[檔案] 或是 • 不備份，直接在查詢視窗中執行 • Backup Log 資料庫名稱 with no_log • 接著執行下列命令語法 • DBCC Shrinkfile(記錄檔邏輯名稱，所要還原的大小)

47. 使用維護計畫 • 可採用設計模式自行規劃作業與流程 • 可直接透過精靈工具快速完成設定 設計模式 可用的作業項目

48. SQL Server 維護計畫精靈（1/4） • 可以選擇性的完成以下作業安排 • 資料庫完整性檢查 • 執行索引維護 • 更新資料庫統計資料 • 執行資料庫備份

49. 維護計畫精靈（2/4） • 設定目標伺服器、連線驗證方式 • 選取工作項目

50. 維護計畫精靈（3/4） • 設定工作項目執行順序 • 設定備份工作中的資料庫、路徑