CERTguide 70-228 Study Guide

What is 70-228 all about?

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.htm
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.htm

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.htm

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

n          Tempdb

n          Model

n          Msdb

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

n          Secondary data files:

n          Log files

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:

n          Constraints relevant for creating indexes:

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

n          Optimistic concurrency control

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

n          Types of bottlenecks

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) -- Joyjit Mukherjee, Joyit Mukherjee; Hardcover

http://www.amazon.com/exec/obidos/ASIN/0072127031/qid=1020606565/sr=8-4/ref=sr_8_67_4/104-3798385-9811168

MCSE SQL Server 2000 Administration for Dummies (with CD-ROM, covers test #70-228) -- Rozanne Whalen, Dan Whalen; Paperback

http://www.amazon.com/exec/obidos/ASIN/0764504800/qid=1020606565/sr=8-5/ref=sr_8_67_5/104-3798385-9811168

MCSE Microsoft SQL Server 2000 Administration Readiness Review Exam 70-228 (With CD-ROM) -- Irfan Chaudhry, Dean Bartholomew; Paperback

http://www.amazon.com/exec/obidos/ASIN/0735612501/qid=1020606565/sr=8-6/ref=sr_8_67_6/104-3798385-9811168

This study guide is developed by Michael Yu Chak Tin. He can be reached at Michael@examreview.net.