CaryHsu - 學無止盡

2011年3月10日星期四

SQL Server 負載平衡架構介紹(Load Balancing)

最近常常遇到有人問我，到底SQL Server的負載平衡(Load Balancing)要怎麼做，Cluster架好後為什麼另一台電腦都不能接收服務，我想在這一起問答給大家好了，也介紹一下我目前的作法給大家。首先我要強調一下，Cluster本身稱為容錯移轉叢集(Failover Cluster)，所以只有支援(Failover)，沒有支援Load Balancing (這個最多人問@_@)所以你會看到另外一台只能在那等著，不能進行服務，那到底Microsoft有沒有計劃推出這類所謂真的叢集架構(Real Cluster)呢，據我自已的推測與Microsoft的說法，因為Windows本身的架構設計，所以目前暫無計劃推出像Oracle的RAC機制，所以我想在SQL Server近期的二版本推出時，應該也不會包含，至少在SQL Server 2012(Denali)就暫時沒有，但就我目前服務客戶的情況下，其實大部份的效能瓶頸都是在I/O的部份，所以我想就算轉換到Oracle的RAC架構，好像也沒有太大的效果，當然詳細的說明大家還是可以參考下列的網址就會更清楚了。

Why SQL Server May Be More Suitable for You than Oracle RAC

http://download.microsoft.com/download/6/9/D/69D1FEA7-5B42-437A-B3BA-A4AD13E34EF6/WhyNotOracleRAC.docx

Oracle RAC架構圖：

SQL Server的負載平衡(Load Balancing)作法：目前在SQL Server上有三種作法可以達到負載平衡(Load Balancing)，我們就逐一的介紹：

Replication (複寫)
Log Shipping (日記檔傳送)
Database Mirroring (鏡射)

1、Replication(複寫)：我想這個功能是我認為目前最佳的負載平衡(Load Balancing)方案，在 Replication 中主要有三種方法，分別是，Snapshot、Transactional、Merge，基本上可以使用Merge與Transactional，因為Snapshot同步化資料時不會比對差異或是否有資料異動，所以不適合，而Merge是一個不錯的選擇方案，而且可以自動比對處理同步衝突，但因為資料比對時間過久，而且衝突排除有時候會失敗需手動排除，所以個人最推薦使用Transactional。

Transactional Replication主要設定架構上有兩種，第一種是一台讀寫資料庫，其他台為唯讀，另一種為多台資料庫可同時讀寫，架構的選擇上主要看你的程式架構而定，假設你的資料庫中Primary Key使用了大量的流水號(IDENTITY)又同時使用在有Master與Detailed的表格時，就只能使用第一種架構，因為當資料庫將資料同步到另一台電腦時，因為流水號的重取，會造成另一台資料庫中的流水號不同步，所以使用第一種架構把Insert、Update與Delete移到第一台然後再同步到其他台，就可以完成而不需修改太多的程式，而程式只要調整將Select的部份指定到其他台即可達到負載平衡(Load Balancing)，而在第二種的架構上可以使用SQL Server 2005 推出的新架構 Peer-to-Peer Transactional Replication即可達成，而且效能與同步化的時間差絕對讓你滿意，此二種架構的同步時間也是目前所有架構中，同步時間最短的，只需約1~3秒(實際情況示網路架構與資料量而定，但保證是所有架構中最快的一種)內即可完資料的同步。

Peer-to-Peer Transactional Replication架構圖：

參考文章：SQL Server 分散式架構 - 點對點交易式複寫 + NLB

2、Log Shipping (日記檔傳送)：這是三個方案之中最便宜也是最簡單的方案，這個主要將第一台的紀錄檔(Log) 傳送到另外一台，而另外一台可以提供唯讀(Standby)的方式讓使用者進行存取，而同步時間因為是透過SQL Agent的排程進行，所以可以設定在一分鐘以內，而這個方案也是我目前遇到的客戶中最多人使用的方案。

Log Shipping 架構圖：

參考文章：如何建立 Log Shipping

