1 sql server 2000 administration kashef mughal msb
TRANSCRIPT
![Page 1: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/1.jpg)
1
SQL Server 2000 Administration
Kashef Mughal
MSB
![Page 2: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/2.jpg)
2
SQL Server 2000 Administration
Introductions – Name, experience with databases or SQL Server, objectives for this class, any hobbies
Contact information Breaks, dinner etc. Syllabus and Addendum Grading Scale Attendance Concerns Any questions?
![Page 3: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/3.jpg)
3
Database Basics
What is a database? A database is a collection of related
information What are the building blocks of a db? Tables, fields and records What are popular kinds of databases? Access, Sybase, SQL Server, Oracle
etc.
![Page 4: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/4.jpg)
4
Database objects - Table 1-7
Table (most important object) Data type Default View Stored procedure User-defined function Trigger Constraint Index
![Page 5: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/5.jpg)
5
Tables
A table contains data on a specific item like customers, orders etc.
Tables are collections of rows and columns like a spread sheet
A column a.k.a. field defines a unique entity like SSN, Customer Name etc
A rows a.k.a record is a group of related data items (fields) treated as one unit of information
Discuss Customer table in Northwind
![Page 6: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/6.jpg)
6
Background of SQL Server
Collaboration with Sybase for OS/2 Decided to abandon OS/2 for Win NT SQL Server 4 for Windows NT SQL Server 6 and then SQL Server
6.5 SQL Server 7 (November of 1998) SQL Server 2000 (August 2000) SQL Server YUKON?
![Page 7: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/7.jpg)
7
Administrator vs. Developer
Administrator has the following tasks:
Backup and restore the database, manage the users and security, setup tasks, alerts and operators, optimize the database
Developer has the following tasks: Business analysis, design the database objects, writing Transact SQL, design the GUI
Reality is that you need to know both
![Page 8: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/8.jpg)
8
Client/server computing
Client/server applications – application is divided into functional components, client (end user) and server
The advantage is that you can dedicate the resources on the Server side and also minimize administration
For example SQL Server is a Server Application, and Visual Basic could be a Client Application
![Page 9: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/9.jpg)
9
Client access to SQL Server
Many options for the client to access data
Transact-SQL English Query OLE DB/ODBC-OLE DB ActiveX Data Objects (ADO)
![Page 10: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/10.jpg)
10
Transact-SQL (T-SQL)
SQL (pronounced sequel or SQL) is the universal language for database. For SQL Server the dialect is T-SQL
TSQL has two types– Data Definition Language (DDL) – used
to create and modify databases e.g. CREATE DATABASE
– Data Manipulation Language (DML) – used to enter, modify, and extract data e.g. SELECT * FROM CUSTOMERS
![Page 11: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/11.jpg)
11
SQL Server editions
Personal edition Standard edition Enterprise edition Enterprise Evaluation edition (we are
using this one) Developer edition Windows CE edition Desktop edition Table 1-1
![Page 12: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/12.jpg)
12
SQL Server features
Centralized management Data replication Distributed transactions Distributed data Data Transformation Services (DTS) Named instances Operating system integration
![Page 13: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/13.jpg)
13
SQL Server services
MSSQLServer SQL Server Agent MS DTC Microsoft Search MSSQLServerOLAPService Table 1-2
![Page 14: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/14.jpg)
14
Communication components
Client Server
![Page 15: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/15.jpg)
15
Network libraries
Named Pipes (default) Multiprotocol TCP/IP Sockets (default) NWLink IPX/SPX AppleTalk Banyan VINES Shared Memory VIA Giganet Table 1-5
![Page 16: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/16.jpg)
16
Default Net-Libraries
Server not supported
TCP/IP SocketsWindows 95
TCP/IP Sockets and Shared Memory
TCP/IP SocketsWindows 98/ Windows Me
TCP/IP Sockets, Named Pipes, and Shared Memory
TCP/IP Sockets and Named Pipes
Windows NT 4.0
TCP/IP Sockets, Named Pipes, and Shared Memory
TCP/IP Sockets and Named Pipes
Windows 2000
ServerClientOperating system
![Page 17: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/17.jpg)
17
Client tools installation
![Page 18: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/18.jpg)
18
Default components
![Page 19: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/19.jpg)
19
Client configuration
General tab
![Page 20: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/20.jpg)
20
Client configuration
Alias tab
![Page 21: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/21.jpg)
21
Server configuration
![Page 22: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/22.jpg)
22
Network communications
If none of the client systems can connect with the server, the problem is likely with the server.
If none of the clients can connect with the server or any other system on the network, the problem is probably a general network failure.
If other clients can connect to the server, the problem is likely with the one client.
![Page 23: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/23.jpg)
23
System databases
master – controls all user databases and SQL Server operations
model – copy source for creating user databases
tempdb – temporary storage pubs – sample database Northwind – sample database msdb – SQL Server Agent support Table 1-6
![Page 24: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/24.jpg)
24
Security in SQL Server
Two Levels– Authentication determines if the user can
connect to SQL Server – Authorization determines what activities
the user can perform once they pass the first level
![Page 25: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/25.jpg)
25
Authentication modes
Authentication is at the Server level Defined by the login ID Two types:
– Windows Authentication mode Windows authentication only Uses Windows users and groups
– Mixed mode Windows or SQL Server authentication Windows users and groups SQL Server login accounts
Windows Authentication is preferred
![Page 26: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/26.jpg)
26
Authorization
Authorization is at the database level Defined by the the user accounts DBA associates a login ID with a user
account e.g. SA login ID is associated with dbo user account
Guest User Account – new login is associated with this account
Roles – similar to Groups Every user in a database belongs to
the Public role (similar to Everyone)
![Page 27: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/27.jpg)
27
![Page 28: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/28.jpg)
28
Review Questions
Page 31 Answers to Chapter Review - Page
889 Next week - Chapter 2
![Page 29: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/29.jpg)
29
Installing SQL Server 2000
Kashef Mughal
MSB
![Page 30: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/30.jpg)
30
Agenda for Today
Do an installation of SQL Server 2000 on your local machine
Talk about the options for installation
![Page 31: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/31.jpg)
31
Planning for Installation
Planning for Installation– Hardware Requirements – Table 2-1– Memory Requirements – Table 2-2
User Account for SQL Server – local system account (this one has no
network access rights)– domain user account (has network
access rights and is prefered option– Example on Page 41
![Page 32: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/32.jpg)
32
Operating system support
SQL Windows CE edition – Windows CE only
Personal edition – Windows 98, Windows Me, Windows NT 4.0, and Windows 2000
Developer edition – Windows NT 4.0 and Windows 2000
Standard edition – Windows NT 4.0 servers and Windows 2000 servers
Enterprise edition – Windows NT 4.0 servers and Windows 2000 servers
![Page 33: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/33.jpg)
33
Installation decisions Local or remote Installation options Name and company name License agreement CD-Key Component selection Instance name Installation type Services accounts Authentication mode Collation settings Network libraries Licensing mode
![Page 34: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/34.jpg)
34
Ready to Install - CD with book
![Page 35: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/35.jpg)
35
Local or remote - which computer?
![Page 36: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/36.jpg)
36
Installation options - new or upgrade?
![Page 37: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/37.jpg)
37
Name and company
![Page 38: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/38.jpg)
38
Installation definition - types of installation?
![Page 39: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/39.jpg)
39
Default instance or a named instance?
![Page 40: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/40.jpg)
40
Setup types
![Page 41: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/41.jpg)
41
Component selection - select all for our case - Table 2-5
![Page 42: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/42.jpg)
42
Service accounts - local or domain?
![Page 43: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/43.jpg)
43
Authentication - windows or mixed?
![Page 44: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/44.jpg)
44
Collation - storage of data - dic-order-case-insensitive (1252)
![Page 45: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/45.jpg)
45
Net-Libraries - TCP/IP and Name Pipes are default
![Page 46: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/46.jpg)
46
Licensing mode - Per Seat (small) vs. Per Server (big)
![Page 47: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/47.jpg)
47
Named Instances
Multiple instances(copies) on same server
Connect using the syntax:computername\instancename
Used for second and later installations Just review the information in Lesson
4 on Named Instances
![Page 48: 1 SQL Server 2000 Administration Kashef Mughal MSB](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649ea45503460f94ba9002/html5/thumbnails/48.jpg)
48
Named instance