賈斯汀：IT技術隨手筆記: 3月 2016

2016年3月28日星期一

[SSIS] 利用SSIS建置動態連接管理員來監控所有資料庫是否正常

說明：
本篇會使用到SSIS以下功能
1. Foreach迴圈容器
2. 動態連接管理員
3. SSIS錯誤處理 - ForceExecutionResult
4. Expressions：此篇並無詳述，只將語法直接列出。
5. 當會用Foreach迴圈容器去取值時，除了本篇監控資料庫STATUS之外，還有許多功能可用例如效能...
6. 完成以下程序，可將dtsx排入排程，定時監控資料庫，若有問題可以發出警告信或是簡訊

建立程序：
01. 建立DatabaseInfo資料庫，語法如下：
CREATE DATABASE [DatabaseInfo] ON PRIMARY
( NAME = N'DatabaseInfo', FILENAME = N'D:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DatabaseInfo.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'DatabaseInfo_log', FILENAME = N'D:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DatabaseInfo_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

02. 建立Instance_Info資料表並產生測試資料，語法如下：
USE [DatabaseInfo]
GO
CREATE TABLE [dbo].[Instance_Info](
        [I_Name] [varchar](30) NOT NULL,       --Instance Name
        [I_Address] [varchar](15) NOT NULL,   --IP Address
        [Version_N] [varchar](12) NOT NULL, --資料庫版本
        [Version_D] [varchar](50) NOT NULL, --資料庫版本(版號)
        [isMonitor] [varchar](2) NOT NULL,      --是否要監控(Y,N)
) ON [PRIMARY]
GO

USE [DatabaseInfo]
GO
INSERT [dbo].[Instance_Info] ([I_Name], [I_Address], [Version_N], [Version_D], [isMonitor]) VALUES (N'sqlcluster03', N'192.168.0.221', N'10.50.2500.0', N'SQL Server 2008 R2', N'Y')

INSERT [dbo].[Instance_Info] ([I_Name], [I_Address], [Version_N], [Version_D], [isMonitor]) VALUES (N'sqlcluster03-2', N'192.168.0.222', N'10.50.2500.0', N'SQL Server 2008 R2', N'Y')

03. [開始]>[所有程式]]>[Microsoft SQL Server 2008 R2]>在[SQL Server Business Intelligence Development Studio]上右鍵[以系統管理員身分執行]

04. 在[起始頁 - Microsoft Visual Studio]視窗中點依序展開[檔案]>[新增]>[專案]

05. 在[新增專案]視窗的[範本]區塊點選[Integation Services專案]，在下方區塊中[名稱]輸入[Dynamic_Connection]，[位置]輸入[D:\SSIS_SOURCE]後按下[確定]

06. 在[Dynamic_Connection - Microsoft Visual Studio]視窗中右邊[方案總管]>[Dynamic_Connection]>[SSIS封裝]>在[Package.dtsx]上右鍵[重新命名]輸入[Dynamic_Connection.dtsx]後按下[ENTER]

07. 在[Microsoft Visual Studio]視窗按下[是]

08. 在[Dynamic_Connection - Microsoft Visual Studio]視窗中的[Dynamic_Connection.dtsx]視窗>[控制流程]視窗右鍵[變數]，新增兩個變數如下
第一組
      名稱：connString
資料類型：String
            值：不用輸入

第二組
      名稱：instanceList
資料類型：Object
            值：預設值

09. 在畫面下方的[連接管理員]視窗中右鍵[新增OLE DB連接]

10. 在[設定OLE DB連接管理員]視窗中按[新增]

11. 在[連接管理員]視窗中的[伺服器名稱]輸入[192.168.0.221]，在[連接至資料庫]>[選取或輸入資料庫名稱]下拉選單找到[DatabaseInfo]後按下[確定]

12. 回到[設定OLE DB連接管理員]視窗中按[確定]

13. 在[Dynamic_Connection - Microsoft Visual Studio]視窗中左邊[工具箱]拉一個[執行SQL工作]到[Dynamic_Connection.dtsx]視窗的[控制流程]視窗中

14. 將[執行SQL工作]改名為[取得DB IP]

15. 雙擊[取得DB IP]

16. 在[執行SQL工作編輯器]視窗左邊點選[一般]，在右方[SQL 陳述式]下拉[Connect]找到[192.168.0.221.DatabaseInfo]

17. 在[SQL 陳述式]中的[SQLStatement]點選[...]

