SQL Server 2017 SSIS Scale Out Configuration

By:   |   Comments (1)   |   Related: 1 | 2 | > Integration Services Configuration Options


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.

SSISScaleOutMaster140 Windows Service
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.

MasterSettings.config file contents

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.

scale out service log
master heartbeat log

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]
ScaleOutMaster 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:

configuration file for the Scale Out Worker

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.

catalog.worker_agents

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
catalog.disable_worker_agent

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.

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
catalog.enable_worker_agent

The stored procedure requires the below permissions:

  • Membership to the ssis_admin database role
  • Membership to the sysadmin server role
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Rajendra Gupta Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Wednesday, July 25, 2018 - 12:43:37 PM - Alex Back To Top (76829)

Hello Rajendra,

can you help me pls. I habe one Master und 3 Workers, and about 60 Packages. The Agent-Job is running every 20 Sek, but i see, than only one process of ISServerExec.exe will be running to each time on the each of worker. I habe every time fast 60 packages with state 5 
SELECT * FROM SSISDB.[catalog].executions WHERE [status] = 5

 

Thas is the settings from WorkerSetting.config

"AgentHeartbeatInterval":"00:00:10",

"TaskHeartbeatInterval":"00:00:5",

"HeartbeatErrorTolerance":"00:05:00",

"TaskRequestMaxCPU":85.0,

"TaskRequestMinMemory":100.0,

"MaxTaskCount":40,

"LeaseInternval":"00:00:8",

"TasksRootFolder":"",

"TaskLogLevel":126,

"TaskLogSegment":"00:00:00",

"TaskLogEnabled":true,

"ExecutionLogCacheFolder":"",

"ExecutionLogMaxBufferLogCount":10000,

"ExecutionLogMaxInMemoryBufferCount":10,

"ExecutionLogRetryCount":3,

"ExecutionLogRetryTimeout":"7.00:00:00",

I don't understand, why the packages will be execute not all at once, but they are allin the queue.

Thank you very much for you help,

King regards,

Alex

 















get free sql tips
agree to terms