sundio group sql server 2012 tap deployment lessons learned n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Sundio Group SQL Server 2012 TAP Deployment Lessons Learned PowerPoint Presentation
Download Presentation
Sundio Group SQL Server 2012 TAP Deployment Lessons Learned

Loading in 2 Seconds...

play fullscreen
1 / 22

Sundio Group SQL Server 2012 TAP Deployment Lessons Learned - PowerPoint PPT Presentation


  • 130 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Sundio Group SQL Server 2012 TAP Deployment Lessons Learned' - kendis


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
sundio group sql server 2012 tap deployment lessons learned

Sundio GroupSQL Server 2012 TAP DeploymentLessons Learned

Bas Bruijninckx (Sundio Group)

Koen Reijns (Sundio Group)

Justin Langford (Coeo)

it challenges
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
sql server 2012 rationale
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
planning
Planning
  • Hardware upgrade
    • New SAN / Blade servers
  • SQL Upgrade
    • Basic preparations done for SQL 2008 upgrade
    • Preparations started in February 2011
planning1
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
planning2
Planning
  • Performance testing
    • SQLCAT performance lab
      • Focus: Price and availability engine
      • Push the configuration to the max
planning3
Planning
  • Functional testing
    • User testing
    • Vendor support
  • Back-office process
    • Jobs
    • Price& Availability engine
    • Replication testing
planning4
Planning
  • Upgrade (November 5th)
    • One 4-node cluster
    • Big bang upgrade
    • Temporary front-end servers
  • Post migration support
  • Upgrade to RC1 (mid-February)
pre upgrade
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
pre upgrade1
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
pre upgrade2
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
post upgrade
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
database administration
Database Administration
  • Used native backup compression
    • Previously using Quest Litespeed
    • Problem: lack of disk space
    • Enable T3042 to avoid pre-allocation
spinlock diagnosis sos object store
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

c onclusion
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
slide22
twitter

Bas Bruijninckx @basbrx

Koen Reijns @koenreijns

Justin Langford @justinlangford

Q&A