Microsoft SQL Server 2008 R2 manuale d’uso

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

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

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

[...]

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

[...]

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

viii Con ten ts Creatin g a UCP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 Creatin g a UCP by Using SSM S 26 Creatin g a UCP by Using W indow s Power She ll 28 UCP Post-Inst allati on Steps 29 Enrollin g SQL Se r ve r Ins ta nces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . [...]

Con ten ts ix CHAPTER 4 High Availability a nd Vir t ualiz ation E nhan cem ents 63 Enhance ment s to High Availab ilit y wi th Win dows S er ver 20 0 8 R2 . . . . . . 63 Failover Cluste rin g with W indow s Se r ve r 20 08 R 2 . . . . . . . . . . . . . . . . . . . . 6 4 T radi tional Failove r Cluste rin g 65 Gue st Failover Clus ter ing 67 Enhanc[...]

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

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

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

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

[...]

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

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

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

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

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

[...]

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

[...]

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

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

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

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

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

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

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

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

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

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 ,[...]

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

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

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

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

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

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

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

[...]

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

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

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

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

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

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

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

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

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

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

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

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

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

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?[...]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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 . ■[...]

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

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

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

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

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

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

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

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

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

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,[...]

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

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

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 ?[...]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

[...]

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

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

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

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

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

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. ■[...]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

[...]

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

[...]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

[...]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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 , [...]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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 ?[...]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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, [...]

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

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

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

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