SQL Server 2008 MCTS (70-432) DBA Exam Guide
- select the contributor at the end of the page -
MCTS (70-432) DBA Exam
The Implementation and Maintenance Certification (based on MCTS exam 70-432) is one of the three entry level SQL Server certifications available:
- MCTS: SQL Server 2008, Implementation and Maintenance
- MCTS: SQL Server 2008, Database Development
- MCTS: SQL Server 2008, Business Intelligence Development and Maintenance
All three of which start up their own certification paths leading all the way up to the Master and Architect certifications; the paths include:
- Database Administration
- Database Development
- Business Intelligence (BI)
As with all of my exam overview articles, there is a lot of information to go through, so I'll do my best to outline exactly what you'll need to know to pass the exam.
Microsoft profiles this exam's audience as IT professionals with two to three years of experience using Microsoft SQL Server 2005 or above, so if you've already been using SQL Server, then this exam would be the perfect next step to keep your certifications up to date.
70-432 Exam Requirements
The MCTS Exam 70-432 focuses on 8 main areas:
- Installing and configuring
- Maintaining instances
- Managing security
- Database maintenance
- Data management
- Monitoring and troubleshooting
- High availability
Let's take a closer look at the 8 areas.
1. Installing and Configuring SQL Server 2008 (10%)
Installing and configuring Microsoft SQL Server 2008 takes up 10% of the exams questions and assumes you are familiar with:
- installing and configuring server instances including using the configuration manager and SQL browser;
- the standard of being able to install Microsoft SQL Server 2008 and related services as well as knowing the installation paths, file locations, etc. You can refer to this MSDN article on how to install Microsoft SQL Server 2008;
- being able to configure additional SQL Server components such as SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), SQL Server Reporting Services (SSRS), etc.;
- being able to implement database mail from installation, set up, and configuration;
- and finally, it assumes you know how to configure, use, and populate full-text indexing.
2. Maintaining SQL Server Instances (13%)
The next focus point of the 70-432 exam is Maintaining SQL Server instances and makes up 13% of the questions on the exam. This focus point will test your knowledge and ability to:
- manage SQL Server Agent jobs, operators, and alerts including creating, scheduling, enabling, and disabling jobs;
- changing job step orders, managing notifications of job execution, and job logging;
- managing performance condition alerts, SQL event alerts, and Windows Management Instrumentation (WMI) alerts;
- understand and manage operator schedules, be able to fail safe and add new operators, and have a working knowledge of operator notification methods;
- implement the Declarative Management Framework (DMF) including but not limited to creating, verifying, and enforcing a policy, scheduling a policy compliance check, and creating a condition;
- and back up a SQL Server environment on an operating system level.
3. Managing SQL Server Security (15%)
The second largest focus point of the exam is based on managing SQL Server security and will make up 15% of the exam. This section of the exam will test your knowledge and ability to:
- manage login, server, user, and database roles including being able to create, enable, and disable logins;
- manage the security model in authentication mode, alter logins, enforce password policy, and manage fixed server roles;
- manage user mapping, user-defined and fixed roles, and creating and deleting user roles;
- SQL Server instance, database, schema, and object permissions including but not limited to logon triggers;
- permissions vs. fixed role membership, cross-database ownership chaining, impersonation, and schema ownership;
- understand transparent data encryption and impact of transparent data encryption on backups
- use DDL triggers and logon triggers to audit SQL Server instances and be familiar with C2, common criteria in SQL Server instance audits, login failures, and event notifications
- configure surface area through “sp_configure,” a function that displays and will allow you to change global configuration settings for the current SQL server (more on “sp_configure” here).
4. Maintaining a SQL Server Database (16%)
The next and largest focus point in the exam as listed by Microsoft's learning center, is based on maintaining an SQL Server database and makes up about 16% of the exam's total questions. This subject will require you to know:
- how to backup and restore databases, as well as understand the different types of backups, such as differential backups, full backups, compressed backups, etc;
- manage and configure databases and database snapshots, this includes creating, dropping, and reverting snapshots, as well as understanding database configurations options and recovery models;
- maintain database integrity (using DBCC CHECKDB and suspect pages);
- use maintenance plans along the way using the Maintenance Plan Wizard and Designer.
5. Performing Data Management Tasks (14%)
The next focus point takes up 14% of the exam and consists of questions related to performing data management tasks This includes importing and exporting data, managing data partitions, implementing data compression, maintaining indexes, and managing collations. These are, for the most part, just based on day to day use tasks such as working with indexes and using GUI tools to import and export data.
That's a Lot of Expectations!
So far, there's quite a bit of information to take in, don't worry though, while the exam is very technical, it is also based on the most important aspects of SQL Server 2008. Which means during your training, while you may go over a lot you already know, you'll be picking up a lot of great new information to help you along your career path. There are a lot of tasks, optimizations, and security aspects that you'll learn along the way that will make your life easier in the long run, as well as help you pass the exam itself.
Back to the exam.
The final three sections of the 70-432 exam make up the remaining 32% of the exam:
6. Monitoring and Troubleshooting SQL Server (13%)
Next topic covers monitoring and troubleshooting, which makes up 13% of the exam. This focus point will test your ability to:
- identify SQL Server problems in the database engine, SQL agent, and SQL browser services;
- identify concurrency problems (blocks, locks, deadlocks, etc.) and SQL Agent job execution problems (proxy accounts, credentials, and job history);
- locate error information such as error and agent logs, job execution history, and event logs.
7. Optimizing SQL Server Performance (10%)
Optimizing SQL Server performance is an important part of managing and running a successful SQL Server, and will take up 10% of the exam. This section mostly deals with using various sub-applications to optimize and analyze the server. You will be expected to be able to implement Resource Governor, use the Database Engine Tuning Advisor, collect trade data using SQL Server Profiler, collect performance data by using Dynamic Management Views (DMVs) and System Monitor, and finally, be able to use Performance Studio.
8. Implementing High Availability (9 percent)
The final section and focus point of the exam is based on Implementing High Availability (database replication/uptime assurance) and takes up the final 9% of the exam. This final section will ask that you understand how to implement database mirroring, clustered instances, log shipping, and replication as well as how to monitor, configure, and failover if necessary.
SQL Server 2008 Database Administration Training
As with all exam preparations, your training should come from a variety of sources. Your real world experience should compliment your training, be it in books, online, videos, etc.
Train Signal's current SQL Server 2008 Administration training is geared directly at the 70-432 MCTS exam that we've been going over.
It covers every major focus point and more, including SQL Server performance, security, and automation. Your real world experience partnered with Train Signal's SQL Server 2008 training is a great way to secure your MCTS certification.
Learn more about Train Signal's SQL Server 2008 Administration training and keep an eye out for my next article, which will outline the exam that will bump up your SQL MCTS to MCITP.