數據庫引擎和 SSISDB 架建構議

February 11, 2020

我正在尋求一些涉及 SQL Server 2017 的數據庫引擎和集成服務組件的架建構議。我們有一個現有的 SQL Server 環境，該環境需要升級，並且過去一直以不太理想的方式使用。隨著即將升級到新的 SQL Server 環境，作為系統管理員，我想利用這個機會重新思考現有架構，以提供更強大的靈活環境。
目前環境
在 2 節點 Windows 故障轉移群集上執行的 SQL Server 2012 Enterprise 實例。數據庫引擎是集群的，DTS 包儲存在集群卷中，這些包通過 SQL Server 代理作為作業執行。他們不使用顯式集成服務組件
問題
管理數據倉庫和創建/管理 DTS 包的員工傳統上堅持通過 RDP 直接在集群伺服器節點上工作。這會給負責伺服器本身的系統管理員帶來訪問問題。數據人員還需要各種軟體工具，這些工具會隨著時間的推移導致持久的磁碟空間問題。此外，這些軟體工具中的許多都不是集群感知的，並且只安裝在一個節點上，從一開始就實際上給我們留下了一個殘缺的集群。結果是，無論何時發生計劃內或計劃外的故障轉移，都會面臨盡快讓原始主節點恢復聯機的壓力，因為大多數作業都失敗了，因為它們依賴於不支持集群的本地軟體。
建議的解決方案
將數據庫引擎單獨安裝在自己的集群上，不允許任何員工進行 RDP 訪問。他們將只收到 SQL Server 集群實例終結點 URL。在單獨的伺服器上安裝集成服務組件，並建議他們開始使用 SSISDB 目錄來儲存他們的包而不是文件系統。我的理解是使用 SSISDB 有很多優點。
問題
這只會將 RDP 訪問問題轉移到另一台伺服器。由於數據人員在包上實時協作並依賴所有相同的軟體工具，他們認為他們必須直接訪問伺服器才能提供共享的相同環境。我的信念是，必須有一種更現代的設計方法，他們可以在本地工作站上使用他們的開發工具，並使用 SSMS 連接到 SSIS，在那裡將發布和安排/執行作業。軟體包依賴的任何軟體（ftp、ssh、perl、python 等）都將由 SSIS 上的伺服器管理員安裝和管理。由於包將儲存在 SSISDB 中，因此不再需要傳統上存放封包件的共享儲存，這應該消除對任何直接 RDP 訪問的需要。
現在 SSISDB 成為單點故障，因為包不再像傳統那樣成為故障轉移集群的一部分。我將如何在這個組件中建構高可用性？我讀過 SSIS Scale Out 功能，但這似乎更多是關於平衡負載而不是容錯。我已經開始閱讀有關 AlwaysOn 可用性組的資訊，這可能是一個解決方案嗎？
非常感謝任何和所有設計建議。謝謝。

