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