Setup SSIS Scale Out

Case
SQL VNext has a new Scale Out function. How does it work and how do you install and configure that?

Solution
The new Scale Out option in SSIS VNEXT gives you the ability to execute multiple packages distributed to multiple worker machines. You can select multiple packages on the master that will be executed in parallel by one or more worker machines.

Machine setup
To make sense of a Scale Out you of course need multiple machines. We need a master machine and one or more worker machines. Because the master distributes the executions and doesn't execute packages it self, you may want to consider installing a worker on the same machine as the master to make use of its resources. The worker machines only have a worker installed. A SQL Server engine installation is not necessary on a worker.

Option 1: Master only distributes executions

Option 2: Master also executes packages itself

For this example I will use option 1 with a master and two separate workers on three HyperV machines. All machines are identical with Windows Server 2012 R2 with all updates installed.

HyperV Machines

Download

Before installing your first you need to download SQL Server VNEXT and the associated SSMS and SSDT.

Installation steps:

1. Install SQL Server VNEXT on master
2. Configure firewall on master
3. Copy master certificate to workers
4. Install SQL Server VNEXT on workers
5. Copy worker certificates to master
6. Install SSMS VNEXT to add Catalog on master
7. Install worker certificates on master
8. Enable scale out workers on master

Installation step 1: install SQL Server VNEXT on master
Install SQL Server VNEXT on the 'Master' machine called "SQLVNEXT_M". Below the most important steps of the installation. At the bottom all screens are shown in a movie.

We need the Database Engine to store the SSISDB

We need SSIS and the Scale Out Master

SQL Server Authentication mode is required on the SSISDB

Choose port 8391 and create a new SSL certificate

All steps

Installation step 2: configure firewall on master
Open a firewall port on the Scale Out Master. We need at least an inbound rule for port 8391 supplied as EndPoint in the previous step, but a complete list of all SQL Server ports can be found here.

Installation step 3: copy master certificate to workers
We created a new certificate during installation of the Scale Out Master. You can find it in <drive>:\Program Files\Microsoft SQL Server\140\DTS\Binn. We need that certificate during installation of the Scale Out Workers. So copy it to the worker machines.

Copy SSISScaleOutMaster.cer to worker

Copy SSISScaleOutMaster.cer to worker

Installation step 4: install SQL Server VNEXT on workers
Install SQL Server VNEXT on the 'Worker' machines called "SQLVNEXT_W1" and "SQLVNEXT_W2". Below the most important steps of the installation. At the bottom all screens are shown in a movie.

Only select SSIS and Scale Out Worker (no engine needed)

Add an EndPoint like https://SQLVNEXT_M:8391. This is the name of the
Scale Out Master machine and the port chosen during the installation of the
Scale Out Master.
The certificate is the one you copied from the Scale Out Master
in one of the previous steps.

All steps

Installation step 5: copy worker certificates to master
During installation of the Scale Out Worker machines, certificates where created which we need register on the machine with the Scale Out Master. With these certificates the Scale Out Master can authenticate the Scale Out Workers. You can find SSISScaleOutWorker.cer in <drive>:\Program Files\Microsoft SQL Server\140\DTS\Binn (repeat this for all workers).

Copy SSISScaleOutWorker.cer to master

Copy SSISScaleOutWorker.cer to master

Installation step 6: install SSMS VNEXT to add Catalog on master
Now we need to add a catalog to the master. To do this you need to install SSMS VNEXT first. For this demo situation I installed SSMS VNEXT on the master machine.

Install SSMS VNEXT

Add catalog as you normally do, but notice the extra option:

Enable this server as SSIS scale out master

Add catalog

Installation step 7: install worker certificates on master
Now we need to install all Scale Out Worker Certificates on the Scale Out Master machine. They should be stored in the Trusted Root Certification Authorities. Repeat the steps below for all Worker Certificates.

Store certificates in Trusted Root Certification Authorities

All Steps

Installation step 8: enable scale out workers on master
Make sure the services SSISScaleOutWorker140 on the Worker machines are started and SSISScaleOutMaster140 on the master. Then start SSMS and connect to the SQL Server instance on the master and execute the following query:

-- Get Worker info
SELECT * FROM [SSISDB].[catalog].[worker_agents]

It could take a few minutes before the worker machines are registered. Once that happens the query should return records. Use the values from the WorkerIdAgentId column in the next Stored Procedure Call to enable the Scale Out Workers

-- Enable Workers
EXEC [SSISDB].[catalog].[enable_worker_agent] 'F5BA7B83-D8FC-49D2-8896-95C0F0725562' -- SQLVNEXT_W1
EXEC [SSISDB].[catalog].[enable_worker_agent] 'FC0B9E86-8BB3-4A3D-B3EB-5A29DE1CE9BE' -- SQLVNEXT_W2

Enable Scale Out Workers

Now you're ready to deploy your first project and execute packages with the new Scale Out function. Also see the Microsoft walkthrough for the scale out setup.

Conclusion
The Master Worker setup is a great way to distribute package executions over multiple servers. When you will choose for upgrading your existing SSIS server with more memory and more cores above the new master-worker setup, probably depends on the licensing model. But when you already maxed out the hardware of your current SSIS server, then this new master-worker setup is an easy way to upgrade.
And what about a future Scale Out to Azure? If a weekly, monthly or quarterly run is taking to much time or one of your worker servers is down (for maintenance?).