Title: Chapter 1: Introduction to Microsoft SQL Server 2000
1Chapter 1 Introduction to Microsoft SQL Server
2000
2Overview
- What Is SQL Server?
- SQL Server Integration
- SQL Server Databases
- SQL Server Security
- Working with SQL Server
- Data Warehousing and Online Analytical Processing
3What Is SQL Server?
- Introduction to SQL Server
- Client-Server Components
- Client-Server Communications
- SQL Server Services
4Introduction to SQL Server
P2-3
SQL Server
Client
Results
OLTP
Query
OLAP
Relational Database Management System
Client Application
5SQL Server Integration
P6-7
- Integrating SQL Server with Operating Systems
- Integrating SQL Server with Windows 2000
- Integrating SQL Server with Other Microsoft
Server Applications
6Integrating 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
7Integrating 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
8Integrating 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
9Client-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
10Client-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
11SQL Server Services
P18
MSSQLServer Service
- Data Management
- Transaction and Query Processing
- Data Integrity
SQLServerAgent Service
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
13Types of Databases
P21
System Databases
master
model
tempdb
msdb
distribution
pubs
Northwind
User1
User Databases
14System 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
15Metadata Retrieval
- System Stored Procedures
- System and Metadata Functions
- Information Schema Views
EXEC sp_help Employees
SELECT USER_NAME(10)
SELECT FROM INFORMATION_SCHEMA.TABLES
16Database 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
17Referring 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
19Login 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
20Database 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
21Types 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
22Permission Validation
P24
2
3
1
Permissions OK Performs Command
Database UserExecutes Command
SQL Server Checks Permissions
SELECT FROM Members
Permissions not OK Returns Error
23Physical 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
24How 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
25Types 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
26Data Warehousing and Online Analytical Processing
P38-41
- OLAP vs OLTP systems
- Application Development Architecture
- Database Language
- Transact-SQL
- XPath
27Review
- What Is SQL Server?
- SQL Server Integration
- SQL Server Databases
- SQL Server Security
- Working with SQL Server
- Data Warehousing and Online Analytical Processing
28Lab 1 NA