Commodity Database Servers Jim Gray Microsoft Research GrayMicrosoft

Commodity Database Servers Jim Gray Microsoft Research Gray@Microsoft. com http: //Research. Microsoft. com/~Gray/talks 1

Outline • Status report on Commodity Server Performance • Why Most VLDBs will be Multi-Media Servers • Preview of Microsoft’s SQL Server 7 2

Status Report on Commodity Server Performance • Standards: – TPC, – Spec. Web, . . . • Product benchmarks: e. g. – SAP, – People. Soft, … • Both indicate that – NT is 18 months behind Unix-SMP performance – but clusters can make up the difference 3

TPC-C SMP • HP 9000 16 cpu, Sybase 11 52. 1 ktpm. C, 82$/tpm. C • NEC 8 cpu SQL Server 14. 9 ktpm. C, 60$/tpm. C Cluster • IBM SP 2 12 x 8 cpu Oracle 8. 2 57 ktpm. C, 148$/tpmc • Predict: large & inexpensive NT cluster number this year. 4

TPC-D • Performance Champions: NCR/Teradata – 1 TB: 32 x 4 node clusters – 300 GB: 24 x 4 node cluster – 100 GB: 8 x 4 cluster • All use Teradata software on NCR World-Mark Intel-based hardware 5

Outline • Status report on Commodity Server Performance • Why Most VLDBs will be Multi-Media Servers • Preview of Microsoft’s SQL Server 7 6

VLDB Reality Test • California DMV – ~ 20 million cars, drivers, doctors, barbers, . . – Some drivers have moving violations – DMV knows about 1. 5 KB about each one – 30 GB total. • Microsoft: too big says Do. J – 40 B$ revenue (in company life time) – ~1 billion unit sales: @ 100 B = 100 GB – ~100 M customers: @1 KB = 100 GB • Wall Mart (no one bigger!) – Sells 10 B items per year – 100 bytes/item => 1 TB • ATT – 300 M calls per day (peak day) – 10 B calls per year – 100 b/call = 1 TB 7

VLDB Reality Test • Its HARD to find 1 TB of transaction data – 100 M web hits/day – 250 B/hit – 1 TB/year • Its HARD to find 1 TB of text data – 100 M web pages – 10 KB/page – = 1 TB • How do they do it? • Lots of indices? – No: that is only 3 x • Precomputed Aggregates? – Yes: OLAP benchmark • Start at 30 MB • Use 2. 7 GB or 6 GB database – But: this is dumb • Email? – Microsoft: 6 TB – Hotmail: 3. 5 TB – AOL? 8

Data Tidal Wave • Seagate 47 GB drive @ 3 k$ – 100 GB penny per MB drive coming in 2000 • 10 $/GB = 10 k$/ Terabyte! (in y 2 k) – Everyone can afford one • What’s a terror bite? – If you sell ten billion items a year (e. g Wal-Mart) – And you record 100 bytes on each one – Then you got a Terror Bite • Where will the terror bytes come from? – Multimedia (like the Terra. Server) and. . . 9

Multi Media: Very Large DBs • Photo is 100 KB, not 100 B – So, photo DBs are 1, 000 x larger • Examples: – Scanned documents – Photo records of products/people/places – Surveillance – Scientific monitoring 10

Some Terror. Byte Databases • EOS/DIS (picture of planet each week) – 15 PB by 2007 • Federal Reserve Clearing house: images of checks – 15 PB by 2006 (7 year history) • Sloan Digital Sky Survey: – 40 TB raw, 2 TB cooked • Terra. Server: 11

Scaleup - Big Database • Build a 1 TB SQL Server database – Show off Windows NT and SQL Server scalability – Stress test the product • Data must be – – 1 TB Unencumbered Interesting to everyone everywhere And not offensive to anyone anywhere • Loaded – 1. 1 M place names from Encarta World Atlas – 1 M Sq Km from USGS (1 meter resolution) – 2 M Sq Km from Russian Space agency (2 m) • Will be on web (world’s largest atlas) • Sell images with commerce server. 12

Terra. Server World’s Largest PC! • 324 disks (2. 9 terabytes) • NT EE & SQL 7. 0 • 8 x 440 Mhz Alpha CPUs • Photo of the planet USGS and Russian images • 10 GB DRAM 13

Background • Someday • Earth is 500 Tera-meters square – USA is 10 tm 2 • 100 TM 2 land in 70ºN to 70ºS • We have pictures of 6% of it • • – 3 tsm from USGS – 2 tsm from Russian Space Agency Compress 5: 1 (JPEG) to 1. 5 TB. Slice into 10 KB chunks Store chunks in DB Navigate with – Encarta™ Atlas • globe • gazetteer – Streets. Plus™ in the USA – multi-spectral image – of everywhere – once a day / hour 1. 8 x 1. 2 km 2 tile 10 x 15 km 2 thumbnail 20 x 30 km 2 browse image 40 x 60 km 2 jump image 14

