微软MCITP-DatabaseAdministrator认证考试题库1

70-450

QUESTION1

YouareaprofessionallevelSQLSever2008DatabaseAdministratorinaninternational

corporationnamedWiikigo.

Youareexperiencedinmanagingdatabasesinanenterprise-levelorganization,optimizingand

sustainingthedatabaselifecycle.

Inthecompany,yourjobistoimplementsolutionsonsecurity,troubleshooting,deploymentand

optimization.ASQLServer2008infrastructureismanagedbyyou.

Adatabaseisincludedbytheinstance,andatablenamedEmployeeDataiscontainedbythe

database.

ThereisacolumnnamedProfileintheEmployeeDatatable.MicrosoftOfficeWord2003

documentsarestoredinthecolumn.

ThespaceutilizedbytheProfilecolumnshouldbecuttotheleast.

Inaddition,areasonableperformanceshouldbemaintainedtoretrievethecontentofthecolumn.

Whichactionwillyouperform?

A.ROWcompressionshouldbeutilizedfortheEmployeeDatatable.

B.TheProfilecolumnshouldbestoredbyutilizingfilestreamstorage,andtheNTFSfilesystem

compressionshouldbeutilized.

C.PAGELEVELcompressionshouldbeutilizedfortheEmployeeDatatable.

D.TheProfilecolumnshouldbeimplementedasaCLRuser-definedtype.

Answer:B

Explanation/Reference:

Muchofthedatathatiscreatedeverydayisunstructureddata,suchastextdocuments,images,

andvideos.Thisunstructureddataisoftenstoredoutsidethedatabase,separatefromits

structureddata.Thisseparationcancausedatamanagementcomplexities.Or,ifthedatais

associatedwithstructuredstorage,thefilestreamingcapabilitiesandperformancecanbelimited.

FILESTREAMintegratestheSQLServerDatabaseEnginewithanNTFSfilesystemby

storingvarbinary(max)binarylargeobject(BLOB)dataasfilesonthefilesystem.

Transact-SQLstatementscaninsert,update,query,search,andbackupFILESTREAMdata.

Win32filesysteminterfacesprovidestreamingaccesstothedata.

FILESTREAMusestheNTsystemcacheforcachingfiledata.Thishelpsreduceanyeffectthat

FILESTREAMdatamighthaveonDatabaseEngineperformance.TheSQLServerbufferpoolis

notused;therefore,thismemoryisavailableforqueryprocessing.

Note:

FILESTREAMdataisnotencryptedevenwhentransparentdataencryptionisenabled.

QUESTION2

YouareaprofessionallevelSQLSever2008DatabaseAdministratorinaninternational

corporationnamedWiikigo.

Youareexperiencedinmanagingdatabasesinanenterprise-levelorganization,optimizingand

sustainingthedatabaselifecycle.

Inthecompany,yourjobistoimplementsolutionsonsecurity,troubleshooting,deploymentand

optimization.

ASQLServer2008instanceshouldbemanaged.

Itisreportedbythecustomersthattheserverperformancedegradedduetoanewly

implementedprocess.

DynamicManagementViewsisutilizedtoconfirmthatnolongrunningqueriesexist.The

operatingsystemperformancedatashouldbecorrelatedwiththeactualqueryexecutiontrace,

andtheleastadministrativeeffortshouldbeutilized.

Whichactionwillyouperformtofinishthetask?

A.Tofinishthetask,DataCollectorshouldbeutilized.

B.Tofinishthetask,theSQLdiag.exeutilityshouldbeutilized.

C.Tofinishthetask,SQLServerProfilerandthetracerpt.exeutilityshouldbeutilized.

D.Tofinishthetask,SQLServerProfilerandSystemMonitorshouldbeutilized.

Answer:D

Explanation/Reference:

SQLServerprofilerdisplaysdataaboutalargenumberofSQLServer

events.WhereasWindowsSystemMonitorgraphicallydisplaysinformation

abouttheserverinternals.Youcanmergethetwosetsofinformationand

walkthroughascenarioviewingbothperspectivesusingSQLServer

Profiler.

