SSIS 2017横向扩展大师和SQL Server群集 [英] SSIS 2017 Scale-out Master and SQL Server Clustering

问题描述

亲爱的

在重新设计BI架构的框架内，我计划在主动/被动模式下集群SQL Server，而且我想部署一个数据集成横向扩展友好的体系结构因此我决定将SSIS Master放在
SQL Server机器上并在不同的机器上分散几个工作者，以便在SQL Server中编排与SQL Agent Jobs和分布式ETL相关的所有内容数据库引擎服务器

基本上这看起来像：

 [SQLSRV节点1] ------>监听器< ------- [SQLSRV节点2] 
 ^ 
 / \ 
 | 
 | 
 [SSIS工人] 

现在这让我思考，如果有的话是一个失败，SQL Server切换到故障转移机器节点（由于我们讨论的是数据仓库，所以我们会说它是READ-ONLY）：

• 故障发生在数据集成Windows之外：除非下一个计划的ETL即将启动，否则没有实际影响
• 发生故障 durin g数据集成：随后作为副本对流的影响是只读的（也介意SSISDB日志）

现在我的问题是：在集群部署中，您是否觉得这样一种公平的方法来划分Master vs Worker？

由于我们处于数据仓库解决方案中，我真的不热衷于启用读/写副本以最大限度地减少间隙，我也在努力减少机器数量（许可成本）因为我不会把SSIS Master放在自己的机器上。

我也考虑过拥有1 st SSIS工作者机器也包含SSIS主服务。

欢迎任何有关此事的想法，并提前感谢

解决方案

您好MyLoudWords，

以下链接对你有所帮助：

通过SQL Server故障转移群集实例扩展对高可用性的支持

最好的问候，

Mona Lv

Dear

Within the framework of re-designing our BI Architecture, I was planning on clustering SQL Server in an Active/Passive mode, moreover I would like to deploy a Data Integration scale-out friendly architecture therefore I decided to put the SSIS Master on the SQL Server machine and spread out few workers on separate machines, so that everything related to SQL Agent Jobs and distributed ETL is orchestrated within the SQL Server DB Engine server.

Basically this would look like:

[SQLSRV Node 1] ------> Listener <------- [SQLSRV Node 2]
                                                ^
				    	       / \
				    		|
				    		|
		      			[SSIS Worker]

Now this got me thinking, what if there is a failure and SQL Server switches to the fail-over machine node (which let's say would be READ-ONLY since we are talking about a data warehouse):

• Failure occurs outside of Data Integration Windows: No real impact unless the next scheduled ETL is about to start
• Failure occurs during Data Integration: Impact on the flows subsequently as the replica is Read-Only (mind SSISDB logs as well)

Now my question is: does this seem to you like a fair approach to segment Master vs Worker in a clustered deployment?

Since we are in a data warehousing solution, I am really not keen on enabling a Read/Write Replica to minimize gaps to the max and I am also trying to reduce the number of machines (licensing cost) as I wouldn't put the SSIS Master on its own machine.

I have also considered having the 1st SSIS Worker machine containing also the SSIS Master service.

Any thoughts on this matter are welcome, and thank you in advance

解决方案

Hi MyLoudWords,

The following link would be helpful for you:

Scale Out support for high availability via SQL Server failover cluster instance

Best Regards,

Mona Lv

这篇关于SSIS 2017横向扩展大师和SQL Server群集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！