2012年5月29日 星期二

如何透過 Distributed Replay 進行資料庫升級評估

        在以往進行資料庫升級時，大部份都需要進行軟體相容性的測試，而最常見的就是在測試的時候，透過 [SQL Server Profiler] 進行SQL與T-SQL的錄製，然後再透過 [SQL Server Upgrade Advisor] 進行分析並產生報表資料，但經由這樣的測試，有時候仍然無法重現所有的問題，尤其是模擬大量使用者的情況。

針對上述的問題，SQL Server 2012推出了 [Distributed Replay] 的功能，讓您可以將錄製好的追踨檔分散到多台的Client端的機器上執行，藉以透過多台電腦的執行，找出系統升級後可能發生的問題。


 Distributed Replay 主要元件：
  • Distributed Replay 管理工具：用來與分散式重新執行控制器進行通訊的主控台應用程式 DReplay.exe。您可以使用管理工具來控制分散式重新執行。
  • Distributed Replay Controller：執行 Windows 服務 (SQL Server Distributed Replay Controller) 的電腦。Distributed Replay Controller 可協調 Distributed Replay Client 的動作。每個 Distributed Replay 環境都只能有一個 Controller 執行個體。
  • Distributed Replay Client：一部或多部執行 Windows 服務 (SQL Server Distributed Replay Client) 的電腦 (實體或虛擬)。多個 Distributed Replay Client 可共同運作，以模擬 SQL Server 執行個體的工作負載。每個 Distributed Replay 環境都可以有一個或多個用戶端。
  • 目標伺服器：Distributed Replay Client 可用來重新執行追蹤資料的 SQL Server 執行個體。我們建議您將目標伺服器放置於測試環境中。

Distributed Replay 管理工具、Controller 及 Client 可以安裝在不同的電腦上，也可以安裝在同一部電腦上。在同一部電腦上，只能執行一個 Distributed Replay Controller 或 Client 服務的執行個體。



環境描述：
1、Distributed Replay 管理工具 + Distributed Replay Controller + Distributed Replay Client + 目標伺服器

由於我要模擬出二台客戶端的情況，所以第一台我會讓他同時擔任主控端與客戶端的角色，相對的此台機器上也會安裝SQL Server 2012的資料庫。

電腦名稱：SQL2012-RTM
IP：192.168.1.12
OS：Windows 2008 R2
DB：SQL Server 2012

2、Distributed Replay Client
此部份我以Windows 7的機器進行。

電腦名稱：win7-86
IP：192.168.1.80
OS：Windows 7

PS：Distributed Replay只有 SQL Server 2012 Web版本以上才有支援，而只有Enterprise可以支援到16個DReplayClient，其他的版本都只能支援一個Client。


1、安裝Distributed Replay

1-1 第一台 - 主控端 (Distributed Replay 管理工具 + Distributed Replay Controller  + Distributed Replay Client  )
在第一台的電腦上，除了資料庫之外，請一定要安裝下列的二個選項。

1-2 第二台 - Client(Distributed Replay Client )
關於第二台的部份，只需要安裝 Distributed Replay Client  即可，但由於此功能並沒有獨立安裝檔，所以仍需要放入SQL Server的光碟片進行安裝，在特徵選取的部份，只需勾選 [Distributed Replay Client] 即可。




2、環境設定

2-1 第一台 - 主控端
2-1-1 請更換 SQL Server 的啟動帳號為 Domain User。
          以 [caryhsu\a-cahs] 為例
2-1-2 請新增一個目錄提供給dreplay執行時進行輸出。
          以 [C:\labData\dreplay\OutFolder] 為例。
2-1-3 新增 [caryhsu\a-chas] 可以擁有讀寫下列目錄的權限。
  •           C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\DReplayController
  •           C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\DReplayClient
  •           C:\labData\dreplay\OutFolder
2-1-4 更改DReplay的設定檔。
依照預設值，不需特別的改變，如果你有興趣了解的話，再請參考下列的位置。

設定檔位址：C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\DReplay.Exe.Replay.config
參考說明：http://msdn.microsoft.com/zh-tw/library/ff878359