Tosetupthedual-perspectiveexperience,youneedtosimultaneouslycaptureserver

performanceusingbothPerformanceMonitor'sCounterLogsandSQLServerProfiler.Thesteps

todothisarelistedbelow:

1.ConfigureSystemMonitorwiththeexactcountersyouwanttoviewlater.Besuretogetthe

scaleandeverythingjustright.SetuptheCounterLogtotheexactsameconfiguration.

2.ConfigureProfilerwiththerightsetoftraceevents.Theymustincludethestartandendtime

datacolumnssothatProfilercanintegratethetwologslater.Savetheprofilerandclose

profiler.

3.ManuallystarttheCounterLog.OpenSQLProfilertracecodetostarttheserver-sidetrace.

4.Whenthetestiscomplete,stopboththecounterLogandtheserversidetrace.Youneedto

stoptheSQLProfilerwhichisanegativepointinthis.

5.Openprofilerandopenthesavedtracefile.

6.UsetheFile->lmportPerformanceDatamenucommandtoimporttheCounterLog.Youhave

theoptionofselectingonlytheimportantcountersfromtheperformancemonitor.Therewill

beperformanceissuesifyouselecttoomanycounters.

Source:

/faq/How_to_lntegrate_Performance_Monitor_and_SQL_

Profiler_p1.aspx

QUESTION3

YouareaprofessionallevelSQLSever2008DatabaseAdministratorinaninternational

corporationnamedWiikigo.

Youareexperiencedinmanagingdatabasesinanenterprise-levelorganization,optimizingand

sustainingthedatabaselifecycle.

Inthecompany,yourjobistoimplementsolutionsonsecurity,troubleshooting,deploymentand

optimization.ASQLServer2008infrastructureismanagedbyyou.

Thereisadatabaseintheinstance,andtheday-to-daybusinessofyourcompanyrequiresthe

database.Whenreportsareexecuted,slowresponsetimewillbeexperiencedbyUsers.

Aperformancemonitoringstrategywillbeimplementedbyyousoastohavethreeaspectsof

datacapturedandstored:

Blockinganddeadlockinformation,

ExecutedTransact-SQLstatements

QueryactivityandCountersfordisk,CPU,andmemory.

Youarerequiredtoutilizetheleastamountofadministrativeefforttoimplementthemonitoring

process.

Whichactionwillyouperformtofinishthetask?

A.Tofinishthetask,theclient-sideprofilertraceshouldbeutilized.

B.Tofinishthetask,thedynamicmanagementviewsshouldbeutilized.

C.Tofinishthetask,thedatacollectorshouldbeutilized.

D.Tofinishthetask,theSystemMonitorcounterlogtraceshouldbeutilized.

Answer:C

Explanation/Reference:

SQLServer2008providesadatacollectorthatyoucanusetoobtainandsavedatathatis

gatheredfromseveralsources.Thedatacollectorenablesyoutousedatacollectioncontainers,

whichenableyoutodeterminethescopeandfrequencyofdatacollectiononaSQLServer

serversystem.

Thedatacollectorprovidespredefinedcollectortypesthatyoucanusefordatacollection.The

collectortypesprovidetheactualmechanismforcollectingdataanduploadingittothe

managementdatawarehouse.Forthisreleaseofthedatacollector,thefollowingcollectortypeis

provided.

InThisSection

TopicDescription

T-SQLQueryCollectorTypeDescribesthestructureandbehavioroftheT-SQLQuerycollectortype.

SQLTraceCollectorTypeDescribesthestructureandbehavioroftheSQLTracecollectortype.

PerformanceCountersCollectorDescribesthestructureandbehaviorofthePerformanceCounters

Typecollectortype.

DescribesthestructureandbehavioroftheQueryActivitycollector

QueryActivityCollectorType

type.

QUESTION4

YouareaprofessionallevelSQLSever2008DatabaseAdministratorinaninternational

corporationnamedWiikigo.

Youareexperiencedinmanagingdatabasesinanenterprise-levelorganization,optimizingand

sustainingthedatabaselifecycle.

Inthecompany,yourjobistoimplementsolutionsonsecurity,troubleshooting,deploymentand

