五餅二魚工作室

之前在介紹 SQL Server 2012 的時候，多半都會介紹到 SQL Server 新的 HA/DR 的解決方案 AlwaysON，而介紹到 SSIS 的時候，自然也不會遺漏到專案部署模式。然而，是否有機會可以讓 SSISDB 搭配 AlwaysON 呢 ? 因此做了一個簡單的試驗來測試一下這兩個的協同運作。

首先我先預備好兩台 Hyper-V 的 VM 來做測試環境，名稱分別是 AlwaysON1 和 AlwaysON2，這兩個 VM 都是安裝 Windows Server 2012 R2 Standard 版本的作業系統，並且也都預先安裝「容錯移轉叢集」，接著在這些環境上安裝 SQL Server 2012 Enterprise SP2 的版本，安裝完成之後，並且透過「SQL Server 組態管理員」，針對所安裝的 SQL Server 服務設定啟用「AlwaysON 高可用性」，這樣基本上就先完成了我們 Lab 所需要的環境。

環境預備

首先我們先在 AlwaysON1 這台上設定，啟用 SSMS 之後，選擇 Instance 下面的「Intergration Services 目錄」節點，按下滑鼠右鍵選擇「建立目錄」，這裡要記住這個 SSISDB 的「密碼」，後續這個密碼會非常的重要。

接著我們在另外一台 AlwaysON2 上也是使用相同的方式建立 SSISDB

建立好之後我們就可以把 SSISDB 的資料庫給「刪除」了。什麼 ?! 有沒有搞錯啊，為什麼要建立後又刪除呢 ? 我們主要是透過這樣的方式，讓這樣的程序幫我們去建立一些 SSIS 要使用到的 Stored Procedure 、帳號 等設定，因為後續我們會把 AlwaysON1 的 SSISDB 複製過來，因此在 AlwaysON2 這台上面的 SSISDB 資料庫就沒有存在的必要了。

因為等一下這兩台 VM 會需要彼此溝通，因此如果沒有關閉 Windows 內建的防火牆，可能會彼此之間無法連通。因此我們選擇使用在「命令列模式」下，使用 netsh 來進行相關設定，讓兩台電腦的 TCP 1433 和 TCP 5022 這兩個 Port 允許開通。

@echo Enabling SQLServer default instance port 1433
netsh advfirewall firewall add rule name="SQL Server" dir=in action=allow protocol=TCP localport=1433
@echo Enabling AlwaysON port 5022
netsh advfirewall firewall add rule name="AlwaysON" dir=in action=allow protocol=TCP localport=5022

設定 AlwaysON 高可用性

完成的 SSISDB 的建立和前置處理，這個時候我們就可以在 AlwaysON1 的這台上，利用 SSMS 選擇「AlwaysON 高可用性」，透過「新增可用性群組精靈」來進行相關設定，前面幾個步驟主是要設定「可用性群組名稱」和選擇資料庫，在這個案例因為是要配合 SSIS，因此選擇 SSISDB 的資料庫。

接著我們要把另外一個 SQL Server 透過「加入複本」 的方式加入，並且完成「接聽程式」的設定

接著就要選擇同步資料庫，為了方便我就直接在本機上分享一個目錄，讓 SQL Server 將備份資料庫放置在該分享目錄下，接著沒有問題 SQL Server 就會開始進行相關處理了。

完成上述相關步驟的設定，接著回到 SSMS 內，我們就可以看到 AlwaysON 相關設定都已經完成了。

測試案例

接下來我們就要來進行測試，看看是否可以順利「容錯移轉」，因此為了要方便測試，我先在 AlwayON1 上建立一個資料庫要用來存放等一下封裝執行的 LOG 資料表

-- 建立資料庫
CREATE DATABASE [DEMO]
GO

USE [DEMO]
GO

-- 建立 Log 紀錄資料表
CREATE TABLE [dbo].[T](
	[T1] [datetime] NOT NULL DEFAULT GETDATE(),
	[T2] [nvarchar](50) NULL,
	[T3] [nvarchar](50) NULL
) ON [PRIMARY]

GO

接著就開啟 SSDT 來建立一個封裝，因為只是要測試封裝能否正常執行，因此這個封裝內只有一個簡單的 「執行 SQL 工作」，設定使用 ADO.Net 連線存取 DEMO 的資料庫。

