• 發行項

SQL 問答集I/O 錯誤、資料庫鏡像及其他主題

Paul S. Randal

問：我在自己管理的資料庫上開始定期執行一致性檢查，甚至還加入一些 SQL 代理程式警示來擷取使用者查詢所遇到的 I/O 錯誤。我不知道我根據檢查和警示實作的邏輯是否可行，因為我所有的資料庫都沒有損毀問題。我該如何在測試資料庫中製造一些損毀，以便確認我設定的一切可以正確運作呢？另外，如果要偵測 I/O 錯誤的話，還需要進行其他什麼動作嗎？

答：在 SQL Server® 2000 中有個建立損毀資料庫以供測試的老把戲，那就是從測試資料庫的 sysindexes 資料表中，手動刪除一個資料列。但是在 SQL Server 2005 中，很難以這種方法損毀系統資料表。自己動手損毀測試資料庫的最佳辦法，是在資料庫關閉的時候，使用十六進位編輯器更改資料檔。方法如下：

  • 關閉資料庫，這樣資料檔就不會處於鎖定狀態了 (不過要小心別中斷資料庫連線，因為如果誤毀了不該損毀的頁面，可能無法重新連接它)。
  • 在檔案中挑選超過 100 頁的位移 (至少 819200 個位元組)，不過它必須符合 8192 位元組的限度 (頁面限度) 才行。這是為了避開重要的中繼資料頁面和配置點陣圖，進而讓您啟動資料庫，並在上面執行 DBCC CHECKDB 的緣故。
  • 在檔案中選定的位移，寫入幾個位元組的零。這種方法幾乎可以保證產生一些頁面標頭損毀錯誤。

儘管如此，建立損毀測試資料庫最快的方法，還是使用別人已經建立好的。您可以在我的部落格上找到損毀的 SQL Server 2000 和 SQL Server 2005 資料庫 (內含說明)，網址是 go.microsoft.com/fwlink/?LinkId=115151。

至於您的第二個問題 (也就是該怎麼做才能偵測到 I/O 錯誤)，答案是啟用頁面總和檢查碼。這項功能是在 SQL Server 2005 引進的，目的在保護整個資料庫頁面免受 I/O 子系統引起的錯誤所干擾。

基本上，當您將頁面寫入磁碟時，SQL Server 最後會計算整個 8KB 頁面的總和檢查碼，然後在頁面加上總和檢查碼的戳記。當您從磁碟讀取頁面時，如果它有頁面總和檢查碼，就會重新計算該總和檢查碼，並與頁面上儲存的版本相比較。如果兩者不一樣，就表示是 SQL Server 外面的東西損毀了頁面，而引起 824 錯誤。錯誤會顯示在導致讀取該頁面的連線，並且記錄到 SQL Server 錯誤記錄檔和 Windows® 應用程式事件記錄中。

在 SQL Server 2005 和 SQL Server 2008 上建立的所有資料庫，預設都會開啟頁面總和檢查碼。但是，如果是從舊版 SQL Server 升級的資料庫，就必須手動啟用。您可以使用這段程式碼來啟用頁面總和檢查碼：

ALTER DATABASE mydb SET PAGE_VERIFY CHECKSUM;

提示：變更預設 SQL Server 連接埠

根據預設，SQL Server 執行個體的設定連接埠是 1433。此連接埠一旦被執行個體佔用後，就不能供其他執行個體使用了。因此，如果您要安裝第二個使用 TCP 在網路上接聽的 (具名) 執行個體，就需要另一個連接埠了。而且在某些情況下，為了避免散亂，系統管理員可能會變更連接埠 (不過這種散亂並不重要，而且可以很容易的使用連接埠掃描器加以解決)。所以嘍，接下來就必須設定用戶端，讓它使用不同的連接埠。常用的方法有三種。

首先，假設系統管理員把執行個體的連接埠改為 5555，其中一個方法是在您想要連接的機器名稱內，把執行個體的連接埠號碼指定為使用 MyServername,5555 語法就行了。如果連接埠再度變更，用戶端也必須再次變更它們的 connectionStrings。

另外一個方法是使用 SQL Server 別名，這是在用戶端上設定的。除了指定別名之外，還必須指定伺服器名稱、連接埠名稱和通訊協定。設定好之後，可像使用伺服器名稱一樣，使用別名連接到資料庫執行個體。這個方法的優點是，伺服器設定變更可由網域系統管理員來部署，因為設定是存放在登錄中。

具名執行個體的第三個方法，是使用 SQL Server Browser 服務，這種情況下的使用者只知道執行個體名稱，並且在 connectionStrings 內使用 MachineName\Instance\Name 指定名稱。這在 SQL Server 2000 中已經視為執行服務的一部分實作了。但是在 SQL Server 2005 中，SQL Server Browser 服務是建置成個別的服務。除了為機器進行執行個體探索之外，它還會在連接埠 1434 上，以適合所要求之執行個體的連接埠號碼，應答連入的使用者資料包通訊協定 (UDP) 要求，藉此重新導向用戶端，以及支援透明連線。

─ Microsoft 的資料庫顧問 Jens K. Suessmeyer

問：為了移除我資料庫中所有的片段，我設定了夜間維護計畫，重建實際執行資料庫中所有的索引，而這項作業是在 SQL Server 2005 Enterprise Edition 含 SP2 上執行。我注意到這會使資料庫過度成長，而磁碟上的空間又不多，所以我多加一個步驟來壓縮所有額外的空間。可是重建步驟好像不能正常運作。到底是怎麼回事呢？

