1 / 33

Consolidation

Explore the case study of a major insurance company FX in Scandinavia that embarked on a SQL Server consolidation project. Learn about the challenges faced due to a growing SQL environment and exploding costs. Discover the strategies employed to create an overview of the existing infrastructure, choose the right consolidation methodology, and migrate to SQL Server 2008 to establish a solid proof of concept. Follow the journey through project phases, from simplification to elite level database migration, to enable a private cloud infrastructure.

iwilliams
Download Presentation

Consolidation

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. Consolidation

  2. Who am i? Raoul Illyés • Technical director and partner at Guide-line. • More then 16 years of experience as an consultant. • Specialized in performance tuning, high end systems. • SQLSTAT2005, Perfmonstuff, RTA (SQLCAT). • rai@guide-line.com • Mobil: +45 27831523Web: http://guide-line.com/Blog: http://guide-line.com/archives/category/raouls-corner

  3. Consolidation A Nordic consolidation project on SQL Server 2008 (How to build a private cloud.)

  4. The customer • Company name: IF • Line of business: Insurance • Company size: Biggest one in all of Scandinavia. • Date and Time: Early spring, 2009

  5. The setup Customer Hosting partner Consumer Of IT-Services Provider Of IT-Services

  6. The challenge • Continuously growing SQL environment. • Lack of overview, data provided was static • Continuously growing cost: • Hardwareincrease • Storagerequirements exploding • Licensingbomb a head • Operationsgetting out of hand • A large number of unsupported versions of SQL Server

  7. SQL Server Infrastructure ?

  8. Getting started Create an overview on existing SQL Server infrastructure Decide on which consolidation methodology to use Decide on which platform to consolidate on ….Everything should end up in a solid POC

  9. Getting started 1. How to create an overview? • Quest Discovery Wizard for SQL Server – Beta FREE • http://www.quest.com/discovery-wizard-for-sql-server/ • Very database centric tool • SQLH2 FREE • http://sqlh2.codeplex.com/ • Very flexible, open source but requires tweeking and time. • MAP Toolkit Office licens required • http://technet.microsoft.com/en-us/solutionaccelerators/dd537566.aspx • Very big, covers eveything, IT assessment tool

  10. Getting started 1. How to create an overview? • So we ended up in creating our own tool which we call. • Guide-line Inventory & Discovery toolkit • DEMO

  11. 2. How to choose the right methodology for consolidation? • Virtualization • Instance consolidation • Database consolidation Easy to implement vs. Biggest savings

  12. 3. How to choose the right platform for consolidation? • SQL Server 2005 or SQL Server 2008 • Windows 2003 or Windows 2008 • 32-bit or 64-bit (X64 or I64)

  13. Technology upgrade discussion SQL Server 2005 vs. SQL Server 2008 • Customer and hosting partner wanted SQL server 2005 • We as experts said SQL Server 2008 • ……..Why?

  14. Technology upgrade discussion SQL Server 2005 vs. SQL Server 2008 • Technology meeting the 25/3-09 • Limitations of SQL Server 2005 • No resource control • Scale-out product, not so consolidation friendly • Aggregated instrumentation only • Single instance only per license for SE • No built in support for compression (Data or backup) • Limited auditing • and no “hot-add” CPU or memory support.

  15. Technology upgrade discussion SQL Server 2005 vs. SQL Server 2008 • Technology meeting the 25/3-09 • Benefits of SQL Server 2008 • Built in resource control • Built for both Scale-out and consolidation in mind • 100 % instrumentation support • Multiple instances per license in SE • Built in support for compression, Auditing and Policy Management • “Hot-add” CPU and memory support • FILESTREAM and spatial data types support • Built on SQL Server 2005s stable engine

  16. Technology upgrade discussion Editions, Windows version and architecture • Technology meeting the 25/3-09 • SQL Server SE or EE • Windows 2003 or Windows 2008 • 32 vs. 64 or X vs. I architecture

  17. End resultsA solid POC built on • SQL Server 2008 • Windows Server 2008 • X64 Architecture • Both SE and EE hotels

  18. Project Phases • The project was divided into three phases • One: Simple • Create a central project site • Begin large scale resource consumption monitoring • Get hardware and configure SE environment. • Create Framework version 1.0 • Create support Tools version 1.0 • Begin migration of simple databases. • Two: Volume (Continue build on SE enviroment.) • Create Information services version 1.0 • Continue and create Framework version 2.0 • Continue and create support Tools version 2.0 • Migrate databases in large volumes • Three: Elite (60 % of human resources needed) • Get hardware and configure EE environment. • Create Framework 3.0 and other tools.. • Migrate high end databases • Create private cloud infrastructure

  19. Project leader 0,5 Hosting partner DBAs Kordinator Mix Application Operations Security Microsoft Applikation Developer 2 • Project Constellation Speciallist Architect Steering group 0,5 2,5

  20. Create a central project site

  21. Resource monitoring

  22. Setting up the hardware

  23. Why a framework?A 37 page document of rules and regulation

  24. Why the need for support tools? • Create databases • Restore databases • managing server traces

  25. Why create a Information Center?

  26. Information centerA central point for • SQL Server error logs • SQL Server Performance reports (Data collection data) • SQL Server Engine information • SQL Server Runtime information • SQL Server Database information • SQL Server Agent information

  27. Migration of databases • Upgrade advisor, saving results in a central location. • Auditing login information • Auditing for unsupported and deprecated features • Handing out a formula to all application owners • Creating a step-by-step script tool box for doing migrations • Experience was incorporated in later phases

  28. Migration of databasesThe Migration Bible 481 pages of law! SQL Server 2008 Upgrade Technical Reference Guide http://www.microsoft.com/downloads/en/details.aspx?FamilyID=66d3e6f5-6902-4fdd-af75-9975aea5bea7&displaylang=en

  29. How to create private cloud infrastructure • Through the use of: • Xevent • SQL Agent • SISS • Resource consumption data is consumed (aggregated) once a day. • Data for billing is delivered once a month • Same data is used for databse profiling and performance tuning • http://sqlcat.codeplex.com/wikipage?title=ExtendedEventsWaitstats&referringTitle=Home • DEMO

  30. Project end • A complete review from local MS resources • A Complete review of framework and tools • A brain storm session on what could have been done better. • Creation of ICC (If competence center)

  31. End result • In the beginning • 200 SQL servers ~4.000 databases • ~ 10 figured MSEK/year operational and license cost • The Project • ~ 10 figured MSEK project cost • The end result • 10 dedicated servers ~1.500 databases • Pure MS 2008 X64, SQL2008 (2005 trashcan instance) • Internal cost diversion /database based on consumption • ~80 % reduction in MSEK/year operational and license cost • No one have less capacity

  32. Q &A

  33. P/X001 The Developer Side of the Microsoft Business Intelligence stack Sascha Lorenz P/L001 Understanding SARGability (to make your queries run faster) Rob Farley P/L002 Notes from the field: High Performance storage for SQL Server Justin Langford P/L005 Service Broker: Message in a bottle Klaus Aschenbrenner P/T007 Save the Pies for Lunch - Data Visualisation Techniques with SSRS 2008 Tim Kent Coming up… • #SQLBITS

More Related