Module 1: SQL Server Overview
Overview
What Is SQL Server?
SQL Server Integration
SQL Server Databases
SQL Server Security
Working with SQL Server
What Is SQL Server?
Introduction to SQL Server
Client-Server Components
Client-Server Communications
SQL Server Services
Introduction to SQL Server
Client
SQL Server Results Results Query Query
OLTP OLAP
Client Application
Relational Database Management System
Client-Server Components Client Client Application Application Database Database API API (OLE DB, ODBC, (OLE DB, ODBC, DB-Library) DB-Library) Client Client Net-Library Net-Library Client
SQL SQL Server Server Server Server Net-Libraries Net-Libraries Open Open Data Data Services Services Relational Relational Engine Engine
Server
Storage Storage Engine Engine
Processor Processor Memory Memory
Local Database
Client-Server Communication Process 1
Query
Result Set
Database Database API API (OLE DB, ODBC, (OLE DB, ODBC, DB-Library) DB-Library) 2 Client
SQL SQL Server Server Server Server Net-Libraries Net-Libraries
Client Client Application Application
Client Client Net-Library Net-Library
3
Open Open Data Data Services Services
Query
5
Result Set
Relational Relational Engine Engine
Server 4
Storage Storage Engine Engine
Processor Processor Memory Memory
Local Database
SQL Server Services MSSQLServer Service SQLServerAgent Service Microsoft Distributed Transaction Coordinator
Data
Management Transaction and Query Processing Data Integrity Jobs Alerts Operators
Distributed
Transaction Management
Server Microsoft Search
Full-Text
Catalogs Full-Text Indexes
SQL Server Integration
Integrating SQL Server with Operating Systems
Integrating SQL Server with Windows 2000
Integrating SQL Server with Other Microsoft Server Applications
Integrating SQL Server with Operating Systems Windows 2000
Windows NT
Windows Me, 98, and 95
Third Party
Browsers
Internet
Client Server
Windows 2000 Windows NT Windows Me Windows 98
Windows CE
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
Integrating SQL Server with Other Microsoft Server Applications Internet
Microsoft Systems Management Server
Microsoft Windows 2000 with Solution Accelerator (SA) for the Internet Storefront
SQL Server
Microsoft Exchange Server
IBM Host Microsoft Host Integration Server 2000
Data and Applications
SQL Server Databases
Types of Databases
Database Objects
Referring to SQL Server Objects
System Tables
Metadata Retrieval
Types of Databases
System Databases master model
tempdb
msdb
pubs
Northwind
User1
User Databases
distribution
Database Objects
PK EmpNum integer
Clustered Index Anderson Anderson Barr ... LastName
FK FirstName
Stored Procedure UpdatePhone
Trigger
CtryCode Extension
LastMod
longstring
varchar(20)
char(2)
char(6)
longstring
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
Check x#####
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 CREATE TABLE TABLE Northwind.dbo.OrderHistory Northwind.dbo.OrderHistory .. .. ..
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
Metadata Retrieval
System Stored Procedures EXEC EXEC sp_help sp_help Employees Employees
System and Metadata Functions SELECT SELECT USER_NAME(10) USER_NAME(10)
Information Schema Views SELECT SELECT ** FROM FROM INFORMATION_SCHEMA.TABLES INFORMATION_SCHEMA.TABLES
SQL Server Security
Login Authentication
Database User Accounts and Roles
Types of Roles
Permission Validation
Login Authentication AUTHENTICATION
Windows Windows2000 2000 Group Groupor orUser User
SQL Server Verifies Trusted Connection Windows 2000
SQL Server
OR
SQL SQLServer Server Login LoginAccount Account
SQL Server Verifies Name and Password
Database User Accounts and Roles Windows 2000 Group User Windows 2000
OR
SQL Server Verifies Trusted Connection
SQL Server Assigns Logins to User Accounts and Roles Database User
SQL Server Database Role
SQL Server Login Account
SQL Server Verifies Name and Password
Types of Roles
Fixed Server Roles
Fixed Database Roles
Group administrative privileges at the server level
Group administrative privileges at the database level
User-defined Database Roles
Represent work defined by a group of employees within an organization
Permission Validation
11
22
33 Permissions OK; Performs Command
Database DatabaseUser User Executes Command Executes Command SELECT SELECT ** FROM FROM Members Members
SQL Server Checks Permissions Permissions not OK; Returns Error
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
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
Implementing a SQL Server Database
Designing the Database
Creating the Database and Database Objects
Testing and Tuning the Application and Database
Planning Deployment
Selecting an Application Architecture for SQL Server Intelligent Intelligent Server Server(2-Tier) (2-Tier)
Intelligent Intelligent Client Client(2-Tier) (2-Tier)
N-Tier N-Tier
Presentation
Presentation
Presentation
Internet Internet
Browser Client
Presentation
Business
Business
Business
Data
Data
Data
Business Data
Designing Applications Using Database APIs Data Object Interfaces
ADO ADO
Application Programming Interfaces
OLE OLEDB DB
Other Data Sources
Relational Databases
Lab A: SQL Server Overview
Review
What Is SQL Server?
SQL Server Integration
SQL Server Databases
SQL Server Security
Working with SQL Server