Manual Microsoft SQL Server 2008 R2

236 pages Not applicable
Download

Go to site of 236

Summary
  • Microsoft SQL Server 2008 R2 - page 1

    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? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . x v ii Pre- Rel eas e Sof t wa ...

  • Microsoft SQL Server 2008 R2 - page 2

    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. N o par t of the contents o f t his book may be reproduced or tr ansm itted i n any fo r m or by any means without the wr itte n perm issi on of ...

  • Microsoft SQL Server 2008 R2 - page 3

    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 . —s t acia M isne R ...

  • Microsoft SQL Server 2008 R2 - page 4

    ...

  • Microsoft SQL Server 2008 R2 - page 5

    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 P TER 3 Dat a- Tie r App lica tion s 4 1 CHA P TER 4 High Availa bilit y and V ir tu ali za tion E nha nce men ts 63 CHA P TER 5 Cons olid ation a nd ...

  • Microsoft SQL Server 2008 R2 - page 6

    ...

  • Microsoft SQL Server 2008 R2 - page 7

    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 .com /l ea rnin g/ bo ok sur vey vi i Cont ents Intr oduc t ...

  • Microsoft SQL Server 2008 R2 - page 8

    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 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ...

  • Microsoft SQL Server 2008 R2 - page 9

    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 . . . . . . . . . . . . . . . . . . . . 6 4 T radi tional Failove r Cluste rin g 65 Gue st Failover Clus ter ing 67 Enhanc ...

  • Microsoft SQL Server 2008 R2 - page 10

    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 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109 Data Ware hous e App liance s 109 Proce ssin g Ar chitec t ure 110 The Mult i-Rack Sys tem 110 Hub-an d-Spoke Archi tec tur e 115 Data M anag ...

  • Microsoft SQL Server 2008 R2 - page 11

    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 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137 V er sion s 137 Sec urit y 138 ...

  • Microsoft SQL Server 2008 R2 - page 12

    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. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165 Combinin g Data f rom M ore T han On e Dat ase t 1 6 6 ...

  • Microsoft SQL Server 2008 R2 - page 13

    Con ten ts xi ii CHAPTER 10 Self- Se r v ice Anal ysis with PowerPivot 189 PowerPivo t for E xcel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 0 The Power Pivot Add -in for E xcel 190 Data S ource s 19 1 Data Pr epa ration 19 3 PowerPivo t Repo r t s 196 Data A nal ysis E x pre ssi ons 199 PowerPivo t ...

  • Microsoft SQL Server 2008 R2 - page 14

    ...

  • Microsoft SQL Server 2008 R2 - page 15

    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 . Y our h ard wo rk , cont ribut ions , edit s , and p er se veran ce are m uch a ...

  • Microsoft SQL Server 2008 R2 - page 16

    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. — R oss Mist r y T he chapte r s of Par t II cover ing th e BI ...

  • Microsoft SQL Server 2008 R2 - page 17

    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 . B ec aus e this ver s ion is R el ea se 2 ( R2) of SQ L S er ve r 20 0 8 , you might thi nk th e ch an ge s ar e rel ativel y mino r — m ore th an a se r v ...

  • Microsoft SQL Server 2008 R2 - page 18

    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 . Chapte r 3, “Da ta-Tier App licat ...

  • Microsoft SQL Server 2008 R2 - page 19

    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 . Be awar e that you m ight als o notice s ome min or dif f ere nce s bet ween the RTM version of the product and the descriptions and screen ...

  • Microsoft SQL Server 2008 R2 - page 20

    ...

  • Microsoft SQL Server 2008 R2 - page 21

    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- T ier A pplic atio ns 41 CHA P TER 4 High Availab ilit y an d Vir tualiz ation Enhance ment s 63 CHA P TER 5 Cons olidati on and M onitor ing 85 ...

  • Microsoft SQL Server 2008 R2 - page 22

    ...

  • Microsoft SQL Server 2008 R2 - page 23

    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. Building on the succ ess of the origi nal SQL Ser ver 2 008 release, SQL Ser ver 2008 R2 has made an impact on organizations worldwi de wi th its gr ...

  • Microsoft SQL Server 2008 R2 - page 24

    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. T ...

  • Microsoft SQL Server 2008 R2 - page 25

    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. Figu re 1 - 1 il lustrates SQ L Server Uti lity dashboard and viewpoin ts for provid ing superior insight into resou rce u til ization and p ...

  • Microsoft SQL Server 2008 R2 - page 26

    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 oint s ■ Custo miz at io n of ut iliz at io n th re sh old s an d po lic ie s DB A s ca n cust omize t he uti lizati on thresho ld and po li cie s for ...

  • Microsoft SQL Server 2008 R2 - page 27

    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. In additi on, a da t a- tier deve l- oper is bui ldi ng a data-t ier a ppl icat ion w ith Visua l S tu ...

  • Microsoft SQL Server 2008 R2 - page 28

    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 . For examp le, the gl obal CPU over utiliz atio n policie s of a man age d ...

  • Microsoft SQL Server 2008 R2 - page 29

    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 Wareh ous e’s design appr oach b ec aus e it par tition s larg e tab les ove r seve ral phy sica l nod es , re sulting in e ach no de hav ing it s own C ...

  • Microsoft SQL Server 2008 R2 - page 30

    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. Chap ter 10, “Se lf-Ser v ice Ana lys is with Powe rPivo ...

  • Microsoft SQL Server 2008 R2 - page 31

    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. Fir s t , availabil ...

  • Microsoft SQL Server 2008 R2 - page 32

    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 . Th e t wo edi tions a re ■ Dat ac ent er For the rst time in the histor y of SQL Ser ver , ...

  • Microsoft SQL Server 2008 R2 - page 33

    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 . It do es n ot include all t he be lls and w his tles in clude d in Dat acent er and Ente rpr ise; h owever, it continue s ...

  • Microsoft SQL Server 2008 R2 - page 34

    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. NOTE ...

  • Microsoft SQL Server 2008 R2 - page 35

    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: 120 MB Integra tion Se r vices : 120 MB Client comp one nts: 850 MB SQL Se r ve r Bo ok s On line: 240 MB T A BL E 1- 2 Sof t w are R equ ire me nt s SO ...

  • Microsoft SQL Server 2008 R2 - page 36

    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 -bit an d 6 4 -bit e dition s, b oth of which ca n be ins t alled e ithe r with th e SQL S er ver Ins tall ation W iz ard or t hrou gh a com- mand p ...

  • Microsoft SQL Server 2008 R2 - page 37

    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 . Upgra ding is a lso f airly q uick , and a dditio nal har dwar e is not re quire d. Be ca use ...

  • Microsoft SQL Server 2008 R2 - page 38

    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. Wh en the S QL Se r ve r 20 08 R 2 ins t allation is compl ete, a D BA migrate s da ...

  • Microsoft SQL Server 2008 R2 - page 39

    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. Addition al hard ware m ight nee d to be p urcha se d. Ap plica tions mi ght also n ee d to be dir ec te d to the n ew SQL S er ver 20 08 R 2 ins ta nce, an d it might n ot be a b es t prac tic e fo ...

  • Microsoft SQL Server 2008 R2 - page 40

    ...

  • Microsoft SQL Server 2008 R2 - page 41

    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. T he big ges t challe nge s o ...

  • Microsoft SQL Server 2008 R2 - page 42

    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 . Af te r dat a is colle c ted f ro m mana ged in st anc es , Utilit y E xplo rer a nd SQL S er ver Utilit y dashb oar d and v iewp oint s in SQL S ...

  • Microsoft SQL Server 2008 R2 - page 43

    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. Th e co ns en su s is th at o rg ani z at ...

  • Microsoft SQL Server 2008 R2 - page 44

    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 . It s us es include co nne c ting to a utilit y , creatin g a UCP , enro lling ins t ance s, de ploy ing dat a ...

  • Microsoft SQL Server 2008 R2 - page 45

    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 cic 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. ( SQL Ser ver ...

  • Microsoft SQL Server 2008 R2 - page 46

    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. The high -leve l ste ps for cr eatin g a UCP include sp e cif y i ...

  • Microsoft SQL Server 2008 R2 - page 47

    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 . 6 . Sp ecif y a name f or the U CP , as illustr ate d in Figure 2-3 ...

  • Microsoft SQL Server 2008 R2 - page 48

    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 . Cor - re ct a ll issu es , and th en click the Re run Validation b ut ton to ver if y the chang e ...

  • Microsoft SQL Server 2008 R2 - page 49

    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 . The UCP is au tomatic all y en- rolle d as a m anag ed ins t ance . The dat a colle c ...

  • Microsoft SQL Server 2008 R2 - page 50

    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 . ■ Th e ins tan ce of ...

  • Microsoft SQL Server 2008 R2 - page 51

    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 . If ther e are any f ailure s pre venting th e enr ollme nt of the SQL S er ver in ...

  • Microsoft SQL Server 2008 R2 - page 52

    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 . Let ’s say you ne ed to e ...

  • Microsoft SQL Server 2008 R2 - page 53

    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 UCP , us e the Utilit y Adminis tration n od e in the Utilit y Ex plo rer naviga tion pa ne to vie w and co ngure gl obal p olic y s et tin gs, s e ...

  • Microsoft SQL Server 2008 R2 - page 54

    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 . 5 . Af ter you ar e conne c ted , you c an dep loy dat a-tier a pplic ation s, m anag e inst an ces , and con? ...

  • Microsoft SQL Server 2008 R2 - page 55

    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 . This s ec ti on, dis - played in F igure 2- 7 , pr ovide s st rate gies to minimi ...

  • Microsoft SQL Server 2008 R2 - page 56

    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. The de- fault eval uatio n period for processo ...

  • Microsoft SQL Server 2008 R2 - page 57

    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 . The Utilit y Admin istr ator is ultim atel y ...

  • Microsoft SQL Server 2008 R2 - page 58

    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. I f you do , the u se ...

  • Microsoft SQL Server 2008 R2 - page 59

    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 . Le t ’s say yo u wan te d to gra nt 50 u se r s th e re a d- ...

  • Microsoft SQL Server 2008 R2 - page 60

    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 . FIG UR E 2-9 Conf ig urin g th e dat a re t ...

  • Microsoft SQL Server 2008 R2 - page 61

    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 , an d mos t pro babl y do not u se adj ec ti ves such as “easy, ” “enjoyabl e,” or “wonder ful ” whe n they d escr ibe th eir exp er ien ce ...

  • Microsoft SQL Server 2008 R2 - page 62

    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 the seco nd meth od, yo u can e x tra ct a DAC from a n exis ting dat a ...

  • Microsoft SQL Server 2008 R2 - page 63

    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. In contra st , dat aba se admini str ator s usin g the E x tr ac t Dat a- T ier ...

  • Microsoft SQL Server 2008 R2 - page 64

    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. B y utili zing d at a -tier a pp lic a ...

  • Microsoft SQL Server 2008 R2 - page 65

    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 . This laborious ta sk is accomplished with the Ex trac t Data- T ier Appl ...

  • Microsoft SQL Server 2008 R2 - page 66

    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 . FIG UR E 3-2 Se le ct ing t he Dat a - T ie r Ap plic ati on pr oj ec t te mpl ate in V is ...

  • Microsoft SQL Server 2008 R2 - page 67

    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 o 2 010 DAC proje c t Im p or ti ng an E xisti ng Data- Tier Ap pl icat io n Proj e ct int o Visual S tudi o 20 10 Inst ead of cr eatin g a DA C fro ...

  • Microsoft SQL Server 2008 R2 - page 68

    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 . 6 . Click the Br ows e but t on, an d navi gate to the f olde r in which you p lace ...

  • Microsoft SQL Server 2008 R2 - page 69

    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. T he wi zar d is in SQL S er ver 20 0 ...

  • Microsoft SQL Server 2008 R2 - page 70

    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. FIG UR E 3-5 Sp eci f y in g DAC prop er ties w he n usi ng th e ...

  • Microsoft SQL Server 2008 R2 - page 71

    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. If the re a re n o issu es, cl ic k Ne x t to co nt inue . Y ou also hav e ...

  • Microsoft SQL Server 2008 R2 - page 72

    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 tion pr oje ct t empla te in Visu al Studio 2010, the Ex trac t Dat a- Tie r Appli catio n Wi zar d in SQL S er ver Mana gem ent Studio, or W indow s ...

  • Microsoft SQL Server 2008 R2 - page 73

    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 A pp lic atio n Wi z ard NOTE If a d at a ba se w ith t he s am e na me a lr ea dy e xi st s on t he in st an ce o f SQL S er ver, the w iz a rd c an ...

  • Microsoft SQL Server 2008 R2 - page 74

    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. 10. T ...

  • Microsoft SQL Server 2008 R2 - page 75

    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. ■ Cre atin g se r ver obj ec t s . ■ ...

  • Microsoft SQL Server 2008 R2 - page 76

    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 . This val ue ca nnot b e alter ed an d is alw ays ide ntica l to the na ...

  • Microsoft SQL Server 2008 R2 - page 77

    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 . In O bje ct E x plo rer, connec t to a SQ L Ser ver ins t ance cont ainin ...

  • Microsoft SQL Server 2008 R2 - page 78

    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 . If the infor matio n is corr ec t , click Nex ...

  • Microsoft SQL Server 2008 R2 - page 79

    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. Da tab as e adminis trato rs u suall y create d scr ipt s that inclu ded th e ne ...

  • Microsoft SQL Server 2008 R2 - page 80

    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 . When the D AC pac kage i s selected, y ou can verify the D AC detai ls, suc h as the appl ...

  • Microsoft SQL Server 2008 R2 - page 81

    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 . Howe ver, the or igin al d at ab a se i s stil l pr es ...

  • Microsoft SQL Server 2008 R2 - page 82

    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 . Se e th e fol low ing so urc es f or m ore i nfo rm atio n: ■ “De sig ning a nd Im pl em enti ng Da t a-tie r Ap pli ca tion s” at h t ...

  • Microsoft SQL Server 2008 R2 - page 83

    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. Ma ny of the enh ancem ent s are af liate d with th e Wind ows S er ver 20 08 R 2 op erat ing s ys tem an d ...

  • Microsoft SQL Server 2008 R2 - page 84

    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. It is now p oss ible to v ir tua lize ...

  • Microsoft SQL Server 2008 R2 - page 85

    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.0 O ption Pack . Mo re over, the hardwar e supp or t , dri ver sup por t, ...

  • Microsoft SQL Server 2008 R2 - page 86

    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. Public Network Heartbeat Network SQL ClusterInstance01 SAN S torage Node1 Node2 FIG UR E 4 - 1 A t wo -n ode s ingl e -ins t anc e fail ...

  • Microsoft SQL Server 2008 R2 - page 87

    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 . T oday, vir t ualiz ation t echno logi es make it p ossib le to fo rm a clus ter wi th each n ode b eing a g ues t ope rating s y ste m ...

  • Microsoft SQL Server 2008 R2 - page 88

    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 . Thi s is m uch m or e be ne ? ...

  • Microsoft SQL Server 2008 R2 - page 89

    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 Cong uration • Validate Reso urce Stat us • Validate Ser vice Pr incipal Na me • Validate V o lume Con sis tenc y ■ Ne t wor k • Lis t Ne t wor k Bindin g Ord er ...

  • Microsoft SQL Server 2008 R2 - page 90

    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 Conr 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 . Whil e the vali dation p roc ess i s runnin g, s ta tus infor ma- tion is cont inually di splaye d on the Validating p age until all t ...

  • Microsoft SQL Server 2008 R2 - page 91

    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) . The BP A de ter mine s how comp liant a se r ve r rol e is by compar ...

  • Microsoft SQL Server 2008 R2 - page 92

    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 alm ost e ver y SQL Se r ve r archite c tur e design s es sion o r execu tive br ien g se ssio n, main ly be c ause o rgani zati ons ar e be ginnin ...

  • Microsoft SQL Server 2008 R2 - page 93

    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 FIG UR E 4 -5 A Hyp er -V clus te r and L ive M igra tio n Wind ows Ser ver 2 0 08 R2 Hyper- V Syst em Requi rements T able 4 - 1 b elow o utline ...

  • Microsoft SQL Server 2008 R2 - page 94

    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 . Or ga niz a tion s sh oul d siz e the ir wor k loa d s to en sur e th at th e Hy pe r-V hos t s ca n suc ce ss ful ly acc omm od at e all of t he v ir ...

  • Microsoft SQL Server 2008 R2 - page 95

    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 . The t oo lki t als o in clu de s to ol s for S QL Se ...

  • Microsoft SQL Server 2008 R2 - page 96

    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. Follow the ste ps in this s ec ti on to ena ble CS V on a Hy pe r-V failover cluste r runn ing on W indow s Se r ve r 20 0 ...

  • Microsoft SQL Server 2008 R2 - page 97

    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. For ex ampl e, the na me SQL S er ver200 8R 2- V M01 and th e VM can b e sto re d on Clus ter Shar ed Volume 1, a s displaye d in Figur ...

  • Microsoft SQL Server 2008 R2 - page 98

    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. Click Nex t to co ntinue . 8 . O n the Conn ec t V ir tu al Hard D isk pa ge, a s show n in Fi ...

  • Microsoft SQL Server 2008 R2 - page 99

    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 . T he V ir t ua l Machine Con nec ti on too l ope ns ...

  • Microsoft SQL Server 2008 R2 - page 100

    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 . Then click A ppl y and OK . FIG UR E 4 -8 Config uri ng th e Auto mati ...

  • Microsoft SQL Server 2008 R2 - page 101

    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. O n the S ele c t Vir tual Ma chine pa ge, sh ow ...

  • Microsoft SQL Server 2008 R2 - page 102

    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 nrm the s el ec tio n, an d then click Ne x t . 14. T he wi zar d congur es th e VM for hi gh availabili t y and p ...

  • Microsoft SQL Server 2008 R2 - page 103

    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 Se r ve r 20 08 R 2 VM , congur ed t he automa tic st ar t option , and m ade th e VM highl y availabl e, it is tim e to initiate a li ve migra - ti ...

  • Microsoft SQL Server 2008 R2 - page 104

    ...

  • Microsoft SQL Server 2008 R2 - page 105

    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 . This m ean s the lar ge pe rce nta ge of or ganiz a- tions o ut the re r unning un der utilize d Micro sof t SQL Ser ver in st allat ...

  • Microsoft SQL Server 2008 R2 - page 106

    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 . T his app roa ch of fe r s organ iza tions im prove d ope ratio ns t hr o u gh ce n tr a li z e d m an a g e me n t , s t ...

  • Microsoft SQL Server 2008 R2 - page 107

    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 re e ins t anc es of SQL S er ver SQLInstance01 SQLInstance02 SQLInstance03 FIG UR E 5-3 Consoli dati ng ma ny dat ab as es o nto mul tipl e phy sic a ...

  • Microsoft SQL Server 2008 R2 - page 108

    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. Fig ure 5- 4 illustr ates a con soli dation ...

  • Microsoft SQL Server 2008 R2 - page 109

    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 . By usin g the das hb ...

  • Microsoft SQL Server 2008 R2 - page 110

    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. ■ Th e Mana ge d Inst an ce vie wpo int. ■ ...

  • Microsoft SQL Server 2008 R2 - page 111

    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. It dis pla ys the n um ber of ma naged i nstances of ...

  • Microsoft SQL Server 2008 R2 - page 112

    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 . By vie wing t he Mana ge d Ins tan ce He alth se c tion , DBA s ar e able to qui ckly o bta in an over view of r es ource utili zat ...

  • Microsoft SQL Server 2008 R2 - page 113

    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. He alth s tat us is illus trate d in a pie ch ar t an d has f our pos sible d esign ation s: ● Well Ut ili ze d The numb er of dep loye d data ...

  • Microsoft SQL Server 2008 R2 - page 114

    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 . ● O ver uti liz ed St orag e Volume s This rep res ent s the num ber of d epl oyed dat a- tier ...

  • Microsoft SQL Server 2008 R2 - page 115

    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. This node h elps da tab ase a ...

  • Microsoft SQL Server 2008 R2 - page 116

    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. He alth st ate ico ns app ear to t he right of each man age d ins ta nce an d prov ide summ ar y st atus fo r each ins t ance o ...

  • Microsoft SQL Server 2008 R2 - page 117

    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 . The he alth of thi s param ete r is dete rmin ed by th e globa l or lo cal s tora ge volum e utiliz ation p olicie s for man age ...

  • Microsoft SQL Server 2008 R2 - page 118

    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. Fo r examp le, th e gre en arr ows in F igure 5 -8 indic a ...

  • Microsoft SQL Server 2008 R2 - page 119

    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 ic 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 . Nex t, s pe cif y th e new o ver util ize d a n ...

  • Microsoft SQL Server 2008 R2 - page 120

    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 en you us e the Ma nage d Ins t ance s vie wpoint to m onito r heal th st atu s and re so urce utiliz ation f or mana ge d ins tan ces of SQ L Ser ve ...

  • Microsoft SQL Server 2008 R2 - page 121

    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. He alth s ta te icons a ppe ar at the r ight of eac ...

  • Microsoft SQL Server 2008 R2 - page 122

    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 data -tier app licati on lis t vie w include ■ App lic at ion C PU This column disp lays th e heal th st ate utili zat ion of the p roc ess or for the ...

  • Microsoft SQL Server 2008 R2 - page 123

    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 te d depl oyed dat a-tie r appli catio n, an d the se con d graph di s- plays da ta b ase d on th e comput er as so ciated w ith the d epl oyed dat a-t ...

  • Microsoft SQL Server 2008 R2 - page 124

    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. T he Polic y Det ails t ab c an als o be us ed to cr eate a cus tom p olic y tha t overr ide s the de fault ...

  • Microsoft SQL Server 2008 R2 - page 125

    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: ● Data - Ti er Ap plica tion CPU Utiliz ation Polici es ● File Space U tiliz ation Policie s ● Compute r CPU Utiliza tion Policie s ● Storage Volu ...

  • Microsoft SQL Server 2008 R2 - page 126

    ...

  • Microsoft SQL Server 2008 R2 - page 127

    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 Event Pr oces sin g with StreamIns ight 145 CHA P TER 9 Rep or t ing Se r v ices Enh ance ment s 165 CHA P TER 10 S elf-Ser vice An aly sis with Powe ...

  • Microsoft SQL Server 2008 R2 - page 128

    ...

  • Microsoft SQL Server 2008 R2 - page 129

    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 . In the m onths f ollowin g the a cquisitio n, Micr osof t re - vam ...

  • Microsoft SQL Server 2008 R2 - page 130

    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 . Parallel D ata ...

  • Microsoft SQL Server 2008 R2 - page 131

    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 Active ser ver Dedicated storage P assive ser ver Data loading Data backup Dual Fibre Channel Dual InfiniBand SQL SQL SQL SQL SQL SQL SQL SQL FIG UR E ...

  • Microsoft SQL Server 2008 R2 - page 132

    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. I t contain s seve ral t y pe s of nod es tha t Parallel D ata Wareh ous e use s ...

  • Microsoft SQL Server 2008 R2 - page 133

    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 . Th e Nex us client is t he que r y e ditor th at you ca n use to sub mit quer ies by u sing SQ L st atem ent s to Paralle l Dat a Warehou se ...

  • Microsoft SQL Server 2008 R2 - page 134

    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. Using your E TL pr oce ss es to m ove data to th e L andin g Z ...

  • Microsoft SQL Server 2008 R2 - page 135

    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. Thi s feat ure, c alle d data colo cation , en sure s that e ach compu te nod e can e xecute i ts p or t ion of ...

  • Microsoft SQL Server 2008 R2 - page 136

    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. Wh en pla nning which st rate gy to impl eme nt, yo u consi der th e t yp es of jo ins that th e par alle ...

  • Microsoft SQL Server 2008 R2 - page 137

    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 . For a det aile d discu ssio n of the choic e of distr ibute d t able s, r efe r to the pr odu c t do ...

  • Microsoft SQL Server 2008 R2 - page 138

    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 . Parall el Dat a Ware ...

  • Microsoft SQL Server 2008 R2 - page 139

    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 . T o cr ea te a n onc lu ste r ed in de x , you u se CR E A TE INDE X . Her e is an exa mple o ...

  • Microsoft SQL Server 2008 R2 - page 140

    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. Her e is an exa mple of th e sy nta x for a CRE A TE T ABL E AS SELEC ...

  • Microsoft SQL Server 2008 R2 - page 141

    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 . In gene ral , Paralle l Data Ware hous e’s control no de foll ow ...

  • Microsoft SQL Server 2008 R2 - page 142

    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. T o loa d ...

  • Microsoft SQL Server 2008 R2 - page 143

    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 . In tegrat ion Ser vices Integra tion Se r ...

  • Microsoft SQL Server 2008 R2 - page 144

    ...

  • Microsoft SQL Server 2008 R2 - page 145

    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 . J ust a s SQL S er ver Rep or tin g Se r v ices (SSRS ) is an ex tensib le re ...

  • Microsoft SQL Server 2008 R2 - page 146

    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. M ore over, even whe n a use ...

  • Microsoft SQL Server 2008 R2 - page 147

    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 . A s da ta s tewar ds , they h ave ...

  • Microsoft SQL Server 2008 R2 - page 148

    128 CHA PT ER 7 Mas te r Da ta S er vi ce s Master Da ta Ser vices Co ngu 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 Congurati on Mana ger. This congurati on too l include s pa ges to cr eate th e MDS dat abas e, congur e the s ys tem s et tin gs for all Web s e ...

  • Microsoft SQL Server 2008 R2 - page 149

    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 w er ror s ar ising f ro m the imp or t pr oce ss , an d create s ubscr iption v iew s for con sumptio n of mas ter da ta by ope ration al and a naly ...

  • Microsoft SQL Server 2008 R2 - page 150

    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. Th at is , the Pro duc tS ubCa tego r y co de s are at tr ibu ...

  • Microsoft SQL Server 2008 R2 - page 151

    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 . Whe n you ad d a leaf memb er, you initially pr ovid e value s for on ly the ...

  • Microsoft SQL Server 2008 R2 - page 152

    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 . T o achie ve this go al, the s y ste m mus t be abl e to re cogniz ...

  • Microsoft SQL Server 2008 R2 - page 153

    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 Value Se ts th e defa ult valu e of an at trib ute to bl ank , a sp ecic v alue that you suppl y in the b usine ss r ule, a g ene rate d value th ...

  • Microsoft SQL Server 2008 R2 - page 154

    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 er dat a valu e bef ore an d af te r the chan ge, th e use r who m ade th e change (not show n), the date and time of th e chang e, and o ther id entif ...

  • Microsoft SQL Server 2008 R2 - page 155

    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. Howe ver, the impor t a nd exp or t proc ess es h ere a re noth ing like ...

  • Microsoft SQL Server 2008 R2 - page 156

    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. FIG UR E 7- 11 Th e st a ging b atch l og If the lo g indic ates any e rro r s for th e st agin g ...

  • Microsoft SQL Server 2008 R2 - page 157

    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. FIG UR E 7- 12 Quer yin g the Pr od uc t sub scr ipt ion v iew Admi nistra tion Of course , Master D ...

  • Microsoft SQL Server 2008 R2 - page 158

    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 . FIG UR E 7- 13 Mo de l ver s ion s Securi t y MDS use s a rol e-b as ed au thor iza tion s ys tem th at allow s you to cong ure se curi t y bot h by func ti ona ...

  • Microsoft SQL Server 2008 R2 - page 159

    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. Wh en ...

  • Microsoft SQL Server 2008 R2 - page 160

    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. T he r st t wo but ton s above th e tab le allow a u ser w ith Up date pe rmis sio ns to add or d ele te a mem ber, but thos e but ton s are un availabl e her e be ca use th e ...

  • Microsoft SQL Server 2008 R2 - page 161

    Admin is tra tio n CH AP TER 7 141 More s pe cicall y, the secur it y cong 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. Le t ’s look r s t at the ef fec t of th ese p er miss ions o n the us er ’s expe ...

  • Microsoft SQL Server 2008 R2 - page 162

    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 . In this way, you can m ...

  • Microsoft SQL Server 2008 R2 - page 163

    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 . Ma ste rDat a Se r v ice s ...

  • Microsoft SQL Server 2008 R2 - page 164

    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 .Re ge xRep la c ...

  • Microsoft SQL Server 2008 R2 - page 165

    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. It ship s with th e Standa rd, E ...

  • Microsoft SQL Server 2008 R2 - page 166

    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 . For e xampl e, compani es se lling pr odu c ts on line of t en us e clicks tr ...

  • Microsoft SQL Server 2008 R2 - page 167

    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 . More sp eci - call y , a stre am is a coll ec tion of d ata th at chang es over t ime. Fo r examp le, a Web lo g cont ains data a bou t each se r ver hit , includin g the date , time, p age r e ...

  • Microsoft SQL Server 2008 R2 - page 168

    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 Event Event CEP Engine S tanding Queries Event Event Event Event Event Output Adapters CEP Application at Run Time S tatic reference data Event Source ...

  • Microsoft SQL Server 2008 R2 - page 169

    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. The de velop ment p ro ces s for an o utput a dapte r is ver y ...

  • Microsoft SQL Server 2008 R2 - page 170

    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 . Fir st , you c an use o nly th e explici t ser ver de velop ment mod el (which is de scr ibe d in the ne x t se c tion of thi s cha ...

  • Microsoft SQL Server 2008 R2 - page 171

    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 congurat ion sp ecic ation w hen th e adap ter is b ound to t he que r y. Conver se ly, an unty pe d outp ut adap ter r ecei ves th e event t ype d escr iption , ...

  • Microsoft SQL Server 2008 R2 - page 172

    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 cica tion to re tri eve quer y pr oce ssin g res ults f rom a d equ eue d eve nt. The ne x t s tep is ...

  • Microsoft SQL Server 2008 R2 - page 173

    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 Resum e( ) meth od, a nd eit her a Pr oduce Event s( ) or Consume Event s( ) metho d, de pe nding o n whe ther yo u are d evelo ping an in put ad apte ...

  • Microsoft SQL Server 2008 R2 - page 174

    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). The ad apter adds e vent s with th e INSERT eve nt kin d to the s trea m as it r e ...

  • Microsoft SQL Server 2008 R2 - page 175

    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 . Y o u use L INQ exp re ssio ns to de ne the  elds f or ou tput eve nts , to lte r event s bef ore qu er y proc ess ing, to gr oup eve nts into s ubse ts , an d to ...

  • Microsoft SQL Server 2008 R2 - page 176

    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. Af ter you create a s tre am of win dows , you c an pe r fo rm ag gre gation , T op K (a LINQ oper ation de scr ibe d later in thi s chapter), or u ser -d ene d op erat ...

  • Microsoft SQL Server 2008 R2 - page 177

    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 . In a h o ...

  • Microsoft SQL Server 2008 R2 - page 178

    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. var outputStream = from ...

  • Microsoft SQL Server 2008 R2 - page 179

    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. For exam ple, a ssum e n is 2 as s hown in Fi gure 8-5. Th ...

  • Microsoft SQL Server 2008 R2 - page 180

    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 . Th e follow ing exa mple sh ows yo u how to us e thes e ag gre gation s as well a s the Count a ggre ga- tion fo r each sna pshot w indow : var outputStream = from eventWindow in inp ...

  • Microsoft SQL Server 2008 R2 - page 181

    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. T he ou tput of a join o per ation i s a ne ...

  • Microsoft SQL Server 2008 R2 - page 182

    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 . If you are u sing th e explici t ser ver de vel- opme nt mod el, yo u creat e a quer y bin ...

  • Microsoft SQL Server 2008 R2 - page 183

    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. W ith th at obje c t ins tantia ted , you ca n use th e Star t( ) and Stop( ) me thod s. T he Star t( ) m etho d ins ta nti- ates th e adapte r s usin g the ada pter f ac tori es , st ar ts ...

  • Microsoft SQL Server 2008 R2 - page 184

    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. Be for e you ca n use a W indow s Pow- er Shell s cript , the Stre amIns ight ...

  • Microsoft SQL Server 2008 R2 - page 185

    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. Th e Re ...

  • Microsoft SQL Server 2008 R2 - page 186

    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 . Y ...

  • Microsoft SQL Server 2008 R2 - page 187

    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. Re por ...

  • Microsoft SQL Server 2008 R2 - page 188

    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. T his f unc tion a ccept s a singl e value f rom th e so urce dat as et as input an d ret urns a n array of m atching val ues f rom th e des tinati on da ...

  • Microsoft SQL Server 2008 R2 - page 189

    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.Value,"EnglishMonthName")) Con di tional R enderi ng E xpressio ns The ex pre ssi on lang uage in SQ L Se r ve r 20 ...

  • Microsoft SQL Server 2008 R2 - page 190

    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 . For exam ple, l et ’s say you have a r ep or t th at display s sum- mari zed s ale s but al so allow s the us er to to ggle a r ep or t it em ...

  • Microsoft SQL Server 2008 R2 - page 191

    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. Fo r examp le, ass ume that yo u have se t up the re por t to inse r t a p age br eak b et we en gr oup ins t ance s, an d you want to up date the exe cutio n time wh en the gr ...

  • Microsoft SQL Server 2008 R2 - page 192

    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. The se pr op er t ies ap pear in t he Prop er ties win dow wh en you s el ec t a t ablix , re ct an gle, or ch ar t in th e rep or t b od y or ...

  • Microsoft SQL Server 2008 R2 - page 193

    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 . Y o u might , for ex ample , use th e group e xpr es sion tha t dene s the p age br eak a s the Page Name p ...

  • Microsoft SQL Server 2008 R2 - page 194

    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 . Altho u ...

  • Microsoft SQL Server 2008 R2 - page 195

    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. Data Bars A d ...

  • Microsoft SQL Server 2008 R2 - page 196

    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. Y ou c an ...

  • Microsoft SQL Server 2008 R2 - page 197

    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. You then den e the r ules that de termin e which in dicato r prop er ly r epr ese n ...

  • Microsoft SQL Server 2008 R2 - page 198

    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 g a map fr om the T oolb ox window t o the re por t bo dy ( if you are u sing Bu sines s Inte lligen ce Deve lop ment Studio) or click the map in th ...

  • Microsoft SQL Server 2008 R2 - page 199

    Reu s abi lit y CHAP TE R 9 1 79 Shared Datasets A share d dat as et allo ws you to d ene 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 . Havin g shar ed dat a - set s availab le on th e ser ver al so he lps SQL S er ver 20 0 8 R2 Re po r t Buil de ...

  • Microsoft SQL Server 2008 R2 - page 200

    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. Per io dic all y, the c ac he e xp ire s and th e ne x t pe r s on t hat re qu es t s t he r ...

  • Microsoft SQL Server 2008 R2 - page 201

    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 . In the Publi sh Rep or t Par ts dial og box , sh own in Figur e 9 - 17 , you sel ...

  • Microsoft SQL Server 2008 R2 - page 202

    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 .0 ca n be fo und late r in this chapt er in the “ Rep or ...

  • Microsoft SQL Server 2008 R2 - page 203

    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 . Rep or t Builder 2. 0 was ...

  • Microsoft SQL Server 2008 R2 - page 204

    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 en et f rom a fe w enh ancem ent s that improve a cces s to rep or ts an d to mana gem ent op erati ons in Re por t Mana ger, in addition to an addi ...

  • Microsoft SQL Server 2008 R2 - page 205

    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 . Rather t han re quirin g you to op en a r epo r t r s t and th en nav igate to th e pro per ties p age s, Repo r t Man age r give s you dire c t acce ss to ...

  • Microsoft SQL Server 2008 R2 - page 206

    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). If you ar e familiar w ith earli er ver si ons of Re por ting Se r v ices ...

  • Microsoft SQL Server 2008 R2 - page 207

    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. In this re le as e , yo u n d b et ter o pti on s f or c on gur ing Sha re Point 2010 for us e w ith Rep o r ti ng Se r v ic es , wor ki ng wit h s cri pt s to aut ...

  • Microsoft SQL Server 2008 R2 - page 208

    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 ia l s f or au th e nt ic at io n , yo u m us t s up pl y a c on n e c ti on s tr i ng to t he si te o r s ub si te in th e f o rm of a UR L that re ...

  • Microsoft SQL Server 2008 R2 - page 209

    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 . The r ...

  • Microsoft SQL Server 2008 R2 - page 210

    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 . Af ter in st alling th e add-in , you c an impor t dat a fro m ex te rnal d ata s ource s and i ...

  • Microsoft SQL Server 2008 R2 - page 211

    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 . The s tr uc ture r ema ins the s ame e ach time yo u re ...

  • Microsoft SQL Server 2008 R2 - page 212

    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 .cs v) • File s fr om E xcel 97 thro ugh E xcel 2010 • Power Pivot wor kb ook s p ublish ed to a ...

  • Microsoft SQL Server 2008 R2 - page 213

    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. Y ou c an ? ...

  • Microsoft SQL Server 2008 R2 - page 214

    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. Rel ations hips en able yo u to use r elate d dat a in the s ame Pi votT ab le even though t he unde rl ying d ata a c tuall y comes f ro m dif f ...

  • Microsoft SQL Server 2008 R2 - page 215

    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. A lth ou gh a lt er e f fe c tiv el y hid es dat a f rom a p re s ent a tion , a nyone w ho c an o pe n th e E xce l wor kb oo k ca n al so c le ar t he lte r s an d vi ew th e ...

  • Microsoft SQL Server 2008 R2 - page 216

    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. For exa mple , you mig ht choose t o dis play a co lum n in t he Power Piv ...

  • Microsoft SQL Server 2008 R2 - page 217

    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 ld e r a n d t he n us e th e as s o ci ate d  el d li s t t o s e l ec t a nd arra nge e lds f or the s ele c ted o bje c t, a s shown in F ig ...

  • Microsoft SQL Server 2008 R2 - page 218

    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. Slicer s ar e a new f eatur e in E xcel 2010 that can be as so ciated w ith ...

  • Microsoft SQL Server 2008 R2 - page 219

    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 create e ven mo re p ower f ul re po r t s by enr iching the Powe rPivot d ata w ith Data A naly sis E xp ressio ns (DA X ) to a dd cus tom ag gre gati ...

  • Microsoft SQL Server 2008 R2 - page 220

    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. FIG UR E 10 - 11 Mea sur e se t tin gs D A X Functi ons The ex ample s show n for a c alculate d column a nd a me asur e are v ...

  • Microsoft SQL Server 2008 R2 - page 221

    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 the se cond ar gument f or ea ch row of the t able in th e r st ar gume nt, an d then c alculat es the a rithm etic m ean T ex t =CONCATENATE([First ...

  • Microsoft SQL Server 2008 R2 - page 222

    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 P owerPivot System Service Excel W eb Access Excel W eb Ser vice P owerPivot Web Service Excel 2010 with P owerPivot - View or create r eports Browser ...

  • Microsoft SQL Server 2008 R2 - page 223

    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 ges th e conne c tion s for ac ti ve, r eus abl e, and c ache d conne c tion s to PowerPi vot work bo ok s , as we ll as admini str ative con nec ti ...

  • Microsoft SQL Server 2008 R2 - page 224

    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. If th ey wer en’t , you woul d have to maint ain se para te les in dif fer ent for mat s and th en ma nually int eg ...

  • Microsoft SQL Server 2008 R2 - page 225

    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. Y o u can th en dow nloa d a docum ent , che ck docume nts in o r out , or p er f ...

  • Microsoft SQL Server 2008 R2 - page 226

    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 . For exa mpl e ...

  • Microsoft SQL Server 2008 R2 - page 227

    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 Ana ly sis S er vic es e ngin e, 1 23, 19 0 anno ta tin g tran sa c tio ns in M DS , 13 4 appl ic atio n er ro rs , m oni tor ing , 122 appl ic atio ns ...

  • Microsoft SQL Server 2008 R2 - page 228

    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. Se e al ...

  • Microsoft SQL Server 2008 R2 - page 229

    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 tion m on itor ing , 9 6 lte rin g op er atio n, 155 lte rin g Powe rPi vot da ta , 192, 194 for mat t ing r ep or ts d yna mic all y, 1 69- 17 0 ...

  • Microsoft SQL Server 2008 R2 - page 230

    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 . Se e Be st Pr ac ti ces A nal y ze r (BPA); SQL S er ver U tilit y Mana ge me ntS er vi ce API , 163 m ...

  • Microsoft SQL Server 2008 R2 - page 231

    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 s , 118- 120 dat a lo ad pr oc es sin g, 1 21 - 122 dat a t y pe s supp or ted i n, 1 20 DDL e x te nsi ons , 117 dis tri bute d s tra te gy, 11 6-11 ...

  • Microsoft SQL Server 2008 R2 - page 232

    212 re por ts alte rn ate a cce ss ma ppi ngs , 187 ca che co ngur 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 edi t se ss ion s, 18 3 exp or ting to Ato m dat a fe e d, 182 layou t , dy nami c, 169 - 170, 1 72- 173 namin g pa ge s in , 173 nes tin g it ems i n, ...

  • Microsoft SQL Server 2008 R2 - page 233

    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 ati on , 118 T r ans ac t-SQL f unc ti ons , 143- 14 4 tro ubl esh oo ting f ail over cl us ter in g, 70 tumb ling w ind ow s, 157- 158 U UCPs ( Util it ...

  • Microsoft SQL Server 2008 R2 - page 234

    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 ace ut iliz at ion m oni tor ing , 97 W Web br ows er s upp or t f or Re po r ti ng Se r vice s , 186 Web edi tio n, 13 win dow s, e ven t, 156 -1 59 Wi ...

  • Microsoft SQL Server 2008 R2 - page 235

    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 eng 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 . His cor e spe cialt y is M icros of t SQ L Ser ver ...

  • Microsoft SQL Server 2008 R2 - page 236

    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 h i s b o o k ? We want to hear fr om you! T o participate in a brief online sur vey , please visit: T ell us how well this book meets your needs —w ...

Manufacturer Microsoft Category Application server software

Documents that we receive from a manufacturer of a Microsoft SQL Server 2008 R2 can be divided into several groups. They are, among others:
- Microsoft technical drawings
- SQL Server 2008 R2 manuals
- Microsoft product data sheets
- information booklets
- or energy labels Microsoft SQL Server 2008 R2
All of them are important, but the most important information from the point of view of use of the device are in the user manual Microsoft SQL Server 2008 R2.

A group of documents referred to as user manuals is also divided into more specific types, such as: Installation manuals Microsoft SQL Server 2008 R2, service manual, brief instructions and user manuals Microsoft SQL Server 2008 R2. Depending on your needs, you should look for the document you need. In our website you can view the most popular manual of the product Microsoft SQL Server 2008 R2.

A complete manual for the device Microsoft SQL Server 2008 R2, how should it look like?
A manual, also referred to as a user manual, or simply "instructions" is a technical document designed to assist in the use Microsoft SQL Server 2008 R2 by users. Manuals are usually written by a technical writer, but in a language understandable to all users of Microsoft SQL Server 2008 R2.

A complete Microsoft manual, should contain several basic components. Some of them are less important, such as: cover / title page or copyright page. However, the remaining part should provide us with information that is important from the point of view of the user.

1. Preface and tips on how to use the manual Microsoft SQL Server 2008 R2 - At the beginning of each manual we should find clues about how to use the guidelines. It should include information about the location of the Contents of the Microsoft SQL Server 2008 R2, FAQ or common problems, i.e. places that are most often searched by users in each manual
2. Contents - index of all tips concerning the Microsoft SQL Server 2008 R2, that we can find in the current document
3. Tips how to use the basic functions of the device Microsoft SQL Server 2008 R2 - which should help us in our first steps of using Microsoft SQL Server 2008 R2
4. Troubleshooting - systematic sequence of activities that will help us diagnose and subsequently solve the most important problems with Microsoft SQL Server 2008 R2
5. FAQ - Frequently Asked Questions
6. Contact detailsInformation about where to look for contact to the manufacturer/service of Microsoft SQL Server 2008 R2 in a specific country, if it was not possible to solve the problem on our own.

Do you have a question concerning Microsoft SQL Server 2008 R2?

Use the form below

If you did not solve your problem by using a manual Microsoft SQL Server 2008 R2, ask a question using the form below. If a user had a similar problem with Microsoft SQL Server 2008 R2 it is likely that he will want to share the way to solve it.

Copy the text from the picture

Comments (0)