Transcript 411_-_Scaling_out_SSAS_-_Cambridge.ppsx

Stick around for SWAG! (Prize drawing at 5.00 p.m)
 All our volunteers and organisers do not get paid for running SPS SQLSaturday Cambridge
and do it because they believe in the power of community (or are just plain nuts).
Please show your gratitude for making this possible by:
 Giving them a hug
 Shaking their hand
 Saying thank you
 Coming back next year
 Consider getting your company to pay for a precon next year
 Speading the word
 Getting involved yourself
 But most of all, by enjoying the day!
 Don’t forget to thank the sponsors for their support
 Thank the speakers for donating their time, energy and expenses
SQLCloud — XBox One!!! (make sure you know how to play the Jet Set Willy vendor competition!)
SQLCloud—Dark Side Of The Moon, The Wall and Wish You Were Here
Microsoft — MCP Voucher
Data Idols — Supercar Driving Experience
Profisee — Signed MDS Book and £50 Amazon Voucher
SQLSentry — Plan Explorer PRO License
Pyramid Analytics—Coffee Machine
Effektor — Lego Big Ben
Rencore — SPCAF Professional License (valued at $2099!)
Axioworks — Amazon Fire TV and £75 Amazon Voucher
DELL — Beats by Dr Dre Headphones
Redgate — DLM Workshop
Pluralsight — Annual Plus subscriptions
All Your Base Conference — 3 x Conference Tickets
VisualSP—Annual training subscription
Loads of books and much much more!!!
 WHAT
 INTRODUCTION
 FROM THE TRENCHES
 SETUP
 WHAT’S NEXT
 INFRASTRUCTURE
 PLATFORM
 FROM THE TRENCHES
 Q&A
 Jens Vestergaard
 Maersk Line IT – MSBI – Engineering
 @vestergaardj
 http://www.t-sql.dk / [email protected]
 $5.200.000.000 GROUP PROFIT 2014 – One Pink Floyd song comes to mind…
 3.000.000 CONTAINERS
 300.000 REFRIGURATED (REEFERS)
 25.000 LAND BASED EMPL.
 7.000 AT SEA
 600 VESSELS (20 TRIPPLE-E’S) (11 EVEN BIGGER ONES TO COME)
 90+ COUNTRIES
 1 BI/MDM DEPARTMENT
 PLAN
 BUILD
 RUN
 DEV -> TEST -> PREPROD -> PROD
 <IMAGINE SOUND OF RECORD NEEDLE SCRATCHING>
 MSBI = (DEV/TEST) + (DEV/PROD) + (ADL+PREPROD)
 TODAY (AND FOR THE LAST 3,5 YRS)
 SSIS 2008 R2
 SSRS 2008 R2
 SSAS 2012
 SHAREPOINT 2010
 TFS 2010
 290 GB SINGLE PROJECT
 40 GB NEXT SIGNIFICANT PROJECT
 1 X LOAD BALANCER (F5)
 2 X 2 WEB APPLICATION
 2 X 2 SHAREPOINT APPLICATION
 1 X ASLB (CODEPLEX)
 4 X SSAS QUERY
 3 X SSAS PROCESSING
 1 X SQL AGENT/SSIS
 1 x TEAM FOUNDATION SERVER
 WHAT did we try?
 Classic Setup
 SAN Read-Only
 A read-only LUN can be shared between multiple servers, and hence enables you to use more than
query server on the same physical disk.
 Dedicated Processing Architecture
 WHAT did we NOT do?
 Client Load Balancing – Every Client Connects to a Specific Target Query Server
 Query/Processing Flipping Architecture
 Say What one more Time…
Ref: https://anoopsihag.wordpress.com/2012/01/21/analysis-services-scale-out-processing-architecture/
Ref: https://anoopsihag.wordpress.com/2012/01/21/analysis-services-scale-out-processing-architecture/
 Initial requirement was to add redundnacy to SPOF
 Improved initial active / passive setup to active / active
 Better use of hardware
 Scale-out enabled
 Two possible solutions
 Hardware load bancing (F5)
 Software load balancing (ASLB)
 Opted for software load balancing
 Based on ASLB from Microsoft (SQL CAT team). Open source.
 Pros
 Flexibility. Configuration per solution.
 Control. Software load balancing based on metadata.
 We can build a configuration interface – and we have
 Cons
 Known issue with PerformancePoint where we see intermittent errors. Have not
troubleshooted this yet.
 Complex kerberos delegation paths
 HTTP connector. Some overhead when moving large amounts of data
 Web front-ends
 Connection strings point here.
 E.g. http://msbi-preprod-as.apmoller.net/FBR_ProfitAndLoss
 Web front-ends are actually load balanced, but only for redundancy (active / passive)
 Analysis Services
 Each instance has the HTTP connector configured
 Meta data
 Web front-end checks meta data and HTTP redirects user
 Round robin and observing”enabled / disabled” (server or database), server avail and CPU load
 SQL Agent jobs validate server availability and CPU load and updates meta tables
 Deploy new database
 Done by updating the meta data in the ASLB database
 When a new entry has been added to dbo.Databases
 http://msb-preprod-as.apmoller.net/<<name from
ASLB.dbo.Databases>>
 http://msb-preprod-as.apmoller.net/<<name from
ASLB.dbo.Databases>>
 Enable / disable a database
 Done from SharePoint web interface.
 Operations Manager
 Orchestrator
 Virtual Machine Manager
 Advocating Hardware Load Balancing
https://twitter.com/Gavin592/status/539524567098982400
&
 BLOG: https://anoopsihag.wordpress.com/2012/01/21/analysis-services-scale-out-
processing-architecture/
 ASLB: http://www.microsoft.com/en-us/download/details.aspx?id=10556
 MSDMPUMP: https://msdn.microsoft.com/en-us/library/gg492140.aspx
 SYNC: https://msdn.microsoft.com/en-us/library/ms174928.aspx
 SCOM: http://www.microsoft.com/en-us/server-cloud/products/system-center-
2012-r2/Components.aspx