scale-out with ssis 2017 - · pdf filesql pass austria sqlsaturday vienna organizer ... ssis...
TRANSCRIPT
THANK YOU TO OUR SPONSORSPLATINUM
GOLD SILVER
PARTICIPATING COMMUNITIESCLOUD PRO PT
About me – Wolfgang Strasser
Consultant
Software, Business Intelligence and DWH
SQL Server, SSIS, SSAS, SSRS, Power BI, Azure, …
SQL Pass Austria
SQLSaturday Vienna Organizer
@wstrasser
wolfgang.strasser_AT_gmx.at
workingondata.wordpress.com
DTS SSIS
SSIS Catalog
Project Deployment Model
Custom Logging Levels
SSISDB
https://static.pexels.com/photos/320265/pexels-photo-320265.jpeg
Incremental Package Deployment
Control Flow Parts
AutoAdjustBufferSize
The Story of SSIS
htt
ps:
//d
ocs
.mic
roso
ft.c
om
/en-
us/
sql/
inte
grat
ion
-ser
vice
s/w
hat
-s-n
ew-i
n-i
nte
grat
ion
-ser
vice
s-in
-sq
l-se
rver
-20
17
https://blogs.msdn.microsoft.com/ssis/2017/05/17/ssis-helsinki-is-available-in-sql-server-vnext-ctp2-1/
dtexec
package mode
No SSIS catalog support
No third-party components
htt
ps:
//d
ocs
.mic
roso
ft.c
om
/en-
us/
sql/
inte
grat
ion
-ser
vice
s/w
hat
-s-n
ew-i
n-i
nte
grat
ion
-ser
vice
s-in
-sq
l-se
rver
-20
17
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
rke
r Se
rvic
e
Worker
Wo
rke
r 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!!
Scale Out – Check the Environment (SSISDB)
SSIS Scale Out Workers
Execute in Scale Out
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
Configuration Options
Scale Out – Configuration Options
C:\Program Files\Microsoft SQL Server\140\DTS\Binn
WorkerSettings.configMasterSettings.config
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
rke
r Se
rvic
e
Package Executions
certificates
w2Mconfiglogs
Worker
Wo
rke
r Se
rvic
e
Package Executions
certificates
wxMconfiglogs
SSIS Scale Out – Santa, can you hear me?
https://www.pexels.com/photo/santa-clause-figurine-41963/
License / Edition ?
Cross Platform?
Parallel Foreach Container
SSIS Scale Out – Santa, can you hear me?
https://www.pexels.com/photo/santa-clause-figurine-41963/
Initial configurationcertificates workflow
Centralized dashboardWorker stateRunning, pending, .. tasksCurrent performance situationCentralized loggingLogging in error state (network)
Manageability„grouping“ of Workers(i.e. third-party components)~Tags in VSTS Builds
Scale-Out with SSIS 2017
@wstrasser workingondata.wordpress.com
Wolfgang Straßer
PLEASE FILL IN EVALUATION FORMSFRIDAY, MAY 19th SATURDAY, MAY 20th
https://survs.com/survey/cprwce7pi8 https://survs.com/survey/l9kksmlzd8
YOUR OPINION IS IMPORTANT!
THANK YOU TO OUR SPONSORSPLATINUM
GOLD SILVER