變更修改 c2 稽核模式所產生的稽核記錄檔之存放路徑

朋友詢問要如何變更 c2 稽核模式所產生的稽核記錄檔之存放路徑呢？

請參考以下的方式：

--01 查詢資料庫的資料檔案之預設路徑：
declare @SmoDefaultFile nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @SmoDefaultFile OUTPUT
SELECT ISNULL(@SmoDefaultFile,N'') AS N'資料檔案的預設路徑'

--02 變更資料庫的資料檔案之預設路徑為磁碟 D:\myTrace
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'D:\myTrace'
GO

--03 查詢資料庫的資料檔案之預設路徑，已經變更為磁碟 D:\myTrace
declare @SmoDefaultFile nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @SmoDefaultFile OUTPUT
SELECT ISNULL(@SmoDefaultFile,N'') AS N'資料檔案的預設路徑'

--04 查詢是否有啟用 C2 稽核追蹤，預設是未啟用。
SELECT * FROM sys.configurations
WHERE name='c2 audit mode'

--05 設定啟用 C2 稽核追蹤
EXEC sys.sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE ;
GO
EXEC sys.sp_configure N'c2 audit mode', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

--06 請重新啟動 SQL Server

--07 查詢是否有啟用 C2 稽核追蹤
SELECT * FROM sys.configurations
WHERE name='c2 audit mode'

--08 查詢系統中目前在執行的追蹤
SELECT * FROM sys.traces

---

關於 C2 稽核模式
選取這個選項，會將伺服器設定為將存取陳述式和物件的失敗嘗試和成功嘗試都記錄下來。這項資訊可協助您分析系統活動並追蹤可能的安全性原則違規。
C2 稽核模式資料會儲存在執行個體之預設資料目錄的檔案中。
如果稽核記錄檔已達到 200 MB 的大小限制，SQL Server 將建立新檔案、關閉舊檔案，並將所有新稽核記錄寫入新檔案。
在填滿稽核資料目錄或關閉稽核之前，會繼續進行這個程序。若要判斷 C2 追蹤的狀態，請查詢 sys.traces 目錄檢視。

重要事項：
C2 稽核模式會將大量的事件資訊儲存到記錄檔，因此該記錄檔會快速成長。
如果儲存記錄檔的資料目錄已用盡空間，SQL Server 將自行關閉。
如果將稽核設為自動啟動，您必須以 -f 旗標 (不使用稽核) 重新啟動執行個體，或為稽核記錄檔釋放額外的磁碟空間。
參考資料：
c2 稽核模式選項
http://technet.microsoft.com/zh-tw/library/ms187634.aspx

認識「資料庫鏡像(Database Mirroring)」所支援的作業模式(Operation mode)

適用的版本：
SQL Server 2005 & SQL Server 2008

在 SQL Server 2005 RTM版本上，「資料庫鏡像」技術可以分成為三種模式：「高可用性」、「高保護性」、「高效能」等模式。
到了 SQL Server 2005 SP1 版本，則依據資料同步的作業模式來分類，可以分成兩種：「同步資料庫鏡像」與「非同步資料庫鏡像」；
其中「同步資料庫鏡像」，又可以稱為「高安全性模式」，其資料庫屬性「SAFETY」是設定為「FULL」，而「非同步資料庫鏡像」則稱為「高效能模式」，其資料庫屬性「SAFETY」設定為「OFF」。

在「同步資料庫鏡像」作業模式下，在依據是否需要自動容錯移轉的功能，再區分兩種模式：「具有自動容錯移轉的高安全性模式」與「不具有自動容錯移轉的高安全性模式」，相對於 SQL Server 2005 RTM 版本來討論，就是對應為「高可用性」與「高保護性」模式。



比較混亂的是，這些專有名詞，混雜在新舊版本的文件說明中，但在管理工具SSMS內，看到的都是 SQL Server 2005 SP1 版本的新名詞，本文也採取新版本的名詞做為說明，這部份要請各位讀者多加留意，並請參考下圖所示：

小技巧：刪除登入帳戶(Logins，伺服器層級)，也一併刪除資料庫使用者(DB Users，資料庫層級)

在 SQL Server 2005/2008 版本，若是要刪除登入帳戶(Login，伺服器層級)，在管理工具 SSMS 上會出現以下的警告訊息：

刪除伺服器登入不會刪除與登入相關聯的資料庫使用者。
若要完成處理序，請刪除每個資料庫中的使用者。可能必須先將結構描述的擁有權轉移給新使用者。

