Microsoft SQL Server

1
Microsoft SQL Server 7.0Jim GraySenior
ResearcherMicrosoft ResearchMicrosoft
Corporation
2
Organizations Are Going Online

• Building a digital nervous system.
• Inexpensive hardware means huge databases are
  possible.
• But, we are drowning in data.
• Databases help organize information.
• Microsofts goal
• Information at your fingertips.
• Make it easy to capture, manage, and
  analyze information.

3
Microsoft SQL Server 7 Goals

• Reduce cost of ownership
• Easy to build applications
• Easy to administer
• Easy to scaleup applications
• Scaleable
• Scale down to portables
• Scale to many nodes (manage, replicate)
• Scale to huge nodes
• Data warehousing
• Data capture
• Data analysis

4
Microsoft SQL Server
Need W2K logo
5
Easy

• Dynamic self management
• Multi-site management
• Operation Scripting
• Job scheduling and execution
• Alert/response management
• DBA profiling/tuning tools
• Installation and upgrade
• Unicode
• Integrated with NT SecurityIntegrated with NT
  files

Easy
Scalability
Data Warehousing
6
Made It (Even) Easier!

• Desktop
• Auto Configure
• Self-Configuring Engine / Dynamic Disk/memory
• Workgroups
• Reduce Learning Curve, Increase Productivity
• Self-Managing SQLAgent, Wizards, Task Pads
• Large Organizations
• Deploy/manage 100s of SQL Servers
• Lower TOC for Large Environments
• Multi-Server Operations/ Remote Operation

7
Multi-Site ManagementCommon Console

• Multi-site management through groups, agents
• Route events from all servers to one master
• Script any set of operations for all servers
• Job Scheduling and Workflow
• Shared sub-set of UI for web administration
• Graphical tools for data
• Import/export

8
Distributed Management Framework

• Administration shell
• MMC snap-in
• Supports all operations
• Namespace objects
• Navigation UI
• For namespace shells
• COM administration interfaces
• ActiveX Scripting
• Agents
• Self-managing component
• Activity monitoring/filters
• Events
• Queries/traffic
• State changes in engine

Trace Events/ Perf Mon
9
Dynamic Self Management

• Dynamic memory management

2 a.m., 5 users
48M Memory, 100 locks
10
Wizards and GUIs

• Many Wizards - over 50 at last count
• MS Access as a query interface
• Query tool built into SQL, Visual Studio,...
• Graphical show plan

11
DBA and Developer Tools

• SQL Server Profiler
• Select server events and trace criteria
• Capture output to screen or replay
• Index Tuning Wizard
• Analyzes actual server usage history
• Makes recommendations to improve performance

12
Demo 5 minutes

• Tour of the wizards wizard
• (touch on create index, DTS, maintenance plan)
• Show database layout (auto schema)
• Show index tuning wizard (using pre-canned
  trace file)
• Let it define the indices

13
Index Wizard is Good but we will make it better

• On a complex query set
• wizard is 90 of best expert.
• Extending it to other aspects of DB design

14
Scalability

• Win9x/NT4/Win2000 version
• Dynamic row-level locking
• VLDB improvements
• Improved query optimizer
• Intra-query parallelism
• VLM support
• Replication improvements
• Distributed query
• High Availability Clusters

Easy
Scalability
Data Warehousing
15
Query Processor Enhancements
Focus on Complex Queries

• Parallelism
• Improved scan, fetch, sort
• Smart hash merge join
• Large joins grouping
• Better query optimization
• Multi-index operations
• Automatic statistics maintenance
• Distributed Query
• Heterogeneous Query

16
Parallel QuerySMP Disk Parallelism

• of emp. per group
• total inc. per group

• Plus Distributed
• Plus Hash Join (fanciest on the planet)
• Plus Optimized Partitioned views

17
Distributed Heterogeneous QueriesData 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

SQL 7.0 Query Processor
18
Replication
Publisher

• Transactional and Merge
• Remote update
• ODBC and OLE DB subscribers
• Wizards
• Performance

2PC, RPC
Updating Subscriber (immediate updates)
19
UtilitiesThe Key to LARGE Databases

• Backup
• Fuzzy
• Parallel
• Incremental
• Restartable
• Recovery
• Fast
• File granularity
• Reorganize
• shrinks file
• reclusters file