答：您碰到的是一般人在設定維護計畫時常見的問題。您被困在壓縮-成長-壓縮-成長的循環中了。

一般重建索引時，會在刪除現有索引之前，先為索引建立一份新複本。而資料庫檔案必須有額外的空間 (通常跟目前索引使用的空間差不多) 來進行這個程序。SQL Server 2000 也必須有額外的空間來排列索引資料列 (大約是索引大小的 20%)，但是 SQL Server 2005 卻為了重建一個簡單的索引，而排除這項需求。

系統管理員有時候會移除在重建索引時所建立的額外空間，因此他們在執行重建步驟之後，在維護計畫加了壓縮作業。但是很多人並不知道，這項壓縮作業會因為其演算法的關係而導致索引分散。也就是說，剛剛重建和重組的索引會馬上散成片段 — 這就與重建它的原意背道而馳了。

由於索引下次重建的時候，資料庫檔案會再度成長，因此最好在資料庫保留額外的空間，並且盡量避免執行壓縮作業 (此外，持續擴大和壓縮資料庫檔案，也會在作業系統層級導致檔案分散 — 這與索引分散一樣會降低效能)。

最後一點，就是考慮降低重建索引的頻率。您甚至可以試試其他方法，例如我為 SQL Server 2000 撰寫的舊版 DBCC INDEXDEFRAG，或是 SQL Server 2005 和 SQL Server 200 中較新的 ALTER INDEX REORGANIZE 語法。

您不妨參考一本有用的白皮書，它除了討論索引分散之外，還告訴您何時該移除分散情形 (網址是 go.microsoft.com/fwlink/?LinkId=115154)。雖然這本白皮書是針對 SQL Server 2000 所寫，但概念是一樣的。

問：我們公司一直在評估嚴重損毀修復策略，而我覺得資料庫鏡像非常適合我們。我要保護的伺服器裡面含有許多不相關的資料庫 (這是之前的伺服器整併專案所造成)，因此我想針對它們全部使用資料庫鏡像。但問題是，我能夠在不折損效能的情況下鏡像多少資料庫？

答：這個問題的答案我常常用到：視情況而定！根據出版的指導方針指出，每一個執行個體鏡像的資料庫最好不要超過 10 個，但是 10 個只是針對大多數使用者所估計的最大值。您必須針對您的硬體設定考慮下列因素：

  • 主體和鏡像執行個體有多少記憶體？(在理想情況下，兩者應該是一樣的)。
  • 主體和鏡像執行個體的處理能力有多強？(應該也是一樣的)。
  • I/O 子系統有多少頻寬可以用在鏡像執行個體？(應該跟主體上的一樣)。
  • 每一個資料庫的工作負載會產生多少交易記錄檔？
  • 主體和鏡像執行個體之間有多少網路頻寬可用？

最後兩個因素最為重要。如果兩個執行個體之間可用的網路頻寬，不足以處理所有要鏡像的資料庫每秒產生的綜合交易記錄檔，就會折損主體資料庫的效能。SQL Server 2008 有助於減輕記錄檔資料流壓縮所產生的一些負荷。

接下來最需要考慮的，是鏡像的記憶體和執行緒需求。每一個鏡像的資料庫都會使用一個執行緒和一些記憶體。低處理能力伺服器在與正常工作負載結合時，如果有大量鏡像的資料庫，可能會對伺服器造成過多負載。

另外，還必須考量執行資料庫鏡像的方式。在同步模式中，主體資料庫上的交易，必須等到所有交易記錄檔記錄全部複製到鏡像資料庫的交易記錄檔之後，才能進行認可。因此，由於網路超載所引起的任何延遲，都可能降低主體上的工作負載效能。

在非同步模式中，交易不用等候就可以在主體上進行認可，但是網路延遲可能會導致等候傳送給鏡像的交易記錄量變大。這可能會導致交易記錄檔大小的問題。更糟的是，一旦事件失敗，就會遺失未傳送的交易記錄檔 — 因此未傳送的交易記錄檔越多，就越可能在修復情況下遺失資料。

每一種案例的差異甚大，而且我在真正的實際執行環境中看過一些有趣的例子。比方說，我就看過一個含有 150 個資料庫的環境，每一個資料庫的活動量都很低，而且不見得都在同時間發生。在這種情況下，即使 150 個資料庫全部鏡像都沒有問題。

相反地，我也看過一個只含三個負載很重的資料庫，卻沒有良好網路連線的設定。在這種情況下，可能連一個資料庫都無法鏡像，因為網路頻寬不足會降低工作負載。

成功的關鍵在於，先計算產生的記錄檔。如果可用的網路頻寬看來足以支援您要鏡像的資料庫數量，應該就不成問題。請在實際執行設定之前先行測試，並且務必包含所有可能產生交易記錄檔的作業 — 尤其是您可能進行的任何資料庫維護作業。

Paul S. Randal 是 SQLskills.com 的常務董事，同時也是 SQL Server MVP。他在 1999 年 2007 年間服務於 Microsoft 的 SQL Server 儲存引擎 (SQL Server Storage Engine) 小組。Paul 曾為 SQL Server 2005 撰寫過 DBCC CHECKDB/repair，並且在 SQL Server 2008 開發期間負責核心儲存引擎。身為嚴重損壞修復、高可用性和資料庫維護方面的專家，Paul 也經常在研討會上發表演說。他的部落格位於 SQLskills.com/blogs/paul。

© 2008 Microsoft Corporation 和 CMP Media, LLC.保留所有權利；未經允許，嚴禁部分或全部複製.