1 / 22

Sundio Group SQL Server 2012 TAP Deployment Lessons Learned

Sundio Group SQL Server 2012 TAP Deployment Lessons Learned. Bas Bruijninckx (Sundio Group) Koen Reijns ( Sundio Group) Justin Langford (Coeo). INTRODUCTION. Sundio Facts & Figures. Sundio Strategy. IT Challenges. Seasonal business pattern €400k/ day to €4 million/ day 24hr business

kendis
Download Presentation

Sundio Group SQL Server 2012 TAP Deployment Lessons Learned

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Sundio GroupSQL Server 2012 TAP DeploymentLessons Learned Bas Bruijninckx (Sundio Group) Koen Reijns (Sundio Group) Justin Langford (Coeo)

  2. INTRODUCTION

  3. Sundio Facts & Figures

  4. Sundio Strategy

  5. IT Challenges • Seasonal business pattern • €400k/ day to €4 million/ day • 24hr business • Massive amounts of data • Performance is business key success factor • Duplicate data for scalability

  6. SQL Server Environment 1 2 3 4

  7. SQL Server 2012 Rationale • Already invested in platform stability, risk-ready • Skip SQL 2008 – one upgrade to SQL 2012 rather than migration path 2005-2008-2012 • Scale-out reporting to AlwaysOn replica • Off-load reporting, reduce OLTP contention • Utilize standby hardware • Recognized as an advanced technology company • Closer to Microsoft

  8. Project PlaN

  9. Planning • Hardware upgrade • New SAN / Blade servers • SQL Upgrade • Basic preparations done for SQL 2008 upgrade • Preparations started in February 2011

  10. Planning • Restoring databases from SQL2005 to SQL2012 CTP version • Upgrade SSIS packages • Preparing connection strings & load balancer • Basic testing • Baseline performance test new hardware • Running SSIS packages • Applications – basic functions

  11. Planning • Performance testing • SQLCAT performance lab • Focus: Price and availability engine • Push the configuration to the max

  12. Planning • Functional testing • User testing • Vendor support • Back-office process • Jobs • Price& Availability engine • Replication testing

  13. Planning • Upgrade (November 5th) • One 4-node cluster • Big bang upgrade • Temporary front-end servers • Post migration support • Upgrade to RC1 (mid-February)

  14. Lessons Learned

  15. Pre-Upgrade • Installation procedure • CTP3 + Refresh install slow* • 5 instances on 4 nodes (20 installs) • *Fixed in RC0 (good experience with RC1 in test) • Challenges with CT3 in-place upgrade • SSIS Bugs • Password retention • Deadlocks with self • Job step properties

  16. Pre-Upgrade • AlwaysOn AG • Async readable secondary • 4-node cluster • Primary and replica may not co-exist on same node • Control failover via Possible owners & anti-affinity • Use T1448 to avoid AlwaysOn latency affecting Log Reader performance

  17. Pre-Upgrade • SAN storage performance • Acceptance criteria • Max Avg read/ write latency <20ms • Target Avg read/ write latency <10ms • Significant challenges reaching target performance • Persisted with EMC and performance improved • Necessary to fix pre-go-live

  18. Post-upgrade • Replication • Heavy users of transactional replication • Utilise SP replication to improve performance • Transaction isolation READ UNCOMMITTED not supported with repl’d SPs from SQL 2008 onwards

  19. Database Administration • Used native backup compression • Previously using Quest Litespeed • Problem: lack of disk space • Enable T3042 to avoid pre-allocation

  20. Spinlock Diagnosis: SOS_OBJECT_STORE Higher CPU with minimal increase in throughput 1 3 • Used Xevents and spinlock_stats to identify XVB structure as the source of the contention and involved CSS and product team via RFC. • After investigation team provided private build to test Observed close to ~80 million spins and 8-10K backoffs per minute on SOS_OBJECT_STORE (below is from 13.3 minute period) 2

  21. Conclusion • SQL Server 2012 platform - supports business growth • Benefits from the TAP program • Working closer with Microsoft • Improved quality of released product (High CPU issue solved in CU1) • Work ahead • RTM upgrade • Upgrade other environment and BI platform to SQL 2012 • Deploy CU1 for contention fix

  22. twitter Bas Bruijninckx @basbrx Koen Reijns @koenreijns Justin Langford @justinlangford Q&A

More Related