1 / 41

SQL Server 2000 Overview

SQL Server 2000 Overview. Don Vilen Program Manager SQL Server Development Team. Agenda. SQL Server Overview SQL Server Architecture Storage and Access Methods Query Processing and Optimization Transaction Processing Other Topics. SQL Server Overview. SQL Server Overview.

valora
Download Presentation

SQL Server 2000 Overview

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. SQL Server 2000Overview Don Vilen Program Manager SQL Server Development Team

  2. Agenda • SQL Server Overview • SQL Server Architecture • Storage and Access Methods • Query Processing and Optimization • Transaction Processing • Other Topics

  3. SQL Server Overview

  4. SQL Server Overview • What Is SQL Server? • SQL Server Integration • SQL Server Databases • SQL Server Security • Working with SQL Server

  5. What Is SQL Server? • Introduction to SQL Server • Client-Server Components • Client-Server Communications • SQL Server Services

  6. Introduction to SQL Server Client SQL Server Results OLTP Query OLAP Relational Database Management System Client Application

  7. Client-Server Components Client Application SQL Server Server Net-Libraries Database API (OLE DB, ODBC, DB-Library) Open Data Services Relational Engine Client Net-Library Client Server Storage Engine Processor Memory Local Database

  8. Query Client-Server Communication Process Client Application SQL Server 1 Server Net-Libraries Result Set Database API (OLE DB, ODBC, DB-Library) 5 Open Data Services 3 Query Result Set Relational Engine Client Net-Library 2 Client Server 4 Storage Engine Processor Memory Local Database

  9. SQL Server Services MSSQLServer Service • Data Management • Transaction and Query Processing • Data Integrity SQLServerAgent Service • Jobs • Alerts • Operators Microsoft Distributed Transaction Coordinator • Distributed Transaction Management Server Microsoft Search • Full-Text Catalogs • Full-Text Indexes

  10. SQL Server Integration • Integrating SQL Server with Operating Systems • Integrating SQL Server with Windows 2000 • Integrating SQL Server with Other Microsoft Server Applications

  11. Integrating SQL Server with Windows 2000 • Active Directory • Security • Multiprocessor Support • Microsoft Event Viewer • Windows 2000 Component Services • Windows 2000 System Monitor • Microsoft Internet Information Services • Windows Clustering

  12. SQL Server is Integrated but Separate • SQL Server runs only under Windows operating systems • Understands Windows concepts and is written specifically to take advantage of them • Memory, files, processes, threads, fibers • No “Portability Layer” to virtualize the OS • Only uses documented Windows features • No “special” APIs that only SQL Server can use • Simply a ‘process’ to Windows

  13. Internet Integrating SQL Server with Other Microsoft Server Applications Microsoft Windows 2000 with Solution Accelerator (SA) for the Internet Storefront SQL Server Microsoft ExchangeServer Microsoft Systems Management Server IBM Host Microsoft Host Integration Server 2000 Data and Applications

  14. SQL Server Databases • Types of Databases • Database Objects • Referring to SQL Server Objects • Transact-SQL • System Tables • Metadata Retrieval

  15. Types of Databases System Databases master model tempdb msdb distribution pubs Northwind User1 User Databases

  16. Clustered Index Anderson Anderson Barr ... FK PK longstring integer longstring varchar(20) char(2) char(6) Checkx##### Database Objects StoredProcedureUpdatePhone Trigger EmpNum LastName FirstName CtryCode Extension LastMod 10191 Labrie Angela FR x19891 \HR\KarlD 10192 Labrie Eva FR x19433 \HR\KarlD 10202 Martin Jose SP x21467 \HR\AmyL EmployeePhoneView SELECT lastname, firstname, extension FROM employee

  17. Referring to SQL Server Objects • Fully Qualified Names • server.database.owner.object • Partially Specified Names • Server defaults to the current instance on the local server • Database defaults to current database • Owner defaults to the user name in the database CREATE TABLE Northwind.dbo.OrderHistory . . .

  18. Transact-SQL • SQL Server’s dialect of Structured Query Language (SQL) • Predates SQL standards • Conforms to Entry-Level SQL-99 • DDL – Data Declaration Language • DML – Data Manipulation Language • DCL – Data Control Language

  19. System Tables • System tables store information (metadata) about the system and database objects • Database catalog stores metadata about a specific database • System catalog stores metadata about the entire system and all other databases

  20. Metadata Retrieval • System Stored Procedures • System and Metadata Functions • Information Schema Views EXEC sp_help Employees SELECT USER_NAME(10) SELECT * FROM INFORMATION_SCHEMA.TABLES

  21. SQL Server Security • Login Authentication • Database User Accounts and Roles • Types of Roles • Permission Validation

  22. Login Authentication AUTHENTICATION SQL ServerVerifies Trusted Connection Windows 2000 Group or User Windows 2000 SQL Server Or SQL Server Verifies Name and Password SQL ServerLogin Account

  23. Database User Accounts and Roles SQL Server Verifies Trusted Connection SQL Server Assigns Logins to User Accounts and Roles Windows 2000 Group User Windows 2000 Database User SQL Server OR Database Role SQL Server Verifies Name and Password SQL Server Login Account

  24. Types of Roles • Fixed Server Roles • Group administrative privileges at the server level • Fixed Database Roles • Group administrative privileges at the database level • User-defined Database Roles • Represent work defined by a group of employees within an organization

  25. Permission Validation 2 3 1 Permissions OK; Performs Command Database UserExecutes Command SQL Server Checks Permissions SELECT * FROM Members Permissions not OK; Returns Error

  26. Working with SQL Server • Administering a SQL Server Database • Implementing a SQL Server Database • Selecting an Application Architecture for SQL Server • Designing Applications Using Database APIs

  27. Administering a SQL Server Database • Common Administrative Tasks • SQL Server Enterprise Manager • SQL Server Administration Tools and Wizards • SQL Server Command Prompt Management Tools • SQL Server Help and SQL Server Books Online

  28. Implementing a SQL Server Database • Designing the Database • Creating the Database and Database Objects • Testing and Tuning the Application and Database • Planning Deployment

  29. Business Data Selecting an Application Architecture for SQL Server Intelligent Server (2-Tier) Intelligent Client (2-Tier) N-Tier Internet Presentation Browser Client Presentation Presentation Presentation Business Business Business Data Data Data

  30. Designing Applications Using Database APIs Data Object Interfaces ADO Application Programming Interfaces OLE DB Other DataSources RelationalDatabases

  31. Editions of SQL Server • SQL Server 2000 for Windows CE Edition - Powered by Windows (Windows CE) • Windows 95 and other operating systems are Client Only – older versions of tools only

  32. SQL Server Components

  33. SQL Server Components • Analysis Services / OLAP • Replication • Transactional, Merge, Snapshot • Linked Servers, Remote Servers, MS DTC • Data Transformation Services - DTS • SQL Server Agent • Full-Text Search • Failover Clustering

  34. SQL Server Components • Tools • SQL Server Enterprise Manager, Query Analyzer, Server Utility, Client Utility, Profiler • OSQL, Index Tuning Wizard, BulkCopy (BCP) • Samples • English Query – in Visual Studio

  35. SQL Server Documentation • Books Online • Samples • Other documentation • TechNet, MSDN, KnowledgeBase • http://microsoft.com/sql/techinfo • Inside SQL Server 2000, Kalen Delaney • Microsoft Press, ISBN: 0735609985 • http://www.InsideSQLServer.com • Database System Concepts, 4th Edition, Sliberschatz • McGraw Hill, ISBN: 0-07-228363-7 • Chapter 27: Microsoft SQL Server

  36. SQL Server History • Ashton-Tate/Microsoft SQL Server 1.0 • May 1989 – based on Sybase DataServer; for OS/2 • Microsoft SQL Server 1.1 • July 1990 – Windows 3.0 • Microsoft SQL Server 4.2 (4.2 to match with Sybase’s 4.2) • March 1992 – Windows, OS/2 – 16-bit Code name • October 1992 – Windows NT – 32-bit SQLNT • Microsoft SQL Server 6.0 • June 1995 SQL95 • Microsoft SQL Server 6.5 • April 1996 Hydra • Microsoft SQL Server 7.0 • January 1999 Sphinx • Microsoft SQL Server 2000 (8.0) • August 2000 Shiloh • 64-bit – Late 2002? Liberty • Microsoft SQL Server ?? -- in development Yukon • Microsoft SQL Server ?? -- the next versionAcadia

  37. SQL Server Usage • SQL Server is used in solutions for systems from 10 MBs to 100 TB • SQL Server is used on highly available systems (web sites, etc.) where 99.999% availability is required • See http://www.microsoft.com/sql for references

  38. Winning in the Enterprise

  39. SQL Server Benchmarks • SQL Server has top position in nearly all important benchmarks

  40. 2002 Benchmark Leadership

  41. Questions?

More Related