MCSE Braindumps - free tests - study guides and mcse simulations are available for download. Looking for mcse braindumps mcse brain dumps or mcsa braindumps? You will find many links...  
MCSE Braindumps Home Members area to download MCSE Braindumps Signup to become member of Sure2Pass.com Download the most latest MCSE Braindumps Need more information?
MCSE Braindumps
Download MCSE braindumps
MCSE Exam Information
MCSE 2000 Braindumps Free Download
MCSE 2003 Braindumps Free Download link
MCSE dumps free
braindumps
Free braindumps
MCSE Exam Tips
Pass Guaranteed
braindumps
70-292
70-290
 
Update News
MCSE Braindumps are  updated on

Special Offer

All Exams
for $69

read more..

 

 

MCSE 70-228 Study Guide

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

Disclaimer: Sure2Pass Tests and MCSE Braindumps are based solely on published objectives of various exams, which cover concepts that are necessary for various networking professional certification designations. Links to other sites are published for the benefit/information of our visitors and we are not responsible for their contents. Our MCSE Study Guides, practice tests, and/or material is not sponsored by, endorsed by or affiliated with Microsoft. Microsoft, MCSE, MCSA, MCSD, the Microsoft logo are trademarks or registered trademarks of Microsoft in the United States and certain other countries. All other trademarks are trademarks of their respective owners
.