scale out - wordpress.com · scale-what? scalability is the capability of a system, network, or...
TRANSCRIPT
Scale Outmit SSIS 2017
Wolfgang [email protected]
@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
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