Microsoft SQL Server 2008, Implementation And Maintenance

Code: 70-432
Category: Certification Exams
Price:

Exam 70-432 - Microsoft SQL Server 2008, Implementation and Maintenance

Skills measured

This exam measures your ability to accomplish the technical tasks listed below. The percentages indicate the relative weight of each major topic area on the exam. The higher the percentage, the more questions you are likely to see on that content area on the exam.

Prerequisties:

Microsoft Training2778 - Writing Queries Using Microsoft SQL Server 2008 Transact-SQL

Microsoft Training6231 -Maintaining a Microsoft SQL Server 2008 R2 Database

Microsoft Training6232- Implementing a Microsoft SQL Server 2008 R2 Database

Please note that the questions may test on, but will not be limited to, the topics described in the bulleted text.

Installing and configuring SQL Server 2008 (11%)

  • Install SQL Server 2008 and related services
    • File locations; default paths; service accounts
  • Configure SQL Server instances
    • sp_configure; Dynamic Management Views (DMVs)
  • Configure SQL Server services
    • Configuration manager; SQL browser
  • Configure additional SQL Server components
    • SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), SQL Server Reporting Services (SSRS), replication; MDS
  • Implement database mail
    • Set up and configure
  • Configure full-text indexing
    • Enable/disable, index population

Preparation resources

  • Installing SQL Server 2008
  • Cornfiguring SQL Server 2008
  • How do I: Configure Database Mail in SQL Server 2008

Maintaining SQL Server instances (9%)

  • Manage SQL Server Agent jobs
    • Create and schedule jobs; notification of job execution; disable/enable jobs; change job step order; logging
  • Manage SQL Server Agent alerts
    • Performance condition alerts; SQL event alerts; Windows Management Instrumentation (WMI) alerts
  • Manage SQL Server Agent operators
    • Operator schedules; fail safe operator; add a new operator; notification methods
  • Implement the declarative management framework (DMF)
    • Create a policy; verify a policy; schedule a policy compliance check; enforce a policy; create a condition
  • Back up a SQL Server environment
    • Operating system-level concepts; SQL Server Utility; compression media families

Preparation resources

  • Scheduling SQL Server Agent jobs
  • Sample: Creating a SQL Server Agent alert by using the WMI Provider for server events
  • Managing the SQL Server Utility

Managing SQL Server security (18%)

  • Manage logins and server roles
    • Create logins; disable/enable logins; security model (authentication mode); password policy enforcement; fixed server roles; alter logins; create credentials; certificate logins
  • Manage users and database roles
    • User mapping; user-defined roles; fixed roles; guest, public, dbo; creating and deleting user roles; application roles
  • Manage SQL Server instance permissions
    • Logon triggers; permissions versus fixed role membership; cross-database ownership chaining; impersonation; endpoint permissions
  • Manage database permissions
    • Impersonation; cross-database ownership chaining
  • Manage schema permissions and object permissions
    • Manage schema ownership; object rights
  • Audit SQL Server instances
    • Use DDL triggers and logon triggers; C2; common criteria; login failures; event notifications
  • Manage transparent data encryption (TDE)
    • Impact of transparent data encryption on backups; certificate management; symmetric keys
  • Configure surface area
    • sp_configure

Preparation resources

  • Managing users, roles, and logins
  • Auditing in Microsoft SQL Server 2008
  • SQL Server 2008 transparent data encryption

rMaintaining a SQL Server database (17%)

  • Back up databases
    • Full backups; differential backups; transaction log; compressed backups; file and filegroup backups; verifying backup; TDE backups
  • Restore databases
    • Online restores; full restores; differential restores; transaction log; file and filegroup restores; verifying restore; tail of the transaction log; TDE restores
  • Manage and configure databases
    • Files, file groups, and related options; database options; recovery model; attach/detach data
  • Manage database snapshots
    • Create, drop, revert
  • Maintain database integrity
    • DBCC CHECKDB; suspect pages; page level restores
  • Maintain a database by using maintenance plans
    • Maintenance Plan Wizard; Maintenance Plan Designer

Preparation resources

  • Backing up and restoring databases in SQL Server
  • Setting up a maintenance plan to back up databases
  • Maintenance plans

Performing data management tasks (10%)

  • Import and export data
    • BCP; BULK INSERT; OPENROWSET; GUI tools
    • NOT: SSIS
  • Manage data partitions
    • Switching data from one partition to another; add a filegroup; alter a partition function; alter a partition scheme
    • NOT: designing partition tables/indexes
  • Implement data compression
    • Sparse columns; page/row; Unicode compression
  • Maintain indexes
    • Create spatial indexes; create partitioned indexes; clustered and non-clustered indexes; XML indexes; disable and enable indexes; filtered index on sparse columns; indexes with included columns; rebuilding/reorganizing indexes; online/offline; statistics on filtered indexes
    • NOT: designing new indexes
  • Manage collations
    • Column collation; database collation; instance collation

Preparation resources

  • The Data Loading Performance Guide
  • Creating compressed tables and indexes
  • Setting and changing collations

Monitoring and troubleshooting SQL Server (14%)

  • Identify SQL Server service problems
    • DB Engine service; SQL Agent service; SQL Browser service
  • Identify concurrency problems
    • Blocks, locks, deadlocks, activity monitor; relevant Dynamic Management Views
  • Identify SQL Agent job execution problems
    • Proxy accounts; credentials; job history
  • Locate error information
    • Error log; agent log; job execution history; event logs

Preparation resources

  • Troubleshooting Database Engine
  • Understanding Concurrency Control
  • Viewingr the SQL Server Error Log

Optimizing SQL Server performance (12%)

  • Implement Resource Governor
  • Use the Database Engine Tuning Advisor
  • Collect trace data by using SQL Server Profiler
  • Collect performance data by using Dynamic Management Views
  • Collect performance data by using System Monitor
  • Use Performance Studio

Preparation resources

  • SQL Server 2008 – Resource Governor
  • Using Database Engine Tuning Advisor
  • SQL Server Performance Survival Guide

Implementing high availability (9%)

  • Implement database mirroring
    • Monitoring, configuring, failover
  • Implement a SQL Server clustered instance
    • Monitoring, configuring, failover
  • Implement log shipping
    • Monitoring, configuring, failover
  • Implement replication
    • Monitoring, configuring

Course Reviews

No reviews found for this course.

Be the first to write a review