Published: September 30, 2008
Languages: English, Japanese
Audiences: IT professionals
Technology: Microsoft SQL Server 2008
Credit toward certification: MCP, MCSA

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. View video tutorials about the variety of question types on Microsoft exams.

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

Do you have feedback about the relevance of the skills measured on this exam? Please send Microsoft your comments. All feedback will be reviewed and incorporated as appropriate while still maintaining the validity and reliability of the certification process. Note that Microsoft will not respond directly to your feedback. We appreciate your input in ensuring the quality of the Microsoft Certification program.

If you have concerns about specific questions on this exam, please submit an exam challenge.

If you have other questions or feedback about Microsoft Certification exams or about the certification program, registration, or promotions, please contact your Regional Service Center.

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
Configuring 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

Maintaining 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
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
Viewing 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

Preparation resources
Technical considerations and best practices for disaster recovery and high availability for SQL Server
Database mirroring and failover clustering
Deployment (replication)

---

Sample Questions

---

QUESTION 1
You maintain a SQL Server 2008 instance that contains a database named DB1. DB1
stores customer data for the company. The customers use a Web application to access their profile data. You need to protect the customer data such that data files, log files, and subsequent backups are as secure as possible even if the backup media is lost. Your solution must not affect the Web application or impact performance.
What should you do?

A. Encrypt the customer data at the cell level and then back up DB1.
B. Configure access to DB1 to only use stored procedures and functions.
C. Enable Transparent Database Encryption for DB1 and then back up the transaction logs.
D. Encrypt the customer data at the folder level by using Encrypted File System (EFS) and then back up the transaction logs.

Answer: C

---

QUESTION 2
You administer a Microsoft SQL Server 2008 R2 database that contains an OrderItems table. The table has the following definition:

Data is grouped into quarterly partitions.
You need to configure the groupings into 12 monthly partitions.
What should you do?

A. Remove the clustered index from the table.
B. Use the ALTER PARTITION FUNCTION … SPLIT RANGE statement.
C. Use the ALTER TABLEstatement to remove the COLLATEoption.
D. Execute the DBCC CLEANTABLEcommand on the OrderItems table.
E. • Create a new filegroup.
• Create a new database file.
• Use the ALTER PARTITION SCHEME statement along with the NEXT USED clause.
• Use ALTER INDEX REORGANIZE statement. F. • Create a new Filegroup.
• Create a new database File.
• Use the ALTER PARTITION SCHEME statement along with the NEXT USED clause.
• Use the ALTER PARTITION FUNCTION statement along with the SPLIT RANGE clause. G. • Create a new table.
• Use the ALTER TABLE statement along with the SWITCH PARTITION clause.
• Use the ALTER PARTITION FUNCTION statement along with the MERGE RANGE clause.
H. • Create a new partition function.
• Create a new partition scheme.
• Add a clustered index to place the data onto the partition scheme.
I. Run the following statement:
CREATE PARTITION SCHEME SEC_FG AS PARTITION FUNC_FG
ALL TO (SECONDARY);
J. Run the following statement: EXECUTE sp_tableoption @TableNamePattern =’OrderItem3′, @OptionName= ‘PartltionByYear’; @OptionValue= ‘true’;

Answer: B

---

QUESTION 3
You administer a SQL Server 2008 instance.
You need to configure the instance to use a single thread for queries that have an estimated execution cost less than 3.
Which sp_configure configuration option should you set?

A. priority boost
B. precompute rank
C. max worker threads
D. query governor cost limit
E. cost threshold for parallelism

Answer: E

---

QUESTION 4
You design a maintenance plan for a SQL Server 2008 instance that contains a database named SalesDB.
The SalesDB database includes spatial indexes to support queries on spatial data.
You need to perform physical consistency checks on SalesDB. You also need to ensure that the performance effect on the SalesDB database is minimized.
Which Transact-SQL statement should you execute?

A. DBCC SYS_CHECK (SalesDB);
B. DBCC SQLPERF (SalesDB);
C. DBCC RSPAIRDB (SalesDB);
D. DBCC CHECKDB (SalesDB);

Answer: A

---

QUESTION 5
You administer a Microsoft SQL Server 2008 R2 instance.
You need to ensure that no suspect pages have been detected in your database.
What should you do?

A. Execute sp_helpfile.
B. Execute DBCC CHECKDB.
C. Examine the msdb..suspect_pagestable.
D. Execute DBCC CHECKDBalong with the REPAIR_FASTclause.
E. Execute DBCC CHECKDBalong with the REPAIR_REBUILDclause.
F. Restore the database from the most recent full backup. Apply any differential and log backups.
G. Use the ALTER DATABASEstatement along with the SET EMERGENCYclause.
H. Use the RESTORE DATABASEstatement along with the PAGESclause. Create a new log backup. Apply all differential and log backups, including the most recent backup.
I. Use the RESTORE DATABASEstatement along with the PAGESclause. Apply any differential and log backups. Create a new log backup and then restore the new log backup.

Answer: C

---

QUESTION 6
You are mastering the company database, On a SQL Server 2008, you find out that one of the data files computer is broken. You should reserve the database which is from the most recent configurations of backups. In order to cut the lost, you should find the method as quickly as possible. Which is the correct answer?

A. You should run a transaction log backup for the database.
B. You should reserve the old database backup for the database.
C. You should reserve the most recent store produce log backup for the database.
D. You should run the whole database backup.

Answer: A

 

 

Click here to view complete Q&A of 70-432 exam
Certkingdom Review

Best Microsoft MCTS Certification, Microsoft 70-432 Training at certkingdom.com