1 / 70

SQL Server 2005 資料庫管理達人一日體驗營

SQL Server 2005 資料庫管理達人一日體驗營. 錢曉明 資策會 資深講師. 議程. SQL Server 2005 新功能槪述 檢視資料庫 Metadata 災難復原 Piecemeal Restore Restore a damaged page online 如何利用資料庫快照還原被刪除的資料表 ? 資料庫監控與效能調教 效能最佳化模型 Default Trace 透過 DMV 及 DMF 檢視效能瓶頸 異地備援 Peer-to-Peer Replication 交易鎖定 Snapshot Isolation Level 高可用度

macaulay-peck
Download Presentation

SQL Server 2005 資料庫管理達人一日體驗營

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Server 2005 資料庫管理達人一日體驗營 錢曉明 資策會 資深講師

  2. 議程 • SQL Server 2005 新功能槪述 • 檢視資料庫 Metadata • 災難復原 • Piecemeal Restore • Restore a damaged page online • 如何利用資料庫快照還原被刪除的資料表? • 資料庫監控與效能調教 • 效能最佳化模型 • Default Trace • 透過DMV及DMF檢視效能瓶頸 • 異地備援 • Peer-to-Peer Replication • 交易鎖定 • Snapshot Isolation Level • 高可用度 • Database Mirroring Configurations

  3. What Is SQL Server 2005? Integration Services Notification Services Analysis Services SQL Server 2005 Full-Text Search Relational Database Engine Reporting Services .NET CLR Service Broker Replication Native HTTP Support

  4. SQL 2005 Top 30 –管理 管理 開發 智慧 管理 開發 B I • Database Mirroring • Online Indexing Operations • New Integrated Toolset • Snapshot Isolation • Data Partitioning • Mirrored Backups • Online Restore • Fast Recovery • Dedicated Administrator Connection • Replication Enhancements

  5. SQL 2005 Top 30 –開發 管理 開發 智慧 管理 開發 B I • .NET Framework Hosting • XML Technologies • ADO.NET 2.0 • Transact-SQL Enhancements • SQL Service Broker • Notification Services • Web Services • XQuery Support • Full-Text Search Enhancements • Security Enhancements

  6. SQL 2005 Top 30 –商業智慧 管理 開發 智慧 管理 開發 B I • Analysis Services • SSIS (Data Transformation Services) • Data Mining • Reporting Services • Clustering Support • Key Performance Indicators • Scalability and Performance • One-Click Cube • Architecture Enhancements • Integration with the Microsoft Office System

  7. SQL Server 2000 Silver Bullets

  8. SQL Server 2005 Changes

  9. Standard Edition Enterprise Edition • Multiple CPU index operations • Automatic index view usage • Single CPU index operations • Manual index views usage Database Performance No Scale-up Limited scale out Scale-up included Scale out included DatabaseScale • Service Broker • Peer-to-Peer replication • Service Broker Only Partitioning Up to 4 CPUs Up to 64 CPUs Hardware

  10. SQL Server 2000: Database is available after Undo completes SQL Server 2005 Standard Edition: Database is available when Undo begins SQL Server 2005 Enterprise Edition: Database recovery leverages all CPU’s  Faster Recovery Database is Available! Redo Undo Undo Redo Database is Available! Redo Undo Database is Available!

  11. Increasing Availability with EE Protect from User & Logical Errors Recover from user & logical errors Prevent user errors Decrease Maintenance Downtime Online Service Pack Install & Upgrades Online Maintenance Operations Recovery from Failures Disaster Recovery Recovery from HW/SW Failure Recovery from Database Failure Database Snapshots DDL Triggers DB Mirroring, Clustering & Peer-to-Peer Replication Online Index Operations Database Mirroring Mirroring, Clustering & Replication Fast Recovery, Online Restore

  12. 議程 • SQL Server 2005 新功能槪述 • 檢視資料庫 Metadata • 災難復原 • Piecemeal Restore • Restore a damaged page online • 如何利用資料庫快照還原被刪除的資料表? • 資料庫監控與效能調教 • 效能最佳化模型 • Default Trace • 透過DMV及DMF檢視效能瓶頸 • 異地備援 • Peer-to-Peer Replication • 交易鎖定 • Snapshot Isolation Level • 高可用度 • Database Mirroring Configurations

  13. SQL Server Management Studio Reports • Server • Memory Consumption • DBCC DropCleanBuffers • Database • Disk Usage • Service Broker • Service Broker Statistics • Logins • Login Statistics • Management • Number of Errors

  14. Disk Usage Report

  15. 議程 • SQL Server 2005 新功能槪述 • 檢視資料庫 Metadata • 災難復原 • Piecemeal Restore • Restore a damaged page online • 如何利用資料庫快照還原被刪除的資料表? • 資料庫監控與效能調教 • 效能最佳化模型 • Default Trace • 透過DMV及DMF檢視效能瓶頸 • 異地備援 • Peer-to-Peer Replication • 交易鎖定 • Snapshot Isolation Level • 高可用度 • Database Mirroring Configurations

  16. SQL Server Backup Types

  17. What Are Recovery Models?

  18. What Is a File or Filegroup Backup Strategy? • Use file or filegroups if: • Database is very large • A full backup would take too long • Back up transaction logs separately • Can be complex to manage Datafile 1 Data file 2 Data file 3 Sunday Monday Tuesday Wednesday

  19. How to Perform a Piecemeal Restore • Restore a database in stages based on filegroups • Start with primary filegroup • Continue with secondary filegroups • Each filegroup comes online as it is restored • Transactions involving filegroups not yet restored will hold locks until rollback can complete

  20. Overview of Online Restore Operations Database Primary filegroup online, database available Primary Filegroup Filegroup A File unavailable, filegroup offline Restoringfile Filegroup B All files available, filegroup online

  21. 議程 • SQL Server 2005 新功能槪述 • 檢視資料庫 Metadata • 災難復原 • Piecemeal Restore • Restore a damaged page online • 如何利用資料庫快照還原被刪除的資料表? • 資料庫監控與效能調教 • 效能最佳化模型 • Default Trace • 透過DMV及DMF檢視效能瓶頸 • 異地備援 • Peer-to-Peer Replication • 交易鎖定 • Snapshot Isolation Level • 高可用度 • Database Mirroring Configurations

  22. How to Restore a Damaged Page • Identify ID of damaged page • Restore page from a full or differential backup • Use PAGE clause to specify pages to restore • Specify NORECOVERY • Restore transaction logs • Backup transaction log tail • Restore transaction log tail 1 2 RESTORE DATABASE AdventureWorks PAGE = '1:610' FROM AWBack WITH NORECOVERY 3 4 5

  23. 議程 • SQL Server 2005 新功能槪述 • 檢視資料庫 Metadata • 災難復原 • Piecemeal Restore • Restore a damaged page online • 如何利用資料庫快照還原被刪除的資料表? • 資料庫監控與效能調教 • 效能最佳化模型 • Default Trace • 透過DMV及DMF檢視效能瓶頸 • 異地備援 • Peer-to-Peer Replication • 交易鎖定 • Snapshot Isolation Level • 高可用度 • Database Mirroring Configurations

  24. What Is a Database Snapshot? • Read-only, consistent view of a database at a specified point in time • Useful as test or development database, or for reporting • Must exist on same server as source database 12:00 Snapshot

  25. How Database Snapshots Work Source DB Snapshot DB SELECT … Copy-On-Write (first time only) UPDATE … SELECT …

  26. How to Use a Database Snapshot to Recover Data Script the object in the database snapshot 1 Execute the script in the source database 2 Repopulate the object (if appropriate) 3 Caution: Not a substitute for a comprehensive backup and restore strategy INSERT INTO Production.WorkOrderRouting SELECT * FROM AdventureWorks_dbsnapshot_1800.Prod.WorkOrderRouting UPDATE HR.Department SET Name = ( SELECT Name FROM AdventureWorks_dbsnapshot_1800.HR.Department WHERE DepartmentID = 1) WHERE DepartmentID = 1

  27. Read-only, consistent copy of database Very fast to create Can be created for any database Production databases Instant standby databases with Database Mirroring Supports scale-out and high availability scenarios Great solution for reporting/read-only applications Safeguarding data against application and human error Rollback to a snapshot to correct the error

  28. 議程 • SQL Server 2005 新功能槪述 • 檢視資料庫 Metadata • 災難復原 • Piecemeal Restore • Restore a damaged page online • 如何利用資料庫快照還原被刪除的資料表? • 資料庫監控與效能調教 • 效能最佳化模型 • Default Trace • 透過DMV及DMF檢視效能瓶頸 • 異地備援 • Peer-to-Peer Replication • 交易鎖定 • Snapshot Isolation Level • 高可用度 • Database Mirroring Configurations

  29. 效能最佳化模型 伺服器 調校 交易鎖定 索引最佳化 查詢最佳化 資料庫結構設計

  30. 資料庫設計迷思 • 到底需不需要正規化呢？ • 資料庫交易記錄檔應該設多大？ • 索引到底該如何建才正確？ • 要如何才能避免資料被Lock呢？

  31. 正規化（Normalization） • 正規化（Normalization）的目的是減少「重複累贅的資料」。 • 一般只會實施到第三正規化。 • 優點--方便維護及修改。 • 缺點--查詢資料時需要Join 較多的資料表才能得到所需資料，因此回應時間加長。 • 檢視標準--每次存取資料時均要Join 六、七個資料表，回應時間加長，既表示過渡正規化。

  32. 應用程式特性（一） • 線上交易（OLTP）應用程式 • 「多人同時上線」(Concurrency) • 「頻繁」的從事「小量交易」 • 使用指令Select/Insert/Update/ Delete • 主要 workload • Reads, writes • 可能問題 • Locking，Blocking，Disk I/O，CPU，Memory • 可能的解決方案 • 可以做正規化 • 刪除不必要的索引 • 使用Transaction Isolation Level Snapshot • 非同步處理（Asynchronous data access)

  33. 應用程式特性（二） • Decision Support System（DSS）應用程式 • OLAP • Data Mining • 報表製作（Reporting） • 特性 • 讀取大量資料，然後作彙總計算 • 使用Select • 主要 workload • Reads • 可能問題 • 大量硬碟 I/O • 資料碎裂 • 可能的解決方案 • 可以做非正規化 • 建立索引 • 索引重整（Index Rebuild）

  34. 索引最佳化 • 建在單一欄位上的非叢集式索引，百分之九十的情況下，Query Analyzer會選擇使用Table Scan，而不會使用該索引。 • 資料經過一段時間，新增/修改/刪除之後，會產生碎裂，應執行索引重整 （類似磁碟機重整）。 • 作索引重整（Index Rebuild）對提升報表製作（Reporting）應用程式效能有幫助。

  35. 議程 • SQL Server 2005 新功能槪述 • 檢視資料庫 Metadata • 災難復原 • Piecemeal Restore • Restore a damaged page online • 如何利用資料庫快照還原被刪除的資料表? • 資料庫監控與效能調教 • 效能最佳化模型 • Default Trace • 透過DMV及DMF檢視效能瓶頸 • 異地備援 • Peer-to-Peer Replication • 交易鎖定 • Snapshot Isolation Level • 高可用度 • Database Mirroring Configurations

  36. Default Trace • 佔資源少 • 預設路徑 C:\Program Files\Microsoft SQL Server\ MSSQL.1\MSSQL\LOG • 預設追蹤事件約三十多項，如： • Audit Addlogin Event • Audit Login Failed • Audit Backup/Restore Event • Data File Auto Grow • Database Mirroring State Change • Object: Created • Object: Altered • Object: Deleted • Server Memory Change

  37. Disable Default Trace SP_CONFIGURE 'show advanced options',1 go RECONFIGURE GO SP_CONFIGURE 'DEFAULT TRACE ENABLED',1 GO RECONFIGURE GO

  38. 議程 • SQL Server 2005 新功能槪述 • 檢視資料庫 Metadata • 災難復原 • Piecemeal Restore • Restore a damaged page online • 如何利用資料庫快照還原被刪除的資料表? • 資料庫監控與效能調教 • 效能最佳化模型 • Default Trace • 透過DMV及DMF檢視效能瓶頸 • 異地備援 • Peer-to-Peer Replication • 交易鎖定 • Snapshot Isolation Level • 高可用度 • Database Mirroring Configurations

  39. 透過DMV及DMF檢視效能瓶頸 • SQL Server 2000，必需得靠Profiler記錄的trace file（追蹤記錄檔）。 • SQL Server 2005中新增許多系統檢視及系統函數 • Dynamic Management View（DMV） • Dynamic Management Function（DMF）。

  40. Dynamic Management View（DMV） Dynamic Management Function（DMF） • 與系統資料表不同，DMV 不會存檔。 • DMV中的紀錄會隨著資料庫的使用狀態而改變 • DMV/DMF的命名規則 • 命名一定是dm_* • DMV及DMF均屬於sys 這個schema • 用時一定要寫two-part name 或three-part name，如sys.dm_*

  41. Dynamic Management View（DMV） Dynamic Management Function（DMF） • DMV/DMF 可大分為兩類，伺服器層級及元件層級 • 伺服器層級（Server Level） • sys.dm_exec_* • 記錄使用者程式碼執行狀態。 • sys.dm_os_* • 記錄伺服器層級系統相關資訊，例如記憶體，locking，及排程等。 • sys.dm_tran_* • 記錄Transactions 及 isolation 相關資訊。 • sys.dm_io_* • 記錄網路及硬碟上的I/O。 • sys.dm_db_* • 記錄資料庫及其相關物件。

  42. Dynamic Management View（DMV） Dynamic Management Function（DMF） • 元件層級（Component Level) • sys.dm_repl_* • 記錄資料庫複寫。 • sys.dm_broker_* • 記錄Service Broker相關資訊。 • sys.dm_fts_* • 記錄全文檢索相關資訊。 • sys.dm_qn_* • 記錄Query Notifications相關資訊。 • sys.dm_clr_* • 記錄CLR程式碼執行相關資訊。

  43. sys.dm_exec_requests • 可檢查伺服器上各Session執行狀態 Select scheduler_id, session_id, status, command, wait_type, wait_resource, percent_complete, cpu_time from sys.dm_exec_requests where session_id > 50

  44. Runnable Queue

  45. CPU Pressure（處理器瓶頸） • Runnable Queue -- Status 欄位顯示為runnable，表示這些session正在等待執行 • 這一類的等待表示一切資源均已完備，只等待CPU 處理，稱之為 Signal Waits。 • 計算Signal Waits的時間，即可知道處理器是否產生瓶頸，此一項評量標準稱為CPU Pressure。

  46. Resources Waits • Waiter List（等待清單）-- Status 欄位顯示為suspended • 這一類的等待稱為Resources Waits，表示在等待資源（Resources），至於是哪種資源，可能是記憶體，或是資料被Block住，或是Disk I/O，…等原因，可參考wait_type（等待種類）欄位 • wait_type -- 在SQL Server 2000 約有76種，SQL Server 2005 約有192種

More Related