2-1-5 更改DReplayController的設定檔。
依照預設值，不需特別的改變。

設定檔位址：C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayController\DReplayController.config
參考說明：http://msdn.microsoft.com/zh-tw/library/ff878359

2-1-6 更改DReplayClient的設定檔。
請開啟下列的設定檔，更改Controller的名稱。

設定檔位址：C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient\DReplayClient.config
參考說明：http://msdn.microsoft.com/zh-tw/library/ff878359
設定檔內容：

<?xml version="1.0" encoding="utf-8"?>
<Options>
  <Controller>SQL2012-RTM</Controller>
  <WorkingDirectory>C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient\WorkingDir\</WorkingDirectory>
  <ResultDirectory>C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient\ResultDir\</ResultDirectory>
  <LoggingLevel>CRITICAL</LoggingLevel>
</Options>

2-1-7 開啟 [系統管理工具] -> [服務]，將下列兩個服務的啟動帳號變更為 [caryhsu\a-cahs]之後，然後啟動服務。
  • SQL Server Distributed Replay Controller
  • SQL Server Distributed Replay Client



2-2 第二台 - 客戶端(Client)
2-2-1 請新增一個目錄提供給dreplay執行時進行輸出。
          以 [C:\labData\dreplay\OutFolder] 為例。
2-2-2 新增 [caryhsu\a-chas] 可以擁有讀寫下列目錄的權限。
  •           C:\Program Files\Microsoft SQL Server\110\Tools\Binn\DReplayClient
  •           C:\labData\dreplay\OutFolder
2-1-3 更改DReplayClient的設定檔。
請開啟下列的設定檔，更改Controller的名稱。

設定檔位址：C:\Program Files\Microsoft SQL Server\110\Tools\DReplayClient\DReplayClient.config
參考說明：http://msdn.microsoft.com/zh-tw/library/ff878359
設定檔內容：

<?xml version="1.0" encoding="utf-8"?>
<Options>
  <Controller>SQL2012-RTM</Controller>
  <WorkingDirectory>C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient\WorkingDir\</WorkingDirectory>
  <ResultDirectory>C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient\ResultDir\</ResultDirectory>
  <LoggingLevel>CRITICAL</LoggingLevel>
</Options>

2-1-4 開啟 [系統管理工具] -> [服務]，將下列服務的啟動帳號變更為 [caryhsu\a-cahs]之後，然後啟動服務。
  • SQL Server Distributed Replay Client

3、錄製追蹤檔案
首先我們必須透過SQL Server Profiler來進行追蹤，藉以記錄系統進行時所有執行的SQL語法。

3-1 啟動 SQL Server Profiler，然後連結到資料庫。
3-2 開啟一個新的追蹤，在範本的地方，請記得選擇 [TSQL_Replay]。


3-3 開始全部執行一次系統上的所有功能。
3-4 當全部執行完成後，請再點選停止後，再將追蹤檔儲存，並放置在下列的位址上。
避蹤檔儲存位址：C:\labData\dreplay\dreplay.trc


4、執行 DReplay
在執行DReplay之前大約的說明一下流程，簡單的來說主要分成兩大步驟，第一個是先進行Preprocess，然後再進行Replay的動作，以下的動作都只需在第一台的主機上執行即可，詳細流程如下。


4-1 執行Preprocess
4-1-1 開啟一個DOS Command視窗
4-1-2 切換目錄
cd C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\
4-1-3 預先啟動 DReplay 的 Preprocess模式
dreplay preprocess -i  "C:\labData\dreplay\dreplay.trc" -d "C:\labData\dreplay\OutFolder"


4-2 執行Replay
4-2-1 開始進行Replay
dreplay replay -d "C:\labData\dreplay\OutFolder" -o -w "SQL2012-RTM,win7-86" -s "SQL2012-RTM"



4-2-2 觀察目前執行的狀況
dreplay status -m localhost -f 60



執行完成後，所有的資料都會輸出到 C:\labData\dreplay\OutFolder 的目錄中，當然執行的過程可以透過4-2-2的語法即時的看到，以上是針對 DReplay 功能的介紹，也希望可以對大家有所幫助。


