scale out - wordpress.com · scale-what? scalability is the capability of a system, network, or...

Scale Outmit SSIS 2017

Wolfgang Strasserwolfgang@powerofbi.at

@wstrasser

http://workingondata.wordpress.com/passcamp2017

SSIS 2017

https://docs.microsoft.com/en-us/sql/integration-services/what-s-new-in-integration-services-in-sql-server-2017

Scalability

Scale-What?

Scalability is 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.(Bondi, André B. (2000). Characteristics of scalability and their impact on performance. Proceedings of the second international workshop on Software and performance – WOSP '00. p. 195. )

Scalability

Scalability is 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.(Bondi, André B. (2000). Characteristics of scalability and their impact on performance. Proceedings of the second international workshop on Software and performance – WOSP '00. p. 195. )

Types of Scalability

Functional

Geographic

Administrative

Load

Generation

ScalabilityMore users? No problem!

New functionality with minimal effort

No problems with going global

Add new generations of components

React to changing loadsHigher number of requests

https://en.wikipedia.org/wiki/Scalability

Vertical versus Horizontal Scaling

Scale Up/Down„Vertical Scalability“

More CPU, Memory

Virtualization

Scale Out/In„Horizontal Scalability“

More Nodes

„Cluster“

Scale Out - Personas

Master Workers

Gets instructions from client

Knows the list of active workers

Job overview

Current state of work

Claim tasks from Master

Work on tasks

Return results and work report

Know their profession

Scale-Out in SSIS 2017

Scale Out in SSIS 2017

Master

SSISDB

SSIS

Mas

ter

Serv

ice

Worker

Wo

rke

r Se

rvic

e

….

Worker

Wo

rker

Se

rvic

e

Worker

Wo

rker

Se

rvic

e

Installation & Configuration

Scale Out Master - Installation

PrerequisitesDB-Engine: Mixed Authentication

SSIS Scale Out Master - Configuration

Port NumberDefault: 8391

Do not forget Firewall

SSL Certificate! „self“ Self-signed certificates

Scale Out Master – SSISDB

Scale Out Worker - Installation

SSS Scale Out - Certificates

Every machine in SSIS Scale Out topology needs own certificate

Master needs workers-certificates

Workers need master-certificate

During (master and worker) setup, certificates are created

But you can use your own certificates!

M w1 w2 wx

SSIS Scale Out – Certificates Workflow

Install SSIS Scale Out Master service

Copy worker certificate from worker machine

Install worker certificate (storage location)

Copy Master certificate to worker

Install master certificate (storage location)

Install Scale Out Worker service

Restart Worker service

M w1 w2 wx

C:\Program Files\Microsoft SQL Server\140\DTS\Binn

Master Machine Worker Machine

Improve the Worker Setup experience

setup.exe/ACTION=Install/FEATURES=IS_Worker/ISWORKERSVCACCOUNT="NT SERVICE\SSISScaleOutWorker140"/ISWORKERSVCSTARTUPTYPE=Automatic/ISWORKERSVCMASTER=https://ssismaster01:8391/ISWORKERSVCCERT="\\pathTo\SSISScaleOutMaster.cer" /QS/IACCEPTSQLSERVERLICENSETERMS

Certificates, certificates, certificates!!

SSIS Scale-Out Manager

SSIS Scale-Out Manager - Worker Manager

Add New Worker

Scale Out – Check the Environment (SSISDB)

SSIS Scale Out Workers

Execute in Scale Out

Catalog Option – Default Execution Mode

Execute in Scale-Out (SSMS)

Scale Out Execution (T-SQL)

Create execution

Set parameters

Define workers

Start execution

Execution Monitoring

Execution Monitoring

Execution Monitoring (T-SQL) SSISDB

Execution Monitoring (behind the scenes)

Worker – Execution LoggingC:\Users\SSISScaleOutWorker140\AppData\Local\SSIS\ScaleOut\AgentC:\Users\SSISScaleOutWorker140\AppData\Local\SSIS\ScaleOut\Tasks

Master – LoggingC:\Users\SSISScaleOutMaster140\AppData\Local\SSIS\ScaleOut\Master

Worker

Execution Monitoring (behind the scenes)

How are logging messages transferred to the server?##MS_SSISLogDBWorkerAgentUser##

Works fine if network is available

Documentations states, that logging is cached locally..

Master Worker

SSIS DB Roles

Configuration Options

Scale Out – Configuration Options

C:\Program Files\Microsoft SQL Server\140\DTS\Binn

Restart services!

Scale Out in SSIS 2017

Master

SSISDB

SSIS

Mas

ter

Serv

ice

certificates

M w1 w2 wx

Worker

Wo

rke

r Se

rvic

e

Package Executions

certificates

w1Mconfiglogs

config

logs

….

Worker

Wo

rker

Se

rvic

e

Package Executions

certificates

w2Mconfiglogs

Worker

Wo

rker

Se

rvic

e

Package Executions

certificates

wxMconfiglogs

Licensing

Licensing

As of 2017-09-14: https://docs.microsoft.com/en-us/sql/integration-services/integration-services-features-supported-by-the-editions-of-sql-server

Scale Outmit SSIS 2017

Wolfgang Strasserwolfgang@powerofbi.at

@wstrasser

workingondata.wordpress.com

Some Links

• https://docs.microsoft.com/en-us/sql/integration-services/scale-out/troubleshooting-scale-out

• https://docs.microsoft.com/en-us/sql/integration-services/scale-out/integration-services-ssis-scale-out