微软认证70—432试题库.doc

70-43270-432 考试是 Microsoft 公司的 TS:MS SQL Server 2008,Implementation and Maintenance 认证考试官方代号，TS:MS SQL Server 2008,Implementation and Maintenance 认证作为全球IT领域专家 Microsoft 热门认证之一，是许多大中IT企业选择人才标准的必备条件。70-432 考试概述：Audience ProfileCandidates should have one to two years of experience using SQL Server 2008 or two to three years of experience using a previous version of SQL Server. Candidates for this exam are IT professionals who have jobs in which database administration is their main area of responsibility, or they work in environments in which databases are central to their job roles. These job roles include the following:IT generalists who work in environments in which SQL Server 2008 is part of an overall solutionIT professionals who manage systems that run SQL Server 2008IT professionals who work with third-party applications that are built on SQL Server 2008Developers who build applications that use SQL Server 2008Credit Toward CertificationWhen you pass Exam 70-432: TS: Microsoft SQL Server 2008, Implementation and Maintenance, you complete the requirements for the following certification(s):Microsoft Certified Technology Specialist (MCTS): Microsoft SQL Server 2008, Implementation and MaintenanceExam 70-432: TS: Microsoft SQL Server 2008, Implementation and Maintenance: counts as credit toward the following certification(s):MCSA, MCSE70-432 考试大纲：Skills Being MeasuredThis 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.Installing and Configuring SQL Server 2008 (10 percent)Install SQL Server 2008 and related services.This objective may include but is not limited to: file locations; default paths; service accountsConfigure SQL Server instances.This objective may include but is not limited to: sp_configureConfigure SQL Server services. This objective may include but is not limited to: configuration manager; SQL browserConfigure additional SQL Server components. This objective may include but is not limited to: SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), SQL Server Reporting Services (SSRS), replicationImplement database mail. This objective may include but is not limited to: set up and configureConfigure full-text indexing. This objective may include but is not limited to: enable/disable, index populationMaintaining SQL Server Instances (13 percent)Manage SQL Server Agent jobs.This objective may include but is not limited to: create and schedule jobs; notification of job execution; disable/enable jobs; change job step order; loggingManage SQL Server Agent alerts.This objective may include but is not limited to: performance condition alerts; SQL event alerts; Windows Management Instrumentation (WMI) alertsManage SQL Server Agent operators.This objective may include but is not limited to: operator schedules; fail safe operator; add a new operator; notification methodsImplement the declarative management framework (DMF).This objective may include but is not limited to: create a policy; verify a policy; schedule a policy compliance check; enforce a policy; create a conditionBack up a SQL Server environment. This objective may include but is not limited to: operating system-level conceptsManaging SQL Server Security (15 percent)Manage logins and server roles.This objective may include but is not limited to: create logins; disable/enable logins; security model (authentication mode); password policy enforcement; fixed server roles; alter loginsManage users and database roles. This objective may include but is not limited to: user mapping; user-defined roles; fixed roles; guest, public, dbo; creating and deleting user rolesManage SQL Server instance permissions.This objective may include but is not limited to: logon triggers; permissions vs. fixed role membership; cross-database ownership chaining; impersonationManage database permissions.This objective may include but is not limited to: impersonation; cross-database ownership chainingManage schema permissions and object permissions.This objective may include but is not limited to: manage schema ownershipAudit SQL Server instances.This objective may include but is not limited to: use DDL triggers and logon triggers; C2; common criteria; login failures; event notificationsManage transparent data encryption.This objective may include but is not limited to: impact of transparent data encryption on backupsConfigure surface area.This objective may include but is not limited to: sp_configureMaintaining a SQL Server Database (16 percent)Back up databases.This objective may include but is not limited to: full backups; differential backups; transaction log; compressed backups; file and filegroup backups; verifying backupRestore databases.This objective may include but is not limited to: online restores; full restores; differential restores; transaction log; file and filegroup restores; verifying restore; tail of the transaction logManage and configure databases.This objective may include but is not limited to: files, file groups, and related options; database options; recovery model; attach/detach dataManage database snapshots.This objective may include but is not limited to: create, drop, revertMaintain database integrity.This objective may include but is not limited to: DBCC CHECKDB; suspect pagesMaintain a database by using maintenance plans.This objective may include but is not limited to: Maintenance Plan Wizard; Maintenance Plan DesignerPerforming Data Management Tasks (14 percent)Import and export data.This objective may include but is not limited to: BCP; BULK INSERT; OPENROWSET; GUI toolsManage data partitions.This objective may include but is not limited to: switching data from one partition to another; add a filegroup; alter a partition function; alter a partition schemeImplement data compression.This objective may include but is not limited to: sparse columns; page/rowMaintain indexes.This objective may include but is not limited to: 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/offlineManage collations.This objective may include but is not limited to: column collation; database collation; instance collationMonitoring and Troubleshooting SQL Server (13 percent)Identify SQL Server service problems.This objective may include but is not limited to: DB Engine service; SQL Agent service; SQL Browser serviceIdentify concurrency problems.This objective may include but is not limited to: blocks, locks, deadlocks, activity monitor; relevant Dynamic Management ViewsIdentify SQL Agent job execution problems.This objective may include but is not limited to: proxy accounts; credentials; job historyLocate error information.This objective may include but is not limited to: error log; agent log; job execution history; event logsOptimizing SQL Server Performance (10 percent)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 (DMVs).Collect performance data by using System Monitor.Use Performance Studio.oImplement database mirroring.This objective may include but is not limited to: monitoring, configuring, failoverImplement a SQL Server clustered instance.This objective may include but is not limited to: monitoring, configuring, failoverImplement log shipping.This objective may include but is not limited to: monitoring, configuring, failoverImplement replication.This objective may include but is not limited to: monitoring, configuring, failoverMicrosoft 70-432Exam AQUESTION 1You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the administrator of the company database. Now you are in charge of a SQL Server 2008instance. There is a computer which hosts several applications in your company. The instance runs on the computer. A job named DiliveryList is created by you. This job requires a file to be writtento a file server. But because the job cannot access the file server, it fails to run. You intend to configure the SQL Server Agent service so that only the SQL Server Agent service has read and writeaccess to the file server. Which account type should you use?A.You should use local Service accountB.You should use network Service accountC.You should use domain accountD.You should use local System accountAnswer: CSection: (none)Explanation/Reference:QUESTION 2（知识点：备份与恢复）You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the administrator of the company database. Now you are in charge of a SQL Server 2008instance. There is a database named DB1 in the instance. On DB1, quite few logged operations are performed. Now according to the requirement of the company CIO, you must validate that thedatabase can be restored to a specific point in time.(特定时间点) So what action should you perform to achieve this goal?A.You should verify that the simple recovery model is used by the databaseB.You should verify that the full recovery model is used by the database.C.You should verify that the checksum page verify option is used by the databaseD.You should verify that the bulk-logged recovery（大容量日志恢复） model is used by the databaseAnswer: BSection: (none)Explanation/Reference:QUESTION 3You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the administrator of the company database. Now you are in charge of a SQL Server 2008instance. There is a SQL Server 7.0 database. You upgrade the database to the SQL Server instance. According to the company requirement, you must make sure that suspect pages can be detectedin the database. So what action should you perform to achieve this goal?A.For the database, you should turn on the TRUSTWORTHY database optionB.For the database, the PAGE_VERIFY database option should be set to CHECKSUMC.For the database, the database compatibility level option should be set to 10.D.For the model database, the PAGE_VERIFY database option should be set to TORN_PAGE_DETECTION.Answer: BSection: (none)Explanation/Reference:QUESTION 4 (恢复)You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the administrator of the company database. Now you are in charge of a SQL Server 2008instance. You configure the SQL Server instance to use the -T1222 and -T1204 trace flags during startup. You must make sure that your failure recovery plan performs backup of the use of the traceflags.So what action should you perform to achieve this goal?A.You should backup the master databaseB.You should backup the default.trc fileC.You should backup the SQL Server registry hive.D.You should backup the resource databaseAnswer: CSection: (none)Explanation/Reference:QUESTION 5You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the administrator of the company database. Now you are in charge of a SQL Server 2008instance. According to the company requirement, the names of all user-defined stored procedures must contain the prefix usp_ on all instances. Besides this, you must make sure that stored procedures that do not contain this prefix cannot be created by you.What should you do?A.A policy should be created. The policy targets the name of the stored procedure that is evaluated on demand.B.A policy should be created. The policy targets the name of the stored procedure that is evaluated on change.C.A condition should be created. The condition targets the name of the stored procedure that is evaluated on changeD.A condition should be created. The condition targets the name of stored procedure that is evaluated on demand.Answer: BSection: (none)Explanation/Reference:QUESTION 6You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the administrator of the company database. Now you are in charge of a SQL Server 2008instance. There is an Internet Information Services application. This application will use anonymous access to access the SSAS instance. You must make sure that the application can access the SSASinstance. What should you do?A.The SecurityRequireClientAuthentication server configuration should be set to FalseB.The SecurityRequireClientAuthentication server configuration should be set to True.C.The NTLM Security Support Provider Interface (SSPI) provider should be added to the SecuritySecurityPackageList server configuration.D.The Kerberos Security Support Provider Interface (SSPI) provider should be added to the SecuritySecurityPackageList server configurationAnswer: ASection: (none)Explanation/Reference:QUESTION 7You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the administrator of the company database. Now for a new application, a SQL Server 2008 instance has to be installed on an existing server. The server contains a default SQL Server 2005 instance. Your company CIO wants their corresponding certified third-party applications can access to both database instances. The company assigns this task to you. You must achieve this by using as little database administrative effort as possible, and never change the existing application environments.What should you do?A.You should install SQL Server 2008 as the default instance, and configure the new application to use the default instanceB.You should upgrade the SQL Server 2005 application to use SQL Server 2008C.You should upgrade the SQL Server 2005 instance to a SQL Server 2008 instanceD.You should install SQL Server 2008 as a named instance, and make the new application use the new instance.Answer: DSection: (none)Explanation/Reference:QUESTION 8You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the administrator of the company database. Now you are in charge of a SQL Server 2008instance. Now your company CIO assigns a task to you. The company CIO wants you to configure FILESTREAM data and the two requirements below must be met: you must enable FILESTREAMfor file I/O streaming access; remote client computers must be able to have streaming access to FILESTREAM data.You must make sure that FILESTREAM data is enabled. Which service should you configure?A.You should configure SQL Server VSS WriterB.You should configure Distributed File SystemC.You should configure SQL ServerD.You should configure SQL Server Full TextAnswer: CSection: (none)Explanation/Reference:QUESTION 9You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the administrator of the company database. Now you are in charge of two instances both of which run on the same computer. One is a SQL Server 2008 instance; another is a SQL Server 2005 instance. There is a database named DB1 in the SQL Server 2008 instance. DB1 uses the Fulltext indexes. Several records that include the word root are added to DB1. An empty resultset is returned when the Fulltext index is queried for the word root. You must make sure the query canreturn records that contain the word root. What should you do?A.You should terminate and restart the MSFTESQL serviceB.You should rebuild the full-text indexC.The word root should be added to the stop listD.The word root should be added to the thesaurus fileAnswer: BSection: (none)Explanation/Reference:QUESTION 10（策略）You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the administrator of the company database. Now you are in charge of a SQL Server 2008 instance. You must make sure that all SQL Server instances are consistently configured for naming conventions, security settings, force index creation and avoidance of data fragmentation. So whataction should you perform to achieve this goal?A.You should use the Database Engine Tuning Advisor.B.In Microsoft SQL Server Management Studio, you should create a maintenance planC.You should use the SQL Server Configuration ManagerD.In Microsoft SQL Server Management Studio, you should create a policyAnswer: DSection: (none)Explanation/Reference:QUESTION 11 （分区表）You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the administrator of the company database. Now you are in charge of a SQL Server 2008 nstance. There is a database named Dworks in the instance. The Dworks database has a table named Orderthings. The Orderthings table is partitioned on the OrderId column. The first partitioncontains integer values greater than 100,000, while the second partition contains integer values between 1 and 100,000. You have to add a new partition. The new partition should contain integer values greater than 200,000. What should you do?A.A new partition function should be createdB.You should change the existing partition schemeC.You should use a Merge clause to change the existing partition functionD.You should use a Split clause to change the existing partition functionAnswer: DSection: (none)Explanation/Reference:QUESTION 12（死锁，trace）You work in a company which is named Wiikigo Corp. The