USGS Digital Ortho Quads (DOQ) • US Geologic Survey • 3 Tera. Bytes • Most data not yet published • Based on a CRADA – Terra. Server makes data available. 1 x 1 meter 4 TB Continental US New Data Coming USGS “DOQ” 15

Russian Space Agency(Sov. Infom. Sputnik) SPIN-2 (Aerial Images is Worldwide Distributor) • • • 1. 5 Meter Geo Rectified imagery of (almost) anywhere Almost equal-area projection De-classified satellite photos (from 200 KM), More data coming (1 m) Want to sell imagery on Internet. Putting 2 tm 2 onto Terra. Server. SPIN-2 16

Demo http: //www. Terra. Server. com Microsoft Back. Office SPIN-2 17

Hardware 1 TB Database Server Alpha. Server 8400 4 x 400. 10 GB RAM 324 Storage. Works disks 10 drive tape library (STC Timber Wolf DLT 7000 ) SPIN-2 18

Software Image Server Active Server Pages Web Client Internet Information Server 4. 0 Java Viewer broswer MTS Terra-Server Stored Procedures HTML The Internet Info Server 4. 0 Sphinx (SQL Server) Microsoft Automap Active. X Server Terra-Server DB Automap Server Terra-Server Web Site Internet Information Server 4. 0 Microsoft Site Server EE Image Delivery SQL Server Application 7 19 Image Provider Site(s)

System Management & Maintenance Backup and Recovery – STC 9717 Tape robot – Legato Net. Worker™ – Sphinx Backup/Restore Utility – Clocked at 80 MBps!! SQL Server Enterprise Mgr – DBA Maintenance – SQL Performance Monitor 20

Terra. Server File Group Layout • Convert 324 disks to 28 RAID 5 sets plus 28 spare drives • Make 4 NT volumes (RAID 50) 595 GB per volume • Build 30 20 GB files on each volume • DB is File Group of 120 files E: F: G: H: 21

Gazetteer Design • Classic Snowflake Schema • Fast First hint to Optimizer 22

Image Data Design • Image pyramid stored in DBMS (250 M recs) 23

Image Delivery and Load DLT Tape “tar” NT Do. Job Drop’N’ Load. Mgr DB Wait 4 Load Backup Load. Mgr ESA Alpha Server 4100 100 mbit Ether. Switch 60 4. 3 GB Drives Alpha Server 4100 Img. Cutter Drop’N’ Images . . . 10: Img. Cutter 20: Partition 30: Thumb. Img 40: Browse. Img 45: Jump. Img 50: Tile. Img 55: Meta Data 60: Tile Meta 70: Img Meta 80: Update Place Enterprise Storage Array STC DLT Tape Library 108 9. 1 GB Drives Alpha Server 8400 24

SQL 7 Testimonial • We started using it March 4 1997 – – SQL 7 Pre-Alpha SQL 7 Alpha SLQ 7 Beta 1 SQL 7 Beta • Loaded the DB twice – (we made application mistakes) • • Now doing it “right” Reliability: Great! SQL 7 never lost data Ease of use: Great! Functionality: Great! 25

Outline • Status report on Commodity Server Performance • Why Most VLDBs will be Multi-Media Servers • Preview of Microsoft’s SQL Server 7 26

SQL 7: Easy & Functional u Easy u u u Scalability u u u Data Warehousing u u Dynamic self management Multi-site management Alert/response management Job scheduling and execution Scriptable management profiling/tuning tools Fully Unicode English Language Query Integrated text search engine 27

Made It Easier! (fewer knobs) • Desktop & Workgroups – Auto Configure Engine / Dynamic Disk/memory – Reduce Learning Curve, Increase Productivity – Self-Managing SQLAgent, Wizards, “Task Pads” • Large Organizations – Deploy/manage hundreds of SQL Servers – Lower TOC for Large Environments – Multi-Server Operations/ “Lights-out” Environment 28

Multi-Site Management • Admin servers from one place • Automate simple stuff • Wizards for common stuff • Manage arrays of servers – operations, security, … – Replication – Import/export • Interface is scriptable – COM object model – Script with Java, VB, . . . • Scheduling and Multi-step jobs 29

DBA and Developer Tools • Built-in GUI – data/schema design – data query & edit – intgrated with programming tools • SQL Server Profiler – Selected server events and trace criteria – “Capture” output to screen or replay • SQL Server Expert – – Analyzes actual server usage history Makes recommendations to improve performance Recommends Index design Recommends operations procedures 30

