Obsah. 1.1 Klienti SQL Serveru. 1.1 Klienti SQL Serveru SQL Server agent Nastavení databáze Systémový katalog

Obsah 1

2

Z´akladn´ı pr´ace

1

1.1

Klienti SQL Serveru . . . . . . . . . . . . . . . . . . . . . . . .

1

1.2

SQL Server agent . . . . . . . . . . . . . . . . . . . . . . . . .

2

Nastaven´ı datab´aze

3

2.1

Nastaven´ı pamˇeti . . . . . . . . . . . . . . . . . . . . . . . . .

4

2.2

Dalˇs´ı nastaven´ı . . . . . . . . . . . . . . . . . . . . . . . . . .

4

2.3

Nastaven´ı opr´avnˇen´ı - pravidlo nejmenˇs´ıho pˇr´ıstupu . . . .

4

2.4

Syst´emovy´ katalog . . . . . . . . . . . . . . . . . . . . . . . .

5

3

Soubory a Filegroups

6

4

Z´aloha a zotaven´ı

6

´ zba datab´aze 5 Udrˇ 5.1 6

8

Nastaven´ı opr´avnˇen´ı . . . . . . . . . . . . . . . . . . . . . . .

9

Uˇzivatel´e datab´aze

9

6.1

Uˇzivatel´e syst´emu Windows . . . . . . . . . . . . . . . . . . .

9

6.2

Uˇzivatel´e SQL Serveru . . . . . . . . . . . . . . . . . . . . . .

10

6.3

Opr´avnˇen´ı . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

10

1 1.1

Z´akladn´ı pr´ace Klienti SQL Serveru

Pˇri pr´aci s SQL Serverem budeme pouˇz´ıvat dva klienty: 1

SQL Server Management Studio (SSMS) - GUI pro konfiguraci SQL Serveru. Spouˇst´ı se z nab´ıdky start, nebo zad´an´ım ssms.exe do pˇr´ıkazov´eho rˇ a´ dku. ˇ adkovy´ procesor SQL Serveru. Umoˇznuje ˇ SQLCMD - R´ spouˇstˇet skripty ˚ ze byt a pos´ılat pˇr´ıkazy na SQL Sever. Muˇ ´ uˇziteˇcny´ pokud chceme spouˇstˇet nˇejak´e pˇr´ıkazy z pˇr´ıkazov´eho rˇ a´ dku, nebo pokud SQL Server pˇrestane odpov´ıdat. ˚ zeme vyuˇz´ıt na spuˇstˇen´ı uloˇzen´eho sql scriptu: SQLCMD muˇ sqlcmd -S <JmenoServeru>\<JmenoInstance> -i -o

˚ Zastaven´ı instance SQL Serveru je moˇzn´e prov´est nˇekolika zpusoby: • Zastaven´ı sluˇzby MSSQLServer a SQLServerAgent. • Stop v SSMS po kliknut´ı pravym ´ tlaˇc´ıtkem na instanci. • Pˇr´ıkaz SHUTDOWN v sqlcmd. • Pˇr´ıkaz pˇr´ıkazov´e rˇ a´ dky: net stop mssqlserver. Spuˇstˇen´ı instance SQL Serveru: • Spuˇstˇen´ı sluˇzby MSSQLServer a SQLServerAgent. • Start v SSMS po kliknut´ı pravym ´ tlaˇc´ıtkem na instanci. • Pˇr´ıkazy pˇr´ıkazov´e rˇ a´ dky: net start mssqlserver, net start mssqlagent. ´ Informace o serveru, instanc´ıch, datab´az´ıch, provedenych uloh´ ach a ´ auditech lze nal´ezt v n´astroji Log file viewer. Je moˇzn´e se do nˇej dostat napˇr´ıklad v oknˇe Object explorer ze z´aloˇzky Management ⇒ SQL server log ˚ zit´e ud´alosti po dvojkliku na nˇektery´ log file. Zde m´ame zobrazeny duleˇ insatnce (datab´aze).

1.2

SQL Server agent