這是一種文化變革，也是一種流程變革，因此獲得管理層的支持將很重要，並最終保護您免受某種程度的技術阻力，並幫助宣傳這一戰略轉變將如何改善每個人的生活。
在我們進入 SSIS 目錄的具體案例之前，讓我們看一下您的選項的概述，以便更好地為您提供知識，以溝通開發人員應該更改其係統的原因：
文件系統
單個包儲存在任何文件夾、任何位置。
沒有安全性，任何使用者或程序都可以在沒有先驗知識的情況下修改文件，並且本機不存在任何程序來防止這種情況發生。
不屬於正常備份計劃的一部分。系統快照不是可行的備份，也不能防止可能導致無法挽回的損壞的意外修改。
必須手動組織包，並且不能在物理位置和/或文件之外對包進行分組
強制使用配置文件來參數化值
SSIS 包商店
預設情況下，包儲存在特定文件夾“C:\Program Files\Microsoft SQL Server< SQL Version Compiled>\DTS\Packages”。可以移動。
有限的安全性；可以通過提升的權限進行修改
不屬於系統快照之外的任何備份策略（同樣，不是有效的備份解決方案）
可以在文件夾下組織包
無法使用角色來限制對來自 SSIS 包儲存的包的訪問
SQL 伺服器 (msdb)
包儲存在 msdb 中，可以導出到文件共享中。
安全性與對 msdb 的訪問有關。
系統 msdb 是有效備份解決方案的一部分。
無法添加到 AlwaysOn 解決方案
文件夾可用於分類
可以使用角色來管理 2016+ 中避免使用系統管理員的權限。集成服務角色（SSIS 服務） - MS Docs
SSISDB 目錄
打包儲存在使用證書加密的 SSISDB 中
單獨的權限和角色進一步加強了安全性（見上面的連結）。
SSISDB 是有效備份解決方案的一部分
SQL Server 2016+ 增加了對 AlwaysOn 的支持！SQL Server 2016 HA 系列第 2 部分 – SSIS 和可用性組 – Microsoft 部落格
成熟的管理層次結構，包括參數、環境變數。
提供與 Visual Studio 的完全集成
SQL 代理作業不再直接使用配置文件引用或實際密碼（希望沒有人這樣做）從 SSMS 腳本中提取出來。
最終，您可以決定哪種路徑適合您的組織。SSIS 目錄是幾種方法之一，因此讓您的管理層支持您進行這項工作將大大有助於進行更改。在描述了一個部署範例方案之後，本文的其餘部分將重點介紹一些 Visual Studio 管理和 SSIS 目錄提示。
一個範例方案可能如下：
所有新的努力都將使用$$ Visual Studio $$2017 或 2015 用於向後兼容性和 SQL Server Data Tools -（下載 SQL Server Data Tools (SSDT) - docs.microsoft.com）。o 請注意，在 VS 2019 中，他們將此添加為市場組件）
項目將保存在程式碼管理文件夾（GitHub 文件夾）中，以便對工作進行版本控制，或者他們將在 DEV 環境中使用 SSIS 目錄，但理想情況下使用 GitHub 儲存庫。
軟體包組件首先安裝在 Dev/Cert 環境的每個節點上。企業 DBA 協助在單個文件共享中收集這些下載，以便於訪問/傳輸到目標集群伺服器，並確保它們位於每個目標伺服器上。
獎金部分：
Visual Studio 管理提示：
您的包取決於部署版本（必須與 SQL Server 實例的集成服務匹配）
32 位/64 位配置取決於使用的組件。
為了使用參數，您必須使用包“參數”（不是變數）或項目參數。您可以使用這些環境來控制所使用的連接。
您可以連接並部署到您的目標位置（不必使用 SSISDB）

SSISDB 目錄管理提示：
您可以通過點擊按鈕來還原版本以撤消負面更改，甚至可以向前還原！

您可以從 SSMS 管理項目參數、驗證、控製版本。不需要VS。
從這裡配置環境參數：
與 SQL Server 代理作業不同，密碼在 SSISDB 數據庫中受到安全保護。
例子：
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'&lt;name of step&gt;', 
       @step_id=1, 
       @cmdexec_success_code=0, 
       @on_success_action=3, 
       @on_success_step_id=0, 
       @on_fail_action=2, 
       @on_fail_step_id=0, 
       @retry_attempts=0, 
       @retry_interval=0, 
       @os_run_priority=0, @subsystem=N'SSIS', 
       @command=N'/ISSERVER "\"\SSISDB\Replication\SSISDB_Replication\SSISDB_Replication.dtsx\"" /SERVER &lt;ServerName&gt; /ENVREFERENCE 3 /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E', 
       @database_name=N'master', 
       @flags=0
選擇環境集後，每個 SQL Server 代理作業都會使用這些參數。
選擇包源 SSIS 目錄。
為變數設置環境。
大多數情況下，參數會自動設置。
檢查 SSMS 中實際出了什麼問題，而無需借助 RDPing 進入您的伺服器。
查看使用真實統計數據的執行之間的性能！
我錯過了什麼嗎？隨意將此作為社區答案。