也就是說，刪除了登入帳戶(Login，伺服器層級)，但是在資料庫內仍然有此資料庫使用者(DB Users，資料庫層級)。
可能需要額外再到此資料庫內，刪除此資料庫使用者(DB Users，資料庫層級)。



若僅是一兩個資料庫，這種手動逐一刪除資料庫使用者的方式，或許還可以接受。
假若此登入帳戶(Login，伺服器層級)，可以進入到多個資料庫呢？



這種手動逐一刪除資料庫使用者的方式，可能就十分吃力了。

建議的作法是：

1. 先選取要被刪除的登入帳戶，滑鼠右鍵，選擇「屬性」。
2. 在「登入屬性」視窗，在左邊的「選取頁面」窗格，點選「使用者對應」頁籤。
3. 在右邊的「已對應到此登入的使用者」區域，取消勾選先前所允許登入的資料庫。

4. 點選「確定」。
5. 再刪除此登入帳戶。

經由上述的作法，可以達成：刪除登入帳戶(Logins，伺服器層級)，也一併刪除資料庫使用者(DB Users，資料庫層級)之需求。

新手學SQL Server 2008 「多伺服器作業(Multi-Server Jobs)」 (下)

一般而言，SQL Server在企業上的應用，不會是僅有一兩台，而是有多台資料庫伺服器的使用，這對於資料庫管理師而言，在日常維護作業上，需要同時管理眾多的伺服器，將造成沈重的管理負載。
所以在面對擁有多台伺服器的環境，如何管理自動化，將是一大課題，關於多伺服器管理自動化的主題，包含十分廣泛，本文將以「多伺服器作業(Multi-Server Jobs)」進行討論。

資料庫伺服器所包含的日常維護作業，十分繁瑣，例如：備份資料庫與交易記錄、重組索引、更新索引統計值、對資料庫執行內部一致性檢查、轉檔作業等等。

雖然SQL Server有提供「維護計畫精靈」可以輕鬆地完成上述部分作業，但是面對擁有多台伺服器的環境，對每台資料庫伺服器逐一進行設定，以及後續的變更修正，將是讓人不得不去碰觸的難題。

在過往經驗中，待過百多台SQL Server的單位，也見識到擁有數千台SQL Server的單位，但專屬的資料庫管理人員可能是個位數，真是將「以更少做更多(Do More With Less)」的精神發揮到極致，利用SQL Server內建的「多伺服器作業(Multi-Server Jobs)」，將可以協助資料庫管理人員輕鬆地自動化管理多台伺服器的日常維護作業。
上集設定多伺服器作業，本集接續上集的內容，繼續將後半段練習設定完
畢。
(續)實作練習 1：建置「多伺服器作業」的基礎環境
...

完整的文章，請參考：第94期之一《新手學SQL Server 2008 「多伺服器作業(Multi-Server Jobs)」（下）》

---

下載資料

---

相關參考資料：
新手學SQL Server 2008 「多伺服器作業(Multi-Server Jobs)」 (上)
http://sharedderrick.blogspot.com/2009/11/sql-server-2008-multi-server-jobs.html

更多相關的技術文章，請參考：DB World 資料庫專家電子雜誌
http://www.dbworld.com.tw/

認識平行(parallelism)處理，以MAXDOP、cost threshold for parallelism與max degree of parallelism選項為例

擁有多個處理器 (CPU) 的伺服器，是有機會可以使用多個執行緒來執行平行處理作業。

理想的願景是：線性延展性，例如：
(1) 2 * CPU = 1/2 回應時間
(2) 2 * 資料量 + 使用 2 * CPU = 相同的回應時間


但是，平行處理是需要額外付出成本，例如：
(1) 增加執行時的額外負載。
(2) 可能會減少CPU的總處理能力。

所以，在多個處理器 (CPU) 的伺服器上，以實際測試的效能結果來看：1+1，的確是不等於 2。

---

SQL Server 提供平行查詢，讓擁有多個處理器 (CPU) 的電腦，也能獲得最佳的查詢執行和索引作業。
因為 SQL Server 可利用數個作業系統執行緒平行地執行查詢或索引作業，所以可快速而有效率地完成作業。

平行查詢實際所使用的執行緒數目，是在查詢計畫執行初始化時，由計畫的複雜度與平行處理原則的程度決定。
平行處理原則的程度決定將要使用的 CPU 最大數目，而不是將要使用的執行緒數目。

平行處理原則的程度值是在伺服器層級設定，可以使用 sp_configure 系統預存程序修改。
您可以指定 MAXDOP 查詢提示或 MAXDOP 索引選項，來覆寫個別查詢或索引陳述式的這個值。