´ Jedn´a se o sluˇzbu, kter´a prov´ad´ı spouˇstˇen´ı ukol u˚ vytvoˇrenych v r´amci ´ ˚ ze tak´e monitorovat datab´azi a upozornit adSSMS. SQL Server agent muˇ ˚ e ud´alosti v datab´azi. Aby bylo moˇzn´e sluˇzbu vyuˇz´ıvat ministr´atora na ruzn´ 2

je nutn´e ji m´ıt spuˇstˇenu (implicitnˇe je tato sluˇzby zastavena). Jej´ı spuˇstˇen´ı je mimo jin´e moˇzn´e prov´est i SSMS (pokud na to m´a pˇrihl´asˇ eny´ uˇzivatel opravnˇen´ı) v object exploreru. ´ Pro manipulaci s ulohami SQL Server agenta je nutn´e aby uˇzivatel mˇel nˇekterou z pˇreddefinovanych ´ rol´ı: SQLAgentUserRole, SQLAgentReaderRole, SQLAgentOperatorRole nebo mus´ı byt ´ pˇr´ımo v roli sysamin. Role jsou zde seˇrazeny od nejmenˇs´ıho opr´avnˇen´ı.

2

Nastaven´ı datab´aze

Podrobn´e informace lze nal´ezt v manu´alovych ´ str´ank´ach k SQL Serveru 1 . ˇ SQL Server umoˇznuje nastavit z´akladn´ı parametry instance serveru v oknˇe SQL Server properties, kam je moˇzn´e se dostat v MS SQL Server man˚ zeme nastavit z´akladn´ı parametry agement studiu (SSMS). V tomto oknˇe muˇ instance. ˚ SQL Server dvˇe kategorie parametru: • Parametry jenˇz jsou nastaveny okamˇzitˇe po nastaven´ı a spuˇstˇen´ı RECONFIGURE (RECONFIGURE WITH OVERRIDE) • Parametry jenˇz jsou nastaveny aˇz po restartu instance. Parametry jenˇz patˇr´ı mezi pokroˇcil´e nastaven´ı (str´anka advanced v Server properties) je moˇzn´e nastavovat jen pokud je nastaven parametr show advanced options na 1. Tento parametr bychom po nastaven´ı ˚ pˇr´ısluˇsn´eho pokroˇcil´eho nastaven´ı mˇeli vr´atit na puvodn´ ı hodnotu. Nastaven´ı parametru˚ SQL Serveru je moˇzn´e zobrazit v pohledu sys.configurations. U kaˇzd´eho parametru jsou dvˇe hodnoty: atribut value a value in use. Prvn´ı ud´av´a aktu´alnˇe nastavenou hodnotu parametru a druh´a ud´av´a hodnotu, kter´a se aktu´alnˇe v instanci (ˇci datab´azi) pouˇz´ıv´a. 1

http://msdn.microsoft.com/en-us/library/ms189631.aspx

3

2.1

Nastaven´ı pamˇeti

˚ zeme adresovat i v´ıce pamˇeti neˇz 2GB v pˇr´ıpadˇe Za urˇcitych ´ podm´ınek muˇ ˚ Pokud v boot.ini zapneme /3GB omez´ıme velikost 32-bitovych ´ poˇc´ıtaˇcu. windows na 1GB a umoˇzn´ıme SQL Serveru adresovat aˇz 3GB. Pokud nav´ıc zapneme /PAE umoˇzn´ı n´am windows adresovat aˇz 16GB. No a posledn´ı ˚ zeme adresovat pamˇet’ aˇz moˇznost je zapnout jen /PAE, v tom pˇr´ıpadˇe muˇ 64GB. Aby tuto moˇznost adresov´an´ı pamˇeti nad 4GB SQL Server pouˇz´ıval je jeˇstˇe nastavit parametr awe enable na 1. Nav´ıc je potˇreba uˇzivateli, ktery´ spouˇst´ı SQL Server sluˇzbu d´at pr´avo ’Lock Pages in Memory’ pomoc´ı Local group policy editoru. Toto se prov´ad´ı ˚ z toho duvodu, aby operaˇcn´ı syst´em pamˇet’ nad 4GB neukl´adal automaticky na disk. U 64-bitovych syst´emu˚ je toto nastaven´ı dobrovoln´e, ale i ´ ˚ pˇresto se vˇetˇsinou doporuˇcuje. Je to zejm´ena z toho duvodu, aby nedoch´azelo ke swapov´an´ı str´anek operaˇcn´ım syst´emem v pˇr´ıpadˇe nedostatku pamˇeti ˚ Pokud jsme takto nastavili (napˇr´ıklad pˇri kop´ırov´an´ı velkych souboru). ´ Windows uˇzivatele, pak je nutn´e tak´e nastavit maxim´aln´ı hodnotu pamˇeti ˚ SQL Serveru. Je to z toho duvodu, aby nedoch´azelo k vyalokov´an´ı cel´e dostupn´e pamˇeti, coˇz by mohlo v´est i k nestabilitˇe OS.

