Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


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


SQL Server Conference Giveaway - click to learn more


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.

New Features of SQL Server 2017

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.

Scale Out Master and Workers in SQL Server 2017

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.

Scale Out Master \ Worker and Workers in SQL Server 2017

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.

Feature Selection for SQL Server 2017

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

Server Configuration of SQL Server 2017

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.
Integration Services Scale Out Master Configuration

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.

Integration Services Scale Out Master Service Configuration

Integration Services Scale Out Master Installation Progress

Integration Services Scale Out Master Installation Succeeded

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.

Copy Master Certificate to Workers in SQL Server 2017

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.

Install Scale Out Worker in SQL Server 2017

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

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.

Integration Services Scale Out Worker Configuration for SQL Server 2017

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.

Specify the endpoint and client SSL certificate

Now review the setup and finish the installation.

Integration Services Scale Out Worker Service Configuration

Scale Out Worker Installation Succeeded

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.

Install Scale Out Worker Client Certificate

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.

Install SQL Server Management Studio 17.0
Begin the SQL Server 2017 SSMS Installation

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

Restart required for SQL Server 2017 SSMS Installation

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

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

SQL Server 2017 SSMS Versions

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).

SQL Server Scale Out Master and Worker services

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.

Enable this server as SSIS Scale Out Master

8. Enable SQL Server Authentication Mode

We need to enable SQL Server authentication for the instance hosing 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]

Find the SSIS Worker ID

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 

SSIS WorkerAgentID

Now we can that the Scale Out Worker is enabled.

SSIS 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.


Last Update:


signup button

next tip button



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

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Friday, May 12, 2017 - 2:20:38 PM - rajendra Back To Top

 

 Hi Chris

 

SQL Server 2017 is still in production preview phase , Licensing information is not yet released. I willl update you once this is available.

 


Thursday, May 11, 2017 - 8:45:44 AM - Chris Back To Top

Thank you for the article. Do you know the licensing requirements for the scal out master and worker nodes? Do they have to be covered by SQL Server licenses the same as the database engine?
 

Thanks,

Chris


Learn more about SQL Server tools