ページに移動 of 236
Contents Contents at a Glance v Cont ents vii Acknowledgments xv I n t r o d u ct i o n x v i i Who I s This Bo ok Fo r? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . x v ii How Is Th is Bo ok Or ganize d? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 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 cic pr ere quisi tes an d re quire ment s: ■ Th e SQL S er ver ver sion r unnin g the UCP mus t be S QL Se r ve r 20 08 R 2 or high er. ( 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 ngure 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 Cong 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 Conr mation p age , rev iew th e det ails f or ea ch tes t, an d the n click Nex t to be gin the valid ation pr oce ss . 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 ien 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 nrm the s el ec tio n, an d then click Ne x t . 14. T he wi zar d congur 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 , congur 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 ic m an ag ed in s ta nc e, s el ec t th e O ver - rid e Th e Glo ba l Polic y optio n but t on . 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 ngu ratio n Manager Befo re you c an s tar t using M DS to mana ge your ma ste r dat a, yo u use Ma ste r Dat a Ser vice s Congurati on Mana ger. This congurati on too l include s pa ges to cr eate th e MDS dat abas e, congur 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 ecic 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 cong 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 cicall y, the secur it y cong uration a llows thi s use r to edit o nly th e Bikes and Ac - ces sor ies c ate gori es in the Re t ail group, bu t the us er c annot e dit c atego rie s in the W hole sal e group. 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 congurat ion sp ecic 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 cica 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 ene 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 dene 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 den 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 ene a qu er y once fo r reu se in many r ep or t s , much as yo u can cre ate a sha re d data so urce to de ne a re us able co nne ct ion s tring . 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 et 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 ngur in g, 179- 18 0 on DAC depl oym en t, 5 4 dat a s ync hro niz ati on , 173 dat a vi sual iz atio n en han cem ent s , 175- 17 8 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 eng s, ar chite ct ural design s es sion s, a nd pr oof of con cept wo rk sh ops to or ganiz atio ns loc ate d in the Silicon Valley . 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 ...
Microsoft SQL Server 2008 R2のメーカーから受け取ることができるドキュメントは、いくつかのグループに分けられます。その一部は次の通りです:
- #BRANDの図面#
- SQL Server 2008 R2の取扱説明書
- Microsoftの製品カード
- パンフレット
- またはMicrosoft SQL Server 2008 R2の消費電力シール
それらは全部重要ですが、デバイス使用の観点から最も重要な情報は、Microsoft SQL Server 2008 R2の取扱説明書に含まれています。
取扱説明書と呼ばれる文書のグループは、Microsoft SQL Server 2008 R2の取り付け説明書、サービスマニュアル、簡易説明書、またはMicrosoft SQL Server 2008 R2のユーザーマニュアル等、より具体的なカテゴリーに分類されます。ご必要に応じてドキュメントを検索しましょう。私たちのウェブサイトでは、Microsoft SQL Server 2008 R2の製品を使用するにあたって最も人気のある説明書を閲覧できます。
Microsoft SQL Server 2008 R2デバイスの取扱説明書はどのようなものですか？
取扱説明書は、ユーザーマニュアル又は単に「マニュアル」とも呼ばれ、ユーザーがMicrosoft SQL Server 2008 R2を使用するのを助ける技術的文書のことです。説明書は通常、全てのMicrosoft SQL Server 2008 R2ユーザーが容易に理解できる文章にて書かれており、その作成者はその分野の専門家です。
Microsoftの取扱説明書には、基本的な要素が記載されているはずです。その一部は、カバー/タイトルページ、著作権ページ等、比較的重要度の低いものです。ですが、その他の部分には、ユーザーにとって重要な情報が記載されているはずです。
1. Microsoft SQL Server 2008 R2の説明書の概要と使用方法。説明書にはまず、その閲覧方法に関する手引きが書かれているはずです。そこにははMicrosoft SQL Server 2008 R2の目次に関する情報やよくある質問、最も一般的な問題に関する情報を見つけられるはずです。つまり、それらはユーザーが取扱説明書に最も期待する情報なのです。
2. 目次。Microsoft SQL Server 2008 R2に関してこのドキュメントで見つけることができる全てのヒントの目次
3. Microsoft SQL Server 2008 R2デバイスの基本機能を使うにあたってのヒント。 Microsoft SQL Server 2008 R2のユーザーが使い始めるのを助けてくれるはずです。
4. トラブルシューティング。Microsoft SQL Server 2008 R2に関する最も重要な問題を診断し、解決するために役立つ体系化された手続き
5. FAQ。よくある質問
6. 連絡先。一人では問題を解決できない場合に、その国におけるMicrosoft SQL Server 2008 R2のメーカー/サービスへの連絡先に関する情報。
Microsoft SQL Server 2008 R2についてご質問がありますか？
次のフォームを使用してください
見つけた説明書を読んでもMicrosoft SQL Server 2008 R2の問題を解決できない場合、下記のフォームを使用して質問をしましょう。ユーザーのどなたかがMicrosoft SQL Server 2008 R2で同様の問題を抱えていた場合、その解決方法を共有したいと考えるかもしれません。