Microsoft SQL Server 2008 R2 Bedienungsanleitung

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236

Zur Seite of

Richtige Gebrauchsanleitung

Die Vorschriften verpflichten den Verkäufer zur Übertragung der Gebrauchsanleitung Microsoft SQL Server 2008 R2 an den Erwerber, zusammen mit der Ware. Eine fehlende Anleitung oder falsche Informationen, die dem Verbraucher übertragen werden, bilden eine Grundlage für eine Reklamation aufgrund Unstimmigkeit des Geräts mit dem Vertrag. Rechtsmäßig lässt man das Anfügen einer Gebrauchsanleitung in anderer Form als Papierform zu, was letztens sehr oft genutzt wird, indem man eine grafische oder elektronische Anleitung von Microsoft SQL Server 2008 R2, sowie Anleitungsvideos für Nutzer beifügt. Die Bedingung ist, dass ihre Form leserlich und verständlich ist.

Was ist eine Gebrauchsanleitung?

Das Wort kommt vom lateinischen „instructio”, d.h. ordnen. Demnach kann man in der Anleitung Microsoft SQL Server 2008 R2 die Beschreibung der Etappen der Vorgehensweisen finden. Das Ziel der Anleitung ist die Belehrung, Vereinfachung des Starts, der Nutzung des Geräts oder auch der Ausführung bestimmter Tätigkeiten. Die Anleitung ist eine Sammlung von Informationen über ein Gegenstand/eine Dienstleistung, ein Hinweis.

Leider widmen nicht viele Nutzer ihre Zeit der Gebrauchsanleitung Microsoft SQL Server 2008 R2. Eine gute Gebrauchsanleitung erlaubt nicht nur eine Reihe zusätzlicher Funktionen des gekauften Geräts kennenzulernen, sondern hilft dabei viele Fehler zu vermeiden.

Was sollte also eine ideale Gebrauchsanleitung beinhalten?

Die Gebrauchsanleitung Microsoft SQL Server 2008 R2 sollte vor allem folgendes enthalten:
- Informationen über technische Daten des Geräts Microsoft SQL Server 2008 R2
- Den Namen des Produzenten und das Produktionsjahr des Geräts Microsoft SQL Server 2008 R2
- Grundsätze der Bedienung, Regulierung und Wartung des Geräts Microsoft SQL Server 2008 R2
- Sicherheitszeichen und Zertifikate, die die Übereinstimmung mit entsprechenden Normen bestätigen

Warum lesen wir keine Gebrauchsanleitungen?

Der Grund dafür ist die fehlende Zeit und die Sicherheit, was die bestimmten Funktionen der gekauften Geräte angeht. Leider ist das Anschließen und Starten von Microsoft SQL Server 2008 R2 zu wenig. Eine Anleitung beinhaltet eine Reihe von Hinweisen bezüglich bestimmter Funktionen, Sicherheitsgrundsätze, Wartungsarten (sogar das, welche Mittel man benutzen sollte), eventueller Fehler von Microsoft SQL Server 2008 R2 und Lösungsarten für Probleme, die während der Nutzung auftreten könnten. Immerhin kann man in der Gebrauchsanleitung die Kontaktnummer zum Service Microsoft finden, wenn die vorgeschlagenen Lösungen nicht wirksam sind. Aktuell erfreuen sich Anleitungen in Form von interessanten Animationen oder Videoanleitungen an Popularität, die den Nutzer besser ansprechen als eine Broschüre. Diese Art von Anleitung gibt garantiert, dass der Nutzer sich das ganze Video anschaut, ohne die spezifizierten und komplizierten technischen Beschreibungen von Microsoft SQL Server 2008 R2 zu überspringen, wie es bei der Papierform passiert.

Warum sollte man Gebrauchsanleitungen lesen?

In der Gebrauchsanleitung finden wir vor allem die Antwort über den Bau sowie die Möglichkeiten des Geräts Microsoft SQL Server 2008 R2, über die Nutzung bestimmter Accessoires und eine Reihe von Informationen, die erlauben, jegliche Funktionen und Bequemlichkeiten zu nutzen.

Nach dem gelungenen Kauf des Geräts, sollte man einige Zeit für das Kennenlernen jedes Teils der Anleitung von Microsoft SQL Server 2008 R2 widmen. Aktuell sind sie genau vorbereitet oder übersetzt, damit sie nicht nur verständlich für die Nutzer sind, aber auch ihre grundliegende Hilfs-Informations-Funktion erfüllen.