參考連結：
SQL Server Distributed Replay
http://msdn.microsoft.com/zh-tw/library/ff878183.aspx
安裝 Distributed Replay (安裝程式)
http://msdn.microsoft.com/zh-tw/library/gg471548
設定 Distributed Replay
http://msdn.microsoft.com/zh-tw/library/ff878359
Features Supported by the Editions of SQL Server 2012
http://msdn.microsoft.com/en-us/library/cc645993.aspx
使用 Upgrade Advisor 來準備升級
http://msdn.microsoft.com/zh-tw/library/ms144256.aspx


關鍵字：SQL 2012、SQL Server、Distributed Replay、Upgrade Advisor 、dreplay
張貼者： caryhsu 於 晚上9:18 0 意見
標籤：

2012年5月23日 星期三

WSUS內建資料庫整合與應用

        最近接到一個需求，要針對WSUS的機器進行自訂報表的設計，想當然最快的方式就是透過 Reporting Service (個人比較熟)進行報表的應用，WSUS實際上所以的資料會存在SQL Server上，但是WSUS預設安裝的資料庫與一般的不同，WSUS預設資料庫為 [Microsoft Internal Database]，版本對應到SQL Server 2005(90)，在連線上必須透過PIPE的方式進行，另外由於此資料庫沒有管理工具，所以如果要進行設定與管理時，建議可以安裝 [Microsoft SQL Server Management Studio Express]進行，連結如下。

WSUS = Windows Server Update Services

下載連結：
Microsoft SQL Server Management Studio Express
http://www.microsoft.com/en-us/download/details.aspx?id=8961


安裝完成後，連接上有特定的方式，在 [Server Name]的地方需輸入下列的語法進行登入。

連線語法：
\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query



由於SQL Server 2005 Express預設是沒有啟用遠端連線的，需要更改設定才可以，但 [Microsoft Internal Database] 是無法啟用的，所以你只能透過本機端的方式進行存取，但是如果你有需要進行遠端時，您可以參考下列的建議與連線進行。

1、 將WSUS的資料庫整併到目前其他SQL Server Express以上版本的資料庫。
2、 在WSUS的電腦上安裝SQL Server Express的資料庫(免費)，然後將WSUS的資料庫移值到SQL Express上。

轉移方式參考：
Migrating from Windows Internal Database to SQL Server 2005
http://technet.microsoft.com/en-us/library/cc708558(v=ws.10).aspx
How to move the WSUS database to a different SQL Server
http://blogs.msdn.com/b/john_daskalakis/archive/2009/04/06/9533669.aspx

關鍵字：Windows Internal Database、WSUS database、SQL Server Management Studio Express、WSUS、Windows Server Update Services
張貼者： caryhsu 於 晚上9:19 0 意見

2012年5月17日 星期四

SQL Server 欄位資料加解密介紹與使用

        企業中資料庫通常都有存放客戶的基本資料，為了保護這些資料，通常都是透過權限的控管，讓限定的使用者可以存取資料庫，但是這樣還是有資料外洩的可能性，所以通常會再資料進行加密的動作，在SQL Server 2005以前，必須透過外部的程式將資料加密後，再將資料寫回，但是幸運的，SQL Server 2005就新增了對稱與非對稱式的加密方法，讓使用者可以透過內建函數就可以達成欄位資料加解密的動作。

在對稱式與非對稱式加解密的選擇上，簡單的區分，如果是在OLTP的系統上，比較不建議使用非對稱式，因為非對稱式較耗損資源，容易加重系統的負荷，而對稱式就會是比較好的選擇，因為又可以達到資料加密，而且對效能的影響相對的又比較的少，底下是SQL Server本身的加解密架構圖，下列我以範例分別進行說明。

關於對稱式與非對稱式金鑰的比較分析，請參考下列的連結。

對稱和非對稱式加密的描述
http://support.microsoft.com/kb/246071