2.2

Dalˇs´ı nastaven´ı

Popis nˇekterych ´ dalˇs´ı parametru˚ lze vidˇet v tabulce 1.

2.3

Nastaven´ı opr´avnˇen´ı - pravidlo nejmenˇs´ıho pˇr´ıstupu

Zde uvedeme nˇekolik z´akladn´ıch postupu˚ k nastaven´ı pˇr´ıstupu˚ jednotlivych ´ cˇ a´ sti SQL Serveru k OS tak, aby jsme postupovali dle pravidla nejmenˇs´ıho pˇr´ıstupu. Pomoc´ı n´astroje SQL Server configuration manager je moˇzn´e nastavit uˇzivatele pˇres kter´e se spouˇst´ı jednotliv´e sluˇzby. Pˇriˇrazen´ım uˇzivatele sluˇzbˇe po˚ s opr´avnˇen´ımi, jelikoˇz configmoc´ı tohoto n´astroje pˇredejdete probl´emum uration manager implicitnˇe nastav´ı potˇrebn´a pr´ava. Pro lepˇs´ı granularitu rˇ´ızen´ı pˇr´ıstupu je dobr´e vytvoˇrit pro kaˇzdou sluˇzbu oddˇelen´e uˇzivatele. 4

Jm´eno parametru Priority boost

Popis ˚ ze zvyˇ Muˇ pro´ sit prioritu SQL Serveru pˇred jinymi ´ cesy bˇezˇ ´ıc´ımi na windows. U dobˇre vyladˇen´eho serveru pˇredstavuje minim´aln´ı zrychlen´ı. Obecnˇe se nedoporuˇcuje nastavovat. Max worker threads Pˇri nenulov´e hodnotˇe je vytvoˇren pool vl´aken, kde ˚ ze doch´azet k optimalizaci pˇri pr´aci s vl´akny. muˇ (doporuˇcovan´a hodnota je nad 256) Recovery interval Definuje maxim´aln´ı dobu v minut´ach, kterou by mˇelo trvat zotaven´ı datab´aze. Tato hodnota se doporuˇcuje nastavit pokud detekujeme probl´emy s cˇ astymi checkpointy. ´ Fill factor Ud´av´a zaplnˇen´ı indexu v pˇr´ıpadˇe jeho vytvoˇren´ı cˇ i znovuvytvoˇren´ı (rebuild). ˇ v sekund´ach po ktery´ ma SQL Server cˇ ekat na Query wait Cas vykon´an´ı SQL pˇr´ıkazu. User connections Maxim´aln´ı poˇcet paraleln´ıch pˇripojen´ı k datab´azi. ˚ ze pomoci pˇri pˇretˇezˇ ov´an´ı datab´aze. Muˇ Tabulka 1: Parametry SQL Server instance ´ Ukoly vytvoˇren´e pro n´astroj SQL Server agent potˇrebuj´ı cˇ asto nastavit ˚ mimo SQL Server. opr´avnˇen´ı k pˇr´ıstupu ke zdrojum

2.4

Syst´emovy´ katalog

