MVA學習筆記：SQL Server管理入門(二)
MVA連結
SQL交易記錄概觀：
交易流程：
- 應用程式輸入資料變更=>在buffer cache找到或載入資料頁修改資料=>變更被記錄在交易記錄(ldf)=>checkpoint將dirty pages更新到資料檔
復原模式應用：
- 針對一些bulk操作可因最小記錄而減少交易記錄使用的空間
備份策略：
- 可容納的資料損失?
- 使用何種備份類型?
- 誰來執行?
- 誰來規劃?
- 頻率為何?
- 多久做一次還原測試?
- 是否搭配協助廠商工具?
- 使用何種備份媒體?
SQL SERVER備份類型：
- 備份交易記錄檔作用中(active)的部份，以回復資料庫
- SQL2008之後提供了備份壓縮功能，不過針對沒有壓縮率的資料就不需要特別使用
- 備份交易記錄檔作用中(active)的部份，以回復資料庫
- 部份-primary檔案群組，每個讀/寫檔案群組，以及特定的唯讀檔案群組
- 備份交易記錄結尾-在還原之前備份交易記錄的結尾記錄
- 如果mdf、ndf都已損毀，但ldf沒有損壞，可以利用此備份，在做還原之後再將所有交易記錄還原。
- 在循序還原之前用來擷取交易記錄的結尾，等同執行一次一般的記錄備份。
- 當接下來就要開始還原，可使用norecovery(將資料庫狀態改為recovering)
- 當資料檔遺失或損毀，但交易記錄檔仍完整，可使用continue_after_error
- 在兩個資料庫之後搬移資料從A拿去B，又不想破壞原來的備份還原順序使用
確認備份完整性的選項：
- 鏡像備份裝置
- 備份集最多可以四份(企業版支援)
- 總和檢查碼(checksum)備份選項
- 驗證備份(restore verifyonly)，搭配checksum選項較為有用
檢視備份資料：
備份考量：
- 備份是線上同時進行，不限制使用者存取，可能因為I/O負係低其它作業效能
- 一般備份時使用CPU約10%以內的資源，但有啟用壓縮時會拉高使用率
- 一般備份作業時，資料庫需處理上線狀態
- 當資料庫損毀時仍可以執行交易記錄備份
- 交易記錄檔必需是完整的
還原類型：
- 簡單模式
- 完整模式
- 還原系統資料庫
- 僅還原受損檔案
- 進階的還原選項，包含online、piecemeal、page還原
準備還原備份：
- 可能需要執行結尾記錄備份(只適用大量記錄和完整)
- 確認要還原的備份內容
還原流程的階段：
- SQLSERVER資料庫的回復流程分三階段
- 分析與資料複製：建立檔案並複製資料到檔案內，分REDO和UNDO
- REDO：從還原的記錄將交易結果更新到資料檔
- UNDO：在還原的當下，將未完成的交易回復
- REDO和UNDO稱為RECOVERY
WITH RECOVERY選項：
- 資料庫必須要完成還原才能上線使用
- WITH NORECOVERY還原選項保持資料庫正在還原狀態，允許對資料庫執行額外的還原作業。
WITH STANDBY選項：
- 可以唯讀的方式查詢未還原的資料庫，透過待命資料庫檔案存放UNDO階段的細節資料。
- 建立待命伺服器提供資料的唯讀查詢
- 在多個交易記錄回復之間查閱資料庫
- 在人為災難時非常好用
回復到特定時間點：
- 讓資料庫可以回到特定的時間點，相關記錄都存在交易記錄備份中
- 定義特定時間點的方式，透過交易名稱標記
- 資料庫最好是完整復原
- 交易記錄若包含大量記錄，回復的特定時間點剛好落在大量記錄復原模式的最小記錄行為區段，將導致回復失敗。
- restore log xx from disk='xx' with standby='xx' ,stopat='時間點'
資料的移轉：
- ETL：EXTRACT TRANSFORM LOAD
- 在伺服器間複製或搬移資料
- 將查詢的資料輸出成檔案
- 從檔案將資料載入資料表
- 從檢視讀取或輸入資料
- 轉換定序或BIG5轉UNICODE
資料轉換可用的工具：
- BULK COPY PROGRAM(BCP)
- BULK INSERT
- OPENROWSET(BULK)
- IMPORT/EXPORT WIZARD(SSIS)
- XML BULK LOAD
強化資料轉換效能：
- 停用條件約束、索引和觸發
- 減少鎖定，考慮使用TABLOCK以加速載入
- 在目的端透過SELECT INTO會比從來源端做來的有效率
- 減少交易記錄，採BULK MODE或是SIMPLE MODE
- 減少資料轉換，採用NATIVE格式
- 來源跟目的皆為SQLSERVER的話採NATIVE模式效率好
- LOCK量會從ROWLOCK到PAGELOCK再到TABLOCK
- SQL SERVER INTEGRATION SERVICES 概觀(SSIS)：
- 提供開發ETL解決方案豐富的架構
SSIS封裝包含：
- 資料來源和目的
- 控制和資料流程
- 各種不同的轉換
- 可執行SSIS封裝的方式
- DTEXECUI和DETEXC命令列工具
- SQLSERVER AGENT作業
- 開發SSIS封裝的方式
BCP：
- 直接在CMD處執行命令
- 中介的通常是一個檔案
- 產生格式檔
- bcp Northwind.dbo.customers format nul -T -c -x -f Cust.xml
- bcp Northwind.dbo.customers out "e:\temp\Cust.dat" -T -c
- bcp tempdb.dbo.customers in Cust.dat -T -f Cust.xml
BULK INSERT語法：
- 提供與BCP近似的選項
- 執行在SQLSERVER應用程式內
- 有CHECK_CONSTRAINTS和FIRE_TRIGGERS選項
沒有留言:
張貼留言