18. 在[輸入SQL查詢]視窗中輸入如下後按下[確定]
SELECT [I_Address] FROM [DatabaseInfo].[dbo].[Instance_Info] WHERE [isMonitor]='Y'

19. 在[結果集]的[ResultSet]欄位下拉點選[完整結果集]

20. 點選左側[結果集]後按下[加入]

21. 在[結果名稱]欄位輸入[0]，在[變數名稱]下拉點選[User::instanceList]後按下[確定]

22. 在[Dynamic_Connection - Microsoft Visual Studio]視窗中左邊[工具箱]拉一個[Foreach迴圈容器]到[Dynamic_Connection.dtsx]視窗的[控制流程]視窗中，再拉一條流程線從[取得DB IP]到[Foreach迴圈容器]

23. 雙擊[Foreach迴圈容器]

24. 在[Foreach迴圈編輯器]視窗中點選[集合]，在[Foreach迴圈編輯器]>[Enumerator]下拉選[Foreach ADO 列舉值]，[ADO物件來源變數]下拉選[User::instance_List]

25. 在[Foreach迴圈編輯器]視窗中點選[變數對應]，在[選起變數以對應至集合值]欄位中的[變數]下拉選[User::connString]，[索引]輸入[0]後按下[確定]

26. 在[Dynamic_Connection - Microsoft Visual Studio]視窗中左邊[工具箱]將[執行SQL工作]及[傳送郵件工作]拉到[Dynamic_Connection.dtsx]視窗的[控制流程]>[Foreach迴圈容器]中

27. 將[Foreach迴圈容器]中的[執行SQL工作]更名為[Check SQL Server Status]，將[傳送郵件工作]更名為[發送警告信]

28. 重複步驟9到步驟12，產生一個新的連接管理員名稱為[192.168.0.221.DatabaseInfo 1]，並將[192.168.0.221.DatabaseInfo 1]更名為[Dynamic_Connection]

29. 在畫面下方的[連接管理員]視窗中在[Dynamic_Connection]右鍵[屬性]

30. 在[Dynamic_Connection - Microsoft Visual Studio]視窗中右邊的[屬性]視窗找到[Expressions]點選[...]

31. 在[屬性運算式編輯器]視窗中的[屬性運算式]下拉[屬性]點選[ConnectionString]

32. 在[屬性運算式編輯器]視窗中的[屬性運算式]的[運算式]點選[...]

33. 在[運算式產生器]視窗中下方[運算式]視窗輸入如下值，輸入完後可以按一下最下方的[評估運算式]看看有沒有錯誤，若沒問題案下[確定]
"Data Source="+@[User::connString]+";Initial Catalog=master;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;"

34. 回到[屬性運算式編輯器]視窗按下[確定]

35. 回到[Dynamic_Connection - Microsoft Visual Studio]視窗>[控制流程]視窗，雙擊[Check SQL Server Status]

36. 在[執行SQL工作編輯器]視窗左邊點選[一般]，在右方[SQL 陳述式]下拉[Connect]找到[Dynamic_Connection]

37. 在[SQL 陳述式]中的[SQLStatement]點選[...]

38. 在[輸入SQL查詢]視窗中輸入如下後按下[確定]
SELECT @@SERVERNAME

39. 回到[執行SQL工作編輯器]視窗後按下[確定]

40. 回到[Dynamic_Connection - Microsoft Visual Studio]視窗，在[Foreach迴圈容器]中將[Check SQL Server Status]拉條流程線到[發送警告信]

41. 在[流程線]上右鍵選[失敗]

42. 雙擊[發送警告信]

43. 在[傳送郵件工作編輯器]視窗中點選[郵件]，在右邊視窗的[郵件]>[SmtpConnection]下拉選[新增連接]

44. 在[SMTP連接管理員編輯器]視窗中輸入郵件主機參數後按下[確定]，本例如下圖

45. 回到在[傳送郵件工作編輯器]視窗中左邊視窗[郵件]>右邊視窗[郵件]的[From]輸入寄件者EMAIL[sqlalert@dba.local]，[To]輸入收件者EMAIL[justin@dba.local]

46. 回到在[傳送郵件工作編輯器]視窗中左邊視窗[運算式]>點開右邊視窗的[Expressions]>[...]

47. 在[屬性運算式編輯器]視窗的[屬性運算式]>[屬性]下拉到[Subject]後點選[...]

