By
Published
Mar 25 2019 04:06 PM
1,265
Views
First published on MSDN on Jan 20, 2017
Previously, SSIS package execution is limited to a single machine. However, the new feature Scale Out, available in SQL Server vNext CTP1 has broken the limit.
Scale Out provides parallel package execution by distributing the executions to multiple machines so that the overall execution time can be significantly reduced. The user is able to submit a job of running multiple packages and these packages will be run in parallel on different machines. In the past, when package execution processes compete for resources such as CPU, memory etc., user will have to manually set up multiple machines with multiple SSIS catalogs and they have to write scripts to trigger execution themselves. With the Scale Out feature that is available in SSIS vNext CTP 1.0, all of these burdens are removed. You can scale out your package executions easily both on premises or on Azure VM as IaaS.
To execute packages in Scale Out from SQL Server Management Studio, right-click on the SSISDB node or the project/package to execute and select “Execute in Scale Out”. The menu item is enabled only if the server is enabled as Scale Out Master during catalog creation. See MSDN doc, Run packages in Scale Out .
In the “Execute Package In Scale Out” dialog, multiple packages can be selected to execute. Each individual package can be configured just like executing on local machine.
Furthermore, a new configuration, called “Retry count” is available for a package executing in Scale Out. It specifies the retry count if a package execution fails.
In cases that both on cloud vms and on premises machines are demanded to run SSIS packages, there’s no need to manage two catalogs, one for on cloud and one for on premises. Instead, a hybrid SSIS Scale Out can be built to manage all the resources.
Example: How to set up a hybrid Scale Out
The sample Scale Out is composed of a Scale Out Master on Azure VM, a Scale Out Worker on Azure VM and a Scale Out Worker on premises. (If Scale Out Master is on premises, it requires some VNet configurations to support connection from Scale Out Workers on Azure VM. To keep it simple, we place Scale Out Master on Azure VM here.) The MSDN doc Walkthrough: Set Up Integration Services Scale Out introduces the detailed instructions to install Scale Out Master and Scale Out Worker.
1. Allocate two Azure VMs and set public and private IP to static
2. Install Scale Out Master on Azure VM
During the installation of Scale Out Master, choose “Use an existing SSL certificate” instead of “Create a new SSL certificate”.
The reason is that the default created certificate does not contain public IP in the CNs, which will lead to the unsuccessful connection from on premises Scale Out Worker later. The certificate can be created and installed through the following command:
MakeCert.exe -n CN={public IP address};CN={private IP address} SSISScaleOutMaster.cer -r -ss Root -sr LocalMachine
(In future releases, the user will be able to set CNs if “Create a new SSL certificate” is selected.)
3. Add Inbound security rule for Scale Out Master VM. Open the port set during Scale Out Master installation.
4. Install Scale Out Worker on Azure VM and on premises and install Worker certificates to the VM of Scale Out Master
Remember to specify public IP in Master endpoint for on premises Worker and private IP for on cloud VM.
Done.
After setting up a hybrid SSIS Scale Out, you can enjoy manage on cloud and on premises resources with one SSIS catalog.
SSIS Scale Out supports choosing the machines to execute packages in the “Machine Selection” page of “Execute Package In Scale Out” dialog. As a result, you can easily choose to run the packages on cloud or on premises. To ensure the package is executed, at least one of the workers you selected must be enabled.
The actual machine that ran the package can be verified on the package execution report. To open the report, right-click on the package node and select “Reports”, “All executions”. Then, click “Overview” of the execution you care about. The status of the execution can also be learnt from this report.
Previously, SSIS package execution is limited to a single machine. However, the new feature Scale Out, available in SQL Server vNext CTP1 has broken the limit.
Scale Out provides parallel package execution by distributing the executions to multiple machines so that the overall execution time can be significantly reduced. The user is able to submit a job of running multiple packages and these packages will be run in parallel on different machines. In the past, when package execution processes compete for resources such as CPU, memory etc., user will have to manually set up multiple machines with multiple SSIS catalogs and they have to write scripts to trigger execution themselves. With the Scale Out feature that is available in SSIS vNext CTP 1.0, all of these burdens are removed. You can scale out your package executions easily both on premises or on Azure VM as IaaS.
Execute packages in Scale Out
To execute packages in Scale Out from SQL Server Management Studio, right-click on the SSISDB node or the project/package to execute and select “Execute in Scale Out”. The menu item is enabled only if the server is enabled as Scale Out Master during catalog creation. See MSDN doc, Run packages in Scale Out .
In the “Execute Package In Scale Out” dialog, multiple packages can be selected to execute. Each individual package can be configured just like executing on local machine.
Furthermore, a new configuration, called “Retry count” is available for a package executing in Scale Out. It specifies the retry count if a package execution fails.
Set up a hybrid SSIS Scale Out
In cases that both on cloud vms and on premises machines are demanded to run SSIS packages, there’s no need to manage two catalogs, one for on cloud and one for on premises. Instead, a hybrid SSIS Scale Out can be built to manage all the resources.
Example: How to set up a hybrid Scale Out
The sample Scale Out is composed of a Scale Out Master on Azure VM, a Scale Out Worker on Azure VM and a Scale Out Worker on premises. (If Scale Out Master is on premises, it requires some VNet configurations to support connection from Scale Out Workers on Azure VM. To keep it simple, we place Scale Out Master on Azure VM here.) The MSDN doc Walkthrough: Set Up Integration Services Scale Out introduces the detailed instructions to install Scale Out Master and Scale Out Worker.
1. Allocate two Azure VMs and set public and private IP to static
2. Install Scale Out Master on Azure VM
During the installation of Scale Out Master, choose “Use an existing SSL certificate” instead of “Create a new SSL certificate”.
The reason is that the default created certificate does not contain public IP in the CNs, which will lead to the unsuccessful connection from on premises Scale Out Worker later. The certificate can be created and installed through the following command:
MakeCert.exe -n CN={public IP address};CN={private IP address} SSISScaleOutMaster.cer -r -ss Root -sr LocalMachine
(In future releases, the user will be able to set CNs if “Create a new SSL certificate” is selected.)
3. Add Inbound security rule for Scale Out Master VM. Open the port set during Scale Out Master installation.
4. Install Scale Out Worker on Azure VM and on premises and install Worker certificates to the VM of Scale Out Master
Remember to specify public IP in Master endpoint for on premises Worker and private IP for on cloud VM.
Done.
After setting up a hybrid SSIS Scale Out, you can enjoy manage on cloud and on premises resources with one SSIS catalog.
SSIS Scale Out supports choosing the machines to execute packages in the “Machine Selection” page of “Execute Package In Scale Out” dialog. As a result, you can easily choose to run the packages on cloud or on premises. To ensure the package is executed, at least one of the workers you selected must be enabled.
The actual machine that ran the package can be verified on the package execution report. To open the report, right-click on the package node and select “Reports”, “All executions”. Then, click “Overview” of the execution you care about. The status of the execution can also be learnt from this report.