Training Kit (Exam 70-462): Administering Microsoft SQL Server 2012 Databases

624 Pages·2012·23.93 MB·English

by  Orin Thomas

spine = 1.47”
EXAM 70-462 Administering Microsoft®
SQL Server® 2012 Databases M70-462
A
Microsoft® Certified Solutions Associate X
Your 2-in-1 Self-Paced Training Kit The new MCSA certifications validate the core E
technical skills required to build a sustainable
career in IT. MCSA opens the door to multiple TSA
career paths and is a requirement for MCSE
1EXAM PREP GUIDE certifications. rQd
Exam 70-642 is one of three required exams for aLm
Fully updated for Windows Server 2008 R2! Ace your preparation for
MCSA: Windows Server 2008 certification. For a
the skills measured by Exam 70-642—and on the job. Work at your limited time, it is also valid for MCTS certification, iSi Administering
own pace through a series of lessons and reviews that fully cover each which will be retired. nen
exam objective. Then, reinforce what you’ve learned by applying your Fviosrit c: ommicprloetseo ifntf.ocormma/tlieoanr onnin Mg/iccreorstoififtc Caetirotinfications, inrvist
knowledge to real-world case scenarios and practice exercises. This ee
Microsoft
official Microsoft study guide is designed to help make the most of gr® ri ®
your study time. Your kit includes: 2n
· g
15% exam discount from Microsoft. K0

