1 / 33

Moving a 100TB Application from Oracle to Microsoft SQL Server

DBI311. Moving a 100TB Application from Oracle to Microsoft SQL Server. Praveen Srivatsa Director, Asthrasoft Consulting Microsoft Regional Director and MVP. Agenda. NMS (Large Load) Case Study Comparison between Oracle and SQL implementation Scaling Points and Optimizations

jaunie
Download Presentation

Moving a 100TB Application from Oracle to Microsoft SQL Server

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. DBI311 Moving a 100TB Application from Oracle to Microsoft SQL Server Praveen Srivatsa Director, Asthrasoft Consulting Microsoft Regional Director and MVP

  2. Agenda • NMS (Large Load) Case Study • Comparison between Oracle and SQL implementation • Scaling Points and Optimizations • Async operations with Service Broker • Distributing data with Table partitions • Data Compression • Other optimization options

  3. Case StudyExisting NMS application • The NMS application collects, logs and analyzes the logs of various digital equipment in the network • Used for mobile towers, wi-max networks, wifi-networks and data centers • It’s agents collects logs 24x7 from all equipment and pumps the same to a central store. • The logs are analyzed for security breaches or attacks as well as to monitor the health of the various equipment.

  4. Existing NMS application Collect the data Analyze the data Log and Report Agent Log Manager Agent

  5. Existing NMS application 25K r/s == 12.5 MB/s 1.5M r/min == 750 MB/min 90M r/hr == 45 GB/hr 2.16B r/day == 1.08 TB/day 0.5 KB/record 2500 records/ agent 10 agents = 25K r/s (12. 5 MB/s) Agent Log Manager Agent Reporting on 100 days of data

  6. Case StudyCurrent architecture with Oracle 3. Data is indexed and analyzed RAW TABLES 2. Oracle BULK Loads data 4. Materialized Views are created for reporting Reporting Dashboard 1. Agents collect data and write to CSV

  7. Case StudyServer Configurations • App Server • 2 Quad Core (8 Cores) • 32 GB RAM • 1TB HDD • Redhat LINUX • Python based App • DB Server • 2 Quad Core (8 Cores) • 32 GB RAM • 1 TB HDD • Solaris 10 • Oracle R2 10G • 200 TB External storage on RAID 5 (120 TB usable)

  8. Case StudyKey Challenges • Bulk Data Import is very fast, but the data is not ready for analysis or reporting till indexes/materialized views are built • With the data inserts going on, the indexing and analysis is really slow – so differed to end of the day when a new table is created (EOD : 13 hrs) • Data querying for reporting now has to be done across multiple tables (upto 100 tables with a UNION) adding a lot of overhead • LAG from data collection to reporting was anywhere from 13 hrs to 37 hrs

  9. Case StudyMoving to SQL Server SERVICE BROKER QUEUES Compressed Tables Partitioned Tables WCF Reporting Dashboard

  10. Case StudyMoving to SQL Server Collection with data co-relation 33 records/sec (2.85 M/day) WCF Reporting Dashboard

  11. Case StudyMoving to SQL Server SERVICE BROKER QUEUES Collection with differed co-relation Batch of 100 recs 25 batches/sec/Q (2500/s) X 10 Queues (25000/s) 2.16 B/day WCF Reporting Dashboard

  12. Case StudyMoving to SQL Server SERVICE BROKER QUEUES Partitioned Tables Parallelized data co-relation 5 batches (100r)/sec = 500r/s X 5 Readers = 2500 r/sec/Q X 10 Q = 25000 r/s 2.16 B/day WCF Reporting Dashboard

  13. Case StudyMoving to SQL Server SERVICE BROKER QUEUES Compressed Tables Partitioned Tables Raw data saving due to compression 28% WCF Reporting Dashboard

  14. Case StudyServer Configurations • App Server • 2 Quad Core (8 Cores) • 32 GB RAM • 500GB HDD • Windows Server 200R2 • WCF Based Collector • Python App for reporting • DB Server • 2 Quad Core (8 Cores) • 64 GB RAM • 500GB HDD • Windows Server 2008R2 • SQL Server 2008R2 200 TB External storage on RAID 5 (120 TB usable) on Windows Storage Server

  15. Case StudyKey Benefits with SQL Server • Data transfer was continuous and could be parallelized using more queues • Querying and analysis did not slow down the data collection • More complex analysis and reporting could be added onto the logs • Lag between collection and reporting came down to 1hr 12 mins

  16. Case StudyAdditional Benefits with SQL Server • Historical data was compressed, reporting data was uncompressed, but partitioned • DB Snapshots every hour provided a shorter window of analysis and reporting • CLR Procedure provided advanced rules operations on the data packets

  17. NMS Walkthrough End to End walkthrough of the simulated app demo

  18. Scale PointsService Broker Advantages • Is a queued operations like MSMQ or MQSeries • Scales for rapid inserts • Can create multiple parallel queues • Can use activation to process the queue • Gives a guaranteed onetime only delivery • Can transmit data across DB instances

  19. OptimizingService Broker • Configure multiple packets per conversation • Bulk load multiple records into a single message • Transform and bulk insert from SSB XML to table • Optimize packet size of SSB Queue

  20. Service Broker Demo Optimizing SSB performance demo

  21. Scale PointsTable Partitions and Compressions • Partition • Separates logical units of data without changing code • Allows fast archiving with the SWITCH Partition operation • Allows partition level management rather than at table • Compression • Compressed 20-35% data • Loading data, backups, traversal over network all benefitted • Page-Row level configurations possible

  22. OptimizingTable Partitions and Compressions • Partition • Create a logical partition switch to parallelize inserts • Align partitions to number of disks and procs • Align indexes to the table partitions • Compression • Enable compression at a page level • Plan table row size in alignment with the page size

  23. Table Partition Demo Inserting into multiple partitions in parallel demo

  24. Scale PointsOther tweaks • DB Snapshots • Gives a point-in-time read-only view of the data • XML operations • Fast and easy XML Bulk operations on SSB Queue data • CLR Procedures • Compiled procedures for co-relation and alerts

  25. Summary • Key benefits of moving to SQL Server from the current Oracle architecture included • A shorter lag for critical reporting (from 13-37hrs to about 75 min) • A scale-out model allowing for even greater scalability • An integrated set of technologies (relational database, queuing technologies, compression options, reporting solutions) to work with

  26. Takeaways • SQL Server leverages a scale out model • It does this by using parallel operations at multiple levels • SQL Service Broker provides a robust queuing mechanism • Async queues provides a horizontal scale operations even across multiple databases • Table Partitions allows tables to scale to billions of records • Effective segregation allows single tables to handle large volumes of data effectively • Leveraging SQL Server features • DB Snapshots provides a point in view of the data • Compression allows us optimize our data storage

  27. Resources • Architecting Service Broker Applications http://technet.microsoft.com/en-us/library/aa964144.aspx • Partitioning Tables and Indexes http://msdn.microsoft.com/en-us/library/ms345146 • My website, blog and EMail web : http://www.asthrasoft.com blog : http://studidesk.asthrasoft.com/srivatsapraveen email : srivatsapraveen@asthrasoft.com

  28. Required Slide Speakers, please list the Breakout Sessions, Interactive Discussions, Labs, Demo Stations and Certification Exam that relate to your session. Also indicate when they can find you staffing in the TLC. Related Content • DBI307 | Automating database migration to Microsoft SQL Server • DBI315 | Microsoft SQL Server in virtualization and private cloud • DBI330 | Can your BI Solutions scale?

  29. Required Slide Track PMs will supply the content for this slide, which will be inserted during the final scrub. Database Platform (DAT) Resources • Visit the updated website for SQL Server® Code Name “Denali” on www.microsoft.com/sqlserverand sign to be notified when the next CTP is available • Follow the @SQLServer Twitter account to watch for updates • Try the new SQL Server Mission Critical BareMetal Hand’s on-Labs • Visit the SQL Server Product Demo Stations in the DBI Track section of the Expo/TLC Hall. Bring your questions, ideas and conversations!

  30. Resources • Connect. Share. Discuss. http://northamerica.msteched.com Learning • Sessions On-Demand & Community • Microsoft Certification & Training Resources www.microsoft.com/teched www.microsoft.com/learning • Resources for IT Professionals • Resources for Developers http://microsoft.com/technet http://microsoft.com/msdn

  31. Complete an evaluation on CommNet and enter to win!

More Related