SQL Select the value in the Set Builder

Walkthrough: Setting Up SQL Server Integration Services (SSIS) Scale Out

  • 6 minutes to read

Scope of application:SQL Server (all supported versions) SSIS integration runtime in Azure Data Factory

Set up (SSIS) scale out for Integration Services by completing the following tasks.

tip

If you are installing Scale Out on a single computer, you are also installing the Scale Out Master and Scale Out Worker components at the same time. If you install the components at the same time, the endpoint that is used to connect to the Scale Out master is automatically generated.

Install the Scale Out Master

To set up the Scale Out master, you must install the Integration Services database engine services. To set up SQL Server, you must install the “Scale Out Master” component from SSIS.

For information about setting up the database engine and Integration Services, see Installing the SQL Server Database Engine and Installing Integration Services.

Use the SQL Server installation wizard or the command prompt to install the Scale Out Master component.

Install the Scale Out Master using the SQL Server Installation Wizard

  1. Activate on the page Function selection the option Scale out masterlisted under Integration Services.

  2. Select on the side Server configuration the account under which the SQL Server Integration Services Scale Out Master Service should be run and select the Start type out.

  3. Enter on the side Integration Services Scale Out Master Configuration the port number over which the scale out master and the scale out worker communicate. The default port number is 8391.

  4. Specify the TLS / SSL certificate that is used to protect communication between the scale out master and the scale out worker using one of the following methods.

    • Let the setup process create a standard self-signed TLS / SSL certificate by clicking Create a new SSL certificate click. The standard certificate is installed under "Trusted Root Certification Authorities", "Local Computer". You can specify the common names in this certificate. The host name of the master endpoint should be included in the common names. By default, the computer name and IP address of the master node are not included.
    • Select an existing TLS / SSL certificate on the local computer by first clicking Use an existing SSL certificate and then on Search click. The certificate thumbprint is displayed in the text field. After clicking Search certificates are displayed that are stored in "Trusted Root Certification Authorities", "Local Computer". The certificate you choose must be saved here.

  5. Complete the SQL Server Installation Wizard.

Install the Scale Out Master from the command prompt

Follow the instructions in Installing SQL Server from the command prompt. Perform the following steps to set the parameters for the scale out master:

  1. Add to the parameter

  2. Configure the scale out master by specifying the following parameters and their values:

    • (optional)
    • (optional)

    Note

    If the Scale Out master was not installed with the database engine and it is a named instance, you must configure it in the service configuration file of the Scale Out master after installation. See Scale Out Masters for more information.

Install the scale out worker

Install Integration Services and its “Scale Out Worker” component in the SQL Server Setup to set up the Scale Out worker.

Use the SQL Server installation wizard or the command prompt to install the Scale Out Worker component.

Install the scale out worker using the SQL Server installation wizard

  1. Activate on the page Function selection the option Scale out workerslisted under Integration Services.

  2. Select on the side Server configuration the account under which the service SQL Server Integration Services Scale Out worker service should be run and select the Start type out.

  3. Enter on the side Integration Services Scale Out worker configuration the endpoint through which a connection with the Scale Out master is established.

    • For an environment with a single computer the endpoint is generated automatically if the scale out master and the scale out worker are installed at the same time.

    • For an environment with multiple computers the endpoint consists of the name or IP address of the computer on which the scale out master is installed and the port number specified during the installation of the scale out master.

    Note

    You can skip the configuration of the worker at this point and use the scale out manager to assign the scale out worker and the scale out master to each other.

  4. Look for an environment with multiple computers the client's TLS / SSL certificate used to verify the scale out master. For an environment with only a single computer you do not need to provide a TLS / SSL certificate for the client.

    click on Search to search for the certificate file (* .cer). Select the file located under on the computer where the Scale Out Master is installed to use the TLS / SSL standard certificate.

    Note

    If the TLS / SSL certificate used by the Scale Out master is self-signed, a corresponding TLS / SSL certificate for the client must be installed on the computer with the Scale Out worker. If you're on the side Worker configuration for horizontal scaling up of Integration Services provide the file path for the TLS / SSL client certificate, it will be installed automatically. Otherwise you will have to install the certificate manually later.

  5. Complete the SQL Server Installation Wizard.

Install the scale out worker from the command prompt

Follow the instructions in Installing SQL Server from the command prompt. Complete the following steps to set the parameters for the scale out worker:

  1. Add "IS_Worker" to the parameter.

  2. Configure the scale out worker by specifying the following parameters and their values:

    • (optional)
    • (optional)

Install the client certificate for scale out workers

During the installation of the scale out worker, a worker certificate is automatically installed on the computer. In addition, a corresponding client certificate ("SSISScaleOutWorker.cer") is installed. In order for the scale out master to be authenticated by the scale out worker, you must add this client certificate to the root store of the local computer with the scale out master.

Double-click the .cer file, then click in the Certificate dialog box Install certificateto add the client certificate to the root store. The Certificate import wizard will be opened.

Open the firewall port

In the Windows Firewall on the computer with the Scale Out master, open the port that was specified during the installation of the Scale Out master and the port of SQL Server (1433 by default).

Note

After opening the firewall port, you need to restart the service for the scale out worker.

Start the SQL Server Scale Out master and worker services

If you did not set the start-up type of the services to Automatically start the following services:

  • SQL Server Integration Services Scale Out-Master 14.0 (SSISScaleOutMaster140)

  • SQL Server Integration Services Scale Out Worker 14.0 (SSISScaleOutWorker140)

Activate the scale out master

Click in the dialog box Create catalog on Activate this server as SSIS Scale Out masterwhen you create the SSISDB catalog in SQL Server Management Studio.

After creating the catalog, you can activate the scale out master using the scale out manager.

Enable SQL Server authentication mode

If you did not enable SQL Server authentication during the database engine installation, enable SQL Server authentication mode for the SQL Server instance hosting the SSISDB catalog.

Package execution is not blocked when SQL Server authentication is disabled. However, the execution log cannot write to SSISDB.

Activate the scale out worker

You can activate the scale out worker using the scale out manager, which provides a graphical user interface, or using a stored procedure.

To activate a scale out worker with a stored procedure, run the stored procedure with WorkerAgentId as a parameter.

You get the value WorkerAgentId from the view in SSISDB after the scale out worker has been registered with the scale out master. Registration takes a few minutes after the Scale Out Master and Scale Out Worker services have started.

example

In the following example the scale out worker is activated.

Next Steps