Solving ETL Bottlenecks with SSIS Scale Out 2017

About Me 2 BI Consultant at Coeo Southampton SQLServer User Group Leader SQLBits Volunteer!

SSIS Scale Out - Scalability “the capability of a system, network, or process to handle a growing amount of work, or its potential to be enlarged to accommodate that growth” For SSIS, this means Peak workloads – needing to process everything in a single short window Peak activity During core hours Specific events - Christmas/Black Friday etc. Increasing data volumes © 2017 Coeo Ltd. Confidential 3

SSIS Bottlenecks A single SSIS Instance servicing requests across a large estate Many independent ETL jobs running Guessing how long a job will take before starting a different one Data Warehouse load Running packages in parallel to the same destination © 2017 Coeo Ltd. Confidential 4

Scaling Up 5 Upgrade hardware Add more memory Single point of failure How much time is the shiny new hardware doing nothing? Will the SSIS package even make use of the increase? © 2017 Coeo Ltd. Confidential

Scaling Out 6 Add more servers to your estate Spread application processing Take individual servers offline for maintenance without impacting the whole Packages could exist on one or more servers Does not provide central management © 2017 Coeo Ltd. Confidential

SSIS Scale Out 7 Provides the ability to scale while retaining control Centralised management Deploy packages to a single location Centrally manage package execution and monitoring Distribute workload to worker servers Add new workers as required Pause and resume VMs for busy periods Specific workers can be used for packages if needed © 2017 Coeo Ltd. Confidential

SSIS Scale Out 8 © 2017 Coeo Ltd. Confidential

SSIS Scale Out – Scale Out Master Responsible for Scale Out management View information in SSISDB catalog Enable or disable workers Execute packages @runinscaleout=True Use a specific worker or set @useanyworker=True Can also be a Scale Out Worker Install worker on the same machine © 2017 Coeo Ltd. Confidential 9

SSIS Scale Out – Scale Out Worker Pull execution tasks from the Scale Out Master Execute packages with ISServer. Exec. exe Execution is retried automatically if it terminates unexpectedly Set the number of retries in the config file Permissions Default account is NT ServiceSSISScale. Out. Worker 140 Account will need access to the resources in the package Consider using a specific account or set of accounts © 2017 Coeo Ltd. Confidential 10

SSIS Scale Out – Installation Install Master SQL Server Database Engine Integration Services Scale Out Master Open Firewall Ports Enable SQL Authentication 11 Copy Certificate to Worker Install Worker Scale Out Worker Install Worker Certificate on Master Copy Certificate to Master Repeat… Enable Scale Out Worker © 2017 Coeo Ltd. Confidential

SSIS Scale Out – Master Set Up Default Port Number is 8391 Create a new SSL certificate or use an existing one Existing Certificate must be stored in Trusted Root Certification Authorities, Local Computer Check the CNs in the certificate If you are mixing on-premises and Azure VMs, ensure that the public IP address is included Worker Certificate Must be installed in Trusted Root Certification Authorities, Local Computer © 2017 Coeo Ltd. Confidential 12

SSIS Scale Out – Worker Set Up No changes needed to firewall Master Endpoint is fully qualified name plus port E. g. https: //Win. SSISMaster. mydomain: 8391 Can be configured after install. . . 140DTSBinnWorker. Settings. config Make sure the service is running as the correct account © 2017 Coeo Ltd. Confidential 13

High Availability 14 Make use of Availability Groups SSIS in AGs from 2016 Configure

SSIS Scale Out - Executing Manually through SSISDB Execution Useful for testing © 2017

SSIS Scale Out - Executing Execute Package through SQL Agent Need to set default option to Scale Out in Catalog Properties Useful in situations where multiple jobs are running across a large estate, when you want to execute a specific package © 2017 Coeo Ltd. Confidential 16

SSIS Scale Out - Executing Execute Package via T-SQL/Stored Procedures Useful to execute multiple

SSIS Scale Out – Monitoring SSISDB Executions Dashboard © 2017 Coeo Ltd. Confidential 18

SSIS Scale Out – Log Files Scale Out Master Logs C: UsersSSISScale. Out. Master 140App. DataLocalSSISScale. OutMaster Scale Out Worker Logs C: UsersSSIS_SvcApp. DataLocalSSISScale. Out Agent C: UsersSSIS_SvcApp. DataLocalSSISScale. OutTasks © 2017 Coeo Ltd. Confidential 19

SSIS Scale Out – Licencing Scale Out Master Requires Enterprise Licence Needs SQL Server to host SSISDB Scale Out Worker Standard or Enterprise Licence Requires Enterprise Licence if the SSIS packages include enterprise features Oracle/Teradata connectors CDC Etc. © 2017 Coeo Ltd. Confidential 20

Thank You 21 Questions? © 2017 Coeo Ltd. Confidential

Just like Jimi Hendrix … We love to get feedback Please complete the session

SQLBits - It's all about the community. . . Please visit Community Corner, we are trying this year to get more people to learn about the SQL Community, equally if you would be happy to visit the community corner we’d really appreciate it. © 2017 Coeo Ltd. Confidential