{"id":4,"date":"2012-02-15T20:44:04","date_gmt":"2012-02-15T19:44:04","guid":{"rendered":""},"modified":"2018-11-05T20:18:50","modified_gmt":"2018-11-05T19:18:50","slug":"8-nastrojov-ms-sql-server-2005","status":"publish","type":"post","link":"https:\/\/spireng.sk\/en\/8-nastrojov-ms-sql-server-2005\/","title":{"rendered":"8 n\u00e1strojov MS SQL Server 2005"},"content":{"rendered":"![\"Obr\u00e1zok\"](\"\/sites\/default\/files\/imagepicker\/1\/4567dfgd.jpg\")
MS SQL Server 2005 je pomerne ve\u013ek\u00fd a zlo\u017eit\u00fd kus softv\u00e9ru. Okrem z\u00e1kladnej funkcionality datab\u00e1zov\u00e9ho stroja, ktor\u00fd sa m\u00f4\u017ee profilova\u0165 a nastavova\u0165, obsahuje e\u0161te pridru\u017een\u00e9 slu\u017eby na reportovanie, integr\u00e1ciu d\u00e1t a anal\u00fdzu. Okrem toho m\u00e1 vyveden\u00e9 rozhrania na r\u00f4zne strany, a tak okrem \u0161tandardn\u00e9ho prij\u00edmania d\u00e1t cez zdie\u013ean\u00fa pam\u00e4\u0165 alebo TCP spojenie dok\u00e1\u017ee zverejni\u0165 sie\u0165ov\u00e9 slu\u017eby a odosiela\u0165 e-maily. Cel\u00fd ten kolos sa mus\u00ed vedie\u0165 nastavova\u0165, ladi\u0165 a diagnostikova\u0165. A to nie je v\u00f4bec jednoduch\u00e1 \u010dinnos\u0165. Aj preto u\u017e v z\u00e1kladnom bal\u00edku, ktor\u00fd sa nain\u0161taluje s SQL serverom, m\u00e1te nieko\u013eko programov, ktor\u00e9 t\u00fato \u00falohu pom\u00e1haj\u00fa zvl\u00e1da\u0165.<\/p>\n
Prv\u00fd n\u00e1stroj je najmocnej\u0161\u00ed zo v\u0161etk\u00fdch a m\u00e1 n\u00e1zov Microsoft SQL Server Management Studio<\/strong>. Pomocou tohto n\u00e1stroja viete pracova\u0165 hlavne s datab\u00e1zov\u00fdm strojom (database engine), ale zvl\u00e1da tie\u017e niektor\u00e9 konfigura\u010dn\u00e9 \u00falohy ako je nastavovanie Database Mail alebo spr\u00e1va SQL agenta. Jeho hlavnou \u00falohou je ale pr\u00e1ca s datab\u00e1zami, tabu\u013ekami, ladenie dotazov. Okrem okna na p\u00edsanie dotazov tu m\u00e1te grafick\u00fd n\u00e1vrh\u00e1r dotazu alebo \u0161trukt\u00fary datab\u00e1zy (nadizajnovan\u00e9 veci sa daj\u00fa samozrejme vyskriptova\u0165 do s\u00faborov). Pozor: V produk\u010dnej prev\u00e1dzke by ste nemali nad tabu\u013ekou pou\u017e\u00edva\u0165 pr\u00edkaz Open Table, preto\u017ee to otvor\u00ed s update c<\/span>ursor-om<\/span><\/span>, \u010do znamen\u00e1 \u013eahk\u00e9 prep\u00edsanie produk\u010dn\u00fdch \u00fadajov.<\/span><\/p>\nDruh\u00fd n\u00e1stroj v porad\u00ed m\u00e1 n\u00e1zov <\/span>Business Intelligence Development Studio<\/strong>. Na rozdiel od MS SQL Server Managament Studio, ktor\u00e9 je postaven\u00e9 na Visual Studio Shell-y, tento n\u00e1stroj je vlastne in\u0161tal\u00e1cia Visual Studia + potrebn\u00fdch \u0161abl\u00f3n. Jeho hlavnou \u00falohou je vytv\u00e1ra\u0165 d\u00e1ta pre reportovaciu, integra\u010dn\u00fa a analytick\u00fa slu\u017ebu. Pod d\u00e1tami mysl\u00edm s\u00fabory, <\/span>ktor\u00e9 s\u00fa tieto slu\u017eby schopn\u00e9 spracova\u0165 a na z\u00e1klade nich kona\u0165<\/span><\/span>. Je to teda hlavne v\u00fdvoj\u00e1rsky n\u00e1stroj.<\/span><\/p>\nTret\u00edm je <\/span>SQL Server Configuration Manager<\/strong>. Je to program na zap\u00ednanie a vyp\u00ednanie slu\u017eieb a protokolov. Ni\u010d viac a ni\u010d menej. Je teda dobr\u00fd napr\u00edklad, ak chcete zastavi\u0165 cel\u00fd server. Alebo ak chcete povoli\u0165 TCP komunik\u00e1ciu, ktor\u00e1 je vo <\/span>v\u00fdchodiskovom<\/span><\/span> nastaven\u00ed zak\u00e1zan\u00e1.<\/span><\/p>\nPravdepodobne pod pa\u013ebou kritiky oh\u013eadom bezpe\u010dnosti sa zrodil n\u00e1stroj <\/span>SQL Server Surface Area Configuration<\/strong>. Jeho n\u00e1zov je naozaj p<\/span>r\u00edslove\u010dn<\/span><\/span>\u00fd, preto\u017ee sa star\u00e1 o povrch, teda vonkaj\u0161ie rozhranie servera. Je to n\u00e1stroj, ktor\u00e9ho hlavnou \u00falohou je pod\u013ea dokument\u00e1cie a popisov, ktor\u00e9 obsahuje, zakazova\u0165 slu\u017eby a funk\u010dnosti servera (aj ke\u010f rovnako \u013eahko ich vie aj povo\u013eova\u0165). Mal by sa sp\u00fa\u0161\u0165a\u0165 po in\u0161talovan\u00ed na kontrolu a vypnutie toho, \u010do nie je potrebn\u00e9. Okrem vypnutia\/zapnutia slu\u017eieb (mierne prekrytie funkcionality s predch\u00e1dzaj\u00facim n\u00e1strojom) v \u0148om, viete vyp\u00edna\u0165\/zap\u00edna\u0165 funk\u010dnosti datab\u00e1zov\u00e9ho stroja, ako napr\u00edklad Ad hoc Distributed Queries, Common Language Runtime Integration (CLR), Dedicated Administrator Connection (DAC), Database Mail a pod. Len dod\u00e1m, \u017ee m\u00e1 aj svoj command-line dvoj\u010da s menom <\/span>sac<\/strong>. Je zauj\u00edmav\u00e9, \u017ee tento n\u00e1stroj sa objavil vo verzii 2005 a vo verzii 2008 op\u00e4\u0165 zmizol (jeho funk\u010dnos\u0165 sa zaintegrovala do in\u00fdch).<\/span><\/p>\n\u010eal\u0161\u00edm pomocn\u00edkom, ktor\u00fd stoj\u00ed za slovo, je <\/span>SQL Server Profiler<\/strong>. Ako jeho n\u00e1zov napoved\u00e1, je ur\u010den\u00fd na profilovanie. Je to v podstate len nadstavba grafick\u00e9ho rozhrania nad funkcionalitou SQL trace, ktor\u00e1 je obsiahnut\u00e1 v datab\u00e1zovom stroji. Jeho hlavnou \u00falohou je konfigurova\u0165 vytv\u00e1ranie tak\u00fdchto SQL trace-ov, ktor\u00e9 predstavuj\u00fa v\u00fdsledok profilovania dotazov.<\/span><\/p>\nAk u\u017e m\u00e1te nejak\u00fd SQL trace vytvoren\u00fd, tak ho potrebujete spracova\u0165 a na to je tu <\/span>Database Engine Tuning Advisor<\/strong>. Op\u00e4\u0165 celkom intuit\u00edvny n\u00e1zov, ktor\u00fd napoved\u00e1, \u017ee tento n\u00e1stroj je na anal\u00fdzu toho, \u010do sa deje v SQL serveri a odpor\u00fa\u010danie, \u010do by sa dalo vylep\u0161i\u0165. Ako vstup m\u00f4\u017ee by\u0165 SQL trace alebo T-SQL dotaz. Okrem toho vie n\u00e1stroj simulova\u0165 Load testy, a pritom do v\u00fdstupn\u00fdch s\u00faborov zapisova\u0165 info <\/span>o <\/span><\/span>deadlock-och a dotazov\u00fdch pl\u00e1noch (query plan).<\/span><\/p>\nSiedmym n\u00e1strojom v porad\u00ed je <\/span>SQLcmd<\/strong>. Je to command-line n\u00e1stroj na sp\u00fa\u0161\u0165anie T-SQL dotazov. To samo o sebe je dos\u0165, preto\u017ee umo\u017e\u0148uje efekt\u00edvnu automatiz\u00e1ciu. Okrem toho umo\u017e\u0148uje spojenie cez Dedicated Administrator Connection, \u010do m\u00f4\u017ee by\u0165 n\u00e1stroj poslednej z\u00e1chrany pri pre\u0165a\u017eenom serveri.<\/span><\/p>\nPosledn\u00fd, \u00f4smi program je <\/span>BCP<\/strong>. Je to op\u00e4\u0165 command-line n\u00e1stroj, ktor\u00fd je hlavne ur\u010den\u00fd na export a import \u00fadajov zo s\u00faborov. Zvl\u00e1da oby\u010dajn\u00e9 textov\u00e9 s\u00fabory, ako aj XML s\u00fabory. Okrem toho vie vyexprotova\u0165 v\u00fdsledok T-SQL dotazu.<\/span><\/p>\n\u00a0<\/span><\/p>\nTak\u017ee e\u0161te jedno zhrnutie ako bodka na z\u00e1ver.<\/p>\n
N\u00e1stroje pre v\u00fdvoj:<\/span><\/p>\n\n- \n
Microsoft SQL Management Studio \u2013 stavba datab\u00e1zy, p\u00edsanie dotazov<\/p>\n<\/li>\n
- Business Intelligence Development Studio \u2013 s\u00fabory pre reportovaciu, integra\u010dn\u00fa a analytick\u00fa slu\u017ebu<\/li>\n<\/ul>\n
N\u00e1stroje na konfigurovanie:<\/span><\/p>\n\n- \n
SQL Server Configuration Manager \u2013 zap\u00ednanie a vyp\u00ednanie slu\u017eieb a protokolov<\/p>\n<\/li>\n
- \n
SQL Server Surface Area Configuration \u2013 zap\u00edsanie a vyp\u00ednanie slu\u017eieb a r\u00f4znych funkci\u00ed datab\u00e1zov\u00e9ho stroja<\/p>\n<\/li>\n
- \n
Microsoft SQL Management Studio \u2013 logy, nastavovanie SQL agenta, database mail …<\/p>\n<\/li>\n<\/ul>\n
Pr\u00e1ca so \u0161trukt\u00farou datab\u00e1zy a \u00fa<\/span>dajmi:<\/span><\/p>\n\n- \n
Microsoft SQL Management Studio \u2013 stavba datab\u00e1zy, p\u00edsanie dotazov<\/p>\n<\/li>\n
- \n
SQLcmd \u2013 sp\u00fa\u0161\u0165anie skriptov na pr\u00edkazovom riadku<\/p>\n<\/li>\n
- \n
BCP \u2013 export a import \u00fadajov zo s\u00faborov<\/p>\n<\/li>\n<\/ul>\n
Ladenie:<\/span><\/p>\n\n- \n
SQL Server Profiler<\/p>\n<\/li>\n
- \n
Database Engine Tuning Advisor<\/p>\n<\/li>\n<\/ul>","protected":false},"excerpt":{"rendered":"
MS SQL Server 2005 je pomerne ve\u013ek\u00fd a zlo\u017eit\u00fd kus softv\u00e9ru. Okrem z\u00e1kladnej funkcionality datab\u00e1zov\u00e9ho stroja, ktor\u00fd sa m\u00f4\u017ee profilova\u0165 a nastavova\u0165, obsahuje e\u0161te pridru\u017een\u00e9 slu\u017eby na reportovanie, integr\u00e1ciu d\u00e1t a anal\u00fdzu. Okrem toho m\u00e1 vyveden\u00e9 rozhrania na r\u00f4zne strany, a tak okrem \u0161tandardn\u00e9ho prij\u00edmania d\u00e1t cez zdie\u013ean\u00fa pam\u00e4\u0165 alebo TCP spojenie dok\u00e1\u017ee zverejni\u0165 sie\u0165ov\u00e9 […]<\/p>","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17],"tags":[],"class_list":["post-4","post","type-post","status-publish","format-standard","hentry","category-databazy"],"_links":{"self":[{"href":"https:\/\/spireng.sk\/en\/wp-json\/wp\/v2\/posts\/4","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/spireng.sk\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/spireng.sk\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/spireng.sk\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/spireng.sk\/en\/wp-json\/wp\/v2\/comments?post=4"}],"version-history":[{"count":2,"href":"https:\/\/spireng.sk\/en\/wp-json\/wp\/v2\/posts\/4\/revisions"}],"predecessor-version":[{"id":326,"href":"https:\/\/spireng.sk\/en\/wp-json\/wp\/v2\/posts\/4\/revisions\/326"}],"wp:attachment":[{"href":"https:\/\/spireng.sk\/en\/wp-json\/wp\/v2\/media?parent=4"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/spireng.sk\/en\/wp-json\/wp\/v2\/categories?post=4"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/spireng.sk\/en\/wp-json\/wp\/v2\/tags?post=4"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}