70431考试指南及题库分享.docx

70431考试指南及题库分享.docx

《70431考试指南及题库分享.docx》由会员分享，可在线阅读，更多相关《70431考试指南及题库分享.docx（34页珍藏版）》请在冰豆网上搜索。

70431考试指南及题库分享

70-431考试资料

MicrosoftSQLServer2005Implementation&Maintenance

科目编号：

70-431

科目名称：

MicrosoftSQLServer2005Implementation&Maintenance

语言（s）：

英语，法语，德语，日语，中文（简体）

观众（补）：

IT专业人士

技术：

MicrosoftSQLServer2005

类型：

监考考试

相关分类：

MCITP

70-431考试是Microsoft公司的MicrosoftSQLServer2005Implementation&Maintenance认证考试官方代号，MicrosoftSQLServer2005Implementation&Maintenance认证作为全球IT领域专家Microsoft热门认证之一，是许多大中IT企业选择人才标准的必备条件。

70-431考试概述：

AboutthisExam

ThisMicrosoftTechnicalSpecialist（TS）exam,Exam70-431:

TS:

MicrosoftSQLServer2005–ImplementationandMaintenance,becameavailableinJanuary2006.Thisexammaycontainperformance-basedtestingelements.

AudienceProfile

Candidatesforthisexamareprofessionalswhotypicallypursuecareersasdatabaseadministrators,databasedevelopers,orbusinessintelligencedevelopers.Additionally,theycanbepeoplewhodonotworkwithMicrosoftSQLServerasapartoftheirprimaryjobfunctionsbutwhowanttoshowtheirbreadthoftechnologyexperience,suchasdevelopers,systemsadministrators,andothers.CandidateshavesoughtoutknowledgeandexperiencewithMicrosoftSQLServer2005toenhancetheirtechnicalexpertise,andtheycanimplementandmaintaindatabasesbyusingspecificinstructionsandspecifications.

Candidatesforthisexamshouldbeexpertsinfollowingareas:

Toolsusage

UInavigation

Wizardsusage

Writingcodeintheappropriatelanguage（Transact-SQL,CLRlanguage,andotherscriptinglanguages）

Codedebuggingorsyntacticissueresolution

Troubleshooting

AccomplishingspecificfocusedtasksbyusingcodeorUInavigation

CreditTowardCertification

WhenyoupassExam70-431:

TS:

MicrosoftSQLServer2005-ImplementationandMaintenance,youcompletetherequirementsforthefollowingcertification（s）:

MicrosoftCertifiedTechnologySpecialist:

SQLServer2005

Exam70-431:

TS:

MicrosoftSQLServer2005-ImplementationandMaintenance:

countsascredittowardthefollowingcertification（s）:

MCITP:

DatabaseAdministrator

MCTSandMCITPupgradefromMCDBA

MCITP:

DatabaseDeveloper

MCSAonMicrosoftWindows2000

MCSAonWindowsServer2003

MCSEonMicrosoftWindows2000

MCSEonWindowsServer2003

70-431考试大纲：

SkillsBeingMeasured

Thisexammeasuresyourabilitytoaccomplishthetechnicaltaskslistedbelow.

Thepercentagesindicatetherelativeweightofeachmajortopicareaontheexam.

InstallingandConfiguringSQLServer2005

InstallSQLServer2005.

Verifyprerequisites.

UpgradefromanearlierversionofSQLServer.

Createaninstance.

ConfigureSQLServer2005instancesanddatabases.

Configurelogfilesanddatafiles.

ConfiguretheSQLServerDatabaseMailsubsystemforaninstance.

Choosearecoverymodelforthedatabase.

ConfigureSQLServersecurity.

Configureserversecurityprincipals.

Configuredatabasesecurables.

Configureencryption.

ConfigurelinkedserversbyusingSQLServerManagementStudio（SSMS）.

Identifytheexternaldatasource.

Identifythecharacteristicsofthedatasource.

Identifythesecuritymodelofthedatasource.

ImplementingHighAvailabilityandDisasterRecovery

Implementdatabasemirroring.

Preparedatabasesfordatabasemirroring.

Createendpoints.

Specifydatabasepartners.

Specifyawitnessserver.

Configureanoperatingmode.

Implementlogshipping.

Initializeasecondarydatabase.

Configurelogshippingoptions.

Configurealogshippingmode.

Configuremonitoring.

Managedatabasesnapshots.

Createasnapshot.

Revertadatabasefromasnapshot.

SupportingDataConsumers

Retrievedatatosupportadhocandrecurringqueries.

ConstructSQLqueriestoreturndata.

FormattheresultsofSQLqueries.

Identifycollationdetails.

Manipulaterelationaldata.

Insert,update,anddeletedata.

Handleexceptionsanderrors.

Managetransactions.

ManageXMLdata.

Identifythespecificstructureneededbyaconsumer.

RetrieveXMLdata.

ModifyXMLdata.

ConvertbetweenXMLdataandrelationaldata.

CreateanXMLindex.

LoadanXMLschema.

ImplementanHTTPendpoint.

CreateanHTTPendpoint.

CreateanHTTPendpoint.

ImplementServiceBrokercomponents.

Createservices.

Createqueues.

Createcontracts.

Createconversations.

Createmessagetypes.

Sendmessagestoaservice.

Routeamessagetoaservice.

Receivemessagesfromaservice.

Importandexportdatafromafile.

Setadatabasetothebulk-loggedrecoverymodeltoavoidinflatingthetransactionlog.

Runthebcputility.

PerformaBulkInserttask.

ImportbulkXMLdatabyusingtheOPENROWSETfunction.

CopydatafromonetabletoanotherbyusingtheSQLServer2005IntegrationServices（SSIS）ImportandExportWizard.

