In this blog post, we will explore the configuration options for the Scale Out Master and Scale Out Worker in SQL Server Integration Services (SSIS) Scale Out feature introduced in SQL Server 2017.
Scale Out Master Configuration
The Scale Out Master manages the Scale Out system through the SSISDB catalog and the Scale Out Master service. The Scale Out Master service, named SSISScaleOutMaster140, is installed as a Windows service and runs under the NT Service\SSISScaleOutMaster140 account by default.
To configure the Scale Out Master, you can modify the MasterSettings.config file located at C:\Program Files\Microsoft SQL Server\140\DTS\Binn\MasterSettings.config. This file contains various settings such as the network port number, SSL certificate thumbprint, SQL Server instance name, and intervals for cleaning up completed execution jobs and dealing with expired execution jobs.
The Scale Out Master service log can be found at C:\Users\SSISScaleOutMaster140\AppData\Local\SSIS\Cluster\Master, and the master heartbeat log is located at C:\Users\SSISScaleOutMaster140\AppData\Local\SSIS\ScaleOut\Master.
Scale Out Worker Configuration
The Scale Out Worker runs a SQL Server Integration Services Scale Out Worker service to pull execution tasks from the Scale Out Master and execute the packages locally. The Scale Out Worker service is located at C:\Program Files\Microsoft SQL Server\140\DTS\Binn.
To configure the Scale Out Worker, you can modify the WorkerSettings.config file located at C:\Program Files\Microsoft SQL Server\140\DTS\Binn\WorkerSettings.config. This file contains settings such as the display name, description, Scale Out Master endpoint, SSL certificate thumbprints, task intervals, CPU and memory limits, and maximum number of tasks.
The task log is created under the C:\Users\SSISScaleOutWorker140\AppData\Local\SSIS\ScaleOut\Tasks folder, and the execution log cache folder is located at C:\Users\SSISScaleOutWorker140\AppData\Local\SSIS\ScaleOut\Agent\ELogCache.
Scale Out SQL Views and Stored Procedures
There are several views and stored procedures available in the SSISDB database to manage the Integration Services Scale Out Master and Worker.
The catalog.master_properties view displays the properties of the Integration Services Scale Out Master. The catalog.worker_agents view provides information about each registered SSISworker with the ScaleoutMaster.
The catalog.disable_worker_agent stored procedure is used to disable a ScaleoutWorker, and the catalog.enable_worker_agent stored procedure is used to enable a ScaleoutWorker.
These views and stored procedures require specific permissions in the SSISDB database, such as membership to the ssis_admin database role and the sysadmin server role.
By understanding and configuring these settings, you can effectively utilize the SQL Server Integration Services Scale Out feature in SQL Server 2017.