optimization.ASQLServer2008instanceshouldbemanagedbyyou.

Thecomputeronwhichtheinstancerunhasthefollowingthreefeatures:a64-GBRAM,four

quad-coreprocessors,andseveralindependentphysicalraidvolumes

Atransactionaldatabasewillbeimplementedontheinstance.Inaddition,thedatabaseshould

haveahighvolumeofINSERT,UPDATE,andDELETEactivities.

Creationofnewtablesiscontainedbytheactivities.

Youneedtomaximizediskbandwidthanddecreasethecontentioninthestorageallocation

structuressoastohavetheperformanceofthedatabaseoptimized.

Whichactionwillyouperformtofinishthetask?

A.Tofinishthetask,databaseandlogfilesshouldbeplacedonthesamevolume.

B.Tofinishthetask,theaffinitymaskoptionshouldbeconfiguredproperly.

C.Tofinishthetask,multipledatafilesshouldbecreateforthedatabase.

D.Tofinishthetask,theaffinityI/Omaskoptionshouldbeconfiguredproperly.

Answer:C

Explanation/Reference:

Ifyourdatabaseisverylargeandverybusy,multiplefilescanbeusedtoincrease

performance.Hereisoneexampleofhowyoumightusemultiplefiles.Let'ssayyouhavea

singletablewith10millionrowsthatisheavilyqueried.Ifthetableisinasinglefile,suchasa

singledatabasefile,thenSQLServerwouldonlyuseonethreadtoperformareadoftherowsin

thetable.Butifthetableweredividedintothreephysicalfiles,thenSQLServerwouldusethree

threads(oneperphysicalfile)toreadthetable,whichpotentiallycouldbefaster.Inaddition,if

eachfilewereonitsownseparatephysicaldiskordiskarray,theperformancegainwouldeven

begreater.

Essentially,themorefilesthatalargetableisdividedinto,thegreaterthepotentialperformance.

Ofcoursethereisapointwheretheadditionalthreadsaren'tofmuchusewhenyoumaxoutthe

serversI/O.ButupuntilyoudomaxouttheI/O,additionalthreads(andfiles)shouldincrease

performance

SOURCE:http7//tips/filegroups_p1.aspx

QUESTION5

YouareaprofessionallevelSQLSever2008DatabaseAdministratorinaninternational

corporationnamedWiikigo.

Youareexperiencedinmanagingdatabasesinanenterprise-levelorganization,optimizingand

sustainingthedatabaselifecycle.

Inthecompany,yourjobistoimplementsolutionsonsecurity,troubleshooting,deploymentand

optimization.ASQLServer2008infrastructureshouldbemanaged.

LogshippingshouldbeimplementedforseveraldatabasesonthreeSQLServerinstances.The

logsaremigratedtoafourthSQLServerinstance.Amanualfailoverwillbeimplemented.

Youneedtoensurethatthedatabaseapplicationsutilizethesecondaryserverafterfailover.

Sinceyouarethetechnicalsupport,youarerequiredtoconfirmthatthelatestdatashouldbe

availabletousers.

Whichactionsshouldyouperformtoachievethegoal?(Choosemorethanone)

A.Toachievethegoal,youshouldutilizetheWITHRECOVERYoptiononthelastlogtoapplyany

unappliedtransactionlogbackupsinsequencetoeachsecondarydatabase.

B.Toachievethegoal,youshouldredirectclientcomputerstothesecondaryinstance.

C.Toachievethegoal,youshouldreplicatealllogshippingnetworksharestothesecondaryinstance.

D.Toachievethegoal,youshouldutilizetheWITHNORECOVERYoptiontobackupthetailofthe

transactionlogofprimarydatabases.

E.Toachievethegoal,youshouldbackupalldatabasesonthesecondaryinstance.

Answer:ABD

Explanation/Reference:

Logshippingconsistsofthreeoperations:

1.Backupthetransactionlogattheprimaryserverinstance.

2.Copythetransactionlogfiletothesecondaryserverinstance.

3.Restorethelogbackuponthesecondaryserverinstance.

