330 likes | 481 Views
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
E N D
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 • Async operations with Service Broker • Distributing data with Table partitions • Data Compression • Other optimization options
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.
Existing NMS application Collect the data Analyze the data Log and Report Agent Log Manager Agent
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
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
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)
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
Case StudyMoving to SQL Server SERVICE BROKER QUEUES Compressed Tables Partitioned Tables WCF Reporting Dashboard
Case StudyMoving to SQL Server Collection with data co-relation 33 records/sec (2.85 M/day) WCF Reporting Dashboard
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
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
Case StudyMoving to SQL Server SERVICE BROKER QUEUES Compressed Tables Partitioned Tables Raw data saving due to compression 28% WCF Reporting Dashboard
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
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
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
NMS Walkthrough End to End walkthrough of the simulated app demo
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
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
Service Broker Demo Optimizing SSB performance demo
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
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
Table Partition Demo Inserting into multiple partitions in parallel demo
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
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
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
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
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?
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!
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