SQL Server 2017 SSIS Scale Out Configuration

Problem

In my previous tip SQL Server vNext (SQL Server 2017) SSIS Scale Out Feature, we explored the new Integration Services Scale Out features. In this tip, we will see how to configure items for both the ScaleOutMaster and ScaleOutWorker along with some important views and stored procedures related to the SSISDB database.

Solution

As we have seen in the previous tip, the SQL Server 2017 ScaleOutMaster and ScaleOutWorker is newly introduced with SQL Server 2017. We have seen how to install it with the SQL installer and now in this tip we will go through some of the configuration options for both the Scale Out Master and Scale Out Worker.

Integration Services (SSIS) Scale Out Master

We have seen in the previous tip that the Scale Out Master manages the Scale Out system through the SSISDB catalog and the Scale Out Master service. When we install the Scale Out Master service it installs a Windows service SSISScaleOutMaster140 (Display Name - SQL Server Integration Services Scale Out Master 14.0) and by default it runs under the NT Service\SSISScaleOutMaster140 account. This service is responsible for the communication with Scale Out Workers. It exchanges the status of package executions with Scale Out Workers through HTTPS and operates on the data in the SSISDB.

$NT Service\SSISScaleOutMaster140 account running the SSIS Scale Out Service$

The Scale Out Master service can be configured using the C:\Program Files\Microsoft SQL Server\140\DTS\Binn\MasterSettings.config file.

$Scale Out Master service can be configured using the C:\Program Files\Microsoft SQL Server\140\DTS\Binn\MasterSettings.config file$

Go to the path and open the MasterSettings.config file as shown below.

Below are the descriptions for each section:

PortNumber: The network port number used to communicate with a Scale Out Worker. By default, it is 8391.
SSLCertThumbprint: The thumbprint of the SSL certificate used to protect the communication with a Scale Out Worker. It is also defined during the installation. If we create a new certificate during installation it is automatically created.
InstanceName: The name of the SQL Server instance that contains the SSISDB catalog. SSIS master service is also installed on this instance.
CleanupCompletedJobsIntervalInMs: It is the interval for cleaning up completed execution jobs, in milliseconds. The default value is 43200000 ms i.e. 12 hours.
DealWithExpiredTasksIntervalInMs: It is the interval for dealing with expired execution jobs, in milliseconds. The default value is 300000 ms i.e. 300 seconds
MasterHeartbeatIntervalInMs: It is the interval for the Scale Out Master heartbeat, in milliseconds. This specifies the interval that Scale Out Master updates its online status in the SSISDB catalog. The default value is 30000 ms i.e. 30 seconds.

The scale out service log is located at the C:\Users\SSISScaleOutMaster140\AppData\Local\SSIS\Cluster\Master and we can also get the master heartbeat log at the C:\Users\SSISScaleOutMaster140\AppData\Local\SSIS\ScaleOut\Master location.

We can also get the ScaleOutMaster properties using the below view.

/****** Script for view ScaleOutMaster Properties ******/
SELECT  [property_name]
      ,[property_value]
  FROM [SSISDB].[catalog].[master_properties]

Integration Services (SSIS) Scale Out Worker

Scale Out Worker runs a SQL Server Integration Services Scale Out Worker service to pull execution tasks from the Scale Out Master and executes the packages locally with ISServerExec.exe.

This service is located at C:\Program Files\Microsoft SQL Server\140\DTS\Binn.

Scale Out Master executes the packages locally with ISServerExec.exe

The configuration file for the Scale Out Worker is located at C:\Program Files\Microsoft SQL Server\140\DTS\Binn\WorkerSettings.config.

The WorkerSettings.config file looks like this:

Below are the contents for the WorkerSettings.config:

DisplayName: This is the machine name of the Scaleout Worker.
Description: This is for the description of the Scale Out Worker services, by default it is blank.
MasterEndpoint: This is the Scale Out Master end point which we defined during the installation of the Scale Out Worker.
MasterHttpsCertThumbprint: This is the thumbprint of the client SSL certificate used to authenticate the Scale Out Master. It was also defined during the installation process.
WorkerHttpsCertThumbprint: The thumbprint of the certificate for Scale Out Master used to authenticate the Scale Out Worker.
StoreLocation: It defines the store location of the worker certificate. The default location is localmachine.

store location of the worker certificate

AgentHeartbeatInterval: This is the interval for the Scale Out Worker heartbeat. Default value is 00:01:00.
TaskHeartbeatInterval: This is the interval of the Scale Out Worker reporting task state. Default value is 00:00:10.
HeartbeatErrorTollerance: After this time period from the last successful task heartbeat, the task is terminated if an error response of heartbeat is received. Default value is 00:10:00.
TaskRequestMaxCPU: This value defines the upper limit of the CPU for the Scale Out Worker to request tasks. Default value is 70% CPU.
TaskRequestMinMemory: The lower limit of memory in MB for the Scale Out Worker to request tasks. Default value 100 MB.
MaxTaskCount: The max number of tasks the Scale Out Worker can hold. Default value is 10.
TasksRootFolder: The task log is created under the C:\Users\SSISScaleOutWorker140\AppData\Local\SSIS\ScaleOut\Tasks. If we have changed the service account for the worker service, it will be created under the respective account folder.
TaskLogLevel: The task log level of the Scale Out Worker. (Verbose 0x01, Information 0x02, Warning 0x04, Error 0x08, Progress 0x10, CriticalError 0x20, Audit 0x40).
TaskLogSegment: It defines the time span of the task log file, after this time a new log file will be created. Default value is 00:00:00.
TaskLogEnabled By default, task logging is enabled, however if we want to disable this, set this to False.
ExecutionLogCacheFolder: It defines the folder that will be used as the cache package execution log. The default location is C:\Users\SSISScaleOutWorker140\AppData\Local\SSIS\ScaleOut\Agent\ELogCache, however if we have changed the service account for the SSISworker, this path will be like <driver>:\Users\[account]\AppData\Local\SSIS\Cluster\Agent\ELogCache folder.</driver>
ExecutionLogMaxBufferLogCount: It is the max number of execution logs cached, in one execution log buffer in memory. By default it is set to 10000.
ExecutionLogMaxInMemoryBufferCount: This parameter is for max number of execution log buffers in memory for the execution log. Default value 10.
ExecutionLogRetryCount: It shows the retry count if execution logging fails. The default value is set to 3 (i.e. 3 retry attempts) in case of execution logging failure.

Scale Out SQL Views and Stored Procedures in SSISDB

Here are some views and stored procedures.

catalog.master_properties

This view displays the properties of the Integration Services Scale Out Master. We have seen explored this above.

catalog.worker_agents

This view is used to display the information for the Integration Services Scale Out Worker.

This view returns details about each SSISworker registered with the ScaleoutMaster. To run this, the below permissions are required in the SSISDB database:

Membership to the ssis_admin database role
Membership to the ssis_cluster_executor database role
Membership to the sysadmin server role

catalog.disable_worker_agent

With this stored procedure, we can disable the ScaleoutWorker.

We need to pass the WorkerAgentId for the worker which we want to disable.

SELECT WorkerAgentId, MachineName, IsEnabled  FROM [catalog].[worker_agents]
GO

EXEC [catalog].[disable_worker_agent] '3CBD1F52-62B5-4DCE-9BB6-64AA170A5E42'
GO 

SELECT WorkerAgentId, MachineName,IsEnabled FROM [catalog].[worker_agents]
GO

The stored procedure requires the below permissions:

Membership to the ssis_admin database role
Membership to the sysadmin server role

catalog.enable_worker_agent

With this stored procedure, we can enable the ScaleoutWorker.