|
This preparation guide includes information
about:
|
Exam News
Exam 70-229 became available May 10, 2002.
Audience Profile
Candidates for this exam work in a medium to
enterprise computing environment that uses SQL Server
2000 Enterprise Edition. Candidates have at least one
year of experience implementing relational databases in
environments with the following characteristics:
- Heterogeneous databases.
- SQL Server security integrated with Windows
Authentication.
- Client/server configurations of 50 to 5,000 or
more users.
- Web configurations that use Internet Information
Services (IIS) or COM+.
- Databases as large as 2 TB.
- Multiple installations of SQL Server 2000.
Skills Being Measured
This certification exam measures your ability to
design and implement database solutions by using
Microsoft SQL Server 2000 Enterprise Edition. Before
taking the exam, you should be proficient in the job
skills listed in the following matrix. The matrix shows
which Official Microsoft Learning Products may help you
reach competency in the skills being tested in the exam.
| Developing a Logical Data Model |
Define entities.
Considerations include entity composition and
normalization.
- Specify entity attributes.
- Specify degree of normalization.
|
Design entity keys.
Considerations include FOREIGN KEY constraints,
PRIMARY KEY constraints, and UNIQUE constraints.
- Specify attributes that uniquely identify
records.
- Specify attributes that reference other
entities.
|
Design attribute
domain integrity. Considerations include CHECK
constraints, data types, and nullability.
- Specify scale and precision of allowable
values for each attribute.
- Allow or prohibit NULL for each attribute.
- Specify allowable values for each attribute.
|
| Implementing the Physical
Database |
Create and alter
databases. Considerations include file groups,
file placement, growth strategy, and space
requirements.
- Specify space management parameters.
Parameters include autoshrink, growth increment,
initial size, and maxsize.
- Specify file group and file placement.
Considerations include logical and physical file
placement.
- Specify transaction log placement.
Considerations include bulk load operations and
performance.
|
Create and alter
database objects. Objects include constraints,
indexes, stored procedures, tables, triggers,
user-defined functions, and views.
- Specify table characteristics.
Characteristics include cascading actions, CHECK
constraints, clustered, defaults, FILLFACTOR,
foreign keys, nonclustered, primary key, and
UNIQUE constraints.
- Specify schema binding and encryption for
stored procedures, triggers, user-defined
functions, and views.
- Specify recompile settings for stored
procedures.
- Specify index characteristics.
Characteristics include clustered, FILLFACTOR,
nonclustered, and uniqueness.
|
Alter database
objects to support replication and partitioned
views.
- Support merge, snapshot, and transactional
replication models.
- Design a partitioning strategy.
- Design and create constraints and views.
- Resolve replication conflicts.
|
| Troubleshoot failed
object creation. |
| Retrieving and Modifying Data |
| Import and export
data. Methods include the bulk copy program, the
Bulk Insert task, and Data Transformation Services
(DTS). |
| Manipulate
heterogeneous data. Methods include linked
servers, OPENQUERY, OPENROWSET, and OPENXML. |
| Retrieve, filter,
group, summarize, and modify data by using
Transact-SQL. |
| Manage result sets by
using cursors and Transact-SQL. Considerations
include locking models and appropriate usage. |
| Extract data in XML
format. Considerations include output format and
XML schema structure. |
| Programming Business Logic |
Manage data
manipulation by using stored procedures,
transactions, triggers, user-defined functions,
and views.
- Implement error handling in stored
procedures, transactions, triggers, and
user-defined functions.
- Pass and return parameters to and from
stored procedures and user-defined functions.
- Validate data.
|
Enforce procedural
business logic by using stored procedures,
transactions, triggers, user-defined functions,
and views.
- Specify trigger actions.
- Design and manage transactions.
- Manage control of flow.
- Filter data by using stored procedures,
triggers, user-defined functions, and views.
|
| Troubleshoot and
optimize programming objects. Objects include
stored procedures, transactions, triggers,
user-defined functions, and views. |
| Tuning
and Optimizing Data Access |
| Analyze the query
execution plan. Considerations include query
processor operations and steps. |
| Capture, analyze, and
replay SQL Profiler traces. Considerations include
lock detection, performance tuning, and trace
flags. |
| Create and implement
indexing strategies. Considerations include
clustered index, covering index, indexed views,
nonclustered index, placement, and statistics. |
| Improve index use by
using the Index Tuning Wizard. |
| Monitor and
troubleshoot database activity by using SQL
Profiler. |
| Designing
a Database Security Plan |
Control data access
by using stored procedures, triggers, user-defined
functions, and views.
- Apply ownership chains.
- Use programming logic and objects.
Considerations include implementing row-level
security and restricting direct access to
tables.
|
| Define object-level
security including column-level permissions by
using GRANT, REVOKE, and DENY. |
| Create and manage
application roles. |
|