48. 在[運算式產生器]視窗中下方[運算式]視窗輸入如下值，輸入完後可以按一下最下方的[評估運算式]看看有沒有錯誤，若沒問題案下[確定]
"錯誤=>SQL Server："+ @[User::connString]+"發生連線異常請立即檢查"

49. 回到[屬性運算式編輯器]視窗按下[確定]

50. 回到[傳送郵件工作編輯器]視窗後按[確定]

51. 回到[Dynamic_Connection - Microsoft Visual Studio]視窗>[控制流程]視窗在[Foreach迴圈編輯器]上右鍵[屬性]

52. 在[Dynamic_Connection - Microsoft Visual Studio]視窗右邊的[屬性]>[Foreach迴圈容器 For Each迴圈]>找到[ForceExecutionResult]屬性後下拉選[Success]

53. 回到[Dynamic_Connection - Microsoft Visual Studio]視窗完成設定

P.S. [發送警告信]出現警告圖示是因為郵件主旨沒設定，但已在[Expressions]有設定可忽略。
54. 在[Dynamic_Connection - Microsoft Visual Studio]視窗點選偵錯圖示

，下圖為所有DB皆OK的情形

55. 在DB中增加一筆不存在的資料庫資訊
USE [DatabaseInfo]
GO
INSERT [dbo].[Instance_Info] ([I_Name], [I_Address], [Version_N], [Version_D], [isMonitor]) VALUES (N'noDB', N'192.168.0.1', N'10.50.2500.0', N'SQL Server 2008 R2', N'Y')
GO
54. 在[Dynamic_Connection - Microsoft Visual Studio]視窗點選偵錯圖示

，下圖為有DB無法連線的情況便會寄送警告信給管理員

信件內容如下圖

2016年3月23日星期三

[SQL Server] 完成SQL Server 2008 R2 Cluster Active-Active Mode安裝後注意事項

>>效能

在A-A MODE時，兩台主機都正常時，不太會有效能問題，但是如果有一台發生異常或是要進行維護時就需要注意尤其是記憶體，以下提供做為參考：

1. 建立效能基準線，以了解每台主機處理器及記憶體的平均及最大使用量，確認當兩個Instance在同一台主機時效能還是足夠的。

2. 依據效能基準線規劃當兩個Instance在同一台主機時每個Instance所能獲得之最大記憶體用量
設定方式如下
--介面

--語法
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max server memory (MB)', N'2048'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO
3. 最大伺服器記憶體(max server memory (MB))此參數是立刻生效，但當發生切換時原本的INSTANCE可能已經把記憶體吃滿了這時切過來的INSTANCE就沒辦法獲得足夠記憶體，就算立刻透過語法或介面變更，也只能等原INSTANCE的記憶體慢慢釋放。
所以如果允許再切換後我會先變更兩台Instance的記憶體最大值，然後下上兩台SQL Serve，讓兩台效能能夠平均一些。

>>HA
在Windows Cluster服務中有個[慣用使用者]選項然後加上[容錯回復]可以讓當故障或是維護的主機復原時SQL Instance自動切換回去，這個是設定也需依據自己的環境來決定，在此僅提供設定方式：
環境介紹：

INSTANCE	慣用擁有者
MSSQLSERVER	C01.dba.local
MSSQLSERVER2	C02.dba.local

設定程序：
01. 在[伺服器管理員]視窗中依序展開[功能]>[容錯移轉叢集管理員]>[ps-db-sqlc03.dba.local]>[服務與應用程式]>[SQL Server(MSSQLSERVER)]上右鍵[內容]

02. 在[SQL Server(MSSQLSERVER)-內容]視窗中的[慣用擁有者]欄位勾選[C01]後按下[套用]

03. 在[SQL Server(MSSQLSERVER)-內容]視窗中的[容錯移轉]頁籤下的[容錯回復]欄位點選[允許容錯回復]後按下[確定]

04. 在[伺服器管理員]視窗中依序展開[功能]>[容錯移轉叢集管理員]>[ps-db-sqlc03.dba.local]>[服務與應用程式]>[SQL Server(MSSQLSERVER2)]上右鍵[內容]

05. 在[SQL Server(MSSQLSERVER2)-內容]視窗中的[慣用擁有者]欄位勾選[C02]後按下[套用]

06. 在[SQL Server(MSSQLSERVER2)-內容]視窗中的[容錯移轉]頁籤下的[容錯回復]欄位點選[允許容錯回復]後按下[確定]