3、Database Mirroring (鏡射)：這是一個在SQL Server 2005推出的新功能，推出的時候真的有讓我非常的驚艷，很多人可能會想說我會不會寫錯了，Database Mirroring 的架構是屬於Active與Passive二種，那到底該如何進行負載平衡(Load Balancing)呢，這個就要透過資料庫快照(Database Snapshot)，透過這個方法就可以將硬體發揮到極限，才不會白白的浪費硬體，但是有一個非常大的問題需要解決，那就是當你要重作快照的時候，需要中斷使用者的需求，所以會有停止服務的時間差產生，這個問題想當然也是有方法可解，那就是透過二個資料庫快照進行切換，假設我已每10分鐘重作一次，10:00的時候使用第一個快照，10:10的時候使用第二個，這時候第一個就可以準備開始重作快照，等到約10:19分的時候就可以開始重作快照，然後再切換回第一個，以此類推，即可達到負載平衡(Load Balancing)。

參考文章：SQL Server - 如何建立 Database Mirroring

Database Mirroring 架構圖：

最後上面的三個架構並沒有一定的好與壞，主要還是視客戶的需求而定，大家可以嘗試評估看看自已的需求，再選擇最合適的架構即可。

相關文章：

關鍵字：Load Balancing、Database Mirroring、Replication、Log Shipping

2011年3月9日星期三

SQL Server IO 測試工具 - SQLIO的使用與不同Block Size下的效能測試

備註：修改日期 2016/01/07
由於SQLIO已不支援，所以建議可以參考使用DiskSpd，並請參考我的另一篇文章。
Windows上不同Block Size的效能測試 - 以AWS EC2為例

資料庫是一種使用I/O非常頻繁的軟體，所以硬碟就相對的非常的重要，當然SQL Server也不例外，但是我們買來的硬體雖然都有廠商或公開的測試值，但是如果硬體配備或環境不是完全相同時，該如何進行模擬測試呢，在我的上一篇 [SQL Server 儲存設備 (Storage) 最佳調整作業] – [Storage Top 10 Best Practices]，在文章中有介紹二個工具可以完成，所以本篇我們介紹一個由原廠(Microsoft)推出的一個模擬SQL Server I/O動作的軟體SQLIO。

在使用前我先來介紹一下使用的方法，首先開啟一個DOS視窗後，然後切換目錄到Program Files/SQLIO下，輸入SQLIO -h 先看一下說明的部份，我將幾個常用參數整理如下：

常用參數介紹：

參數	說明
-k<R\|W>	設定測試項目為讀取(R)或寫入(W)
-s	設定測試執行的時間
-d	設定同時運作的磁碟有那些
-o	指定I/O執行需求的深度
-b	指定I/O的Block Size
-F	讀入參數檔案
-f	I/O讀寫的方式(Random or Sequential)
-t	執行緒的數量

介紹完參數的介紹後，我們介紹一下使用的方法，在這邊我們利用一個範列介紹一下，在資料庫中我們的磁碟機格式化時，通常都使用預設值，但是在理論上如果Block Size切的越大時，其實是可以提高傳輸的速度與節省I/O的傳輸次數，所以我利用SQLIO的工具來比較一下其不同Block Size下的效能比較：

執行語法：c:\Program Files\SQLIO> SQLIO -kW -t4 -s120 -o8 -frandom -b64 -Fparam.txt
語法上只要修上列紅色字體的部份即可得到下圖的數據，建議時間的設定上不要太少，以免測試的數據不太穩定。

每秒I/O次數比：

每數傳輸率(MB)比：

在Windows的架構中Block Size可以設定如下表的部份，從以上的數據看來，的確透過Block Size的改變可以提高每秒的傳輸量與降低傳輸次數提高硬碟的壽命，但是天下沒有白吃的午餐，將Block Size設定到這麼大有什麼缺點呢，最大的缺點就是你設定Block Size成64K，如果你的檔案只有1k時，檔案還是佔64k，這也稱為內部斷裂，類似的檔案越多時，就越浪費空間，但是如果你是大型檔案，如DVD等檔案時，你就會發現檔案的讀寫會變快，我想在現在硬碟越來越大也越來越便宜的情況下，我想這應不是什麼大問題吧。

Windows - Block Size：

FAT32	NTFS
4K	512(Byte)
8K(Default)	1K
16K	2K
32K	4K(Default)
64K	8k
x	16K
x	32K
x	64K

SQLIO - 檔案下載位址：http://www.microsoft.com/downloads/en/details.aspx?familyid=9a8b005b-84e4-4f24-8d65-cb53442d9e19&displaylang=en

當然也可以另外透過微軟提供的效能監視器來觀察，我選出幾個比較重要的重點來說明：