• Auto-Repair
• Index creation 2x faster than 6.5
• DBCC
• not required,
• a good practice
• 5x - 100x faster

20
SQL Server 7.0 Backup
Parallel Backup Throughput (MBps)
Scaling at device speed
95 of throughput maintained during full on-line
backup (8 drives)
Max tape backup speed 600GB/hr
21
Recovery Speed Comparison4x faster!
Recovery time (secs)
of indices
22
SQL Server 7.0 Scalability
Application SAP R/3 BAAN IV PeopleSoft
Benchmark SD Users Baan HRMS 3-Tier Reference Us
ers Users
23
Best Single Node NT TPC-C
Published TPC-C results as of 1/20/99 www.tpc.org

DBMS Oracle Informix Sybase 6.5
7.0
4X PII, Xeon
System-- 4X Pent-Pro, 200 MHz --
26
/tpmC 108 100 82 56
24
The TPC Report

• 621,784 for 5 years
• 1.7 Tera Byte Disk
• 4 CPU
• 4 GB RAM
• 18,850 users
• Affordable Manageable

25
Price Performance Leadership 250/year on
Price, 100/year performance
Best performance 23,143 tpmC, 26/tpmC Best
price/performance 17715, 22tpmC
26
VLDB ImprovementsTerabyte Support

• File Groups for easier management
• Exabyte Database Size (theoretical max)
• Tested to 2.5 Terabytes
• 64-bit support (up to 32 GB with NT5)

27
Demo
http//www.TerraServer.com
28
Site Configuration
Database Server
29
File System Configuration

• 28 hardwareRAID5 sets Each raid set has 11
  disks (16 spare drives)
• 4 595 GB NTFS volumes Each striped over 7
  Raid sets on 7 controllers
• 26 20 GB files on F, G, H, I
• DB is File Group of 104 files (2 TB)

30
TerraServer Statistics
TerraServer June 22 1998 to Jan 18 1999
35000000
Billion SQL queries served 99.98 of scheduled
availability New data loaded in background
30000000
25000000
Sessions
20000000
Hit
Count
Page View
15000000
DB Query
Image
10000000
5000000
0
7/6/98
8/3/98
6/22/98
7/20/98
8/17/98
8/31/98
9/14/98
9/28/98
11/9/98
10/12/98
10/26/98
11/23/98
Date
31
High AvailabilityMicrosoft Clusters

• Automatic failover in less than a minute

Server 1
Server 2
Web site
Web site
Web site files
Database files
32
Data Warehousing

• Warehousing Framework
• Visual data modeler
• Microsoft repository
• Data transformation services (DTS)
• Plato Dcube - Multi Dimensional Data Cubes
• Integration with Office2000

Easy
Scalability
Data Warehousing
33
Microsoft Data Warehousing Framework
Building
Using
Data Warehouse Design (logical/physical schema/
data flow)
Data Mart Design (Cubes/Star schema)
End-User Tools (Excel, Access, English Query, 3rd
parties)
Operational Data (ODBC,OLE-DB)
Data Transformation Services (DTS)
Data Marts (RDBMS, OLAP)
OLE DB
Managing
Microsoft Repository (Persistent Shared
Meta-Data)
DB Schema
Transformation
Scheduling data
OLAP Metadata
Data Warehouse Management (Console, Scheduling,
Events,Topology)
Data Flow
Meta-Data Flow
Only partially available in SQL
7 Implemented after SQL 7
34
Data Warehouse / Data Analysis

• Data Transformation Services to get data into
  the warehouse
• CUBE (OLE/DB OLAP) to analyze data

Operational Data
35
Data Transformation Services

• Job steps
• Workflow
• Scheduler
• RepositoryMetadata

36
Warehouses of Data Cubes

• Warehouse is a collection of cubes
• Cubes have
• Star and Snow Flake Schemas
• Data Attributes
• Dimensions

37
RelationalOLAPMultidmensionalOLAPHybridOLAP
Source table
Europe
USA
Asia
38
OLAP Office 2000 demo
39
Readiness

• 300 applications now days3,000 within 18 months
• System Integrators and ISVs
• 52,000 trained DBAs

40
Internet Platform
Site Server
Webclient
Officeclient
IIS
MTS
MSMQ
Windows NT Server
41
Microsoft SQL Server 7.0all these features in
one productworkstation, server, enterprise
42
(No Transcript)