Thelogcanbeshippedtomultiplesecondaryserverinstances.Insuchcases,operations2and3

areduplicatedforeachsecondaryserverinstance.

Alogshippingconfigurationdoesnotautomaticallyfailoverfromtheprimaryservertothe

secondaryserver.Iftheprimarydatabasebecomesunavailable,anyofthesecondarydatabases

canbebroughtonlinemanually.

Tomakethetargetasthenewsourcedatabaseyouhaveto

1.Backupthetransactionlogtailinordertohavethelatesttransactionsandtoputthedatabase

inrecoverystate

BACKUPLOG[AdventureWorks]

TODISK=N'C:\ProgramFiles\MicrosoftSQL

Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\Adv.trn1

WITHNO_TRUNCATE,

NOFORMAT,

NO工NIT,

NAME=N*AdventureWorks-TransactionLogBackup,,

SKIP,

NOREW工ND,

NOUNLOAD,

NORECOVERY,

STATS=10

2.Afterhavingcopiedallthetransactionlogbackup,restoretheminorderand,forthelatest,

usetheWITHRECOVERYoption

3.Redirectalltheclientstothenewsourcedatabase

QUESTION6

YouareaprofessionallevelSQLSever2008DatabaseAdministratorinaninternational

corporationnamedWiikigo.

Youareexperiencedinmanagingdatabasesinanenterprise-levelorganization,optimizingand

sustainingthedatabaselifecycle.Inthecompany,yourjobistoimplementsolutionsonsecurity,

troubleshooting,deploymentandoptimization.

ASQLServer2008infrastructureismanagedbyyou.Afteraregulartest,youfindthat

performancedegradationisexperiencedbyaninstanceforthethreereasons:ExcessiveCPU

usage,ServerprocessespagingandDeadlocks.

Amonitoringsolutionshouldbeimplementedtoprovidedata,monitorandtroubleshoot

performanceissues.Anddetaileddeadlockinformationshouldbecontainedintheprovideddata.

Youshouldutilizetheleastamountofadministrativeefforttofinishthetask.

Whichtoolwillyouutilizetofinishthetask?

A.Tofinishthetask,youshouldutilizeResourceGovernor.

B.Tofinishthetask,youshouldutilizeDatabaseEngineTuningAdvisor.

C.Tofinishthetask,youshouldutilizeExtendedEvents.

D.Tofinishthetask,youshouldutilizePerformanceMonitor(SYSMON).

Answer:C

Explanation/Reference:

IntroducingSQLServerExtendedEvents

SQLServerExtendedEvents(ExtendedEvents)isageneralevent-handlingsystemforserver

systems.TheExtendedEventsinfrastructuresupportsthecorrelationofdatafromSQLServer,

andundercertainconditions,thecorrelationofdatafromtheoperatingsystemanddatabase

applications.Inthelattercase,ExtendedEventsoutputmustbedirectedtoEventTracingfor

Windows(ETW)inordertocorrelatetheeventdatawithoperatingsystemorapplicationevent

data.

Allapplicationshaveexecutionpointsthatareusefulbothinsideandoutsideanapplication.

Insidetheapplication,asynchronousprocessingmaybeenqueuedusinginformationthatis

gatheredduringtheinitialexecutionofatask.Outsidetheapplication,executionpointsprovide

monitoringutilitieswithinformationaboutthebehavioralandperformancecharacteristicsofthe

monitoredapplication.

ExtendedEventssupportsusingeventdataoutsideaprocess.Thisdataistypicallyusedby:

Tracingtools,suchasSQLTraceandSystemMonitor.

Loggingtools,suchastheWindowseventlogortheSQLServererrorlog.

Usersadministeringaproductordevelopingapplicationsonaproduct.

QUESTION7

YouareaprofessionallevelSQLSever2008DatabaseAdministratorinaninternational

corporationnamedWiikigo.

Youareexperiencedinmanagingdatabasesinanenterprise-levelorganization,optimizingand

sustainingthedatabaselifecycle.Inthecompany,yourjobistoimplementsolutionsonsecurity,

troubleshooting,deploymentandoptimization.

ASQLServer2008infrastructureismanagedbyyou.Thereare30branchofficesin

