Walkthrough: Set up Integration Services (SSIS) Scale Out

12/13/2017

6 minutes to read

Contributors

In this article

Set up Integration Services (SSIS) Scale Out by completing the following tasks.

Tip

If you are installing Scale Out on a single computer, install the Scale Out Master and Scale Out Worker features at the same time. When you install the features at the same time, the endpoint is automatically generated to connect to Scale Out Master.

To use the default SQL Server authentication account for Scale Out logging, select Mixed Mode for authentication mode on the Database Engine Configuration page during Database Engine installation. See Change the account for Scale Out logging for more information.

To install the Scale Out Master feature, use the SQL Server installation wizard or the command prompt.

Install Scale Out Master with the SQL Server installation wizard

On the Feature Selection page, select Scale Out Master, which is listed under Integration Services.

On the Server Configuration page, select the account to run SQL Server Integration Services Scale Out Master service and select the Startup Type.

On the Integration Services Scale Out Master Configuration page, specify the port number that Scale Out Master uses to communicate with Scale Out Worker. The default port number is 8391.

Specify the SSL certificate used to protect the communication between Scale Out Master and Scale Out Worker by doing one of the following.

Let the setup process create a default, self-signed SSL certificate by clicking Create a new SSL certificate. The default certificate is installed under Trusted Root Certification Authorities, Local Computer. You can specify the CNs in this certificate. The host name of master endpoint should be included in CNs. By default, the machine name and ip of Master Node are included.

Select an existing SSL Certificate on the local computer by clicking Use an existing SSL certificate and then clicking Browse to select a certificate. The thumbprint of the certificate appears in the text box. Clicking Browse displays certificates that are stored in Trusted Root Certification Authorities, Local Computer. The certificate you select must be stored here.

Install Scale Out Master from the command prompt

Configure Scale Out Master by specifying the following parameters and their values:

/ISMASTERSVCACCOUNT

/ISMASTERSVCPASSWORD

/ISMASTERSVCSTARTUPTYPE

/ISMASTERSVCPORT

/ISMasterSVCSSLCertCN (optional)

/ISMASTERSVCTHUMBPRINT (optional)

Note

If Scale Out Master is not installed together with Database Engine, and the Database Engine instance is a named instance, you have to configure SqlServerName in the Scale Out Master service configuration file after installation. For more info, see Scale Out Master.

Install Scale Out Worker

To set up Scale Out Worker, you have to install Integration Services and its Scale Out Worker feature in SQL Server setup.

To install the Scale Out Worker feature, use the SQL Server installation wizard or the command prompt.

Install Scale Out Worker with the SQL Server installation wizard

On the Feature Selection page, select Scale Out Worker, which is listed under Integration Services.

On the Server Configuration page, select the account to run SQL Server Integration Services Scale Out Worker service and select the Startup Type.

On the Integration Services Scale Out Worker Configuration page, specify the endpoint to connect to Scale Out Master.

For a single-computer environment, the endpoint is automatically generated when Scale Out Master and Scale Out Worker are installed at the same time.

For a multiple-computer environment, the endpoint consists of the name or IP of the computer with Scale Out Master installed and the port number specified during the Scale Out Master installation.

Note

You can also skip Worker configuration at this point and associate the Scale Out Worker with the Scale Out Master by using Scale Out Manager after installation.

For a multiple-computer environment, specify the client SSL certificate that is used to validate Scale Out Master. For a single-computer environment, you don't have to specify a client SSL certificate.

Click Browse to find the certificate file (*.cer). To use the default SSL certificate, select the SSISScaleOutMaster.cer file located under \<drive\>:\Program Files\Microsoft SQL Server\140\DTS\Binn on the computer on which Scale Out Master is installed.

Note

When the SSL certificate used by Scale Out Master is self-signed, a corresponding client SSL certificate has to be installed on the computer with Scale Out Worker. If you provide the file path for the client SSL Certificate on the Integration Services Scale Out Worker Configuration page, the certificate will be installed automatically; otherwise, you have to install the certificate manually later.

Install Scale Out Worker from the command prompt

Configure Scale Out Worker specifying the following parameters and their values:

/ISWORKERSVCACCOUNT

/ISWORKERSVCPASSWORD

/ISWORKERSVCSTARTUPTYPE

/ISWORKERSVCMASTER (optional)

/ISWORKERSVCCERT (optional)

Install Scale Out Worker client certificate

During the installation of Scale Out Worker, a worker certificate is automatically created and installed on the computer. Also, a corresponding client certificate, SSISScaleOutWorker.cer, is installed under \<drive\>:\Program Files\Microsoft SQL Server\140\DTS\Binn. For Scale Out Master to authenticate the Scale Out Worker, you have to add this client certificate to the Root store of the local computer with Scale Out Master.

To add the client certificate to the Root store, double-click the .cer file and then click Install Certificate in the Certificate dialog box. The Certificate Import Wizard opens.

Open firewall port

On the Scale Out Master computer, open the port specified during the Scale Out Master installation and the port for SQL Server (1433, by default) in the Windows Firewall.

Note

After you open the firewall port, you also have to restart the Scale Out Worker service.

Start SQL Server Scale Out Master and Worker services

If you didn't set the startup type of the services to Automatic during installation, start the following services:

Enable SQL Server Authentication mode

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

Enable Scale Out Worker

You can enable Scale Out Worker with Scale Out Manager, which provides a graphical user interface, or with a stored procedure.

To enable a Scale Out Worker with a stored procedure, execute the [catalog].[enable_worker_agent] stored procedure with WorkerAgentId as the parameter.

Get the WorkerAgentId value from the [catalog].[worker_agents] view in SSISDB, after Scale Out Worker registers with Scale Out Master. Registration takes several minutes after the Scale Out Master and Worker services are started.

Next steps

The feedback system for this content will be changing soon. Old comments will not be carried over. If content within a comment thread is important to you, please save a copy. For more information on the upcoming change, we invite you to read our blog post.