Chapter 11. Scale Out with SSIS 2017

This chapter will cover the following recipes:

• SQL Server 2017 download and setup
• SQL Server client tools setup
• SSIS configuration for scale out executions
• Scaling out a package execution

Introduction

Since its inception, SSIS was meant to execute on a single machine running Windows. The service by itself could not scale on multiple machines. Although it would have been possible to call package execution with custom orchestration mechanism, it didn't have anything built in. You needed to manually develop an orchestration service and that was tedious to do and maintain. See this article for a custom scale-out pattern with SSIS: https://msdn.microsoft.com/en-us/dn887191.aspx .

What lots of developers wanted was a way to use SSIS a bit like the way Hadoop works: call a package execution from a master server and scale it on multiple workers (servers). The SSIS team is delivering a similar functionality in 2017, enabling us to enhance scalability and performance in our package executions.

As mentioned before, the scale out functionality is like Hadoop. The difference is that we use tools we have more knowledge of. It's also a lot easier to work with SSIS since we are on the Windows...

SQL Server 2017 download and setup

This recipe will cover the following subtopics:

• Download SQL Server 2017
• Set up SQL Server with SSIS scale out options

Getting ready

This recipe assumes that you have the necessary permissions to install SQL Server on your machine.

How to do it...

1. Open your web browser and navigate to https://www.microsoft.com/en-us/sql-server/sql-server-2017 .
2. Select Download the preview, as shown in the following screenshot:

3. On the page that appears, select Windows in the platform menus at the right and click on Install on Windows, as shown in this screenshot:

4. As shown in the following screenshot, fill in the form and click on Continue:

5. Choose ISO and click on Continue, as shown here:

6. Choose the language (we selected English) and click on Download, as shown in the following screenshot:

7. The download will now start. Once completed, navigate to the folder where you saved the file named SQLServer2017CTP2.1-x64-ENU.iso and double-click on it. Select setup.exe to proceed with...

SQL Server client tools setup

We will see how to setup SQL Server client tools.

Getting ready

This recipe assumes that you have access to the internet and you have the necessary rights to install the software on your PC.

How to do it...

1. From SQL Server Installation Center, click on Install SQL Server Management Tools, as highlighted in the following screenshot:

2. A browser opens and the following page opens. Select version 17.1 (the latest version available at the time of writing this book), highlighted in the following screenshot:

3. Once the download completes, double-click on the downloaded file to start the installation process. Once completed, you get a window similar as the one shown in the following screenshot:

4. Go back to SQL Server Installation Center, and this time, click on Install SQL Server Data Tools, as highlighted in this screenshot:

5. You are directed to the SSDT website. As shown in the following screenshot, download the latest version (17.1 at the time of writing) of SSDT:

6. The...

Configuring SSIS for scale out executions

We'll now configure the SSIS catalog and workers to be able to use scale out executions with SSIS.

Getting ready

This recipe assumes that you've installed SQL Server 2017, SSIS in scale out mode as well as SSMS 17.1 or later.

How to do it...

1. Open SQL Server Management Studio and connect to the newly installed SQL Server 2017 instance.
2. In the Object Explorer, right-click on the Integration Services Catalogs node and select Create Catalog.
3. The Create Catalog window appears. As shown in the following screenshot, check the Enable this server as SSIS scale out master option as well as providing a password for the catalog. Click on OK when finished.

4. Still in SSMS, run the following SQL statements. The first statement lists the workers available. Copy the WorkerAgentId into the clipboard by right-clicking on the value in the grid and select Copy. Type the second SQL statement and use the clipboard's content as parameter (the grayed-out shape in the screenshot...

Executing a package using scale out functionality

Finally, we're able to do the real work: creating a simple package and execute it in scale out mode.

Getting ready

You will need SQL Server 2017, SSIS 2017, SSDT, and SSMS 2017 to complete this recipe. It is also assumed that you have configured SSIS in the previous recipe.

How to do it...

1. Open SSDT and create a new SSIS project named SSISCookBookScaleOut, as shown in the following screenshot. Click on OK to create it:

2. In the Solution Explorer, right-click on the Package.dtsx that is created with the project and select Delete, as shown in this screenshot:

3. Right-click on the project and select Properties from the contextual menu. As shown in the following screenshot, change the ProtectionLevel property of the project to Do not save sensitive data and click on OK in both windows. You'll get a warning telling you that you'll have to do the same for all packages in the project. We don't have any, so we simply get rid of the warning dialog. Sensitive...