DoubleSecurityInsurance,andinthebranchoffices,customerdataarestoredinSQLServer

2008databases.

Customerdatashouldbesecuritycompliantifitisstoredthroughmultipledatabaseinstances.

YouintendtoutilizethePolicy-BasedManagementfeaturetodesignastrategyforcustom

policies.AndtheformatofcustompoliciesisXMLformat.Therequirementslistedbelowshould

besatisfied.

Thecompanydistributescustompoliciestoallinstances.Inaddition,thecompanyenforcesthe

policiesonallinstances.

Astrategyshouldbethoughtoutandtheminimumamountofadministrativeeffortshouldbe

utilized.

Whichactionshouldyouperformtofinishthetask?

A.Tofinishthetask,theDistributedFileSystemReplicationserviceshouldbeutilized.

B.Tofinishthetask,aconfigurationservershouldbeutilized.

C.Tofinishthetask,thepoliciesshouldbedistributedbyutilizingGroupPolicyObjects.

D.Tofinishthetask,thepoliciesshouldbedistributedbyutilizingtheActiveDirectorydirectoryservice.

Answer:B

Explanation/Reference:

ConfigurationServerorCentralManagementServer

InSQLServer2008,youcandesignateaninstanceofSQLServerasaCentralManagement

Server.CentralManagementServersstorealistofinstancesofSQLServerthatisorganizedinto

oneormoreCentralManagementServergroups.ActionsthataretakenbyusingaCentral

ManagementServergroupwillactonallserversintheservergroup.Thisincludesconnectingto

serversbyusingObjectExplorerandexecutingTransact-SQLstatementsandPolicy-Based

Managementpoliciesonmultipleserversatthesametime.AllCentralManagementServersand

subordinateserversmustberegisteredbyusingWindowsAuthentication.VersionsofSQL

ServerthatareearlierthanSQLServer2008cannotbedesignatedasaCentralManagement

Server.

QUESTION8

YouareaprofessionallevelSQLSever2008DatabaseAdministratorinaninternational

corporationnamedWiikigo.

Youareexperiencedinmanagingdatabasesinanenterprise-levelorganization,optimizingand

sustainingthedatabaselifecycle.Inthecompany,yourjobistoimplementsolutionsonsecurity,

troubleshooting,deploymentandoptimization.

ASQLServer2008databasesolutionismanagedbyyou.

Alldatachangesareimplementedthroughstoredprocedures,andonlytheINSERT,UPDATE,or

DELETEstatementsareutilizedbytheprocedures.Abackupstrategyshouldbeimplemented.

Thebusinessrequirementslistedbelowshouldbesatisfied.Point-in-timerecoveryforfailureis

supportedbythebackupstrategyatanytimeofday.

Inaddition,theleastamountofdiskspaceshouldbeutilizedbythetransactionlog.

Whichactionshouldyouperformtofinishthetask?

A.Tofinishthetask,hourlydatabasesnapshotsshouldbeutilized.

B.Tofinishthetask,thefull-recoverymodelalongwithtransactionlogbackupsshouldbeutilized.

C.Tofinishthetask,thefull-recoverymodelalongwithdifferentialbackupsshouldbeutilized.

D.Tofinishthetask,thesimple-recoverymodelalongwithdifferentialbackupsshouldbeutilized.

Answer:B

Explanation/Reference:

Full-recoveryModel

Description

Requireslogbackups.

Noworkislostduetoalostordamageddatafile.

Canrecovertoanarbitrarypointintime(forexample,priortoapplicationorusererror).

Worklossexposure

Normallynone.

Ifthetailofthelogisdamaged,changessincethemostrecentlogbackupmustberedone.

Recovertopointintime?

Canrecovertoaspecificpointintime,assumingthatyourbackupsarecompleteuptothat

pointintime.

QUESTION9

YouareaprofessionallevelSQLSever2008DatabaseAdministratorinaninternational

corporationnamedWiikigo.

Youareexperiencedinmanagingdatabasesinanenterprise-levelorganization,optimizingand

sustainingthedatabaselifecycle.Inthecompany,yourjobistoimplementsolutionsonsecurity,