如果下列任何條件為真，則 SQL Server 查詢最佳化工具不使用平行執行計畫進行查詢：
1. 查詢的序列執行成本不夠高，無法考量替代平行執行計畫。
2. 序列執行計畫被認為比特定查詢之任何可能的平行執行計畫更快。

3. 此查詢包含無法平行執行的純量或關聯式運算子。特定運算子可能造成查詢計畫的一個區段以序列模式執行，或整個計畫以序列模式執行。

在執行 SQL Server Enterprise 的多處理器電腦上，索引陳述式可能會如同其他查詢般，使用多個處理器來執行與索引陳述式相關聯的掃描、排序和索引作業。

執行單一索引陳述式所用的處理器數目，取決於 max degree of parallelism 組態選項、目前的工作負載以及索引統計資料。

max degree of parallelism 選項會決定用於執行平行計畫的最大處理器數目。
如果 SQL Server Database Engine 偵測到系統忙碌中，在陳述式執行開始之前，會先自動降低索引作業之平行處理原則的程度。

如果非資料分割索引的前端索引鍵資料行具有有限的相異值數目，或者每個相異值的頻率具有大幅差異，Database Engine 也可能會降低平行處理原則的程度。

查詢最佳化工具所使用的處理器數目通常可以提供最佳的效能。

然而，諸如建立、重建、卸除非常大的索引都需要大量的資源，並可能在索引作業期間造成其他應用程式和資料庫作業的資源不足。

當發生此問題時，您可以指定 MAXDOP 索引選項和限制索引作業要使用的處理器數目，藉以手動設定執行索引陳述式要使用的最大處理器數目。

---

環境說明：
1. Windows Server 2008 R2 (x64) Enterprise Edition - 版本：6.1.7600。
2. SQL Server 2008 (SP1) (x64) Enterprise Editon - 版本：10.0.2531.0。

3. 四核心的 CPU。
4.設定最大可用的記憶體是：1024 MB。

-- 準備環境
-- 建立資料庫
CREATE DATABASE parallelism
GO
-- 建立資料表
CREATE TABLE parallelism.dbo.tbMax
(sid INT identity PRIMARY KEY, cDateTime datetime,cData char(8000))
GO
-- 新增資料列
SET NOCOUNT ON
GO
DECLARE @cnt INT=1
WHILE @cnt<=50000 
BEGIN  
INSERT parallelism.dbo.tbMax VALUES( GETDATE() ,GETDATE())  
SET @cnt+=1 
END   
-- 5 萬筆 SELECT COUNT(*) FROM parallelism.dbo.tbMax 

---

--EX1. 使用「查詢提示(Query Hints)」：MAXDOP

MAXDOP 針對指定這個選項的查詢覆寫 sp_configure 和資源管理員的 max degree of parallelism 組態選項。 MAXDOP 查詢提示可能會超過使用 sp_configure 所設定的值。

如果 MAXDOP 超過使用資源管理員所設定的值，Database Engine 就會使用＜ALTER WORKLOAD GROUP (Transact-SQL)＞中所描述的資源管理員 MAXDOP 值。
當您使用 MAXDOP 查詢提示時，適用所有搭配 max degree of parallelism 組態選項使用的語意規則。

--01 預設值：採取：平行查詢執行計畫，耗用成本：334.553
/*
採取：平行查詢執行計畫，可以使用一個以上的執行緒。
使用到「平行處理原則(蒐集資料流)」運算子。
*/
SELECT * FROM parallelism.dbo.tbMax
ORDER BY cDateTime desc

--02 設定查詢提示 MAXDOP 為 1，採取：序列執行計畫，設定耗用成本：603.74
/*
設定查詢提示 MAXDOP 為 1
採取：序列執行計畫，執行時只會使用一個執行緒。
無法使用「平行處理原則(蒐集資料流)」運算子。
*/
SELECT * FROM parallelism.dbo.tbMax
ORDER BY cDateTime desc
OPTION (MAXDOP 1)

---

--EX2. 伺服器屬性的設定值：cost threshold for parallelism

cost threshold for parallelism 選項 使用 cost threshold for parallelism 選項指定 Microsoft SQL Server 為查詢建立及執行平行計畫的臨界值。

唯有執行同一查詢的序列計畫的估計成本高於 cost threshold for parallelism 中設定的值時，SQL Server 才會建立及執行查詢的平行計畫。

成本是指在特定硬體組態下，執行序列計畫所需估計的經過時間 (以秒為單位)。只有在對稱式多重處理器上才應該設定 cost threshold for parallelism。

---