Inhaltsverzeichnis der Gebrauchsanleitungen

  • Seite 1

    Contents Contents at a Glance v Cont ents vii Acknowledgments xv I n t r o d u ct i o n x v i i Who I s This Bo ok Fo r? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . x v ii How Is Th is Bo ok Or ganize d? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . x v ii Pre- Rel eas e Sof t wa[...]

  • Seite 2

    PUBL IS HED B Y Microso f t Press A Di v isio n o f Microso f t Corporation One Micros oft W ay Redmond, W ashing ton 9 805 2- 63 99 Copyright © 20 1 0 b y Microso f t Corporat ion All r ights reser ved. N o par t of the contents o f t his book may be reproduced or tr ansm itted i n any fo r m or by any means without the wr itte n perm issi on of [...]

  • Seite 3

    I dedic ate this book to m y wife and child ren, who mak e it all worthwhile. — R oss M istRy I dedic ate this book to m y husband an d best friend, Gerr y . —s t acia M isne R[...]

  • Seite 4

    [...]

  • Seite 5

    Contents at a Glance Introduc tion x vii P ART I DA T ABAS E ADMI NI STR A TION CHA P TER 1 SQL Se r ver 20 08 R 2 Editio ns an d Enh an cem ent s 3 CHA P TER 2 Multi-Se r ve r Adminis tra tion 21 CHA P TER 3 Dat a- Tie r App lica tion s 4 1 CHA P TER 4 High Availa bilit y and V ir tu ali za tion E nha nce men ts 63 CHA P TER 5 Cons olid ation a nd[...]

  • Seite 6

    [...]

  • Seite 7

    Wha t do you t hink of th is bo ok? We want to h ea r fro m you! Micr os of t is int er es te d in hea rin g your fe edb ac k so we ca n cont inua lly im pro ve our boo k s and le ar nin g res our ce s for yo u. T o p ar ti cip ate in a bri ef onli ne sur vey, plea se vi sit : micr os of t .com /l ea rnin g/ bo ok sur vey vi i Cont ents Intr oduc t[...]

  • Seite 8

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

  • Seite 9

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

  • Seite 10

    x Conte nt s P ART II BUS INE SS INTELLIGE NC E DEVE LOPMENT CHAPTER 6 Scal able Dat a Warehousin g 109 Parallel D ata Wareh ous e Ar chitec tur e . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109 Data Ware hous e App liance s 109 Proce ssin g Ar chitec t ure 110 The Mult i-Rack Sys tem 110 Hub-an d-Spoke Archi tec tur e 115 Data M anag[...]

  • Seite 11

    Con ten ts xi Integra tion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135 Impor ting Ma ste r Dat a 135 E xpo r ting M as ter Dat a 136 Administ ration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137 V er sion s 137 Sec urit y 138 [...]

  • Seite 12

    xii Co nte nt s CHAPTER 9 Repor ti ng Se r vic es Enh ance ment s 1 65 New Dat a So urce s . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165 E xpre ss ion L ang uage Imp rovem ent s. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165 Combinin g Data f rom M ore T han On e Dat ase t 1 6 6 [...]

  • Seite 13

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

  • Seite 14

    [...]

  • Seite 15

    x v Acknowledgments I would like to r st a cknow le dge Shir mat tie S ee narin e for a ssis ting m e on this title. I co uldn't have wr it te n this bo ok wi thou t your as sis tan ce in such a sho r t timef rame w ith eve r y thing el se goin g on in my life . Y our h ard wo rk , cont ribut ions , edit s , and p er se veran ce are m uch a[...]

  • Seite 16

    x v i And la st b ut not l eas t , I would like to thank my Micr osof t mento r s who a ssis te d with my ca re er de velop ment an d tran sition to t he Micro sof t T echno log y Cente r in Silicon Valley: Kell y Olive r , Alex V ier a, B uck Woody, Kevin T sai , and B ur zin Patel. — R oss Mist r y T he chapte r s of Par t II cover ing th e BI [...]

  • Seite 17

    x vi i I n t ro d u ct i o n O ur purp ose in I ntro ducing Microso f t SQL S er ver 2 0 0 8 R 2 is to po int out bo th the ne w and the im prove d in the la tes t ver sion of S QL Se r ve r . B ec aus e this ver s ion is R el ea se 2 ( R2) of SQ L S er ve r 20 0 8 , you might thi nk th e ch an ge s ar e rel ativel y mino r — m ore th an a se r v[...]

  • Seite 18

    x vi ii Intr od uc tio n are impr oved w ith the int rod uc tion of the S QL Se r ve r Utilit y Control Point . Step - by-ste p ins truc ti ons sh ow DBA s how to q uickly d esign ate a SQL S er ver ins ta nce as a Utilit y Co ntrol Point an d enr oll ins ta nces f or centr alize d multi-s er ver mana ge - ment . Chapte r 3, “Da ta-Tier App licat[...]

  • Seite 19

    Intr od uc tio n xix New ” topi c in SQL Se r ve r Bo ok s On line at ht t p:/ /msdn. microsof t.com/en-us /l ibrar y/bb50 0 435(SQL .105).aspx for the mos t up -to-d ate lis t of change s to the pro duc t . Be awar e that you m ight als o notice s ome min or dif f ere nce s bet ween the RTM version of the product and the descriptions and screen [...]

  • Seite 20

    [...]

  • Seite 21

    P A R T I D a t a b a s e A d m i n i s t r a t i o n R oss M istRy CHA P TER 1 SQL S er ver 20 0 8 R2 Editi ons an d Enhance ment s 3 CHA P TER 2 Multi-Se r ve r Adminis tratio n 21 CHA P TER 3 Dat a- T ier A pplic atio ns 41 CHA P TER 4 High Availab ilit y an d Vir tualiz ation Enhance ment s 63 CHA P TER 5 Cons olidati on and M onitor ing 85[...]

  • Seite 22

    [...]

  • Seite 23

    3 C H A P T E R 1 S Q L Se r v e r 2 00 8 R2 E d i t i o n s a n d E n h a n c e me n t s M icroso f t SQL Server 2008 R2 is the most advanced, trusted, a nd scalab le d ata platform released to date. Building on the succ ess of the origi nal SQL Ser ver 2 008 release, SQL Ser ver 2008 R2 has made an impact on organizations worldwi de wi th its gr [...]

  • Seite 24

    4 CHAP TER 1 SQL S er ver 2 00 8 R 2 Edi tion s an d En ha nc em ent s Micros of t ha s mad e major inve stm ent s in the SQL S er ver pr oduc t a s a who le; howeve r , the ne w feat ures a nd br eak th rou gh cap abilitie s that sh ould inte res t DBA s th e mos t are t he adva ncem ent s in appli catio n and mul ti-se r ve r adminis tratio n. T [...]

  • Seite 25

    SQL S er ver 2 00 8 R 2 Enh an ce me nt s fo r DBA s CHAP TE R 1 5 the total cost of ownership of their database envi ronment. The new SQL Ser ver Util ity dashboards also assist with consol idati on effor ts. Figu re 1 - 1 il lustrates SQ L Server Uti lity dashboard and viewpoin ts for provid ing superior insight into resou rce u til ization and p[...]

  • Seite 26

    6 CHAP TER 1 SQL S er ver 2 00 8 R 2 Edi tion s an d En ha nc em ent s FIG UR E 1- 2 Ide nti f y ing co ns oli dati on op po r tu niti es w ith th e SQL S er ver U tili t y da shb oar d an d vie wp oint s ■ Custo miz at io n of ut iliz at io n th re sh old s an d po lic ie s DB A s ca n cust omize t he uti lizati on thresho ld and po li cie s for[...]

  • Seite 27

    SQL S er ver 2 00 8 R 2 Enh an ce me nt s fo r DBA s CHAP TE R 1 7 envi ronment. This can be done at scale, wit h in forma tion on resou rce uti lizati on throug hout the mana ged database envi ronme nt, as a res ult of centra lized visib ili t y. In additi on, a da t a- tier deve l- oper is bui ldi ng a data-t ier a ppl icat ion w ith Visua l S tu[...]

  • Seite 28

    8 CHAP TER 1 SQL S er ver 2 00 8 R 2 Edi tion s an d En ha nc em ent s In the exa mple in Fig ure 1 - 4, a DBA ha s optimiz ed har dwa re re so urces w ithin the e nviro n- ment by m odif ying t he glob al utiliz atio n policie s to me et the n ee ds of th e organ iza tion . For examp le, the gl obal CPU over utiliz atio n policie s of a man age d [...]

  • Seite 29

    SQL S er ver 2 00 8 R 2 Enh an ce me nt s fo r DBA s CHAP TE R 1 9 spoke (control no de an d comput e nod es) architec tur e. Per form ance impr ovem ent s can be at t aine d with Par allel Da ta Wareh ous e’s design appr oach b ec aus e it par tition s larg e tab les ove r seve ral phy sica l nod es , re sulting in e ach no de hav ing it s own C[...]

  • Seite 30

    10 CHAP TER 1 SQL S er ver 2 0 08 R 2 Edi tion s a nd En ha nc em en ts PowerPivo t data a cces s in the fa rm . This ne w appr oach p romis es b et te r integr ation with Shar ePoint w hile als o enh ancing Sha re Point ’s suppor t of Powe rPivot wo rkb oo k s publish ed to Sh are Point. Chap ter 10, “Se lf-Ser v ice Ana lys is with Powe rPivo[...]

  • Seite 31

    S QL S er ver 20 0 8 R 2 Edit ion s CHAP TER 1 11 ■ Hyp er-V impr oveme nt s Building o n the app roval a nd succe ss of th e ori ginal Hyp er-V re leas e, W indow s Se r ve r 20 08 R 2 de liver s s ever al new c apa bilitie s to the Hyp er-V plat f orm to f ur th er impr ove the SQ L Se r ve r vir t ualiz ation e xpe rie nce. Fir s t , availabil[...]

  • Seite 32

    12 CHAP TER 1 SQL S er ver 2 0 08 R 2 Edi tion s a nd En ha nc em en ts Prem iu m Edit ions The pr emium e dition s of SQL S er ver 20 08 R 2 ar e mea nt to mee t the high es t dem ands of larg e-s cal e dat acente r s and dat a war eh ouse s olut ions . Th e t wo edi tions a re ■ Dat ac ent er For the rst time in the histor y of SQL Ser ver ,[...]

  • Seite 33

    S QL S er ver 20 0 8 R 2 Edit ion s CHAP TER 1 13 ■ Sta nda rd SQL S er ver 20 08 R 2 Stan dar d is a compl ete dat a man age ment an d BI plat fo rm that p rov ides m edium -cla ss s olutio ns for s malle r organ iza tions . It do es n ot include all t he be lls and w his tles in clude d in Dat acent er and Ente rpr ise; h owever, it continue s [...]

  • Seite 34

    1 4 CHAP TE R 1 SQL Se r ve r 20 0 8 R2 E diti ons a n d Enh an ce me nt s ■ Comp ac t SQL Ser ver 20 0 8 R 2 Compa ct i s t yp icall y use d to deve lop m obile a nd small desk top applic atio ns. I t is fr ee to us e and i s commo nly r edis trib ute d with em be dde d and mo bile ind ep end ent sof t ware ve ndo r (IS V ) ap plic ation s. NOTE[...]

  • Seite 35

    Har dw ar e an d So f t w ar e Re quir em en ts CHA PT ER 1 15 HAR DWARE COMP ON EN T RE QU IRE ME NT S Disk Spa ce Data bas e Engine: 280 M B Anal ysis S er vice s: 9 0 MB Repo r tin g Ser vice s: 120 MB Integra tion Se r vices : 120 MB Client comp one nts: 850 MB SQL Se r ve r Bo ok s On line: 240 MB T A BL E 1- 2 Sof t w are R equ ire me nt s SO[...]

  • Seite 36

    16 CHAP TER 1 SQL S er ver 2 0 08 R 2 Edi tion s a nd En ha nc em en ts Installation, Upgr ade, and Migra tion Stra tegies Like its p re dece ss or s , SQL S er ver 20 08 R 2 is avail able in b oth 32 -bit an d 6 4 -bit e dition s, b oth of which ca n be ins t alled e ithe r with th e SQL S er ver Ins tall ation W iz ard or t hrou gh a com- mand p [...]

  • Seite 37

    Ins t all ati on , Upgr a de, a nd M igr ati on Str ate gi es CHAP TE R 1 1 7 In-P la ce Upgrade P ros and Cons The in-p lace up grad e str ateg y is usu ally ea sier a nd con sider ed l es s risk y compar ed to t he side -by-side m igratio n str ateg y . Upgra ding is a lso f airly q uick , and a dditio nal har dwar e is not re quire d. Be ca use [...]

  • Seite 38

    18 CHAP TER 1 SQL S er ver 2 0 08 R 2 Edi tion s a nd En ha nc em en ts Si de-b y - Side M ig ratio n The te rm side- by-side migrati on de scrib es th e dep loym ent of a bra nd-ne w SQL S er ver 20 08 R2 ins t ance al ong side a le gac y S QL Se r ve r ins tan ce. Wh en the S QL Se r ve r 20 08 R 2 ins t allation is compl ete, a D BA migrate s da[...]

  • Seite 39

    Ins t all ati on , Upgr a de, a nd M igr ati on Str ate gi es CHAP TE R 1 19 Howeve r , th er e are dis ad vant age s to the si de- by-side s trate gy. Addition al hard ware m ight nee d to be p urcha se d. Ap plica tions mi ght also n ee d to be dir ec te d to the n ew SQL S er ver 20 08 R 2 ins ta nce, an d it might n ot be a b es t prac tic e fo[...]

  • Seite 40

    [...]

  • Seite 41

    21 C H A P T E R 2 M u l t i - S e r v e r A d m i n i s t r a t i o n O ver the ye ar s, an in crea sing num ber of o rgani zat ions h ave turne d to Micro sof t SQL Ser ver be c ause i t emb odie s the Micr osof t Data Plat f orm v isio n to help or gani za - tions m anag e any dat a, at any pl ace, an d at any time. T he big ges t challe nge s o[...]

  • Seite 42

    22 CHAP TER 2 Mult i-Se r ve r Adm inis tr ati on SQL Se r ve r Utilit y . It f orm s a colle c tion of man age d ins ta nces w ith a re pos itor y for pe r fo r- mance da ta an d mana gem ent p olicies . Af te r dat a is colle c ted f ro m mana ged in st anc es , Utilit y E xplo rer a nd SQL S er ver Utilit y dashb oar d and v iewp oint s in SQL S[...]

  • Seite 43

    The S QL S er ver Ut ilit y CHA PT ER 2 23 R E AL W O R L D M any or ga ni za tio ns th at p ar ticip at e in th e Mic ro so f t SQL S er ver e a rl y ad opt er pr ogr am a re c urr en tl y eit he r eva lu atin g SQL S er ver 2 0 08 R 2 or a lre a dy u sin g it in th eir p ro duc ti on inf ra st ru c tur e. Th e co ns en su s is th at o rg ani z at[...]

  • Seite 44

    24 CHAP TER 2 Mult i-Se r ve r Adm inis tr ati on ■ The Ut ilit y E xpl or er u se r int er fa ce A compo nent of SSM S , this inter face pr ovid es a hiera rchica l tre e vie w for man agin g and contr olling th e SQL S er ver Utilit y . It s us es include co nne c ting to a utilit y , creatin g a UCP , enro lling ins t ance s, de ploy ing dat a[...]

  • Seite 45

    The S QL S er ver Ut ilit y CHA PT ER 2 25 UCP P rere qui sites As w ith oth er SQL S er ver comp one nts an d fea ture s, th e dep loym ent of a SQ L Ser ver UCP mus t mee t the fo llowin g spe cic pr ere quisi tes an d re quire ment s: ■ Th e SQL S er ver ver sion r unnin g the UCP mus t be S QL Se r ve r 20 08 R 2 or high er. ( SQL Ser ver [...]

  • Seite 46

    26 CHAP TER 2 Mult i-Se r ve r Adm inis tr ati on Creating a UCP Th e UCP is r e la ti ve l y e a s y to s et up a nd co n gu r e. Y ou c an d ep l oy it e it he r by us in g th e Create Ut ilit y Contr ol Point Wi zar d in SSMS o r by leve ragin g Win dows Powe rSh ell s cript s. The high -leve l ste ps for cr eatin g a UCP include sp e cif y i[...]

  • Seite 47

    Cre at ing a U CP CH AP TE R 2 27 5 . O n the Spe cif y The Ins t ance O f SQL S er ver pag e, click the Con nec t b ut ton to sp ecif y the ins ta nce of SQL S er ver in which th e new U CP will be cre ated , and th en click Con- nec t in th e Conne c t T o Se r ve r dialo g box . 6 . Sp ecif y a name f or the U CP , as illustr ate d in Figure 2-3[...]

  • Seite 48

    28 CHAP TER 2 Mult i-Se r ve r Adm inis tr ati on 8 . O n the nex t pag e, the SQ L Ser ver in st ance i s compar ed a gains t a se rie s of pre re quisite s bef ore th e UCP is creat ed. Faile d condi tions ar e disp layed in a vali dation r ep or t . Cor - re ct a ll issu es , and th en click the Re run Validation b ut ton to ver if y the chang e[...]

  • Seite 49

    Enr oll ing S QL S er ver In st a nc es C HA PT ER 2 29 UCP P ost -Instal lat ion S teps Whe n the Crea te Utilit y Co ntrol Point W iz ard i s close d, th e Utilit y E x plo rer is invo ked, an d you are a utomati call y conne c ted to th e new ly cre ate d UCP . The UCP is au tomatic all y en- rolle d as a m anag ed ins t ance . The dat a colle c[...]

  • Seite 50

    3 0 CHAP TE R 2 Multi-Se r ver Adm inis tr ati on Managed I nstance Enro ll ment Prerequ isites As w ith many of th e other t a sk s in this chap ter, cert ain con dition s mus t be s atis e d to suc - ces sf ully enr oll an in st ance: ■ Y ou mus t have admini str ator pr ivil eg es on th e inst an ce of SQL S er ver . ■ Th e ins tan ce of [...]

  • Seite 51

    Enr oll ing S QL S er ver In st a nc es C HA PT ER 2 31 8 . A s sh own in Figur e 2-4, a s er ies of con ditio ns will b e evalua ted a gains t the SQ L Ser ver inst an ce to ens ure that i t pas se s all of the pr er equisi tes b efor e the in st ance i s enro lled . If ther e are any f ailure s pre venting th e enr ollme nt of the SQL S er ver in[...]

  • Seite 52

    32 CHAP TER 2 Mult i-Se r ve r Adm inis tr ati on Enrol lin g SQL Ser ver I nst ances by U sing Wind ows P owerShel l Wind ows Powe rSh ell c an als o be us ed to e nroll in st ance s . In fac t , scr ipting may b e the way to go if the re is a n ee d to enr oll a lar ge numb er of ins t ances int o a SQL Se r ve r UCP . Let ’s say you ne ed to e[...]

  • Seite 53

    Man a ging U tilit y Admin is tra tio n Se t tin gs C HA PT ER 2 33 FIG UR E 2-5 The Ma nag e d Ins t ance s da shb oar d Managing Util ity Administra tion Sett ings Af ter yo u are con nec te d to a UCP , us e the Utilit y Adminis tration n od e in the Utilit y Ex plo rer naviga tion pa ne to vie w and co ngure gl obal p olic y s et tin gs, s e[...]

  • Seite 54

    3 4 CHAP TE R 2 Multi-Se r ver Adm inis tr ati on 3 . O n the Utilit y E xplo rer to olba r , click th e Conne c t T o Utilit y icon. 4 . In th e Conne c t T o Se r ve r dialo g box , spe cif y a UCP inst ance , and th en click Conne c t . 5 . Af ter you ar e conne c ted , you c an dep loy dat a-tier a pplic ation s, m anag e inst an ces , and con?[...]

  • Seite 55

    Man a ging U tilit y Admin is tra tio n Se t tin gs C HA PT ER 2 35 FIG UR E 2-6 Modif yin g glob al p olici es f or ma nag ed i ns ta nce s V olatil e Resource Po licy Evaluat ion The na l se ct ion on th e Polic y ta b is Volatile Reso urce Polic y Eva luation . This s ec ti on, dis - played in F igure 2- 7 , pr ovide s st rate gies to minimi [...]

  • Seite 56

    36 CHAP TER 2 Mult i-Se r ve r Adm inis tr ati on FIG UR E 2- 7 Volatil e re so urc e po lic y ev alu atio n The ne x t set of con gurab le elemen ts al lows you to determine how frequent ly C PU uti liza - tion p olices should be in viola tion b efore t he C PU is reported as being underut iliz ed. The de- fault eval uatio n period for processo[...]

  • Seite 57

    Man a ging U tilit y Admin is tra tio n Se t tin gs C HA PT ER 2 37 The Secu rity T ab From a se curit y and auth ori zat ion pe r spe c tive , ther e are t wo secur it y r ole s as socia ted w ith a UCP . The r st r ole i s the Utilit y Adminis trator, and the se cond r ole is t he Utilit y Reade r . The Utilit y Admin istr ator is ultim atel y[...]

  • Seite 58

    38 CHAP TER 2 Mult i-Se r ve r Adm inis tr ati on 3 . O n the Ge ne ral pa ge, e nter the n ame of a W indow s use r in the L ogin Nam e box . 4 . S ele c t Win dows Au thentic atio n. 5 . Click OK . NOTE Un like in th e pr ev iou s ex amp le , do n ot a ss ign th is us er t he s y sa dmi n rol e on th e Se r ve r Rol e pa ge. I f you do , the u se[...]

  • Seite 59

    Man a ging U tilit y Admin is tra tio n Se t tin gs C HA PT ER 2 39 R E AL W O R L D M any or ga ni za tio ns h ave la r ge te am s ma na gin g th eir S QL Se r ver inf ra st ru ct ur es be ca us e th ey h ave hu ndr ed s of S QL Se r ver in st an ce s wit hin th ei r env iro n- men t . Le t ’s say yo u wan te d to gra nt 50 u se r s th e re a d-[...]

  • Seite 60

    4 0 CHAP TE R 2 Multi-Se r ver Adm inis tr ati on 4 . I n t h e U ti l i t y E xp l o r e r Co n t e n t p a n e , s e le c t th e d e s ir e d d a t a r e t e nt i o n p er io d f o r th e U MDW, as d isp laye d i n Fi gur e 2-9. The opt ion s a re 1 mo nth , 3 mo nth s, 6 m ont hs , 1 year, or 2 years . FIG UR E 2-9 Conf ig urin g th e dat a re t[...]

  • Seite 61

    41 C H A P T E R 3 D a ta - T i e r Ap p l i ca t i o n s A sk applic atio n devel ope r s or dat aba se adm inistr ator s wh at it was like to wor k with data -dr iven a pplic ation s in the pa st , an d mos t pro babl y do not u se adj ec ti ves such as “easy, ” “enjoyabl e,” or “wonder ful ” whe n they d escr ibe th eir exp er ien ce[...]

  • Seite 62

    42 CHAP TER 3 Dat a - T ie r Ap pli ca tio ns The Da ta- Tier Ap pli cation L ife C ycle Ther e are t wo common m eth ods fo r gen eratin g a DAC. On e is to auth or and b uild a DAC using a SQ L Ser ver dat a-tie r applic atio n proj ec t in Micro sof t Visua l Studio 2010. In the seco nd meth od, yo u can e x tra ct a DAC from a n exis ting dat a[...]

  • Seite 63

    In tr odu c tion t o Da ta - Tie r Ap pli ca tio ns C HA PT ER 3 43 Data -tier deve lop er s usin g a dat a-tier ap plic ation pr oje c t templ ate in Vi sual Studio 2010 r st buil d a DAC and then dep loy the DAC packa ge to an ins t ance of SQ L Ser ver 20 0 8 R2. In contra st , dat aba se admini str ator s usin g the E x tr ac t Dat a- T ier [...]

  • Seite 64

    4 4 CHAP TE R 3 Data - Tie r A ppl ic atio ns Real W orl d O rg ani z atio ns l oo kin g to a cce le ra te an d st a nd ar diz e dep loy me nt o f dat a ba se app lic ati on s wit hin th eir d at ab a se e nvi ron me nt s sho ul d leve ra ge d at a-ti er app lic ati on s inc lud ed i n SQL S er ver 20 0 8 R 2. B y utili zing d at a -tier a pp lic a[...]

  • Seite 65

    Vi su al Stu dio 2010 an d Da ta - Tie r Ap pl ica tio n Pr oje c ts CHAP TE R 3 45 ■ T y pe: Us er- de ne d Data Type ■ T y pe: Us er- de ne d T able T ype ■ Us er ■ V iew Database administrator s do not ha ve to worr y about look ing for unsuppor ted objec ts . This laborious ta sk is accomplished with the Ex trac t Data- T ier Appl[...]

  • Seite 66

    4 6 CHAP TE R 3 Data - Tie r A ppl ic atio ns 4 . In th e Proje c t T emp late pa ne, s ele c t Dat a- T ier A pplic ation . 5 . Sp ecif y the nam e, lo cati on, a nd so lution n ame fo r the dat a-tier a pplic ation , as sh own in Figure 3-2, and click OK . FIG UR E 3-2 Se le ct ing t he Dat a - T ie r Ap plic ati on pr oj ec t te mpl ate in V is [...]

  • Seite 67

    Vi su al Stu dio 2010 an d Da ta - Tie r Ap pl ica tio n Pr oje c ts CHAP TE R 3 4 7 FIG UR E 3-3 T he Cr e at e T ab le s ch e ma a nd t he S ol u ti on E x p l or e r p a n e i n a V i su al St ud i o 2 010 DAC proje c t Im p or ti ng an E xisti ng Data- Tier Ap pl icat io n Proj e ct int o Visual S tudi o 20 10 Inst ead of cr eatin g a DA C fro [...]

  • Seite 68

    4 8 CHAP TE R 3 Data - Tie r A ppl ic atio ns 4 . Re view t he infor matio n on the Welco me pa ge, an d then click N ex t . 5 . O n the Spe cif y Impor t Optio ns pa ge, s ele c t the op tion that a llows yo u to impo r t fr om a data -tier app licati on pa ckage . 6 . Click the Br ows e but t on, an d navi gate to the f olde r in which you p lace[...]

  • Seite 69

    E x tr ac tin g a Da t a-Tier A ppl ic atio n wi th SQ L Se r ve r Ma na ge me nt Stu dio CHAP TE R 3 49 Ex tracting a Data- Tier Application with SQL Ser ver Management Studio The E x tr ac t Dat a- Tier A pplic atio n Wiz ar d is anoth er to ol that you c an us e for cr eating a n ew data -tier app licati on. T he wi zar d is in SQL S er ver 20 0[...]

  • Seite 70

    50 CHAP TER 3 Dat a - T ie r Ap pli ca tio ns ■ De sc r ipt io n This pro per t y is opt ion al. Use it to de sc ri be the DAC. If thi s sec ti on is c ompl eted, the i nfor mat ion i s saved in the msdb da tab ase un der th e dat a-tier applic ation s no de in Mana gem ent Studio. FIG UR E 3-5 Sp eci f y in g DAC prop er ties w he n usi ng th e [...]

  • Seite 71

    E x tr ac tin g a Da t a-Tier A ppl ic atio n wi th SQ L Se r ve r Ma na ge me nt Stu dio CHAP TE R 3 51 co n rms that th e inf orm at ion is supp orted b y t he D A C, an d disp lays D AC obje ct issue s, D AC o bj ect warn ings, and D A C ob jects th at are su pported. If the re a re n o issu es, cl ic k Ne x t to co nt inue . Y ou also hav e [...]

  • Seite 72

    52 CHAP TER 3 Dat a - T ie r Ap pli ca tio ns Installing a New D A C Instance with t he Deploy Data- Tier Application Wizard Af ter th e DAC package ha s be en cre ate d using th e dat a-tier ap plica tion pr oje ct t empla te in Visu al Studio 2010, the Ex trac t Dat a- Tie r Appli catio n Wi zar d in SQL S er ver Mana gem ent Studio, or W indow s[...]

  • Seite 73

    Ins t alli ng a Ne w DAC Inst a nc e wit h th e De plo y Dat a - T ie r Ap pli ca tio n Wi z ar d C HA PT ER 3 53 FIG UR E 3- 7 Spe cif yi ng a DAC pack ag e to de ploy w ith t he D epl oy Dat a-Tier A pp lic atio n Wi z ard NOTE If a d at a ba se w ith t he s am e na me a lr ea dy e xi st s on t he in st an ce o f SQL S er ver, the w iz a rd c an [...]

  • Seite 74

    5 4 CHAP TE R 3 Data - Tie r A ppl ic atio ns 9. The n ex t pa ge inclu des a s ummar y of the se t tings t hat are u se d to dep loy the dat a-tie r applic ation . Rev iew th e inform ation dis playe d in the Summar y pa ge and DAC prop er- ties tr ee to e nsur e that the a c tions t aken ar e corr ec t , an d then click Ne x t to contin ue. 10. T[...]

  • Seite 75

    Regi ste ri ng a Da t a-Tier A pp lic ati on C HAP TE R 3 55 NOTE T hr oug ho ut thi s ch ap ter, you ca n al s o us e Win dow s Powe r Sh ell s cr ipt s in c on- junc ti on wi th da ta -tie r ap plic at ion s to do m any o f th e ta sk s disc us s ed , su ch a s ■ Cre atin g da ta -tie r ap plic at ion s. ■ Cre atin g se r ver obj ec t s . ■[...]

  • Seite 76

    56 CHAP TER 3 Dat a - T ie r Ap pli ca tio ns 5 . O n the Se t Prop er ties pa ge, co mple te the DAC prope r tie s by t y ping in th e applic atio n name , ver sion , and d es cription , as d escr ibe d her e: ■ Ap p lic at ion n am e This ref er s to the n ame of th e DA C . This val ue ca nnot b e alter ed an d is alw ays ide ntica l to the na[...]

  • Seite 77

    De le ting a D at a - T ie r Ap pli ca tio n CHA P TER 3 57 ■ Del et e Dat ab as e The DA C met ada ta an d the a sso ciate d dat abas e are dr opp ed . The dat a an d log l es ar e dele ted . Login s are n ot re moved . T o del ete th e DA C , follow th es e ste ps: 1 . In O bje ct E x plo rer, connec t to a SQ L Ser ver ins t ance cont ainin[...]

  • Seite 78

    58 CHAP TER 3 Dat a - T ie r Ap pli ca tio ns 6 . Re view t he infor matio n displaye d in the Summ ar y p age , as sh own in Figur e 3- 10. FIG UR E 3-10 Vie win g the S umma r y p age w he n de let ing a DAC Ensure th at the ap plica tion nam e, dat aba se na me, an d del ete me tho d are cor re c t . If the infor matio n is corr ec t , click Nex[...]

  • Seite 79

    Upg ra din g a Da ta - Tie r Ap pli ca tio n CH AP TER 3 59 Upgrad ing a Data- Tier Application Let us r ec all the p as t for a m oment , wh en up dating cha nge s to exis ting da tab as e sche mas and dat aba se ap plica tions w as a notic eabl y challe nging t as k. Da tab as e adminis trato rs u suall y create d scr ipt s that inclu ded th e ne[...]

  • Seite 80

    6 0 CHAP TE R 3 Data - Tie r A ppl ic atio ns 6 . On the Select Pac kage page, specif y the D AC pac kage t hat contai ns t he new D AC v ersion to upgrade to . Alt ernatively , you ca n use the Browse button t o spec if y the locati on o f t he D AC package . When the D AC pac kage i s selected, y ou can verify the D AC detai ls, suc h as the appl[...]

  • Seite 81

    Upg ra din g a Da ta - Tie r Ap pli ca tio n CH AP TER 3 61 NOTE If t he d at ab a se h as c ha ng e d, it i s a be st p ra c tice t o re vie w th e po ten tia l dat a los s es b ef or e you p ro ce e d an d ver if y t ha t thi s is th e ou tcom e yo u wan t for t he up gra d- ed d at ab a se . Howe ver, the or igin al d at ab a se i s stil l pr es[...]

  • Seite 82

    62 CHAP TER 3 Dat a - T ie r Ap pli ca tio ns FIG UR E 3-12 Rev iew ing t he r esul t info rm atio n on th e Upg rad e DAC page NOTE D at a-ti er a ppl ic atio ns a re a l ar ge a nd in tri ca te s ubje c t . Se e th e fol low ing so urc es f or m ore i nfo rm atio n: ■ “De sig ning a nd Im pl em enti ng Da t a-tie r Ap pli ca tion s” at h t [...]

  • Seite 83

    63 C H A P T E R 4 H i g h A v a i l a b i l i t y a nd V i r t u a l i za t i o n E n h a n c e m e n t s M icros of t SQ L Ser ver 20 0 8 R2 d elive r s seve ral e nhance ment s in th e area s of high availabilit y and vir tuali zati on. Ma ny of the enh ancem ent s are af liate d with th e Wind ows S er ver 20 08 R 2 op erat ing s ys tem an d[...]

  • Seite 84

    6 4 CHAP TE R 4 High Avail ab ilit y and V ir tua liz a tion E nh an ce me nt s ■ Win dows S er ver 20 0 8 R2 Hy pe r-V The Hyp er-V vir tualiz ation t echno log y im- prove ment s in Win dow s Ser ver 20 0 8 R2 w ere th e mos t so ught-afte r and anticip ated enhan ceme nt s for W indow s Ser ver 20 0 8 R 2. It is now p oss ible to v ir tua lize[...]

  • Seite 85

    Failov er Cl us ter in g wit h Win do ws S er ver 20 0 8 R 2 CH AP TER 4 65 including In ternet Information Ser v ices (IIS), Cluster Ser ver , SQL Ser ver 7 .0 Enterprise Edition, Microsof t Distributed T rans action Coordinator (MSDT C ) 2.0, and sometimes the Window s NT 4.0 O ption Pack . Mo re over, the hardwar e supp or t , dri ver sup por t,[...]

  • Seite 86

    6 6 CHAP TE R 4 High Avail ab ilit y and V ir tua liz a tion E nh an ce me nt s Figure 4 - 1 illus trat es a t wo -no de sin gle- inst an ce failove r clust er run ning SQ L Ser ver on Wind ows S er ver 20 08 R 2. Public Network Heartbeat Network SQL ClusterInstance01 SAN S torage Node1 Node2 FIG UR E 4 - 1 A t wo -n ode s ingl e -ins t anc e fail[...]

  • Seite 87

    Failov er Cl us ter in g wit h Win do ws S er ver 20 0 8 R 2 CH AP TER 4 67 Guest F ailo ver C luste ring In the pa st , phys ical s er ver s wer e usuall y af liate d wi th the no des in a f ailover clu ste r . T oday, vir t ualiz ation t echno logi es make it p ossib le to fo rm a clus ter wi th each n ode b eing a g ues t ope rating s y ste m[...]

  • Seite 88

    6 8 CHAP TE R 4 High Avail ab ilit y and V ir tua liz a tion E nh an ce me nt s NOTE G ue st c lu ste ri ng is a ls o su pp or te d w he n Hyp e r-V is on Wi ndo ws S er ver 20 0 8. Howe ver, Wind ows S er ver 2 0 08 R 2 pr ovi de s Li ve Migr ati on f or mov in g vir tua l ma chi ne s be t we en p hys ic al ho st s . Thi s is m uch m or e be ne ?[...]

  • Seite 89

    Failov er Cl us ter in g wit h Win do ws S er ver 20 0 8 R 2 CH AP TER 4 69 • Lis t Clus ter Volumes • Lis t Clus ter Se r v ice s And A pplic ation s • Validate Quor um Cong uration • Validate Reso urce Stat us • Validate Ser vice Pr incipal Na me • Validate V o lume Con sis tenc y ■ Ne t wor k • Lis t Ne t wor k Bindin g Ord er[...]

  • Seite 90

    70 CHAP TER 4 High Av ail abi lit y a nd V ir tua liz a tion E nh an ce me nt s 6 . O n the Conr mation p age , rev iew th e det ails f or ea ch tes t, an d the n click Nex t to be gin the valid ation pr oce ss . Whil e the vali dation p roc ess i s runnin g, s ta tus infor ma- tion is cont inually di splaye d on the Validating p age until all t[...]

  • Seite 91

    Failov er Cl us ter in g wit h Win do ws S er ver 20 0 8 R 2 CH AP TER 4 71 The Win dow s Ser ver 2008 R2 Best Practi ces Analy zer Anoth er to ol availab le in Win dow s Ser ver 20 0 8 R2 i s a ser ver man age ment to ol r efer re d to as the B est Pra c tices An aly zer ( BPA) . The BP A de ter mine s how comp liant a se r ve r rol e is by compar[...]

  • Seite 92

    72 CHAP TER 4 High Av ail abi lit y a nd V ir tua liz a tion E nh an ce me nt s SQL Ser ver 2008 R2 V ir tualization and Hyper- V Vir tualiz ation i s one of th e hot te st top ics of dis cus sion in alm ost e ver y SQL Se r ve r archite c tur e design s es sion o r execu tive br ien g se ssio n, main ly be c ause o rgani zati ons ar e be ginnin[...]

  • Seite 93

    SQL Se r ve r 20 0 8 R2 V ir t ua liz a tio n an d Hy pe r-V CHA P TER 4 73 Hyper -V01 Hyper -V02 Hyper -V03 Hyper -V04 C:ClusterSharesV olume1 VHD VHD VHD VHD C:ClusterSharesV olume2 VHD VHD VHD VHD FIG UR E 4 -5 A Hyp er -V clus te r and L ive M igra tio n Wind ows Ser ver 2 0 08 R2 Hyper- V Syst em Requi rements T able 4 - 1 b elow o utline [...]

  • Seite 94

    7 4 CHAP TE R 4 High Avai lab ilit y an d Vir tua liz a tio n Enh an ce me nt s NOTE Sys te m re qu ire me nt s va r y b as e d on a n or gan iz at ion's vir tu ali z ati on re qu ire me nt s . Or ga niz a tion s sh oul d siz e the ir wor k loa d s to en sur e th at th e Hy pe r-V hos t s ca n suc ce ss ful ly acc omm od at e all of t he v ir [...]

  • Seite 95

    Implem en tin g Li ve Migr at ion f or SQ L Se r v er 20 0 8 R2 CHAP T ER 4 75 NOTE T he Mi cr os of t A s se s sm ent a nd Pl an nin g T o olk it c an b e us ed t o ide ntif y whe th er o r not a n or ga niz a tion’s SQL Se r ver s ys tem s a re go o d ca ndi dat es f or v ir t ual iz ati on . The t oo lki t als o in clu de s to ol s for S QL Se[...]

  • Seite 96

    76 CHAP TER 4 High Av ail abi lit y a nd V ir tua liz a tion E nh an ce me nt s Enabl ing CSV As sumin g that the H yp er-V cluste r has a lrea dy b ee n built , the ne x t s tep is e nablin g CSV in Failover Cluste r Mana ger. Follow the ste ps in this s ec ti on to ena ble CS V on a Hy pe r-V failover cluste r runn ing on W indow s Se r ve r 20 0[...]

  • Seite 97

    Implem en tin g Li ve Migr at ion f or SQ L Se r v er 20 0 8 R2 CHAP T ER 4 77 5 . O n the Spe cif y Name An d Lo catio n pag e, ente r the na me of the SQ L Se r ve r VM and spe cif y w her e it will b e sto re d. For ex ampl e, the na me SQL S er ver200 8R 2- V M01 and th e VM can b e sto re d on Clus ter Shar ed Volume 1, a s displaye d in Figur[...]

  • Seite 98

    78 CHAP TER 4 High Av ail abi lit y a nd V ir tua liz a tion E nh an ce me nt s 7 . O n the N et wor kin g pag e, conn ec t th e net work ad apter to a n exis ting v ir tu al net work by sel ec tin g the app rop riate n et wo rk ada pter f rom th e men u. Click Nex t to co ntinue . 8 . O n the Conn ec t V ir tu al Hard D isk pa ge, a s show n in Fi[...]

  • Seite 99

    Implem en tin g Li ve Migr at ion f or SQ L Se r v er 20 0 8 R2 CHAP T ER 4 79 11. Fr o m t h e V i r t ua l Ma ch in es s e c ti o n o f t h e r e su lt s p an e in Hy p e r-V Ma na g er, r ig ht- cli ck t h e n am e of th e SQ L Se r ve r VM yo u ju s t c re a te d , a n d cl ic k C on ne c t . T he V ir t ua l Machine Con nec ti on too l ope ns [...]

  • Seite 100

    8 0 CHAP TE R 4 High Avail ab ilit y and V ir tua liz a tion E nh an ce me nt s 4 . Un der Auto matic Sta r t Ac tion , for th e What D o Y o u Want This Vir tual Machin e T o Do Whe n The Physi cal Com puter St ar t s? questio n, s ele c t Nothin g, as sh own in Fig ure 4 -8 . Then click A ppl y and OK . FIG UR E 4 -8 Config uri ng th e Auto mati [...]

  • Seite 101

    Implem en tin g Li ve Migr at ion f or SQ L Se r v er 20 0 8 R2 CHAP T ER 4 81 11. O n the S ele c t Ser vic e Or Ap plica tion pa ge, sh own in Fig ure 4 -9, click Vir tu al Machin e, and the n click Nex t . FIG UR E 4 -9 Se le c ting t he s er vic e and a ppli cat ion f or hi gh avai labi lit y 12. O n the S ele c t Vir tual Ma chine pa ge, sh ow[...]

  • Seite 102

    82 CHAP TER 4 High Av ail abi lit y a nd V ir tua liz a tion E nh an ce me nt s NOTE To make a VM high ly a vail ab le , you m ust e n sur e th at it i s not r un ning . It mu st be e ith er tu rn ed o f f or sh ut do wn . 13. Co nrm the s el ec tio n, an d then click Ne x t . 14. T he wi zar d congur es th e VM for hi gh availabili t y and p[...]

  • Seite 103

    Implem en tin g Li ve Migr at ion f or SQ L Se r v er 20 0 8 R2 CHAP T ER 4 83 In itia ti ng a Live M ig rat io n of a SQ L Ser ver VM Af ter an a dminis trator h as en able d CS V , cre ated a S QL Se r ve r 20 08 R 2 VM , congur ed t he automa tic st ar t option , and m ade th e VM highl y availabl e, it is tim e to initiate a li ve migra - ti[...]

  • Seite 104

    [...]

  • Seite 105

    85 C H A P T E R 5 C o n s o l i d a t i o n a n d M o n i t o r i n g T oday's com peti tive e conomy dic t ate s that or ganiz atio ns re duce co st an d impr ove agilit y in t heir dat ab ase e nviro nment s . This m ean s the lar ge pe rce nta ge of or ganiz a- tions o ut the re r unning un der utilize d Micro sof t SQL Ser ver in st allat[...]

  • Seite 106

    8 6 CHAP TE R 5 Cons oli dat ion a nd M oni tor ing Conso li dat ing Databases and Instances A ver y common SQ L Ser ver con soli dation s trate gy invol ves p lacing m any data bas es on a sin - gle ins tan ce of SQL S er ver . T his app roa ch of fe r s organ iza tions im prove d ope ratio ns t hr o u gh ce n tr a li z e d m an a g e me n t , s t[...]

  • Seite 107

    SQL S er ver C on so lida tio n Stra te gi es C HA PT ER 5 87 SQLInstance01 SQLInstance02 SQLInstance03 FIG UR E 5-2 Con so lidat ing m any dat ab as es o nto a si ngle p hys ica l ho st r unni ng th re e ins t anc es of SQL S er ver SQLInstance01 SQLInstance02 SQLInstance03 FIG UR E 5-3 Consoli dati ng ma ny dat ab as es o nto mul tipl e phy sic a[...]

  • Seite 108

    8 8 CHAP TE R 5 Cons oli dat ion a nd M oni tor ing and 6 4 -bi t ver sion s within a sin gle ho st . In ad dition , physi cal SQ L Ser ver s c an eas ily be v ir t ual- ized by u sing th e physic al-to-v ir t ual (P2V ) migratio n tool in clude d with Sys tem Cent er Vir tual Machine Ma nag er 20 0 8 R2. Fig ure 5- 4 illustr ates a con soli dation[...]

  • Seite 109

    Usin g th e SQL S er ve r Utilit y for C on so lid atio n an d Mo nito rin g CH AP TE R 5 89 Using the SQL Ser ver Util ity for Consolidation and Monit oring The SQ L Ser ver Utili t y is the c enter of o per ation s for mo nitor ing man age d ins ta nces of S QL Ser ver, databas es , and d epl oyed dat a-tie r applic ation s . By usin g the das hb[...]

  • Seite 110

    9 0 CHAP TE R 5 Cons oli dat ion a nd M oni tor ing The thr ee m ain dashb oar ds for m onito ring a nd man aging r es ource u tiliz ation an d cons oli- dation e f for ts ar e dis cuss ed in th e nex t sec tio ns . The se da shbo ard s and vi ewp oint s are ■ Th e SQL S er ver Utilit y d ashb oar d. ■ Th e Mana ge d Inst an ce vie wpo int. ■[...]

  • Seite 111

    Usin g th e SQL S er ve r Utilit y Da shb oa r d CH AP TER 5 91 The SQ L Ser ver Utili t y dash boa rd inclu des th e follow ing info rmati on: ■ Util it y S umm ar y Fo und in th e center of t he top r ow of the Utili t y E xpl ore r Content pane , thi s section is the rst pl ace t o look. It dis pla ys the n um ber of ma naged i nstances of [...]

  • Seite 112

    92 CHAP TER 5 Con so lid ati on a nd Mo nit or ing ● No D ata Avai la ble Eithe r dat a has no t be en uplo ade d fr om a ma nage d ins t ance or the re is a pr obl em wit h the colle c tion an d uplo ad pr oce ss . By vie wing t he Mana ge d Ins tan ce He alth se c tion , DBA s ar e able to qui ckly o bta in an over view of r es ource utili zat [...]

  • Seite 113

    Usin g th e SQL S er ve r Utilit y Da shb oa r d CH AP TER 5 93 ■ Dat a- Tie r Ap pli cat io n Hea lt h This se c tion is l oc ated in th e top -right co rne r of the Utilit y E x plor er Conte nt pan e. He alth s tat us is illus trate d in a pie ch ar t an d has f our pos sible d esign ation s: ● Well Ut ili ze d The numb er of dep loye d data[...]

  • Seite 114

    9 4 CHAP TE R 5 Cons oli dat ion a nd M oni tor ing ● O ver uti liz ed D at aba se Fi les This r epr es ent s the numb er of de ploye d dat a-tier applic ation s with da tab ase  les th at are v iolatin g le sp ace over utiliz atio n polici es . ● O ver uti liz ed St orag e Volume s This rep res ent s the num ber of d epl oyed dat a- tier[...]

  • Seite 115

    Usin g th e Ma na ge d In st an ce s V iew p oint CHAP TER 5 95 This se c tion e xplain ed h ow to obt ain summ ar y inf orma tion fo r all mana ge d inst an ces of SQL Se r ve r . D BA s se ek ing mo re infor matio n might be inte re ste d in the M anag ed In st ance s nod e in the tre e vi ew of Utilit y E xplo rer. This node h elps da tab ase a [...]

  • Seite 116

    9 6 CHAP TE R 5 Cons oli dat ion a nd M oni tor ing Reso urce utili zati on for e ach man age d ins ta nce of SQL S er ver is pr es ente d in the lis t vie w loc ate d at the top of th e Utilit y Ex plor er Conte nt pan e. He alth st ate ico ns app ear to t he right of each man age d ins ta nce an d prov ide summ ar y st atus fo r each ins t ance o[...]

  • Seite 117

    Usin g th e Ma na ge d In st an ce s V iew p oint CHAP TER 5 97 ■ V o lu me Sp ac e V olum e spa ce utiliz ation i s summar ize d in this column f or volume s with dat aba se s be longin g to each m anag ed in st ance . The he alth of thi s param ete r is dete rmin ed by th e globa l or lo cal s tora ge volum e utiliz ation p olicie s for man age[...]

  • Seite 118

    9 8 CHAP TE R 5 Cons oli dat ion a nd M oni tor ing FIG UR E 5- 8 Th e Stora ge U tili za tion t ab o n the M ana ge d Ins t anc es v iew po int Independent of how the les are g r ouped, hea lth status is communi cated fo r ev er y data- bas e, le gro up, datab as e le, or vo lume. Fo r examp le, th e gre en arr ows in F igure 5 -8 indic a[...]

  • Seite 119

    Usin g th e Ma na ge d In st an ce s V iew p oint CHAP TER 5 99 FIG UR E 5-9 The Polic y Det ail s t ab on th e Man ag ed In st an ce s vi ewp oint NOTE To overri de th e glo ba l pol ic y fo r a sp ec ic m an ag ed in s ta nc e, s el ec t th e O ver - rid e Th e Glo ba l Polic y optio n but t on . Nex t, s pe cif y th e new o ver util ize d a n[...]

  • Seite 120

    100 CHA PT ER 5 Con sol ida tio n an d Mon ito rin g FIG UR E 5-10 The Pr op er t y De ta ils t ab o n the M ana ge d Ins t ance s v iew po int Using the Data- Tier Application Viewpoin t As i t is wh en you us e the Ma nage d Ins t ance s vie wpoint to m onito r heal th st atu s and re so urce utiliz ation f or mana ge d ins tan ces of SQ L Ser ve[...]

  • Seite 121

    Usi ng th e Da t a-Tier A ppl ic atio n V iew po int CHAP TER 5 101 FIG UR E 5-11 The da ta -tie r app lic atio n vi ewp oin t Reso urce utili zati on for e ach de ploye d dat a-tier ap plica tion is pr es ente d in the lis t vi ew lo - cate d at the to p of the Utilit y E xplo rer Co ntent pa ne. He alth s ta te icons a ppe ar at the r ight of eac[...]

  • Seite 122

    102 CHA PT ER 5 Con sol ida tio n an d Mon ito rin g The Da ta- Tier Ap pli cation L ist View The colum ns pr ese nting th e st ate of he alth fo r each de ploye d dat a-tier a pplic ation in th e data -tier app licati on lis t vie w include ■ App lic at ion C PU This column disp lays th e heal th st ate utili zat ion of the p roc ess or for the [...]

  • Seite 123

    Usi ng th e Da t a-Tier A ppl ic atio n V iew po int CHAP TER 5 103 T wo line ar graph s are pr es ente d nex t to each othe r . T he r s t graph sh ows CPU utiliz a - tion ba se d on the s ele c te d depl oyed dat a-tie r appli catio n, an d the se con d graph di s- plays da ta b ase d on th e comput er as so ciated w ith the d epl oyed dat a-t[...]

  • Seite 124

    104 CHA PT ER 5 Con sol ida tio n an d Mon ito rin g ■ Polic y D et ai ls The Polic y D et ails t ab, sho wn in Figur e 5- 13, i s whe re a DBA c an vi ew the glob al po licies app lie d to a sel ec te d dep loyed d ata -tier app licati on. T he Polic y Det ails t ab c an als o be us ed to cr eate a cus tom p olic y tha t overr ide s the de fault[...]

  • Seite 125

    Usi ng th e Da t a-Tier A ppl ic atio n V iew po int CHAP TER 5 105 Th e dis p lay i s b r oke n up int o th e f o llo w in g f o ur p ol ici e s , w hi ch c an b e v i ew e d or ove r r id de n: ● Data - Ti er Ap plica tion CPU Utiliz ation Polici es ● File Space U tiliz ation Policie s ● Compute r CPU Utiliza tion Policie s ● Storage Volu[...]

  • Seite 126

    [...]

  • Seite 127

    P A R T I I B u s i n e s s I n t e l l i g e n c e D e v e l o p m e n t S t acia M iSner CHA P TER 6 Sc alab le Dat a Warehou sing 109 CHA P TER 7 Mas ter D ata S er vice s 125 CHA P TER 8 Compl ex Event Pr oces sin g with StreamIns ight 145 CHA P TER 9 Rep or t ing Se r v ices Enh ance ment s 165 CHA P TER 10 S elf-Ser vice An aly sis with Powe [...]

  • Seite 128

    [...]

  • Seite 129

    10 9 C H A P T E R 6 S ca l ab l e D a ta W a re h o u s i n g M icros of t SQ L Ser ver 20 0 8 R2 Par allel Da ta Wareh ous e is an ente rpr ise d ata wa re - hous e appli ance ba se d on te chnolo gy o rigina lly cre ated b y DA T A lle gro an d acquire d by Micr osof t in 20 08 . In the m onths f ollowin g the a cquisitio n, Micr osof t re - vam[...]

  • Seite 130

    110 CHA PT ER 6 Sc ala bl e Da ta Wa re ho us ing Warehou se sof t war e. Wh en the a ss embl y pro ces s is comp lete , the ve ndor sh ips the ap plianc e to you usin g shock pr oof pall et s. W hen i t arr ives , you r em ove the app liance f rom th e palle t s, plug it into a p ower s ourc e, and co nne c t it to your ne t wor k . Parallel D ata[...]

  • Seite 131

    Par all el D at a War eh ou se A rc hit ec t ur e CHA P TER 6 111 Control rack Data rack Management node active/passive User queries Control node active/passive Landing Zone Backup node Control rack Active ser ver Dedicated storage P assive ser ver Data loading Data backup Dual Fibre Channel Dual InfiniBand SQL SQL SQL SQL SQL SQL SQL SQL FIG UR E [...]

  • Seite 132

    112 CHA PT ER 6 Sc ala bl e Da ta Wa re ho us ing The Cont rol Rack The contr ol ra ck is a se parate r ack that h ous es the s er ver s , sto rage , and n et wo rkin g com- pon ent s for the n ode s that p rovi de contr ol, ma nage me nt, or int er f ace f unc tion s. I t contain s seve ral t y pe s of nod es tha t Parallel D ata Wareh ous e use s[...]

  • Seite 133

    Par all el D at a War eh ou se A rc hit ec t ur e CHA P TER 6 113 Deve lopm ent Studio, SQ L Ser ver Inte grati on Se r v ices , SQL S er ver An aly sis Se r v ices , an d SQL Ser ver Rep or ting Se r v ices . Th e Nex us client is t he que r y e ditor th at you ca n use to sub mit quer ies by u sing SQ L st atem ent s to Paralle l Dat a Warehou se[...]

  • Seite 134

    114 CHA PT ER 6 Sc ala bl e Da ta Wa re ho us ing The Landing Zone Node The L an ding Zon e is a high- cap acit y d ata s tora ge no de in th e contro l rack that co ntain s tera - by te s of disk spa ce for te mpo rar y stor age of us er dat a b efor e loa ding it into t he app liance. Using your E TL pr oce ss es to m ove data to th e L andin g Z[...]

  • Seite 135

    Dat a M an ag em ent CHA PT ER 6 115 any nece ss ar y data to e ach comp ute no de so t hat it c an pro ces s the qu er y in parall el with other co mpute n ode s with out r equir ing dat a fr om oth er lo cati ons dur ing pr oce ssin g. Thi s feat ure, c alle d data colo cation , en sure s that e ach compu te nod e can e xecute i ts p or t ion of [...]

  • Seite 136

    116 CHA PT ER 6 Sc ala bl e Da ta Wa re ho us ing Y ou d esign th e dat a layout on th e appli ance to avoid o r minimize dat a move ment f or par - allel qu eri es by usin g eith er a r eplic ate d or a dis tribu ted s trate gy f or sto rag e. Wh en pla nning which st rate gy to impl eme nt, yo u consi der th e t yp es of jo ins that th e par alle[...]

  • Seite 137

    Dat a M an ag em ent CHA PT ER 6 11 7 nodes i n the applianc e. There are p er formance cons iderati ons f or t he selection of a d istribut ion column, s uch as dis tinc tn ess , dat a skew, and the t ype s of quer ie s execut ed on th e s ys tem . For a det aile d discu ssio n of the choic e of distr ibute d t able s, r efe r to the pr odu c t do[...]

  • Seite 138

    118 CHA PT ER 6 Sc ala bl e Da ta Wa re ho us ing CREA TE DA T AB ASE The CRE A TE DA T A BASE s t ateme nt has a s et of op tions f or supp or ting dis tribu ted an d re pli- cate d ta ble s. You determin e how mu ch space yo u ne ed in tot al for t he dat aba se fo r rep licate d tab les , dis trib uted t ab les , and l ogs . Parall el Dat a Ware[...]

  • Seite 139

    Dat a M an ag em ent CHA PT ER 6 119 NOTE Pa ra lle l Da ta Wa re ho us e do es n ot us e th e T r an s ac t-SQL pa r ti tion s ch em a or p ar t i- tion f unc ti on. A l so, yo u ca n cr e ate a c lu ste r ed in de x onl y wh en yo u us e CRE A TE TABLE . T o cr ea te a n onc lu ste r ed in de x , you u se CR E A TE INDE X . Her e is an exa mple o[...]

  • Seite 140

    120 CHA PT ER 6 Sc ala bl e Da ta Wa re ho us ing In additi on, yo u can u se a CRE A TE T ABLE A S SELEC T st atem ent to cre ate a t able f rom t he res ults of a SELEC T s ta teme nt. You might use this te chniqu e whe n you are r edi str ibutin g or defr agme nting a t abl e. Her e is an exa mple of th e sy nta x for a CRE A TE T ABL E AS SELEC[...]

  • Seite 141

    Dat a M an ag em ent CHA PT ER 6 121 Quer y Processin g Que r y p roc ess ing in Parall el Dat a Wareho use is m ore co mple x than in an SMP dat a war e - hous e be ca use pr oce ssin g mus t man age high ava ilabilit y , p aralle liz ation , and d ata m oveme nt bet ween n ode s . In gene ral , Paralle l Data Ware hous e’s control no de foll ow[...]

  • Seite 142

    122 CHA PT ER 6 Sc ala bl e Da ta Wa re ho us ing Data Ware hous e’s Landin g Zone . Y ou t hen invoke a comm and-lin e tool , DWLoa der, and spe c- if y o ption s to loa d the dat a into the ap plian ce. Or yo u can us e Inte gration S er vice s to move data to th e L andin g Zone an d call th e loa ding f unc tion alit y dir ec tl y. T o loa d [...]

  • Seite 143

    Bu sin es s Int el lige nc e Int eg rat ion C HA PT ER 6 123 Business Intelli gence Integra tion Parallel D ata Wareh ous e integr ates w ith the SQ L Se r ve r busin ess int ellige nce (B I) comp o- nent s — Inte grati on Se r v ices , Re por ting Se r v ices , an d SQL Se r ve r Ana lys is Ser vic es . In tegrat ion Ser vices Integra tion Se r [...]

  • Seite 144

    [...]

  • Seite 145

    125 C H A P T E R 7 M as t e r D a ta S er v i c es M icros of t SQ L Ser ver 20 0 8 R2 M as ter Dat a Se r v ices ( MDS) is a nothe r new te chno log y in the SQL S er ver famil y and i s bas ed on s of t ware f rom Micr osof t ’s acquisition of Stratat ure in 20 07 . J ust a s SQL S er ver Rep or tin g Se r v ices (SSRS ) is an ex tensib le re [...]

  • Seite 146

    126 CHA PT ER 7 Mas te r Da ta S er vi ce s This s cenario presents add itional p roblems fo r opera tional master data in an org aniza- tion becau se the re is n o coor dinatio n acro ss multip le s ys tem s. B usine ss us er s c annot b e sure whi ch of the many availa ble s ys tem s has th e corr ec t info rmat ion. M ore over, even whe n a use [...]

  • Seite 147

    Mas ter D at a S er vic es C omp on en ts CHAP T ER 7 127 In additi on to of f erin g ex ibilit y, MDS allows you to m anag e mas ter da ta p roa c tivel y. Inst ead of dis cover ing dat a pr oble ms in fa iled E TL pr oce ss es or in accurat e rep or t s , you c an enga ge bu sines s us er s as da ta stewards . A s da ta s tewar ds , they h ave[...]

  • Seite 148

    128 CHA PT ER 7 Mas te r Da ta S er vi ce s Master Da ta Ser vices Co ngu ratio n Manager Befo re you c an s tar t using M DS to mana ge your ma ste r dat a, yo u use Ma ste r Dat a Ser vice s Congurati on Mana ger. This congurati on too l include s pa ges to cr eate th e MDS dat abas e, congur e the s ys tem s et tin gs for all Web s e[...]

  • Seite 149

    Dat a Ste wa rd shi p CH AP TER 7 129 ■ Inte grat io n Man ag em ent Use t his are a to creat e and pr oce ss b atches f or im- por ting dat a fr om st a ging t able s into the MDS da tab ase , vie w er ror s ar ising f ro m the imp or t pr oce ss , an d create s ubscr iption v iew s for con sumptio n of mas ter da ta by ope ration al and a naly [...]

  • Seite 150

    130 CHA PT ER 7 Mas te r Da ta S er vi ce s An enti t y can a lso h ave any numbe r of domain -bas ed a t trib utes w hos e value s are m em- ber s of an othe r rel ated e ntit y. In the examp le in Figur e 7 - 1 , the Pro duc tSub Ca tego r y at tribute is a domain -bas ed a t trib ute. Th at is , the Pro duc tS ubCa tego r y co de s are at tr ibu[...]

  • Seite 151

    Dat a Ste wa rd shi p CH AP TER 7 131 FIG UR E 7- 3 A coll ec ti on Master Da ta Main tenance Mas ter Dat a Man age r is mor e than a pl ace to de ne mo del o bje c ts . It als o allow s you to create , edit , an d update l eaf me mbe r s and con soli dated m emb er s . Whe n you ad d a leaf memb er, you initially pr ovid e value s for on ly the[...]

  • Seite 152

    132 CHA PT ER 7 Mas te r Da ta S er vi ce s FIG UR E 7- 5 At tri but es an d val idat ion i ss ues Busi ness Rules One of th e goal s of a mas ter dat a ma nage ment s y ste m is to se t up dat a corr ec tl y once an d to prop agate o nly va lid chang es to dow ns tre am sy st ems . T o achie ve this go al, the s y ste m mus t be abl e to re cogniz[...]

  • Seite 153

    Dat a Ste wa rd shi p CH AP TER 7 133 FIG UR E 7- 7 The Re quir ed F iel ds b usin es s r ule Whe n creatin g a busin es s rul e, you c an use a ny of the follo wing t ype s of ac tion s: ■ Defa ult Value Se ts th e defa ult valu e of an at trib ute to bl ank , a sp ecic v alue that you suppl y in the b usine ss r ule, a g ene rate d value th [...]

  • Seite 154

    134 CHA PT ER 7 Mas te r Da ta S er vi ce s T r ansaction Logg ing MDS use s a tran sa cti on lo g, as s hown in Fi gure 7 -9, to captur e ever y chang e mad e to mas ter data , includin g the m ast er dat a valu e bef ore an d af te r the chan ge, th e use r who m ade th e change (not show n), the date and time of th e chang e, and o ther id entif[...]

  • Seite 155

    Inte gr ati on CH AP TE R 7 135 Integr ation Mas ter Dat a Man age r also p rov ides s uppo r t for d ata inte grati on be t we en MDS an d othe r ap- plica tions . Ma ste r Data M anag er inclu des an Int egrat ion Man age ment ar ea fo r impo r ting a nd expo r tin g dat a. Howe ver, the impor t a nd exp or t proc ess es h ere a re noth ing like [...]

  • Seite 156

    136 CHA PT ER 7 Mas te r Da ta S er vi ce s sel ec te d. Wh en the b atch pro ces sing i s compl ete, yo u can r evi ew the s t atus of th e batch in the st a ging batch l og, w hich is availab le in Ma ste r Data M anag er, as shown in Figur e 7 - 11. FIG UR E 7- 11 Th e st a ging b atch l og If the lo g indic ates any e rro r s for th e st agin g[...]

  • Seite 157

    Admin is tra tio n CH AP TER 7 137 subscr iption v iew in Ma st er Dat a Mana ger a s an entit y-bas ed le af me mbe r view, you c an quer y the Pro duc t vi ew and s ee t he re sult s in SQL S er ver Mana gem ent Studio, a s show n in Figure 7- 12. FIG UR E 7- 12 Quer yin g the Pr od uc t sub scr ipt ion v iew Admi nistra tion Of course , Master D[...]

  • Seite 158

    138 CHA PT ER 7 Mas te r Da ta S er vi ce s create a n ew ver si on by copy ing a pr evi ousl y commit ted ver sio n and all owing u ser s to m ake their chan ge s to the ne w ver sion . FIG UR E 7- 13 Mo de l ver s ion s Securi t y MDS use s a rol e-b as ed au thor iza tion s ys tem th at allow s you to cong ure se curi t y bot h by func ti ona[...]

  • Seite 159

    Admin is tra tio n CH AP TER 7 139 Figure 7- 15. An adm inistr ator wi th full a cces s pri vile ge s would in ste ad se e the f ull lis t of fun c- tional a rea s on the h ome p age. FIG UR E 7- 15 Th e Ma ste r Dat a Ma nag er h om e pag e for a u se r wi th onl y E xp lor er p e rmi ssi ons Data s ecur it y b egin s at the m ode l leve l. Wh en [...]

  • Seite 160

    14 0 CHA PT ER 7 Mas te r Da ta S er vi ce s Color t abl e on the r ight side of th e pag e. Th ese ico ns indi cate th at the val ues in th e tab le ar e not edi tab le. T he r st t wo but ton s above th e tab le allow a u ser w ith Up date pe rmis sio ns to add or d ele te a mem ber, but thos e but ton s are un availabl e her e be ca use th e [...]

  • Seite 161

    Admin is tra tio n CH AP TER 7 141 More s pe cicall y, the secur it y cong uration a llows thi s use r to edit o nly th e Bikes and Ac - ces sor ies c ate gori es in the Re t ail group, bu t the us er c annot e dit c atego rie s in the W hole sal e group. Le t ’s look r s t at the ef fec t of th ese p er miss ions o n the us er ’s expe[...]

  • Seite 162

    142 CHA PT ER 7 Mas te r Da ta S er vi ce s Model De plo yment Whe n you have nali zed th e mas ter da ta m ode l str uc tur e, you c an us e the mo del d epl oy - ment c apab ilities in M ast er Dat a Mana ger to s er ialize th e mod el and i ts o bje ct s as a p acka ge that you c an late r dep loy on an other s er ver . In this way, you can m[...]

  • Seite 163

    Pro gra mm ab ilit y CHA P TER 7 143 ■ Mic ro sof t . Ma ste rDat a Se r v ice s. S e r vi ce s Con t a in s a c la s s t o p r ov i d e in s t an c e s of t h e M ds S e r v i ce H o s t cl a s s a n d a cl a s s to pr o v i de a n A PI f o r o p e ra t io n s r e l at e d t o bu s in e s s r u l e s ■ Mic ro sof t . Ma ste rDat a Se r v ice s[...]

  • Seite 164

    14 4 CHA PT ER 7 Mas te r Da ta S er vi ce s ■ Mdq .Re ge xIsV alid Indic ates w het her th e re gular ex pre ssio n is valid ■ Mdq .Re ge xMa sk Conver t s a se t of re gular ex pre ssio n optio n ags into a b inar y value ■ Mdq .Re ge xMatc h es Finds all m atches of a r egul ar expr es sion in an in put s trin g ■ Mdq .Re ge xRep la c[...]

  • Seite 165

    1 45 C H A P T E R 8 C o m p l e x E v e n t P r o c es s i n g w i t h S t r e a m I ns i g h t M icros of t SQ L Ser ver Strea mInsight i s a compl ex event p roc ess ing (CEP) engine. Thi s techn olog y is a ne w of fe ring in t he SQL S er ver famil y, making it s r st ap pe arance in SQL Se r ve r 20 08 R 2. It ship s with th e Standa rd, E[...]

  • Seite 166

    146 CHA PT ER 8 Comp le x Even t Pr oc es sin g wi th Str ea mIn si ght Similarl y , ther e are ce r t ain t y pe s of applic ation s that b ene t fr om the a bilit y to an aly ze data a s clos e as po ssib le to the tim e that the a pplic ation s capt ure th e data . For e xampl e, compani es se lling pr odu c ts on line of t en us e clicks tr [...]

  • Seite 167

    Stre am Ins ight A r chi te c tur e CH AP TER 8 147 Data S tructures The high -through put dat a that Stre amIns ight re quire s is kn own as a str eam . More sp eci - call y , a stre am is a coll ec tion of d ata th at chang es over t ime. Fo r examp le, a Web lo g cont ains data a bou t each se r ver hit , includin g the date , time, p age r e[...]

  • Seite 168

    148 CHA PT ER 8 Comp le x Even t Pr oc es sin g wi th Str ea mIn si ght P agers and monitoring devices Input Adapters Data feeds Event stores and databases Web servers Devices and sensors Event Event Event Event CEP Engine S tanding Queries Event Event Event Event Event Output Adapters CEP Application at Run Time S tatic reference data Event Source[...]

  • Seite 169

    Stre am Ins ight A r chi te c tur e CH AP TER 8 14 9 Output Ad apters The ou tput a dapter s r ever s e the op er ations of t he inpu t adapte r s by tran slatin g event s into a form at that is u sab le by the t ar get d evice a nd the n sen ding th e trans late d dat a to the dev ice. The de velop ment p ro ces s for an o utput a dapte r is ver y[...]

  • Seite 170

    150 CHA PT ER 8 Comp le x Even t Pr oc es sin g wi th Str ea mIn si ght If you choo se to de ploy CEP as a s ta ndalo ne se r ve r , ther e are s ome limit ati ons tha t af fe c t the way you de velo p applic atio ns . Fir st , you c an use o nly th e explici t ser ver de velop ment mod el (which is de scr ibe d in the ne x t se c tion of thi s cha[...]

  • Seite 171

    A pp lic at ion D eve lo pme nt C HA PT ER 8 15 1 Ser ver dat aba se mu st a dapt to the s chem a of the t able th at it que rie s. I nst ead , you pr ovide the t able s chem a in a congurat ion sp ecic ation w hen th e adap ter is b ound to t he que r y. Conver se ly, an unty pe d outp ut adap ter r ecei ves th e event t ype d escr iption , [...]

  • Seite 172

    152 CHA PT ER 8 Comp le x Even t Pr oc es sin g wi th Str ea mIn si ght the e lds on e at a time an d enqu eue t he event . Th e unt yp e d outpu t adapt er wor k s similar ly, but ins tea d it mus t be a ble to us e the con guratio n spe cica tion to re tri eve quer y pr oce ssin g res ults f rom a d equ eue d eve nt. The ne x t s tep is [...]

  • Seite 173

    A pp lic at ion D eve lo pme nt C HA PT ER 8 153 The na l ste p is to crea te a .NE T as se mbl y for the a dapte r . At minimum , the a dapter include s a cons tr uc tor , a Star t( ) me thod , a Resum e( ) meth od, a nd eit her a Pr oduce Event s( ) or Consume Event s( ) metho d, de pe nding o n whe ther yo u are d evelo ping an in put ad apte[...]

  • Seite 174

    154 CHA PT ER 8 Comp le x Even t Pr oc es sin g wi th Str ea mIn si ght Anoth er t ask th e adapt er mus t pe r fo rm is cla ssi catio n of an event . Th at is , the ada pter mus t spe cif y th e event k ind a s eithe r INSERT o r Curre nt Time In crem ent (CT I). The ad apter adds e vent s with th e INSERT eve nt kin d to the s trea m as it r e[...]

  • Seite 175

    A pp lic at ion D eve lo pme nt C HA PT ER 8 155 Queries Af ter yo u create a n event s tre am obje c t , you wr ite a LIN Q expr es sion o n top of the eve nt str eam ob jec t . Y o u use L INQ exp re ssio ns to de ne the  elds f or ou tput eve nts , to lte r event s bef ore qu er y proc ess ing, to gr oup eve nts into s ubse ts , an d to[...]

  • Seite 176

    156 CHA PT ER 8 Comp le x Even t Pr oc es sin g wi th Str ea mIn si ght Even t Windows A windo w rep res ent s a subs et of dat a fr om an eve nt str eam f or a pe rio d of time. Af ter you create a s tre am of win dows , you c an pe r fo rm ag gre gation , T op K (a LINQ oper ation de scr ibe d later in thi s chapter), or u ser -d ene d op erat[...]

  • Seite 177

    A pp lic at ion D eve lo pme nt C HA PT ER 8 157 As yo u might gue ss , the key to wo rkin g with w indow s is to have a cle ar unde r st an ding of the time sp an that e ach wind ow cover s . The re ar e thre e t yp es of w indow s tre ams th at Stream- Insight sup por ts — h oppi ng windo ws , snapsho t windo ws , an d count wind ows . In a h o[...]

  • Seite 178

    158 CHA PT ER 8 Comp le x Even t Pr oc es sin g wi th Str ea mIn si ght windo ws s trea m. Alt hough yo u can u se the H oppin gWindow m eth od to cre ate tumb ling win- dows , the re is a T umblingWin dow me thod . The fo llowin g code ill ustr ates h ow to count eve nts in tumblin g windo ws that o ccur eve r y ha lf hour. var outputStream = from[...]

  • Seite 179

    A pp lic at ion D eve lo pme nt C HA PT ER 8 159 Count wind ows ar e compl ete ly dif f er ent fr om the ot her w indow t ype s be cau se the s ize of the win dows i s variab le. W hen yo u create w indow s , you pr ovide a p aram eter n a s a count of event s to ful ll within a w indow. For exam ple, a ssum e n is 2 as s hown in Fi gure 8-5. Th[...]

  • Seite 180

    160 CHA PT ER 8 Comp le x Even t Pr oc es sin g wi th Str ea mIn si ght As sume yo u want to app ly the Sum a nd Avg aggr egati ons to e ld x in an inp ut st ream . Th e follow ing exa mple sh ows yo u how to us e thes e ag gre gation s as well a s the Count a ggre ga- tion fo r each sna pshot w indow : var outputStream = from eventWindow in inp[...]

  • Seite 181

    A pp lic at ion D eve lo pme nt C HA PT ER 8 161 Joi ns Y ou c an us e a join op erati on to match eve nts f ro m t wo st ream s. T he CEP ser ver r s t matche s event s onl y if they h ave overl appin g time inter vals , an d then a pplie s the con dition s that you spe cif y in th e join pr edic ate. T he ou tput of a join o per ation i s a ne[...]

  • Seite 182

    162 CHA PT ER 8 Comp le x Even t Pr oc es sin g wi th Str ea mIn si ght Quer y T empl ate B indi ng The me tho d that the CEP se r ve r use s to ins ta ntiate the qu er y templ ate as a s ta nding qu er y dep end s on the de velo pme nt mod el that yo u use . If you are u sing th e explici t ser ver de vel- opme nt mod el, yo u creat e a quer y bin[...]

  • Seite 183

    The Ma na ge me nt In ter fa ce CH AP TE R 8 163 The Quer y Object In both th e explicit a nd impli cit devel opm ent mo del s, yo u create a q uer y obje c t. W ith th at obje c t ins tantia ted , you ca n use th e Star t( ) and Stop( ) me thod s. T he Star t( ) m etho d ins ta nti- ates th e adapte r s usin g the ada pter f ac tori es , st ar ts [...]

  • Seite 184

    164 CHA PT ER 8 Comp le x Even t Pr oc es sin g wi th Str ea mIn si ght Wind ows P owerShel l Diag nostics For quick anal ysi s, yo u can us e Win dow s Power Shell s cript s to vi ew diagn os tic infor mation rathe r than wr itin g a comple te diagn ost ic applic atio n. Be for e you ca n use a W indow s Pow- er Shell s cript , the Stre amIns ight[...]

  • Seite 185

    165 C H A P T E R 9 R e p o r t i n g S e r v i c es E n h a n c e m e n ts I f you tho ught Micros of t S QL Se r ve r 20 08 Re por ting S er vices intr odu ced a l ot of great ne w fe at ur e s to th e re p or ti n g p la t f or m , ju s t w ai t unt il yo u dis c ove r wh at ’s n ew in Repo r tin g Ser vice s in SQL S er ver 20 0 8 R2. Th e Re[...]

  • Seite 186

    166 CHA PT ER 9 Rep or ti ng S er vic es E nh an ce me nt s Com bini ng Data from More T han One Da tase t T o displ ay data f rom m ore t han on e sour ce in a tab le (or in any data r egio n, fo r that mat t er), you mus t crea te a dat ase t that s omeh ow comb ines th e dat a be cau se a dat a re gion bin ds to one an d only o ne dat as et . Y [...]

  • Seite 187

    Ex pr e ssi on L a ng ua ge Imp r ovem en ts CHAP TE R 9 167 =Lookup(Fields!StateProvinceCode.Value, Fields!StProv.Value, Fields!StProvName.Value, "Dataset1") The Mult iLoo kup f unc tion al so r equir es a on e-to -o ne re lation ship be t we en the s our ce and des tinatio n, b ut it acce pts a s et of s ource v alue s as inpu t. Re por[...]

  • Seite 188

    168 CHA PT ER 9 Rep or ti ng S er vic es E nh an ce me nt s Whe n ther e is a one -to- many re lation ship be t we en the s our ce and de st ination v alue s, yo u use th e Loo kupSe t fun ct ion. T his f unc tion a ccept s a singl e value f rom th e so urce dat as et as input an d ret urns a n array of m atching val ues f rom th e des tinati on da[...]

  • Seite 189

    Ex pr e ssi on L a ng ua ge Imp r ovem en ts CHAP TE R 9 169 FIG UR E 9- 4 Aggre gat ion of a n agg re gati on Her e is the ex pre ssi on for th e value di splaye d in the Mo nthly Avera ge r ow: =Avg(Sum(Fields!SalesAmount.Value,"EnglishMonthName")) Con di tional R enderi ng E xpressio ns The ex pre ssi on lang uage in SQ L Se r ve r 20 [...]

  • Seite 190

    1 70 CHAP TER 9 Rep or t ing S er vi ce s Enh an ce me nt s Anoth er opt ion is to us e the Re nderF ormat gl obal v ariab le with t he IsInt era ct ive me mbe r to set th e condi tions of a p rop er t y . For exam ple, l et ’s say you have a r ep or t th at display s sum- mari zed s ale s but al so allow s the us er to to ggle a r ep or t it em [...]

  • Seite 191

    L ayo ut Con tr ol C HAP TE R 9 1 71 FIG UR E 9- 6 Changing r ep or t var iabl es T o wr ite to your r epo r t var iabl e, you us e the S etValue meth od of th e varia ble. Fo r examp le, ass ume that yo u have se t up the re por t to inse r t a p age br eak b et we en gr oup ins t ance s, an d you want to up date the exe cutio n time wh en the gr [...]

  • Seite 192

    1 72 CHAP TER 9 Rep or t ing S er vi ce s Enh an ce me nt s Pag inati on Properties Ther e are t hre e new p rop er ties availab le to man age p aginati on: Dis able d, Re se tPage Numbe r , and Page Nam e. The se pr op er t ies ap pear in t he Prop er ties win dow wh en you s el ec t a t ablix , re ct an gle, or ch ar t in th e rep or t b od y or [...]

  • Seite 193

    L ayo ut Con tr ol C HAP TE R 9 1 73 Las t, consider how you can use the P ageName prope r t y. As one example, instead of using page n umbe r s in an E xcel wor kb ook , you can a ssign a uni que nam e to each sh ee t in the work bo ok . Y o u might , for ex ample , use th e group e xpr es sion tha t dene s the p age br eak a s the Page Name p [...]

  • Seite 194

    1 7 4 CHA P TER 9 Rep or t ing S er vi ce s Enh an ce me nt s FIG UR E 9-9 Synchro nize d gr oup s T e x t Box Or ientat ion Each tex t box has a Wri tingMo de pr ope r t y that by def ault disp lays tex t hor izont ally. There i s also a n option to di splay tex t ver ti call y to accomm odate l angua ges th at displ ay in that for - mat . Altho u[...]

  • Seite 195

    Dat a V is ua liz a tion CHA PT ER 9 1 75 Data Visualization Prior to SQ L Se r ve r 20 08 R 2 Rep or t ing Se r v ices , your o nly op tion fo r enha ncing a r epo r t wi th data v isuali zat ion was to a dd a char t or gaug e. Now yo ur option s have be en e xpan ded to include da ta b ar s, sp ark line s, in dicato r s, an d map s. Data Bars A d[...]

  • Seite 196

    1 76 CHAP TER 9 Rep or t ing S er vi ce s Enh an ce me nt s Sparkl ines Like data b ar s, s park line s can b e use d to inclu de a dat a vis ualiz ation al ong side the d et aile d data . Wh ere as a dat a bar u sually s hows a s ingle p oint, a s park line sh ows mul tiple dat a p oints over time , makin g it ea sier to s pot tr end s. Y ou c an [...]

  • Seite 197

    Dat a V is ua liz a tion CHA PT ER 9 1 77 FIG UR E 9-13 Indic ato r t y pe s Af ter s ele c ting a s et of indi cator s , you a ss ociate th e set w ith a val ue in your dat as et or w ith an expr es sion , such as a co mpar iso n of a data se t value to a go al. You then den e the r ules that de termin e which in dicato r prop er ly r epr ese n[...]

  • Seite 198

    1 78 CHAP TER 9 Rep or t ing S er vi ce s Enh an ce me nt s Altho ugh you c an man ually con gure th e prop er ties fo r the map a nd ea ch map layer, the easi es t way to get s ta r te d is to dra g a map fr om the T oolb ox window t o the re por t bo dy ( if you are u sing Bu sines s Inte lligen ce Deve lop ment Studio) or click the map in th [...]

  • Seite 199

    Reu s abi lit y CHAP TE R 9 1 79 Shared Datasets A share d dat as et allo ws you to d ene a qu er y once fo r reu se in many r ep or t s , much as yo u can cre ate a sha re d data so urce to de ne a re us able co nne ct ion s tring . Havin g shar ed dat a - set s availab le on th e ser ver al so he lps SQL S er ver 20 0 8 R2 Re po r t Buil de[...]

  • Seite 200

    180 CHA PT ER 9 Rep or ti ng S er vic es E nh an ce me nt s Se r v ic es ca n r es po nd to a r e po r t re qu es t f as t er, and u se r s a re ge ne ra lly hap pi er w it h th e re po r t ing s ys te m. Ho weve r, cach e s to ra ge i s n ot un limi te d. Per io dic all y, the c ac he e xp ire s and th e ne x t pe r s on t hat re qu es t s t he r [...]

  • Seite 201

    Reu s abi lit y CHAP TE R 9 181 Y ou c an pub lish re por t par ts bo th fro m Rep or t B uilde r 3.0 an d Repo r t D esign er in Bus ines s Intellige nce D evel opme nt Studio. In Rep or t Design er, the Repor t menu co ntain s the Publi sh Repo r t Par t s com mand . In the Publi sh Rep or t Par ts dial og box , sh own in Figur e 9 - 17 , you sel[...]

  • Seite 202

    182 CHA PT ER 9 Rep or ti ng S er vic es E nh an ce me nt s Altho ugh you c an pub lish re por t par t s in Re por t Desi gner an d Rep or t B uilde r 3.0 , you can on ly us e Rep or t B uilder 3 .0 to nd a nd us e thos e re por t par t s . Mor e inform ation ab out Repo r t Buil der 3 .0 ca n be fo und late r in this chapt er in the “ Rep or [...]

  • Seite 203

    Rep or t B uil de r 3.0 C HA PT ER 9 183 Report Builder 3.0 Repo r t Buil der 1 .0 was t he r st r el eas e of a rep or t develo pme nt tool t ar gete d for b usine ss use rs . Th at ver sio n res tr ic ted th e use r s to quer ies b ase d on a r epo r t m ode l and sup por ted limited r ep or t l ayout ca pabilit ies . Rep or t Builder 2. 0 was[...]

  • Seite 204

    184 CHA PT ER 9 Rep or ti ng S er vic es E nh an ce me nt s FIG UR E 9-19 The Re po r t Par t Gall er y Report Access and Management In this late st r ele ase of R epo r tin g Ser vice s, yo u can b en et f rom a fe w enh ancem ent s that improve a cces s to rep or ts an d to mana gem ent op erati ons in Re por t Mana ger, in addition to an addi[...]

  • Seite 205

    Repo r t Ac ce ss a nd M an ag em en t CH AP TER 9 185 FIG UR E 9-20 Repor t Vi ewe r Notice al so that th e Rep or t V iew er do es not in clude a link to o pen th e re por t prop er ties . Rather t han re quirin g you to op en a r epo r t r s t and th en nav igate to th e pro per ties p age s, Repo r t Man age r give s you dire c t acce ss to [...]

  • Seite 206

    186 CHA PT ER 9 Rep or ti ng S er vic es E nh an ce me nt s Repo r t Viewer I mprov ements The disp lay of re por ts is a lso imp rove d in the Rep or t Vie wer availab le in this r ele as e of SQL Ser ver, which now suppo r t s A JA X (A s y nchro nous Jav aScript a nd XML). If you ar e familiar w ith earli er ver si ons of Re por ting Se r v ices[...]

  • Seite 207

    Sha r ePoi nt Int eg ra tion CHAP TE R 9 187 SharePoin t Integr ation SQL Se r ver 2 0 0 8 R 2 Re p or ting Se r vic es con tinu es to im pr ove inte gr atio n w ith Sha re Poin t. In this re le as e , yo u n d b et ter o pti on s f or c on gur ing Sha re Point 2010 for us e w ith Rep o r ti ng Se r v ic es , wor ki ng wit h s cri pt s to aut[...]

  • Seite 208

    188 CHA PT ER 9 Rep or ti ng S er vic es E nh an ce me nt s cre ate th e d at a s o u r ce u s in g t h e Mi cr o s o f t Sh a r e Po in t L is t c o nn e c ti o n t yp e an d p r o vi d e cr e d e nt ia l s f or au th e nt ic at io n , yo u m us t s up pl y a c on n e c ti on s tr i ng to t he si te o r s ub si te in th e f o rm of a UR L that re [...]

  • Seite 209

    18 9 C H A P T E R 1 0 S e l f - S er v i c e A n a ly s i s w i t h P o w er P i v o t M any busine ss int ellige nce (B I) so lution s re quire a cces s to centrali zed , clean se d data in a data w areh ous e, an d ther e are m any goo d rea son s for an o rgani zat ion to continue to m aintain a d ata wa reh ous e for the se s oluti ons . The r[...]

  • Seite 210

    190 CHAP TER 10 Sel f-Ser vic e An al ys is w ith Po wer Piv ot Po werPiv ot for E xcel PowerPivo t for E xcel is a n add-in th at ex te nds t he fun c tiona lit y of E xcel 2010 to suppor t analy sis of la rge , rel ated da ta set s on yo ur comput er . Af ter in st alling th e add-in , you c an impor t dat a fro m ex te rnal d ata s ource s and i[...]

  • Seite 211

    Powe rPi vot f or E xc el CHA P TER 10 191 The A tom Data Feed Provider La st , the a dd-in in st alls an Atom dat a fe ed p rov ider to a llow you to imp or t data f rom Atom data f ee ds into a Power Pivot wor kb ook . A d ata f ee d prov ide s dat a to a client app licati on on re que st . The s tr uc ture r ema ins the s ame e ach time yo u re [...]

  • Seite 212

    192 CHAP TER 10 Sel f-Ser vic e An al ys is w ith Po wer Piv ot • IBM D B2 8.1 • Sybas e • Any dat aba se th at can b e acce ss ed by us ing an OL E DB pr ovide r or an OD BC dri ver ■ File s • De limite d tex t l es (.tx t , .tab, a nd .cs v) • File s fr om E xcel 97 thro ugh E xcel 2010 • Power Pivot wor kb ook s p ublish ed to a[...]

  • Seite 213

    Powe rPi vot f or E xc el CHA P TER 10 193 Link ed T a bles If your dat a is in an E xcel t abl e alrea dy, or if you conver t a range of d ata into an E xce l ta ble, you can a dd the t ab le to your wor kb oo k in the E xcel w indow an d the n use th e Create L inked T able b ut ton to imp or t the dat a into the Power Pivot win dow. Y ou c an ?[...]

  • Seite 214

    194 CHAP TER 10 Sel f-Ser vic e An al ys is w ith Po wer Piv ot Relati onships By buildin g re lation ships b et we en th e dat a, you c an ana ly ze th e data a s if it all c ame f rom a common s our ce. Rel ations hips en able yo u to use r elate d dat a in the s ame Pi votT ab le even though t he unde rl ying d ata a c tuall y comes f ro m dif f[...]

  • Seite 215

    Powe rPi vot f or E xc el CHA P TER 10 195 FIG UR E 10 -5 Filt er ing a n ume ric co lumn b y val ue IM P ORTANT Use o f a lte r is n ot a s ec uri t y me a sur e. A lth ou gh a lt er e f fe c tiv el y hid es dat a f rom a p re s ent a tion , a nyone w ho c an o pe n th e E xce l wor kb oo k ca n al so c le ar t he lte r s an d vi ew th e [...]

  • Seite 216

    196 CHAP TER 10 Sel f-Ser vic e An al ys is w ith Po wer Piv ot Y ou can use the H ide a nd Unh ide b utton on the Design tab ( show n in F igu re 1 0 -4) t o co ntr ol the a ppear anc e of a c olu mn i n t he P owerP ivo t wind ow an d also i n the Pivo tT able Field List. For exa mple , you mig ht choose t o dis play a co lum n in t he Power Piv [...]

  • Seite 217

    Powe rPi vot f or E xc el CHA P TER 10 197 stor e the dat a tha t you se lec te d fo r the char t. Jus t as yo u do with a s t anda rd PivotT able o r Pi vot Ch ar t , yo u se l e c t th e p l ac eh o ld e r a n d t he n us e th e as s o ci ate d  el d li s t t o s e l ec t a nd arra nge e lds f or the s ele c ted o bje c t, a s shown in F ig[...]

  • Seite 218

    198 CHAP TER 10 Sel f-Ser vic e An al ys is w ith Po wer Piv ot FIG UR E 10 -9 Th e CUBE VALUE fu nc tio n Sli cers The t ask p ane fo r PowerPi vot is similar to th e one yo u use fo r an E xcel PivotT abl e, but i t include s t wo ad dition al drop zo nes f or slice r s. Slicer s ar e a new f eatur e in E xcel 2010 that can be as so ciated w ith [...]

  • Seite 219

    Powe rPi vot f or E xc el CHA P TER 10 199 Data Analysi s Expressions The abili t y to comb ine dat a fr om multip le so urces into a s ingle Pivo tT abl e is ama zingl y pow- er f ul, bu t you ca n create e ven mo re p ower f ul re po r t s by enr iching the Powe rPivot d ata w ith Data A naly sis E xp ressio ns (DA X ) to a dd cus tom ag gre gati[...]

  • Seite 220

    20 0 CH AP TE R 10 Self-Ser vi ce A na ly sis w it h Powe rPi vot rep or t , pr ovid e a name f or the cur re nt PivotT a ble if you w ant, a nd the n spe cif y t he for mula for the m eas ure, a s show n in Figure 10 - 11. FIG UR E 10 - 11 Mea sur e se t tin gs D A X Functi ons The ex ample s show n for a c alculate d column a nd a me asur e are v[...]

  • Seite 221

    Powe rPi vot f or Sh ar e Poin t CHAP TE R 10 201 FUNC TI ON T YPE E X AM PLE DE SC RIP TI ON Statis tical =AVERAGEX(ResellerSales, [SalesAmount]- [TotalProductCost]) Evaluate s the ex pre ssio n in the se cond ar gument f or ea ch row of the t able in th e r st ar gume nt, an d then c alculat es the a rithm etic m ean T ex t =CONCATENATE([First[...]

  • Seite 222

    202 CHAP TER 10 Sel f-Ser vic e An al ys is w ith Po wer Piv ot ShareP oint Farm Web fr ont end Application server P owerPivot database Analysis Ser vices- V er tiPaq Mode Excel Calculation Ser vices P owerPivot System Service Excel W eb Access Excel W eb Ser vice P owerPivot Web Service Excel 2010 with P owerPivot - View or create r eports Browser[...]

  • Seite 223

    Powe rPi vot f or Sh ar e Poin t CHAP TE R 10 203 balan cing acro ss s er ver s for qu er y pro ces sing if mult iple s er ver s are avail able . Fur ther mor e, the Power Pivot Syste m Ser vic e mana ges th e conne c tion s for ac ti ve, r eus abl e, and c ache d conne c tion s to PowerPi vot work bo ok s , as we ll as admini str ative con nec ti [...]

  • Seite 224

    20 4 CH AP TE R 10 Self-Ser vi ce A na ly sis w it h Powe rPi vot Con tent Managem ent Content ma nage ment f or Power Pivot is quit e simple b ec aus e the dat a an d the pr es ent ation layout ar e kept in the s ame do cume nt. If th ey wer en’t , you woul d have to maint ain se para te les in dif fer ent for mat s and th en ma nually int eg[...]

  • Seite 225

    Powe rPi vot f or Sh ar e Poin t CHAP TE R 10 205 or right a rrow t o brin g a dif fe rent t humbnail into t he pr evie w are a. Y ou can a lso s wit ch to All Docum ent s vie w, which allows yo u to se e all the wo rkb oo k s in a st an dard do cume nt librar y view. Y o u can th en dow nloa d a docum ent , che ck docume nts in o r out , or p er f[...]

  • Seite 226

    20 6 CH AP TE R 10 Self-Ser vi ce A na ly sis w it h Powe rPi vot appl ica tion th at can co nne ct to Ana lysis Servic es d irectl y can use the Pow erP ivo t W eb Service . Y ou si mply u se t he S hare Po int URL for th e workb ook in ste ad o f a n Analy si s Se rvices server na me i n t he co nn ecti on st ring of the pro vider . For exa mpl e[...]

  • Seite 227

    Index 207 A adap te r bas e cla ss es , 151 Adap ter Fac tor y obj ec t s , 152 adap te rs , f or CEP app lic ati ons , 151 - 154 Admin Co ns ole , 122 aggr e gate f un c tio ns , 168 A JA X , 18 6 Ana ly sis S er vic es e ngin e, 1 23, 19 0 anno ta tin g tran sa c tio ns in M DS , 13 4 appl ic atio n er ro rs , m oni tor ing , 122 appl ic atio ns [...]

  • Seite 228

    20 8 dat a fe ed l ibra ri es , 205 Dat a Move me nt Se r vice ( DM S), 112 dat a ra ck s, 111 dat a so urce s joini ng , 166 - 168 for Pow erP ivot f or E xce l, 191 - 193 dat a st ewar ds , 12 7 dat a t y pe s supp or ted in Pa rall el D ata War eh ous e, 1 20 supp or ted in Po wer Pivo t, 195 dat a war eh ous e ap plia nce s , 109 - 110. Se e al[...]

  • Seite 229

    209 gue st m od el , 67 - 68 his tor y of, 6 4 - 65 tra diti ona l mo del , 65 tro ubl esh oo ting , 70 vali datin g pr er e quisi te s for, 68-7 0 fe edb ack o n bo ok , x ix le sp ace u tili za tion m on itor ing , 9 6 lte rin g op er atio n, 155 lte rin g Powe rPi vot da ta , 192, 194 for mat t ing r ep or ts d yna mic all y, 1 69- 17 0[...]

  • Seite 230

    210 over ut ilize d r es our ces , 92 pro ce ss or ut iliz at ion , 9 6 unde r utili ze d re so urce s , 92 vie win g, 91 Mana ge d In st an ces v ie wp oint ( Util it y E x plo re r), 95-1 0 0 mana ge me nt no de , 114 mana ge me nt uti liti es . Se e Be st Pr ac ti ces A nal y ze r (BPA); SQL S er ver U tilit y Mana ge me ntS er vi ce API , 163 m[...]

  • Seite 231

    21 1 Paral lel D at a Wareh ou se Admin Co ns ole , 122 archi te c tur e of, 109 - 115 auto mati c gro w th f eat ure , tog glin g, 118 con guri ng, 110 contr ol n od e, 112- 113 cre atin g ta ble s , 118- 120 dat a lo ad pr oc es sin g, 1 21 - 122 dat a t y pe s supp or ted i n, 1 20 DDL e x te nsi ons , 117 dis tri bute d s tra te gy, 11 6-11 [...]

  • Seite 232

    212 re por ts alte rn ate a cce ss ma ppi ngs , 187 ca che co ngur in g, 179- 18 0 on DAC depl oym en t, 5 4 dat a s ync hro niz ati on , 173 dat a vi sual iz atio n en han cem ent s , 175- 17 8 edi t se ss ion s, 18 3 exp or ting to Ato m dat a fe e d, 182 layou t , dy nami c, 169 - 170, 1 72- 173 namin g pa ge s in , 173 nes tin g it ems i n, [...]

  • Seite 233

    21 3 The ate r vi ew (Po wer Pivo t Gall er y), 20 4 -205 Tim e Inte llig en ce f unc ti ons ( Powe rPi vot), 193 T o pK op er ati on, 16 0 tran s ac tio n lo gs for MD S , 131 - 13 4 spa ce all oc ati on , 118 T r ans ac t-SQL f unc ti ons , 143- 14 4 tro ubl esh oo ting f ail over cl us ter in g, 70 tumb ling w ind ow s, 157- 158 U UCPs ( Util it[...]

  • Seite 234

    214 vir tuali z atio n. S ee also H yp er -V cons oli dat ion w ith , 87-88 tech no log y fo r, 7 2 Vi sual Stu dio 2010 dep loy ing DACs f ro m, 45- 46 impo r ti ng DACs into , 47 - 4 8 volum e sp ace ut iliz at ion m oni tor ing , 97 W Web br ows er s upp or t f or Re po r ti ng Se r vice s , 186 Web edi tio n, 13 win dow s, e ven t, 156 -1 59 Wi[...]

  • Seite 235

    215 About the Authors Ross Mis tr y is a techni cal ar chite ct a t the Micro sof t T echno log y Cente r (MTC) in Silicon Valley. Ross provi des e xecuti ve bri eng s, ar chite ct ural design s es sion s, a nd pr oof of con cept wo rk sh ops to or ganiz atio ns loc ate d in the Silicon Valley . His cor e spe cialt y is M icros of t SQ L Ser ver[...]

  • Seite 236

    S t a y i n t o u c h ! T o subscribe to the Micr osoft Press ® Book Connection Newsletter —for news on upcoming books, events, and special offers—please visit: W h a t d o y o u t h i n k o f t h i s b o o k ? We want to hear fr om you! T o participate in a brief online sur vey , please visit: T ell us how well this book meets your needs —w[...]