troubleshooting,deploymentandoptimization.

ASQLServer2008instanceismanagedbyyou.

Adatabaseisutilizedbytheinstance,andthedatabaseisutilizedbyaWeb-basedapplication.

15,000transactionsareprocessedbytheapplicationeveryminute.Acolumniscontainedbya

tableinthedatabase,andthecolumnisutilizedonlybytheapplication.Sensitivedataisstoredin

thiscolumn.

Thesensitivedatashouldbestoredwiththehighestsecuritylevel.Inaddition,theleastamount

ofmemoryspaceandprocessortimeshouldbeutilized.

Fromthefollowingfourencryptiontypes,whichoneshouldyouutilize?

A.Asymmetrickeyencryptionshouldbeutilized.

B.Certificate-basedencryptionshouldbeutilized.

C.Symmetrickeyencryptionshouldbeutilized.

D.Transparentdataencryptionshouldbeutilized.

Answer:C

Explanation/Reference:

CryptographicKeyHierarchy

AttherootofencryptiontreeistheWindowsDataProtectionAPI(DPAPI),whichsecuresthekey

hierarchyatthemachinelevelandisusedtoprotecttheservicemasterkey(SMK)forthe

databaseserverinstance.TheSMKprotectsthedatabasemasterkey(DMK),whichisstoredat

theuserdatabaselevelandwhichinturnprotectscertificatesandasymmetrickeys.Theseinturn

protectsymmetrickeys,whichprotectthedata.TDEusesasimilarhierarchydowntothe

certificate.TheprimarydifferenceisthatwhenyouuseTDE,theDMKandcertificatemustbe

storedinthemasterdatabaseratherthanintheuserdatabase.Anewkey,usedonlyforTDE

andreferredtoasthedatabaseencryptionkey(DEK),iscreatedandstoredintheuserdatabase.

Thishierarchyenablestheservertoautomaticallyopenkeysanddecryptdatainbothcell-level

anddatabase-levelencryption.Theimportantdistinctionisthatwhencell-levelencryptionisused,

allkeysfromtheDMKdowncanbeprotectedbyapasswordinsteadofbyanotherkey.This

breaksthedecryptionchainandforcestheusertoinputapasswordtoaccessdata.InTDE,the

entirechainfromDPAPIdowntotheDEKmustbemaintainedsothattheservercan

automaticallyprovideaccesstofilesprotectedbyTDE.Inbothcell-levelencryptionandTDE,

encryptionanddecryptionthroughthesekeysisprovidedbytheWindowsCryptographicAPI

(CAPI).

Symmetrickeysusethesamepasswordtoencryptanddecryptdata,soitisthe

lessspaceconsuming,becauseoneasymetrickeywilluseoneprivateandone

publickey.

QUESTION10

YouareaprofessionallevelSQLSever2008DatabaseAdministratorinaninternational

corporationnamedWiikigo.

Youareexperiencedinmanagingdatabasesinanenterprise-levelorganization,optimizingand

sustainingthedatabaselifecycle.Inthecompany,yourjobistoimplementsolutionsonsecurity,

troubleshooting,deploymentandoptimization.

ASQLServer2008instanceismanagedbyyou.anewdatabaseapplicationishostedbythe

instance.

Thesecurityrequirementsshouldbedesignedfortheapplication.AuniquelogintotheSQL

Server2008serverisassignedtoeachapplicationuser.

Storedproceduresareincludedbytheapplicationdatabasetoexecutestoredproceduresinthe

MSDBdatabase.SQLAgentjobsarescheduledbythestoredproceduresintheMSDBdatabase.

Sinceyouarethetechnicalsupport,youarerequiredtoconfirmthatthestoredproceduresinthe

MSDBdatabaseshouldbeexecutedbyutilizingthesecuritycontextoftheapplicationuser.

Whichactionshouldyouperform?

A.EachusershouldbeaddedtothepublicroleintheMSDBdatabase.

B.Eachusershouldbeaddedtothedb_dtsltduserdatabaseroleintheMSDBdatabase.