通常較長的查詢比較適合平行計畫；
其效能「優點」：
就是不需要額外的時間來初始化、同步處理及終止平行計畫。


在執行混合較短及較長的查詢時，就會啟用 cost threshold for parallelism 選項。 較短的查詢執行序列計畫，而較長的查詢使用平行計畫。

---

cost threshold for parallelism 的值可判斷哪些查詢是短的，所以要使用序列計畫來執行。 


在某些狀況下，即使查詢的成本計畫小於目前的 cost threshold for parallelism 值，還是會選擇平行計畫。


之所以會發生這種情形，是因為在決定要使用平行或序列計畫時，所依據的成本預估值是在完成整體最佳化之前提供的。

cost threshold for parallelism 選項可設成從 0 到 32767 的任何值。預設值是 5。
在下列情況下，SQL Server 會忽略 cost threshold for parallelism 值：
1. 您的電腦只有一個處理器。
2. 因為 affinity mask 組態選項的關係，只有一個 CPU 可供 SQL Server 使用。
3. max degree of parallelism 選項設為 1。

 cost threshold for parallelism 選項是進階選項。如果您是使用 sp_configure 系統預存程序來變更設定，只有當 show advanced 選項設定為 1 時，才能變更 cost threshold for parallelism。

這個設定會立即生效 (不需重新啟動伺服器)。

--01 查詢 cost threshold for parallelism 選項，其預設值為：5。
SELECT * FROM sys.configurations
WHERE name = 'cost threshold for parallelism'

--02 調整 cost threshold for parallelism 選項值為：700。
EXEC sp_configure 'show advanced options', 1;
GO
reconfigure;
GO
EXEC sp_configure 'cost threshold for parallelism', 700;
GO
reconfigure;
GO

--03 查詢 cost threshold for parallelism 選項值為：700。
SELECT * FROM sys.configurations
WHERE name = 'cost threshold for parallelism'

--04 採取：序列執行計畫，耗用成本：603.74
/*
採取：序列執行計畫，執行時只會使用一個執行緒。

因為設定 cost threshold for parallelism 選項值為：700。
但此程式的耗用成本為：603.74，無法使用「平行處理原則(蒐集資料流)」運算子。
*/
SELECT * FROM parallelism.dbo.tbMax
ORDER BY cDateTime desc

--05 調整 cost threshold for parallelism 選項值為預設值：5。
EXEC sp_configure 'show advanced options', 1;
GO
reconfigure;
GO
EXEC sp_configure 'cost threshold for parallelism', 5;
GO
reconfigure;
GO

-- 查詢 cost threshold for parallelism 選項值為：5。
SELECT * FROM sys.configurations
WHERE name = 'cost threshold for parallelism'

--06 採取：平行查詢執行計畫，耗用成本：334.553
/*
採取：平行查詢執行計畫，可以使用一個以上的執行緒。
使用到「平行處理原則(蒐集資料流)」運算子。
*/
SELECT * FROM parallelism.dbo.tbMax
ORDER BY cDateTime desc

---

--EX3. 設定 cost threshold for parallelism 超過 2 秒以上，就建立及執行查詢的平行計畫

成本是指在特定硬體組態下，執行序列計畫所需估計的經過時間 (以秒為單位)。只有在對稱式多重處理器上才應該設定 cost threshold for parallelism。

--01 查詢 cost threshold for parallelism 選項，其預設值為：5。
SELECT * FROM sys.configurations
WHERE name = 'cost threshold for parallelism'

--02 採取：序列執行計畫，耗用成本：2.12348
USE AdventureWorks2008
GO
SELECT TransactionID
FROM Production.TransactionHistory
WHERE TransactionID >185000
ORDER BY TransactionType DESC

--02 調整 cost threshold for parallelism 選項值為：2。
EXEC sp_configure 'show advanced options', 1;
GO
reconfigure;
GO
EXEC sp_configure 'cost threshold for parallelism', 2;
GO
reconfigure;
GO

--03 查詢 cost threshold for parallelism 選項值為：2。
SELECT * FROM sys.configurations
WHERE name = 'cost threshold for parallelism'

--04 採取：平行查詢執行計畫，耗用成本：1.34245
/*
採取：平行查詢執行計畫，可以使用一個以上的執行緒。
使用到「平行處理原則(蒐集資料流)」運算子。
*/
USE AdventureWorks2008
GO
SELECT TransactionID
FROM Production.TransactionHistory
WHERE TransactionID >185000
ORDER BY TransactionType DESC

