如何使用快照集隔離來降低鎖定問題的發生

        快照集隔離是SQL Server 2005所引進的一個新功能，快照集隔離一旦啟用後，即會在 tempdb 中維護每筆交易所更新的資料列版本。透過唯一交易序號識別每筆交易，並會針對每個資料列版本記錄這些唯一的號碼。交易使用其序號在該交易序號之前的最新資料列版本。該交易會忽略在交易開始之後建立的較新資料列版本。

PS：由於快照集隔離主要透過 tempdb 來進行交易的快照與維護，所以建議你一定要進行tempdb的最佳化，建議你可以參考我的另一篇文章 [SQL Server 儲存設備 (Storage) 最佳調整作業]。

        「快照集」這個詞彙反映了根據交易開始時資料庫的狀態，交易中所有查詢都看到資料庫的同一版本 (或快照集) 這一事實。在快照集交易中的基礎資料列或資料頁面上沒有鎖定，這允許其他交易執行，而不會被之前未完成的交易封鎖。修改資料的交易不會封鎖讀取資料的交易，而讀取資料的交易也不會封鎖寫入資料的交易，因為它們通常處於 SQL Server 中預設的 READ COMMITTED 隔離等級中。這種無封鎖的行為也會大幅降低複雜交易發生死結的可能性。

        再來我們來介紹一下 SQL Server Database Engine 的 交易隔離層級，為了保證資料庫中的每一個交易的完整性，所以所有的交易一定必須遵守 (ACID)的原則，也就是 (Atomicity, Consistency, Isolation, and Durability) 四個項目，其中重要的就是隔離屬性 (Isolaton Property)是大家一定要認識的。它的能力是保護交易免於受到其他並行交易執行更新的影響。隔離等級 (Isolation Level) 事實上可針對每一筆交易來自訂。

        SQL Server Database Engine支援以 SQL-92 定義的交易隔離層級。設定交易隔離層級可讓程式設計人員，針對某些增加完整性問題的風險以及支援更大的資料並行存取之間，進行截長補短的取捨。交易隔離等級為：

隔離等級	中途讀取 (Dirty read)	非可重複讀取	幽靈 (Phantom)
READ UNCOMMITTED	是	是	是
READ COMMITTED	否	是	是
REPEATABLE READ	否	否	是
SNAPSHOT	否	否	否
SERIALIZABLE	否	否	否

PS：READ COMMITTED為SQL Server Database Engine的預設交易隔離層級

        由於SQL Server Database Engine的預設交易隔離層級為READ COMMITTED，但由於有些應用用程式或語法的設計問題，所以造成系統中有許多Blocking或Deadlock的發生，所以有些人就會透過 with nolock 的Table Hint來進行降低隔離層級至 (READ UNCOMMITTED)，但是在使用上並沒有完全的了解此語法的使用方式，導致造成 Dirty Read的發生，所以還是要看你的系統的應用，不能盲目的使用。

接下來我們來進行一個實驗，確認一下快照集隔離的威力。

1、我們先透過下列的語法確認目前已有那些資料庫已啟用 [快照集隔離] 的功能。
select database_id, name,snapshot_isolation_state, is_read_committed_snapshot_on
from sys.databases

2、確認在 [cary_test] 的資料庫下的 test資料表中有三筆資料。
SELECT [sn] ,[name]
  FROM [cary_test].[dbo].[test]


3、透過下列的指令進行資料列的更新，但是為了模擬鎖定的狀況，所以我在語法中有設定 Begin Transaction，但是卻沒有進行Commit或Rollback的指令。
Begin Tran
update test
set name = 'jim'
where sn = 2;


4、當下你在透過另一個查詢視窗進行 Select 時，由於需要取得資料列，所以會一直查不出來，但是如果你跳過 sn = 2 的那一筆時，查詢就會正確。

查詢不出結果
SELECT [sn] ,[name]
  FROM [cary_test].[dbo].[test]