• • Wizards and GUIs Wizards galore (over 50 at last count) MS Access as a query interface Built-in data access tools (integrated with tools) Graphical show plan 31

Many New Wizards. . . • • • Create a Database Scheduled Backup Create a Maintenance Plan Create a Scheduled Job Create an Alert Security Wizard Import Data to SQL Server Export Data From SQL Server Clustering (Wolfpack) Index Tuning Wizard - Web Assistant Register Servers Configure Replication Create Publication Create Pull Subscription Create Push Subscription Replication Partitioning Create an Index Create a Stored Procedure Create a View More to come. . . 32

Distributed Management Objects (SQL-DMO) • COM Interfaces for administering SQL Server – Embedded Administration (no UI) • All Administration Functions Supported – Server, Database Configurations, Settings – Object Creation, Security, Replication, Scripting, . . – 40+ Objects, 1000+ properties and methods • Integration Interface for ISV Administration – I. e. , Baan using DMO for Scripted App Install • Scripting Via VBA and Jscript + DCOM 33

DMO: Object Model (Overview) SQL Server SQLAgent Databases Jobs Users Tasks DB Options Alerts Transaction Logs Operators Publications File. Groups Files Logins Configurations Linked Servers Remote Login Table View Columns Stored Procs Indexes Rules Keys (PK/FK) Defaults Triggers 34

DMO Scripting • Backup a Database Set My. Server = Create. Object("SQLDMO. SQLServer")‘Create Server Object Set My. Backup = Create. Object("SQLDMO. Backup") ‘Create Backup Object My. Server. Name = “MSSALES” My. Server. Login. Secure = True My. Server. Connect ‘ Identify Server ‘ Windows NT Auth ‘ Connect My. Backup. Database = ”SALESII” My. Backup. Files = "\My. ServerBackups" _ + My. Backup. Database +”. bak” My. Backup. SQLBackup My. Server ‘ Database to backup ‘ Backup Location ‘ Name Backup File ‘ Back it Up My. Server. Disconnect ‘ We’re Done! 35

Scalability u u u Data Warehousing u Easy Win 9 x/NTW version Dynamic row-level locking Improved query optimizer Intra-query parallelism 64 -bit support Replication Distributed query High Availability Clusters 36

Scale Down to Windows 95 -98 • • • Full function (same as NTW) Self managing Many tools Integration with Next MS Access Great for imbedded apps 37

Replication • • • Transactional and Merge Remote update ODBC and OLE DB subscribers Wizards Performance OS 390 VSAM DB 2 Subscriber CICS Publisher 2 PC, RPC Distributor DB 2 Subscriber Updating Subscriber (immediate updates) 38

Parallel Query SMP & Disk Parallelism Global Agg. + Result 50 rows 4 x 50 rows Local Agg. + + Disks • Plus Distributed • Plus Hash Join (fanciest on the planet) • Plus Optimized Partitioned views 50, 000 rows • # of emp. per group • total inc. per group 39

Distributed Heterogeneous Queries Data Fusion / Integration Join spread sheets, databases, directories, Text DBs etc. Any source that exposes OLE DB interfaces SQL Server as gateway, even on the desktop Directory Service Database (DB 2, VSAM, Oracle, …) Spreadsheet SQL 7. 0 Query Processor Photos Mail Maps Documents 40 and the Web

Utilities The Key to LARGE Databases • Backup – Fuzzy – Parallel – Incremental – Restartable • Recovery – Fast – File granularity • Reorganize – shrinks file – reclusters file • Auto-repair 41

Data Warehousing Easy u u Scalability u u Data Warehousing u Warehousing Framework Visual data modeler Microsoft repository Data transformation services (DTS) Plato & Dcube - Multi Dimensional Data Cubes English query 2. 0 Built-in text-index engine 42

Key Microsoft Data Warehouse Programs • Data Warehouse Framework (DWF) – Process -- for building, using and managing – Pipeline -- for metadata flow – Protocols -- to integrate components • Data Warehouse Alliance (DWA) – Partners -- ISVs pledged to the framework and its parts – Products -- complete spectrum from Microsoft and third -parties 43

Microsoft Data Warehousing Framework Using Data Warehouse Design Data Mart Design** (logical/physical schema*/ data flow**) (Cubes/Star schema) End-User Tools OLE-DB** Managing Building (Excel**, Access, English Query) Operational Data (OLE-DB **) DB Schema** Data Transformations (DTS**) Transformation** Data Marts (SQL Server** & OLAP Server**) Scheduling OLAP Microsoft Repository** (Persistent Shared Meta-Data) Data Warehouse Management (Console*, Scheduling**, Events**, Topology*, ) ** available in SQL Server 7 (* partially) Data Flow Meta-Data 44 Flow