--05 調整 cost threshold for parallelism 選項值為預設值：5。
EXEC sp_configure 'show advanced options', 1;
GO
reconfigure;
GO
EXEC sp_configure 'cost threshold for parallelism', 5;
GO
reconfigure;
GO

-- 查詢 cost threshold for parallelism 選項值為：5。
SELECT * FROM sys.configurations
WHERE name = 'cost threshold for parallelism'

---

--EX4. 伺服器屬性的設定值：max degree of parallelism 選項

SQL Server 在具有多個微處理器或 CPU 的電腦上執行時，會偵測平行處理原則的最佳程度，也就是說，針對每一個平行計畫的執行，執行單一陳述式所要採用的處理器個數。您可以使用 max degree of parallelism 選項來限制要用於平行計畫執行的處理器數目。

預設值為 0 會使用所有可以使用的處理器。將 max degree of parallelism 設成 1 可抑制產生平行計畫。將這個值設成大於 1 的數字 (最大值 64)，則會限制單一查詢執行所使用的最大處理器個數。 如果指定的數值大於可用的處理器數目，就會使用可用處理器的實際數目。

如果電腦只有一個處理器，則會忽略 max degree of parallelism 值。

覆寫 max degree of parallelism SQL Server 會針對查詢、索引資料定義語言 (DDL) 作業，以及靜態和索引鍵集驅動資料指標擴展，考慮進行平行執行計畫。 您可以在查詢陳述適中指定 MAXDOP 查詢提示，來覆寫查詢中的 max degree of parallelism 值。

建立或重建索引的索引作業，或者卸除叢集索引的索引作業，都需要大量資源。您可以在索引陳述式中指定 MAXDOP 索引選項，覆寫索引作業中的 max degree of parallelism 值。

MAXDOP 值會在執行時套用至陳述式，且不會儲存在索引中繼資料內。 除了查詢作業和索引作業外，此選項也會控制 DBCC CHECKTABLE、DBCC CHECKDB 和 DBCC CHECKFILEGROUP 的平行處理原則。您可以使用追蹤旗標 2528，來停用這些陳述式的平行執行計畫。

---1 查詢 max degree of parallelism 選項，其預設值為 0，表示可以使用所有的處理器。
SELECT * FROM sys.configurations
WHERE name ='max degree of parallelism'

--02 採取：平行查詢執行計畫，耗用成本：334.553
/*
採取：平行查詢執行計畫，可以使用一個以上的執行緒。
使用到「平行處理原則(蒐集資料流)」運算子。
*/
SELECT * FROM parallelism.dbo.tbMax
ORDER BY cDateTime desc
--03 調整 max degree of parallelism 選項值為：1，則會限制單一查詢執行所使用的最大處理器個數為：1。
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'max degree of parallelism', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

--04 採取：序列執行計畫，耗用成本：603.74
/*
採取：序列執行計畫，執行時只會使用一個執行緒。

因為設定  max degree of parallelism 選項值為：1，則會限制單一查詢執行所使用的最大處理器個數為：1。
無法使用「平行處理原則(蒐集資料流)」運算子。
*/
SELECT * FROM parallelism.dbo.tbMax
ORDER BY cDateTime desc

--05 調整 max degree of parallelism 選項為預設值：0，表示可以使用所有的處理器。
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'max degree of parallelism', 0;
GO
RECONFIGURE WITH OVERRIDE;
GO

--06 採取：平行查詢執行計畫，耗用成本：334.553
/*
採取：平行查詢執行計畫，可以使用一個以上的執行緒。
使用到「平行處理原則(蒐集資料流)」運算子。
*/
SELECT * FROM parallelism.dbo.tbMax
ORDER BY cDateTime desc

---

參考資料：
cost threshold for parallelism 選項 http://technet.microsoft.com/zh-tw/library/ms188603.aspx

max degree of parallelism 選項 http://msdn.microsoft.com/zh-tw/library/ms181007.aspx

平行查詢處理 http://msdn.microsoft.com/zh-tw/library/ms178065.aspx

平行查詢範例 http://msdn.microsoft.com/zh-tw/library/ms175097.aspx

平行程度 http://msdn.microsoft.com/zh-tw/library/ms188611.aspx

查詢提示 (Transact-SQL) http://technet.microsoft.com/zh-tw/library/ms181714.aspx

設定平行索引作業 http://msdn.microsoft.com/zh-tw/library/ms189329.aspx

Parallelism http://blogs.msdn.com/craigfr/archive/tags/Parallelism/default.aspx

Parallel Query Execution Presentation http://blogs.msdn.com/craigfr/archive/2007/04/17/parallel-query-execution-presentation.aspx