[SQL Server] 安裝及設定SQL Server 2008 R2 Cluster Active-Active Mode

前言：
此篇教學為已完成SQL Server 2008 R2 Cluster Active-Passive Mode的架構下所撰寫，若無請參閱
安裝設定SQL Server 2008 R2 容錯移轉叢集 (一)
安裝設定SQL Server 2008 R2 容錯移轉叢集 (二)
安裝設定SQL Server 2008 R2 容錯移轉叢集 (三)

環境簡介
AD主機：dc01.dba.local
IP：192.168.0.10

Storage主機：c-storage.dba.local
IP：192.168.0.20

節點一：c01.dba.local
外部IP：192.168.0.110
內部IP：10.0.0.110

節點二：c02.dba.local
外部IP：192.168.0.120
內部IP：10.0.0.120

Windows Cluster Name：ps-db-sqlc03
Windows Cluster IP：192.168.0.210

MSDTC Name：ps-db-sqlc03Dtc
MSDTC IP：192.168.0.211

Instance01
SQLCluster Name：sqlcluster03
SQLCluster IP：192.168.0.221

需事先準備參數如下：
SQL Server網路名稱：sqlcluster03-2
具名執行個體：MSSQLSERVER2
SQLCluster IP：192.168.0.222

安裝設定流程
在Storage主機新增一組DISK
1. 在[Microsoft iSCSI軟體目標] > [iSCSI目標]上右鍵[建立iSCSI目標]

2. 在[歡迎使用iSCSI目標建立精靈]視窗按[下一步]

3. 在[iSCSI目標識別]視窗中的[iSCSI目標名稱]輸入[Instance-Disk1]後按[下一步]，此名稱可自訂。

4. 在[iSCSI啟動器識別元]視窗中按[進階]

5. 在[進階識別元]視窗中按[新增]

6. 在[新增/編輯識別元]視窗中按[瀏覽]

7. 在[新增iSCSI啟動器]視窗中的[iSCSI啟動器]欄位全選後按[確定]

8. 回到[新增/編輯識別元]視窗中按[確定]

9. 在[Microsoft iSCSI軟體目標]視窗按下[是]

10. 在[進階識別元]視窗按下[確定]

11. 在[iSCSI啟動識別元]視窗按[下一步]

12. 在[完成iSCSI目標建立精靈]視窗按下[完成]

13. 在[Microsoft iSCSI軟體目標] >[Instance-Disk1]上右鍵[建立iSCSI目標的虛擬磁碟]

14. 在[歡迎使用虛擬磁碟建立精靈]視窗中按[下一步]

15. 在[檔案]視窗中的[檔案]欄位中指定存放路徑如下圖，輸入完後按[下一步]

16. 在[大小]視窗中的[虛擬磁碟大小]欄位輸入所需之容量後按[下一步]

17. 在[描述]視窗中按[下一步]

18. 在[完成虛擬磁碟建立精靈]視窗中按下[完成]

在每個節點上執行步驟19到步驟22，掛載新的磁碟機
19. 在[iSCSI啟動器 - 內容]視窗中的[目標]頁籤的[目標]欄位輸入STORAGE主機IP[192.168.020]後按[快速連線]

20. 在[快速連線]視窗中[探索到的目標]欄位點選先增加的連線如下圖後按下[連線]

21. 在[快速連線]視窗中按下[完成]

22. 回到[iSCSI啟動器 - 內容]視窗按下[確定]

將新增加的磁碟機掛載到Windows Cluster
註：在執行步驟23前請先確認目前cluster service是在哪一台機器上，以下圖為例是在C01.dba.local主機上

23. 在C01.dba.local主機上的[伺服器管理員]視窗中點選[存放]>[磁碟管理]，找到新增加的磁碟後右鍵[連線]

24. 在[伺服器管理員]視窗中點選[存放]>[磁碟管理]，再將剛剛連線的磁碟後右鍵[初始化磁碟]

25. 在[初始化磁碟]視窗中按下[確定]

26. 在[伺服器管理員]視窗中點選[存放]>[磁碟管理]，在剛剛初始化的磁碟上右鍵[新增簡單磁碟區]

27. 在[歡迎使用新增簡單磁碟區精靈]視窗中按[下一步]

28. 在[指定磁碟區大小]視窗中按[下一步]

29. 在[指派磁碟機代號或路徑]視窗中，依據需求指定磁碟機代號後按[下一步]

30. 在[磁碟分割格式化]視窗中按[下一步]