Maximize your performance on the exam by learning to: Offer expires 12/31/15. Details inside. 1M SQL Server 2012
• Configure IPv4 and IPv6 addressing · Official self-paced study guide. it2 ®
• Deploy and configure DHCP servers, DNS servers, and DNS zones · Practice tests with multiple, Dic
customizable testing options and a r
• Implement IPsec, Windows® Firewall, and Network Access learning plan based on your results. ao Databases
Protection (NAP) · 200+ practice and review questions. ts
• Plan and manage Windows Server Update Services · ao
Case scenarios, exercises, and best b
f
• Manage file and print services in Windows Server 2008 R2 practices. at
· ®
• Enable remote and wireless access, including DirectAccess Fully searchable eBook of this guide. s
e
• Monitor and troubleshoot network performance
For system requirements, see the Introduction. s
2
PRACTICE TESTS
About the Authors
Assess your skills with practice tests on CD. You can work through
Tony Northrup, MCITP, MCPD, MCSE, CISSP,
hundreds of questions using multiple testing modes to meet your
has written 20+ books covering Windows
specific learning needs. You get detailed explanations for right system administration and develop m ent,
and wrong answers —including a customized learning path that including several Microsoft Press® Training Kits EXAM
describes how and where to focus your studies. and Windows Server 2008 Networking and 70-462
Network Access Protection (NAP).
Orin Thomas
J.C. Mackin, MCITP, MCTS, MCSE, MCT, is a
writer, editor, and trainer who’s worked with Peter Ward
Windows networking technologies for 10+ years.
boB Taylor
He has written several Training Kits, including for
microsoft.com/mspress Exams 70-622, 70-643, and 70-685. PPrraaccttiiccee tteessttss oonn CCDD
Training Kit
ISBN: 978-0-7356-6607-8
U.S.A. $69.99
Canada $80.99
[Recommended]
Certification/
Windows Server
www.it-ebooks.info
Cyan Magenta Yellow Black
Contents
Introduction xvii
System Requirements xvii
Lesson 1: Configuring AddHiatirodnwaal rSeQ RLe Sqeurivreerm Ceonmtsp onents xviii 106
Deploying and ConfigurinSgo fAtnwaalryes iRs eSqeurviriecemse n1t0s6 xviii
Deploying and ConfigurinPgr aRcetpicoer Stientgu pS eInrvsitcreusc tions 10x8ix
LUBBBBUUBPVLLLRPPRRRCLLCCCCSMCRALLLCCCCCCQCQCQCQACCCCACeeeeeeeeeueeeeeeeeaaaaaHhoooaaaaaaaaaaaaaaainnbnssuuuueasssssssssgrrriicccccssssssssssssssssssssenndssodAnneeeenwsssssssssfffttttteeeeeeeeeeeeeeekkkkkwwgcfitoooeoooooooooueooooossssggPa iiiiiriketttt nnnnnSSSSSSSSSSSSSSSgeerrrrnnnnnnnnnntrrrrri gTCiiiiibmmm DBssiiiiettttttttcccccccoooorrgggggun gtnnnnEeuuuuuuuutta2SR3SR123sseeeeeee uahannnn rgae DR iiigggg suuddddddddUUUUU: :eeBnnnnnnnnnnecStet ssssnd e oRCammkayyyyyyyyvv DQaaaaaaaa 1gggppppp DaSRda At reuiiboSrrrrrrrcPy2 ea55555555512341234eeLnamm aiDBFPiiiiiiiSRMAMsukptansnr ooooooot11p22223 ww 44btduytiaaeatSultaas laafiga o99s410945ilew0eDsaeh d gce123123cepribpt rrbM g tcrdm srakto B: ri::ayyeele aRi CoaaeuieiiHvnbuRFS ycr aacvmas etsssRrhresDagQepsaaeeiicei ee is cnserSOenb skst csdatsd DLteSSte seec oAguor otn a stQtkidt aDSaDrp vgoka rP isapeteRn bLye aa t4445555555555555555555555reaDranTsoru eearotm111119955222222339996000tn4Sab vydayaipsasst764585383390775222263450e 4ntepat ebeBb laBMartrsiescaacav steba Ast ascaessAce aekegsir kdnsd suseDeu t apnwaspat tiu iStn St5444444555555aamthit1111999990008rbntr a7 121223317948 TLLMMMLLCCCSCCALLLLLCCCIBLUUDUCTRLLLDUUDUDULLCCCSIIALLCCCSBLEMMMLLaagCnmdtrreeeeeeeeeeeeeeeeeeeeuuQtxeeesHHrraaaaaaaaaaae nnnnnnsssieeeoaadeeaaooooaaeeeessssssssssssssssssssggiiivffppssssssssssgssfitsLddddnAAnnnncgnnn ssssssssssssssssssssooegnnnnaasceeeeeeeeeeeiiwwggBl tynkooooooooooooooooooooeeeeetg sgggxtPPeaayuSttrriiii nuc o eeapttttiiSSSSSSSSSSeieieweerrrrnnnnnnnnnnnnnnnnnnnne n ggmeTTtfntnnorooooAARsssscssscccccccccc rraiyis eriEEiiiagnYYttttggnttosR5SR123451SR2SR121SRrrrrkssneeeeeeeeeenne nec rlv aaaaeeRRiiiiai oowa guuuuu: eg::: aeeeeennnnnnnnnnnnnnpng55555tnTggeg M aTnnnnddn niIuuIIM nmmmm pvvvvv ra1111aaaaaaaaaa11gggg0rg ot v rdmndPJadddd dIaAJdU iiiiitai y77rrrrrrrrrr66PPo01in4 rA4444444a4BBeeeee4eedonesammmmS iiiitc DMCMiiiiiiiiiiB npsltnnnnnDrroooooooooobCsd11111n1Rwwwwwy666rntneeen gdbesOaaoPisMilrafaaaggggu 0 11s3s2s011edo2aue enrggt tsea es occaMa12121212Uftb rrrrCinit sktx ms lngmgobiifnftttottISSc ::::yyyyrstMennay o enyoliiyehomc i irtytQtURDAUIF eccd d o- nIeisdi liadg,rueEU naneeannn voePSin sseUr LrnagBtef nefyrbIiissedeaegapgseenyAi apoynntnrt ns a sxSci rggrcoS rec iel slinilCtnrtdc UniciBrateynvfnot kQT xkciaeerniegyoakaceoiEnegzro fgsgyesyaani 444444444444444444444444c pLnuvs tyfnrxiarn d Btpi ssS mn11177o5555gy6666880000000 fi44ma enieItpdSt g cS EnaQne 4744Iig127885l257t333m0 9886n4c00RrsQgo , nueeDenoasg sdTYi dLeLa een ndrC rnyvL r Aeee arso InvPnla eSi eocMmaaeixonroSuecdnontecexonerdnel dfdkrpka eer-so vR sn clcRrA av45iaQAis osir MrSeutvnymbne e o 1cn 8icgtBteau Qrntygsrt1vrP7arak44444444reanrea tie gui eeunPol rs1U2o3335en00iCnldyAronc naera7n7783v035rytCssto togibncesrgS a i inuo nnsS Pyfiey lctlegr e nca cge enlemPaem outcrd tIsetiPrnfu prse LCCCSIIALLCCCCBLFCIMPTLLLWMCSCDULLCCCSIIALLCCCTBLGCCLLLCURCLLLMWLLLCAL s orrTmmnmmeTtdrerr neeeeueeeeeeeeeeeeeeeeeeuueefeeeeHHlrrooaaaaaaaaaaaaaannninsreearrroosuouoeoche eessssssssssssssssssssssltlmggrivffneeppppppppssssssssssfnntasspitdefiAAn sssssssssssssssssssssssuuooeslfrxnlraaoa4444444444444eeeeeeeeeeitawwneggctllllttttlsfifi oooooooocooooooooooooooolmteelkgaeitPPeeeebbyttrrt2255i i6666800uuuuo44rllp yenceeitt-SSSSSSSSSSggii ieewlireernnnnnnnnnnnnnnnnnnnnnnn(ms mmmmzTTn30ll01nnn5422n66iA099rrrrio yaSyrimBAsgsseecccccccccc n rrnuuP iniiiicEEii gYYitggg ttgunnR121SR2SR121SR2SR3SR4Srnnnnssreeeeeeeeeean- nsseeeevngegrrraee RRiiSe ooT uuuuuu hh: :b:: ::cgeeeeeeTnnnnnnnnnnnggggnniioannnngg A g E aDnw ddnnt rMUeuuAWCWoammmmmmFvvvvvvoon iaaaaaaaaaac 8A9ggbatttt de vdlC ggPAAaaa Ti Wa iiiiiiAyelarrrrrrrrrrlPPooesr t lS3e3333DRaAl3BBeeeeeeaiuommmmmmwoonietr onAiiiiiMiiiiieiccwitsth bntrrlttARooooooooool)Q n22a2i666wwwwawweMeeseilniw rrrda ottmaaSdllii ac owniaaaaaagaFl ennatss45kck477fiiv8gapggAldti LQ ecc12121212Cvytvv eaoi rrrrrraontdye4444444 MbaiiaasggnAat ilii ttceScnn s o::ii::yyyyyyyrovtinniSsr w11 L 55y n6d0ibott4iiiyu cOltlihOtQPDADFrl mcclgOgsriaFSyyo el n w8899ai908iiseaw aatilaSiGaOeeevrttaM nQ lnteaarnOuyvLbgndbtswwaweowwalyeihssi svir iat tc e lnegttD il Ln yrlFo rTnoArooaae lSoiiaiipAyGPetgSr e P i vttttasrca onMvAbb rtduQSvSy v hhehhueviaqOeCreMie Syten arQseaapelevF3333333333333333333333343orra tcrLauroi leOSESrPnig vfrassarrM usMS277722222299s6666666888ie044ofu l L ioCcvl QrvQxee Saeeo C Cii se3a81sn 6613337259977C79166 4444pnoolloPeert lSoebiorraotr44otlbLL uRMtelmi r nrefuenrmAesrubnitdeP 2v 04l oii senl SvrSo rlnsldr2isvseiiiare0Ft2e9iifratprdttelveet rteenga0aorinamsyoerc0n eyetrlrre rr ro1ig ii rtc rCy8rv vlrcercleGyd2aom aG2 or e see eIlaDqd Gni0 Rbunr r RvnrrEr taD ou 1caM33333333333333o s2 isreiTIevgnPlPoneouittt2a77222339888i444 ourpes etaFarcron sp ut62a891378759i940poyMnoCol are ntatn itps ealsactfifiil cG iluoD?ssCsatntolle ereosnsPoacTsvotr rri etarhee ta uao iorrotlp fis r CICsp VnlCCSMMALLCCCSBLUAMPUDLLLTTTULLLUCUCPLLCCCCSMTAALLLCCCCMBLDMCCCRMULLLRRSTPMR 333333333333333e loirrrrreeeeeeeeeeeeeeeeeeaunuuseioeroeeeeeHHhooooaaaaaaaaaaaunnsnpnssss55777723999a6e444rnaooooiiaaaaoetontsssssssssssssssssscsagg siiiiepppfflrrsssssssssssla4nmnntd239982sst55 7946da66gAA nnnnn einnnnessssssssssssssssssuuuurootinutrrTteeeeeeeeeeeapeFrcwwggnlllgfififingieooooooooooooooooooeeooryoggggs iii-PPaaaaobbbbrrti mrabrysScccaeegtnSSSSSSSSSSSagggrceeatceere nnnnnnnnnnnnnnnnnnirrms ggggTTyhbllll-oSaaaanw odSSScsssiieeeeeccccccccccccitori rr uuuo BsnnEEieeennoYYQRtrtttttsi2121SR2SR3SR1231SR2rdgQcesseeeeeeeeeee-tniisssssi nrrrniiiaieennRRe iagg tooigghPt uuuuehh:h h ::::oookceeee nnnnnnnnnnn niiiLUDgLo cLAgn dd nnnS si g AuuTD uMMpe aemmmmRvvvvooooConnnM naaaaaaaaaaa67gh aPnOePsaQ d Sguggr SPm emriiiilOurrrrrrrrrrrePP oooog raegn Mir222d222tBBeeeeao2eiiammmmeiATMi3333333d eiMiiiiiiiiiiinpeircLrepatndtrrrttttrLMMooiooooooooo tdl277722wwwwr 7b yeenu ar7223rn668rEy aai aar riiigsreiMerTbmoaaaaglitvo 4SnnnRsS655o546r pivggmscjaob i22 te2255f 0btscc1212123123rrii Rr re racrrrraCe g eeihrrefngggeiMar geariiaiarqttliSo:n ::::yyyyaoeeaacrrnnnaisier s rociiiriapnii tsnsnniAeCTICoo uncc nsregttttnsnCEpAcv ondusin tIsrdlACeeoeirssneaegic rrn iooethgun ia deoileogudr ossP AacSiidi ci ougssrougD frislnnndno dcvdRFit rnt Dn aecet uQotr tu SrnteaDdm thaea2atfiggiyop uDeoe t tMarasbigteaDdnamsLt iiii fietm0 agp rstiombntlPoopa itlro wwnai icioe32222222222222222222223332 abn oo1cetVn3tuSalnvielctennlbieniagn 11 44ie6833377777777722229900taviiist2yandsecb6eeerniMngratt aa-bi ahe715 est009993331t88638s239077788i b amhh rcred6OsaagntnL Ulseb iswar ovS to aoa s st Ree R cgsabiWCisseDQ otesdossn ve Foiee siajree RsnstDaoienes aLe i lpiinfi rsnIPe ogtno lncb sant l Sacdsde li tPign trsecDaefib s raiDCMCCLLLDCUUEPMULLCCCSFTALLCCCMBLUMCMLLLCUUIIUPSLLCCCSIIALLCCCSBLSSUCLLLDDoet sKak stmmmmrmcaSaarbeeeeeeeeeeeeeeeeeeeIuEuQQne areewrHHriooooooaaaaaaaaaa nn3222222222222222222222233npssssssvaaeeteaLaaoiiiaaesemtneLsssssssssssssssssssriraggciiiiieffppppssssssssssgggei4445nnnnpp66688333tt7772229999000sspEsymLLdgeosAAtannnnnnnnssssssssssssssssssssvcoopntEdrceeeeeeeeeeaasriwwgger1 5l ooo1rrr4s2092123363S87090fifififi4440yy00iiooooooooooooooooooolssbergungggggesyAPPeaa-CeSL erra t sobb aaae aieeTiiSprrrSSSSSSSSSSggggeeeerDinnnnnnnnnnnnnnnnnnnaprA rsnoo a ggmennsTTrtttunToyaatttdDDtBFsP ssReicccccccccci rraiuuuu siii ttr iRiigntEEiiuheggo YYintttttssnnnSR2SR121SR2SR121SR2nossneeeeenneeeeeaenn iCeciEenIrvyaarrrrahieenRReeFnofis itooe Nnuuuuug :gg::g::eaieeeeeunnnnnnnnnnnrriiii gggnnggAe PDattg h nnddannnn eR g ngmlmuuMEMMMslmmmmmn aavvvvv ,URrdaaaaaaaaaa45rT Stens s M DebdaSgn a ggggoaDD iFLte x iiiiiabI trrrrrrrrrrPPf OoQ staDdL2a1s1111ai1BBeeeeeCon Qm aaiammmmmtrit nouE poiiiiiiiiiialdsny Dgaaa nerrIDnFSFaoooooooooooaliei t4405888wwwww neennt oaLs2222223anxketdclgaaMr o LE dttsIprrIiiaaaaaQgnbls a ss9g9i09S88a4Fa ggmnaaapt4l533ca77L0aaam-s dI ccea12121212s o xrS C errrrro t danLaM TeiFt iiagg EgCiitL dm02260t4tt9o tCp ::::lefyyyyynaenTg nnabbopo beiCspireiiir sio tSiii ooDTCC lnrccxaSnbrooSssa etrv nnntiFyyx neogsaorTEtraoveeei a,tabnngooegneanr see itn ggg stsriaRnneilUUssDRstnBt n n filro srnn lpaeesttgt Ii e niastrEcs ve o aaULnuocsnoasssgTagsfissRlo apo,srndAeio ii oIein ooalnldntg uos gnniyntnom awekilOdrana ySMgbdnoe11111111111111111111111122222’eld e rpnt ggun esdlsiSDr iigQIOeiorp777444441d 99995558888880000i 222ltx ,Int222222 tndndr dn CTetaC AsThm sPPPPPPPUHHHAEWSPCPBLEDPSDRIIPBLLLCIIIEITLLCCCSCAALLCCCCBLIDDCLLLIDCUCLLCCChBO073311ai1i133E4 156677820309040o644iaar4L88372pg Fotnnnnnnddnigo S ans ooeeeeeeeeeeeeeeeetahucrvnazterrrrrrrrll SDeyxeeeUEHHteooooonaaaaaaaac dnr1ssaieeeooo352 38iPp aaeQTyassssssbgr eenaeSeeeeeeeegiscmarlsnatissssssssssssssnpctrQgTariikmffnxSgssssssssnnnnndotaspssdenttttttaLwwwrnnAAEnns reyotnn tsssssssssssssso eioo lppppppppf lnlreeeeeeeefiabLpdSnaaaaaaiihwoagancWeaar rtuiKFfififififie tgonooooooooooooooi lNLgmgggnnaggrrttePPBn urraaaaaaaaU niboadiaellllnlghannl ootansrkIettt iiSabSSSSSSSS gggggtnv erlllllreennnnnnnnnnnnnn niDiinny mLTTSrrrrrrrriffSunRg tIa aoooiiiiitrgby naDicnnamurosmstWdiwsrdoeetcccccccc&nnnnnN r eeeeeeeiuuuuuuyynSi sEnviiQeEEehntltepaO YYineTti tson D1SR2SR121SR2SRagsggnnreeeeeeeeu rbr akpnn iinCegggggIUU rrrrrtalagiSee aRRgeriEPSTo itoornnetaAaaaaaS -uuuunCgaves n::::ndeeeeLnnnnnnnn Benieiii gg ptnWae2srag vgn n aTors111111112dr annnn aLEaSxDns eo RueggrauuIMPC tdtSSSAmmmms vvvv Dgo DdvCsaaaaaaaao12sg at kCCMSTFCgesrr0nbSeR7 4 11tdf88330epnSeboAnRhcn ggggstttiirbM QQQac l iiiiSSiaIee ogorrrrrrrrP ndsn oinehon ro 1 ehha 555aoooaBB eeee5naW6s 1a8e7nv778a3nEa mmmmd nonTlTed haiiiiiiiid DD toE ate2rnRnmHerrcttdaaDRoooooooodusLLLittnnr2l k76e6wwww2leersm mmeengAhrsta erg r a loSTdsaem 0fwatto vRS Se aaaaafiDnniC vaeI etrtss il fin0dItegg aa Pete nSSScDa MenttcS121212 G( t l IS s1nhiea Q t CpppmirrrrtnDn o gdslaSwUoiBn1h tgaaiitib g iiWctaeeeslRtohunOdi at::::tsyyyynolr2staMennPoirlaauu dn siaeliu rnl2oreaLhUseuo rtrrr ipeeaSAPIvo aSoeb potcoMaa t agn t nrEebunppntMtatt tg vvvir t ssdE rfrrarQgnieeb ehslaLMvztlSnawpaIeedwPitcMr i aat rg arvilaaeee easeae tn ixan bMYisSKrlomaarcirSeelsyoltcSrrdeo ro dlnitLEeignsnrrrti ri cssla ovane siEoo engetarylnuen aeSrUna)Q imam 1111111111111111eoI n -ntRrri giPnotegibemdo2I2t SmnrFnv a stctcFnug Qo nrlctgLsgt tn 777 6499555a888222iDp ncu SG2t siL rriyC d00eiMee iv nuog bitucisReLireof cnx59xxx12245555567799 1Fio85 9ln 133t r5 a8866n4444rYbL Qnidn 2FSp eotD11earFrrrDe oro neisSCeecevyeCxxxxen945188233I8g86604cnoc aa gTnavyueQig2Tnaert n22 rlSeDSLeeg vrp gqawtvvv aoteSoaeI ne na eiu pCa grnt a i y sneD aieel rlNtLSrttsuSn c la efboSs cbTnr dacb tt to avcSaDiurnr r rsSy ateSeIteiioegaseuelyreSsa nDvloetvbternotnsrlesad SledQarp tlsir s niaerewsea eeerotrTmloasUnRfiotbvrcet t v teaie lelrm vrn JhLsrateans aruasoeln gpuenaOssv t oDa a me iedsneIfabnara itri11111111112y Srsln uelenntooinrta uehoSis a ndtennmd7a644411e830i2 a tnrstSrIPs coonsrgDseatteyn ysda xxxxx7913678896as1d7 4r55n288 90te t e2 EaaIR enrr n mRe vaBaastxxxxxris3922660 t daCr anstb0vsJiAndt ee d iiinnevvavco ktEo iWnvvaieo1gaaCltriiJic odpehcds nanzn Jin d2osmineineooIr don oiatc egnct tM nGidses nfSCns itrhemiseIi s n ndi dtnete fiS aeootEsi Ig oan raneQ tgnt IrrnS Dvotn wn reeguaDt e ILoi dtonr occ gsrt rIox12281112235678961aarSe ha en smve PSxt7t 5651732t88044etLst-eitshaemevraci haLort oob eernDvaeiaegennv maselc vD onl e F sDeraCe Fpmteor etsly2o i t sial m2cSoae0 m t0tlei 1nuaionn0ac2 ir xx233689Oniu8geS nxx113694 rQs p Rii tDtL2yi o i SsDneco oramvvneeadrr i xx159yn2Jxx584 o0 RiiCii1eno2p nI otot rrtnoot al lt ehC5reo 5 mDopmutaeirn R uxxixnxiniing a Server Core Operating System 55 E S EswuxebaebxOIPIICMMMCIMOICAMCRIIIMMMTIIIImnmmmmmmmdjNMM rleesoooouBPaoseaaaaaaAAisSpppppppc nnlndartPPtnTnnnnnnnJulOtatINTeellllllofififiioE LLteeeeeeaaaaaaINb tliA cvbtMirgggl nEECrAmggggggmmmmmmffl SottTeeoSjLuuuyMMs TeeeeeeGQeI Aa aseeeeeetQ LrerrrcdZ Iacahnn eeeESaSdulnnnnnnEEL VIhotLanAEltoN QQn ddsaNNttttttlh iaSaESDatg adr eSvNodt AaaaaAdre LQLndeneTTatia rAeIe7et nb n ira rDdNN ldsxnc gmSb oSSLrmpeswTvSHss adtpo viQa ee a 2anes DSealAmetSEsCia0 aeionlIorronisgm .bccTerLyeoCGy navvOrefira TldsTu(ar Aszar isvnee0tUSt da2aHating.htv rNeROp s-n e Nrradeiitd ni0aseReuc neo tseO itltrFnAiatAoacdn ruy C1aern tIr4nrvaPmSI t.eoeUtT.agon VmiyrriGEsea.nQrnaEn xvnirY tueiBeArdSbnc.s trUeRi2saae6 Lcnabs lir tea LcatI(annrmmA uCisorR taLllSt1sn Esbtaieuc.trraErnNo.EeA ee S82d erotaesisbolN cnneerdHh iB eDctDsrl(vPsweaebngegso 1.koxTItsOe :ss.sErcLjy .e9lDiieo)e.eru.eent R.yIOs rhtsAsTdcapPc .gvACi.p.Tnoot TYEi isEirc ognmAuvR tt(oNe(r 1fsetCB1ddbspa t aTs4e.p2aEAtalo e ex)ln tNrPnSiPmagmaibscoEeETmE.tyesRneSr.)R.. t dCnaCo(s 1Eoi.bEh7NtnNsjei ecPTrTceeeE)i)t RiaasvsCrneeEtds N:c eahuTttr )trMrpe:nii/c/tnr woawssg oowfft. m’ tsh Misicosr lobeso oidofcitks.c’csrro epmotuio/blnesli.ac Poranlteiionafngset /de vC11431328219611145656877 ani00111Hts/SeiutA.s QtEP/hexTexaE amMLRm iosc /bro7je0sco-L1211131122131122212tf4i2312E–tv6 S6Le2Ses.O maarNrsnepi nx.g
Controlling Replication of ConstraCionntst,a Cinoeludm Unses,r sa nd Trig2g1eS6rusg gested 3P1r7actices 99
Heterogeneous Data 318 Least Privilege 21C8onfigure Instances 99
Lesson Summary 320 Application Roles 21I8nstall and Manage Multiple Instances 99 www.it-ebooks.info
Lesson Summary 22A1nswers 100
Lesson Review 22L1esson 1 100
Case Scenarios 22L2esson 2 101
Case Scenario 1 103
Case Scenario 2 104
CHAPTER 3
Configuring SQL Server 2012 Components 105
Before You Begin 105
www.it-ebooks.info
PUBLISHED BY
Microsoft Press
A Division of Microsoft Corporation
One Microsoft Way
Redmond, Washington 98052-6399
Copyright © 2012 by Orin Thomas (Content); Orin Thomas (Sample Code); Peter Ward (Sample Code); Peter
Ward (Content)
All rights reserved. No part of the contents of this book may be reproduced or transmitted in any form or by any
means without the written permission of the publisher.
Library of Congress Control Number: 2012938612
ISBN: 978-0-7356-6607-8
Printed and bound in the United States of America.
First Printing
Microsoft Press books are available through booksellers and distributors worldwide. If you need support related
to this book, email Microsoft Press Book Support at [email protected]. Please tell us what you think of
this book at http://www.microsoft.com/learning/booksurvey.
Microsoft and the trademarks listed at http://www.microsoft.com/about/legal/en/us/IntellectualProperty
/Trademarks/EN-US.aspx are trademarks of the Microsoft group of companies. All other marks are property of
their respective owners.
The example companies, organizations, products, domain names, email addresses, logos, people, places, and
events depicted herein are fictitious. No association with any real company, organization, product, domain name,
email address, logo, person, place, or event is intended or should be inferred.
This book expresses the authors’ views and opinions. The information contained in this book is provided without
any express, statutory, or implied warranties. Neither the authors, Microsoft Corporation, nor its resellers, or
distributors will be held liable for any damages caused or alleged to be caused either directly or indirectly by
this book.
Acquisitions Editor: Anne Hamilton
Developmental Editor: Karen Szall
Project Editor: Karen Szall
Editorial Production: nSight, Inc.
Technical Reviewer: boB Taylor; Technical Review services provided by Content Master, a member of CM
Group, Ltd.
Copyeditor: Kerin Forsyth
Indexer: Lucie Haskins
Cover: Twist Creative • Seattle
www.it-ebooks.info
Contents
Introduction xvii
CHAPTER 1 Planning and Installing SQL Server 2012 1
CHAPTER 2 Configuring and Managing SQL Server Instances 61
CHAPTER 3 Configuring SQL Server 2012 Components 105
CHAPTER 4 Migrating, Importing, and Exporting 153
CHAPTER 5 SQL Server Logins, Roles, and Users 193
CHAPTER 6 Securing SQL Server 2012 229
CHAPTER 7 Mirroring and Replication 279
CHAPTER 8 Clustering and AlwaysOn 327
CHAPTER 9 Troubleshooting SQL Server 2012 371
CHAPTER 10 Indexes and Concurrency 417
CHAPTER 11 SQL Server Agent, Backup, and Restore 465
CHAPTER 12 Code Case Studies 523
Index 567
www.it-ebooks.info
www.it-ebooks.info
Contents
Introduction xvii
System Requirements xvii
Practice Setup Instructions xix
Using the Companion CD xxiii
Acknowledgments xxv
Errata & Book Support xxv
We Want to Hear from You xxv
Stay in Touch xxv
Preparing for the Exam xxvii
Chapter 1 Planning and Installing SQL Server 2012 1
Lesson 1: Planning Your Installation .................................2
Evaluating Installation Requirements 2
Designing the Installation 7
Planning Scale Up versus Scale Out Basics 8
Shrinking and Growing Databases 9
Designing the Storage for New Databases 13
Remembering Capacity Constraints 15
Identifying a Standby Database for Reporting 15
Identifying Windows-Level Security and Service-Level Security 15
Performing a Core Mode Installation 17
Benchmarking a Server 19
Lesson Summary 23
Lesson Review 24
Lesson 2: Installing SQL Server and Related Services .................26
What do you think of this book? We want to hear from you!
Microsoft is interested in hearing your feedback so we can continually improve our
books and learning resources for you. To participate in a brief online survey, please visit:
microsoft.com/learning/booksurvey
vii
www.it-ebooks.info
Configuring an Operating System Disk 26
Installing the SQL Server Database Engine 27
Installing SQL Server 2012 from the Command Prompt 33
Installing SQL Server Integration Services 34
Enabling and Disabling Features 36
Installing SQL Server 2012 by Using a Configuration File 39
Testing Connectivity 40
Lesson Summary 52
Lesson Review 53
Case Scenarios ...................................................54
Suggested Practices ..............................................55
Answers .........................................................56
Chapter 2 Configuring and Managing SQL Server Instances 61
Lesson 1: Configuring SQL Server Instances .........................62
Instance-Level Settings 62
Database Configuration and Standardization 68
Distributed Transaction Coordinator 71
Configuring Database Mail 72
Lesson Summary 78
Lesson Review 78
Lesson 2: Managing SQL Server Instances ...........................80
Installing Additional Instances 80
Deploying Software Updates and Patch Management 84
Configuring Resource Governor 86
Using WSRM with Multiple Database Engine Instances 91
Cycle SQL Server Error Logs 93
Lesson Summary 96
Lesson Review 96
Case Scenarios ...................................................98
Suggested Practices ..............................................99
Answers ........................................................100
viii Contents
www.it-ebooks.info
Chapter 3 Configuring SQL Server 2012 Components 105
Lesson 1: Configuring Additional SQL Server Components ...........106
Deploying and Configuring Analysis Services 106
Deploying and Configuring Reporting Services 108
Deploying and Configuring SharePoint Integration 112
Configuring SQL Server Integration Services Security 114
Managing Full-Text Indexing 116
Configuring FILESTREAM 118
Configuring FileTables 120
Lesson Summary 123
Lesson Review 124
Lesson 2: Managing and Configuring Databases ....................125
Designing and Managing Filegroups 125
Configuring and Standardizing Databases 128
Understanding Contained Databases 128
Using Data Compression 131
Encrypting Databases with Transparent Data Encryption 135
Partitioning Indexes and Tables 137
Managing Log Files 140
Using Database Console Commands 141
Lesson Summary 146
Lesson Review 146
Case Scenarios ..................................................147
Suggested Practices .............................................148
Answers ........................................................149
Chapter 4 Migrating, Importing, and Exporting 153
Lesson 1: Migrating to SQL Server 2012 ...........................154
Upgrading an Instance to SQL Server 2012 154
Migrating a Database to a SQL Server 2012 Instance 161
Copying Databases to Other Servers 164
Migrating SQL Logins 170
Contents ix
www.it-ebooks.info
Lesson Summary 173
Lesson Review 173
Lesson 2: Exporting and Importing Data ...........................175
Copying and Exporting Data 175
Using the SQL Server Import and Export Wizard 176
Using BCP to Import and Export Data 178
Importing Data by Using BULK INSERT 179
Importing Data by Using OPENROWSET(BULK) 180
Using Format Files 180
Preparing Data for Bulk Operations 181
SELECT INTO 182
Lesson Summary 184
Lesson Review 184
Case Scenarios ..................................................185
Suggested Practices .............................................187
Answers ........................................................188
Chapter 5 SQL Server Logins, Roles, and Users 193
Lesson 1: Managing Logins and Server Roles .......................194
SQL Logins 194
Server Roles 201
User-Defined Server Roles 203
Credentials 204
Lesson Summary 206
Lesson Review 207
Lesson 2: Managing Users and Database Roles .....................209
Database Users 209
Database Roles 211
Contained Users 216
Least Privilege 218
Application Roles 218
Lesson Summary 221
Lesson Review 221
x Contents
www.it-ebooks.info