查詢正確
SELECT [sn] ,[name]
  FROM [cary_test].[dbo].[test]
 WHERE sn <> 2


5、現在我們就來啟用快照集隔離的功能，請透過下列的語法進行。
ALTER DATABASE cary_test
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE cary_test
SET READ_COMMITTED_SNAPSHOT ON

select database_id, name,snapshot_isolation_state, is_read_committed_snapshot_on
from sys.databases


6、接下來再將原本1~4的步驟再次模擬測試，你會發現原本在第4個步驟中查詢不出來的情況，已可正常的查詢。

相信透過上面的解說，你應已了解到快照集隔離可以有效的降低 Blocking 與 Deadlock 的發生，但是再次提醒由於啟用後會大量的使用到 tempdb，所以請別忘了進行 tempdb 的效能最佳化，以免解決了 Blocking，但是造成了另一個 I/O 上的瓶頸就划不來了。



參考進結：
Locking Hints
http://msdn.microsoft.com/en-us/library/aa213026(v=sql.80).aspx
資料表提示 (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms187373.aspx
sys.databases (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms178534.aspx
Database Engine 中的隔離等級
http://msdn.microsoft.com/zh-tw/library/ms189122.aspx
調整交易隔離等級
http://msdn.microsoft.com/zh-tw/library/ms189542.aspx
啟用資料列版本控制式的隔離等級
http://msdn.microsoft.com/zh-tw/library/ms175095.aspx
使用快照集隔離
http://msdn.microsoft.com/zh-tw/library/tcbchxcb(v=vs.80).aspx
SQL Server 儲存設備 (Storage) 最佳調整作業
http://caryhsu.blogspot.com/2011/02/sql-server-io.html


關鍵字：SQL Server、ALLOW_SNAPSHOT_ISOLATION、READ_COMMITTED_SNAPSHOT、SNAPSHOT、快照集隔離、tempdb、Blocking、snapshot isolation level

災難救援演練 - 如何修復一個 Kill Session 後，發生問題的資料庫

        最近遇到大家常遇到的一個問題，就是當資料表在進行大量的更新 (Update) 或刪除 (Delete)時，由於執行過久，所以強制透過 Kill 的語法進行刪除，但是有時候由於語法可能只執行到一半，所以當被強制的 Kill 時，SQL Server就會很聰明的自動進行 Rollback 的動作，但是有時候就會非常的久，大部份的使用者如果耐不住性子時，就會重啟系統並強制關閉，這樣的流程其實非常的危險，所以我在正確的方法與流程整理如下，以免大家因為操作不慎造成資料的損失。

問題重現：

1、先將範例的資料庫 [AdventureWorks2008R2] 裝好，裝法與設定方式請參考我之前的文章 (安裝SQL Server 中的範例資料庫)。

2、由於為了快速將問題重現，所以我特別將表格 [SalesOrderDetail] 的資料重覆填入到另一個新的資料表中，讓這個資料表的筆數達到約1千二百萬筆左右。

3、執行更新語法進行資料的變更。
UPDATE [AdventureWorks2008R2].[Sales].[SalesOrderDetail2]
set UnitPrice = UnitPrice + 0.1
where UnitPrice < 100

4、由於執行約需要3分鐘左右，所以我們先透過下列的 DMV 語法查出目前所有執行的 Session。
select * from sys.dm_exec_sessions
where session_id > 50;


PS：因為 session_id 小於50都是屬於SQL Server的內部執行，所以可以略過。

5、然後找出目前的 session_id 然後再透過下列的指令查出此 session 的內容。
dbcc inputbuffer(59);


6、確定是我們正在執行更新語法的執行緒後，我們模擬使用者的情況，因為等不及了，所以透過 Kill 的指令將 session 強制砍掉。
kill 59


7、執行完成後，你會發現系統正在進行 Rollback，此時我們馬上進行關機，因為要模擬使用者等不及的狀態。

8、此時你就會發現資料庫會進入到 復原中 (In Recovery) 的狀態中。

經過上述的還原步驟，主要是模擬使用者的情況，接下來我們要介紹遇到此狀況時，該如何進行還原。

正確修復步驟：

1、當你執行完 Kill 的指令後，請先透過下列的指令切換到單人模式，加快還原的速度，如果執行後30分鐘內仍未完成，再請進行下一個步驟。
--切換成單人模式
alter database <database_name> set single_user with rollback_immediate
go
--切換回多人模式
alter database <database_name> set multi_user
go
2、重新啟動SQL Server Service。

3、如果重啟後，資料表仍然無法存取時，再請進行下列的步驟。

• 停止 SQL Server Service
• 將資料庫的mdf與ldf檔，搬移到暫時目錄，例如C:\Temp
• 然後啟動SQL Server服務
• 使用SQL Server Management Studio將此資料庫刪除
• 使用您的備份檔還原資料庫

4、完成上述的動作後，建議再進行下列的指令進行資料庫完整性的檢查。

•   開啟 SQL Server Management Studio
•   輸入DBCC CHECKDB(‘database name’);

基本上將 SQL Server的服務重啟後，問題就會解決，但建議最好執行 DBCC CHECKDB的指令檢查資料庫的完整性，藉以確保資料庫日後的運行。


參考連結：
DBCC CHECKDB (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms176064.aspx
ALTER DATABASE (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms174269.aspx

KILL (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms173730.aspx
DBCC INPUTBUFFER (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/ms187730.aspx
sys.dm_exec_sessions (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/ms176013.aspx

關鍵字：SQL Server、Kill Session、DBCC CHECKDB、rollback_immediate

SQL Server - 雙主動模式叢集環境架設

        許多人抱怨說使架設SQL Server的Cluster有點可惜，因為第二台電腦(Passive)的電腦只能 Standby ，當沒有問題發生時，根本一點用途也沒有，所以許多公司通常在購買第二台電腦的時候只會用很少的預算進行採購，所以當問題發生時，只能短暫的替代第一台主機 (Active)的角色，很快的就會撐不住，而第一台又來不及復原進行上線服務時，整個服務只能停擺了。

        針對上述的問題，所以我在這介紹如何將原本的 AP (Active/Passive) Mode Cluster，變成 AA (Active/Active) Mode Cluster，方法上非常的簡單，只需多新增一顆Share Disk與再另外安裝一個 Instance即可，詳細的步驟如下，再請參考下列的步驟。

圖表說明：在我的SAN Storage中總共會先切出六顆磁碟，Quotum與MSDTC的磁碟會共用，而SysDB-1與UserDB-1會給第一個Instance，而另外二顆則給第二個Instance使用。

1、請先將原本的 AP (Active/Passive) Mode Cluster安裝完成，詳細步驟請參考我之前的作品。

相關文章：

• SQL Server 2008 R2 容錯移轉叢集環境架設 - 利用 VM 與 Windows Storage Server - Part I
• SQL Server 2008 R2 容錯移轉叢集環境架設 - 利用 VM 與 Windows Storage Server - Part II
• SQL Server 2008 R2 容錯移轉叢集環境架設 - 利用 VM 與 Windows Storage Server - Part III(終)
• 容錯移轉叢集中多個節點切換順序的設定

2、請再新增二個叢集磁碟，分別存放 System Database與 User Database。
3、先在第一個節點進行SQL Server安裝，方法與第一個 Instance 的方法相同。
4、安裝到 [執行個體組態] 的時候，請輸入不同的 [SQL Server 網路名稱] ，在這我以 [CLUSTER-AA-2] 為例，Instance Name則輸入 [AAMODE]。

5、安裝到 [叢集資源群組] 的時候，你會看到資源的偵測，此時候請將 SQL Server 安裝到另外一顆新的叢集磁碟上。

6、此處由於是另外一個Instance，所以請使用不同 IP 位址，然後安裝到最後即可完成。

7、安裝完成後，即可看到 [服務與應用程式] 已可以看到另外一個應用程式的名稱。

8、再來請進行第二台主機的安裝，安裝時請選擇 [新的SQL Server容錯移轉叢安裝]，啟動安裝後，你就可以看到系統會列出目前所擁有執行個體名稱，並自動選擇合適的執行個體名稱，再一直安裝到最後即可完成。

9、安裝完成後，再到 [服務與應用程式]，將 SQL Server (AAMODE) 切換到第二台上。

10、切換完成後，你就可以看到 SQL Server (AAMODE) 的目前擁有者即切換到第二台上，如此即大功告成。

12、測試完成後，我們再到 SQL Server (AAMODE) 點選 [內容]，將慣用擁用者的順序調整。

13、最後由於我們是設定成 AA-Mode的模式，所以請記得在最壞的情況下，你的SQL Server可能會同時放在同一台上，所以在資源的分配上，也請盡量分成好，比較常見的是 (資源數/Instance) 的數量，但往往有時候不一定是相對的，所以請依照你自已的環境進行調整。

參考文件：
Failover Cluster Step-by-Step Guide - Configuring a Two-Node File Server Failover Cluster
http://www.microsoft.com/download/en/confirmation.aspx?id=11247

關鍵字：Cluster、AA-Mode、AP-Mode、Active/Passive、Active/Active、High Available、Cluster、Failover、容錯移轉叢集

SQL Server - 如何建立 Database Mirroring

        在高可用性 (High Availability) 的架構上，Failover Clustering一直以來是大家的首選，但是在SQL Server 2005 SP1之後推出了  [資料庫鏡像 (Database Mirroring) ]，也讓大家有更多的選擇，而其中與Failover Clustering有下列幾個差異，我在此列出。

Failover Clustering vs Database Mirrioring

1. Failover Clustering 架構於作業系統上的容錯移轉。
2. Failover Clustering 需要一組 Shared Disk進行。
3. SQL Server Failover Clustering 是以一整個 Instance 為主，而 Database Mirrioring是以單一的Database為主進行控管。
4. SQL Server Failover Clustering主要為AP (Active/Passive) Mode，所以另一台主機只能待命無法使用(如果您需要將Failover變成 A/A Mode的話，可以參考 [SQL Server - 雙主動模式叢集環境架設])，而Database Mirroring只有設定鏡像的資料庫不能讀取，但是可以透過資料庫快照來讓 Mirro 端的資料庫可以進行唯讀的動作。
5. 如果 Database Mirrioring 要作到自動容錯移轉時，需要第三台主機進行監控，但只需 Windows XP 或 Windows 7的作業系統，而資料庫只需 Express 免費版即可。

PS：Failover Clustering對於硬體的支援較嚴格，所以在一開始的成本會較於明顯，而 Database Mirroring只要兩台單機的電腦即可輕易的進行架設，所以也較容易入門。

Database Mirroring 作業模式：
鏡像作業模式共有兩種，請參考下列說明。

第一種模式 (「High-Safety Mode」) 可支援同步作業。在高安全性模式下，當工作階段開始時，鏡像伺服器會儘快將鏡像資料庫與主體資料庫進行同步處理。一旦資料庫同步處理完成之後，交易將同時在兩個夥伴上進行認可，代價是會增加交易延遲性。 

第二種作業模式 (「High-Performance Mode」) 則以非同步方式執行。鏡像伺服器會盡量跟上主體伺服器所傳送的記錄。鏡像資料庫可能會稍微落後主體資料庫。然而，在資料庫之間的間距通常很小。但是，若主體伺服器的工作負載很大，或鏡像伺服器的系統超載時，此差距就會變大。

配備說明：
第一台
角色名稱：Principal
電腦名稱：WIN-2008R2-1
OS：Windows 2008R2 Enterprise
DB：Windows 2008R2 Enterprise
說明：主要資料庫，資料庫復原模式(Recovery Mode)需設定成完整 (Full)。

第二台
角色名稱：Mirror
電腦名稱：WIN-2008R2-2
OS：Windows 2008R2 Enterprise
DB：Windows 2008R2 Enterprise

說明：鏡射資料庫，接收從主要資料庫的交易資料，但還原的過程中無法使用，如果想要使用時，可以透過資料庫快照即可間接使用即可。

第三台
角色名稱：Witness
電腦名稱：WIN7-CARYHSU-PC
OS：Windows 7 Home
DB：Windows 2008R2 Express

說明：如果你的 Database Mirroring 想要擁有 Auto Failover 的功能時，就需要透過此台電腦的監控來進行，由於此台電腦只負責監控，所以可以只安裝 Express 的版本即可，而作業系統也需要一般的作業系統即可。

範例說明：
在下列的範例中，為了可以實現  Auto Failover ，所以我將以 [High-Safety Mode] 進行，資料庫將以 Northwind 為例。

建立 Database Mirroring
1、建立 Mirror端資料庫。

1-1 先將 Principal 端的 Northwind 資料庫最後的備份複製到 Mirror 電腦上，並進行還原，還原時，請記得選擇 [讓資料庫保持不運作，且不回復未認可的交易，可以還原其他交易記錄。(A)(RESTORE WHIT NORECOVERY)]，這個步驟也是最容易疏忽的一部份，所以再請特別注意。

1-2 還原完成後，你可以看到資料庫的狀態會變成 [正在還原...]。

2、設定資料庫鏡像


2-1 選擇要設定鏡像的資料庫，然後點選滑鼠右鍵選擇屬性。
2-2 點選 [鏡像] -> [設定安全性]。

2-3 點選下一步。

2-4 由於是進行 [High-Safety Mode]，所以請選擇 [是]，然後選擇 [下一步]。

2-5 依照預設值，選擇 [下一步]。

2-6 由於 Database Mirroring 預設是透過 5022 的通訊埠，如果你希望更換成不同的通訊埠時，請記得在三台主機的通訊埠設定成相同，此處依照預設值，點選 [下一步]。

2-7 選擇第二台的主機 ，如通訊埠沒有更動時，此處依照預設值，點選 [下一步]。

2-8 選擇第三台的主機 ，如通訊埠沒有更動時，此處依照預設值，點選 [下一步]。

2-9 由於三台主機都有加入 Domain，所以請記得選擇一個網域帳號即可。
PS：如果你的主機沒有加入 Domain時，你有兩種方法可以進行，一種是三台的帳號密碼都相同，藉以矇過系統，另一種是透過憑證的方式進行，詳細的作法再請參考 [Setting Up Database Mirroring Using Certificates] 。

2-10 設定完成後，即會秀出總結的資訊，再點選 [完成]。

2-11 精靈設定完成後，即時會提供你是否要立即啟動鏡像，可此時啟動，或是稍後再手動啟動即可。

2-12 設定完成後，你即會看到目前的 [作業模式] 會設定成 [具有自動容錯移轉的高安全性 (同步)]。

2-13 此時再切換到資料庫清單中，即可看到目前 Database Mirroring 的狀態。

3、系統測試與模擬 Failover

3-1 開啟資料庫屬性，點選 [容錯移轉]，讓兩台主機的身份互換。

3-2 點選後，你就會看到 Failover 已完成，主體與鏡像的身份也已進行切換了。

參考連結：
Database Mirroring Overview
http://msdn.microsoft.com/en-us/library/ms189852.aspx
Example: Setting Up Database Mirroring Using Certificates (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms191140.aspx

關鍵字：Database Mirroring、SQL Server、Principal、Mirror、Witness