Chapter 1: Introduction to Microsoft SQL Server 2000

1
Chapter 1 Introduction to Microsoft SQL Server
2000
2
Overview

• What Is SQL Server?
• SQL Server Integration
• SQL Server Databases
• SQL Server Security
• Working with SQL Server
• Data Warehousing and Online Analytical Processing

3
What Is SQL Server?

• Introduction to SQL Server
• Client-Server Components
• Client-Server Communications
• SQL Server Services

4
Introduction to SQL Server
P2-3
SQL Server
Client
Results
OLTP
Query
OLAP
Relational Database Management System
Client Application
5
SQL Server Integration
P6-7

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

6
Integrating SQL Server with Operating Systems
P6-7
Windows 2000
Windows Me, 98, and 95
Browsers
Third Party
Windows NT
Client
Server
Windows 2000
Windows 98
Windows NT
Windows Me
Windows CE
7
Integrating SQL Server with Other Microsoft
Server Applications
Microsoft Windows 2000 with Solution Accelerator
(SA) for the Internet Storefront
Microsoft ExchangeServer
SQL Server
Microsoft Systems Management Server
IBM Host
Microsoft Host Integration Server 2000
Data and Applications
8
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

9
Client-Server Components
P10
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
10
Client-Server Communication Process
P10
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
11
SQL Server Services
P18
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

12
SQL Server Databases
P21

• Types of Databases
• Database Objects
• Referring to SQL Server Objects
• System Tables
• Metadata Retrieval

13
Types of Databases
P21
System Databases
master
model
tempdb
msdb
distribution
pubs
Northwind
User1
User Databases
14
System Tables

• System Tables Store Information (Metadata) About
  the System and Database Objects (only in Master)
• Database Catalog Stores Metadata About a Specific
  Database (including Master)
• System Catalog Stores Metadata About the Entire
  System and All Other Databases (only in Master)
• System table examples
• Syslogins, sysmessages, sysdatabases, sysusers

15
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
16
Database Objects
P22-23
StoredProcedureUpdatePhone
Trigger
EmpNum
LastName
FirstName
Extension
CtryCode
LastMod
10191
Labrie
Angela
x19891
FR
\HR\KarlD
10192
Labrie
Eva
x19433
FR
\HR\KarlD
10202
Martin
Jose
x21467
SP
\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
SQL Server Security
P24

• Login Authentication
• Database User Accounts and Roles
• Types of Roles
• Permission Validation

19
Login Authentication
P24
AUTHENTICATION SQL ServerVerifies Trusted
Connection
Windows 2000 Group or User
Windows 2000
SQL Server
OR
SQL Server Verifies Name and Password
SQL ServerLogin Account
20
Database User Accounts and Roles
P24
SQL Server Assigns Logins to User Accounts and
Roles
SQL Server Verifies Trusted Connection
Windows 2000 Group User
Windows 2000
Database User
SQL Server
OR
Database Role
SQL Server Verifies Name and Password
SQL Server Login Account
21
Types of Roles
P24

• 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

22
Permission Validation
P24
2
3

1
Permissions OK Performs Command
Database UserExecutes Command
SQL Server Checks Permissions
SELECT FROM Members
Permissions not OK Returns Error
23
Physical Database Architecture
P25-26

• Pages and Extents
• Page 8K blocks of memory
• Extent 8 contiguous pages
• Database Files and Filegroups
• Primary data file
• Secondary data file
• Log file
• Table and Index Architecture
• Clustered tables
• Heaps

24
How Data Is Stored
P25
Database
Data (file).mdf or .ndf
Log (file).Idf
Extent (8 contiguous 8-KB pages)
Tables, Indexes
Page (8 KB)
Data
Max row size 8060 bytes
25
Types of Pages and Extents
P26

• Types of Pages
• Pages that track space allocation
• Pages that contain user and index data
• Types of Extents

Uniform Extents
Free Space
Mixed Extent
26
Data Warehousing and Online Analytical Processing
P38-41

• OLAP vs OLTP systems
• Application Development Architecture
• Database Language
• Transact-SQL
• XPath

27
Review

• What Is SQL Server?
• SQL Server Integration
• SQL Server Databases
• SQL Server Security
• Working with SQL Server
• Data Warehousing and Online Analytical Processing

28
Lab 1 NA