Transcript SQL Server 2000

Introduction to SQL Server 2000
Presented by Tarek Ghazali
IT Technical Specialist
Microsoft SQL Server MVP
Web Development MCP
LebDev Vice President
© 2006 Tarek Ghazali. All rights reserved.
What is SQL Server 2000? (1)




SQL Server is a client/server based relational
database management system
Runs on Windows 2003 server ,2000
Professional & Server, Advanced Server, NT 4,
Windows 9x/ME/XP Pro. or Windows CE
Included in BackOffice product family
Included in .Net Servers family
What is SQL Server 2000? (2)
Client
SQL Server
Results
Query
Client Application
Relational Database
Management System
What is SQL Server 2000? (3)

Microsoft SQL Server 2000 is designed
to work effectively as:


A central database on a server shared by
many users who connect to it over a
network.
A desktop database that services only
applications running on the same desktop
SQL Server Services

Consists of 3 Services:

MSSQLServer


SQL Server Agent


Data & query processing
Scheduled jobs & alerts
Microsoft Distributed Transaction
Coordinator

The Microsoft Distributed Transaction Coordinator (MS DTC) is a
transaction manager that allows client applications to include several
different sources of data in one transaction. MS DTC coordinates
committing the distributed transaction across all the servers enlisted in
the transaction.
SQL Server Tools

Enterprise manager




SQL Server Profiler
Service Manager
Query Analyzer


MMC application that provides easy visual control
over numerous common DBA and developer tasks
Enter queries, browse objects on the server, view
execution paths, etc.
Numerous other tools
Client-Server Communication
Process
1
Query
Result Set
Database API
(OLE DB, ODBC,
DB-Library)
2
Client
SQL Server
Client Application
Client Net-Library
Server Net-Libraries
3
Open Data Services
Query
5
Result Set
Relational
Engine
Server
4
Storage Engine
Processor
Memory
Local
Database
Types of Databases
System Databases
master
model
tempdb
msdb
pubs
Northwind
User1
User Databases
distribution
Database (master)


The master database records all login
accounts and all system configuration
settings.
master is the database that records
the existence of all other databases,
including the location of the database
files.
Databases
(msdb,distribution,model)



The msdb database is used by SQL Server
Agent for scheduling alerts and jobs, and
recording operators.
The distribution database is used for
replication.
The model database is used as the template
for all databases created on a system.
Database (tempdb)


tempdb holds all temporary tables and
temporary stored procedures. It also
fills any other temporary storage needs
such as work tables generated by SQL
Server.
tempdb is re-created every time SQL
Server is started so the system starts
with a clean copy of the database.
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
User Tables



Defined by DBA or developer to hold
application specific information
Created either through Transact-SQL
script or Enterprise Manager
System catalog is updated to hold
meta-information about the user tables
PK
EmpNum
integer
Clustered Index
Anderson
Anderson
Barr
...
LastName
FirstName
FK
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 TABLE Northwind.dbo.OrderHistory
.
.
.
Implementing a SQL Server
Database




Designing the Database
Creating the Database and Database Objects
Testing and Tuning the Application and
Database
Planning Deployment
SQL Server
Demonstration
SQL Server Documentation

Books Online – very useful


Samples
Other documentation

TechNet, MSDN, KnowledgeBase

http://microsoft.com/sql/techinfo
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
Thank you!

Microsoft Resources:



Contact me:



msdn.microsoft.com/sqlserver/
www.microsoft.com/sql/community
[email protected]
www.sqlmvp.com (will be available soon)
Download Presentation :

www.lebdev.net