Nˇekter´e pohledy syst´emov´eho katalogu jsou vyps´any v tabulce 2. Podrobn´e informace lze nal´ezt v manu´alovych ´ str´ank´ach k SQL Serveru 2 . ˚ zeme nahl´ızˇ et tak´e pomoc´ı uloˇzenych Do syst´emov´eho katalogu muˇ ´ procedur. SQL Server obsahuje celou takovych ´ procedur jeˇz vracej´ı informace o datab´azi a instanci a pomoc´ı kterych ´ je moˇzn´e manipulovat s nastaven´ım (viz. tabulka 3). Podrobn´e informace lze nal´ezt v manu´alovych ´ str´ank´ach k SQL Serveru 3 . 2 3

http://msdn.microsoft.com/en-us/library/ms174365.aspx http://msdn.microsoft.com/en-us/library/ms187961.aspx

5

Jm´eno pohledu Popis sys.configurations Obsahuje informace o nastaven´ı jednotlivych ´ parametru˚ instance. sys.traces Obsahuje traces beˇz´ıc´ı na dan´e instanci sys.sysfilegroups Filegroups v datab´azi Tabulka 2: Pohledy SQL Server instance Jm´eno procedury Popis sp configure Nastaven´ı jednotlivych parametru˚ instance a ´ datab´aze. sp monitor Vrac´ı statistiky SQL Serveru sp who Vrac´ı seznam uˇzivatelu˚ pˇripojenych ´ k datab´azi sp spaceused Odhad velikosti pln´e z´alohy Tabulka 3: Uloˇzen´e procedury SQL Server instance

3

Soubory a Filegroups

Podrobn´e informace o filegroups lze nal´ezt v manu´alovych str´ank´ach k ´ 4 SQL Serveru . Filegroups dan´e datab´aze je moˇzn´e vytv´arˇ et v database ˚ zeme vytv´arˇ et datov´e soubory, kde specifikujeme jejich properties. D´ale muˇ ˚ um´ıstˇen´ı, relaci na filegroup a granularitu rustu souboru.

4

Z´aloha a zotaven´ı

Z´alohu i zotaven´ı je moˇzn´e prov´adˇet jak pomoc´ı pˇr´ıkazu˚ T-SQL5 tak s pomoc´ı SSMS6 . Nejprve vyzkouˇs´ıme jednotliv´e pˇr´ıkazy T-SQL: • Pln´a z´aloha datab´aze se prov´ad´ı pˇr´ıkazem: BACKUP DATABASE <jmeno database> TO DISK = N’cesta ciloveho souboru’ WITH INIT 4

http://msdn.microsoft.com/en-us/library/ms179316.aspx http://msdn.microsoft.com/en-us/library/aa337534.aspx 6 http://msdn.microsoft.com/en-us/library/ms189621.aspx 5

6

Zotaven´ı datab´aze ze z´alohy se provede: RESTORE DATABASE <jmeno database> FROM DISK = N’cesta k souboru zalohy’ WITH REPLACE • Rozd´ılovou z´alohu datab´aze, kter´a se prov´ad´ı vzhledem k posledn´ı pln´e z´aloze, lze spustit: BACKUP DATABASE <jmeno database> TO DISK = N’cesta ciloveho souboru’ WITH DIFFERENTIAL, INIT Zotaven´ı datab´aze ze z´alohy s jednou rozd´ılovou z´alohou se provede ve dvou kroc´ıch. Nejprve se nakop´ıruje pln´a z´aloha: RESTORE DATABASE <jmeno database> FROM DISK = N’cesta k souboru plne zalohy’ WITH NORECOVERY, REPLACE GO Pot´e se pokraˇcuje aplikov´an´ım rozd´ılov´e z´alohy: RESTORE DATABASE <jmeno database> FROM DISK = N’cesta k souboru rozdilove zalohy’ GO • Archivaci logu prov´ad´ıme pˇr´ıkazem: BACKUP LOG <jmeno database> TO DISK = N’cesta ciloveho souboru’ WITH INIT Pˇri zotaven´ı ze z´alohy s vyuˇzit´ım archivovanych ´ logu˚ mus´ıme vˇzdy nejprve prov´est z´alohu aktu´aln´ıho logu a teprve po t´e prov´adˇet zotaven´ı. Zotaven´ı s vyuˇzit´ım archivovan´eho logu se provede n´asleduj´ıc´ım pˇr´ıkazem: RESTORE LOG <jmeno database> FROM DISK = N’cesta k souboru archivovaneho logu’