在測試進行前，請先透過下列的語法建立測試資料表：
CREATE TABLE [dbo].[Customer](
[sn] [smallint] NOT NULL,
[name] [nvarchar](50) NOT NULL,
[sid] [varchar](15) NOT NULL,
[e_sid] [varbinary](max) NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[sn] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

1、Master Key 管理與建立

1-1 每一個資料庫皆會對應一把Master Key，你可以透過下列的語法確認目前的資料庫是否有包含 Master Key。
select database_id, name, is_master_key_encrypted_by_server
from sys.databases
1-2 Create Master Key：
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
1-3 Backup Master key：
BACKUP MASTER KEY TO FILE = 'phyical path\mk_name'
ENCRYPTION BY PASSWORD = 'password'
PS:密碼的部份必須符合複雜性檢查，而且請你一定要記得備份Master Key，以免日後發生問題時，無法進行資料解密的動作。

1-4 Restore Master Key：
在還原Master Key之前，必須先將Master Key開啟，然後再將Master Key還原，如果無法還原時，只須在還原時加上Force的參數即可。
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'
GO

RESTORE MASTER KEY
FROM FILE = 'phyical path\mk_name'
DECRYPTION BY PASSWORD = 'password'
ENCRYPTION BY PASSWORD = 'password'
GO

CLOSE MASTER KEY;
GO
Force選項說明： Specifies that the RESTORE process should continue, even if the current database master key is not open, or if SQL Server cannot decrypt some of the private keys that are encrypted with it.


2、憑證(Certificate)管理與建立
憑證主要相依於Master Key，所以在建立憑證前，請記得一定要先進行第一個程序。

2-1 查詢目前資料庫中已建立的憑證
select * from sys.certificates
2-2 Create Certificate：
CREATE CERTIFICATE cary_test_cert
WITH SUBJECT = 'cary test',
EXPIRY_DATE = '20180430';
GO
PS:憑證日期的部份，你可以設定一個較久的時間，但是就算是憑證過期也還是可以使用，此日期根據官方說法主要是提供其他的程式進行確認，所以不必擔心。

參考連結：
CREATE CERTIFICATE (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms187798(v=sql.90).aspx

2-3 Backup Certificate：
BACKUP CERTIFICATE cary_test_cert TO FILE = 'phyical path\certificate_name'
WITH PRIVATE KEY ( FILE = 'phyical path\pk_name' ,
ENCRYPTION BY PASSWORD = 'password');
GO
2-4 Create Certificate from backup file：
CREATE CERTIFICATE cary_test_cert
FROM FILE = 'phyical path\certificate_name'
WITH PRIVATE KEY (FILE = 'phyical path\pk_name',
DECRYPTION BY PASSWORD = 'password');
GO

3、對稱式金鑰使用與管理

3-1 查詢目前資料庫中已建立的對稱式金鑰
select * from sys.symmetric_keys

3-2 Create Symmetric Key
CREATE SYMMETRIC KEY cary_test_sym_key WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE cary_test_cert;
GO
3-3 透過對稱式金鑰進行加密
OPEN SYMMETRIC KEY cary_test_sym_key
    DECRYPTION BY CERTIFICATE cary_test_cert;

UPDATE cary_test.dbo.Customer
SET e_sid
    = EncryptByKey(Key_GUID('cary_test_sym_key'), sid);
GO

CLOSE SYMMETRIC KEY cary_test_sym_key
3-4 透過對稱式金鑰進行解密
OPEN SYMMETRIC KEY cary_test_sym_key
   DECRYPTION BY CERTIFICATE cary_test_cert;
GO

SELECT sn, name, sid, e_sid,
    CONVERT(varchar, DecryptByKey(e_sid))
    AS 'decrypt_e_sid'
    FROM cary_test.dbo.Customer;
GO

CLOSE SYMMETRIC KEY cary_test_sym_key
3-5 對稱式金鑰主要透過憑證進行產生，金鑰本身無法進行備份，而是在資料庫進行備份時也會一起進行備份，但是如果你有需要將資料庫單獨進行步署時，可以將憑證先行備份後，再還原到新的機器上即可。


4、非對稱式金鑰使用與管理

4-1 查詢目前資料庫中已建立的非對稱式金鑰
select * from sys.asymmetric_keys

4-2 Create Asymmetric Key
CREATE ASYMMETRIC KEY cary_test_asym_key
    WITH ALGORITHM = RSA_2048
    ENCRYPTION BY PASSWORD = N'password'
GO
4-3 使用非對稱式金鑰
4-3-1 透過非對稱式金鑰加密
UPDATE cary_test.dbo.Customer
SET e_sid
    = EncryptByAsymKey(AsymKey_ID('cary_test_asym_key'), sid)
GO

4-3-2 透過非對稱式金鑰解密
SELECT sn, name, sid, e_sid,
CONVERT(varchar(max),
    DecryptByAsymKey( AsymKey_Id('cary_test_asym_key'),
    e_sid , N'password')) 'decrypt_e_sid'
FROM cary_test.dbo.Customer
4-3-3 透過非對稱式金鑰加密時沒有備份的方式，所以如果你有可能會要將資料庫或金鑰步署到其他台時，請使用憑證的方式進行加密。

4-4 使用非對稱式金鑰與憑證
4-4-1 使用非對稱式金鑰與憑證進行加密
UPDATE cary_test.dbo.Customer
SET e_sid =
EncryptByCert(Cert_ID('cary_test_cert'), sid)
GO
4-4-2 使用非對稱式金鑰與憑證進行解密
select sn, name, sid, e_sid,
convert(varchar(max),DecryptByCert(Cert_Id('cary_test_cert'),
e_sid)) 'decrypt_e_sid'
from cary_test.dbo.Customer

5、權限設定
對於加解密的部份需要嚴格的進行控管，所以請單獨設定允許特定的使用者可以進行加解密，以免同樣的造成資料的外洩。

5-1 對稱式金鑰權限設定
5-1-1 賦予權限：
GRANT CONTROL ON SYMMETRIC KEY::[key_name] TO [user_name]
GO
5-1-2 取消權限：
REVOKE CONTROL ON SYMMETRIC KEY::[key_name] TO [user_name]
GO

5-2 對稱式金鑰權限設定
5-2-1 賦予權限：
GRANT CONTROL ON ASYMMETRIC KEY::[key_name] TO [user_name]
GO
5-2-2 取消權限：
REVOKE CONTROL ON ASYMMETRIC KEY::[key_name] TO [user_name]
GO

5-3 對稱式金鑰權限設定
5-3-1 賦予權限：
GRANT CONTROL ON CERTIFICATE::[certificate_name] TO [user_name]
GO
5-3-2 取消權限：
REVOKE CONTROL ON CERTIFICATE::[ certificate _name] TO [user_name]
GO


參考連結：

關鍵字：Certificates、Master Key、Symmetric key、Asymmetric key
張貼者： caryhsu 於 晚上8:40 8 意見
標籤：

2012年5月2日 星期三

SQL Server DBA聚會!!

因受Study4.tw的邀請，將於5/19日於台中舉辦兩堂SQL Server的課程，由我本人與另一位講師進行SQL Server的介紹，也希望藉由這個機會邀請各方的DBA好手可以一起參加並相互學習，詳細資訊，再請參考下列資訊與連結。

訊息轉貼(From Study4.TW)

免費入場

你是DBA嗎?或是專門寫T-SQL的程式設計師?台中難得一見的SQL Server聚會，就在五月要展開了， 我們特別邀請到兩位SQL Server的大師James和 Cary，在這邊分享SQL Server的經驗， 並且針對SQL Server 2012的新HA架構，進行超詳細的介紹與解說，歡迎各個DBA一起來互相討論心得與交流喔!!
  • 時間：2012/5/19 (六) 13:00
  • 地點：台中市西區民權路239號17樓
  • 課程大綱：
    1. T-SQL 實務經驗分享 - James
    2. SQL Server 2012 HA新架構 AlwaysOn - Cary
  • 報名方式：( 因官網報名系統尚未完成，所以請利用以下方式報名 )
    1. Facebook：加入粉絲團並參加活動
    2. EMail：寫信至study4.tw@livemail.tw


SQL Server DBA聚會!
http://www.study4.tw/
張貼者： caryhsu 於 晚上9:28 0 意見
訂閱： 文章 (Atom)