Alliance for Data Warehousing Technical and marketing relationship Supports SQL Server storage engine Third-party products tested with Back. Office DW Build BMC Data Mirror Execusoft Informatica Microsoft Platinum Technology Praxis Prism Sagent SAS Sterling V-Mark DW Access Andyne Business Objects Cognos IQ Software Microsoft NCR Data Mining Pilot Platinum Technology Sagent SAS Seagate Wall Data 45

DW Alliance Milestones • • 9/96 - Launched with 8 founding members 3/97 - Design review 1/97 - 6/97 - Expanded to 21 members 7/97 - Repository design review – Team development of shared metadata • 9/97 - OLE DB for OLAP API specification • 1 H’ 98 - Integration development with Sphinx DTS and Replication APIs 46

Microsoft Repository • • Based on joint Sterling/Microsoft design (Shipped 97 Q 2) Wide distribution: VB, Visual Studio and Third-Parties Designed with over 60 vendors Extended to support DB schema, transformations, OLAP – Key element of the DW Framework • UML is abstract model • Everything viewable in UML terms UML UMX CDE DTM COM GEN DBM SQL OCL UML Unified Modeling Language GEN Generic UMX Uml Extensions DBM Database Model CDE Component Descriptions SQL Microsoft SQL Server COM Component Object Model OCL Oracle DTM Data Type Model 47

Repository & Data Warehousing • Common infrastructure -- the meta-data pipeline • Supports interoperability between data warehousing tools and products • Process: – Initial spec developed with 12 vendors – Gathering feedback now – Final spec review in Redmond, 2/98 48

Data Transformation • Workflow system manages Data Pump – Pre-defined transforms using the DTS GUI – Procedural VB Script, Java. Script, VBA, any COM • Multi-stream in, Multi-stream out Repository Metadata Transforms Oracle > SQL Server Transformation Objects Active. X Scripts Function Example() Transform() If DTSSource(“Credit. Rating”) = “ 1” then DTSDestination(” Risk ") = ”Good" Else If DTSSource(”Credit") = ” 2” DTSDestination(” Risk ") = ”Average” Else If DTSSource(”Credit") = ” 3” DTSDestination(” Risk ") = ”Bad” Else Example = DTS_Skip. Row End if End Function SQLAgent Multiserver Operations IDTSData. Pump IUnknown Data Pump 49

Transformations • Data quality and validation – Missing values, scrubbing, exception handling • Data integration – Heterogeneous query, join keys, elim. dups • Transforms – Combine/decompose multiple columns to one • Aggregation • Central metadata – Business rules, data lineage 50

ROLAP Hybrid • Debates between MOLAP and ROLAP vendors obscure customer needs • Plato is the product that best supports MOLAP, ROLAP and Hybrid and offers the most seamless integration of all three • Users & apps only see cubes MOLAP Flexible Architecture User View Data load Persistent Store User View Data access MD Cache User View MD Cache 51

Plato and Dcube and HOLAP CH EV Y RD By Year 0 199 991 1 992 1 993 1 By Make & Year RED WHITE BLUE Source table Europe FO By Color & Year Sum Partition 1 By Color “Plato” User 1 SQL Asia Partition 2 MD SQL Client app “Plato” server Partition 3 ROLAP Dcu b e USA Designer Dcu b e ROLAP Client app User 2 52

Product Family Product Month Quarter Product Family How Plato Handles Data Explosion Products Month Aggregation Wizard finds the aggregations that feed the most other aggregations Fact Table 53

How Plato Handles Data Explosion • Aggregation Wizard finds the “ 80 -20” rule in the data – The 20 percent of all possible pre-aggregations that provide 80 percent of the performance gain – Analyses level counts for each dimensions and parent-child ratios for each level • Independent of OLAP data model 54

OLE DB For OLAP • OLE DB extensions to access MD data – Part of OLE DB 2. 0 • • • One new object: Dataset Enhancements to existing objects Heavily leverages OLE DB 55

OLE DB For OLAP Objects And Interfaces Co. Create. Instance Command Enumerator Flattened Rowset Data source Dataset Session Range Rowset Schema Rowsets 56

English Query 57

OBJECT RELATIONAL The Next Great DBMS Wave • • • All the DB vendors are adding objects Microsoft is adding DBs to Objects Integration with COM+ Gives user-defined types and objects Plug-ins will be Billion dollar industry – Blades for SQL Server razor 58

Outline • Status report on Commodity Server Performance • Why Most VLDBs will be Multi-Media Servers • Preview of Microsoft’s SQL Server 7 59