Pˇri pouˇzit´ı SSMS je moˇzn´e nastavit tˇri z´akladn´ı modely zotaven´ı: 7

• Model pln´eho zotaven´ı znamen´a, zˇ e se bude prov´adˇet z´aloha datovych ´ souboru˚ i z´aloha redo logu. Redo log soubor je uchov´av´an dokud nen´ı provedena jeho archivace. • Model jednoduch´eho zotaven´ı, znamen´a, zˇ e se neprov´ad´ı z´aloha redo logu a star´e z´aznamy jsou postupnˇe pˇrepisov´any. • Model pln´eho zotaven´ı mimo bulk operac´ı, kde nedoch´az´ı k logov´an´ı ˚ ze doj´ıt ke ztr´atˇe tˇechto bulk operac´ı. V pˇr´ıpadˇe chyby m´edia tedy muˇ informac´ı. ˚ Modely definuj´ı zpusob manipulace s redo log souborem a tak´e jak´e ˚ zpusoby zotaven´ı jsou podporov´any v datab´azi. Toto nastaven´ı tedy samo o sobˇe nepˇredstavuje zˇ a´ dn´e zabezpeˇcen´ı datab´aze. Dalˇs´ı nastaven´ı z´alohy ´ zbu datab´aze (maintenance plans) je moˇzn´e prov´est pomoc´ı pl´anu˚ na udrˇ jenˇz jsou pops´any v kapitole 5.

´ zba datab´aze 5 Udrˇ ´ ´ zby datab´aze je moˇzn´e vyuˇz´ıt MainPro vytv´arˇ en´ı jednotlivych udrˇ ´ uloh ˚ zeme spustit po prav´em kliknut´ı myˇsi na tenance Plan Wizard, ktery´ muˇ Maintenance plans v SSMS. Dalˇs´ı moˇznost´ı je vytvoˇren´ı nov´eho pl´anu (New ˚ zeme definovat podpl´any (subplan), kter´e Maintanace Plan). V pl´anu muˇ ´ pˇredstavuj´ı mnoˇzinu uloh, kter´e maj´ı byt ´ spouˇstˇeny v urˇcitou dobu. Jakmile vytvoˇr´ıme novy´ podpl´an, specifikujeme cˇ as a frekvenci jeho spouˇstˇen´ı ´ ˚ zeme vkl´adat ulohy. ´ a po t´e do nˇej muˇ Ulohu do podpl´anu vloˇz´ıme jednoduˇse ´ pˇretaˇzen´ım z toolboxu Maintenance Plan Tasks. Jakmile je ukol vytvoˇren je potˇreba na nˇej dvakr´at kliknout a specifikovat jeho podrobnosti. Vˇsechny ´ takto vytvoˇren´e ukoly se spouˇstˇej´ı sluˇzbou SQL Server agent, kter´a tedy mus´ı byt ´ tak´e spuˇstˇena. ´ Takto vytvoˇren´e ulohy je moˇzn´e nal´ezt v z´aloˇzce jobs SQL Server agenta. ´ Ulohu je mimo jin´e moˇzn´e vytvoˇrit i zde. ´ Log file viewer, ktery´ zobraz´ı informace o spuˇstˇenych a pl´anech ´ ukolech je moˇzn´e otevˇr´ıt po prav´em kliknut´ı na Maintenance plans v SSMS a po vybr´an´ı view history. 8

5.1

Nastaven´ı opr´avnˇen´ı