31. 在[完成新增簡單磁碟區精靈]視窗中按下[完成]

32. 在[伺服器管理員]視窗中依序展開[功能]>[容錯移轉叢集管理員]>[ps-db-sqlc03.dba.local]>在[存放裝置]上右鍵[新增磁碟]

33. 在[選取您要新增的磁碟]視窗中按[確定]

34. 便可在[存放裝置]中看到新加入的磁碟，如下圖所示

在C01.dba.local安裝第二個SQL CLUSTER INSTANCE
35. 在[SQL Server安裝中心]視窗中依序點選[安裝]>[新的SQL Server容錯移轉叢集安裝]

36. 在[安裝程式支援規則]視窗中按[確定]

37. 在[安裝程式支援檔案]視窗中按[安裝]

38. 在[安裝程式支援規則]視窗按[下一步]。註：其中有兩個關於網路警告可忽略。

39. 在[產品金鑰]視窗中按[下一步]

40. 在[授權條款]視窗中勾選[我接受授權條款]後按[下一步]

41. 在[特徵選取]視窗中按下[全選]後再按[下一步]

42. 在[執行個體組態]視窗中需輸入兩個參數可自訂，如下圖
SQL Server網路名稱：sqlcluster03-2
具名執行個體：MSSQLSERVER2

43. 在[磁碟空間需求]視窗中按[下一步]

44. 在[叢集資源群組]視窗中按[下一步]

45. 在[叢集資源選取]視窗中按[下一步]

46. 在[叢集網路組態]視窗中，取修勾選[DHCP]，在[位置]輸入[192.168.0.222]後按[下一步]

47. 在[叢集安全性原則]視窗中按[下一步]

48. 在[伺服器組態]視窗中點選[所有SQL Server服務都使用相同的帳戶]

49. 在[所有SQL Server 2008 R2服務都使用相同的帳戶]視窗中輸入服務的帳號及密碼後按[確定]

50. 回到[伺服器組態]視窗後按[下一步]

51. 在[資料庫引擎組態]視窗中點選[混合模式]，並輸入密碼後在按下[加入]

52. 在[選取使用者，電腦或群組]視窗中的[輸入物件名稱來選取]欄位輸入所需帳號如[dbadmin]後按下[檢查名稱]

53. 再按下[確定]

54. 回到[資料庫引擎組態]視窗按[下一步]

55. 在[Analysis Services組態]視窗中按下[加入]

56. 在[選取使用者，電腦或群組]視窗中的[輸入物件名稱來選取]欄位輸入所需帳號如[dbadmin]後按下[檢查名稱]

57. 再按下[確定]

58. 回到[Analysis Services組態]視窗後按[下一步]

59. 在[Reporting Services組態]視窗中按[下一步]

60. 在[錯誤報告]視窗中按[下一步]

61. 在[叢集安裝規則]視窗按[下一步]

62. 在[準備安裝]視窗中按[安裝]

63. 在[完成]視窗中按下[關閉]

在C02.dba.local主機
64. 在[SQL Server安裝中心]視窗中依序點選[安裝]>[將節點加入到SQL Server容錯移轉叢集]

65. 在[安裝程式支援規則]視窗中按[確定]

66. 在[安裝程式支援檔案]視窗中按[安裝]

67. 在[安裝程式支援規則]視窗中按[下一步]

68. 在[產品金鑰]視窗中按[下一步]

69. 在[授權條款]視窗中勾選[我接受授權條款]後按[下一步]

70. 在[叢集節點組態]視窗中按[下一步]

71.在[服務帳戶]視窗中[密碼]欄位輸入對應的密碼後按[下一步]

72. 在[錯誤報告]視窗中按[下一步]

73. 在[加入節點規則]視窗中按[下一步]

74. 在[準備加入節點]視窗按下[安裝]

75. 在[完成]視窗中按下[關閉]

76. 兩個節點都安裝完成後在[伺服器管理員]視窗中依序展開[功能]>[容錯移轉叢集管理員]>[ps-db-sqlc03.dba.local]>[服務與應用程式]下可以看到兩個執行個體如下圖

變更MSSQLSERVER2的Service Port，需在執行第二個Instance的主機上設定
77. 開啟[SQL Server組態管理員]後在[Sql Server Configuration Manager]視窗中依序展開[SQL Server組態管理員]>[SQL Server網路組態]>[MSSQLSERVER2的通訊協定]，在右邊視窗找到[TCP/IP]右鍵[內容]