| Candidates for this exam (#70-229) operate in medium
to very large computing environments that use Microsoft SQL
Server 2000 Enterprise Edition. Candidates have at least one
year of experience administering SQL Server. They also have
at least one year of experience implementing relational databases
in environments that contain: |
| - |
Heterogeneous databases. |
| - |
SQL Server security integrated
with Windows Authentication. |
| - |
Client/server configurations
of 50 to 5,000 or more users. |
| - |
Web configurations that
use Microsoft Internet Information Services (IIS) or COM+. |
| - |
Databases as large as
2 terabytes. |
| - |
Multiple installations
of SQL Server 2000. |
|
This course will allow you to study at your own pace through
a system of case-study scenarios and tutorials to gain practical
experience analyzing, planning and optimizing a network infrastructure
for Windows 2000 Server. As you build these real-world design
skills, you are also preparing for MCP Exam 70-221 - a core
credit on the Windows 2000 MCSE track. |
With our dynamic CD-ROM series and the accompanying exam
simulator and student workbook we include plenty of clear and
concise demos, technical tips and tricks and real-world scenarios.
|
| |
| Our dynamic training
program includes: |
| - |
6 Interactive CD-ROM's
|
| - |
1 Microsoft approved study
guide |
| - |
1 Practice Exam Simulator |
| - |
Our Upgrade Policy |
| - |
Compatibility with: NT/
95 / 98 / 2000 |
| - |
One year of educational
support |
|
| |
| Skills Being Measured |
| This certification exam measures your
ability to administer and troubleshoot information systems that
incorporate Microsoft SQL Server™ 2000 Enterprise Edition.
Before taking the exam, you should be proficient in the job
skills listed below. |
| |
| 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. |
![MCP CD Training (CBT) Course #70-229 [Exam Now Retired] or a generic image.](./images/70-229-mcp-design-sql-2000.jpg)