´ Kaˇzd´a vytvoˇren´a uloha se spouˇst´ı s pr´avy uˇzivatele, ktery´ ji vytvoˇril. SQL Server obsahuje nˇekolik subsyst´emu, kter´e vyˇzaduj´ı rozˇs´ırˇ en´a opr´avnˇen´ı. Mezi takov´eto subsyst´emy patˇr´ı napˇr´ıklad Integration services, nebo Pow˚ erShell. Abychom nemuseli uˇzivatelum, kteˇr´ı potˇrebuj´ı tyto subsyst´emy ´ cet proxy. vyuˇz´ıvat, d´avat roli sysadmin, je nutn´e vytvoˇrit uˇ Pro nastaven´ı pˇr´ıstupu k subsyst´emu s pomoc´ı proxy: • Nejprve vytvoˇr´ıte v OS uˇzivatele. K tomu vyuˇzijte n´astroj Computer management v Administrative tools. • Tomuto uˇzivateli vytvoˇr´ıte povˇerˇ en´ı v z´aloˇzce Security ⇒ Credentials. • V z´aloˇzce SQL Server agent ⇒ Proxies, vytvoˇr´ıte po kliknut´ı pravym ´ tlaˇc´ıtkem novou proxy, kter´e pˇriˇrad´ıte povˇerˇ en´ı vytvoˇren´e v pˇredchoz´ım kroku. Vyberete subsyst´emy, ke kterym ´ tato proxy umoˇzn´ı pˇristupovat ˚ zete pˇriˇradit uˇzivatele (principals), jenˇz a nakonec do t´eto proxy muˇ budou moci tuto proxy vyuˇz´ıt k pˇr´ıstupu k subsyst´emu.

6

Uˇzivatel´e datab´aze

´ ctu, ˚ jenˇz muˇ ˚ zeme pˇri pˇrihl´asˇ en´ı k V z´asadˇe jsou dva druhy uˇzivatelskych ´ uˇ SQL Serveru vyuˇz´ıt: Uˇzivatel´e syst´emu Windows a Uˇzivatel´e SQL Serveru. ˚ zeme pˇriˇrazovat do rol´ı, jenˇz n´am zjednoduˇssˇ´ı Jednotliv´e uˇzivatele pak muˇ ˚ Z´akladn´ı rol´ı je sysadmin, coˇz pˇredstavuje roli spr´avu opr´avnˇen´ı uˇzivatelu. administr´atora SQL Serveru. Podobnˇe jako v Oracle zde najdeme tak´e roli public, kter´a je implicitnˇe pˇriˇrazena kaˇzd´emu uˇzivateli.

6.1

Uˇzivatel´e syst´emu Windows

Do SQL Server je moˇzn´e se pˇrihl´asit jako uˇzivatel OS windows, pokud m´a uˇzivatel v SQL Serveru vytvoˇreno jm´eno. Jm´eno pro uˇzivatele je moˇzn´e vytvoˇrit pˇr´ıkazem CREATE LOGIN <jmeno uzivatele> FROM WINDOWS 9

WITH DEFAULT DATABASE = <jmeno database>, nebo s pouˇzit´ım SSMS v z´aloˇzce Security ⇒ Logins.

6.2

Uˇzivatel´e SQL Serveru

Tito uˇzivatel´e nemus´ı byt ´ pˇrihl´asˇ en´ı do OS aby se mohli pˇrihl´asit do SQL Serveru. Je moˇzn´e je vytvoˇrit pˇr´ıkazem CREATE LOGIN <jmeno uzivatele> WITH PASSWORD = , WITH DEFAULT DATABASE = <jmeno database>.

6.3

Opr´avnˇen´ı

˚ ym ˚ doch´az´ı s pomoc´ı pˇr´ıkazu GRANT Opr´avnˇen´ı k pˇr´ıstupu k ruzn ´ zdrojum ON [::] TO . M´ısto uˇzivatele ˚ zeme uv´est tak´e nˇejakou roli, kter´e pr´avo pˇridˇelujeme. Obecnˇe plat´ı, zˇ e muˇ ´ jednoduˇssˇ´ı je spr´ava opr´avnˇen´ı na urovni role, neˇz uˇzivatele.

10