Installing, Configuring, and Administering
Microsoft SQL Server 2000 Enterprise Edition
This exam tests your ability to administer and troubleshoot
information systems that incorporate SQL Server 2000
Enterprise Edition. The expected exam scenarios are of the
following characteristics:
n
Heterogeneous databases.
n
SQL Server security integrated with Windows
Authentication.
n
Client/server configurations of 50 to 5,000 or
more users.
n
Web configurations that use Microsoft Internet
Information Services (IIS) or COM+.
n
Databases as large as 2 terabytes.
n
Multiple installations of SQL Server 2000.
When you pass this exam, you achieve Microsoft Certified
Professional status. You also earn credit toward the
following certifications:
n
Core credit toward Microsoft Certified
Database Administrator on Microsoft SQL Server 2000
certification
n
Elective credit toward Microsoft Certified
Systems Engineer on Microsoft Windows 2000 certification
n
Elective credit toward Microsoft Certified
Systems Engineer on Microsoft Windows NT 4.0 certification
n
Elective credit toward Microsoft Certified
Systems Administrator on Microsoft Windows 2000
certification
About this study guide
SQL Server 2000 Admin is a huge topic. In fact, this is
one of the most difficult MCP exams available. Many topics
are covered, each with a certain degree of "depth". In order
to effectively assist you in your preparation exam, this
study guide will guide you through the entire study process.
Note that there are simulation questions in this exam.
Without hands-on experience, there is no way you can pass!
What is SQL Server?
SQL Server is a full-blown DBMS. A DBMS is a software
system that enables you to store, modify, and extract
information from a database. There are many different types
of DBMSs. They can differ widely. As said by Webopedia, the
terms relational, network, flat, and hierarchical all refer
to the way a DBMS organizes information internally.
Microsoft SQL Server is a Relational DBMS that stores
data in the form of related tables. Generally speaking,
relational databases are powerful as they require few
assumptions about how data is related or how it will be
extracted from the database. The same database can be viewed
in many different ways, depending on how you manipulate the
queries: requests for database information in a stylized
form.
Build your test lab
To truly experience SQL Server 2000, you need to prepare
at least 2 PCs, with one acting as the server and another
one acting as the client.
|
The OFFICIAL Minimum
Requirements
* information
retrieved from
http://www.microsoft.com/sql/evaluation/sysreqs/2000/default.asp
|
|
Processor |
166-MHz or higher processor. |
|
Operating System |
-
SQL Server 2000 Enterprise Edition and Standard
Edition run on Microsoft Windows 2000 Server,
Windows 2000 Advanced Server, Windows 2000 Datacenter
Server operating systems, Microsoft Windows NT Server
version 4.0 Service Pack 5 (SP5) or later, and
Windows NT Server 4.0 Enterprise Edition with SP5 or
later
-
SQL Server 2000 trial software and SQL Server 2000
Developer Edition run on the operating systems listed
above for the Enterprise and Standard Editions as well
as on Windows XP Professional, Windows XP Home
Edition, Windows 2000 Professional and Windows NT
Workstation 4.0 with SP5 or later.
-
SQL Server 2000 Personal Edition and SQL Server 2000
Desktop Engine run on the operating systems listed
above for Enterprise and Standard Editions, as well as
on Windows 98, Windows Millennium Edition
(Windows Me), Windows XP Professional, Windows XP Home
Edition, Windows 2000 Professional, and Windows NT
Workstation 4.0 with SP5 or later.
|
|
Memory |
-
Enterprise Edition: 64 megabytes (MB) RAM; 128 MB
recommended.
-
Standard Edition: 64 MB.
-
Evaluation Edition: 64 MB; 128 MB recommended.
-
Developer Edition: 64 MB.
-
Personal Edition: 128 MB for Windows XP; 64 MB for
Windows 2000; 32 MB for other operating systems.
-
Desktop Engine: 128 MB for Windows XP; 64 MB for
Windows 2000; 32 MB for other operating systems.
|
|
Hard Disk |
Enterprise, Standard, Evaluation, Developer, and
Personal Editions require:
-
95–270 MB free hard-disk space for the server; 250 MB
for a typical installation.
-
50 MB free hard-disk space for a minimum installation
of Analysis Services; 130 MB for a typical
installation.
-
80 MB free hard-disk space for English Query
(supported on the Windows 2000 operating system but
not logo certified).
-
Desktop Engine requires 44 MB of available hard-disk
space.
|
Our recommended real
world requirements:
Recommended Server settings:
n
Windows 2000 Server with the latest service
pack applied
n
256 MB RAM
n
1 NIC
n
4GB Hard disk (assuming that the majority of
the hard drive space is free)
Recommended Client settings:
n
Windows 98
n
128 MB RAM
n
1 NIC
n
2GB Hard disk (assuming that the majority of
the hard drive space is free)
You want to download the evaluation version of SQL Server
2000 and have it installed. The URL to download SQL Server
is
http://www.microsoft.com/sql/evaluation/trial/2000/download.asp
The file has a size of around 325MB!
*** SQL Server does not have a HCL. Your hardware will
work with SQL Server as long as they are listed in the
Windows 2000 HCL which is located at
http://www.microsoft.com/hcl/
Choosing the right editions for the test lab
Quoted directly from Microsoft’s technical document on
SQL Server editions:
"SQL Server 2000
Enterprise Edition and SQL Server 2000 Standard Edition are
the only editions of SQL Server 2000 that can be installed
and used in live (deployed) server environments."
For the
purpose of exam preparation, you may use the Standard
edition instead of the more powerful Enterprise edition. The
UIs are mostly the same. However, if you are using the
evaluation version, you are in fact using the time limited
version of the Enterprise edition.
As said by Microsoft, the standard edition:
"... is a
more affordable option for small-sized and medium-sized
organizations that do not require the advanced scalability,
availability, performance, or analysis features of SQL
Server 2000 Enterprise Edition. Standard Edition can be used
on symmetric multiprocessing systems with up to 4 CPUs and 2
GB of RAM."
while the Evaluation edition is:
"... a
time-limited version of SQL Server 2000 Enterprise Edition
that is licensed for demonstration, testing, examination,
and evaluation for a period of 120 days. This means that
Evaluation Edition is not for production use; solutions must
be deployed on SQL Server 2000 Enterprise or Standard
Edition. Evaluation Edition will not function after the
120-day limit has been reached."
On the client you only need to install the Desktop client
for management purpose. That means, you need to install the
Personal edition, which can be run on a non-server OS like
Win98, ME or Windows 2000 Pro:
"SQL Server
2000 Personal Edition is ideal for mobile users who spend
some of their time disconnected from the network but run
applications that require SQL Server data storage, and for
stand-alone applications that require local SQL Server data
storage on a client computer."
Obtaining the BOL
The SQL Server 2000 Book Online is the single most
important resource for your exam preparation effort. It has
all the information covered by the 228 exam.
The BOL is constantly updated. To obtain the latest
version, visit the link below:
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Important technical facts to remember:
Upgrade requirements:
|
Components |
Upgrade
requirements |
| Operating
system |
NT Server
Enterprise Edition version 4.0 with Service Pack 5 (SP5)
or later.
Windows NT Server version 4.0 with SP5 or later.
Windows NT Workstation 4.0 with SP5 or later.
Internet Explorer 5.0 or later.
Windows 2000. |
| SQL Server
6.5 |
SQL Server
6.5 Service Pack 5 (SP5) or later if upgrading on the
same computer. SQL Server 6.5 Service Pack 3 (SP3) or
later if upgrading on a different computer. |
| SQL Server
7.0 |
Any service
pack will do. |
| Network
protocols |
Named Pipes
at the default pipe of \\.\pipe\sql\query. |
| Disk space |
From SQL
Server 6.5 to SQL Server 2000 - 1.5 times the size of
the SQL Server 6.5 databases. |
Collation options:
|
Installation type |
Collation/Locale to use |
| On a totally
new system without the need to synchronize with any
existing system |
The locale
identified by Setup. |
| To upgrade
an installation of SQL Server 6.5 or SQL Server 7.0 to a
default instance of SQL Server 2000 |
The SQL
collation chosen by Setup. |
| To
synchronize with an existing instance of SQL Server 2000 |
Select
SERVERPROPERTY(N'Collation') on the existing
instance, and specify that collation. |
| To
synchronize with an existing installation of SQL Server
6.5 or 7.0 |
Execute
sp_helpsort on the existing system, and then use the
sort ID to select a SQL collation. |
| To
synchronize with a Windows locale of another computer |
The locale
specified in control Panel’s Regional Settings
application. |
Code pages:
|
Code page |
Language |
|
1258 |
Vietnamese |
|
1257 |
Baltic |
|
1256 |
Arabic |
|
1255 |
Hebrew |
|
1254 |
Turkish |
|
1253 |
Greek |
|
1252 |
Latin1 (ANSI) |
|
1251 |
Cyrillic |
|
1250 |
Central European |
|
950 |
Chinese
(Traditional) |
|
949 |
Korean |
|
936 |
Chinese (Simplified) |
|
932 |
Japanese |
|
874 |
Thai |
|
850 |
Multilingual (MS-DOS
Latin1) |
|
437 |
MS-DOS U.S. English |
SQL Server service accounts:
|
Service |
Name |
Default instance
name |
|
Microsoft SQL Server |
SQL Server |
MSSQLSERVER |
|
Microsoft SQL Server
Agent |
SQL Server Agent |
SQLSERVERAGENT |
Default file paths:
n
Default SQL Server directory name: Mssql
n
Program files: drive:\MySqlDir\Mssql\Binn
n
Data files: drive:\MySqlDir\Mssql\Data
System databases:
n
Master
u
records all system level information for a
SQL Server system
u
records all login accounts and all system
configuration settings
u
records the existence of all other databases
n
Tempdb
u
holds all temporary tables for all users
u
holds all temporary stored procedures for
all users
u
temporary tables and stored procedures are
dropped automatically on disconnect
u
autogrows as needed by default
u
you may use ALTER DATABASE to increase the
size of tempdb
n
Model
u
template for all databases created on a
system
u
must always exist on a SQL Server system
n
Msdb
u
used by SQL Server Agent
u
used for scheduling alerts and jobs and
recording operators
Database files:
|
Database file |
File name |
|
master
primary data |
Master.mdf |
|
master
log |
Mastlog.ldf |
|
tempdb
primary data |
Tempdb.mdf |
|
tempdb
log |
Templog.ldf |
|
model
primary data |
Model.mdf |
|
model
log |
Modellog.ldf |
|
msdb
primary data |
Msdbdata.mdf |
|
msdb
log |
Msdblog.ldf |
Types of files:
n
Primary data files
u
starting point of the database
u
points to the other files in the database
u
every database has one
u
file name extension - .mdf.
n
Secondary data files:
u
all of the data files other than the primary
data file
u
optional
u
file name extension -.ndf.
n
Log files
u
log information for recovering the database
u
at least one log file for each database
u
file name extension - .ldf.
Rules for filegroups:
n
A file or filegroup cannot be used by more
than one database.
n
A file can be a member of only one filegroup.
n
Data and transaction log information cannot be
part of the same file or filegroup.
n
Transaction log files are never part of any
filegroups.
Recommendations for files and filegroups:
n
Most databases work well with a single data
file and a single transaction log file.
n
When using multiple files, create a second
filegroup for the additional file and make that filegroup
the default filegroup.
n
For the best performance, create files or
filegroups on as many different available local physical
disks as possible.
n
Always place objects that compete heavily for
space in different filegroups. In fact, you can use
filegroups to allow placement of objects on specific
physical disks.
n
Place different tables used in the same join
queries in different filegroups.
n
Place heavily accessed tables and the
nonclustered indexes belonging to those tables on different
filegroups.
n
Do not place the transaction log on the same
disk with the other files and filegroups.
Object types:
|
n
Constraints
n
Tables
n
Defaults
n
Triggers
n
Indexes |
n
User-defined data types
n
Keys
n
User-defined functions
n
Stored procedures
n
Views |
Data types:
|
n
binary
n
Bigint
n
bit
n
Char
n
datetime
n
decimal
n
Float
n
image
n
Int
n
Money
n
nchar
n
Ntext
n
nvarchar |
n
Numeric
n
Real
n
smalldatetime
n
smallint
n
smallmoney
n
sql_variant
n
sysname
n
text
n
timestamp
n
tinyint
n
varbinary
n
varchar
n
uniqueidentifier |
Mechanisms for enforcing column data integrity:
n
PRIMARY KEY constraints
n
FOREIGN KEY constraints
n
UNIQUE constraints
n
CHECK constraints
n
DEFAULT definitions
n
Nullability
Indexes:
n
Types of SQL Server indexes:
u
Clustered - sort and store the data rows in
the table based on their key values.
u
Nonclustered - structure completely separate
from the data rows.
n
Constraints relevant for creating indexes:
u
PRIMARY KEY creates a unique index to
enforce the primary key.
u
UNIQUE creates a unique index.
u
CLUSTERED creates a clustered index.
Functions of Index Tuning Wizard:
n
Recommend the best mix of indexes for a
database given a workload
n
Analyze the effects of proposed query changes
n
Recommend ways to tune the database
n
Give room for customizing the recommendations
Concurrency control:
n
Pessimistic concurrency control
u
prevents users from modifying data in a way
that affects other users
u
other users cannot perform actions that
would conflict with the lock until the owner releases it
u
mainly used in environments where there is
high contention for data
n
Optimistic concurrency control
u
users do not lock data when they read it
u
the system checks to see if another user
changed the data after it was read when an update is
performed
u
raise error if another user updated the data
u
mainly used in environments where there is
low contention for data
Benefits and uses of views:
n
Focus on Specific Data
n
Simplify Data Manipulation
n
Customize Data
n
Export and Import Data
n
Combine Partitioned Data
Benefits of using stored procedures:
n
modular programming
n
faster execution
n
reduce network traffic
n
security mechanism
Replication roles:
n
Publisher - defines an article for each table
or other database object to be used as a replication source
n
Subscriber - defines a subscription to a
particular publication
n
Distributor - performs various tasks when
moving articles from Publishers to Subscribers
Types of replication:
n
Snapshot - copies data or database objects
exactly as they exist at any moment, based on a scheduled
basis
n
Transactional - used when data must be
replicated as it is modified
n
Merge - lets multiple sites work autonomously
with a set of Subscribers and then later merge the combined
work back to the Publisher
Import and export
options:
|
What to do |
Tools |
| |
DTS wizards |
Replication |
bcp |
BULK INSERT |
SELECT INTO/ INSERT |
|
Import text data |
YES |
|
YES |
YES |
YES |
|
Export text data |
YES |
|
YES |
|
|
|
Import from ODBC
data sources |
YES |
YES |
|
|
|
|
Export to ODBC data
sources |
YES |
YES |
|
|
|
|
Import from OLE DB
data sources |
YES |
YES |
|
|
YES |
|
Export to OLE DB
data sources |
YES |
YES |
YES |
|
|
|
Graphical user
interface (GUI) |
YES |
YES |
|
|
|
|
Command prompt/batch
scripts |
YES |
YES |
YES |
|
|
|
Transact-SQL scripts |
|
YES |
|
YES |
YES |
|
Automatic scheduling |
YES |
YES |
YES |
YES |
|
|
Ad hoc import/export |
YES |
|
YES |
YES |
YES |
|
Recurring
import/export |
YES |
YES |
YES |
|
|
|
Maximum performance |
|
|
YES |
YES |
|
|
Data transformation |
YES |
|
|
|
|
|
Programmatic
interface |
YES |
YES |
YES |
|
|
BCP switches:
|
Data format |
switch |
equivalent BULK
INSERT clause |
|
Native |
-n |
DATAFILETYPE =
'native' |
|
Character |
-c |
DATAFILETYPE =
'char' |
|
Unicode character |
-w |
DATAFILETYPE = 'widechar' |
|
Unicode native |
-N |
DATAFILETYPE = 'widenative' |
Recovery models:
n
Simple Recovery - allows the database to be
recovered to the most recent backup.
n
Full Recovery - allows the database to be
recovered to the point of failure.
n
Bulk-Logged Recovery - allows bulk-logged
operations.
Types of backups
available for each recovery model as listed in BOL:
|
Model |
Backup Type |
| |
Database |
Database
differential |
Transaction
log |
File or file
differential |
|
Simple |
Required |
Optional |
Not allowed |
Not allowed |
|
Full |
Required
(or file backups) |
Optional |
Required |
Optional |
|
Bulk-Logged |
Required
(or file backups) |
Optional |
Required |
Optional |
Ways to change the log file sizes:
n
DBCC SHRINKDATABASE statement
n
DBCC SHRINKFILE statement referencing a log
file
n
Autoshrink operation
Functions of transaction logging:
n
Recovery of individual transactions.
n
Recovery of all incomplete transactions when
SQL Server is started.
n
Rolling a restored database forward to the
point of failure.
Default server Net-Libraries on Windows NT 4.0 or Windows
2000:
n
TCP/IP Sockets.
n
Named Pipes.
Default server Net-Libraries on Windows 98:
n
TCP/IP Sockets.
n
Shared Memory.
Protocols supported by the Client Network Utility:
n
Named Pipes
n
TCP/IP Sockets
n
Multiprotocol
n
NWLink IPX/SPX
n
AppleTalk
n
Banyan VINES
Connections through OLE DATABASE:
n
OLE DB Provider for SQL Server
n
OLE DB Provider for ODBC
Managing servers:
n
Linked server - allows SQL Server to execute
commands against OLE DB data sources on different servers.
n
Remote server - allows a client connected to
one instance of SQL Server to execute a stored procedure on
another instance of SQL Server without establishing another
connection.
n
Standby server - a second server that can be
brought online if the primary server fails.
Performance issues:
n
Causes of bottlenecks
u
Insufficient resources
u
Resources of the same type that do not share
workloads evenly
u
Malfunctioning resources.
u
Incorrectly configured resources.
n
Types of bottlenecks
u
Memory usage
u
CPU processor utilization
u
Disk I/O performance
u
User connections
u
Blocking locks
Functions of Enterprise Manager:
n
Defines groups of servers
n
Registers individual servers in a group
n
Configures SQL Server options for each
registered server
n
Creates and administers SQL Server databases,
objects, logins, users, and permissions in each registered
server
n
Defines and executes SQL Server administrative
tasks on each registered server
n
Designs and tests SQL statements, batches, and
scripts interactively via Query Analyzer
n
Call up the various wizards provided by SQL
Server
Tasks performed by DTS:
n
Importing and exporting data.
n
Transforming data.
n
Copying database objects.
n
Sending and receiving messages to and from
other users and packages.
n
Executing Transact-SQL statements or ActiveX
scripts against a data source.
Commonly used command line utilities locations:
|
Paths |
Utilities |
|
\Program
Files\Microsoft SQL Server\MSSQL\Binn |
bcp
console
isql
sqlagent
sqldiag
sqlmaint
sqlservr
vswitch |
|
\Program
Files\Microsoft SQL Server\80\Tools\Binn |
bcp
dtsrun
dtswiz
isql
isqlw
itwiz
odbccmpt
osql
rebuildm
sqlftwiz |
|
\Program
Files\Microsoft SQL Server\80\Com |
distrib
logread
replmerg
snapshot |
|
\Program
Files\Common Files\Microsoft Shared\Service Manager |
scm |
XML support:
n
allow access to SQL Server using HTTP
n
support XDR XML-Data Reduced schemas
n
use XPath queries against XDR schema
n
retrieve XML data using the SELECT statement +
FOR XML clause.
n
write XML data using OPENXML rowset provider
n
retrieve XML data using the XPath query
language
According to webopedia.com, XML is,
"… a
specification developed by the W3C. XML is a pared-down
version of SGML, designed especially for Web documents. It
allows designers to create their own customized tags,
enabling the definition, transmission, validation, and
interpretation of data between applications and between
organizations."
OpenXML:
n
a rowset provider
n
can be used in Transact-SQL statements in
which rowset providers can appear
n
provides a rowset view over an XML document
n
to write queries against an XML document using
OPENXML, you must first call sp_xml_preparedocument to parse
the XML document and returns a handle to the parsed document
that is ready for consumption
n
you must remove the internal representation of
an XML document from memory by calling sp_xml_removedocument
system stored procedure to free the memory
Reference Books
MCDBA Administering SQL Server 2000 Study Guide (Exam
70-228)
By Joyjit Mukherjee, Joyit Mukherjee; Hardcover
MCSE SQL Server 2000 Administration for Dummies (with
CD-ROM, covers test #70-228)
By Rozanne Whalen, Dan Whalen; Paperback
MCSE Microsoft SQL Server 2000 Administration
Readiness Review Exam 70-228 (With CD-ROM)
By Irfan Chaudhry, Dean Bartholomew; Paperback
| 
