SQL Server vNext (SQL Server 2017) SSIS Scale Out Feature

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

Problem

SQL Server vNext represents a major step towards making SQL Server a platform that gives you many choices for development languages, data types, on-premises, in the cloud, and across operating systems by bringing the power of SQL Server to Linux, Linux-based Docker containers, and Windows. We have explored SQL Server vNext on Linux in previous tips and in this tip we will explore SQL Server vNext on Windows and its new feature SSIS Scale Out for Integration Services.

Solution

In April 2017, Microsoft released the Community Technology Preview (CTP) 2.0 of SQL Server vNext. Microsoft also officially announced that this Community Technology Preview (CTP) 2.0 of SQL Server vNext will be called SQL Server 2017.

Yes, we now have a production-quality preview of SQL Server 2017 and it is available on both Windows and Linux.  SQL Server 2017 preview can be downloaded from this link.

SQL Server 2017 preview (vNext CTP 2.0) installation

Once we download the release from the link above, installation is almost the same as SQL Server 2016.

In the features page, you can notice the new features under Integration Services as highlighted below.

SQL Server Integration Services Scale Out

Until SQL Server 2016, SSIS package execution is limited to a single machine. With the introduction of the Scale Out features in SQL Server 2017, Microsoft has removed this limitation. Integration Services Scale Out provides high performance package execution by distributing executions to multiple machines. We can execute the package in Scale Out mode in parallel execution on different servers.

SSIS Scale Out functionality consists of:

• SSIS Scale Out Master  - The Scale Out Master is responsible for Scale Out management and receives package execution requests from users.
• SSIS Scale Out Workers - Scale Out Workers pull execution tasks from the Scale Out Master and do the package execution work.

The below modes are available for SSIS Scale Out Master and Workers.

SSIS Master only distributes executions

In this mode, the Scale Out Master role is used to distribute the execution between workers and to coordinate their executions. The master will not run the package itself.

SSIS Master acts as a worker too

In this mode, the SSIS Scale Out Master will also act as a Scale Out Worker. It distributes the execution and execute the package as well.

Installation and configuration of Integration Services Scale Out

Installation and Configuration of the Integration Services Scale Out contains the below steps:

1. Install Scale Out Master
2. Copy Master Certificate to Workers
3. Install Scale Out Worker
4. Install Scale Out Worker client certificate
5. Install SQL Server Management Studio 17.0
6. Start SQL Server Scale Out Master and Worker Service
7. Enable Scale Out Master
8. Enable SQL Server Authentication mode
9. Enable Scale Out Worker

Now, we will explore each step in detail.

1. Install Scale Out Master

To enable the functionality of Scale Out Master, we must have install Database Engine Services, Integration Services, and its Scale Out Master feature when you set up SQL Server. In this tip, I am assuming that the database engine services are already installed since its installation is similar to SQL Server 2016.

Select the Scale Out Master from the features pane and click on Next.

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.

Note: This port should be open in the firewall between the Master and Worker Machines.

In the next step, we need to specify the SSL certificate that will be used to protect the communication between the Scale Out Master and Scale Out Worker. It can be done as below:

• SQL Server Setup process creates 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.
• Select an existing SSL Certificate on the local computer by clicking Use an existing SSL certificate and then clicking Browse. 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 we select must be stored here.

In this demo, I want setup process to create a SSL certificate, so select the default option.

Now review the installation overview and finish the installation.

2. Copy Master Certificate to Workers

Once we have installed the scale out master, we can see the SSISScaleOutMaster.cer is installed on the default installation path Root:\Program Files\Microsoft SQL Server\140\DTS\Binn.

If we are installing Scale Out Master and Scale Out Worker in a single computer we don't need to copy this certificate, however for multiple computers, copy this certificate to the worker computers under the same path Root:\Program Files\Microsoft SQL Server\140\DTS\Binn.

3. Install Scale Out Worker

To enable the functionality of Scale Out Worker, we must install Integration Services and its Scale Out Worker feature in the SQL Server setup. 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.

In the next page, we need to specify the endpoint to connect to the Scale Out Master. If we have the Scale Out Master and Worker on a single computer, the endpoint is automatically generated when the Scale Out Master and Scale Out Worker are installed or we can specify this during setup as well.

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.

For the SSL certificate, in the case of single computer use the default certificate SSISScaleOutMaster.cer from Root:\Program Files\Microsoft SQL Server\140\DTS\Binn, but for multiple computers use the certificate copied in the above step from the master to the worker completers.

The endpoint format will be https://[MachineName]:[Port] which we defined during the SSIS master creation.

Now review the setup and finish the installation.

4. Install Scale Out Worker Client Certificate

Once we have the Scale Out Worker and Scale Out Master installed, we can see worker certificate will be automatically created and installed on the computer.

We need to copy this SSISScaleOutWorker.cer certificate to the SSIS master and register it there. To do this, double click the .cer file and then click Install Certificate in the Certificate dialog box. With these certificates the Scale Out Master can authenticate the Scale Out Workers.

5. Install SQL Server Management Studio 17.0

We need to install SQL Server Management Studio release 17.0 (Build number 14.0.17099.0) to add a Catalog for the Scale Out Master and Worker configuration. This can be downloaded from this link. SSMS 17.X is the latest generation of SQL Server Management Studio and provides support for SQL Server 2017. It also provides backward compatibility to all SQL Server versions and can be installed in parallel with other versions of SQL Server Management Studio.

We need to restart the system once SSMS 17.0 is installed.

Now open the SQL Server Management Studio 17.0 and connect to the instance.

We can see here the complete new look of SSMS.

6. Start SQL Server Scale Out Master and Worker services

If the startup type of the services is not set to Automatic during installation, start the services SQL Server Integration Services Scale Out Master 14.0 (SSISScaleOutMaster140) and SQL Server Integration Services Scale Out Worker 14.0 (SSISScaleOutWorker140).

These services are not part of SQL Server Configuration Manager yet, so you have to open services.msc to manage these services. This will probably be added to SQL Server Configuration Manager in future releases.

7. Enable Scale Out Master

Now create a new SSIS Catalog as we normally do (Understanding the SQL Server Integration Services Catalog and creating the SSISDB Catalog), but we will see an extra option - Enable this server as SSIS scale out master.

8. Enable SQL Server Authentication Mode

We need to enable SQL Server authentication for the instance hosting the SSISDB catalog. This is required to write execution logs to SSISDB.

9. Enable Scale Out Worker

Connect to the SQL Server instance on the master and execute the following query to find the worker id.

SELECT * FROM [SSISDB].[catalog].[worker_agents]

To enable a Scale Out Worker, execute the [catalog].[enable_worker_agent] stored procedure with WorkerAgentId as the parameter. We can get the WorkerAgentid from the query above.

EXEC [catalog].[enable_worker_agent] 'WorkerAgentID'
GO 

Now we can that the Scale Out Worker is enabled.

Next Steps

• The setup of the Scale Out feature is finished. We can now run packages in Scale Out mode which we will explore in future tips.
• Explore SQL Server 2017 in this preview.
• Read more about SQL Server vNext Linux Tips.

About the author

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