70431考试指南及题库分享Word文件下载.docx

70431考试指南及题库分享Word文件下载.docx

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

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:

MicrosoftSQLServer2005-ImplementationandMaintenance,youcompletetherequirementsforthefollowingcertification（s）:

MicrosoftCertifiedTechnologySpecialist:

SQLServer2005

Exam70-431:

MicrosoftSQLServer2005-ImplementationandMaintenance:

countsascredittowardthefollowingcertification（s）:

MCITP:

DatabaseAdministrator

MCTSandMCITPupgradefromMCDBA

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.

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.

Restoreadatabase.

Identifywhichfilesareneededfromthebackupstrategy.

Restoreadatabasefromasinglefileandfrommultiplefiles.

Chooseanappropriaterestoremethod.

Moveadatabasebetweenservers.

Chooseanappropriatemethodformovingadatabase.

MonitoringandTroubleshootingSQLServerPerformance

GatherperformanceandoptimizationdatabyusingtheSQLServerProfiler.

Startanewtrace.

Savethetracelogs.

ConfigureSQLServerProfilertraceproperties.

ConfigureaSystemMonitorcounterlog.

CorrelateaSQLServerProfilertracewithSystemMonitorlogdata.

GatherperformanceandoptimizationdatabyusingtheDatabaseEngineTuningAdvisor.

BuildaworkloadfilebyusingtheSQLServerProfiler.

TuneaworkloadfilebyusingtheDatabaseEngineTuningAdvisor.

Saverecommendedindexes.

Monitorandresolveblocksanddeadlocks.

Identifythecauseofablockbyusingthesys.dm_exec_requestssystemview.

Terminateanerrantprocess.

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.

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&

amp;

1.AnewemployeeneedsaccesstoaSQLServer?

2005databasethatislocatedonaservernamedSQL1.

YoucreatealoginnamedajonesbyusingthefollowingTransactSQL

statement.

CREATELOGINajonesWITHPASSWORD='

SQLServer$1'

Thenewemployeereportsthatwhenhelogsin,hereceivesthefollowingerrormessage:

"

Loginfailed.The

userisnotassociatedwithatrustedSQLServerconnection."

YouneedtoresolvetheerrorandallowthenewemployeetogainaccesstoSQL1.

Whatshouldyoudo?

A.ChangetheSQLServersecuritymodefromWindowsAuthenticationmodetoSQLServerandWindows

Authenticationmode.

B.ChangetheSQLServersecuritymodefromSQLServerandWindowsAuthenticationmodetoWindows

C.Ensurethattheloginnameiscreatedwithsquarebrackets（[]）.

D.GivetheloginaccesstoaspecificdatabasebyusingtheCREATEUSERTransactSQL

Answer:

A

2.ApplicationdevelopersinyourcompanycreateanassemblythatcontainsaCL