Benutzeranleitung / Produktwartung SQL Server 2008 R2 des Produzenten Microsoft
Zur Seite of 236
Contents Contents at a Glance v Cont ents vii Acknowledgments xv I n t r o d u ct i o n x v i i Who I s This Bo ok Fo r? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . x v ii How Is Th is Bo ok Or ganize d? . . .
PUBL IS HED B Y Microso f t Press A Di v isio n o f Microso f t Corporation One Micros oft W ay Redmond, W ashing ton 9 805 2- 63 99 Copyright © 20 1 0 b y Microso f t Corporat ion All r ights reser ved.
I dedic ate this book to m y wife and child ren, who mak e it all worthwhile. — R oss M istRy I dedic ate this book to m y husband an d best friend, Gerr y .
.
Contents at a Glance Introduc tion x vii P ART I DA T ABAS E ADMI NI STR A TION CHA P TER 1 SQL Se r ver 20 08 R 2 Editio ns an d Enh an cem ent s 3 CHA P TER 2 Multi-Se r ve r Adminis tra tion 21 CHA.
.
Wha t do you t hink of th is bo ok? We want to h ea r fro m you! Micr os of t is int er es te d in hea rin g your fe edb ac k so we ca n cont inua lly im pro ve our boo k s and le ar nin g res our ce s for yo u. T o p ar ti cip ate in a bri ef onli ne sur vey, plea se vi sit : micr os of t .
viii Con ten ts Creatin g a UCP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 Creatin g a UCP by Using SSM S 26 Creatin g a UCP by Using W indow s Power She ll 28 UCP Post-Inst allati on Steps 29 Enrollin g SQL Se r ve r Ins ta nces .
Con ten ts ix CHAPTER 4 High Availability a nd Vir t ualiz ation E nhan cem ents 63 Enhance ment s to High Availab ilit y wi th Win dows S er ver 20 0 8 R2 . . . . . . 63 Failover Cluste rin g with W indow s Se r ve r 20 08 R 2 . . . . . . . . . . . .
x Conte nt s P ART II BUS INE SS INTELLIGE NC E DEVE LOPMENT CHAPTER 6 Scal able Dat a Warehousin g 109 Parallel D ata Wareh ous e Ar chitec tur e . . .
Con ten ts xi Integra tion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135 Impor ting Ma ste r Dat a 135 E xpo r ting M as ter Dat a 136 Administ ration . . . . . . . . . . . . . . . . . . .
xii Co nte nt s CHAPTER 9 Repor ti ng Se r vic es Enh ance ment s 1 65 New Dat a So urce s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165 E xpre ss ion L ang uage Imp rovem ent s. . . . . . . . . . . .
Con ten ts xi ii CHAPTER 10 Self- Se r v ice Anal ysis with PowerPivot 189 PowerPivo t for E xcel . . . . . . . . . . . . . . . . . . . . . . . . . . .
.
x v Acknowledgments I would like to r st a cknow le dge Shir mat tie S ee narin e for a ssis ting m e on this title. I co uldn't have wr it te n this bo ok wi thou t your as sis tan ce in such a sho r t timef rame w ith eve r y thing el se goin g on in my life .
x v i And la st b ut not l eas t , I would like to thank my Micr osof t mento r s who a ssis te d with my ca re er de velop ment an d tran sition to t he Micro sof t T echno log y Cente r in Silicon Valley: Kell y Olive r , Alex V ier a, B uck Woody, Kevin T sai , and B ur zin Patel.
x vi i I n t ro d u ct i o n O ur purp ose in I ntro ducing Microso f t SQL S er ver 2 0 0 8 R 2 is to po int out bo th the ne w and the im prove d in the la tes t ver sion of S QL Se r ve r .
x vi ii Intr od uc tio n are impr oved w ith the int rod uc tion of the S QL Se r ve r Utilit y Control Point . Step - by-ste p ins truc ti ons sh ow DBA s how to q uickly d esign ate a SQL S er ver ins ta nce as a Utilit y Co ntrol Point an d enr oll ins ta nces f or centr alize d multi-s er ver mana ge - ment .
Intr od uc tio n xix New ” topi c in SQL Se r ve r Bo ok s On line at ht t p:/ /msdn. microsof t.com/en-us /l ibrar y/bb50 0 435(SQL .105).aspx for the mos t up -to-d ate lis t of change s to the pro duc t .
.
P A R T I D a t a b a s e A d m i n i s t r a t i o n R oss M istRy CHA P TER 1 SQL S er ver 20 0 8 R2 Editi ons an d Enhance ment s 3 CHA P TER 2 Multi-Se r ve r Adminis tratio n 21 CHA P TER 3 Dat a.
.
3 C H A P T E R 1 S Q L Se r v e r 2 00 8 R2 E d i t i o n s a n d E n h a n c e me n t s M icroso f t SQL Server 2008 R2 is the most advanced, trusted, a nd scalab le d ata platform released to date.
4 CHAP TER 1 SQL S er ver 2 00 8 R 2 Edi tion s an d En ha nc em ent s Micros of t ha s mad e major inve stm ent s in the SQL S er ver pr oduc t a s a who le; howeve r , the ne w feat ures a nd br eak th rou gh cap abilitie s that sh ould inte res t DBA s th e mos t are t he adva ncem ent s in appli catio n and mul ti-se r ve r adminis tratio n.
SQL S er ver 2 00 8 R 2 Enh an ce me nt s fo r DBA s CHAP TE R 1 5 the total cost of ownership of their database envi ronment. The new SQL Ser ver Util ity dashboards also assist with consol idati on effor ts.
6 CHAP TER 1 SQL S er ver 2 00 8 R 2 Edi tion s an d En ha nc em ent s FIG UR E 1- 2 Ide nti f y ing co ns oli dati on op po r tu niti es w ith th e SQL S er ver U tili t y da shb oar d an d vie wp oi.
SQL S er ver 2 00 8 R 2 Enh an ce me nt s fo r DBA s CHAP TE R 1 7 envi ronment. This can be done at scale, wit h in forma tion on resou rce uti lizati on throug hout the mana ged database envi ronme nt, as a res ult of centra lized visib ili t y.
8 CHAP TER 1 SQL S er ver 2 00 8 R 2 Edi tion s an d En ha nc em ent s In the exa mple in Fig ure 1 - 4, a DBA ha s optimiz ed har dwa re re so urces w ithin the e nviro n- ment by m odif ying t he glob al utiliz atio n policie s to me et the n ee ds of th e organ iza tion .
SQL S er ver 2 00 8 R 2 Enh an ce me nt s fo r DBA s CHAP TE R 1 9 spoke (control no de an d comput e nod es) architec tur e. Per form ance impr ovem ent s can be at t aine d with Par allel Da ta Ware.
10 CHAP TER 1 SQL S er ver 2 0 08 R 2 Edi tion s a nd En ha nc em en ts PowerPivo t data a cces s in the fa rm . This ne w appr oach p romis es b et te r integr ation with Shar ePoint w hile als o enh ancing Sha re Point ’s suppor t of Powe rPivot wo rkb oo k s publish ed to Sh are Point.
S QL S er ver 20 0 8 R 2 Edit ion s CHAP TER 1 11 ■ Hyp er-V impr oveme nt s Building o n the app roval a nd succe ss of th e ori ginal Hyp er-V re leas e, W indow s Se r ve r 20 08 R 2 de liver s s ever al new c apa bilitie s to the Hyp er-V plat f orm to f ur th er impr ove the SQ L Se r ve r vir t ualiz ation e xpe rie nce.
12 CHAP TER 1 SQL S er ver 2 0 08 R 2 Edi tion s a nd En ha nc em en ts Prem iu m Edit ions The pr emium e dition s of SQL S er ver 20 08 R 2 ar e mea nt to mee t the high es t dem ands of larg e-s cal e dat acente r s and dat a war eh ouse s olut ions .
S QL S er ver 20 0 8 R 2 Edit ion s CHAP TER 1 13 ■ Sta nda rd SQL S er ver 20 08 R 2 Stan dar d is a compl ete dat a man age ment an d BI plat fo rm that p rov ides m edium -cla ss s olutio ns for s malle r organ iza tions .
1 4 CHAP TE R 1 SQL Se r ve r 20 0 8 R2 E diti ons a n d Enh an ce me nt s ■ Comp ac t SQL Ser ver 20 0 8 R 2 Compa ct i s t yp icall y use d to deve lop m obile a nd small desk top applic atio ns. I t is fr ee to us e and i s commo nly r edis trib ute d with em be dde d and mo bile ind ep end ent sof t ware ve ndo r (IS V ) ap plic ation s.
Har dw ar e an d So f t w ar e Re quir em en ts CHA PT ER 1 15 HAR DWARE COMP ON EN T RE QU IRE ME NT S Disk Spa ce Data bas e Engine: 280 M B Anal ysis S er vice s: 9 0 MB Repo r tin g Ser vice s: 12.
16 CHAP TER 1 SQL S er ver 2 0 08 R 2 Edi tion s a nd En ha nc em en ts Installation, Upgr ade, and Migra tion Stra tegies Like its p re dece ss or s , SQL S er ver 20 08 R 2 is avail able in b oth 32.
Ins t all ati on , Upgr a de, a nd M igr ati on Str ate gi es CHAP TE R 1 1 7 In-P la ce Upgrade P ros and Cons The in-p lace up grad e str ateg y is usu ally ea sier a nd con sider ed l es s risk y compar ed to t he side -by-side m igratio n str ateg y .
18 CHAP TER 1 SQL S er ver 2 0 08 R 2 Edi tion s a nd En ha nc em en ts Si de-b y - Side M ig ratio n The te rm side- by-side migrati on de scrib es th e dep loym ent of a bra nd-ne w SQL S er ver 20 08 R2 ins t ance al ong side a le gac y S QL Se r ve r ins tan ce.
Ins t all ati on , Upgr a de, a nd M igr ati on Str ate gi es CHAP TE R 1 19 Howeve r , th er e are dis ad vant age s to the si de- by-side s trate gy.
.
21 C H A P T E R 2 M u l t i - S e r v e r A d m i n i s t r a t i o n O ver the ye ar s, an in crea sing num ber of o rgani zat ions h ave turne d to Micro sof t SQL Ser ver be c ause i t emb odie s the Micr osof t Data Plat f orm v isio n to help or gani za - tions m anag e any dat a, at any pl ace, an d at any time.
22 CHAP TER 2 Mult i-Se r ve r Adm inis tr ati on SQL Se r ve r Utilit y . It f orm s a colle c tion of man age d ins ta nces w ith a re pos itor y for pe r fo r- mance da ta an d mana gem ent p olicies .
The S QL S er ver Ut ilit y CHA PT ER 2 23 R E AL W O R L D M any or ga ni za tio ns th at p ar ticip at e in th e Mic ro so f t SQL S er ver e a rl y ad opt er pr ogr am a re c urr en tl y eit he r eva lu atin g SQL S er ver 2 0 08 R 2 or a lre a dy u sin g it in th eir p ro duc ti on inf ra st ru c tur e.
24 CHAP TER 2 Mult i-Se r ve r Adm inis tr ati on ■ The Ut ilit y E xpl or er u se r int er fa ce A compo nent of SSM S , this inter face pr ovid es a hiera rchica l tre e vie w for man agin g and contr olling th e SQL S er ver Utilit y .
The S QL S er ver Ut ilit y CHA PT ER 2 25 UCP P rere qui sites As w ith oth er SQL S er ver comp one nts an d fea ture s, th e dep loym ent of a SQ L Ser ver UCP mus t mee t the fo llowin g spe cic pr ere quisi tes an d re quire ment s: ■ Th e SQL S er ver ver sion r unnin g the UCP mus t be S QL Se r ve r 20 08 R 2 or high er.
26 CHAP TER 2 Mult i-Se r ve r Adm inis tr ati on Creating a UCP Th e UCP is r e la ti ve l y e a s y to s et up a nd co n gu r e. Y ou c an d ep l oy it e it he r by us in g th e Create Ut ilit y Contr ol Point Wi zar d in SSMS o r by leve ragin g Win dows Powe rSh ell s cript s.
Cre at ing a U CP CH AP TE R 2 27 5 . O n the Spe cif y The Ins t ance O f SQL S er ver pag e, click the Con nec t b ut ton to sp ecif y the ins ta nce of SQL S er ver in which th e new U CP will be cre ated , and th en click Con- nec t in th e Conne c t T o Se r ve r dialo g box .
28 CHAP TER 2 Mult i-Se r ve r Adm inis tr ati on 8 . O n the nex t pag e, the SQ L Ser ver in st ance i s compar ed a gains t a se rie s of pre re quisite s bef ore th e UCP is creat ed. Faile d condi tions ar e disp layed in a vali dation r ep or t .
Enr oll ing S QL S er ver In st a nc es C HA PT ER 2 29 UCP P ost -Instal lat ion S teps Whe n the Crea te Utilit y Co ntrol Point W iz ard i s close d, th e Utilit y E x plo rer is invo ked, an d you are a utomati call y conne c ted to th e new ly cre ate d UCP .
3 0 CHAP TE R 2 Multi-Se r ver Adm inis tr ati on Managed I nstance Enro ll ment Prerequ isites As w ith many of th e other t a sk s in this chap ter, cert ain con dition s mus t be s atis e d to suc - ces sf ully enr oll an in st ance: ■ Y ou mus t have admini str ator pr ivil eg es on th e inst an ce of SQL S er ver .
Enr oll ing S QL S er ver In st a nc es C HA PT ER 2 31 8 . A s sh own in Figur e 2-4, a s er ies of con ditio ns will b e evalua ted a gains t the SQ L Ser ver inst an ce to ens ure that i t pas se s all of the pr er equisi tes b efor e the in st ance i s enro lled .
32 CHAP TER 2 Mult i-Se r ve r Adm inis tr ati on Enrol lin g SQL Ser ver I nst ances by U sing Wind ows P owerShel l Wind ows Powe rSh ell c an als o be us ed to e nroll in st ance s . In fac t , scr ipting may b e the way to go if the re is a n ee d to enr oll a lar ge numb er of ins t ances int o a SQL Se r ve r UCP .
Man a ging U tilit y Admin is tra tio n Se t tin gs C HA PT ER 2 33 FIG UR E 2-5 The Ma nag e d Ins t ance s da shb oar d Managing Util ity Administra tion Sett ings Af ter yo u are con nec te d to a .
3 4 CHAP TE R 2 Multi-Se r ver Adm inis tr ati on 3 . O n the Utilit y E xplo rer to olba r , click th e Conne c t T o Utilit y icon. 4 . In th e Conne c t T o Se r ve r dialo g box , spe cif y a UCP inst ance , and th en click Conne c t .
Man a ging U tilit y Admin is tra tio n Se t tin gs C HA PT ER 2 35 FIG UR E 2-6 Modif yin g glob al p olici es f or ma nag ed i ns ta nce s V olatil e Resource Po licy Evaluat ion The na l se ct ion on th e Polic y ta b is Volatile Reso urce Polic y Eva luation .
36 CHAP TER 2 Mult i-Se r ve r Adm inis tr ati on FIG UR E 2- 7 Volatil e re so urc e po lic y ev alu atio n The ne x t set of con gurab le elemen ts al lows you to determine how frequent ly C PU uti liza - tion p olices should be in viola tion b efore t he C PU is reported as being underut iliz ed.
Man a ging U tilit y Admin is tra tio n Se t tin gs C HA PT ER 2 37 The Secu rity T ab From a se curit y and auth ori zat ion pe r spe c tive , ther e are t wo secur it y r ole s as socia ted w ith a UCP . The r st r ole i s the Utilit y Adminis trator, and the se cond r ole is t he Utilit y Reade r .
38 CHAP TER 2 Mult i-Se r ve r Adm inis tr ati on 3 . O n the Ge ne ral pa ge, e nter the n ame of a W indow s use r in the L ogin Nam e box . 4 . S ele c t Win dows Au thentic atio n. 5 . Click OK . NOTE Un like in th e pr ev iou s ex amp le , do n ot a ss ign th is us er t he s y sa dmi n rol e on th e Se r ve r Rol e pa ge.
Man a ging U tilit y Admin is tra tio n Se t tin gs C HA PT ER 2 39 R E AL W O R L D M any or ga ni za tio ns h ave la r ge te am s ma na gin g th eir S QL Se r ver inf ra st ru ct ur es be ca us e th ey h ave hu ndr ed s of S QL Se r ver in st an ce s wit hin th ei r env iro n- men t .
4 0 CHAP TE R 2 Multi-Se r ver Adm inis tr ati on 4 . I n t h e U ti l i t y E xp l o r e r Co n t e n t p a n e , s e le c t th e d e s ir e d d a t a r e t e nt i o n p er io d f o r th e U MDW, as d isp laye d i n Fi gur e 2-9. The opt ion s a re 1 mo nth , 3 mo nth s, 6 m ont hs , 1 year, or 2 years .
41 C H A P T E R 3 D a ta - T i e r Ap p l i ca t i o n s A sk applic atio n devel ope r s or dat aba se adm inistr ator s wh at it was like to wor k with data -dr iven a pplic ation s in the pa st , .
42 CHAP TER 3 Dat a - T ie r Ap pli ca tio ns The Da ta- Tier Ap pli cation L ife C ycle Ther e are t wo common m eth ods fo r gen eratin g a DAC. On e is to auth or and b uild a DAC using a SQ L Ser ver dat a-tie r applic atio n proj ec t in Micro sof t Visua l Studio 2010.
In tr odu c tion t o Da ta - Tie r Ap pli ca tio ns C HA PT ER 3 43 Data -tier deve lop er s usin g a dat a-tier ap plic ation pr oje c t templ ate in Vi sual Studio 2010 r st buil d a DAC and then dep loy the DAC packa ge to an ins t ance of SQ L Ser ver 20 0 8 R2.
4 4 CHAP TE R 3 Data - Tie r A ppl ic atio ns Real W orl d O rg ani z atio ns l oo kin g to a cce le ra te an d st a nd ar diz e dep loy me nt o f dat a ba se app lic ati on s wit hin th eir d at ab a se e nvi ron me nt s sho ul d leve ra ge d at a-ti er app lic ati on s inc lud ed i n SQL S er ver 20 0 8 R 2.
Vi su al Stu dio 2010 an d Da ta - Tie r Ap pl ica tio n Pr oje c ts CHAP TE R 3 45 ■ T y pe: Us er- de ne d Data Type ■ T y pe: Us er- de ne d T able T ype ■ Us er ■ V iew Database administrator s do not ha ve to worr y about look ing for unsuppor ted objec ts .
4 6 CHAP TE R 3 Data - Tie r A ppl ic atio ns 4 . In th e Proje c t T emp late pa ne, s ele c t Dat a- T ier A pplic ation . 5 . Sp ecif y the nam e, lo cati on, a nd so lution n ame fo r the dat a-tier a pplic ation , as sh own in Figure 3-2, and click OK .
Vi su al Stu dio 2010 an d Da ta - Tie r Ap pl ica tio n Pr oje c ts CHAP TE R 3 4 7 FIG UR E 3-3 T he Cr e at e T ab le s ch e ma a nd t he S ol u ti on E x p l or e r p a n e i n a V i su al St ud i.
4 8 CHAP TE R 3 Data - Tie r A ppl ic atio ns 4 . Re view t he infor matio n on the Welco me pa ge, an d then click N ex t . 5 . O n the Spe cif y Impor t Optio ns pa ge, s ele c t the op tion that a llows yo u to impo r t fr om a data -tier app licati on pa ckage .
E x tr ac tin g a Da t a-Tier A ppl ic atio n wi th SQ L Se r ve r Ma na ge me nt Stu dio CHAP TE R 3 49 Ex tracting a Data- Tier Application with SQL Ser ver Management Studio The E x tr ac t Dat a- Tier A pplic atio n Wiz ar d is anoth er to ol that you c an us e for cr eating a n ew data -tier app licati on.
50 CHAP TER 3 Dat a - T ie r Ap pli ca tio ns ■ De sc r ipt io n This pro per t y is opt ion al. Use it to de sc ri be the DAC. If thi s sec ti on is c ompl eted, the i nfor mat ion i s saved in the msdb da tab ase un der th e dat a-tier applic ation s no de in Mana gem ent Studio.
E x tr ac tin g a Da t a-Tier A ppl ic atio n wi th SQ L Se r ve r Ma na ge me nt Stu dio CHAP TE R 3 51 co n rms that th e inf orm at ion is supp orted b y t he D A C, an d disp lays D AC obje ct issue s, D AC o bj ect warn ings, and D A C ob jects th at are su pported.
52 CHAP TER 3 Dat a - T ie r Ap pli ca tio ns Installing a New D A C Instance with t he Deploy Data- Tier Application Wizard Af ter th e DAC package ha s be en cre ate d using th e dat a-tier ap plica.
Ins t alli ng a Ne w DAC Inst a nc e wit h th e De plo y Dat a - T ie r Ap pli ca tio n Wi z ar d C HA PT ER 3 53 FIG UR E 3- 7 Spe cif yi ng a DAC pack ag e to de ploy w ith t he D epl oy Dat a-Tier .
5 4 CHAP TE R 3 Data - Tie r A ppl ic atio ns 9. The n ex t pa ge inclu des a s ummar y of the se t tings t hat are u se d to dep loy the dat a-tie r applic ation . Rev iew th e inform ation dis playe d in the Summar y pa ge and DAC prop er- ties tr ee to e nsur e that the a c tions t aken ar e corr ec t , an d then click Ne x t to contin ue.
Regi ste ri ng a Da t a-Tier A pp lic ati on C HAP TE R 3 55 NOTE T hr oug ho ut thi s ch ap ter, you ca n al s o us e Win dow s Powe r Sh ell s cr ipt s in c on- junc ti on wi th da ta -tie r ap plic at ion s to do m any o f th e ta sk s disc us s ed , su ch a s ■ Cre atin g da ta -tie r ap plic at ion s.
56 CHAP TER 3 Dat a - T ie r Ap pli ca tio ns 5 . O n the Se t Prop er ties pa ge, co mple te the DAC prope r tie s by t y ping in th e applic atio n name , ver sion , and d es cription , as d escr ibe d her e: ■ Ap p lic at ion n am e This ref er s to the n ame of th e DA C .
De le ting a D at a - T ie r Ap pli ca tio n CHA P TER 3 57 ■ Del et e Dat ab as e The DA C met ada ta an d the a sso ciate d dat abas e are dr opp ed . The dat a an d log l es ar e dele ted . Login s are n ot re moved . T o del ete th e DA C , follow th es e ste ps: 1 .
58 CHAP TER 3 Dat a - T ie r Ap pli ca tio ns 6 . Re view t he infor matio n displaye d in the Summ ar y p age , as sh own in Figur e 3- 10. FIG UR E 3-10 Vie win g the S umma r y p age w he n de let ing a DAC Ensure th at the ap plica tion nam e, dat aba se na me, an d del ete me tho d are cor re c t .
Upg ra din g a Da ta - Tie r Ap pli ca tio n CH AP TER 3 59 Upgrad ing a Data- Tier Application Let us r ec all the p as t for a m oment , wh en up dating cha nge s to exis ting da tab as e sche mas and dat aba se ap plica tions w as a notic eabl y challe nging t as k.
6 0 CHAP TE R 3 Data - Tie r A ppl ic atio ns 6 . On the Select Pac kage page, specif y the D AC pac kage t hat contai ns t he new D AC v ersion to upgrade to . Alt ernatively , you ca n use the Browse button t o spec if y the locati on o f t he D AC package .
Upg ra din g a Da ta - Tie r Ap pli ca tio n CH AP TER 3 61 NOTE If t he d at ab a se h as c ha ng e d, it i s a be st p ra c tice t o re vie w th e po ten tia l dat a los s es b ef or e you p ro ce e d an d ver if y t ha t thi s is th e ou tcom e yo u wan t for t he up gra d- ed d at ab a se .
62 CHAP TER 3 Dat a - T ie r Ap pli ca tio ns FIG UR E 3-12 Rev iew ing t he r esul t info rm atio n on th e Upg rad e DAC page NOTE D at a-ti er a ppl ic atio ns a re a l ar ge a nd in tri ca te s ubje c t .
63 C H A P T E R 4 H i g h A v a i l a b i l i t y a nd V i r t u a l i za t i o n E n h a n c e m e n t s M icros of t SQ L Ser ver 20 0 8 R2 d elive r s seve ral e nhance ment s in th e area s of high availabilit y and vir tuali zati on.
6 4 CHAP TE R 4 High Avail ab ilit y and V ir tua liz a tion E nh an ce me nt s ■ Win dows S er ver 20 0 8 R2 Hy pe r-V The Hyp er-V vir tualiz ation t echno log y im- prove ment s in Win dow s Ser ver 20 0 8 R2 w ere th e mos t so ught-afte r and anticip ated enhan ceme nt s for W indow s Ser ver 20 0 8 R 2.
Failov er Cl us ter in g wit h Win do ws S er ver 20 0 8 R 2 CH AP TER 4 65 including In ternet Information Ser v ices (IIS), Cluster Ser ver , SQL Ser ver 7 .0 Enterprise Edition, Microsof t Distributed T rans action Coordinator (MSDT C ) 2.0, and sometimes the Window s NT 4.
6 6 CHAP TE R 4 High Avail ab ilit y and V ir tua liz a tion E nh an ce me nt s Figure 4 - 1 illus trat es a t wo -no de sin gle- inst an ce failove r clust er run ning SQ L Ser ver on Wind ows S er ver 20 08 R 2.
Failov er Cl us ter in g wit h Win do ws S er ver 20 0 8 R 2 CH AP TER 4 67 Guest F ailo ver C luste ring In the pa st , phys ical s er ver s wer e usuall y af liate d wi th the no des in a f ailover clu ste r .
6 8 CHAP TE R 4 High Avail ab ilit y and V ir tua liz a tion E nh an ce me nt s NOTE G ue st c lu ste ri ng is a ls o su pp or te d w he n Hyp e r-V is on Wi ndo ws S er ver 20 0 8. Howe ver, Wind ows S er ver 2 0 08 R 2 pr ovi de s Li ve Migr ati on f or mov in g vir tua l ma chi ne s be t we en p hys ic al ho st s .
Failov er Cl us ter in g wit h Win do ws S er ver 20 0 8 R 2 CH AP TER 4 69 • Lis t Clus ter Volumes • Lis t Clus ter Se r v ice s And A pplic ation s • Validate Quor um Cong uration • Vali.
70 CHAP TER 4 High Av ail abi lit y a nd V ir tua liz a tion E nh an ce me nt s 6 . O n the Conr mation p age , rev iew th e det ails f or ea ch tes t, an d the n click Nex t to be gin the valid ation pr oce ss .
Failov er Cl us ter in g wit h Win do ws S er ver 20 0 8 R 2 CH AP TER 4 71 The Win dow s Ser ver 2008 R2 Best Practi ces Analy zer Anoth er to ol availab le in Win dow s Ser ver 20 0 8 R2 i s a ser ver man age ment to ol r efer re d to as the B est Pra c tices An aly zer ( BPA) .
72 CHAP TER 4 High Av ail abi lit y a nd V ir tua liz a tion E nh an ce me nt s SQL Ser ver 2008 R2 V ir tualization and Hyper- V Vir tualiz ation i s one of th e hot te st top ics of dis cus sion in .
SQL Se r ve r 20 0 8 R2 V ir t ua liz a tio n an d Hy pe r-V CHA P TER 4 73 Hyper -V01 Hyper -V02 Hyper -V03 Hyper -V04 C:ClusterSharesV olume1 VHD VHD VHD VHD C:ClusterSharesV olume2 VHD VHD VHD VHD .
7 4 CHAP TE R 4 High Avai lab ilit y an d Vir tua liz a tio n Enh an ce me nt s NOTE Sys te m re qu ire me nt s va r y b as e d on a n or gan iz at ion's vir tu ali z ati on re qu ire me nt s .
Implem en tin g Li ve Migr at ion f or SQ L Se r v er 20 0 8 R2 CHAP T ER 4 75 NOTE T he Mi cr os of t A s se s sm ent a nd Pl an nin g T o olk it c an b e us ed t o ide ntif y whe th er o r not a n or ga niz a tion’s SQL Se r ver s ys tem s a re go o d ca ndi dat es f or v ir t ual iz ati on .
76 CHAP TER 4 High Av ail abi lit y a nd V ir tua liz a tion E nh an ce me nt s Enabl ing CSV As sumin g that the H yp er-V cluste r has a lrea dy b ee n built , the ne x t s tep is e nablin g CSV in Failover Cluste r Mana ger.
Implem en tin g Li ve Migr at ion f or SQ L Se r v er 20 0 8 R2 CHAP T ER 4 77 5 . O n the Spe cif y Name An d Lo catio n pag e, ente r the na me of the SQ L Se r ve r VM and spe cif y w her e it will b e sto re d.
78 CHAP TER 4 High Av ail abi lit y a nd V ir tua liz a tion E nh an ce me nt s 7 . O n the N et wor kin g pag e, conn ec t th e net work ad apter to a n exis ting v ir tu al net work by sel ec tin g the app rop riate n et wo rk ada pter f rom th e men u.
Implem en tin g Li ve Migr at ion f or SQ L Se r v er 20 0 8 R2 CHAP T ER 4 79 11. Fr o m t h e V i r t ua l Ma ch in es s e c ti o n o f t h e r e su lt s p an e in Hy p e r-V Ma na g er, r ig ht- cli ck t h e n am e of th e SQ L Se r ve r VM yo u ju s t c re a te d , a n d cl ic k C on ne c t .
8 0 CHAP TE R 4 High Avail ab ilit y and V ir tua liz a tion E nh an ce me nt s 4 . Un der Auto matic Sta r t Ac tion , for th e What D o Y o u Want This Vir tual Machin e T o Do Whe n The Physi cal Com puter St ar t s? questio n, s ele c t Nothin g, as sh own in Fig ure 4 -8 .
Implem en tin g Li ve Migr at ion f or SQ L Se r v er 20 0 8 R2 CHAP T ER 4 81 11. O n the S ele c t Ser vic e Or Ap plica tion pa ge, sh own in Fig ure 4 -9, click Vir tu al Machin e, and the n click Nex t . FIG UR E 4 -9 Se le c ting t he s er vic e and a ppli cat ion f or hi gh avai labi lit y 12.
82 CHAP TER 4 High Av ail abi lit y a nd V ir tua liz a tion E nh an ce me nt s NOTE To make a VM high ly a vail ab le , you m ust e n sur e th at it i s not r un ning . It mu st be e ith er tu rn ed o f f or sh ut do wn . 13. Co nrm the s el ec tio n, an d then click Ne x t .
Implem en tin g Li ve Migr at ion f or SQ L Se r v er 20 0 8 R2 CHAP T ER 4 83 In itia ti ng a Live M ig rat io n of a SQ L Ser ver VM Af ter an a dminis trator h as en able d CS V , cre ated a S QL S.
.
85 C H A P T E R 5 C o n s o l i d a t i o n a n d M o n i t o r i n g T oday's com peti tive e conomy dic t ate s that or ganiz atio ns re duce co st an d impr ove agilit y in t heir dat ab ase e nviro nment s .
8 6 CHAP TE R 5 Cons oli dat ion a nd M oni tor ing Conso li dat ing Databases and Instances A ver y common SQ L Ser ver con soli dation s trate gy invol ves p lacing m any data bas es on a sin - gle ins tan ce of SQL S er ver .
SQL S er ver C on so lida tio n Stra te gi es C HA PT ER 5 87 SQLInstance01 SQLInstance02 SQLInstance03 FIG UR E 5-2 Con so lidat ing m any dat ab as es o nto a si ngle p hys ica l ho st r unni ng th .
8 8 CHAP TE R 5 Cons oli dat ion a nd M oni tor ing and 6 4 -bi t ver sion s within a sin gle ho st . In ad dition , physi cal SQ L Ser ver s c an eas ily be v ir t ual- ized by u sing th e physic al-to-v ir t ual (P2V ) migratio n tool in clude d with Sys tem Cent er Vir tual Machine Ma nag er 20 0 8 R2.
Usin g th e SQL S er ve r Utilit y for C on so lid atio n an d Mo nito rin g CH AP TE R 5 89 Using the SQL Ser ver Util ity for Consolidation and Monit oring The SQ L Ser ver Utili t y is the c enter of o per ation s for mo nitor ing man age d ins ta nces of S QL Ser ver, databas es , and d epl oyed dat a-tie r applic ation s .
9 0 CHAP TE R 5 Cons oli dat ion a nd M oni tor ing The thr ee m ain dashb oar ds for m onito ring a nd man aging r es ource u tiliz ation an d cons oli- dation e f for ts ar e dis cuss ed in th e nex t sec tio ns . The se da shbo ard s and vi ewp oint s are ■ Th e SQL S er ver Utilit y d ashb oar d.
Usin g th e SQL S er ve r Utilit y Da shb oa r d CH AP TER 5 91 The SQ L Ser ver Utili t y dash boa rd inclu des th e follow ing info rmati on: ■ Util it y S umm ar y Fo und in th e center of t he top r ow of the Utili t y E xpl ore r Content pane , thi s section is the rst pl ace t o look.
92 CHAP TER 5 Con so lid ati on a nd Mo nit or ing ● No D ata Avai la ble Eithe r dat a has no t be en uplo ade d fr om a ma nage d ins t ance or the re is a pr obl em wit h the colle c tion an d uplo ad pr oce ss .
Usin g th e SQL S er ve r Utilit y Da shb oa r d CH AP TER 5 93 ■ Dat a- Tie r Ap pli cat io n Hea lt h This se c tion is l oc ated in th e top -right co rne r of the Utilit y E x plor er Conte nt pan e.
9 4 CHAP TE R 5 Cons oli dat ion a nd M oni tor ing ● O ver uti liz ed D at aba se Fi les This r epr es ent s the numb er of de ploye d dat a-tier applic ation s with da tab ase les th at are v iolatin g le sp ace over utiliz atio n polici es .
Usin g th e Ma na ge d In st an ce s V iew p oint CHAP TER 5 95 This se c tion e xplain ed h ow to obt ain summ ar y inf orma tion fo r all mana ge d inst an ces of SQL Se r ve r . D BA s se ek ing mo re infor matio n might be inte re ste d in the M anag ed In st ance s nod e in the tre e vi ew of Utilit y E xplo rer.
9 6 CHAP TE R 5 Cons oli dat ion a nd M oni tor ing Reso urce utili zati on for e ach man age d ins ta nce of SQL S er ver is pr es ente d in the lis t vie w loc ate d at the top of th e Utilit y Ex plor er Conte nt pan e.
Usin g th e Ma na ge d In st an ce s V iew p oint CHAP TER 5 97 ■ V o lu me Sp ac e V olum e spa ce utiliz ation i s summar ize d in this column f or volume s with dat aba se s be longin g to each m anag ed in st ance .
9 8 CHAP TE R 5 Cons oli dat ion a nd M oni tor ing FIG UR E 5- 8 Th e Stora ge U tili za tion t ab o n the M ana ge d Ins t anc es v iew po int Independent of how the les are g r ouped, hea lth status is communi cated fo r ev er y data- bas e, le gro up, datab as e le, or vo lume.
Usin g th e Ma na ge d In st an ce s V iew p oint CHAP TER 5 99 FIG UR E 5-9 The Polic y Det ail s t ab on th e Man ag ed In st an ce s vi ewp oint NOTE To overri de th e glo ba l pol ic y fo r a sp ec ic m an ag ed in s ta nc e, s el ec t th e O ver - rid e Th e Glo ba l Polic y optio n but t on .
100 CHA PT ER 5 Con sol ida tio n an d Mon ito rin g FIG UR E 5-10 The Pr op er t y De ta ils t ab o n the M ana ge d Ins t ance s v iew po int Using the Data- Tier Application Viewpoin t As i t is wh.
Usi ng th e Da t a-Tier A ppl ic atio n V iew po int CHAP TER 5 101 FIG UR E 5-11 The da ta -tie r app lic atio n vi ewp oin t Reso urce utili zati on for e ach de ploye d dat a-tier ap plica tion is pr es ente d in the lis t vi ew lo - cate d at the to p of the Utilit y E xplo rer Co ntent pa ne.
102 CHA PT ER 5 Con sol ida tio n an d Mon ito rin g The Da ta- Tier Ap pli cation L ist View The colum ns pr ese nting th e st ate of he alth fo r each de ploye d dat a-tier a pplic ation in th e dat.
Usi ng th e Da t a-Tier A ppl ic atio n V iew po int CHAP TER 5 103 T wo line ar graph s are pr es ente d nex t to each othe r . T he r s t graph sh ows CPU utiliz a - tion ba se d on the s ele c t.
104 CHA PT ER 5 Con sol ida tio n an d Mon ito rin g ■ Polic y D et ai ls The Polic y D et ails t ab, sho wn in Figur e 5- 13, i s whe re a DBA c an vi ew the glob al po licies app lie d to a sel ec te d dep loyed d ata -tier app licati on.
Usi ng th e Da t a-Tier A ppl ic atio n V iew po int CHAP TER 5 105 Th e dis p lay i s b r oke n up int o th e f o llo w in g f o ur p ol ici e s , w hi ch c an b e v i ew e d or ove r r id de n: ● .
.
P A R T I I B u s i n e s s I n t e l l i g e n c e D e v e l o p m e n t S t acia M iSner CHA P TER 6 Sc alab le Dat a Warehou sing 109 CHA P TER 7 Mas ter D ata S er vice s 125 CHA P TER 8 Compl ex .
.
10 9 C H A P T E R 6 S ca l ab l e D a ta W a re h o u s i n g M icros of t SQ L Ser ver 20 0 8 R2 Par allel Da ta Wareh ous e is an ente rpr ise d ata wa re - hous e appli ance ba se d on te chnolo gy o rigina lly cre ated b y DA T A lle gro an d acquire d by Micr osof t in 20 08 .
110 CHA PT ER 6 Sc ala bl e Da ta Wa re ho us ing Warehou se sof t war e. Wh en the a ss embl y pro ces s is comp lete , the ve ndor sh ips the ap plianc e to you usin g shock pr oof pall et s. W hen i t arr ives , you r em ove the app liance f rom th e palle t s, plug it into a p ower s ourc e, and co nne c t it to your ne t wor k .
Par all el D at a War eh ou se A rc hit ec t ur e CHA P TER 6 111 Control rack Data rack Management node active/passive User queries Control node active/passive Landing Zone Backup node Control rack A.
112 CHA PT ER 6 Sc ala bl e Da ta Wa re ho us ing The Cont rol Rack The contr ol ra ck is a se parate r ack that h ous es the s er ver s , sto rage , and n et wo rkin g com- pon ent s for the n ode s that p rovi de contr ol, ma nage me nt, or int er f ace f unc tion s.
Par all el D at a War eh ou se A rc hit ec t ur e CHA P TER 6 113 Deve lopm ent Studio, SQ L Ser ver Inte grati on Se r v ices , SQL S er ver An aly sis Se r v ices , an d SQL Ser ver Rep or ting Se r v ices .
114 CHA PT ER 6 Sc ala bl e Da ta Wa re ho us ing The Landing Zone Node The L an ding Zon e is a high- cap acit y d ata s tora ge no de in th e contro l rack that co ntain s tera - by te s of disk spa ce for te mpo rar y stor age of us er dat a b efor e loa ding it into t he app liance.
Dat a M an ag em ent CHA PT ER 6 115 any nece ss ar y data to e ach comp ute no de so t hat it c an pro ces s the qu er y in parall el with other co mpute n ode s with out r equir ing dat a fr om oth er lo cati ons dur ing pr oce ssin g.
116 CHA PT ER 6 Sc ala bl e Da ta Wa re ho us ing Y ou d esign th e dat a layout on th e appli ance to avoid o r minimize dat a move ment f or par - allel qu eri es by usin g eith er a r eplic ate d or a dis tribu ted s trate gy f or sto rag e.
Dat a M an ag em ent CHA PT ER 6 11 7 nodes i n the applianc e. There are p er formance cons iderati ons f or t he selection of a d istribut ion column, s uch as dis tinc tn ess , dat a skew, and the t ype s of quer ie s execut ed on th e s ys tem .
118 CHA PT ER 6 Sc ala bl e Da ta Wa re ho us ing CREA TE DA T AB ASE The CRE A TE DA T A BASE s t ateme nt has a s et of op tions f or supp or ting dis tribu ted an d re pli- cate d ta ble s. You determin e how mu ch space yo u ne ed in tot al for t he dat aba se fo r rep licate d tab les , dis trib uted t ab les , and l ogs .
Dat a M an ag em ent CHA PT ER 6 119 NOTE Pa ra lle l Da ta Wa re ho us e do es n ot us e th e T r an s ac t-SQL pa r ti tion s ch em a or p ar t i- tion f unc ti on. A l so, yo u ca n cr e ate a c lu ste r ed in de x onl y wh en yo u us e CRE A TE TABLE .
120 CHA PT ER 6 Sc ala bl e Da ta Wa re ho us ing In additi on, yo u can u se a CRE A TE T ABLE A S SELEC T st atem ent to cre ate a t able f rom t he res ults of a SELEC T s ta teme nt. You might use this te chniqu e whe n you are r edi str ibutin g or defr agme nting a t abl e.
Dat a M an ag em ent CHA PT ER 6 121 Quer y Processin g Que r y p roc ess ing in Parall el Dat a Wareho use is m ore co mple x than in an SMP dat a war e - hous e be ca use pr oce ssin g mus t man age high ava ilabilit y , p aralle liz ation , and d ata m oveme nt bet ween n ode s .
122 CHA PT ER 6 Sc ala bl e Da ta Wa re ho us ing Data Ware hous e’s Landin g Zone . Y ou t hen invoke a comm and-lin e tool , DWLoa der, and spe c- if y o ption s to loa d the dat a into the ap plian ce. Or yo u can us e Inte gration S er vice s to move data to th e L andin g Zone an d call th e loa ding f unc tion alit y dir ec tl y.
Bu sin es s Int el lige nc e Int eg rat ion C HA PT ER 6 123 Business Intelli gence Integra tion Parallel D ata Wareh ous e integr ates w ith the SQ L Se r ve r busin ess int ellige nce (B I) comp o- nent s — Inte grati on Se r v ices , Re por ting Se r v ices , an d SQL Se r ve r Ana lys is Ser vic es .
.
125 C H A P T E R 7 M as t e r D a ta S er v i c es M icros of t SQ L Ser ver 20 0 8 R2 M as ter Dat a Se r v ices ( MDS) is a nothe r new te chno log y in the SQL S er ver famil y and i s bas ed on s of t ware f rom Micr osof t ’s acquisition of Stratat ure in 20 07 .
126 CHA PT ER 7 Mas te r Da ta S er vi ce s This s cenario presents add itional p roblems fo r opera tional master data in an org aniza- tion becau se the re is n o coor dinatio n acro ss multip le s ys tem s. B usine ss us er s c annot b e sure whi ch of the many availa ble s ys tem s has th e corr ec t info rmat ion.
Mas ter D at a S er vic es C omp on en ts CHAP T ER 7 127 In additi on to of f erin g ex ibilit y, MDS allows you to m anag e mas ter da ta p roa c tivel y. Inst ead of dis cover ing dat a pr oble ms in fa iled E TL pr oce ss es or in accurat e rep or t s , you c an enga ge bu sines s us er s as da ta stewards .
128 CHA PT ER 7 Mas te r Da ta S er vi ce s Master Da ta Ser vices Co ngu ratio n Manager Befo re you c an s tar t using M DS to mana ge your ma ste r dat a, yo u use Ma ste r Dat a Ser vice s Congurati on Mana ger.
Dat a Ste wa rd shi p CH AP TER 7 129 ■ Inte grat io n Man ag em ent Use t his are a to creat e and pr oce ss b atches f or im- por ting dat a fr om st a ging t able s into the MDS da tab ase , vie .
130 CHA PT ER 7 Mas te r Da ta S er vi ce s An enti t y can a lso h ave any numbe r of domain -bas ed a t trib utes w hos e value s are m em- ber s of an othe r rel ated e ntit y. In the examp le in Figur e 7 - 1 , the Pro duc tSub Ca tego r y at tribute is a domain -bas ed a t trib ute.
Dat a Ste wa rd shi p CH AP TER 7 131 FIG UR E 7- 3 A coll ec ti on Master Da ta Main tenance Mas ter Dat a Man age r is mor e than a pl ace to de ne mo del o bje c ts . It als o allow s you to create , edit , an d update l eaf me mbe r s and con soli dated m emb er s .
132 CHA PT ER 7 Mas te r Da ta S er vi ce s FIG UR E 7- 5 At tri but es an d val idat ion i ss ues Busi ness Rules One of th e goal s of a mas ter dat a ma nage ment s y ste m is to se t up dat a corr ec tl y once an d to prop agate o nly va lid chang es to dow ns tre am sy st ems .
Dat a Ste wa rd shi p CH AP TER 7 133 FIG UR E 7- 7 The Re quir ed F iel ds b usin es s r ule Whe n creatin g a busin es s rul e, you c an use a ny of the follo wing t ype s of ac tion s: ■ Defa ult.
134 CHA PT ER 7 Mas te r Da ta S er vi ce s T r ansaction Logg ing MDS use s a tran sa cti on lo g, as s hown in Fi gure 7 -9, to captur e ever y chang e mad e to mas ter data , includin g the m ast e.
Inte gr ati on CH AP TE R 7 135 Integr ation Mas ter Dat a Man age r also p rov ides s uppo r t for d ata inte grati on be t we en MDS an d othe r ap- plica tions . Ma ste r Data M anag er inclu des an Int egrat ion Man age ment ar ea fo r impo r ting a nd expo r tin g dat a.
136 CHA PT ER 7 Mas te r Da ta S er vi ce s sel ec te d. Wh en the b atch pro ces sing i s compl ete, yo u can r evi ew the s t atus of th e batch in the st a ging batch l og, w hich is availab le in Ma ste r Data M anag er, as shown in Figur e 7 - 11.
Admin is tra tio n CH AP TER 7 137 subscr iption v iew in Ma st er Dat a Mana ger a s an entit y-bas ed le af me mbe r view, you c an quer y the Pro duc t vi ew and s ee t he re sult s in SQL S er ver Mana gem ent Studio, a s show n in Figure 7- 12.
138 CHA PT ER 7 Mas te r Da ta S er vi ce s create a n ew ver si on by copy ing a pr evi ousl y commit ted ver sio n and all owing u ser s to m ake their chan ge s to the ne w ver sion .
Admin is tra tio n CH AP TER 7 139 Figure 7- 15. An adm inistr ator wi th full a cces s pri vile ge s would in ste ad se e the f ull lis t of fun c- tional a rea s on the h ome p age. FIG UR E 7- 15 Th e Ma ste r Dat a Ma nag er h om e pag e for a u se r wi th onl y E xp lor er p e rmi ssi ons Data s ecur it y b egin s at the m ode l leve l.
14 0 CHA PT ER 7 Mas te r Da ta S er vi ce s Color t abl e on the r ight side of th e pag e. Th ese ico ns indi cate th at the val ues in th e tab le ar e not edi tab le.
Admin is tra tio n CH AP TER 7 141 More s pe cicall y, the secur it y cong uration a llows thi s use r to edit o nly th e Bikes and Ac - ces sor ies c ate gori es in the Re t ail group, bu t the us er c annot e dit c atego rie s in the W hole sal e group.
142 CHA PT ER 7 Mas te r Da ta S er vi ce s Model De plo yment Whe n you have nali zed th e mas ter da ta m ode l str uc tur e, you c an us e the mo del d epl oy - ment c apab ilities in M ast er Dat a Mana ger to s er ialize th e mod el and i ts o bje ct s as a p acka ge that you c an late r dep loy on an other s er ver .
Pro gra mm ab ilit y CHA P TER 7 143 ■ Mic ro sof t . Ma ste rDat a Se r v ice s. S e r vi ce s Con t a in s a c la s s t o p r ov i d e in s t an c e s of t h e M ds S e r v i ce H o s t cl a s s a n d a cl a s s to pr o v i de a n A PI f o r o p e ra t io n s r e l at e d t o bu s in e s s r u l e s ■ Mic ro sof t .
14 4 CHA PT ER 7 Mas te r Da ta S er vi ce s ■ Mdq .Re ge xIsV alid Indic ates w het her th e re gular ex pre ssio n is valid ■ Mdq .Re ge xMa sk Conver t s a se t of re gular ex pre ssio n optio n ags into a b inar y value ■ Mdq .Re ge xMatc h es Finds all m atches of a r egul ar expr es sion in an in put s trin g ■ Mdq .
1 45 C H A P T E R 8 C o m p l e x E v e n t P r o c es s i n g w i t h S t r e a m I ns i g h t M icros of t SQ L Ser ver Strea mInsight i s a compl ex event p roc ess ing (CEP) engine. Thi s techn olog y is a ne w of fe ring in t he SQL S er ver famil y, making it s r st ap pe arance in SQL Se r ve r 20 08 R 2.
146 CHA PT ER 8 Comp le x Even t Pr oc es sin g wi th Str ea mIn si ght Similarl y , ther e are ce r t ain t y pe s of applic ation s that b ene t fr om the a bilit y to an aly ze data a s clos e as po ssib le to the tim e that the a pplic ation s capt ure th e data .
Stre am Ins ight A r chi te c tur e CH AP TER 8 147 Data S tructures The high -through put dat a that Stre amIns ight re quire s is kn own as a str eam .
148 CHA PT ER 8 Comp le x Even t Pr oc es sin g wi th Str ea mIn si ght P agers and monitoring devices Input Adapters Data feeds Event stores and databases Web servers Devices and sensors Event Event .
Stre am Ins ight A r chi te c tur e CH AP TER 8 14 9 Output Ad apters The ou tput a dapter s r ever s e the op er ations of t he inpu t adapte r s by tran slatin g event s into a form at that is u sab le by the t ar get d evice a nd the n sen ding th e trans late d dat a to the dev ice.
150 CHA PT ER 8 Comp le x Even t Pr oc es sin g wi th Str ea mIn si ght If you choo se to de ploy CEP as a s ta ndalo ne se r ve r , ther e are s ome limit ati ons tha t af fe c t the way you de velo p applic atio ns .
A pp lic at ion D eve lo pme nt C HA PT ER 8 15 1 Ser ver dat aba se mu st a dapt to the s chem a of the t able th at it que rie s. I nst ead , you pr ovide the t able s chem a in a congurat ion sp ecic ation w hen th e adap ter is b ound to t he que r y.
152 CHA PT ER 8 Comp le x Even t Pr oc es sin g wi th Str ea mIn si ght the e lds on e at a time an d enqu eue t he event . Th e unt yp e d outpu t adapt er wor k s similar ly, but ins tea d it mus t be a ble to us e the con guratio n spe cica tion to re tri eve quer y pr oce ssin g res ults f rom a d equ eue d eve nt.
A pp lic at ion D eve lo pme nt C HA PT ER 8 153 The na l ste p is to crea te a .NE T as se mbl y for the a dapte r . At minimum , the a dapter include s a cons tr uc tor , a Star t( ) me thod , a .
154 CHA PT ER 8 Comp le x Even t Pr oc es sin g wi th Str ea mIn si ght Anoth er t ask th e adapt er mus t pe r fo rm is cla ssi catio n of an event . Th at is , the ada pter mus t spe cif y th e event k ind a s eithe r INSERT o r Curre nt Time In crem ent (CT I).
A pp lic at ion D eve lo pme nt C HA PT ER 8 155 Queries Af ter yo u create a n event s tre am obje c t , you wr ite a LIN Q expr es sion o n top of the eve nt str eam ob jec t .
156 CHA PT ER 8 Comp le x Even t Pr oc es sin g wi th Str ea mIn si ght Even t Windows A windo w rep res ent s a subs et of dat a fr om an eve nt str eam f or a pe rio d of time.
A pp lic at ion D eve lo pme nt C HA PT ER 8 157 As yo u might gue ss , the key to wo rkin g with w indow s is to have a cle ar unde r st an ding of the time sp an that e ach wind ow cover s . The re ar e thre e t yp es of w indow s tre ams th at Stream- Insight sup por ts — h oppi ng windo ws , snapsho t windo ws , an d count wind ows .
158 CHA PT ER 8 Comp le x Even t Pr oc es sin g wi th Str ea mIn si ght windo ws s trea m. Alt hough yo u can u se the H oppin gWindow m eth od to cre ate tumb ling win- dows , the re is a T umblingWin dow me thod . The fo llowin g code ill ustr ates h ow to count eve nts in tumblin g windo ws that o ccur eve r y ha lf hour.
A pp lic at ion D eve lo pme nt C HA PT ER 8 159 Count wind ows ar e compl ete ly dif f er ent fr om the ot her w indow t ype s be cau se the s ize of the win dows i s variab le. W hen yo u create w indow s , you pr ovide a p aram eter n a s a count of event s to ful ll within a w indow.
160 CHA PT ER 8 Comp le x Even t Pr oc es sin g wi th Str ea mIn si ght As sume yo u want to app ly the Sum a nd Avg aggr egati ons to e ld x in an inp ut st ream .
A pp lic at ion D eve lo pme nt C HA PT ER 8 161 Joi ns Y ou c an us e a join op erati on to match eve nts f ro m t wo st ream s. T he CEP ser ver r s t matche s event s onl y if they h ave overl appin g time inter vals , an d then a pplie s the con dition s that you spe cif y in th e join pr edic ate.
162 CHA PT ER 8 Comp le x Even t Pr oc es sin g wi th Str ea mIn si ght Quer y T empl ate B indi ng The me tho d that the CEP se r ve r use s to ins ta ntiate the qu er y templ ate as a s ta nding qu er y dep end s on the de velo pme nt mod el that yo u use .
The Ma na ge me nt In ter fa ce CH AP TE R 8 163 The Quer y Object In both th e explicit a nd impli cit devel opm ent mo del s, yo u create a q uer y obje c t.
164 CHA PT ER 8 Comp le x Even t Pr oc es sin g wi th Str ea mIn si ght Wind ows P owerShel l Diag nostics For quick anal ysi s, yo u can us e Win dow s Power Shell s cript s to vi ew diagn os tic infor mation rathe r than wr itin g a comple te diagn ost ic applic atio n.
165 C H A P T E R 9 R e p o r t i n g S e r v i c es E n h a n c e m e n ts I f you tho ught Micros of t S QL Se r ve r 20 08 Re por ting S er vices intr odu ced a l ot of great ne w fe at ur e s to th e re p or ti n g p la t f or m , ju s t w ai t unt il yo u dis c ove r wh at ’s n ew in Repo r tin g Ser vice s in SQL S er ver 20 0 8 R2.
166 CHA PT ER 9 Rep or ti ng S er vic es E nh an ce me nt s Com bini ng Data from More T han One Da tase t T o displ ay data f rom m ore t han on e sour ce in a tab le (or in any data r egio n, fo r that mat t er), you mus t crea te a dat ase t that s omeh ow comb ines th e dat a be cau se a dat a re gion bin ds to one an d only o ne dat as et .
Ex pr e ssi on L a ng ua ge Imp r ovem en ts CHAP TE R 9 167 =Lookup(Fields!StateProvinceCode.Value, Fields!StProv.Value, Fields!StProvName.Value, "Dataset1") The Mult iLoo kup f unc tion al so r equir es a on e-to -o ne re lation ship be t we en the s our ce and des tinatio n, b ut it acce pts a s et of s ource v alue s as inpu t.
168 CHA PT ER 9 Rep or ti ng S er vic es E nh an ce me nt s Whe n ther e is a one -to- many re lation ship be t we en the s our ce and de st ination v alue s, yo u use th e Loo kupSe t fun ct ion.
Ex pr e ssi on L a ng ua ge Imp r ovem en ts CHAP TE R 9 169 FIG UR E 9- 4 Aggre gat ion of a n agg re gati on Her e is the ex pre ssi on for th e value di splaye d in the Mo nthly Avera ge r ow: =Avg(Sum(Fields!SalesAmount.
1 70 CHAP TER 9 Rep or t ing S er vi ce s Enh an ce me nt s Anoth er opt ion is to us e the Re nderF ormat gl obal v ariab le with t he IsInt era ct ive me mbe r to set th e condi tions of a p rop er t y .
L ayo ut Con tr ol C HAP TE R 9 1 71 FIG UR E 9- 6 Changing r ep or t var iabl es T o wr ite to your r epo r t var iabl e, you us e the S etValue meth od of th e varia ble.
1 72 CHAP TER 9 Rep or t ing S er vi ce s Enh an ce me nt s Pag inati on Properties Ther e are t hre e new p rop er ties availab le to man age p aginati on: Dis able d, Re se tPage Numbe r , and Page Nam e.
L ayo ut Con tr ol C HAP TE R 9 1 73 Las t, consider how you can use the P ageName prope r t y. As one example, instead of using page n umbe r s in an E xcel wor kb ook , you can a ssign a uni que nam e to each sh ee t in the work bo ok .
1 7 4 CHA P TER 9 Rep or t ing S er vi ce s Enh an ce me nt s FIG UR E 9-9 Synchro nize d gr oup s T e x t Box Or ientat ion Each tex t box has a Wri tingMo de pr ope r t y that by def ault disp lays tex t hor izont ally. There i s also a n option to di splay tex t ver ti call y to accomm odate l angua ges th at displ ay in that for - mat .
Dat a V is ua liz a tion CHA PT ER 9 1 75 Data Visualization Prior to SQ L Se r ve r 20 08 R 2 Rep or t ing Se r v ices , your o nly op tion fo r enha ncing a r epo r t wi th data v isuali zat ion was to a dd a char t or gaug e. Now yo ur option s have be en e xpan ded to include da ta b ar s, sp ark line s, in dicato r s, an d map s.
1 76 CHAP TER 9 Rep or t ing S er vi ce s Enh an ce me nt s Sparkl ines Like data b ar s, s park line s can b e use d to inclu de a dat a vis ualiz ation al ong side the d et aile d data . Wh ere as a dat a bar u sually s hows a s ingle p oint, a s park line sh ows mul tiple dat a p oints over time , makin g it ea sier to s pot tr end s.
Dat a V is ua liz a tion CHA PT ER 9 1 77 FIG UR E 9-13 Indic ato r t y pe s Af ter s ele c ting a s et of indi cator s , you a ss ociate th e set w ith a val ue in your dat as et or w ith an expr es sion , such as a co mpar iso n of a data se t value to a go al.
1 78 CHAP TER 9 Rep or t ing S er vi ce s Enh an ce me nt s Altho ugh you c an man ually con gure th e prop er ties fo r the map a nd ea ch map layer, the easi es t way to get s ta r te d is to dra.
Reu s abi lit y CHAP TE R 9 1 79 Shared Datasets A share d dat as et allo ws you to d ene a qu er y once fo r reu se in many r ep or t s , much as yo u can cre ate a sha re d data so urce to de ne a re us able co nne ct ion s tring .
180 CHA PT ER 9 Rep or ti ng S er vic es E nh an ce me nt s Se r v ic es ca n r es po nd to a r e po r t re qu es t f as t er, and u se r s a re ge ne ra lly hap pi er w it h th e re po r t ing s ys te m. Ho weve r, cach e s to ra ge i s n ot un limi te d.
Reu s abi lit y CHAP TE R 9 181 Y ou c an pub lish re por t par ts bo th fro m Rep or t B uilde r 3.0 an d Repo r t D esign er in Bus ines s Intellige nce D evel opme nt Studio. In Rep or t Design er, the Repor t menu co ntain s the Publi sh Repo r t Par t s com mand .
182 CHA PT ER 9 Rep or ti ng S er vic es E nh an ce me nt s Altho ugh you c an pub lish re por t par t s in Re por t Desi gner an d Rep or t B uilde r 3.0 , you can on ly us e Rep or t B uilder 3 .0 to nd a nd us e thos e re por t par t s . Mor e inform ation ab out Repo r t Buil der 3 .
Rep or t B uil de r 3.0 C HA PT ER 9 183 Report Builder 3.0 Repo r t Buil der 1 .0 was t he r st r el eas e of a rep or t develo pme nt tool t ar gete d for b usine ss use rs . Th at ver sio n res tr ic ted th e use r s to quer ies b ase d on a r epo r t m ode l and sup por ted limited r ep or t l ayout ca pabilit ies .
184 CHA PT ER 9 Rep or ti ng S er vic es E nh an ce me nt s FIG UR E 9-19 The Re po r t Par t Gall er y Report Access and Management In this late st r ele ase of R epo r tin g Ser vice s, yo u can b e.
Repo r t Ac ce ss a nd M an ag em en t CH AP TER 9 185 FIG UR E 9-20 Repor t Vi ewe r Notice al so that th e Rep or t V iew er do es not in clude a link to o pen th e re por t prop er ties .
186 CHA PT ER 9 Rep or ti ng S er vic es E nh an ce me nt s Repo r t Viewer I mprov ements The disp lay of re por ts is a lso imp rove d in the Rep or t Vie wer availab le in this r ele as e of SQL Ser ver, which now suppo r t s A JA X (A s y nchro nous Jav aScript a nd XML).
Sha r ePoi nt Int eg ra tion CHAP TE R 9 187 SharePoin t Integr ation SQL Se r ver 2 0 0 8 R 2 Re p or ting Se r vic es con tinu es to im pr ove inte gr atio n w ith Sha re Poin t.
188 CHA PT ER 9 Rep or ti ng S er vic es E nh an ce me nt s cre ate th e d at a s o u r ce u s in g t h e Mi cr o s o f t Sh a r e Po in t L is t c o nn e c ti o n t yp e an d p r o vi d e cr e d e nt.
18 9 C H A P T E R 1 0 S e l f - S er v i c e A n a ly s i s w i t h P o w er P i v o t M any busine ss int ellige nce (B I) so lution s re quire a cces s to centrali zed , clean se d data in a data w areh ous e, an d ther e are m any goo d rea son s for an o rgani zat ion to continue to m aintain a d ata wa reh ous e for the se s oluti ons .
190 CHAP TER 10 Sel f-Ser vic e An al ys is w ith Po wer Piv ot Po werPiv ot for E xcel PowerPivo t for E xcel is a n add-in th at ex te nds t he fun c tiona lit y of E xcel 2010 to suppor t analy sis of la rge , rel ated da ta set s on yo ur comput er .
Powe rPi vot f or E xc el CHA P TER 10 191 The A tom Data Feed Provider La st , the a dd-in in st alls an Atom dat a fe ed p rov ider to a llow you to imp or t data f rom Atom data f ee ds into a Power Pivot wor kb ook . A d ata f ee d prov ide s dat a to a client app licati on on re que st .
192 CHAP TER 10 Sel f-Ser vic e An al ys is w ith Po wer Piv ot • IBM D B2 8.1 • Sybas e • Any dat aba se th at can b e acce ss ed by us ing an OL E DB pr ovide r or an OD BC dri ver ■ File s • De limite d tex t l es (.tx t , .tab, a nd .
Powe rPi vot f or E xc el CHA P TER 10 193 Link ed T a bles If your dat a is in an E xcel t abl e alrea dy, or if you conver t a range of d ata into an E xce l ta ble, you can a dd the t ab le to your wor kb oo k in the E xcel w indow an d the n use th e Create L inked T able b ut ton to imp or t the dat a into the Power Pivot win dow.
194 CHAP TER 10 Sel f-Ser vic e An al ys is w ith Po wer Piv ot Relati onships By buildin g re lation ships b et we en th e dat a, you c an ana ly ze th e data a s if it all c ame f rom a common s our ce.
Powe rPi vot f or E xc el CHA P TER 10 195 FIG UR E 10 -5 Filt er ing a n ume ric co lumn b y val ue IM P ORTANT Use o f a lte r is n ot a s ec uri t y me a sur e.
196 CHAP TER 10 Sel f-Ser vic e An al ys is w ith Po wer Piv ot Y ou can use the H ide a nd Unh ide b utton on the Design tab ( show n in F igu re 1 0 -4) t o co ntr ol the a ppear anc e of a c olu mn i n t he P owerP ivo t wind ow an d also i n the Pivo tT able Field List.
Powe rPi vot f or E xc el CHA P TER 10 197 stor e the dat a tha t you se lec te d fo r the char t. Jus t as yo u do with a s t anda rd PivotT able o r Pi vot Ch ar t , yo u se l e c t th e p l ac eh o.
198 CHAP TER 10 Sel f-Ser vic e An al ys is w ith Po wer Piv ot FIG UR E 10 -9 Th e CUBE VALUE fu nc tio n Sli cers The t ask p ane fo r PowerPi vot is similar to th e one yo u use fo r an E xcel PivotT abl e, but i t include s t wo ad dition al drop zo nes f or slice r s.
Powe rPi vot f or E xc el CHA P TER 10 199 Data Analysi s Expressions The abili t y to comb ine dat a fr om multip le so urces into a s ingle Pivo tT abl e is ama zingl y pow- er f ul, bu t you ca n c.
20 0 CH AP TE R 10 Self-Ser vi ce A na ly sis w it h Powe rPi vot rep or t , pr ovid e a name f or the cur re nt PivotT a ble if you w ant, a nd the n spe cif y t he for mula for the m eas ure, a s show n in Figure 10 - 11.
Powe rPi vot f or Sh ar e Poin t CHAP TE R 10 201 FUNC TI ON T YPE E X AM PLE DE SC RIP TI ON Statis tical =AVERAGEX(ResellerSales, [SalesAmount]- [TotalProductCost]) Evaluate s the ex pre ssio n in t.
202 CHAP TER 10 Sel f-Ser vic e An al ys is w ith Po wer Piv ot ShareP oint Farm Web fr ont end Application server P owerPivot database Analysis Ser vices- V er tiPaq Mode Excel Calculation Ser vices .
Powe rPi vot f or Sh ar e Poin t CHAP TE R 10 203 balan cing acro ss s er ver s for qu er y pro ces sing if mult iple s er ver s are avail able . Fur ther mor e, the Power Pivot Syste m Ser vic e mana.
20 4 CH AP TE R 10 Self-Ser vi ce A na ly sis w it h Powe rPi vot Con tent Managem ent Content ma nage ment f or Power Pivot is quit e simple b ec aus e the dat a an d the pr es ent ation layout ar e kept in the s ame do cume nt.
Powe rPi vot f or Sh ar e Poin t CHAP TE R 10 205 or right a rrow t o brin g a dif fe rent t humbnail into t he pr evie w are a. Y ou can a lso s wit ch to All Docum ent s vie w, which allows yo u to se e all the wo rkb oo k s in a st an dard do cume nt librar y view.
20 6 CH AP TE R 10 Self-Ser vi ce A na ly sis w it h Powe rPi vot appl ica tion th at can co nne ct to Ana lysis Servic es d irectl y can use the Pow erP ivo t W eb Service . Y ou si mply u se t he S hare Po int URL for th e workb ook in ste ad o f a n Analy si s Se rvices server na me i n t he co nn ecti on st ring of the pro vider .
Index 207 A adap te r bas e cla ss es , 151 Adap ter Fac tor y obj ec t s , 152 adap te rs , f or CEP app lic ati ons , 151 - 154 Admin Co ns ole , 122 aggr e gate f un c tio ns , 168 A JA X , 18 6 An.
20 8 dat a fe ed l ibra ri es , 205 Dat a Move me nt Se r vice ( DM S), 112 dat a ra ck s, 111 dat a so urce s joini ng , 166 - 168 for Pow erP ivot f or E xce l, 191 - 193 dat a st ewar ds , 12 7 dat a t y pe s supp or ted in Pa rall el D ata War eh ous e, 1 20 supp or ted in Po wer Pivo t, 195 dat a war eh ous e ap plia nce s , 109 - 110.
209 gue st m od el , 67 - 68 his tor y of, 6 4 - 65 tra diti ona l mo del , 65 tro ubl esh oo ting , 70 vali datin g pr er e quisi te s for, 68-7 0 fe edb ack o n bo ok , x ix le sp ace u tili za t.
210 over ut ilize d r es our ces , 92 pro ce ss or ut iliz at ion , 9 6 unde r utili ze d re so urce s , 92 vie win g, 91 Mana ge d In st an ces v ie wp oint ( Util it y E x plo re r), 95-1 0 0 mana ge me nt no de , 114 mana ge me nt uti liti es .
21 1 Paral lel D at a Wareh ou se Admin Co ns ole , 122 archi te c tur e of, 109 - 115 auto mati c gro w th f eat ure , tog glin g, 118 con guri ng, 110 contr ol n od e, 112- 113 cre atin g ta ble .
212 re por ts alte rn ate a cce ss ma ppi ngs , 187 ca che co ngur in g, 179- 18 0 on DAC depl oym en t, 5 4 dat a s ync hro niz ati on , 173 dat a vi sual iz atio n en han cem ent s , 175- 17 8 ed.
21 3 The ate r vi ew (Po wer Pivo t Gall er y), 20 4 -205 Tim e Inte llig en ce f unc ti ons ( Powe rPi vot), 193 T o pK op er ati on, 16 0 tran s ac tio n lo gs for MD S , 131 - 13 4 spa ce all oc at.
214 vir tuali z atio n. S ee also H yp er -V cons oli dat ion w ith , 87-88 tech no log y fo r, 7 2 Vi sual Stu dio 2010 dep loy ing DACs f ro m, 45- 46 impo r ti ng DACs into , 47 - 4 8 volum e sp ac.
215 About the Authors Ross Mis tr y is a techni cal ar chite ct a t the Micro sof t T echno log y Cente r (MTC) in Silicon Valley. Ross provi des e xecuti ve bri eng s, ar chite ct ural design s es sion s, a nd pr oof of con cept wo rk sh ops to or ganiz atio ns loc ate d in the Silicon Valley .
S t a y i n t o u c h ! T o subscribe to the Micr osoft Press ® Book Connection Newsletter —for news on upcoming books, events, and special offers—please visit: W h a t d o y o u t h i n k o f t .
Ein wichtiger Punkt beim Kauf des Geräts Microsoft SQL Server 2008 R2 (oder sogar vor seinem Kauf) ist das durchlesen seiner Bedienungsanleitung. Dies sollten wir wegen ein paar einfacher Gründe machen:
Wenn Sie Microsoft SQL Server 2008 R2 noch nicht gekauft haben, ist jetzt ein guter Moment, um sich mit den grundliegenden Daten des Produkts bekannt zu machen. Schauen Sie zuerst die ersten Seiten der Anleitung durch, die Sie oben finden. Dort finden Sie die wichtigsten technischen Daten für Microsoft SQL Server 2008 R2 - auf diese Weise prüfen Sie, ob das Gerät Ihren Wünschen entspricht. Wenn Sie tiefer in die Benutzeranleitung von Microsoft SQL Server 2008 R2 reinschauen, lernen Sie alle zugänglichen Produktfunktionen kennen, sowie erhalten Informationen über die Nutzung. Die Informationen, die Sie über Microsoft SQL Server 2008 R2 erhalten, werden Ihnen bestimmt bei der Kaufentscheidung helfen.
Wenn Sie aber schon Microsoft SQL Server 2008 R2 besitzen, und noch keine Gelegenheit dazu hatten, die Bedienungsanleitung zu lesen, sollten Sie es aufgrund der oben beschriebenen Gründe machen. Sie erfahren dann, ob Sie die zugänglichen Funktionen richtig genutzt haben, aber auch, ob Sie keine Fehler begangen haben, die den Nutzungszeitraum von Microsoft SQL Server 2008 R2 verkürzen könnten.
Jedoch ist die eine der wichtigsten Rollen, die eine Bedienungsanleitung für den Nutzer spielt, die Hilfe bei der Lösung von Problemen mit Microsoft SQL Server 2008 R2. Sie finden dort fast immer Troubleshooting, also die am häufigsten auftauchenden Störungen und Mängel bei Microsoft SQL Server 2008 R2 gemeinsam mit Hinweisen bezüglich der Arten ihrer Lösung. Sogar wenn es Ihnen nicht gelingen sollte das Problem alleine zu bewältigen, die Anleitung zeigt Ihnen die weitere Vorgehensweise – den Kontakt zur Kundenberatung oder dem naheliegenden Service.
