搜尋本站文章

2009-12-30

變更修改 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

2009-12-26

認識「資料庫鏡像(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 版本的新名詞,本文也採取新版本的名詞做為說明,這部份要請各位讀者多加留意,並請參考下圖所示:

2009-12-15

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



在 SQL Server 2005/2008 版本,若是要刪除登入帳戶(Login,伺服器層級),在管理工具 SSMS 上會出現以下的警告訊息:
刪除伺服器登入不會刪除與登入相關聯的資料庫使用者。
若要完成處理序,請刪除每個資料庫中的使用者。可能必須先將結構描述的擁有權轉移給新使用者。



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




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




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

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




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


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

2009-12-10

新手學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/

2009-12-04

認識平行(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

2009-11-25

新手學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)」,將可以協助資料庫管理人員輕鬆地自動化管理多台伺服器的日常維護作業。

完整的文章,請參考:第91期之二《新手學SQL Server 2008 「多伺服器作業(Multi-Server Jobs)」(上)》





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

2009-11-18

忘記 sa 密碼,也無法使用 Windows 認證的 Administrator 帳戶來登入 SQL Server。I forgot my SA password and cannot login as administrator using windows authentication.



若是遇到以下的情境,將導致無法登入到 SQL Server:


1. 屬於 sysadmin 固定伺服器角色之成員的所有登入,都因為錯誤而遭到移除。
2. 屬於 sysadmin 固定伺服器角色之成員的所有 Windows 群組,都因為錯誤而遭到移除。
3. 屬於 sysadmin 固定伺服器角色之成員的員工都已經離職,但沒有交接相關資訊。
4. sa 帳戶已遭到停用或是沒有人知道密碼。






已測試過的適用版本:
SQL Server 2005、2008


有數種的解決方法,例如:
1. 重新安裝 SQL Server。
2. 將另外一個執行個體的 master 資料庫的實體檔案(master.mdf、mastlog.ldf),直接覆蓋掉此受損的執行個體之 master 資料庫。

之後再利用先前對此執行個體的 master 資料庫備份來還原,但這些作法,可能相當耗時。
此外,如果 master 資料庫的備份較舊,可能不會有所有的資訊。
如果 master 資料庫的備份比較新,可能與先前的執行個體擁有相同的登入,因此,系統管理員仍然會遭到鎖定。




在 SQL Server 2008 線上叢書內的「疑難排解:當系統管理員遭到鎖定時連接到 SQL Server」一文,有提供一項節省時間的作法:1. 以單一使用者模式使用 -m 或 -f 選項啟動 SQL Server 的執行個體。
2. 接著,電腦本機管理員群組的任何成員都可以利用 sysadmin 固定伺服器角色的成員身分,連接到 SQL Server 的執行個體。




SQL Server 服務啟動選項之說明:
-f
啟動只含最小組態的 SQL Server 執行個體。
如果組態值設定 (如過度調配記憶體) 造成伺服器無法啟動,這就很有用。
以最低組態模式啟動 SQL Server 會將 SQL Server 放在單一使用者模式下。如需詳細資訊,請參閱隨後的 -m 描述。

-m
當您以單一使用者模式啟動 SQL Server 執行個體。以單一使用者模式啟動 SQL Server 執行個體時,只有單一使用者可以進行連接,且不會啟動 CHECKPOINT 處理序。
CHECKPOINT 會保證將交易規律地從磁碟快取區寫到資料庫裝置。(一般而言,如果遇到一些應該修復系統資料庫的問題時,就會使用這個選項)。
啟用 sp_configure allow updates 選項。根據預設,allow updates 是停用的。
在單一使用者模式下啟動 SQL Server 可讓電腦本機管理員群組的任何成員以 sysadmin 固定伺服器角色的成員身分,連接到 SQL Server 的執行個體。



實作練習:
任務1:停用 SQL Server 與 SQL Server Agent Service
當您以單一使用者模式啟動 SQL Server 執行個體時,請先停止 SQL Server Agent 服務。
否則,SQL Server Agent 有可能會先進行連接,您就無法以另一個使用者的身分進行連接。

任務2:以「單一使用者模式(single user mode)」來啟動 SQL Server 執行個體1. 在命令提示列(Command Prompt)中,執行以下的範例程式碼:
net start mssqlserver /m


上述程式碼是以預設的執行個體為例。若是為 SQLEXPRESS 版本,且安裝為具名執行個體個體時,可能的範例程式碼如下:
net start mssql$sqlexpress /m






任務3:使用本機 Windows 的 Administrators 管理群組之成員來連線 SQL Server
使用本機 Windows 的 Administrators 管理群組之成員來連線 SQL Server 後,就可以各項作業,例如:
1. 重新設定 sa 帳戶的密碼。
2. 啟用 sa 帳戶。
3. 設定「伺服器驗證」模式,例如:修改為「SQL Server 及 Windows 驗證模式」,也就是「混合(mixed)模式」。
4. 加入指定的 Windows 帳戶到 sysadmin 固定伺服器角色內。
... 等等。


1. 使用 SSMS 管理工具連線 SQL Server。
這項 SSMS 管理工具雖然很方便,但可能產生多條的連線來執行,將會導致您無法調整所需要的設定。







2. 使用 sqlcmd 模式來登入系統,這會是可以確保單一使用者連線的作法,以下提供數種範例程式碼:
-- 重新設定 sa 帳戶的密碼為:P@ssw0rd。
USE [master]
GO
ALTER LOGIN [sa] WITH PASSWORD=N'P@ssw0rd'
GO

-- 啟用 sa 帳戶
USE [master]
GO
ALTER LOGIN [sa] ENABLE
GO

-- 修改為「SQL Server 及 Windows 驗證模式」,也就是「混合(mixed)模式」
/*
1 是指:「Windows 驗證模式」
2 是指:「SQL Server 及 Windows 驗證模式」
*/
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2
GO


任務4:關閉 SQL Server,再一般模式來重新啟動 SQL Server
經過上述的步驟,應該可以讓系統恢復正常登入方式。


參考資料:
疑難排解:當系統管理員遭到鎖定時連接到 SQL Server
http://msdn.microsoft.com/zh-tw/library/dd207004.aspx

使用 SQL Server 服務啟動選項
http://msdn.microsoft.com/zh-tw/library/ms190737.aspx

以單一使用者模式啟動 SQL Server
http://msdn.microsoft.com/zh-tw/library/ms188236.aspx

sp_password (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms174428.aspx

如何驗證與變更 MSDE 系統管理員密碼
http://support.microsoft.com/kb/322336

2009-11-11

新手學 SQL Server 2008 「以原則為基礎的管理(Policy-Based Management,PBM)」(5):技術文章



在前一篇:「新手學以原則為基礎的管理(4)」文章中,我們討論:檢視原則的健全狀態、匯出與匯入原則、匯入與使用「SQL Server最佳作法」以及排程定期評估指定的原則等主題,每一項的「SQL Server最佳作法」都有其使用情境與需求。

在本期文章中,我們將繼續介紹相關主題:檢視與設定伺服層級的「Facet」、「介面區組態」、「以原則為基礎的管理」搭配「中央管理伺服器」等等。

完整的文章,請參考:第93期之一《新手學以原則為基礎的管理(5)》





相關參考資料:
新手學 SQL Server 2008 「以原則為基礎的管理(Policy-Based Management,PBM)」(1):技術文章
http://sharedderrick.blogspot.com/2009/03/sql-server-2008-policy-based.html

新手學 SQL Server 2008 「以原則為基礎的管理(Policy-Based Management,PBM)」(2):技術文章
http://sharedderrick.blogspot.com/2009/03/sql-server-2008-policy-based_18.htm

新手學 SQL Server 2008 「以原則為基礎的管理(Policy-Based Management,PBM)」(3):技術文章
http://sharedderrick.blogspot.com/2009/06/sql-server-2008-policy-based.html

新手學 SQL Server 2008 「以原則為基礎的管理(Policy-Based Management,PBM)」(4):技術文章
http://sharedderrick.blogspot.com/2009/10/sql-server-2008-4.html

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

2009-11-09

將 SQL Server 資料庫建立在網路硬碟(例如:NAS,Network Attached Storage):network database




本文僅討論實作方式,並未包含:效能、安全等主題的討論。



若在建立資料庫時,選擇是建立到網路硬碟時,將遇到以下的錯誤訊息:
訊息 5110,層級 16,狀態 2,行 1
檔案 "\\10.0.1.8\d$\DBNetDB01.mdf" 所在的網路路徑不支援資料庫檔案。
訊息 1802,層級 16,狀態 1,行 1
CREATE DATABASE 失敗。某些列出的檔案名稱無法建立。請檢查相關的錯誤。







若是確認要在網路硬碟(例如:NAS,Network Attached Storage) 上建立資料庫,請啟用追蹤旗標 1807 。

啟用追蹤旗標 1807 會略過檢查,並允許您使用網路資料庫檔案設定 SQL Server。

因為網路錯誤有損害資料庫完整性的風險,以及使用網路檔案共享來儲存資料庫也可能影響效能。

建議:
您利用存放區域網路 (SAN)、iSCSI 型網路或本機連接的磁碟來儲存 SQL Server 資料庫檔案,因為這個組態可使 SQL Server 效能和可靠性最佳化。
依預設,不會對 SQL Server 啟用使用網路資料庫檔案 (儲存在具備網路功能的伺服器或網路連接的儲存體上)。



實作練習

適用版本:
SQL Server 7、2000、2005、2008

準備工作
請先設定、確認執行個體的「SQL Server 服務帳戶」,對於遠端的網路硬碟具備寫入的權限。

工作1:啟用追蹤旗標 1807
工作2:建立資料庫在 UNC 網路硬碟。


請參考以下的範例碼:
-- 啟用追蹤旗標 1807 。
DBCC TRACEON (1807)
GO

-- 列出所有針對目前工作階段而啟用的追蹤旗標。
DBCC TRACESTATUS;
GO

-- 建立資料庫,已啟用追蹤旗標 1807。
CREATE DATABASE [NetDB01] ON  PRIMARY
( NAME = N'NetDB01', FILENAME = N'\\10.0.1.8\c$\NetDB01.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'NetDB01_log', FILENAME = N'\\10.0.1.8\c$\NetDB01_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

-- 停用指定的追蹤旗標 1807
DBCC TRACEOFF(1807)
GO

-- 列出所有針對目前工作階段而啟用的追蹤旗標。
DBCC TRACESTATUS;
GO

-- 檢查資料庫的檔案
SELECT * FROM NetDB01.sys.database_files

-- 對此資料庫新增資料表與資料
SELECT *
INTO NetDB01.dbo.TransactionHistory
FROM AdventureWorks2008.Production.TransactionHistory


請參考下圖所示:






參考資料:
說明 SQL Server 對網路資料庫檔案的支援
Description of support for network database files in SQL Server
http://support.microsoft.com/kb/304261

每個資料庫管理員都應知道的磁碟機快取與 SQL Server 並用說明
Description of using disk drive caches with SQL Server that every database administrator should know
http://support.microsoft.com/kb/234656/

2009-11-08

使用 SSIS 將 SSRS 所產生 Excel 檔案,做適當的歸檔與分類


友人的需求,因為使用 SSRS 雖然可以直接將 Excel 檔案以電子郵件 E-Mail 方式發送到使用者端,但使用者平常沒時間看,之後還是需要將此 Email自行歸檔到適當的資料夾內存放。

所以,要求能將 Excel 自動歸檔的適合的資料夾內。

可以能處理方式有數種,以下是其中一種處理方式:

(一) 設定 SSRS 產生 Excel 到特定資料夾
請參考下圖所示:




在「路徑」文字方塊中,輸入到您要傳遞報表之現有資料夾的通用命名慣例 (UNC) 路徑 (例如,\\\)。
在路徑的開頭包含雙反斜線字元。請勿在尾端指定反斜線。


關於檔案的覆寫機制有以下的設定方式:
「以較新新版本覆寫現有檔案」。
「如果舊版存在,不要覆寫檔案」。
「加入較新版本時,遞增檔案名稱」。



(二) 利用 SSIS 來移動、複製、改名所需的 Excel 檔案
使用「檔案系統工作」的等功能,將所產生的 Excel 檔案複製移動到適當的資料夾做分類。



「檔案系統」工作會在檔案系統中的檔案和目錄上執行作業。
例如,封裝可使用「檔案系統」工作建立、移動或刪除目錄和檔案。
您也可以使用「檔案系統」工作設定檔案和目錄的屬性。例如,「檔案系統」工作可將檔案設為隱藏或唯讀。





延伸應用:
1. SSRS 產生 Excel 檔案,利用 SSIS 的「指令碼工作」來加密 Excel 檔案,再利用「傳送郵件工作 」將此加密的 Excel 檔案,E-mail 發送給所需的使用者。
2. 取代某些需要利用 SSIS 動態產生 Excel 檔案的情境。



適用版本:SSIS 2005、SSIS 2008、SSRS 2005、SSRS 2008

參考資料:
如何:訂閱報表 (報表管理員)
http://technet.microsoft.com/zh-tw/library/ms157386.aspx

檔案系統工作
http://technet.microsoft.com/zh-tw/library/ms140185.aspx