裡面放一個 T-SQL 的 INSERT 指令“ INSERT INTO T(T2,T3) VALUES (@Server , ‘SSIS’ )“

並且指定使用「System::MachineName」當作參數傳入上述的 SQL 指令，這樣我們就可以知道封裝是由哪一台 SQL Server 所執行的了。

完成封裝的設計，我們就要把這個封裝給上傳到 SSISDB 上囉，這部份我就不多做說明，有需要了解 SSIS 的專案部署模式運作的朋友，可以參考另外一篇文章「小試專案部署模式( Project Deployment Model ) 和參數 ( Parameter )使用」

完成上傳之後，我們就可以在 SSMS 內來測試一下囉

Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'A', @project_name=N'DEMO', @use32bitruntime=False, @reference_id=Null
Select @execution_id
EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO

封裝可以正常執行，並且我們透過指令查看我們所放的 LOG 檔案，也可以看到有資料存入，上面記錄是透過 AlwaysON1 去執行的。

進行容錯移轉

前面的測試都還在原本的 AlwaysON1 的上面，因此我們測試看看，要是將 SSISDB 移轉到另外一台 AlwaysON2 上，是否還可以正常執行呢 ? 我們透過 SSMS 上選擇 Instance 下的「AlwaysON 高可用性」→「可用性群組」→「SSISDBAG」，按下滑鼠右鍵選擇「容錯移轉」

選擇要移到主要複本 AlwaysON2 上，透過 Wizard 的方式幾乎就是下一步就可以完成移轉了

那此時我們在下指令看看是否可以正常執行封裝，跟前面執行封裝的指令相同，我只是在多一行顯示目前是在哪一台 Server 上面，但會發現會有個 15581 的錯誤訊息，表示雖然資料庫有移轉過來，但是沒有開啟主要金鑰，因此無法正常執行。

或者你不是透過 T-SQL，而是透過 SSMS 上在封裝上選擇「執行」，也會看到類似的錯誤訊息

因此在 AlwaysON2 上，我們要使用以下的指令，來開啟主要金鑰，整篇文章最重要的就是這段指令了，當我們使用該指令之後，就可以正常執行封裝了，其中在第四列的地方，這個密碼就是我們一開始在建立 SSISDB 時候，所使用的密碼。

USE [SSISDB]
GO
PRINT 'OPEN MASTER KEY';
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'P@ssw0rd123' -- Password used when creating SSISDB
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
GO

而我們透過我們的紀錄檔案，可以看到封裝正常執行，並且這個時候執行封裝已經換成 AlwaysON2 的機器了

自動處理

在透過上述的過程中，我們發現只要透過 AlwaysON 去進行容錯移轉的話，則我們必須要使用 OPEN MASTER KEY 的方式重新設定，但有些時候移轉來移轉去，怎麼會記得之前有沒有下過指令呢 ? 在跟 Terry 討論的過程中，他提供了另外一個方式，就是透過 Alert 的方式，我們透過攔截錯誤代號 1480 ，就可以知道有發生容錯移轉，因此首先我們先建立一個 JOB ，名稱是「AlwaysON FailOver JOB」

裡面會執行 T-SQL 指令如下：

DECLARE @Role int ;
SELECT @Role=[role]
    FROM sys.dm_hadr_availability_replica_states
    WHERE is_local = 1

IF @Role=1
BEGIN
	PRINT ''OPEN MASTER KEY'';
	OPEN MASTER KEY DECRYPTION BY PASSWORD = ''P@ssw0rd123'' -- Password used when creating SSISDB
	ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
END

接著建立一個名稱叫做「AlwaysON FailOver」的警示，主要會針對「錯誤號碼1480」會發生，並且執行前面一個步驟所建立的「AlwaysON FailOver JOB」

這樣切換來切換過去，就不用再去針對 SSISDB 去開啟金鑰了。如果大家對於這個部份有興趣，也可以參考 MSDN 上另外一篇文章「SSIS with AlwaysOn」，上面有另外提到他再用一個加密，去把密碼的部分編碼，避免讓有心人士一眼就看到密碼，只是我個人覺得似乎沒有那個必要，因為如果真的能看到那個部份的人員，他也可以透過對應的指令取得密碼，因此我就沒有參考那個部份去實做了。