Managereplication.

Distinguishbetweenreplicationtypes.

Configureapublisher,adistributor,andasubscriber.

Configurereplicationsecurity.

Configureconflictresolutionsettingsformergereplication.

Monitorreplication.

Improvereplicationperformance.

Planfor,stop,andrestartrecoveryprocedures.

MaintainingDatabases

ImplementandmaintainSQLServerAgentjobs.

Setajobowner.

Createajobschedule.

Createjobsteps.

Configurejobsteps.

Disableajob.

Createamaintenancejob.

Setupalerts.

Configureoperators.

Modifyajob.

Manageajob.

ManagedatabasesbyusingTransact-SQL.

Manageindexfragmentation.

Managestatistics.

Shrinkfiles.

PerformdatabaseintegritychecksbyusingDBCCCHECKDB.

Backupadatabase.

Performafullbackup.

Performadifferentialbackup.

Performatransactionlogbackup.

InitializeamediasetbyusingtheFORMAToption.

Appendoroverwriteanexistingmediaset.

Createabackupdevice.

Backupfilegroups.

Backupadatabase.

Performafullbackup.

Performadifferentialbackup.

Performatransactionlogbackup.

InitializeamediasetbyusingtheFORMAToption.

Appendoroverwriteanexistingmediaset.

Createabackupdevice.

Backupfilegroups.

Restoreadatabase.

Identifywhichfilesareneededfromthebackupstrategy.

Restoreadatabasefromasinglefileandfrommultiplefiles.

Chooseanappropriaterestoremethod.

Moveadatabasebetweenservers.

Chooseanappropriatemethodformovingadatabase.

MonitoringandTroubleshootingSQLServerPerformance

GatherperformanceandoptimizationdatabyusingtheSQLServerProfiler.

Startanewtrace.

Savethetracelogs.

ConfigureSQLServerProfilertraceproperties.

ConfigureaSystemMonitorcounterlog.

CorrelateaSQLServerProfilertracewithSystemMonitorlogdata.

GatherperformanceandoptimizationdatabyusingtheDatabaseEngineTuningAdvisor.

BuildaworkloadfilebyusingtheSQLServerProfiler.

TuneaworkloadfilebyusingtheDatabaseEngineTuningAdvisor.

Saverecommendedindexes.

Monitorandresolveblocksanddeadlocks.

Identifythecauseofablockbyusingthesys.dm_exec_requestssystemview.

Terminateanerrantprocess.

ConfigureSQLServerProfilertraceproperties.

Identifytransactionblocks.

Diagnoseandresolvedatabaseservererrors.

Connecttoanon-responsiveserverbyusingthededicatedadministratorconnection（DAC）.

ReviewSQLServerstartuplogs.

Reviewerrormessagesineventlogs.

MonitorSQLServerAgentjobhistory.

Identifythecauseofafailure.

Identifyoutcomedetails.

Findoutwhenajoblastran.

GatherperformanceandoptimizationdatabyusingDMVs.

CreatingandImplementingDatabaseObjects

Implementatable.

Specifycolumndetails.

Specifythefilegroup.

Assignpermissionstoarolefortables.

Specifyapartitionschemewhencreatingatable.

Specifyatransaction.

Implementaview.

Createanindexedview.

Createanupdateableview.

Assignpermissionstoaroleorschemaforaview.

Implementtriggers.

Createatrigger.

CreateDDLtriggersforrespondingtodatabasestructurechanges.

Identifyrecursivetriggers.

Identifynestedtriggers.

Identifytransactiontriggers.

Implementfunctions.

Createafunction.

Identifydeterministicversusnondeterministicfunctions.

Implementstoredprocedures.

Createastoredprocedure.

Recompileastoredprocedure.

Assignpermissionstoaroleforastoredprocedure.

Implementconstraints.

Specifythescopeofaconstraint.

Createanewconstraint.

Implementindexes.

Specifythefilegroup.

Specifytheindextype.

Specifyrelationalindexoptions.

Specifycolumns.

Specifyapartitionschemewhencreatinganindex.

Disableanindex.

CreateanonlineindexbyusinganONLINEargument.

Createuser-definedtypes.

CreateaTransact-SQLuser-definedtype.

Specifydetailsofthedatatype.

CreateaCLRuser-definedtype.

Implementafull-textsearch.

Createacatalog.

Createanindex.

Specifyafull-textpopulationmethod.

70-431考试题库；

Exam:

Microsoft70-431

Title:

MicrosoftSQLServer2005Implementation&Maintenance

1.AnewemployeeneedsaccesstoaSQLServer?

2005databasethatislocatedonaservernamedSQL1.

YoucreatealoginnamedajonesbyusingthefollowingTransactSQL

statement.

CREATELOGINajonesWITHPASSWORD='SQLServer$1'

Thenewemployeereportsthatwhenhelogsin,hereceivesthefollowingerrormessage:

"Loginfailed.The

userisnotassociatedwithatrustedSQLServerconnection."

YouneedtoresolvetheerrorandallowthenewemployeetogainaccesstoSQL1.

Whatshouldyoudo?

A.ChangetheSQLServersecuritymodefromWindowsAuthenticationmodetoSQLServerandWindows

Authenticationmode.

B.ChangetheSQLServersecuritymodefromSQLServerandWindowsAuthenticationmodetoWindows

Authenticationmode.

C.Ensurethattheloginnameiscreatedwithsquarebrackets（[]）.

D.GivetheloginaccesstoaspecificdatabasebyusingtheCREATEUSERTransactSQL

statement.

Answer:

A

2.ApplicationdevelopersinyourcompanycreateanassemblythatcontainsaCL