C.TheMSDBdatabaseshouldbesettoutilizetheTRUSTWORTHYoption,andtheneachusershould

be

addedtotheMSDBdatabase.

D.ThenewdatabaseshouldbesettoutilizetheTRUSTWORTHYoption,andtheneachusershouldbe

added

totheMSDBdatabase.

Answer:D

Explanation/Reference:

TheTRUSTWORTHYdatabasepropertyisusedtoindicatewhethertheinstanceofSQLServer

truststhedatabaseandthecontentswithinit.Bydefault,thissettingisOFF,butcanbesettoON

byusingtheALTERDATABASEstatement.Forexample,ALTERDATABASE

AdventureWorks2008R2SETTRUSTWORTHYON;

BydefaultmsdbhastheoptionTRUSTWORTHYsettoTrue.

QUESTION11

YouareaprofessionallevelSQLSever2008DatabaseAdministratorinaninternational

corporationnamedWiikigo.

Youareexperiencedinmanagingdatabasesinanenterprise-levelorganization,optimizingand

sustainingthedatabaselifecycle.Inthecompany,yourjobistoimplementsolutionsonsecurity,

troubleshooting,deploymentandoptimization.ASQLServer2008infrastructureismanagedby

you.

Amaintenancestrategyshouldbedesignedforamission-criticaldatabase,andalargetable

namedOrdersiscontainedbythedatabase.

Indexmaintenanceoperationsarecontainedinthedesignplan.Whenyoudesignthestrategy,

thefactslistedbelowshouldbetakenintoconsideration.

First,theuserscontinuouslyaccesstotheOrderstableinthedatabase.

Secondly,acolumnofthexmldatatypeiscontainedbyOrderstable.

Thirdly,thenewrowsareregularlyaddedtotheOrderstable.

Fourthly,theaveragefragmentationfortheclusteredindexoftheOrderstableisnomorethan2

percent.

Astrategyshouldbedesignedtohavetheperformanceofthequeriesonthetableoptimized.

Whichactionwillyouperform?

A.TheclusteredindexoftheOrderstableshouldbedropped.

B.TheclusteredindexoftheOrderstableofflineshouldberebuiltonceamonth.

C.TheclusteredindexoftheOrderstableshouldbeexcludedfromscheduledreorganizingorrebuilding

operations.

D.TheclusteredindexoftheOrderstableshouldbereorganizedbyreducingthefillfactor.

Answer:C

Explanation/Reference:

Astheuserswillcontinouslyaccessthedatabaseandthereisoneclusterindex,theclusterindex

couldnotbeunavaillablebecausetheleafpagesoftheclusteredindexcontainsthetabledata.

Furthermore,theclusterindexhasneveronefragmentationofmorethan2%,thismeansit

doesn'tneedtobereordered.

Withthis,youcanbesurethatanswerA,BandDarewrong.

QUESTION12

YouareaprofessionallevelSQLSever2008DatabaseAdministratorinaninternational

corporationnamedWiikigo.

Youareexperiencedinmanagingdatabasesinanenterprise-levelorganization,optimizingand

sustainingthedatabaselifecycle.Inthecompany,yourjobistoimplementsolutionsonsecurity,

troubleshooting,deploymentandoptimization.ASQLServer2008instanceismanagedbyyou.

Thesecurityrequirementsshouldbedesignedforanewdatabaseapplication.

Acodesegmentisutilizedbytheapplication,andthecodesegmentincludesthefollowing

components:

AmethodthattheregistryisaccessedontheSQLServer,amethodthatthefilesystemis

accessedonanetworkfileserver,andaclassdefinitionthatpublicstaticfieldsareutilized.

SQLCLRintegrationisutilizedbythecodesegment,andthecodesegmentisimplementedasa

singleassembly.

Sinceyouarethetechnicalsupport,youarerequiredtomakesurethattheapplicationshouldbe

successfullydeployedtotheinstance.

Whichactionwillyouperform?

A.TheSAFEcodeaccesssecurityshouldbeutilizedfortheassembly.

B.Allpublicstaticfieldsshouldbereplacedwithpublicfields.

C.Allpublicstaticfieldsshouldbeutilized