YourSQLDba的翻譯(七)

YourSQLDba的翻譯(七)

YourSQLDba是可定製化的，不過在定製之前，你需要知道YourSQLDba預設做了什麼
YourSQLDba 自動部分包括兩個任務 在SQLSERVER代理裡
（1）YourSQLDba_FullBackups_And_Maintenance
（2）YourSQLDba_LogBackups. 

每個任務包含單獨的步驟，每個步驟都會調用YourSQLDba_DoMaint預存程序，不過只是調用時候的參數不一樣已而
YourSQLDba_LogBackups任務可以快點解釋完，它負責記錄備份
他們執行不間斷的記錄備份大概每15分鐘，這個是由SQLSERVER代理來調度的。
一些人會問：他怎麼避免在啟動並執行時候資料庫遇到高並發的時候備份交易記錄出問題。
YourSQLDba使用了一個已經SQL2000介紹過的特性來避免這種情況。這個特性叫應用程式鎖（預存程序 sp_getapplock）
如果資料庫擷取不到應用程式鎖，他就會放棄當前的記錄備份。YourSQLDba對給定資料庫定義了日誌和維護。
但是完整維護任務要等至少5分鐘在放棄等待一個鎖之前。我們建議5分鐘是一個合適等待記錄備份，當記錄備份在15分鐘內做完這樣不會有太多時間令備份變大

在記錄備份的時候，當記錄備份完畢後日誌是空的，記錄檔增長的那部分會被收縮。這個大小是這樣被計算的:
主要資料檔案+預設檔案組的總和大小+預設檔案組的額外檔案的1/10。如果日誌大小比計算公式得出的要大，我們會假定
收縮檔案時間已經到來。為什麽不計算1/10檔案在預設檔案組裡？通常，特別的檔案組被用來儲存BLOB或者曆史資料
或者做表分區、索引分割區之用。我們有一個大的列記錄了內容的改變。日誌的比率大小跟這些需要縮小的大檔案有關係，跟主要資料檔案和預設檔案組也有關係

YourSQLDba_FullBackups_And_Maintenance任務需要更多的詳細解釋他所執行的任務

 

執行日誌清理
SQLSERVER的errorlog是迴圈的，並且會有新日誌產生
像SQL 代理備份曆史的日誌會被限制在30日裡，超過30日就清理
YourSQLDba的日誌曆史被限制在30日裡
郵件日誌被限制在30日裡
代理作業曆史被限制在30日裡

預設YourSQLDba_DoMaint預存程序會被調用，並且根據不同的參數做下面介紹的不同的任務
檢查資料庫完整性。如果資料庫被發現任何問題，YourSQLDba會把資料庫設定為緊急狀態
更新分發統計。更新時候會用全表掃描，每個星期更新一次統計資訊
重組或重建索引根據他們片段層級和根據預存程序裡定義的臨界值
執行完整Database Backup
完整Database Backup檔案超過一日的存放時間就會從備份檔案夾裡刪除，這個可以通過參數進行配置
記錄備份檔案超過七日的存放時間就會從備份檔案夾裡刪除，這個可以通過參數進行配置
一個完整Database Backup檔案在建立的時候會被命名為dbname_[yyyy-mm-dd_hhhmimsss_dw].bak
dbname是資料庫名，[yyyy-mm-dd_hhhmimsss_dw]是一個時間
記錄備份檔案在建立的時候會被命名為同完整備份檔案一樣的命名格式，只是檔案尾碼名是.trn
每個備份迴圈，不管是完整備份還是記錄備份，都會記錄在MSDB資料庫。MSDB資料庫會跟蹤所有
已完成的備份並使還原更加容易
備份檔案名和位置存放在YourSQLDba的一張表裡，這張表允許YourSQLDba重用備份記錄檔對於特定資料庫
繼續追加記錄備份。這極大減少了備份過程檔案的產生，只要一直使用同樣的記錄備份檔案

 

Default Maintenance
  

Updated : 2009-02-25

YourSQLDba is customizable, but before to do it, may be you should consider what it already does by default.

YourSQLDba automated part is expressed as two tasks in the SQL Server Agent:

YourSQLDba_FullBackups_And_Maintenance
YourSQLDba_LogBackups. 
Each task contains a single step which call YourSQLDba_DoMaint, but with different parameters.

YourSQLDba_LogBackups is quicker to explain, as it deal exclusively with log backups. 

They are performed continuouly all around the clock, at every 15 minutes (thanls to SQL Server Agent Scheduler).  Some may wonder how it avoid problems like running into a concurrent attempt to do log backup at the same time there is an ongoing database backup on the same database.  YourSQLDba use a feature introduced in SQL2000 to avoid, this.  This is called application locks (See sp_getapplock).  A log backup for a given database give up immediately, if it can't obtain the application lock. YourSQLDba defines for both log and complete maintenance for a given database.  However complete maintenance wait at least 5 minutes before giving up waiting for a lock.  We suppose that 5 minutes is a reasonable time to wait for a log backup, since log backup done at every 15 minutes doesn't have too much time to become too large, and hence long to backup.
At log backup time, after the log is emptied by the log backup, log file that have grown beyond a certain size are shrunk.  This target size is calculated this way (sum of size of primary data file + sum of size of files in default file group + 1/10 sum of the size of files outside default file group).  If log size that is greater than 40% of this computation, we assumed that shrunk time has come.   Why compute on 1/10 of the file outside of default file group ?  Usually special filegroup are used to stored blob, or historical data, or to do table and index partionning.  We have a big volume in these filegroups for which only a fraction of the content is usually changed. Log ratio size relatively to these big file need to be smaller, contrary to primary data file of default filegroup.
YourSQLDba_FullBackups_And_Maintenance needs more explanations as it performs more tasks.

Log cleanup are performed
Sql Server current is recycled (it means archived) and a new log is generated. 
Logs like SQL Agent backup history are limited to 30 days. 
YourSQLDba log history is limited to 30 days.
Mail items log is limited to 30 days
Agent job history is limited to 30 days.
 
By default the procedure YourSQLDba_DoMaint is called with the option to do the following tasks.
 
Check database integrity.  If a database is found with any integrity problem, it is put immediately in emergency mode
Update distribution statistics.  Updates are done using full scan, but process is spread by default (parameter) on seven days.
Reorganize or Rebuild index depending on their real level of fragmentation, base on a threshold specified internally into the procedure
Perform full database backup
Full database backup files older that one day (parameter) are suppressed from disk backup directory
Log backup files older that seven day (parameter) are suppressed from disk backup directory
A full backup file of the database is created and named this way dbname_[yyyy-mm-dd_hhhmimsss_dw].bak where dbname is the name of the database, yyyy-mm-dd HHhMImSSs_dw a timestamp plus the day name of the week and .Bak extension is the regular extension used for full database backup.
A log backup file is created and named about the same as a full backup file except the extension which is .Trn for transaction log backups.
Every backup cycle, whether it is full backup or transaction log backup cycle, is followed by a complete backup of MSDB.  Msdb keeps tracks of all backups done and makes it easier to do other restores once it is restored since all the backup history become accurate and up-to-date.
Backup file name and locations are retained into a table into YourSQLDba.  It allows YourSQLDba to reuse the backup log file for a given database, to continue to append other log backups to the same file.  It greatly reduce the number of files produced by the backup procedure, by using the same log backup file for all the day.