物件類型	子物件	說明
Processor	Processor Time	[% Processor Time] 是處理器用在執行非閒置執行緒的經過時間百分比。這是測量處理器用在執行閒置執行緒的時間百分比，然後以 100% 減去該值所得。(每個處理器都有一個閒置執行緒，當沒有其他執行緒準備執行時，就會耗用週期。) 此計數器是處理器活動的主要指示，並會顯示抽樣間隔期間所觀察之忙碌時間的平均百分比。請注意，處理器是否閒置的帳戶處理計算，是以系統時鐘 (10ms) 的內部抽樣間隔來執行的。因此，在當今的快速處理器上，% Processor Time 可能會低估處理器使用率，因為處理器可能會花很多時間服務系統時鐘抽樣間隔之間的執行緒。以工作量為依據的計時器應用程式，就是其中一種較可能測量不準確的應用程式，因為計時器是在取樣之後收到訊號。
PhyicalDisk	% Disk Read Time % Disk Write Time	[% Disk Read Time] 是選取的磁碟機進行讀取操作/寫入服務所花費時間的百分比。
	Avg. Disk Bytes/Read Avg. Disk Bytes/Write	[Avg. Disk Bytes/Read] 是磁碟上的位元組在讀取/寫入過程中的平均轉移速率。
	Avg. Disk Queue Length	[Avg. Disk Queue Length] 是取樣時間內在所選取的磁碟佇列中的讀寫要求平均數目。

2011年3月4日星期五

SQL Server 2008 T-SQL 新語法介紹 - Merge (效能改善)

最近在重新修正我之前寫的一個基金(Data Mining)預測的程式時，由於資料來源都是我寫的程式自動到網路上捉取後並存入資料庫中，但是在存入前我都會檢查資料是否存在，如果存在就更新，不存在就新增，但由於資料量過大而且一來需要進行二個資料庫的動作，所以希望針對這部份進行改良，所以我就利用了SQL Server 2008推出的新語法Merge來進行改善。

T-SQL Merge語法主要是由SQL Server 2008所推出的新語法，可以判斷資料是否存在，動態選擇Insert或Update語法進行處理，進而同步二個表格之間的資料，而目前大部份的範例都是說明如何同步二個表格，但是根據我的需求，好像都行不通，後來終於讓我試出來了，而且原本二個程序的語法變成一個程序，實測下感覺速度快了很多，所以在此提供。

原本的語法，先查詢此基金的編號與日期是否存在，如果存在則更新基金的淨值，不存在的話就新增基金當天的淨值資料。



select 1 from fund_value

where fund_sn = 2062 and gdate_111 = '2008/12/30'

新增語法：



insert into fund_value(fund_sn, gdate, nvalue) 

values(2062, '2008/12/30',5.7602);

更新語法：



update fund_value

set nvalue = 5.701

where fund_sn = 2062 and gdate_111 = '2008/12/30'

利用SQL Server 2008 提供的Merge語法來將上面的三段語法進行整合，最後只要透過下列的語法即可完成：



MERGE INTO fund_value as t_fv  --Target

USING (select 2062 fund_sn, '2008/12/30' gdate_111) s_fv  --Source

ON t_fv.fund_sn = s_fv.fund_sn and t_fv.gdate_111 = s_fv.gdate_111

WHEN MATCHED THEN

 update set t_fv.nvalue = 5.76003

WHEN NOT MATCHED THEN

 insert(fund_sn, gdate, nvalue) values(2062, '2008/12/30',5.7602);

2011年3月3日星期四

SQL Server 2012(Code Name Denali) - 新T-SQL語法介紹 – Code Snippet Manager

經過前面介紹的分頁功能(Offset與Fetch)與Sequence之後，我再來介紹最後一個新增的功能，那就是Code Snippet Manager，在以往SQL Server 2000與2005的年代，如果你有需要撰寫SQL的時候，有任何的錯誤，只有在執行的時候才會知道是否正確，當時只能靠著其他廠商的工具來協助撰寫，但是在2008以後加入了Intellisense之後，讓你在撰寫可以更加的方便，在SQL Server 2012的版本中更是推出了Code Snippet Manager，相信如果有在寫程式的人因為有聽過或用過此功能，此功能可以讓你在撰寫SQL Function、Trigger或Stored Procedures時，可以透過此功能，直接產生樣版，透過樣版可以藉以減少撰寫上的時間，回想以前沒有這個功能的時候，我都要透過Create Stored Procedures as的功能來修修改改，這樣一樣可以減少我許多編輯上的時間。

基本上你可以透過Code Snippet Manager來看到你目前擁有的語法樣版有那些，當然你也可以透過此工具來將自已常用的樣版放置於此，建立自已的樣版庫。

另外在T-SQL程式碼作的同時也可樣可以點擊滑鼠右鍵後，再點選 [Insert Snippet] 來完成樣版的產生。

最後相信這個功能的增加，可以讓各位在撰寫T-SQL的時候更加的得心應手，而且在日後安裝時，也可以不用在安裝其他的工具配合寫作，可說是SQL Server使用者的一大福音。

其他相關網址：

2011年3月1日星期二

SQL Server 2012(Code Name Denali) - 新T-SQL語法介紹 – Sequence

上次介紹完SQL Server 2012 Denali的新功能(分頁功能)後，接下來我再介紹他的另一個功能，也就是Sequence，這個功能有點像是資料表欄位中的識別欄位，用來進行流水號的產生，但是以往流水號產生不能跨表格，也就是每個表格各有各的識別值，但是這樣一來如果N個表格需要產生唯一流水號時，只能透過程式的方式，將多個表格 union all 起來後，再找出最後一筆，如下列語法，實在不太方便：

select max(max_sn) mm_sn from
(
select max(sn) max_sn from table_a
union all
select max(sn) max_sn from table_b
union all
select max(sn) max_sn from table_c
) aa

Sequence的使用上非常的簡單，當你開啟 [SQL Server Management Studio] 之後，在左邊的[Object Explorer]裡面的 Programmability -> Sequences 如下圖所示，當需要產生或移除Sequence也可以透過此區來執行即可(當然也可以透過T-SQL的方式來產生)，產生的方式如下圖：

建立完成後，使用的方式必須配合NEXT VALUE FOR SequenceName來產生識別值，使用的語法如下：

1、直接產生識別值：
select next value for SequenceName

2、將識別值直接寫入表格中：
insert into dbo.Employee

values(next value for SequenceName, 0, 'Cary', 'Hsu');

3、大量新增識別值與表格的值到另一個表格中，此範例主要是透過AdventureWorks2008R2的資料庫中Person的資料表，其中將Type為EM的全部新增到我新增的表格中：

3-1、建立表格：
CREATE TABLE [dbo].[Employee](
[EMP_ID] [int] NOT NULL,
[Person_ID] [int] NOT NULL,

[First_Name] [nvarchar](20) NULL,

[Last_Name] [nvarchar](20) NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[EMP_ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

3-2、將符合的資料寫入到新增的[Employee]表格中：
insert into dbo.Employee
select next value for SequenceName, BusinessEntityID, FirstName, LastName
from Person.Person
where PersonType = 'EM'

另外系統也另外提供一個Stored Procedure來進行跳號的功能，名稱為 [sp_sequence_get_range]，使用方法如下：

DECLARE

@sequence_name nvarchar(100) = 'SequenceName',
@range_size int = 'Jump Value', --輸入你要跳號的數值 @range_first_value sql_variant,
@range_last_value sql_variant,
@range_cycle_count int,
@sequence_increment sql_variant,
@sequence_min_value sql_variant,
@sequence_max_value sql_variant;
EXEC sp_sequence_get_range
@sequence_name = @sequence_name,
@range_size = @range_size,
@range_first_value = @range_first_value OUTPUT,
@range_last_value = @range_last_value OUTPUT,
@range_cycle_count = @range_cycle_count OUTPUT,
@sequence_increment = @sequence_increment OUTPUT,

@sequence_min_value = @sequence_min_value OUTPUT,

@sequence_max_value = @sequence_max_value OUTPUT;
SELECT
@range_size AS [Range Size],

@range_first_value AS [Sequence First Value],

@range_last_value AS [Sequence Last Value],
@range_cycle_count AS [Range Cycle Count],

@sequence_increment AS [Sequence Increment],

@sequence_min_value AS [Sequence Min Value],
@sequence_max_value AS [Sequence Max Value];

最後如果你有需要重新設定識別值時候，你可以透過[Sequence Properties]的畫面來進行調整即可。

其他相關網址：

訂閱：文章 (Atom)

2011年3月10日 星期四