ubi

Okidači
Okidač je specijalna vrsta snimljene procedure koja se ispaljuje kao reakcija na specifične događaje.
Postoje dvije vrste okidača:
DDL (Data Definition Language) okidači i
DML (Data Manipulation Language) okidači.
DDL okidači se ispaljuju ako neko na neki način mijenja strukturu baze podataka (CREATE, ALTER, DROP i slične naredbe). Oni su od kritičnog značaja za neke instalacije (pogotovo za instalacije visoke sigurnosti), ali područje primjene je prilično usko. ( ekstremno praćenje promjena/istorije strukture baze podataka).
DML okidači su dijelovi koda koji se vezuju za konkretnu tabelu ili prikaz. Za razliku od snimljenih procedura, kod kojih se mora eksplicitno pozovati kod, kod okidača se automatski izvršava kad god se u tabeli desi događaj (ili događaji) za koje je vezan taj okidač. Zaista, nemoguće je eksplicitno pozvati okidač - jedini način da se aktivira je da se u tabeli izvrši predviđena akcija za koju je on vezan.

Slika 1. Okidači
Neke uobičajene primjene okidača su:
Nametanje referencijalnog integriteta: Mada se preporučujem da se, kad god je moguće, koristi DRI (Declarative Referential Integrity), postoje mnoge stvari koje DRI ne može (na primjer, referencijalni integritet preko više baza podataka pa čak i servera, mnoge složene vrste odnosa, i tako dalje).
Pravljenje kontrolnog praćenja: To znači ispisivanje zapisa za praćenje ne samo najnovijih podataka, već takođe i same istorije mijenjanja svakog zapisa. To bi moglo da izgubi na popularnosti od kad je u SQL Serveru 2008 dodato praćenje promjena podataka, ali okidači još uvek predstavljaju prilično popularno rješenje.
Pravljenje funkcionalnosti slične ograničenju CHECK : Za razliku od ograničenja check, ovo funkcioniše preko više baza podataka pa čak i više servera.
Podmetanje naredbi na mjesto naredbi za korisničku akciju: To se obično koristi da bi se omogućilo insertovanje u složenim prikazima.
NAPOMENA: Pored nemogućnosti eksplicitnog pozivanja okidača, u snimljenim procedurama postoje još dve stvari kojih u okidačima nema:
PARAMETRI: Mada okidači ne prihvataju parametre, oni poseduju mehanizam kojim otkrivaju nad kojim zapisima treba da se djeluju.
POVRATNI kodovi'. Mada se može koristiti ključna reč RETURN, ne može se vratiti konkretan povratni kod (pošto se okidač ne može eksplicitno pozvati, kome vratiti povratni kod?).


SPECIFICIRANJE DOGAĐAJA
U SQL-u postoje samo tri vrste akcionih upita koji mogu da se koriste: mogu da se insertuju redovi u tabelu (ili prikaz), da se ažuriraju redovi, ili da se brišu. Zato, postoje tri vrste okidača, plus hibridi koji nastaju miješanjem i povezivanjem događaja i tempiranja koja ih ispaljuju:
INSERT okidači
DELETE okidači
UPDATE okidači
mješavina i povezivanje prethodnih

Sintaksa za pravljenje okidača liči na sve ostale primjere sintakse CREATE, osim što, kao sa prikazom, mora da bude vezana za tabelu - okidač ne može da postoji samostalno.
Sintaksa za triger:

Okidač INSERT - Kod okidača koji je označen kao FOR INSERT izvršiće se svaki put kada neko insertuje nov red u tabelu. Za svaki insertovan red, SQL Server pravi kopiju tog novog reda i insertuje je u jednu specijalnu tabelu koja postoji jedino u dometu tog okidača. Ta tabela se zove Inserted i živi samo dok je živ i okidač. Ona ne postoji dok okidač ne počne da radi, niti nakon njegovog završetka.
Okidač DELETE - On funkcioniše sasvim slično okidaču INSERT, osim što je tabela Inserted prazna (konačno, briše se a ne insertuje, pa nema zapisa za tabelu Inserted). Umjesto toga, kopija svakog izbrisanog zapisa insertuje se u drugu tabelu koja se zove Deleted. Ta tabela, kao i tabela Inserted, ima domet ograničen na trajanje okidača.
Okidač UPDATE - Opet isto, osim male razlike. Kod unutar okidača deklarisanog kao FOR UPDATE ispaljuje se kad god se promjeni postojeći red u tabeli. Razlika je u tome što ne postoji tabela UPDATED. Umjesto toga, SQL Server postupa sa svakim redom kao da je postojeći zapis izbrisan a potpuno nov zapis insertovan. Kao što iz toga može vjerovatno pogoditi, okidač deklarisan kao FOR UPDATE sadrži ne jednu nego dvije specijalne tabele koje se zovu Inserted i Deleted. Naravno, te tabele imaju tačno isti broj redova.
FOR|AFTER - Klauzula FOR (ili alternativno, može se pisati i AFTER; značenje je isto) ukazuje na vrstu akcije (ili akcija) koje ispaljuju okidač. Može se narediti da se okidač ispali kad god se dogodi INSERT, UPDATE, ili DELETE, ili neka mješavina te tri akcije. Znači, na primjer, klauzula FOR bi mogla da izgleda ovako:
FOR INSERT, DELETE
. . . ili:
FOR UPDATE, INSERT
. . . ili:
FOR DELETE
Kao što je rečeno okidači deklarisani sa klauzulom FOR ili AFTER moraju da budu vezani za tabelu – prikazi/pogledi nisu dozvoljeni (za njih su okidači INSTEAD OF)
Okidač FOR (ili AFTER) već je odavno poznat, pa većina ljudi tako zamišlja pojam okidača. Ali može se takođe izvršiti takozvani okidač INSTEAD 0F (umjesto). Od izbora između ova dva okidača zavisi da li će se SQL kod izvršiti prije ulaska u okidač (FORI AFTER) ili se neće izvršiti (INSTEAD OF).
Bez obzira na izbor, SQL Server pravi dvije radne tabele jednu u kojoj se čuvaju kopije insertovanih zapisa (koja se, slučajno, zove Inserted) i jednu u kojoj se čuvaju kopije izbrisanih zapisa (koja se zove Deleted).
Primjer:




PRIMJENA OKIDAČA ZA PRAVILA INTEGRITETA PODATAKA
Mada ne bi trebalo da predstavljaju prvi izbor, okidači mogu takođe da imaju istu funkcionalnost kao ograničenje CHECK, pa čak i kao DEFAULT. Odgovor na pitanje „Da li primjeniti okidače ili ograničenja CHECK ?” je veoma odlučno: „Zavisi”. Ako CHECK može da obavi posao, to je vjerovatno bolji izbor. Ima, međutim, slučajeva, kada ograničenje CHECK ne može da zadovolji potrebu, ili kada je zbog nečega u okviru procesa CHECK on manje poželjan od okidača. Primjeri situacija kada je bolje primjeniti okidač nego CHECK mogu da budu:
Poslovno pravilo zahtjeva da se referencira podatak u drugoj tabeli.
Poslovno pravilo zahtjeva da se provjeri delta (razlika između prije i posle) naredbe UPDATE.
Potrebna je prilagođena poruka o grešci.
Ograničenja CHECK SU izvrsna - brza i efikasna - ali ne obavljaju sve što se želi. Možda se najveći nedostatak javlja kada treba provjeravati podatke u raznim tabelama.
PROBA:
Kao ilustraciju, pogledajmo tabele Sales.SalesOrderDetail i Sales.SpecialOfferProduct u bazi podataka Adventure Works.
Relacija je kao na slici:


Pod normalnim DRI (Declarative Referential Integrity), može se biti sigurno da stavka SalesOrderDetail ne može da se unese u tabelu SalesOrderDetail ako ne postoji odgovarajući ProductlD u tabeli Sales.SpecialOfferProduct (čak i kada je zapis za specijalne ponude “bez popusta”).
U ovom slučaju, međutim, traži se nešto više od samog pravila.
Odeljenje za inventar se žali da ljudi iz podrške kupcima neprestano naručuju proizvode koji su označeni kao ukinuti (discontinued). Oni bi htjeli da se takve porudžbine odbace prije nego što uđu u sistem.
Ovo ne može da se riješi pomoću ograničenja CHECK, zato što se oznaka da je proizvod ukinut nalazi u drugoj tabeli (tabeli Product) a ne u onoj na koju se stavlja ograničenje (tabela SalesOrderDetail).
Rješenje okidač:

Test:
Nažalost, AdventureWorks izgleda nikad ranije nije ukinuo neki proizvod
Moraće se to sada promjeniti.

Sada, pošto imamo ukinuti proizvod, dodajmo detaljnu stavku porudžbine koja će prekršiti ograničenje. Uzimamo postojeću porudžbinu (43659) u koju ubacujem stavku:

Tako se dobija odbacivanje koje se očekuje:



PRIMJENA OKIDAČA ZA PROVJERU DELTE KOD AŽURIRANJA
Ponekad nije zanimljivo kolika je vrijednost bila niti kolika sad jeste, već prije kolika je bila promjena. Mada postoji kolona ili tabela gde bi se našla tainformaciju, može se izračunati ako se u okidaču upotrijebe tabele Inserted i Deleted.
PROBA:
AdventureWorks želi da zadovolji što više kupaca kako bi dugoročno opstao na tržištu, te je odlučio da uvede sistem racionisanja proizvoda. Odjeljenje inventara zahtjeva da se spriječi svaka porudžbina kojom bi se prodalo više od polovine količine na zalihi za bilo koji konkretan proizvod.
Tabela Production.Productlnventory sadrži kolonu Quantity.

Kako ovo funkcioniše - Izabrati bilo koji zapis i pokušati izvršiti ažuriranje kojim se količina smanjuje za preko 50 procenata:



IF UPDATE() I COLUMNS_UPDATED
U UPDATE okidaču, ispitivanjem da li je izmjenjena kolona (ili kolone), koje su zanimljive, često se može unutar okidača ograničiti dio koda koji će se izvršiti. To se može uraditi pomoću funkcije UPDATE() ili COLUMNS_UPDATED().
Funkcija UPDATE() relevantna je jedino u opsegu okidača. Njena jedina svrha u životu jeste da obezbjedi Bulovski odgovor (tačno/netačno) na pitanje da li je određena kolona ažurira. Ta funkcija se može upotrijebiti za određivanje da li neki određen blok koda treba izvršiti - na primjer, ako je taj kod relevantan jedino ako je konkretna kolona ažurirana.
Primjer:
Izmjena prethodnog okidača Production.ProductIsRationed.

Funkcija COLUMNS_UPDATED() - Ova funkcija je malo drugačija od UPDATE(), ali ima istu opštu svrhu. Funkcija COLUMNS_UPDATED() pruža mogućnost da se odjednom provjeri više kolona. Da bi to postigla, funkcija koristi masku bitova koja povezuje pojedinačne bitove jednog ili više bajtova varbinary podatka sa pojedinačnim kolonama u tabeli. To na kraju izgleda otprilike kao na slici.

U ovom slučaju, u samo jednom bajtu postoje informaciju da su ažurirane druga, treća i šesta kolona - ostale nisu.
U slučaju da ima više od osam kolona, SQL Server prosto dodaje još jedan bajt na desnu stranu i nastavlja da broji.

Da bi se upotrijebile oveu informacije, moraju se saberati binarne vrijednosti svih bitova, uzimajući prvu cifru sa lijeve strane kao najmanju.


ZAKLJUČAK
Okidač je mehanizam SUBP koji se automatski pokreće i izvršava svaki put kada se izvrši određena operacija ažuriranja nad tabelom za koju je vezan triger. Okidači se najčešće koriste za realizaciju poslovnih pravila koji se tiču provjere integriteta podataka i/ili obezbeđenja konzistentnog stanja baze podataka.
Pri kreiranju okidača obavezno se navode predmet i vrsta ažuriranja koja bi trebala aktivirati okidač. Predmet ažuriranja je uvjek neka tabela (ili pogled), a pod vrstom ažuriranja se podrazumevaju različite operacije ažuriranja, kao što su unos, modifikacija ili brisanje podataka. Uobičajeno je da se za svaku operaciju ažuriranja nad nekom tabelom definiše poseban okidač, mada je teoretski moguće koristiti isti okidač za sve tri operacije ažuriranja. Ono što ne može, jeste da se za istu operaciju ažuriranja nad jednom tabelom definiše više okidača.
Okidač obično služi za provjeru ili izmjenu podataka pomoću odgovarajućih SQL iskaza i ne bi trebao vraćati neku vrijednost(i) korisniku.
DDL iskazi koji se koriste za rad sa okidačima su CREATE TRIGGER, ALTER TRIGGER i DROP TRIGGER.
FOR | AFTER | INSTEAD OF -Ključne riječi koje određuju vrijeme aktiviranja okidača u odnosu na operaciju ažuriranja. Navodi se jedna od tri opcija.
FOR | AFTER - FOR i AFTER imaju isti efekat. Ključna riječ FOR je zaostavština iz prošlosti koja je sačuvana zbog kompatibilnosti sa prethodnim verzijama SUBP. Danas se u osnovi razlikuju tri vrste okidača: AFTER, BEFORE i INSTEAD OF od kojih SQL-Server podržava samo 2 vrste (AFTER i INSTEAD OF). AFTER okidač se aktivira tek nakon uspješnog izvršenja operacija ažuriranja. Ukoliko se pojavi neki problem pri izvršenju operacije ažuriranja okidač se neće ni pokrenuti.
INSTEAD OF - Govori da će se okidač izvršavati umjesto okidajućeg SQL iskaza. Koristi se kada se izvršenje operacije ažuriranja uslovljava ispunjenošću nekih preduslova, pa se u tijelu okidača prvo provjerava ispunjenost ovih uslova pa tek onda izvršava sama operacija ažuriranja. Ovo ažuriranje se vrši na osnovu sadržaja logičkih tabela koje se automatski formiraju pri aktiviranju okidača
Ključne riječi koje specificiraju koje operacije ažuriranja podataka, kada se izvrše nad specificiranom tabelom ili pogledom, aktiviraju okidač. Najmanje jedna opcija mora biti navedena.
AS - Ključna riječ koja označava početak tzv. tijela okidača u kome se navode akcije koje okidač treba da preduzme pri sopstvenom aktiviranju.
Tijelo okidača - Okidači mogu sadržati proizvoljan broj i vrste Transact-SQL iskaza. Pored generičkih SQL iskaza (DDL i DML iskazi) u okidaču se mogu koristiti i naredbe kontrole toka kao i druge naredbe svojstvene strukturalnim programskim jezicima (naredbe dodjeljivanja vrijednosti (SET),...). Rad sa okidačima je povezan sa korišćenjem nekoliko specijalnih tabela:
Deleted i Inserted su logičke (konceptualne) tabele. One su strukturalno identične tabeli nad kojom je okidač definisan i sadrže stare ili nove vrijednosti torki koje su obuhvaćene operacijom ažuriranja koja je aktivirala okidač. Na primjer, da bi se pretražile sve vrijednosti u deleted tabeli, treba koristiti sljedeću naredbu:
SELECT * FROM deleted
IF UPDATE (column) - Testira da li je izvršena INSERT ili UPDATE operacija nad specificiranom kolonom. Može se navesti više kolona.
UPDATE(column) se može koristiti bilo gde unutar tijela okidača.


Oporavak podataka
BAZE PODATAKA SU NAJKRITIČNIJE KOMPONENTE U VELIKOM BROJU ORGANIZACIJA.
Sve što je vezano finansijske proračune i informacije o kupcima, kao i osnovne informacije koje su neophodne organizaciji da bi mogla efikasno da funkcioniše, smešteno je u bazama podataka. Sve veći broj organizacija svoje poslovanje zasniva na odgovarajućim bazama podataka, koje im omogućavaju da efikasno funkcionišu. Mnoge kompanije su bankrotirale, zatvorile svoje prodavnice ili otpustile radnike zbog problema sa bazama podataka, koje su mogle da spriječe adekvatnim planom oporavka podataka. Na bazu podataka može negativno da utiče sve što je vezano za hardverske otkaze, prirodne katastrofe koje mogu da unište fizičku lokaciju servera baze podatka (server sala), pa sve do otkaza ili nanamjernog brisanja ili modifikovanja podataka.Problemi sa bazama podataka se najčešće javljaju kada se najmanje očekuju.

Ukoliko postoji dokumentovana i testirana strategija bekapovanja i restauriranja, to će olakšati posao administratora baza podataka u situacijama kada se dogodi ono najgore. Ukoliko postoji odgovarajuća strategija oporavka baze podataka, postoji politika koja omogućava da se odgovori na nepredviđene situacije. Veliki dio opisa poslova svakog administratora baza podataka je obezbjeđivanje ispravnog bekapovanja i oporavka baza podataka u organizaciji u najkraćem roku nakon pojave problema.
Da bi se zaštitili podaci odgovarajuće organizacije i obezbjedila raspoloživost i mogućnost oporavka baza podataka, neophodno je imati veoma precizan i dobro smišljen plan oporavka podataka.


TRANSAKCIONA ARHITEKTURA
Razumjevanje SQL Server transakcione arhitekture je veoma značajno zbog bliske povezanosti sa oporavkom podataka. Poznavanje procesa koji je vezan za izvršavanje transakcija omogućava donošenje inteligentnijih odluka vezanih za veličinu, lokaciju, bekapovanje i restauriranje baza podataka i transakcionih logova.
Svaka SQL Server baza podataka sadrži odgovarajući transakcionih log. Transakcioni log sadrži zapise vezane za sve promjene koje su izvršene u bazi podataka. Modifikacije se prvo izvršavaju u transakcionom logu, pa tek onda u datotekama baze podataka. Zapisi se snimaju sekvencijalno u transakcioni log, a svaka transakcija dobija odgovarajući identifikator. Periodično se obavlja provjera, koja se bilježi u transakcionom logu. Nakon što se dostigne tačka provjere (engl. checkpoint), promjene koje su izvršene od posljednje tačke provjere zapisuju se u datoteke baze podataka.

Skoro svaka modifikacija SQL Server baze podataka je transakcionog tipa. Transakcija se u potpunosti potvrđuje, ili se ingoriše ukoliko nije u potpunosti izvršena. Pojedinačne INSERT, UPDATE i DELETE naredbe, koje se izvršavaju izvan korisnički definisane transakcije, implicitno se tretiraju kao dio transakcije, zato što one modifikuju podatke koji se nalaze u bazi podataka. Za implicitne transakcije se automatski potvrđuju izvršene promjene nakon završetka njihovog izvršavanja. Eksplicitne ili korisnički definisane transakcije označavaju se pomoću BEGIN TRANSACTION i COMMIT TRANSACTION naredbi, pomoću kojih se grupišu pojedinačne naredbe u jednu cjelinu. Korisnički definisana transakcija se može poništiti ukoliko se pojavi greška prilikom izvršavanja naredbi ili ukoliko se eksplicitno izvrši ROLLBACK TRANSACTION naredba.
Jedan od glavnih razloga zbog koga transakcioni log prvo bilježi modifikacije je oporavak baze podataka. Ukoliko bi računar na kome se izvršava SQL Server na trenutak prekinuo svoj rad zbog prekida napajanja, transakcioni log bi mogao da se primjeni prilikom utvrđivanja stanja u kome se nalazio sistem prije nego što je došlo do prekida napajanja. Proces oporavka se automatski izvršava za sve baze podataka koje su prisutne na serveru u trenutku kada se SQL Server startuje. U ovom procesu prvo se analizira sadržaj transakcionog loga od posljednje identifikovane tačke provjere, zatim potvrđuju sve potpuno izvršene transakcije, i na kraju poništavaju sve transakcije koje nisu potpuno izvršene u trenutku kada je prekinuto izvršavanje. Proces oporavka se primjenjuje prilikom upravljanja restauriranjem niza bekapovanih baza podataka.

MODELI OPORAVKA BAZA PODATAKA
Model oporavka koji se definiše za bazu podataka utvrđuje na koji način se mijenja transakcioni log, odnosno može li transakcioni log da se bekapuje. Za bazu podataka na koju se primenjuje Simple model oporavka, transakcioni log se prazni nakon svake tačke provjere. Bekapovanje transakcionog loga prilikom korištenja Simple modela oporavka nije moguće, zato što ovaj log ne sadrži sve modifikacije koje su izvršene nad podacima prisutnim u bazi podataka. U Simple modelu oporavka, log se prevashodno koristi za oporavak baze podataka prilikom startovanja servisa. Međutim, prilikom korištenja potpunog ili Bulk-logged modela oporavka, javlja se mnogo manje potencijalnih gubitaka podataka.

Ukoliko se primenjuje Full ili Bulk-logged model oporavka, transakcioni log treba da se redovno bekapuje. Oba modela oporavka omogućavaju da se u transakcionom logu čuvaju informacije vezane za vremenski period koji je mnogo duži od perioda koji je protekao od prethodne tačke provjere, pod uslovom da se redovno obavlja potpuni bekap baze podataka. Potpuni bekap je neophodan da bi se imali neophodni podaci na koje se mogu primjeniti bekapovane transakcije. Oba modela oporavka bilježe modifikacije u transakcioni log sve do trenutka njegovog bekapovanja. Najveći broj produkcionih baza podataka koristi Full model oporavka, zato što on omogućava smanjenje potencijalnih gubitaka na minimum. Bulk-logged model oporavka koristi manje prostora za izvršene operacije, kao što je kreiranje indeksa, ili izvršavanje SELECT..INTO naredbe. Glavni nedostatak korištenja Bulk-logged modela oporavka je da se može obnoviti samo cjelokupan bekap transakcionog loga, tako da povratak u određenu tačku u vremenu nije moguć.

SPECIFIČNOSTI BILJEŽENJA PODATAKA O TRANSAKCIJAMA
Transakcioni log se može tretirati kao sekvenca zapisa, koji predstavljaju sve modifikacije koje su izvršene nad podacima koji se nalaze u bazi podataka. Svaki pojedinačni zapis je definisan brojem (Log Sequence Number, skraćeno LSN), kao i indetifikacionim brojem transakcije, koji ga povezuje sa drugim zapisima u istoj transakciji. Bekapovi sadrže metapodatke o opsegu LSN brojeva koji su zabilježeni prilikom bekapovanja. Ovi metapodaci se mogu primjenjivati prilikom utvrđivanja sekvence operacija restauriranja, koje su neophodne da bi se izvršio potpuni oporavak baze podataka. Zavisni logovi su povezani u lanac, kako bi se ubrzao postupak oporavka transakcije, ukoliko je to neophodno. Bilo koja operacija poništavanja je, takođe, zabilježena u transakcioni log, bilo da je posljedica izvršavanja eksplicitne ROLLBACK naredbe ili pojave greške.
Modifikacije transakcionog loga mogu se snimiti na dva različita načina, zavisno od tipa izvršene modifikacije. Neke modifikacije zahtjevaju snimanje zapisa same logičke operacije. Da bi se poništilo izvršavanje logičke operacije, neophodno je izvršiti suprotnu operaciju. Neke modifikacije se prate preko i nakon snimanja modifikovanog zapisa. Da bi se poništila modifikacije ovog tipa, neophodno je koristiti snimak podataka prije nego što je izvršena modifikacija.

Transakcioni log je na disku smješten u jednoj ili više fizičkih datoteka. Najveći broj baza podataka sadrži transakcioni log koji je smješten u jednu datoteku. Zapisi u logu se uklanjaju prilikom bekapovanja transakciong loga, ukoliko se primjenjuje Full ili Bulk-logged model oporavka, ili prilikom pojave svake tačke provjere ukoliko se za bazu podataka primjenjuje Simple model oporavka. Nakon što zapisi budu uklonjeni iz transakcionog loga, prostor koji su prethodno zauzimali može se ponovo koristiti.
Transakcioni log se smatra "wrap-around" datotekom, zato što nakon dostizanja kraja fizičke datoteke zapisi mogu da se upisuju na početku datoteke, uz pretpostavku da postoji raspoloživi prostor za upisivanje podataka, koji je dobijen uklanjanjem nepotrebnih zapisa iz transakcionog loga.
Kada se uklone nepotrebni podaci iz transakcionog loga, prostor koji je oslobođen može se ponovo koristiti. Baza potaka može da sadrži i više od jedne datoteke za upisivanje podataka o izvršenim transakcijama, ali nije moguće istovremeno pristupanje većem broju ovih datoteka. Prilikom korištenja većeg broja datoteka transakcionog loga, svaka datoteka se može više puta koristiti prije ponovnog pristupanja prvoj datoteci loga.
Ukoliko se ispuni transakcioni log, a log je konfigurisan tako da se automatski povećava, tada će se transakcioni log automatski povećati na osnovu unaprijed definisanih inkremenata za automatsko povećanje, i to se može obavljati sve dok se ne dostigne maksimalna veličina navedena za datoteku. Ukoliko nije moguće dodatno povećanje veličine transakcionog loga, generiše se poruka o pojavi greške 9002, što ukazuje na to da je transakcioni log ispunjen i da mora biti bekapovan. Ukoliko je transakcioni log u potpunosti ispunjen, nijedna modifikacija ne smije da se izvrši nad podacima koji se nalaze u bazi podataka sve dok se ne oslobodi neophodan prostor za skladištenje informacija o izvršenim modifikacijama.
Bekapovanje transakcionog loga je najčešći način za oslobađanje prostora u logu. Fizičke datoteke koje se koriste za smještanje transakcionih logova podijeljene su u virtuelne logove, kojima okruženje za upravljanje bazama podataka automatski upravlja i dinamički mijenja njihovu veličinu.

Korištenjem malih inkremenata za automatsko povećanje, može se povećati broj virtuelnih logova prilikom proširivanja baze podataka. Postojanje velikog broja malih virtuelnih logova može značajno da uspori oporavak baze podataka, kao i procese bekapovanja i restauriranja. Podrazumjevani inkrement za automatsko povećanje od 10 procenata je sasvim prihvatljiv u najvećem broju situacija.


NA KOJI NAČIN SE BILJEŽE PODACI O TRANSAKCIJAMA
Transakcioni log se koristi za mnoge operacije u bazi podataka. U transakcionom logu nalaze se informacije vezane za izvršene modifikacije. (Izvršavanje SELECT naredbi ne zahtjeva bilježenje, tako da one ne zauzimaju bilo kakav prostor u transakcionom logu). Informacije o izvršavanju sljedećih operacija snimaju se u obliku zapisa u transakcionom logu:
Početak i kraj izvršavanja svake pojedinačne transakcije
Sve modifikacije podataka, koje su vezane za izvršavanje INSERT, UPDATE i DELETE naredbi
Definisanje, modifikovanje i uklanjanje tabela i indeksa
Alociranje i dealociranje osnovnih stranica i stranica višeg nivoa u bazi podataka

Na ovoj slici prikazan je pojednostavljen primjer sekvence operacija u transakcionom logu, odnosno šta se događa ukoliko u određenom trenutku dođe do prekida napajanja. Transakcije se sekvencijalno bilježe u transakcioni log, a periodično se izvršava provjera, nakon koje se potvrđena transakcija primjenjuje na datoteke baze podataka.
Nakon što se dostigne prva tačke provjere, Transakcija 1 i implicitna DELETE naredba se primjenjuju na datoteke baze podataka. Kada se dostigne druga tačke provjere, Transakcija 2 i INSERT naredba se primjenjuju na datoteke baze podataka. Transakcija 3 nije potvrđena u tom trenutku, tako da operacije koje su definisane ovom transakcijom ne primjenjuju na datoteke baze podataka. Kada prestane napajanje, odnosno SQL Server se ponovo startuje, proces oporavka počinje od prethodne tačke provjere. Transakcija 3 i Transakcija 5 se poništavaju, zato što nisu potvrđene prije prekida napajanja. Transakcija 4 nastavlja sa izvršavanjem u procesu oporavka.
SQL Server koristi transakcioni log prilikom izvršavanja sljedećih operacija:
Oporavak pojedinačnih transakcija Ukoliko baza podataka detektuje grešku prilikom izvršavanja transakcije, ili se eksplicitno izvrši ROLLBACK naredba, zapisi iz loga se koriste prilikom poništavanja svih modifikacija koje se izvršavaju prilikom izvršavanja transakcije.
Nepotpuni oporavak transakcija prilikom startovanja Prilikom startovanja SQL Servera, pristupa se transakcionom logu i vraća baza podataka u konzistentno stanje. Ukoliko se SQL Server normalno zaustavi, onda nije neophodno izvršavanje oporavka. Ukoliko se SQL server zaustavi u nepredviđenom trenutku, recimo usljed privremenog prekida napajanja, transakcioni log se koristi za nastavak izvršavanja svih potvrđenih transakcija, koje nisu izvršile predviđeno modifikovanje baze podataka, odnosno za poništavanje svih nepotpuno izvršenih transakcija.
Proces oporavka se javlja za svaku bazu podataka na serveru prilikom pokretanja SQL Servera i ovaj proces se obavlja automatski. Poruke se zapisuju u Windows log događaja i SQL Server log za bilježenje grešaka, pri čemu se bilježi broj transakcija koje su poništene i broj transakcija čije je izvršavanje nastavljeno.
Oporavak baze podataka do trenutka pojave otkaza Transakcioni log se koristi prilikom modifikovanja restaurirane baze podataka, čime je omogućen oporavak do trenutka pojave greške, pod uslovom da transakcioni log sadrži sve neophodne podatke do tog trenutka. Proces oporavka za vrijeme restauriranja baze podataka je sličan procesu oporavka koji se izvršava prilikom startovanja baze podataka. Proces oporavka se završava kada se iskoristi poslednji bekap u sekvenci.
Podrška za preslikavanje baza podataka i prosleđivanje logova Preslikavanje baza podataka omogućava da se svako ažuriranje, koje je izvršeno nad primarnom bazom podataka, primjenjuje i na preslikanu bazu podataka. Zapisi vezani za transakcioni log se odmah šalju do dodatnog servera, kako bi se odgovarajuće izmjene primjenile na kopiju originalne baze podataka.
Prosleđivanjem logova omogućava se da primarna baza podataka periodično šalje bekapove logova do servera koji će se restaurirati na osnovu definisanog rasporeda.
Podrška transakcionoj replikaciji Transakciona replikacija omogućava da izmjene budu primjenjene na korisnike objekata koji se repliciraju. Agent za očitavanje loga je odgovoran za očitavanje zapisa iz transakcionog loga, odnosno njihovo prosljeđivanje do servera koji je u ulozi distributera.


STRATEGIJE BEKAPOVANJA I RESTAURIRANJA
Da bi se definisala strategija bekapovanja i restauriranja, neophodno je dati odgovor na nekoliko pitanja.
Koliko je važno da podaci budu bekapovani? Neki sistem za računovodstvo ili proizvodni sistem će zaista imati veoma specifične zahtjeve u odnosu na bazu podataka čiji se podaci samo povremeno očitavaju i koriste. Razumjevanje tipa informacija i njihovog značaja za organizaciju može biti od velike pomoći prilikom određivanja rada koji je neophodno uložiti i prihvatljivih gubitaka.
Koji tip baze podataka se bekapuje? Veoma je važno proveriti da li su pored baza podataka bekapovane i sistemske baze podataka, kako bi se omogućilo da SQL Server izvrši oporavak cjelokupnog sistema. Iako master bazu podataka ne treba/mora bekapovati svakih sat vremena, treba je bekapovati uvjek kada se dodaju nove baze podataka, kreiraju novi nalozi, ili kada se na bilo koji dragi način mijenja konfiguracija sistema, msdb baza podataka smješta SQL Server Agent konfiguraciju, tabele sa istorijom bekapovanja, definicije DTS i SSIS paketa, i još mnogo toga. Primjena funkcionalnosti koje podržava msdb baza podataka omogućava da se utvrdi učestanost bekapovanja. Model baze podataka je jednostavan šablon za nove baze podataka na SQL Serveru, i treba da ga bekapovati svaki put kada se javi određena promjena.
Koji su šabloni korištenja baze podataka? Neopodno je da neke baze podataka budu raspoložive 24 časa dnevno, a nekim bazama podataka se može pristupati samo u toku radnog vremena. Ukoliko je poznato na koji će se način koristiti baze podataka, može se definisati raspored procesa bekapovanja tako da u najmanjoj mogućoj mjeri utiče na performanse sistema.
Koliko često se mijenjaju podaci? Činjenica je da se neke baze podataka mijenjaju češće od ostalih. Baze podataka sa velikim brojem promjena treba češće bekapovati. Baza podataka, čiji se podaci samo očitavaju, ne mora stalno da se bekapuje, već samo onda kada se jave izmjene podataka. Baza podataka koja se učitava svake noći, treba da se bekapuje nakon što se izvrše odgovarajuće izmjene.
Do kog nivoa su prihvatljivi gubitci? Odgovor će vjerovatno biti takav da ne prihva nikakav gubitak, ali za neke baze podataka, kao što su probne ili razvojne baze podataka, prihvatljiv je mnogo veći gubitak nego kod drugih baza podataka.
Koliko dugo treba da traje oporavak baze podataka? Neke strategije bekapovanja zahtjevaju mnogo više vremena za oporavak od drugih strategija. Vrijeme neophodno za oporavak neke kritične baze podataka može da utiče na to da se promjeni strategiju bekapovanja podataka.
Da li će se velika količina podataka mijenjati redovno, ili će promjene biti povezane sa određenim dijelovima? Kada se mali dio baze podataka mijnja redovno, ima smisla da se dio podataka koji se menjaju izdvoji u posebnu grupu datoteka, koje će se češće bekapovati od ostalih podataka u bazi podataka, ukoliko je to moguće. Restauriranje pojedinačne datoteke ili grupe datoteka može zahtjevati mnogo manje vremena od restauriranja kopije cjelokupne baze podataka koja sadrži veliku količinu podataka.
Koliki je prostor za skladištenje raspoloživ za bekapove? Različiti tipovi bekapova zahtevaju različit prostor za skladištenje. Strategija bekapovanja ne može da zahtjeva više prostora od onog koji stoji na raspolaganju. Veoma je važno da utvrditi koliko dugo je neophodno čuvati odgovarajuće bekapove, jer to ima uticaj na veličinu prostora koji je neophodan za bekapovanje.
Bekapovanje baza podataka je različito od bekapovanja radnih stanica i servera, zato što je neophodno da se koriste kombinaciju tehnika bekapovanja kako bi se omogućio potpuni oporavak. Mnoge baze podataka će koristiti kombinaciju bekapovanja potpunog, diferencijalnog i transakcionce loga, tako da potencijalni gubici podataka budu svedeni na minimum.
Plan oporavka treba da uključi tip bekapa i učestanost bekapovanja, gdje se skladište bekapovi, ko je odgovoran za izvršavanje operacija vezanih za oporavak baze podataka, odnosno proces testiran i plana oporavka. Veoma je važno testirati plan oporavka, kako bi se omogućilo da funkcioniše u situaciji u kojoj se javljaju problemi u radu baze podataka. Plan oporavka treba da bude dokumentovan u uputstvu za izvršavanje operacija.


MODELI OPORAVKA I STRATEGIJA BEKAPA
Izbor odgovarajućeg modela oporavka je kritičan za planiranje i izvršavanje odgovarajuće strategije bekapovanja. Dvije glavne opcije su Full i Simple oporavak. Bulk-logged oporavak se može primenjivati u kombinaciji sa Full modelom oporavka, kako bi se optimizovale performanse izvršenih operacija i prostor za logove sveo na najmanju mjeru prilikom izvršavanja transakcija. Najveći broj produkcionih baza podataka koristi Full model oporavka, pri čemu se redovno bekapuje transakcioni log, kako bi se umanjio potencijalni gubitak podataka.


SIMPLE MODEL OPORAVKA
Simple model oporavka baze podataka zahtjeva najmanje održavanje, ali je najveća vjerovatnoća da doći do gubitka podataka ukoliko se javi otkaz sistema. Prilikom korištenja Simple modela oporavka dozvoljeni su samo Full i Differential bekapovi. Transakcioni log se koristi za potrebe oporavka, ali ne sadrži dovoljan skup podataka za sve izvršene promjene u bazi podataka. U svakoj tački provjere, cjelokupan neaktivni dio loga, koji je vezan za transakcije koje su prethodno potvrđene, uklanja se.
Simple model oporavka se koristi u sljedećim situacijama:
Ako se javi problem, prihvatljivo je da izgube informacije od posljednjeg potpunog bekapa.
Zahtjevi vezani za održavanje transakcionog loga su mnogo veći od koristi koja se ima od toga.
Baza podataka se koristi prilikom testiranja ili se podaci iz baze podataka samo očitavaju, tako da je moguće veoma jednostavno ponovno kreiranje neophodnih podataka.


FULL MODEL OPORAVKA
Ukoliko se za odgovarajuću bazu podataka primenjuje Full model oporavka, sve modifikacije izvršene nad bazom podataka se bilježe u transakcionom logu nakon što se obavi potpuni bekap baze podataka. Nakon prvog potpunog bekapa, bekapovi logova se koriste za oslobađanje prostora u transakcionom logu, kako bi mogli da se bilježe podaci o novim transakcijama.
Prilikom korištenja Full modela oporavka, veličina transakcionog loga zavisi prije svega od broja i tipova modifikacija, odnosno koliko često se transakcioni log bekapuje. Baze podataka sa velikim brojem modifikacija zahtjevaju više prostora za transakcioni log. Bekapovanje loga omogućava oslobađanje prostora, a učestalije bekapovanje može da dovede do toga da transakcioni log bude manje veličine.
Korištenje Full modela oporavka u kombinaciji sa serijom bekapova može da omogući restauriranje baze podataka definisane u određenom trenutku. Full model oporavka može da omogući oravak baze podataka od odgovarajućeg otkaza, pod uslovom da postoje neophodni podaci u transakcionom logu. To je jedan od razloga zbog koga je neophodno stalno skladištiti sadržaj transakcionih logova na posebnom pouzdanijem medijumu, izdvojenom od datoteka baza podataka.
Full model oporavka je neophodno primjenjivati u sljedećim situacijama:
Neophodno svesti na minimum gubitak podataka prilikom pojave otkaza sistema.
Neophodno je imati mogućnost oporavka baze podataka, tako da se povrati stanje koje je bilo prije otkaza.
Želi se imati mogućnost oporavka baze podataka, tako da povrati stanje koje je bilo u određenom trenutku u prošlosti.
Neophodno je imati mogućnost restauriranja pojedinačne stranice baze podataka.
Želi se imati mogućnost da administrator izvršava redovno bekapovanje transakcionog loga.
Prilikom korištenja Full modela oporavka, o svim transakcijama se bilježe sve informacije, uključujući operacije kao što su SELECT..INTO i CREATE INDEX. Bilježenje svih neophodnih informacija o izvršavanju operacija može značajno da smanji performanse izvršavanja operacija, odnosno zahtjeva značajan prostor za bilježenje informacija, ali tada je moguće da se povrati stanje baze dodataka na stanje koje je bilo u određenom trenutku u prošlosti.


BULK-LOGGED MODEL OPORAVKA
Bulk-Logged model oporavka omogućava bilježenje manje informacija u transakcioni log prilikom izvršavanja operacija, tako da se ubrzava izvršavanje operacija, ali po cijenu nemogućnosti vraćanja stanja baze podataka na stanje koje je bilo u određenom trenutku u prošlosti. Samo promjene koje su izazvane izvršavanjem operacije, a ne i cjelokupna operacija, zapisane su u log za bekapovanje. Na taj način se za skladištenje podataka vezanih za izvršavanje operacija koristi manje prostora. Pošto se bilježe samo izmjenjene stranice višeg nivoa, neophodno je restauriranje cjelokupnog bekap loga. Oporavak, koji podrazumjeva vraćanje baze podataka u stanje u kome je bila u određenom trenutku u vremenu, nije podržan prilikom bekapovanja logova koji sadrže Bulk-logged operacije. Operacije kojim se mogu minimalno zabilježiti podaci prilikom korištenja Bulk-logged modela oporavka su:
SELECT..INTO operacije
INSERT..SELECT operacije
Bulk Import operacije (izvršavanjem bcp ili BULK INSERT naredbe)
DDL operacije za rad sa indeksima, kao što su kreiranje novih indeksa, ponovno indeksiranje ili uklanjanje grupisanih indeksa, čime se zahtjeva ponovno kreiranje hipa.
Bulk-logged model oporavka treba da se koristi u kombinaciji sa Full modelom oporavka. Baza podataka može da koristi Bulk-logged model prije izvršavanja složenih operacija, a zatim se može ponovo prijeći na Full model oporavka nakon izvršavanja složenih operacija. Pošto se izmjenjene stranice višeg nivoa direktno kopiraju u log za bekapovanje, datoteke baze podataka koje sadrže veće izmjene moraju biti raspoložive prilikom bekapovanja loga.


VRSTE BEKAPOVA
SQL Server podržava dvije vrste bekapova: potpuni i diferencijalni, a postoji i mogućnost bekapovanja transakcionog loga.
Potpuni bekap omogućava kreiranje kopije podataka i zapisivanje te kopije u datoteku za bekapovanje, kao i dio transakcionog loga, kako bi se omogućilo detektovanje promjena koje su izvršene nad bazom podataka od poslednjeg potpunog bekapa.
Diferencijalni bekap omogućava snimanje podataka izmjenjenih nakon prethodnog potpunog bekapa. Veličina diferencijalnog bekapa zavisi od količine podataka koji su izmjenjeni nakon prethodnog potpunog bekapa. Potpuni i diferencijalni bekapovi mogu se primjenjivati nad cjelokupnom bazom podataka grupom datoteka ili pojedinačnim datotekama baze podataka.

Diferencijalni bekap zahtjeva da postoji prethodno kreirani potpuni bekap na osnovu koga se prate izvršene promjene. Ovaj potpuni bekap je poznat kao osnova za direfencijalni bekap. Da bi se iskoristili podaci sačuvani putem diferencijalnog bekapa, neophodno je prvo restaurirati podatke zabilježene putem potpunog bekapa. Za restauriranje podataka putem diferencijalnog bekap neophodno je mnogo manje vremena nego prilikom restauriranja podataka na osnovu većeg broja bekapova transakcionog loga, zavisno od obima modifikacija koje u izvršene nad podacima u bazi podataka. Diferencijalni bekap, sadrži samo konačne vrijednosti podataka, a ne i međuvrijednosti.
Bekapovanje transakcionog loga je moguće za baze podataka u kojima se primenjuju Full ili Bulk-logged model oporavka. Bekapovanje transakcionog loga ima smisla samo ako je izvršen potpuni bekap, tako da se transakcije mogu primjeniti nakon oporavka baze podataka na osnovu potpunog bekapa. Prije nego što se izvrši bekapovanje transakcionog loga, neophodno je izvršiti potpuni bekap baze podataka.
Parcijalni bekapovi su uvedeni u SQL Server 2005 verziji i omogućavaju bekapovanje svih grupa datoteka koje omogućavaju čitanje i upis. Parcijalni bekap nije podržan u dijalogu za definisanje plana održavanja baze podataka, niti u management studio alatu. Da bi se kreirao parcijalni bekap, neophodno je iskoristi READ_WRITE_FILEGROUPS opciju BACKUP Transact-SQL naredbe.


KREIRANJE BEKAPOVA
Bekapovi mogu da se obavljaju izvršavanjem Transact-SQL naredbi ili korištenjem SQL Server Management Studio alata.


POTPUNI BEKAPOVI
Izvršavanje potpunog bekapa je veoma jednostavan proces. Potpuno bekapovanje baze podataka podrazumjeva bekapovanje svih podataka koji se nalaze u bazi podataka. Pojedinačne grupe datoteka i datoteke se takođe mogu bekapovati za bazu podataka koja je podeljena u više datoteka. Prednost bekapovanja grupe datoteka, ili pojedinačne datoteke baze podataka, u odnosu na bekapovanje cjelokupne baze podataka je da u slučaju otkaza treba da se restauriraju samo određene datoteke ili grupe datoteka. Za to je neophodno znatno manje vremena u odnosu na obnavljanje sadržaja cjelokupne datoteke, posebno kada se radi o velikim bazama podataka.

Kada se radi o bazama podataka za koje se primjenjuje Simple model oporavka, podržani su jedino potpuni i diferencijalni bekap. Ukoliko dođe do otkaza, sve transakcije nakon prethodnog bekapa biće izgubljene. Korištenjem Simple modela oporavka ne može se restaurirati baza podataka tako da se vrati u stanje u kome je bila prije pojave otkaza.
Primjer: Kreiranje potpunog bekapa baze podataka


DIFERENCIJALNI BEKAPOVI
Diferencijalni bekapovi se kreiraju korištenjem iste naredbe kao i potpuni bekapovi, osim što se mora navesti WITH DIFFERENTIAL opcija u BACKUP naredbi. Da bi se izvršilo diferencijalno bekapovanje, neophodno je prethodno kreirati potpuni bekap baze podataka. Diferencijalni bekap služi za skladištenje svih promjena koje su izvršene nad bazom podataka nakon poslednjeg potpunog bekapa. Potpuni bekap se u ovoj situaciji koristi kao osnova za diferencijalni bekap.

Diferencijalni bekapovi mogu umnogome da ubrzaju procese bekapovanja i restauriranja baze podataka.
Veličina diferencijalnog bekapa zavisi, prije svega, od toga koliko podataka se promjenilo od trenutka kada je obavljen prethodni potpuni bekap baze pdoataka.
Primjer: Izvršavanje diferencijalnog bekapa korištenjem Transact-SQL naredbi

BEKAPOVANJE TRANSAKCIONIH LOGOVA
Bekapovanje transakcionih logova korištenjem Transact-SQL naredbi veoma je slično kreiranju drugih tipova bekapova. Bekapovanje transakcionog loga može da se primjenjuje nad bazama podataka koje koriste Full ili Bulk-logged model oporavka.

Primjer: Bekapovanje transakcionog loga korištenjem Transact-SQL naredbe


Nakon bekapovanja transakcionog loga, bekapovani dio se uklanja u cilju oslobađanja prostora za skladištenje podataka o narednim transakcijama. Sve bekapove loga neophodno je čuvati sve dok se ne obavi novi potpuni bekap, jer se time povećava pouzdanost sistema.
Ukoliko je baza podataka oštećena, možda će biti neophodno da se bekapuje ostatak loga prije nego što se počne postupak oporavka sistema. Prilikom bekapovanja dijela loga, neophodno je koristiti WITH NORECOVERY opciju, ukoliko je baza podataka i dalje aktivna, kako bi se izbjegla pojava greške. Ukoliko baza podataka nije trenutno aktivna, iskoristite NO_TRUNCATE opciju. Opciju NO TRUNCATE neophodno je da koristite samo kada je baza podataka na bilo koji način oštećena.


KREIRANJE BEKAPOVA KORIŠTENJEM MANAGEMENT STUDIO ALATA
Korisnički interfejs SQL Server Management Studio alata može se koristiti prilikom kreiranja bekapova. Interfejs alata omogućava da generiše skript na osnovu izabranih opcija korištenjem Script tastera, koji se nalazi u gornjem dijelu ekrana. Ovo može biti od velike koristi za korisnike koji ne poznaju dovoljno sintaksu Transact-SQL naredbi.
Za bekap korištenjem SQL Server Management Studio alata, neophodno je uraditi sljedeće:
Otvoriti SQL Server Management Studio, a zatim se, pomoću Object Explorer prozora, povezati sa odgovarajućom SQL Server instancom koja sadrži bazu podataka koja želite da bekapujete.
Pristupite Databases direktorijumu i opciono System Databases direktorijumu, zavisno od toga koja baza podataka se želi bekapovati.
Desnim tasterom miša selektovati bazu podataka se želi bekapovati, izaberati Tasks stavku menija, a zatim Back Up stavku.
Otvara se Backup Database dijalog, kao što je prikazano na slici:
Odabrati odgovarajući tip bekapa (Full, Differential ili Transaction Log).
Odabrati odgovarajući komponentu za bekapovanje. Ukoliko se bekapuje transakcioni log, može se preći na sljedeći korak.
Opciono, definiše se naziv, opis i rok trajanja skupa bekapova.
Dodaje se uređaj koji će se koristiti za smještanje bekapa, tako što se pritisne Add taster. Otvara se Select Backup Destination dijalog, koji je prikazan na slici dole niže. Može se dodati ili naziv datoteke, ili logički uređaj za bekapovanje.

Selektovati Options stranicu, koja se nalazi u gornjem lijevom uglu. Stranica sa opcijama je prikazana na slici:

Na osnovu inicijalnih podešavanja, novi podaci se dodaju na kraju postojećeg skupa medija. Da bi se uklonili svi skupovi bekapova u skupu medija, neophodno je odabrati Overwrite All Existing Backup Sets opciju. To je ekvivalentno korišćenju WITH INIT Transact-SQL opcije. Back Up to a New Media Set i Erase All Existing Backup Sets opcije ekvivalentne su korišćenju WITH FORMAT Transact-SQL opcije.
Ukoliko se želi verifikovati bekap, ili provjeriti kontrolna suma prije nego što se snimi bekap na medijum, treba selektujte odgovarajuće Reliability opcije.
Ukoliko se bekapuje transakcioni log, podrazumjevano podešavanje definiše da se uklanjaju svi nepotrebni podaci iz loga nakon bekapovanja. Ovo je sasvim prihvatljivo, osim ako se ne bekapuje rep transakcionog loga u cilju oporavka oštećene baze podataka.
Ukoliko se koriste magnetne trake, mogu se odaberati opcije vezane za izbacivanje ili premotavanje trake.
Ukoliko se koristi Enterprise izdanje SQL Servera, postoji mogućnost da se kompresuju bekapovi. Može se birati da li će se koristiti kompresija ili će se prihvatiti podrazumjevana podešavanja vezana za bekapovanje.
Da bi se pregledale Transact-SQL naredbe koje se koriste prilikom kreiranja bekapa, možet se upotrebiti Script taster, koji se nalazi u gornjem dijelu Back Up Database dijaloga.
Pritisnite OK taster, kako bi se započelo bekapovanje.
Parcijalni bekapovi nisu podržani u SQL Server Management Studio korisničkom interfejsu. Da bi se izvršio parcijalni bekap, moraju se iskoristiti odgovarajuće Transact-SQL naredbe.


IZVRŠAVANJE POTPUNOG OPORAVKA BAZE PODATAKA
Proces restauriranja baze podataka zavisi od raspoloživih bekapova. Prije nego što se restaurira baza podataka, treba znati koji bekapovi su raspoloživi, odnosno treba bekapovati rep transakcionog loga, ukoliko je to moguće.
Prilikom izvršavanja potpunog oporavka baze podataka, proces će se razlikovati zavisno od toga koji model oporavka je izabran za posmatranu bazu podataka.


KORIŠTENJE SIMPLE MODELA OPORAVKA
Ukoliko se radi o bazi podataka za koju je definisan Simple model oporavka, ima mnogo manje raspoloživih opcija u odnosu na baze podataka za koje se primjenjuje Full ili Bulk-logged model oporavka. Potpuni i diferencijalni bekapovi mogu se koristiti, ali su velike šanse da su sve promjene izvršene nakon posljednjeg bekapa zauvjek izgubljene. Ukoliko je potpuni bekap izvršen u ponoć, a problem se javio u 3 sata posle ponoći, biće izgubljeni svi podaci vezani za promjene izvršene u tri prethodna sata.
U situacijama u kojima su podaci manje kritični, samo se očitavaju ili mogu veoma jednostavno da se ponovo kreiraju, Simple model oporavka može da bude veoma dobro rješenje. U nastavku prikazan je primjer bekapovanja i restauriranja baze podataka na osnovu potpunog bekapa baze podataka.
Primjer: Izvršavanje oporavka baze podataka na osnovu potpunog bekapa korištenjem Simple modela oporavka



Veoma često se koriste diferencijalni bekapovi u cilju ubrzavanja procesa bekapovanja i oporavka, pogotovo ukoliko se primjenjuje Simple model oporavka. Pošto nakon posljednjeg potpunog bekapa baze podataka postoje i drugi bekapovi koji se mogu koristiti prilikom oporavka treba navesti NORECOVERY
opciju.
Primjer: Korištenje diferencijalnog bekapa prilikom oporavka baze podataka, uz korištenje Simple modela oporavka



KORIŠTENJE FULL MODELA OPORAVKA
Baze podataka, za koje se primenjuje Full model oporavka, zahtjevaju bekapovanje i transakcionog loga. Prilikom restauriranja transakcionih logova, redosljed u kome se to izvršava je veoma značajan. Takođe, veoma je kritično očitavanje posljednjeg dijela transakcionog loga, ukoliko je moguće, kako bi se detektovale transakcije koje su se izvršavale nakon posljednjeg bekapa transakcionog loga.
Korištenje diferencijanih bekapova može da smanji broj operacija restauriranja, koje su neophodne prilikom oporavka baze podataka. Korištenjem diferencijalnog bekapa mogu se preskočiti bekapovi logova koji su se javili prije diferencijalnog bekapa.
Primjer: Full modela oporavka





IZVRŠAVANJE POINT IN TIME OPORAVKA
Baza podataka, za koju se primjenjuje Full model oporavka, može da se povrati u stanje koje đefiniše specifični log marker ili u stanje koje je bilo u određenom trenutku u prošlosti. To se realizuje restauriranjem dijela bekapa transakcionog loga. Sve modifikacije koje su izvršene nakon posmatranog trenutka ostaju zauvjek izgubljene. Uz pretpostavku da postoje odgovarajući bekapovi loga, baza podataka se može povratiti u stanje koje je definisano, na sljedeći način:
Specifičan trenutak u vremenu, definisan bekapom transakcionog loga
Prije markera loga
Vremenski trenutak koji je definisan trenutnom pozicijom markera loga
Bekapovi transakcionog loga, koji sadrže pakete modifikacija, ne mogu se parcijalno restaurirati, zbog načina na koje se modifikacije prate u bekapovima transakcionog loga.
Vraćanje baze podataka u stanje u kome je bila u određenom trenutku u vremenu realizuje se korištenjem sledeće sintakse:

Da se ne ponavlja proces oporavka, dobra ideja je navesti STOPAT opciju za svaki bekap transakcionog loga koji se koristi. Ukoliko je navedeno vrijeme prije vremenskog opsega koji je definisan u bekapu loga, ne izvršava se oporavak, zato što već postoji stanje koje je definisano nakon onog stanja za koje postoje podaci u logu. Ukoliko je navedeno vrijeme nakon vremenskog opsega definisanog u bekapu loga, uspješno se obavlja oporavak, ali se baza podataka ne oporavlja jer nije trenutno dosegnuto navedeno vrijeme. Ukoliko je navedeno vrijeme u vremenskom opsegu koji definišu podaci u bekapu loga, nastavlja se izvršavanje transakcija do tog trenutka, odnosno izvršava se oporavak baze podataka.
Primjer: Povratak baze podataka na stanje u trenutku 14:00 14.12.2014 (prije 14:18 14.12.2014 kada je izvršen UPDATE [DB2].[dbo].[STUDENT] SET [NACIN_STUDIRANJA]=null (možemo možda reći greškom))




KORIŠTENJE SQL SERVER MANAGEMENT STUDIO ALATA PRILIKOM OPORAVKA BAZE PODATAKA
SQL Server Management Studio sadrži veoma moćan interfejs za restauriranje baza podataka, grupa datoteka i transakcionih logova. Dijalog vezan za restauriranje baza podataka omogućava izvršavanje pojedinačnih operacija oporavka na osnovu raspoloživih datoteka koje sadrže bekapove, tako da se mogu postići identični rezultati kao prilikom korištenja Transact-SQL naredbi. Obezbjeđen je interfejs koji pomaže da se identifikuju prethodno izvršeni bekapi postojećih baza podataka. Mogu da se odaberu skupovi bekapova koje žele da se koriste prilikom oporavka baze podataka, a Management Studio alat će izvršiti odgovarajuće Transact-SQL naredbe u cilju oporavka baze podataka na osnovu izabranih bekapova. Da bi dobio neophodne informacije vezane za raspoložive bekapove, SQL Server Management Studio koristi nekoliko tabela msdb baze podataka.
Da bi se izvršio oporavak odgovarajuće baze podataka korištenjem SQL Server Management Studio alata, neophodno je uraditi sljedeće:
Otvoriti SQL Server Management Studio i povezati se sa SQL Server instancom kako bi se pristupilo restauriranju baze podataka.
Desnim tasterom miša selektovati Databases direktorijum, a zatim iz kontekstnog menija izaberati Restore Database stavku. Otvara se Restore Database dijalog.
Izabrati postojeću bazu podataka, ili unijeti novu bazu podataka koja se želi restaurirati.
Ukoliko se želi restaurirati baza podataka na osnovu prethodno izvršenih bekapova, neophodno je selektovati bazu podataka iz padajuće liste. Lista u donjem dijelu ekrana prikazuje postojeće skupove bekapova, koji se mogu koristiti prilikom oporavka baze podataka, kao što je prikazano na slici.

Ukoliko se za oporavak baze podataka želi koristiti specifičan uređaj za bekapovanje, selektovati radio taster koji se nalazi pored From Device obilježja.
Izaberati bekapove koje se žele koristiti prilikom restauriranja, selektovanjem polja za potvrdu koja se nalaze pored pojedinačnih bekapova u donjem dijelu dijaloga.
Ukoliko se želi povratiti stanje u kome se baza podataka nalazila u određenom trenutku u vremenu, neophodno je pritisnuti taster Timeline, a zatim izabrati datum i vrijeme na osnovu koga se definiše željeno stanje baze podataka.

Izabrati Options stranicu kako bi se provjerilo da li su opcije vezane za oporavak baze podataka ispravne.




SIGURNOST VEZANA ZA BEKAPOVANJE
SQL Server automatski definiše pristupne privilegije za datoteke baze podataka i datoteke bekapova, tako da SQL Service nalog i grupa lokalnih administratora ima odgovarajuće pristupne privilegije. Ukoliko service nalog nema privilegije za pristupanje datotekama za bekapovanje, to sprečava Windows korisnike da izvršavaju operacije bekapovanja i restauriranja. Preporučuje se ograničiti pristup svim direktorijumima koji se koriste za skladištenje bekapova.



Korisnički definisane funkcije, posebne teme i uvoz i izvoz podataka
Korisnički definisana funkcija (user-defined functions) ili UDF je veoma slična snimljenoj proceduri, uređen skup T-SQL naredbi unaprijed optimizovan i preveden i može da se poziva kao cjelina. Osnovna razlika među njima je način vraćanja rezultata. Zbog onoga što mora da se obezbjedi da bi se podržale te različite vrste vraćenih vrijednosti, korisnički definisane funkcije imaju nekoliko dodatnih ograničenja u odnosu na snimljene procedure.

Korisnički definisane funkcije mogu da sadrže jedan ili više parametara, izvršavaju izračunavanja ili neke druge operacije, odnosno vraćaju skalarnu vrijednost ili rezultujući skup. Funkcije koje vraćaju rezultujući skup smatraju se funkcijama koje vraćaju tabelarne vrednosti i one se koriste u FROM klauzuli upita. Skalarne funkcije se mogu koristiti u upitima, CHECK ograničenjima, kolonama čije se vrijednosti izračunavaju, upravljačkim naredbama, kao i na mnogim drugim mjestima.
Snimljenim procedurama se mogu proslijediti parametri i takođe se od njih mogu primiti vrijednosti u parametrima. Može se vratiti vrijednost, ali ta vrijednost je prije namjenjena kao znak uspjeha ili neuspjeha nego za vraćanje podataka. Može se takođe vratiti skup rezultata, ali taj rezultat se ne može koristiti u upitu dok se najprije ne insertuje u nekakvu tabelu (obično u privremenu tabelu) da bi se sa njim dalje radilo.
Međutim, korisnički definisanoj funkciji se mogu proslijediti parametri, ali ne mogu se dobiti od nje. Umjesto toga, koncept izlaznih parametara je zamjenjen daleko robustnijom vraćenom vrijednošću. Kao kod sistemskih funkcija, može da se vrati skalarna vrijednost - ali što je posebno praktično, ta vrijednost ne mora da bude tipa integer kao što je to slučaj kod snimljenih procedura. Umjesto toga, može vratiti skoro svaku vrstu SQL Server podataka.
UDF nisu ograničene na vraćanje skalarnih vrijednosti – mogu se takođe vratiti tabele.
Znači postoje dvije vrste korisnički definisanih funkcija:
one koje vraćaju skalarnu vrednost
one koje vraćaju tabelu
Opšta sintaksa kojom se pravi UDF:
CREATE FUNCTION [<schema name>.]<function name>
( [ <@parameter name> [AS] [<schema name>.]<data type>
[ = <default value> [READONLY]]
[ ,...n ] ] )
RETURNS {<scalar type>|TABLE [(<table definition>)]}
[ WITH [ENCRYPTION]|[SCHEMABINDING]|
[ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ] |
[EXECUTE AS { CALLER|SELF|OWNER|<’user name’>} ]
]
[AS] { EXTERNAL NAME <external method> |
BEGIN
[<function statements>]
{RETURN <type as defined in RETURNS clause>|RETURN (<SELECT statement>)}
END }[;]


FUNKCIJE I SNIMLJENE PROCEDURE – SLIČNOSTI I RAZLIKE
Korisnički definisane funkcije imaju dosta sličnosti sa snimljenim procedurama, ali postoji i niz razlika. Snimljene procedure su raspoložive u SQL Server okruženju mnogo duže od korisnički definisanih funkcija, i koristi ih veliki broj programera, iako korisnički definisane funkcije predstavljaju mnogo fleksibilniju opciju. Korisnički definisane funkcije imaju brojne prednosti u odnosu na snimljene procedure:
Modularno programiranje Korisnički definisane funkcije promovišu višestruko korišćenje Transact-SQL logike u aplikaciji. Korisnički definisane funkcije se mogu referencirati na mnogo više lokacija u odnosu na snimljene procedure.
Redukovanje mrežnog saobraćaja Korisnički definisanim funkcijama se može značajno redukovati mrežni saobraćaj, kao što se to čini korištenjem snimljenih procedura. Funkcija može da sadrži veliki broj naredbi i može da se koristi u nekoliko dijelova upita.
Keširanje plana izvršenja Planovi izvršenja, kreirani na osnovu korisnički definisanih funkcija, keširaju se na isti način kao i kada se radi o snimljenim procedurama. To znači da optimizator upita ne mora ponovo da kreira planove za izvršavanje upita za isti dio koda koji se koristi u više različitih upita.
Dve glavne razlike između snimljenih procedura i korisnički definisanih funkcija su način na koji se one izvršavaju i način na koji vraćaju rezultate nakon izvršavanja. Korisnički definisane funkcije obično obezbjeđuju mnogo fleksibilnije izračunavanje u odnosu na snimljene procedure, koje vraćaju skalarne vrijednosti ili jedan rezultujući skup podataka.
Takođe, primjena korisnički definisanih funkcija, a ne snimljenih procedura, omogućava da kod bude mnogo pregledniji, razumljiviji i da može mnogo jednostavnije da se održava kada za to postoji potreba.


PONOVNO PODSJEĆANJE NA UGRAĐENE FUNKCIJE
SQL Server obezbjeđuje veliki broj ugrađenih sistemskih funkcija. Ove funkcije se koriste prilikom izvršavanja različitih kategorija zadataka, kao što su rad sa stringovima, matematička izračunavanja i korištenje metapodataka. Korisnički definisane funkcije su veoma slične ugrađenim funkcijama, bar kada se radi o načinu njihovog korištenja. U brojnim situacijama, korisnički definisane funkcije uključuju neke od sistemskih funkcija u svom tijelu.
Ugrađene funkcije se mogu kategorizovati u determinističke i nedeterminističke funkcije. Deterministička funkcija će uvjek vratiti isti rezultat za isti skup ulaznih vrijednosti i uslova definisanih za bazu podataka. Primjer determinističke funkcije može biti SUBSTRING ukoliko se navedu isti ulazni parametri, uvjek će generisati identičan rezultat:
PRINT SUBSTRING('ABCDE1', 2, 1);
Izvršavanjem prethodnog koda uvjek ćete dobiti slovo B. GETDATE() je primjer jedne nedeterminističke funkcije. Rezultat izvršavanja sljedećeg primjera zavisi od trenutnog datuma i vremena:
PRINT GETDATE() + 1;
PRINT GETDATE() + 1;


UDF KOJI VRAĆA SKALARNU VRIJEDNOST
Ova vrsta korisnički definisane funkcije vjerovatno je najsličnija onome što se očekuje od funkcije. Kao i većina SQL Serverovih vlastitih ugrađenih funkcija, ona pozivajućem skriptu ili proceduri vraća jednu skalamu vrednost; funkcije kao što su GETDATE() ili USER() vraćaju skalarne vrijednosti.
Kao što je već pomenuto, jedna od zaista odličnih stvari u UDF je to što vraćena vrijednost nije ograničena samo na integer - već ona može da bude bilo kojeg tipa podataka dozvoljenog u SQL Serveru (uključujući korisnički definisane tipove podataka), osim BLOB-ova, kursora i vremenskih pečata. Čak i da vraća integer, korisnički definisana funkcija može da bude privlačna iz dva razloga:
Za razliku od snimljenih procedura, cijela svrha vraćene vrijednosti je da posluži kao svrsishodan podatak. Kod snimljenih procedura, vraćena vrijednost je zamišljena kao indikator uspjeha ili neuspjeha, a u slučaju neuspeha, da obezbjedi neku konkretnu informaciju o prirodi tog neuspjeha.
Funkcija može da se izvrši inlajn sa upitom. Na primjer, može da se uključi kao dio naredbe SELECT. Sa snimljenom procedurom to nije moguće.
Primjer 1: Kreirati listu studenata koji su za svaki ispit kojemu su pristupali ostvarili nadprosječan rezultat tog ispita. (ISPITNI)

I korak
Kreirati korisnički definisanu funkciju koja za svaki ispit i ispitni rok računa prosječnu ocjenu za sve studente koji su pristupili tom ispitu.
CREATE FUNCTION AVFG_Rez_Ispit (@ID_ISPITA int)
RETURNS float
AS
BEGIN
DECLARE @AVFG_Rez float;
SELECT @AVFG_Rez=AVG(BODOVA) FROM REZULTATI_ISPITA
WHERE ID_ISPITA=@ID_ISPITA
RETURN( @AVFG_Rez);
END;
Testiranje funkcije:
SELECT AVG(BODOVA) FROM REZULTATI_ISPITA
WHERE ID_ISPITA=1
SELECT * FROM REZULTATI_ISPITA
WHERE ID_ISPITA=1

SELECT AVG(BODOVA) FROM REZULTATI_ISPITA
WHERE ID_ISPITA=1
SELECT dbo.AVFG_Rez_Ispit (1)

SELECT ID_ISPITA, COUNT(ID_ISPITA),AVG(BODOVA) FROM REZULTATI_ISPITA
GROUP BY ID_ISPITA

II korak
Kreiranje liste studenata koji su ostvarili veći broj bodova na datom ispitu od prosjeka bodova svih studenata na tom ispitu. (ali za sve ispite)
Rješenje:
SELECT dbo.STUDENT.IME, dbo.STUDENT.PREZIME, dbo.PREDMET.NAZIV, dbo.REZULTATI_ISPITA.BODOVA, dbo.ISPIT.DATUM
FROM dbo.ISPIT INNER JOIN
dbo.REZULTATI_ISPITA ON dbo.ISPIT.ID = dbo.REZULTATI_ISPITA.ID_ISPITA INNER JOIN
dbo.PREDMET ON dbo.ISPIT.ID_PRED = dbo.PREDMET.ID INNER JOIN
dbo.STUDENT ON dbo.REZULTATI_ISPITA.ID_STUD = dbo.STUDENT.BR_INDEKSA
WHERE (dbo.REZULTATI_ISPITA.BODOVA > dbo.AVFG_Rez_Ispit(dbo.REZULTATI_ISPITA.ID_ISPITA))

Testiranje:
SELECT dbo.STUDENT.IME, dbo.STUDENT.PREZIME, dbo.PREDMET.NAZIV, dbo.REZULTATI_ISPITA.BODOVA, dbo.ISPIT.DATUM,
dbo.AVFG_Rez_Ispit(dbo.REZULTATI_ISPITA.ID_ISPITA) AS PROSJEK_BODOVA
FROM dbo.ISPIT INNER JOIN
dbo.REZULTATI_ISPITA ON dbo.ISPIT.ID = dbo.REZULTATI_ISPITA.ID_ISPITA INNER JOIN
dbo.PREDMET ON dbo.ISPIT.ID_PRED = dbo.PREDMET.ID INNER JOIN
dbo.STUDENT ON dbo.REZULTATI_ISPITA.ID_STUD = dbo.STUDENT.BR_INDEKSA
WHERE (dbo.REZULTATI_ISPITA.BODOVA > dbo.AVFG_Rez_Ispit(dbo.REZULTATI_ISPITA.ID_ISPITA))

Primjer 2: Validacija po modulu 11
Gdje se koristi:
Slučaj 1:
Jedinstveni matični broj građana (JMBG) je identifikacioni broj koji je dodjeljivan svim građanima SFRJ počev od 1976. godine prema mjestu rođenja, a za građane rođene prije 1976. godine prema prebivalištu. Ovaj lični broj je još uvjek u upotrebi u državama nastalim od bivših republika SFRJ.
Jedinstveni matični broj građana sastoji od 13 cifara u obliku DDMMGGGRRBBBK, gdje su:
DD - dan rođenja
MM - mjesec rođenja
GGG - zadnje tri cifre godine rođenja
RR - region rođenja, odn. prebivalište za građane rođene prije 1976. godine.
BBB - jedinstveni broj, dodjeljen prema
000-499 - muški
500-999 - ženski
K - kontrolna cifra
Kontrolna cifra se izračunava formulom (po modulu 11), gde je DDMMGGGRRBBBK = ABVGDĐEŽZIJKL
L = 11 - (( 7*(A+E) + 6*(B+Ž) + 5*(V+Z) + 4*(G+I) + 3*(D+J) + 2*(Đ+K) ) % 11)
ako je kontrolna cifra između 1 i 9, ostaje ista (L = K), a ako je kontrolna cifra veća od 9, postaje nula (L = 0). U formuli % označava ostatak cjelobrojnog deljenja.
Slučaj 2:
PRAVILNIK
O REGISTRACIJI I IDENTIFIKACIJI PORESKIH OBVEZNIKA
(Objavljen u "Sl. glasniku RS", br. 25 od 26. marta 2010)
Član 5.
JIB za pravna lica, organe uprave, upravne organizacije i ostale organizacije ima 13 cifara, a struktura mu je sljedeća:
1 2 3 4 5 6 7 8 9 10 11 12 13
4 4 Dž Dž Dž Dž Dž Dž K A A A C
a) 44 označava da je riječ o pravnom licu, organu uprave, upravnoj organizaciji ili ostalim organizacijama, sa sjedištem u Republici Srpskoj,
b) DžDžDžDžDžDž su sekvencijalni brojevi,
v) K je kontrolni broj po modulu 11 za prethodni osmocifreni dio broja,
g) AAA su dodatni kodovi za organizacione jedinice koje dodjeljuje PU (za glavnu organizacionu jedinicu ovaj kod je 000, a za poslovne jedinice kod može biti od 001 do 999) i
d) C je kontrolni broj po modulu 11 za cijeli trinaestocifreni broj.
-- =============================================
-- Author:<Asmir Butković>
-- Create date: <11.12.2007>
-- Description:<funBHASModule11>
-- =============================================
CREATE FUNCTION [dbo].[funBHASModule11]
(
-- Add the parameters for the function here
@unumber nvarchar(11)
)
RETURNS char(11)
AS
BEGIN
DECLARE @broj nvarchar(11),@cb smallint, @mod bigint,@control bit
SET @broj = cast(@unumber as bigint) + 1
SET @control=1
while @control=1 AND @broj<99999999999
begin
set @mod = substring(@broj,1,1)*5 + substring(@broj,2,1)*4 + substring(@broj,3,1)*3 + substring(@broj,4,1)*2 +substring(@broj,5,1)*7 + substring(@broj,6,1)*6 +substring(@broj,7,1)*5 + substring(@broj,8,1)*4 + substring(@broj,9,1)*3 + substring(@broj,10,1)*2
set @mod = @mod % 11
if @mod <> 1
begin
if@mod = 0
begin
set @cb=0
end
else
if @mod between 2 and 10
begin
set @cb=11-@mod
end
end
if @cb = substring(@broj,11,1)
begin
set @control=0
-- @broj<99999999999
end
else
set @broj = cast(@broj as bigint) + 1
end
RETURN @broj
END
Primjer upotrebe funkcije:
SELECT @unumber=max(LocalUnit_ID) FROM [BosBR].[dbo].[Local_Unit_Base] WHERE Entity=@Entity
SELECT @unumber=[BosBR].[dbo].[funBHASModule11] (@unumber)


UDF KOJI VRAĆA TABELU
Korisnički definisane funkcije u SQL Serveru nisu ograničene na vraćanje samo skalarnih vrijednosti. One mogu da vraćaju nešto daleko zanimljivije - tabele.
Funkcije koje vraćaju vrijednosti u tabeli su veoma moćno sredstvo za generisanje rezultujućeg skupa podataka. One se mogu koristiti svuda gdje je u upitu dozvoljeno korišćenje tabele ili pogleda. Kada se radi o korištenju, ove funkcije su mnogo fleksibilnije u odnosu na snimljenu proceduru koja vraća rezultujući skup podataka, pošto se rezultujući skupovi funkcija mogu spajati sa drugim tabelama u upitu.
SQL Server definiše dva tipa funkcija koje vraćaju više vrijednosti. Linijske funkcije koje vraćaju vrijednosti u tabeli su konceptualno slične pogledu sa parametrima. Složene funkcije koje vraćaju vrijednosti u tabeli omogućavaju vam da koristite više naredbi za kreiranje rezultujućeg skupa koji se predstavlja u obliku podataka tabele.


INLAJN UDF
Linijske funkcije koje vraćaju vrijednosti u tabeli veoma je jednostavno kreirati. Sadržaj linijske funkcije koja vraća vrednosti u tabeli je SELECT naredba sa odgovarajućim parametrima. Povratni tip podataka je uvjek tabela, ali je struktura povratne tabele definisana strukturom SELECT naredbe. U sljedećem primjeru prikazana je jedna linijska funkcija koja se koristi za određivanje ukupne prodaje proizvoda za dati CustomerlD identifikator:
CREATE FUNCTION Sales.ufnSalesByCustomer (@CustomerID int)
RETURNS TABLE
AS
RETURN
(
SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS Total
FROM Production.Product AS P
JOIN Sales.SalesOrderDetail AS SD
ON SD.ProductID = P.ProductID
JOIN Sales.SalesOrderHeader AS SH
ON SH.SalesOrderID = SD.SalesOrderID
WHERE SH.CustomerID = @CustomerID
GROUP BY P.ProductID, P.Name
);
Treba obratiti pažnju na to da se tijelo ove funkcije sastoji od jedne RETURN naredbe. U sljedećem primjeru koristi se upit u kome se primjenjuje prethodno definisana funkcija:
SELECT * FROM Sales.ufnSalesByCustomer(328);
Linijske funkcije ovog tipa predstavljaju veoma moćan alat, koji može da se koristi u situacijama u kojima je neophodno parametrizovanje upita. One omogućavaju mnogo veću fleksibilnost prilikom korištenja rezultujućeg skupa podataka.
Primjer 3: Funkcija prikazuje rezultate test i završnog ispita, za ID_PRED i DATUM održavanja ispita.
CREATE FUNCTION Rez_Test_Zavrs_Ispit(@ID_PRED int,@DATUM date)
RETURNS TABLE
AS
RETURN
(
SELECT dbo.STUDENT.IME, dbo.STUDENT.PREZIME, dbo.PREDMET.NAZIV, dbo.REZULTATI_ISPITA.BODOVA
FROM dbo.ISPIT INNER JOIN
dbo.REZULTATI_ISPITA ON dbo.ISPIT.ID = dbo.REZULTATI_ISPITA.ID_ISPITA INNER JOIN
dbo.STUDENT ON dbo.REZULTATI_ISPITA.ID_STUD = dbo.STUDENT.BR_INDEKSA INNER JOIN
dbo.PREDMET ON dbo.ISPIT.ID_PRED = dbo.PREDMET.ID
WHERE (dbo.ISPIT.ID_PRED = @ID_PRED) AND CONVERT(DATE,DATUM)= @DATUM
----FORMAT ( CONVERT(DATE,DATUM), 'd', 'de-de' )
);
SELECT * FROM Rez_Test_Zavrs_Ispit(2, '2012-11-28')

SELECT * FROM Rez_Test_Zavrs_Ispit(1, '2012-11-24')



SLOŽENE FUNKCIJE KOJE VRAĆAJU VRIJEDNOSTI U TABELI
Složene funkcije koje vraćaju vrijednosti u tabeli omogućavaju da se koristi više Transact-SQL naredbi prilikom kreiranja sadržaja tabele. Ove funkcije predstavljaju veoma moćnu alternativu snimljenim procedurama koje kreiraju rezultujuće skupove izvršavanjem više operacija.
Ovaj tip funkcija omogućava programeru da dinamički ispunjava tabelu u više koraka, koji su identični koracima koji se definišu u snimljenim procedurama, ali se mogu referencirati kao tabele u SELECT naredbi.
Prilikom korištenja složenih funkcija koje vraćaju vrijednosti u tabeli, struktura tabele mora da bude definisana u zaglavlju same funkcije. Za tabelu se definiše odgovarajuća promjenljiva, a sve operacije modifikovanja podataka se mogu primjenjivati samo nad tako definisanom promenljivom.
U sljedećem primjeru prikazana je funkcija koja je slična ufnSalesByCustomer funkciji, koja je definisali u prethodnom odeljku. Prvo se definiše promjenljiva vezana za tabelu, a zatim se ažurira ta promjenljiva, kako bi se uključio cjelokupan inventar proizvoda korištenjem prethodno kreirane skalarne funkcije. Naredbe neophodne za kreiranje funkcije prikazane su u sljedećem kodu:
CREATE FUNCTION Sales.ufnSalesByCustomerMS (@CustomerID int)
RETURNS @table TABLE
( ProductID int PRIMARY KEY NOT NULL,
ProductName nvarchar(50) NOT NULL,
TotalSales numeric(38,6) NOT NULL,
TotalInventory int NOT NULL )
AS
BEGIN
INSERT INTO @table
SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS Total, 0
FROM Production.Product AS P
JOIN Sales.SalesOrderDetail SD ON SD.ProductID = P.ProductID
JOIN Sales.SalesOrderHeader SH ON SH.SalesOrderID = SD.SalesOrderID
WHERE SH.CustomerID = @CustomerID
GROUP BY P.ProductID, P.Name;
UPDATE @table
SET TotalInventory = dbo.ufnGetTotalInventoryStock(ProductID);
RETURN;
END;
SELECT * FROM Sales.ufnSalesByCustomerMS(328);


POSEBNE TEME
U ovom poglavlju će biti obrađeni:
Hijerarhije
Operatori PIVOT i UNPIVOT
Kursori


HIJERARHIJE
Iako su hijerarhijske structure veoma česte u praksi, SQL uopšte, T-SQL posebno ne pruža mnogo u toj oblasti. Skrivene tabele, kursori, proceduralno procesiranje tabela u zadatom redosljedu, sve to treba da bi se nekako isčupali u specifičnoj situaciji. Malo toga je bilo prenosivo na neku drugu situaciju. SQL Server 2008 uvodi HIERARCHYID tip podataka da bi se neki od problema prevazišli. Predviđeno je da HIERARCHYID čuva podatke o poziciji čvora u hijerarhijskom stablu. Sljedeći odgovori mogu se dobiti i bez uvođenja parent-child kolona i složenih kverija:
Organizacione structure
Skup zadataka koji čine veći projekat, kao Gantt dijagrami
File sistemi (direktorijumi i poddirektorijumi)
Riječnici
Sastavnice i recapture, specifikacije materijala
Graficka reprezentacija linkova između web stranica
Primjer 4: Jednostavan organizacioni dijagram
Tradicionalni pristup – Model:

Tradicionalni pristup – Tabela:
ID	Parent ID	Employee Name
1	NULL	Larry
2	1	Matt
3	2	Craig
4	2	Tom
5	1	David
6	5	Melinda
7	1	Mark
8	7	Jennifer
9	8	Wendy
Ugnežđene setovi pristup – model:

HierarchyID pristup – model:


HierarchyID Funkcije:
GetAncestor
GetDescendant
GetLevel
GetRoot
IsDescendantOf
Parse
GetReparentedValue
ToString
Read *
Write
Primjer 5: Upotreba HierarchyID tipa podatka
CREATE TABLE SimpleDemo
(Level hierarchyid NOT NULL,
Location nvarchar(30) NOT NULL,
LocationType nvarchar(9) NULL);
INSERT SimpleDemo
VALUES
('/1/', 'Europe', 'Continent'),
('/2/', 'South America', 'Continent'),
('/1/1/', 'France', 'Country'),
('/1/1/1/', 'Paris', 'City'),
('/1/2/1/', 'Madrid', 'City'),
('/1/2/', 'Spain', 'Country'),
('/3/', 'Antarctica', 'Continent'),
('/2/1/', 'Brazil', 'Country'),
('/2/1/1/', 'Brasilia', 'City'),
('/2/1/2/', 'Bahia', 'State'),
('/2/1/2/1/', 'Salvador', 'City'),
('/3/1/', 'McMurdo Station', 'City');
SELECT CAST(Level AS nvarchar(100)) AS [Converted Level], *
FROM SimpleDemo ORDER BY Level;



OPERATORI PIVOT I UNPIVOT
Relacioni operator PIVOT u T-SQL-u se koristi za rotiranje tabele, tako što jedinstvene vrijednosti kolone transformiše u toliki broj kolona, pri čemu obavlja potrebne agregacije nad vrijednostima preostalih kolona. Jednostavnije rečeno, prevodi vertikalno orjentisanu tabelu u horizontalno orjentisanu tj. sa nivoa redova prelazi se na nivo kolona.
Često je skup vrijednosti kolone od koje kreiramo nove kolone konačan (ili želimo da prikažemo samo određene vrijednosti) i tada se, za taj skup, koristi PIVOT za predefinisani skup kolona.

U tabeli FactInternetSales, svaka porudžbina ima datum porudžbine; to je prirodan izvor za razlaganje podataka po godinama i mjesecima, na primjer, da bi se analizirali trendovi. Na primjer, da se za analizu traži da se prikažu mjesečni podaci po godinama. Na izlazu, svaka godina treba da bude razložena na mjesece, a mjesece treba prikazati po nazivima (na primer, „September”) a ne po rednim brojevima. Logičan početak takvog upita bi mogao da izgleda ovako:
USE [AdventureWorksDW2012]
GO
SELECT
YEAR(OrderDate) OrderYear,
FORMAT( OrderDate, 'MMMM', 'en-US' ) OrderMonth,
SUM(ExtendedAmount) SumExtendedAmount
FROM [dbo].[FactInternetSales]
GROUP BY YEAR(OrderDate), FORMAT( OrderDate, 'MMMM', 'en-US' )
ORDER BY SUM(ExtendedAmount) DESC

Svi pravi podaci su tu, ali nisu u lako čitljivom formatu. Pravi zahtjev nije da se dobije dugačka kolona podataka, već da sa lijeve strane budu godine a mjeseci po vrhu - kao PivotTable u Microsoft Excelu. Srećom, T-SQL nudi PIVOT.
Kada se koristi PIVOT, upit će biti iz dva dijela.
Postaviti upit da bi se dobile potrebne vrijednosti podataka.
Odrediti skup vrijednosti podataka iz jedne kolone koje treba da se razlože (pivot) u zasebne kolone i jednu vrijednost za sabiranje.
Sa da bi se prethodni upit konvertovao u PIVOT upit, najprije sa izvora treba ukloniti agregatnu funkciju (SUM i GROUP BY), dodati klauzulu PIVOT, a zatim sve uokviriti naredbom SELECT (čiji je skup rezultata izlaz izraza PIVOT):
SELECT PivotOrders.*
FROM (
SELECT
YEAR(OrderDate) OrderYear,
FORMAT( OrderDate, 'MMMM', 'en-US') OrderMonth,
ExtendedAmount
FROM FactInternetSales
) FormattedOrders
PIVOT (
SUM(ExtendedAmount)
FOR OrderMonth IN (
[January],[February],[March],[April],[May],[June],
[July],[August],[September],[October],[November],[December]
)
) PivotOrders

Treba primjetiti da je PIVOT uvjek agregator; u ćelijama PIVOT tabele ne mogu biti detaljni redovi. Važno ograničenje: kada se koristi PIVOT, moraju se znati sve moguće vrijednosti koje će se proglasiti kolonama. (Pogledati kako su imena mjeseci poredana (u uglastim zagradama) u prethodnom upitu) Ako se to ne može uraditi, ne može se koristiti pivot.

UNPIVOT, predvidivo, upravo je suprotno od PIVOT; uzima skup kolona i pretvara ih u skup redova. Međutim, vrijedi primjetiti da, mada su te dvije funkcije u pravom smislu suprotne, one nisu recipročne; to jest, ako se uzme skup podataka u PIVOT, a zatim primjeni UNPIVOT na dobijeni rezultat, neće se vratiti originalni skup podataka. Zašto? Ne treba zaboraviti, PIVOT je agregator... detaljni redovi su nestali. Vratiće se originalna struktura, ali ne mogu se vratiti svi originalni podaci.
Funkcija FORMAT je nova u SQL Serveru 2012. Ona ima dva obavezna argumenta (vrijednost koja treba da se formatira i ciljno formatiranje) i jedan neobavezan (kultura za rezultate), FORMAT koristi niz stringova .NET formata koji su već poznati korisnicima jezika C# ili VB.NET, što je praktično, a i svestan je kulture: ako se ne navede vrijednost za argument kulture, upotrijebiće se ono što koristi sesija. Ako se navede, izlaz može da se promjeni u bilo koji od instaliranih jezika.
SELECT dbo.STUDENT.IME, dbo.STUDENT.PREZIME, dbo.PREDMET.NAZIV,FORMAT ( CONVERT(DATE,DATUM), 'd', 'de-de' ),DATUM, dbo.REZULTATI_ISPITA.BODOVA
FROM dbo.ISPIT INNER JOIN
dbo.REZULTATI_ISPITA ON dbo.ISPIT.ID = dbo.REZULTATI_ISPITA.ID_ISPITA INNER JOIN
dbo.STUDENT ON dbo.REZULTATI_ISPITA.ID_STUD = dbo.STUDENT.BR_INDEKSA INNER JOIN
dbo.PREDMET ON dbo.ISPIT.ID_PRED = dbo.PREDMET.ID

Primjer 6: Kreirati upit koji daje broj studenata na pojedinim godinama studija prema načinu studiranja i za pojedinačne studijske programe. Izlaz u formi kao na slici. (ISPITNI)

RJEŠENJE:
Prikaz 1:
SELECT STUDIJSKI_PROGRAM
,NACIN_STUDIRANJA, GODINA_STUDIJA,
COUNT(BR_INDEKSA) BROJ_STUDENATA
FROM [dbo].[STUDENT]
GROUP BY [STUDIJSKI_PROGRAM]
,[NACIN_STUDIRANJA], GODINA_STUDIJA

Prikaz 2:
SELECT PivotOrders.*
FROM (
SELECT STUDIJSKI_PROGRAM
,NACIN_STUDIRANJA, GODINA_STUDIJA,
BR_INDEKSA
FROM [dbo].[STUDENT]
) FormattedStudent
PIVOT (
COUNT(BR_INDEKSA)
FOR GODINA_STUDIJA IN (
[1],[2],[3]
)
) PivotOrders

Prikaz 3 (Bez PIVOT):
SELECT STUDIJSKI_PROGRAM
,NACIN_STUDIRANJA,
(SELECT COUNT(*) FROM [dbo].[STUDENT] WHERE S1.STUDIJSKI_PROGRAM =STUDIJSKI_PROGRAM AND S1.NACIN_STUDIRANJA=NACIN_STUDIRANJA AND GODINA_STUDIJA =1) AS 'I GODINA',
(SELECT COUNT(*) FROM [dbo].[STUDENT] WHERE S1.STUDIJSKI_PROGRAM =STUDIJSKI_PROGRAM AND S1.NACIN_STUDIRANJA=NACIN_STUDIRANJA AND GODINA_STUDIJA =2) AS 'II GODINA',
(SELECT COUNT(*) FROM [dbo].[STUDENT] WHERE S1.STUDIJSKI_PROGRAM =STUDIJSKI_PROGRAM AND S1.NACIN_STUDIRANJA=NACIN_STUDIRANJA AND GODINA_STUDIJA =3) AS 'III GODINA'
FROM [dbo].[STUDENT] S1



KURSORI
DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR FOR select_statement
Rezultat bilo koje SELECT naredbe predstavlja neki skup torki. Ukoliko rezultujući skup čini samo jedna torka, tada će vrijednost promjenljivih, za koje je u SELECT klauzuli specificirana dodjela vrednosti, biti jednaka vrijednosti odgovarajućih izraza u rezultujućoj torci. Ako je rezultujući skup torki prazan skup, tada se promjenljivama dodjeljuje NULL vrijednost. Međutim, ako se rezultujući skup sastoji od više torki, tada će vrijednost promjenljivih po izvršenju SELECT naredbe biti jednaka vrijednosti odgovarajućih izraza u posljednjoj selektiranoj torki. Na ovaj način se gubi informacija o vrijednostima izraza za sve ostale torke rezultujućeg skupa. Ukoliko je neophodno znati vrijednosti za sve selektirane torke, a postoji mogućnost da rezultujući skup čini više torki, koriste se kursori.
Rad sa kursorima obično podrazumjeva sljedeće korake:
DECLARE - Deklaracija kursora pri kojoj se kursoru pridružuje neki SELECT iskaz i definišu karakteristike kursora (npr. da li se torke u kursoru mogu modifikovati ili ne).
OPEN - Otvaranje kursora koje podrazumjeva izvršavanje pridruženog SELECT iskaza i popunjavanje kursora torkama rezultujućeg skupa.
FETCH - Pregled torki u kursoru (pristup narednoj ili, eventualno, prethodnoj torki se naziva fetch-om).
Opcionalno, modifikaciju tekuće torke.
CLOSE - Zatvaranje kursora.

INSENSITIVE
Definiše kursor koji pravi privremenu kopiju podataka koje koristi kursor. Kursor se nakon punjenja isključivo koristi podacima iz ove privremene tabele (koja se kreira u tempdb bazi), te se izmjene koje su u međuvremenu učinjene nad baznim tabelama ne reflektuju na podatke koji se dobijaju pregledom kursora. Ovakav kursor ne dozvoljava modifikaciju torki rezultujućeg skupa. Ukoliko se izostavi ključna riječ INSENSITIVE, (commit-ovana) brisanja i modifikacije nad baznim tabelama će se reflektovati pri narednim fetch-ovima.
SCROLL
Specificira da su sve fetch opcije (FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE) na raspolaganju. Ukoliko nije naveden SCROLL jedina podržana fetch opcija je NEXT.
select_statement
Je standardni SELECT iskaz koji definiše rezultujući skup kursora.
READ ONLY
Onemogućuje vršenje modifikacija na osnovu sadržaja kursora (koje je, inače, dozovoljeno po default-u). Kursor se ne može referencirati u WHERE CURRENT OF klauzuli UPDATE ili DELETE iskaza.
UPDATE [OF column_name [,...n]]
Definiše kolone koje se mogu modifikovati unutar kursora. Ukoliko se koristi OF column_name [,...n], modifikacije je moguće vršiti samo nad navedenim kolonama. Ukoliko se navede samo UPDATE bez navođenja liste kolona, sve kolone se mogu modifikovati.
OPEN cursor_name
Otvaranje kursora podrazumeva izvršavanje SQL naredbe definisane prilikom deklaracije kursora i podrazumjeva popunjavanje kursora torkama rezultujućeg skupa izvršene SQL naredbe. Po otvaranju kursora aktuelna torka je prva torka rezultujućeg skupa.
Naredni primjer prikazuje otvaranje kursora i pregled torki unutar kursora.
DECLARE Employee_Cursor CURSOR FOR
SELECT LastName, FirstName
FROM AdventureWorks.HumanResources.vEmployee
WHERE LastName like 'B%';
OPEN Employee_Cursor;
FETCH NEXT FROM Employee_Cursor;
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Employee_Cursor
END;
CLOSE Employee_Cursor;
DEALLOCATE Employee_Cursor;
FETCH [ [ NEXT | PRIOR | FIRST | LAST] { { [ GLOBAL ] cursor_name } | @cursor_variable_name } [ INTO @variable_name [ ,...n ] ]
FETCH omogućava pristup torkama koje se nalaze u kursoru. Pomoću FETCH naredbe može se biti sigurno da će svaka torka koja se nalazi u kursoru proći određene modifikacije zadate u tijelu okidača/storne procedure.
NEXT – predstavlja defaultnu fetch opciju. Podrazumjeva pristup svakoj torci respektivno. Prvim fetch-om pristupa se prvoj torci, zatim drugoj… Postupak se ponavlja sve dok se ne doĎe do poslednje torke.
PRIOR - podrazumjeva pristup, obradu, predhodne torke. Kursor se pozicionira tako da uvjek pristupa predhodnoj torci i za nju daje rezultate. U slučaju da se radi o prvom fetch-u (pristup prvoj torci) kao rezultat promjenljive će imati null vrijednost i kursor će se pozicionirati na prvu torku. Tek pristupom drugoj torci, doći će do obrade prve torke.
FIRST – pozicioniranje na prvu torku u kursoru.
LAST – pozicioniranje na posljednju torku u kursoru.
CLOSE {cursor_name | cursor_variable_name }
Zatvara otvoren kursor što podrazumjeva pražnjenje kursora (oslobađa se memorijski prostor koji se koristio za čuvanje podataka kursora), ali njegova deklaracija ostaje i dalje aktuelna, te se zatvoren kursor uvjek može ponovo otvoriti.
DEALLOCATE { cursor_name | @cursor_variable_name }
Poništava deklaraciju kursora. Ukoliko se ovo ne bi uradilo prilikom sljedećeg aktiviranja okidača/storne procedure bi nastao problem usljed nemogućnosti ponovne deklaracije kursora sa istim nazivom.
@@FETCH_STATUS
Predefinisana (sistemska) varijabla koja vraća status posljednjeg FETCH iskaza nad aktivnim kursorom.
Return vrednost	Opis
0	FETCH iskaz je bio usp
j
ešan.
-
1	FETCH iskaz nije uspio ili je torka izvan rezultujućeg skupa.
-
2	Fetch-ovana torka ne postoji.


UVOZ I IZVOZ VELIKE KOLIČINE PODATAKA
Problemi kojima se bavio ovo poglavlje postoje u velikom procentu sistema, bar u nekom vidu, kako da se podaci dopreme u sistem iz stranih izvora ili iz postojećeg sistema u neki drugi. Potrebna je alatka koja omogućava ekstrahovanje, transformaciju i unošenje (Extract, Transform and Load) podataka u bazu podataka – takva alatka se obično jednostavno naziva ETL alatkom.

Dostupni alati za transfer podataka:

BULK COPY PROGRAM (BCP) je program komandne linije koji je isključivo namjenjen za masovno premještanje formatiranih podataka u SQL Server i iz njega. Iako se može koristiti s opcijom upita se obično koristi za prijenos podataka bez transformacije.

BULK INSERT
Transact-SQL iskaz koji uvozi podatke direktno iz datoteke podataka u tabelu ili pogled baze podataka.
Može se koristiti za uvoz, ali ne i za izvoz.

OPENROWSET() funkcija je primaran metod za izvršavanje upita na odredišnom serveru na ad hok bazi. Svako izvršavanje OPENROWSET() funkcije je samo po sebi specificno i sadrži sve što jedan upit treba da zna da bi uspostavio konekciju i izvršio neohodne operacije. Funkcija osvaruje vezu putem OLEDB i zbog toga je ova opcija razpoloživa za sve izvore podataka koji su OLEDB kompatibilni.

XML Bulk Load
XML podaci mogu biti uvezeni u obliku binarnog toka, u postojeći red.
Import / Export Wizard
SQL Server Import and Export Wizard je alat koji je dio SQL Server Management Studio-a. Koristi se za prebacivanje podataka iz izvora u destinaciju, pri čemu i izvor i destinacija mogu biti tabela iz neke baze, ali isto tako i neki dokument (Excel, tekstualni dokument, csv…).
Da bi se pokrenuo Wizard potrebno je otvoriti SQL Server Management Studio i u Object Explorer-u se pozicionirati na bazu iz koje se izvoze podaci. Desnim klikom se otvara meni iz koga treba izabrati Tasks/Export Data… Ovim je pokrenut Wizard. Najpre treba izabrati izvor.

Za sada, u padajućoj listi Data Source ostaviti postojeći izbor SQL Native Client - to je obično prioritetan metod pristupanja SQL Serveru. Nakon toga, podesiti informacije za provjeru autentičnosti (u ovom primjeru je odabrano Windows Authentication), a kao bazu podataka odabrati AdventureWorks.
Sada pritisnuti Next, pa će se ugledati, na prvi pogled, tako reći isti okvir za dijalog. Međutim, ovde će trebati promjeniti neke stvari. Izmjeniti polje Destination u Flat File Destination kao na slici, ostale opcije u nešto što je primjerenije odredištu u sistemu datoteka a ne tabeli na SQL Serveru.

Sada treba odlučiti na koji način će se birati podaci iz izvorne baze podataka. Ovde postoje dvije mogućnosti:
Kopiranje podataka iz jedne ili više tabela ili prikaza (Copy data from one or more tables or views): Dobija se direktna kopija iz tabele ili prikaza. Sa podacima iz prikaza se postupa isto kao da su iz tabele, ali opstaju svi spojevi i filtriranja enkapsulirana u prikazu.
Pisanje upita kojim se određuju podaci za transfer (Write a query to specify the data to transfer): Time se omogućava biranje tako reći svake operacije koja daje skup rezultata. Moguće je upotrijebiti bilo kakvu naredbu T-SQL-a, čak i pozive snimljenih procedura ili funkcija koje prave tabele.
Biramo prvu opciju, idemo na Next i prelazimo na izbor tabele iz koje izvozimo podatke.







SQL injection napad, praćenje aktivnosti SQL Servera i sličnosti i razlike između Oracle i MS SQL Server
Vjerovatno najosetljivija tačka u pogledu očuvanja sigurnosti baza podataka jeste provjera podataka koje korisnik šalje bazi. Ako je posetiocima neke web stranice dozvoljen unos podataka u bazu, potrebno je provjeriti da li podaci koje je unio korisnik sadrže neke SQL naredbe.

SQL injection je napadačka tehnika koja koristi sigurnosnu ranjivost kod pristupa web aplikacije bazi podataka.
Na taj način moguće je ugroziti sigurnost web aplikacije koja konstruira SQL upite iz podataka unesenih od strane korisnika.
Web aplikacije mogu koristiti korisnički unesene podatke kako bi stvorile posebne SQL upite za rad s dinamički generiranim web stranicama.
Ranjivost se javlja uslijed neodgovarajućeg filtriranja znakova posebne namjene od kojih se kreira SQL iskaz ili u slučaju kada nisu implementirane određene restrikcije vezane uz ulazne podatke od kojih se stvaraju SQL upiti. Napadač spomenute ranjivosti može iskoristiti za izmjenu konstrukcije pozadinskog SQL upita. Ukoliko napadač uspije izmijeniti upit, on će se izvesti s dozvolama izvorne
SQL naredbe. Posljedica napada je preuzimanje kontrole nad podacima u bazi podataka.
Napad SQL injection je direktna posljedica lošeg dizajna aplikacije koja kreira dinamičke SQL upite na osnovu interakcije sa korisnikom. To omogućava napadaču da prosljedi bazi podataka SQL upit po svojoj volji. Ukoliko se ovo zanemari i ostavi prostor za mogući napad, svako dalje obezbjeđivanje DBMS postaje beskorisno.
Iako su SQL injection napadi po svojoj prirodi jednostavni (napadač aplikaciji prosljeđuje unos koji sadrži SQL upit), poželjno je da napadač koji želi da izmjeni podatke u bazi poznaje strukturu baze (koje tabele postoje, od kojih se kolona koja tabela sastoji itd). Izvođenje napada nad bazom čija je struktura nepoznata je znatno komplikovanije.


VRSTE SQL INJECTION NAPADA
Posljedice SQL injection napada mogu varirati od prikupljanja osjetljivih podataka do manipulisanja informacijama baze podataka, i od izvršenja komandi na sistemskom nivou do uskraćivanja usluga veb aplikacije.

Posljedice zavise i od baze podataka, ciljane mašine i od uloga i privilegija koje SQL naredbe imaju.
Postoje 4 glavne kategorije SQL injection napada:
Modifikacija SQL upita (SQL Manipulation). Napadač modifikuje SQL upit pomoću skupovnih operacija (najčešće UNION) ili mijenja WHERE klauzulu sa ciljem dobijanja drugačijeg rezultata. Najpoznatiji napad ove vrste je modifikacija WHERE klauzule upita za provjeru identiteta korisnika tako da klauzula uvjek daje rezultat TRUE.
Umetanje koda (Code Injection). Napadač unosi novi SQL upit ili novu komandu u postojeći SQL upit. Ova vrsta napada funkcioniše isključivo kod DBMS koji podržavaju višestruke SQL upite po jednom zahtjevu bazi podataka (na primer, EXECUTE naredba u MS SQL Serveru). Ovakav napad na Oracle DBMS se teško ostvaruje.
Umetanje funkcijskih poziva (Function Call Injection). Napadač umeće ugrađene ili neke korisničke funkcije u ranjiv SQL upit. Ovi funkcijski pozivi se zatim mogu iskoristiti za izvršavanje funkcijskih poziva operativnog sistema ili za izmjenu podataka u bazi.
Prekoračenja bafera (Buffer Overflows). Ovo je podvrsta prethodno opisane vrste SQL injection napada. Ranjivost postoji u nekim funkcijama DBMS-a koje mogu izazvati prekoračenje Buffer-a.


NAJČEŠĆE METODE SQL INJECTION NAPADA
Različiti tipovi napada se u velikom broju napada ne koriste izolovano, mnogi tipovi se koriste zajedno ili u nizu, zavisno od ciljeva napadača.


TAUTOLOGIJA
Ciljevi napada: Zaobilaženje autentifikacije, identifikacija ranjivih parametara, izvlačenje podataka.
Opis: Glavni cilj napada baziran na tautologiji je da se ubrizga kod u jednu ili više uslovnih naredbi tako da uvijek vraćaju vrijednost TRUE. Posljedice ovog napada zavise od uticaja vraćenih rezultata upita na aplikaciju. Najčešće se upotrebaljva za zaobilaženje strane autentifikacije korisnika i izvlačenje podataka.

SELECT UserName, Password,Description FROM Users WHERE UserName = 'admin' AND Password = 'Pa$$w0rd'
U ovom tipu ubrizgavanja, napadač koristi polje forme čiji sadržaj se poredi u WHERE klauzuli upita. Transformacija uslova u tautologiju ima za posljedicu vraćanje svih redova ciljne tabele u bazi podataka. U principu, da bi ovakav napad vratio podatke, napadač mora ne samo da razmotri ranjive parametre, već mora i procjeniti rezultate upita. I konačno, napad je uspješan ukoliko kod ili prikazuje zapise iz tabele ili obavlja neku akciju, ukoliko je bar jedan od podataka vraćen.
Primjer 1: U primjeru napada, napadač ubacuje svoj dio koda u obliku anything' OR 'x'='x "Korisnicka loz:" polje, što u bazi podataka rezultira pravljenje upita:
SELECT UserName, Password,Description FROM Users WHERE UserName = 'anything' AND Password = 'anything' OR 'x'='x'

Kod ubačen u uslov (OR 'x'='x' ILI OR 1=1 ) pretvara čitavu WHERE klauzulu u tautologiju. S obzirom da je uslov tautologija, upit se pretvara u TRUE za bilo koji
unos korisničog imena i pasworda. Na prikazanom primjeru povratna informacija će biti NOTNULL, što za posljedicu ima da aplikacija zaključuje da je autentifikacija korisnika uspješna i ukoliko je aplikacija kodirana, da prikaže informaciju o korisniku, prikazaće sve naloge koji se nalaze u bazi podataka.


ILEGALNI/LOGIČNO NETAČNI UPITI
Ciljevi napada: Identifikacija ranjivih parametara, obavljanje "finger-printing" baze podataka, izvlačenje podataka.
Opis: Cilj napada je da napadač prikupi važne informacije o tipu i strukturi "back-end" baze podataka koju koristi veb aplikacija. Ovakav napad se smatra preliminarnim napadom, odnosno prikupljanje informacija za druge napade.
Iskorištavanje ranjivosti ovim napadom je u tome da podrazumjevana "error" strana vraćena od servera aplikacije je često previše opisna. Na primjer, jednostavna poruka o grešci može često da otkrije napadaču ugrožene parametre.
Dodatne informacije o grešci, prvenstveno namjenjene kao pomoć programerima u otklanjanju grešaka u aplikaciji, kasnije pomažu napadačima da prikupljaju informacije o šemi baze podataka.
Kada se izvodi ovakav napad, napadač pokušava da ubaci uslove koji će prouzrokovati sintaksne, logičke ili greške konverzije tipova podataka u bazi podataka. Sintaksne greške se koriste za identifikovanje ranjivih parametara. Logičke greške često vraćaju informaciju o nazivima tabela i kolona koje su prouzrokovale grešku.
Greška prilikom konverzije tipova podataka se koristi da se dođe do tipa podatka kolone ili da se izvuku podaci.
Primjer 2: U primjeru napada, napadač ubacuje svoj dio koda u obliku anything' AND email IS NULL "Korisnicka loz:" polje, što u bazi podataka rezultira pravljenje upita:
SELECT UserName, Password,Description FROM Users WHERE UserName = 'anything' AND Password = 'anything' AND email IS NULL
Primjer 3: Greške konverzije tipova podataka, pokušaj da se dođe do naziva tabele koja sadrži korisničke podatke.
convert (int,(select top 1 name from sysobjects where xtype='u' and name like '%us%'))


UPIT UNION
Ciljevi napada: Zaobilaženje autentifikacije, izvlačenje podataka.
Opis: U upit UNION napadima, napadač koristi ranjive parametre za izmjenu skupa podataka koje vraća postavljeni upit. Ovom tehnikom, napadač može natjerati aplikaciju da vrati podatke iz neke druge tabele, a ne iz tabele za koju je programer napisao kod. Napadači ovo postižu upisivanjem svog koda: UNION SELECT <ostatakUbačenogUpita>. S obzirom da napadači u potpunosti preuzimaju kontrolu nad drugim,odnosno ubačenim upitom, sa lakoćom izvlače podatke iz tabele. Rezultat ovog napada je da baza podataka vraća skup podataka traženih originalnim upitom, i podatke tražene drugim ubačenim upitom.
Primjer 4: U primjeru napada, napadač ubacuje svoj dio koda u obliku anything' AND email IS NULL "Korisnicka loz:" polje, što u bazi podataka rezultira pravljenje upita:
SELECT UserName, Password,Description FROM Users WHERE UserName = 'admin' AND Password = 'anything'
UNION SELECT UserName, Password,Password FROM Users WHERE UserName='admin'


PIGGY-BACKED UPITI
Ciljevi napada: Izvlačenje podataka, dodavanje ili izmjena podataka, uskraćivanje usluga, daljinsko izvršavanje komandi.
Opis: Ovakvim napadima, napadač pokušava ubaciti dodatne upite u orginalni upit. Razlikujemo ovaj tip napada od drugih, jer u ovom slučaju, napadači ne pokušavaju mijenjati originalnu namjenu upita, suprotno, pokušavaju ubaciti nove i različite "piggy backed" upite. Kao rezultat toga, baza podataka prima višestuke SQL upite. Prvi, koji se izvršava je normalan upit, a zatim se ubacuje napadačev upit koji se izvršava kao dodatak prvom. Ovakav način napada može biti veoma štetan. Ako bude uspješan, napadači mogu ubaciti praktično bilo koji tip SQL komande, uskladištene procedure, dodatne upite i izvršavati ih zajedno sa originalnim upitom. Ranjivost na ovu vrstu napada zavisi od konfiguracije baze podataka koja dozvoljava višestruke naredbe u jednom stringu.
Primjer 5: Ukoliko napadač upiše anything'; DROP TABLE [dbo].[Customer] -- and šifra=' u polje za šifru, aplikacija će generisati sljedeći upit:
SELECT UserName, Password,Description FROM Users WHERE UserName = 'admin' AND Password = 'anything'; DROP TABLE [dbo].[Customer] -- and šifra=''
Nakon završavanja prvog upita, baza podataka će prepoznati karatker (";") koji označava kraj upita, i potom izvršiti drugi upit kao zaseban. Rezultat izvršavanja drugog upita će biti brisanje tabele kupci, što dovodi do uništavanja važnih podataka. Drugi tipovi upita mogu upisivati nove korisnike u bazu podataka ili da izvršavaju uskladištene procedure.
Napomena: Mnoge baze podataka ne zahtjevaju specijalni karakter za razdvajanje različitih upita, te jednostavno skeniranje u traženju specijalnog karaktera neće imati efekta u prevenciji od ove vrste napada.


USKLADIŠTENE PROCEDURE
Ciljevi napada: Uskraćivanje usluga aplikacije, daljinsko izvršavanje komandi.
Opis: U ovim napadima, napadači pokušavaju izvršavati uskladištene procedure koje već postoje u bazi podataka.
Danas, većina proizvođača baza podataka u baze ugrađuju uskladištene procedure koje povećavaju funkcionalnost baze podataka i dozvoljava interakciju sa operativnim sistemom.
Nažalost, jednom kada napadač dođe do informacije o kojoj bazi podataka se radi, napadači mogu izvršavati procedure na operativni sistem. Programeri su često u zabludi kada koristeći sistemske uskladištene procedure u razvoju Web aplikacije misle da će izbjeći SQL injection ranjivost, ne znajući da je aplikacija isto ranjiva kao i normalna aplikacija. Pored toga, pošto su uskladištene procedure često napisane u posebnim skript jezicima, mogu sadržati i ostale ranjivosti, poput prekoračenja bafera, što omogućava napadačima da pokrenu kontrolu koda na serveru ili prošire svoje privilegije.


ZAŠTITA OD SQL INJECTION NAPADA
SQL Injection napad može biti jednostavno sprečen malim izmjenama aplikativnog koda. Programeri aplikacija baza podataka moraju biti disciplinovani u primjeni zaštitnih mjera za svaku proceduru i funkciju koja može biti dostupna preko mreže. Svaki dinamički SQL iskaz mora biti zaštićen. Jedan nezaštićeni SQL iskaz može dovesti do kompromitovanja aplikacije, podataka ili servera baze podataka.
Provjera tipa ulaznih podataka: SQL injection napad može biti izveden ubacivanjem komande u numerički ili string parametar. Čak i jednostavna provjera ulaznih parametara može sprečiti mnogo napada. Na primjer, ukoliko je ulazni parametar numeričkog tipa, programer može odbiti sve ulazne parametre koji nisu numeričkog tipa. Mnogi programeri slučajno izostavljaju ovu vrstu provjere, jer ulazni parametar skoro uvijek predstavljaju kao string, bez obzira koji mu je sadržaj ili za šta se koristi.
Kodiranje ulaznih parametara: Ubacivanje napadačevog koda u string parametar se često postiže pomoću metaznakova koji vara SQL "parser" u tumačenju korisničkog unosa kao SQL tokena. Iako je moguća zabrana upotrebe ovih meta-karaktera time bi se ograničila mogućnost legalnog korisnika da specifira neškodljive ulazne parametre koji sadrže karaktere. Bolje riješenje je da se koriste funkcije koje bi kodirale string i na taj način svi meta-karakteri bi se posebno kodirali i interpretirali u bazi podataka kao normalni karakteri.
Upoređivanje paterna: Programeri bi trebali uspostaviti rutinu validacije ulaznih parametara da bi se mogli razlikovati dobri i loši ulazni parametri. Ovakav pristup se generalno zove pozitivna validacija, za razliku od negativne validacije, koja u ulaznim parametrima traži zabranjene paterne ili SQL tokene. Pošto programeri ne bi mogli da predvide svaki tip napada koji bi mogao biti upotrijebljen protiv aplikacije, trebali bi specifirati sve forme legalnih ulaznih parametara, pozitivna validacija je pouzdaniji način za provjeru ulaznih parametara.
Identifikacija svih ulaznih izvora: Programeri moraju provjeriti sve ulaze u njihovu aplikaciju, jer ukoliko se ti ulazi koriste za pravljenje upita ovi ulazi mogu biti mogući putevi da napadači ubace svoj kod. Jednostavno rečeno, svi ulazi moraju biti provjereni.


METODE TESTIRANJA NA SQL INJECTION
Provjera ranjivosti je postupak otkrivanja poznatih ranjivosti i slabosti u mrežnim sredinama i računarima, a provodi se pomoću specijaliziranih alata koji, u procesu provjere ranjivosti, obavljaju niz testova na segmentu računarske mreže definisanom na početku testa.
Provjera ranjivosti je automatizirani postupak te je upotreba alata za provjeru sigurnosti jedini način da se u složenoj računarskoj mreži dobiju informacije o stupnju ranjivosti pojedinih računara ili servera.
Alati za provjeru ranjivosti mogu se podijeliti u dvije skupine, alati za lokalnu provjeru ranjivosti i alati za udaljenu provjeru ranjivosti. Alati za lokalnu provjeru ranjivosti pokreću se isključivo na sistemu koji se ispituje, dok alati za udaljenu provjeru ranjivosti ispitivanja obavljaju preko mreže pa ne trebaju nužno biti pokrenuti na sistemu koji se ispituje.
Automatizirani testni programi nisu u stanju do kraja razjasniti i utvrditi važnost pojedine ranjivosti te ju staviti u kontekst okoline u kojoj se ona javlja pa je potreban ljudski faktor za prepoznavanje i vrednovanje ranjivosti sistema.
Neki od sigurnosnih propusta koji se mogu pojaviti na računalnoj mreži su ranjivosti na XSS i SQL injection napade.
XSS (eng. Cross-site scripting) je napadačka tehnika koja web aplikaciju prisiljava da korisničkom pregledniku proslijedi preoblikovan odgovor, koji se zatim učitava i prikazuje. Preoblikovani odgovor obično uključuje dijelove programskog koda pisanog u programskom jeziku JavaScript, ali može biti kreiran i nekim drugim jezikom kojeg podržava korisnikov web preglednik. Neki od tih jezika su HTML, VBScript, ActiveX, Java i Flash. (XSS napadi neće biti analize u ovom predmetu)
Postoji mnogo alata za provjeru XSS i SQL injection ranjivosti, a jedan od njih je Exploit Me. Radi se o skupu alata za udaljenu provjeru ranjivosti koji se sastoji od XSS Me te SQL Inject Me programa.


SQL INJECT ME
SQL Inject Me je alat za testiranje web aplikacija na SQL injection ranjivosti. Rad alata moguće je usporediti ručnim ispitivanjem. Testiranje ranjivosti izgleda kao da zlonamjeran korisnik upisuje različite nizove znakova u polja obrazaca na web aplikaciji, pri čemu to alat izvodi mnogo brže i efikasnije.

SQL Inject Me ne analizira niti izvorni kod, niti računarsku mrežu već isključivo testira kreirane web aplikacije. Programom nije moguće kompromitirati ispitivani sistem, niti izvesti napad na njega.


SQL INJECT ME – POKRETANJE I OPCIJE
SQL Inject Me alat pokreće se odabirom u izborniku Firefox web preglednika: Tools → SQL Inject Me → Open SQL Inject Me Sidebar.
Nakon pokretanja i učitavanja web aplikacije u preglednik program izgleda ovako:

SQL Inject Me alat prikazuje trenutne vrijednosti ulaznih polja učitane stranice. Također je potrebno staviti kvačicu pored polja koje se želi testirati.
Program testira jednu po jednu kvačicom označenu vrijednost.
Alat radi tako da na obrascima učitanih stranica zamijeni ulazne vrijednosti sa napadačkim nizovima znakova reprezentativnim za SQL Injection napade.
Postoje tri vrste rezultata:
Promašaji (eng. Failures) – broj testova koji su detektirali postojanje potencijalne ranjivosti na SQL Injection napad.
Upozorenja (eng. Warnings) – broj testova čiji rezultat upućuje na mogućnost postojanja SQL Injection ranjivosti (npr. postoji razlika u odgovoru servera nakon što je upisana uobičajena ulazna vrijednost i nakon testiranja napadačkog izraza).
Prolaz (eng. Passes) – broj testova koji nisu otkrili postojanje SQL Injection ranjivosti.
Slijedeće slike prikazuju rezultate izvođenja jednog testa (vidljiv je pronalazak 54 mogućih SQL Injection ranjivosti):



Rezultati testa za www.ecampus.ba



ZAKLJUČAK
U današnje je vrijeme sigurnost aplikacija bitan faktor u procesu njihove izgradnje. Kako bi se očuvali podaci i onemogućio pristup neovlaštenim korisnicima, posebno je važno smanjiti mogućnosti XSS i SQL injection napada. Mnogo je razloga zašto programeri pišu ranjive aplikacije, a većina ih se može svesti na nedovoljnu educiranost i zanemarivanje važnosti sigurnosne komponente. Izučavanje računarske sigurnosti može biti naporan posao koji iziskuje znatno vrijeme potrebno za dodatne analize napada i smišljanje njihove prevencije. Svaka web aplikacija može sadržavati sigurnosni propust koji je rezultat najobičnije nepažnje, a koji može biti poguban, kako za stabilnost aplikacije, tako i za podatke koje ona štiti.
Alati za otkrivanje ranjivosti web aplikacija od velike su pomoći programerima pri stvaranju sigurnih aplikacija. Opisani Exploit Me jedan je od takvih. Korištenjem njegovih funkcionalnosti programeri mogu otkriti tačan uzrok problema, a sve to daleko prije završetka razvoja, odnosno daleko prije eventualnih napadača. Unatoč tome, Exploit Me skup alata ima i svoja ograničenja te rezultate njegovog rada nikako ne treba uzeti zdravo za gotovo kao jamstvo neranjivosti, već samo kao korisnu sugestiju o mogućim problemima.


PRAĆENJE AKTIVNOSTI SQL SERVERA
Kao administrator baze podataka, neophodno je poznavati opcije raspoložive za praćenje aktivnosti SQL Servera. Poznavanje raspoloživih opcija omogućava da detektovanje probleme vezanih za performanse.


KORIŠTENJE SYSTEM MONITOR ALATA
Nadgledanje funkcionisanja SQL Servera može biti veoma zahtjevan zadatak, koji zahtjeva razumjevanje različitih performansi.

Iako mnoge organizacije pokušavaju da riješe probleme vezane za performanse dodavanjem novog hardvera, to obično nije najefikasniji način za rješavanje problema. Neophodno je nadgledati nekoliko nivoa, kako bi se utvrdilo da li je poboljšanje hardvera efikasan način za rješavanje problema.
System Monitor je Microsoft Management Console (MMC) alat za upravljanje, koji predstavlja dio Performance konzole koja se može pronaći u Administrative Tools programskoj grupi. System Monitor je sastavni dio operativnog sistema. Mnogi korisnici i danas ovaj alat nazivaju Performance Monitor, zbog velike sličnosti sa Performance Monitor alatom koji je sastavni dio Windows NT 4.0 operativnog sistema. System Monitor je po mnogo čemu veoma sličan starom Performance Monitor alatu, ali postoje razlike u tome na koji način se bilježe podaci vezani za performanse sistema. Performance Monitor alat je bilježio sve podatke o performansama posmatranog objekta, pružajući brojna mjerenja koja nisu bila od koristi, povećavajući značajno veličinu loga.
Glavne primjene System Monitor alata su:
Nadgledanje performansi u realnom vremenu, lokalno i globalno
Analiziranje performansi na osnovu podataka zabilježenih u logovima
Prikazivanje podataka o performansama u obliku grafikona, histograma i različitih izvještaja
Snimanje i očitavanje skupova parametara kojima se definišu performanse sistema na osnovu nadgledanja
Prilikom korištenja System Monitor alata i sa njim povezanih alata, razmatraju se objekti kojima se definišu performanse, brojače i instance.
Objekat kojim se definišu performanse sadrži grupu brojača i instanci, koje su povezane sa nekom karakteristikom sistemske komponente ili aplikacije koja se nadgleda. Neki objekti kojima se definišu performanse povezani su sa specifičnim dijelovima hardvera, kao što su memorija, procesor ili fizički disk (Memory, Processor, PhysicalDisk). Drugi objekti kojima se definišu performanse povezani su sa komponentama operativnog sistema, kao što je proces ili logički disk (Process, LogicalDisk). Objekti kojima se definišu performanse mogu da predstavljaju i softverske komponente koje su instalirane na serveru. SQL Server obezbeđuje brojne objekte kojima se opisuju performanse, kao što je SQL Server: Databases, za analiziranje različitih performansi sistema.
Neki od objekata kojima se definišu performanse sadrže više instanci. Objekat kojim se definišu performanse karakteriše se odgovarajućim instancama.

System Monitor alat se koristi za prikazivanje podataka vezanih za brojač performansi. Podaci se mogu analizirati u realnom vremenu, ili se mogu analizirati prethodno kreirani logovi sa performansama. Na slici je prikazan System Monitor, koji grafički predstavlja nekoliko brojača performansi.

System Monitor može da koristi tri režima za prikazivanje podataka: Chart, Histogram ili Report. Chart režim prikazivanja koristi se prilikom prikazivanja Performance Monitor vrijednosti brojača u toku vremena. Histogram režim prikazivanja koristi se prilikom prikazivanja vrijednosti Performance Monitor brojača korištenjem obojenih vertikalnih stubića. Report režim prikazivanja koristi se prilikom prikazivanja trenutnih vrijednosti Performance Monitor brojača u formatu tekstualnog izvještaja.
Na osnovu inicijalnih podešavanja, System Monitor prikazuje podatke vezane za performanse sistema u realnom vremenu. Ovo je veoma koristan način za pregledanje performansi u kratkom vremenskom intervalu, kada se žele pregledati podaci o performansama u realnom vremenu.


KORIŠTENJE SQL PROFILER & TRACE OKRUŽENJA
SQL Server sadrži veoma moćno okruženje za praćenje izvršavanja događaja. Trag definiše detaljne informacije o različitim događajima koji se izvršavaju na SQL Servera. Postoje brojni razlozi zbog kojih je neophodno praćenje izvršavanja događaja, kao što su:
Pregledanje Transact-SQL naredbi koje izvršava aplikacija, uključujući i plan izvršavanja upita
Pregledanje upita koji se dugo izvršavaju
Detektovanje zastoja u sistemu, uključujući i događaje koji dovode do blokiranja sistema
Praćenje izvršavanja snimljenih procedura do nivoa pojedinačnih naredbi
Praćenje sigurnosti sistema
Analiziranje aktivnosti korisničkih konekcija
Korelacija podataka vezanih za performanse sa aktivnostima koje se javljaju na SQL Serveru u odgovarajućem trenutku
Trag može da se definiše pomoću nekoliko sistemskih snimljenih procedura, ili korištenjem grafičkog SQL Server Profiler alata. Najveći broj administratora koristi SQL Server Profiler alat, zato što on ima dodatne mogućnosti i veoma je jednostavan za korištenje. Korištenje snimljenih procedura prilikom kreiranja traga može da bude veoma korisno prilikom automatizovanja tragova, ili ukoliko se želi definisati trag kojim upravlja proizvoljna aplikacija.


PROJEKTOVANJE DOBROG TRAGA
Profiler trag se koristi prilikom detektovanja informacija o događajima koji se izvršavaju na serveru. Događaji mogu biti prijavljivanja na server ili prekid korištenja servera, ali i startovanje i izvršavanje niza SQL naredbi, ili izvršavanje snimljenih procedura. Treba voditi računa o broju i tipovima događaja koje se prate, zato što je neophodno da to ne utiče na funkcionisanje cjelokupnog sistema. Događaji se mogu pregledati u realnom vremenu, a mogu se i snimati podaci u odgovarajuće datoteke ili tabele, kako bi se podaci mogli kasnije detaljnije da analizirate.
Svaki događaj sadrži kolone sa podacima, koje obezbeđuju dodatne informacije o događaju. Kolone sa podacima zavise od događaja koji se nadgleda. Neke od standardnih kolona sa podacima su ApplicationName, NTUserName, LoginName, CPU, Reads, Writes i Duration. Treba voditi računa o tome da se biraju samo kolone sa podacima koje su relevantne za analizu. Praćenje svih mogućih informacija može samo da napravi problem.
Filtriranje traga je veoma važno, tako da samo relevantni događaji i kolone sa podacima treba da se detektuju. Filtriranje je jedan od najvažnijih aspekata projektovanja dobrog traga, zbog redukovanja nepotrebnih obrada i redukovanja količine podataka koje je neophodno analizirati. Na primjer, ukoliko se prate upiti koji se dugo izvršavaju, treba filtrirati trag tako da se prate samo događaji koji traju duže od 30 sekundi.
Da bi se definisao trag, neophodno je izvršiti sljedeće korake:
Dati naziv tragu i izabrati gdje se smješta rezultat praćenja.
Izabrati događaje koje želite pratiti.
Izabrati kolone sa podacima koje sadrže informacije o posmatranim događajima koji su predmet interesovanja.
Filtrirati trag, tako da se dobiju samo relevantne informacije.
Startovati praćenje i analizirati dobijene rezultate.


KREIRANJE NOVOG TRAGA
NAPOMENA: SQL Profiler ne dolazi sa SQL Server Express izdanjem.
Da bi se kreirao novi trag korištenjem SQL Server Profiler alata, neophodno je izvršiti sljedeće korake:
Otvoriti SQL Server Profiler alat, selektovanjem Start > All Programs > Microsoft SQL Server 2012 > Performance Tools > SQL Server Profiler stavke menija. (Na slikama je ovo odrađeno za SQL SERVER 2005)
Kreirati novi trag selektovanjem File > New Trace stavke menija. Novi trag se može definisati i korištenjem New Trace tastera u paleti sa alatkama. Prikazuje se Connect to Server dijalog.

U Server Name padajućoj listi, unijeti naziv servera kome se želi pristupati. Izabrati odgovarajući metod za provjeru autentičnosti, a zatim unijeti korisničko ime i šifru ukoliko se to zahtjeva. Pritisnuti Connect taster.
Otvara se Trace Properties dijalog, koji je prikazan na slici ispod. Ispuniti podatke na osnovu kojih se definišu svojstva traga, uključujući i naziv traga.

Izabrati šablon traga.
(TSQL - Šablon za pregledanje Transact-SQL naredbe koja je poslata iz klijentske aplikacije. Ovaj šablon je identičan sa Standard šablonom, ali se prati mnogo manje kolona sa podacima. ; SP_Counts - Šablon za praćenje izvršavanja snimljene procedure. Prati se SP:Starting događaj, a rezultati se grupišu na osnovu EventCIass, ServerName, DatabaseID i ObjectID podataka. ITD. – postoji više šablona kao što se vidi na slici niže.)

Ukoliko se žele snimati rezultati praćenja u datoteku, izabrati Save to File polje za potvrdu, a zatim izabrati datoteku u koju se žele snimiti podaci.
Izabrati Events Selection kraticu. Otvara se dijalog koji je prikazan na slici:

Pritisnuti Column Filters taster. Otvara se dijalog koji je prikazan na slici niže. Da bi se pristupilo filterima za određenu kolonu sa podacima, pritisnuti zaglavlje kolone.

Pritisnuti Organize Columns taster, kako bi se otvorio dijalog koji je prikazan na slici slici niže. Korištenjem Up i Down tastera mogu se preuređivati ili grupisati kolone.



ANALIZA REZULTATA DOBIJENIH PRAĆENJEM
Nakon što se pokrene praćenje, neophodno je da analizirati rezultate vezane za posmatrane događaje. Neke analize mogu da se realizuju pregledanjem događaja koji su se pojavili, kao što je prikazano na slici niže, ali je moguća i mnogo detaljnija analiza dobijenih rezultata.
U većini situacija, se želi snimiti rezultat praćenja u odgovarajuću datoteku ili tabelu. Obje ove opcije su raspoložive prilikom praćenja događaja, ali rezultat praćenja se može snimiti i korištenjem File > Save As stavke menija.


SLIČNOSTI I RAZLIKE IZMEĐU ORACLE I MS SQL SERVER
U nastavku su navedene razlike između ORACLE i MS SQL Servera.


INSTANCES, DATABASES & TABLESPACES
Možda najvažnija razlika izmedju Oracle-a i SQL Servera na nivou arhitekture je u shvatanju pojmova Instance i Database.
Instanca u SQL Serveru je pojam koji označava samostalni aplikativni servis koji u sebe uključuje fajlove operativnog sistema, memorijske strukture, pozadinske procese i informacije iz registry-a. Instanca je u SQL Serveru predstavljena servisom u Windows-u i može se nalaziti u 2 statusa: Running ili Stopped. Kada je u statusu running, instanca zauzima dio serverske memorije i povezuje nekoliko pozadinskih procesa.
U središtu zbivanja instance SQL Servera su njene databases – baze podataka. SQL Server database je repozitorij podataka i programskog koda koji upravlja tim podacima. Ukoliko instanca ne radi, bazama podataka unutar instance se ne može pristupiti.
Postoje dva tipa SQL Server baza podataka: system databases i user databases. Kada se SQL Server instanca prvi put instalira, kreira se 5 sistemskih baza podataka: master, model, msdb, tempdb i resource. Ukoliko postoji više od jedne SQL Server instance koja je na računaru u statusu running, svaka instanca će imati svoj predefinisani skup sistemskih baza podataka. Instanca se ne može startovati ukoliko fali ili je oštećena bilo koja od sistemskih baza podataka izuzev baze msdb. Korisničke baze podataka, sa druge strane su kreirane od strane DBA i developer-a kada je instanca već instalirana i sistemske baze podataka startovane. Ovo korisničke baze podataka su baze u kojima se čuvaju poslovni podaci u skladu sa idejama korisnika o njihovoj upotrebi i organizaciji podataka.
Dakle, ukratko, SQL Server instance će uvjek uključivati bazu podataka (čak i ako je to samo jedna sistemska baza podataka) a baza podataka će biti uvjek povezana sa jednom (i samo jednom) instancom.
Na fizičkom nivou, SQL Server baza podataka je predstavljena skupom fajlova unutar operativnog sistema koji postoje na diskovima serverskog računara. Postoji 2 tipa fajlova za SQL Server bazu podataka: data file i transaction log file. U najosnovnijoj konfiguraciji baza podataka mora imati po jedan data file i transaction log file. Data file je glavni repozitorijum sa informacijama u SQL Server bazi podataka. Transaction log file, sa druge strane snima izmjene koje su se desile nad podacima. Ovaj fajl je potreban SQL Serveru u slučaju izvršavanja sistemskog recovery-a. I data i log file će uvjek pripadati nekoj konkretnoj bazi podataka - ne postoji mogućnost da dvije baze podataka dijele isti data ili log file. Ukoliko je baza podataka velika, može da sadrži nekoliko data fajlova. Više data fajlova u jednoj bazi podataka se mogu logički grupisati u strukture poznate pod imenom filegroups.
Kod Oracle-a je stvar donekle obrnuta. Kada se Oracle pokrene,on radi kao i SQL Server u smislu da je dio memorije server računara alociran za njegove operacije. Ovaj memorijski prostor je poznat kao System Global Area (SGA) i podjeljen je na nekoliko tačno definisanih struktura. Pored memorijskog prostora, prilikom pokretanja Oracle-a, takodje se pokreće i odredjeni broj pozadinskih (background) procesa. Njihov osnovni zadatak je komunikacija sa SGA. Ovo dvoje zajedno – memorijski prostor i pozadinski procesi formiraju Oracle instance. Treba uočiti da u ovom momentu Oracle baza podataka još nije u igri. U stvari, Oracle instanca može da radi savršeno čak i bez OnLine baze podataka ili baze podataka kojoj bi se moglo na neki način pristupiti. Prilikom instalacije Oracle, postoji opcija da se instalira samo softver a da se baza podataka kreira kasnije.
Baza podataka u Oracle je kolekcija fajlova operativnog sistema. Za razliku od SQL Servera, Oracle baza podataka ne predstavlja logičko grupisanje objekata. Umjesto toga, postoji samo jedna baza, zajednički pojam za skup fajlova na disku koji primarno sadrže podatke.
Fajlovi koji formiraju Oracle bazu podataka se mogu klasifikovati u 3 tipa: data file, redo log file i control file. Data files su fajlovi gdje su smješteni podaci. Može postojati bilo koliki broj data fajlova u Oracle bazi podataka. Redo log files su nešta poput SQL Server transaction logova u smislu da se u njih upisuju sve promjene na bazi podataka i koriste se za system recovery. Control files su posebna vrsta fajlova male veličine koji sadrže bitne informacije o konfiguraciji Orace baze podataka. Bez Control file, instanca neće moći da otvori bazu podataka.
Pored data, redo log i control files, baza podataka će takođe sadržati parameter file (konfiguracioni parametri za SGA), password file (accounti i paswordi za administratore baze) i opcionalno, archive log files (arhivirani podaci iz redo log fajlova).
Kada se startuje Oracle sistem, prvo se kreira instanca u memoriji. Instanca se povezuje sa bazom podataka koja postoji na diskovima a na kraju se baza podataka otvara za korištenje od strane krajnjih korisnika. Kada je sistem “shut down”, instanca je izbrisana iz memorije: sve memorijske strukture i procesi su ispražnjeni ali baza podataka i dalje postoji na diskovima, mada u statusu “closed”. Kao što je rečeno ranije, moguće je imati Oracle instancu koja je podignuta a da baza podataka nije “open” – ovo je ključna razlika u odnosu na SQL Server gdje se instanca ne može startovati ukoliko nisu prvo startovane njene sistemske baze podataka. Sa druge strane, baš kao i kod SQL Servera, nemoguće je povevezati se na Oracle bazu podataka ukoliko instanca baze nije podignuta - startovana.
Generalno govoreći, relacija izmedju Oracle instance i njegovih baza podataka je 1:1. Instanca ima uz sebe vezanu samo jednu bazu podataka. Baza podataka, sa druge strane, može imati jednu ili više instanci sa kojima je vezana. Standalone Oracle instalacija je primjer jedne instance koja je povezana sa jednom bazom podataka. Oracle instalacija konfigurisana kao RAC (Real Application Cluster) će imati više instanci koje će se izvršavati na više računara a sve one će biti povezane sa jednom bazom na djeljenom (shared) disk sistemu.
Pitanje je, gdje se vrši logičko grupisanje baznih objekata unutar Oracle-a? U SQL Server-u, ovo logičko grupisanje je realizovano unutar same baze podataka. Kod Oracle-a je to realizovano kroz mehanizam koji se zove tablespaces. Oracle tablespace-ovi su logičke strukture koje u sebi grupišu tabele, view-ove, indekse i druga bazne objekte. Npr. produkciona Oracle baza podataka može imati jedan tablespace rezervisan za HR aplikaciju a drugi tablespace za aplikaciju osnovna sredstva. Svaki tablespace je fizički predstavljen sa jednim ili više data fajlova na disku koji formiraju dio baze podataka. Baza podataka je logički napravljena od odredjenog broja tablespace-ova a tablespace-ovi su fizički sačinjeni od odredjenog broja (jednog ili više) data fajlova. Dakle, Oracle-ov ekvivalenat (uslovno govoreći – samo na nivou apstrakcije modela) za SQL Server database je tablespace.
Pošto su ovo slični mehanizmi u svojoj funkcionalnosti, proces kreiranja baze podataka u SQL Serveru je sličan procesu kreiranja tablepace-a u Oracle-u. Bilo da se kreira baza podataka (SQL Server) bilo da kreirate tablespace (Oracle), DBA mora prvo da specificira ime tog objekta. DBA u nastavku vrši dodjelu jednog ili više data file-ova bazi podataka odnosno tablespace-u. Na kraju, definiše se inicijalna veličina fajlova i mehanizmi za promjenu veličine tih fajlova kada se napune podacima.
Slično kao što SQL Server korisničke baze podataka mogu biti offline i read-only, tako mogu biti i Oracle korisnički tablespace-ovi. I upravo kao što jedan ili više data fajlova u SQL Server korisničkim bazama podataka mogu biti read-only, jedan ili više data fajlova kod Oracle korisničkih tablespace-ova mogu biti postavljeni kao offline.
Ipak, baza podataka i tablespaces se razlikuju jedno od drugog u nekim stvarima:
Kod SQL Servera, data files mogu logički biti grupisani u filegroups. Oracle tablespaces nemaju taj koncept.
Kod SQL Server baza podataka, svaka baza podataka ima svoj vlastiti transakcijski log a svojstva log fajla moraju biti specificirana tokom specificiranja – kreiranja baza podataka. Za Oracle, transakcije za cijelu bazu podataka (a to značii za sve tablespace-ove) se snimaju u jedan redo log. Posljedica ovoga je da ne postoji mogućnost da se kreira individualni log za tablespaces.
Za SQL Server, baza podataka može biti kreirana sa opcijom simple recovery mode. Simple recovery mode znači da baza podataka nema aktivan mehanizam za praćenje izmjena podataka (sve log informacije se brišu iz log fajla odmah poslije izvršenja checkpointa). Oracle ima kod sebe sličan mehanizam – ali nije moguće da se ovaj mehanizam implementira na pojedinačnom tablespace-u već na cijeloj Oracle bazi.


SIGURNOST BAZE PODATAKA
Jedna od očiglednih razlika između SQL Servera i Oracle-a na nivou sigurnosti je da je kod SQL Servera pristup bazi podataka dvofazni proces. Na nivou instance, server održava listu korisničkih naloga koji se nazivaju logins. Logins su nalozi kojima su data prava da se povežu na instancu baze podataka. Ipak, da bi pristupio konkretnoj bazi podataka koja je vlasništvo konkretne instance, login nalog treba da se mapira na korisnički nalog unutar baze podataka. SQL Server login može biti ili lokalni Windows login ili nalog iz aktivnog direktorijuma ili sertifikovani ključ. Nalog može biti i nešto što je potpuno nezavisno od windows-a: DBA administrator može kreirati nalog koji posjeduje svoju vlastitu autentifikaciju preko username/password-a unutar SQL Servera. U varijanti kada se koristi windows-ov nalog, odnosno nalog koji se vezuje za AD Windowsa govori se o “trusted” tipu nalogu. Ukoliko se koristi nalog kreiran unutar SQL Servera, govori se o “non-trusted” tipu nalogu.
Za Oracle, ne postoji koncept trusted i non trusted naloga. Nalog običnog korisnika se kreira samo jednom i na jednom mjestu – u bazi podataka. Za razliku od SQL Servera, Oracle ne vrši mapiranje naloga iz operativnog sistema u naloge baze podataka.
Kada se instalira SQL Server, kreira se poseban non-trusted nalog pod imenom sa (akronim za System administrator). sa je ugrađeni DBA nalog koji je vlasnik svake baze podataka koja se kreira na instanci. On ima potpunu kontrolu nad serverom: sa može da kreira, mjenja i briše logins-e i baze, mijenja sistemsku konfiguraciju, spušta instancu i dodjeljuje drugim korisnicima DBA privilegije.
Osim sa naloga, svaka baza podataka će takođe imati i dbo ili database owner nalog. Ovaj nalog će biti mapiran na nivou servera kao glavni korisnik – owner koji je kreirao bazu podataka. Database owner ima sve privilegije unutar svoje baze podataka ali nema nikakvih prava izvan konkretne baze podataka.
Za Oracle, kada se kreira Oracle baza podataka, može biti kreirano desetak naloga sa system nivoom privilegija. Po defaultu, svi ovi nalozi će imati statuse expired i locked osim 4 (dakle, samo 4 će biti aktivna). Četiri specijalna korisnička naloga su sys, system, sysman i dbsnmp. sys nalog je ekvivalentan SQL Server-verovom nalogu sa. Korisnik sys je vlasnik data dictionary-a (Oracle-ov data dictionary je kreiran u sys šemi). Korisnik system ima pristup svim objektima u bazi podataka.
Pored naloga, SQL Server takođe ima unaprijed predefinisano nekoliko fiksnih serverskih rola. Ove fiksne role su poput Windows groupa - mogu da sadrže jedan ili više login-a. Svaka rola ima unaprijed definisani set privilegija. Svi članovi role (korisnici, logins koje kasnije dodjeljujemo ovim rolama) na taj način imaju isti nivo prava nad serverom.Serverska rola sa najvećim nivoom privilegija je sysadmin koja ima isti nivo privilegija kao i dba nalog. Kod SQL Server-a rola ne može da sadrži druge role – ne mogu se prenositi prava iz jedne role u drugu. Na nivou svake od baza podataka unutar instance takodje postoje role unutar kojih se mogu dodjeljivati privilegije koje važe za konkretnu bazu podataka. Ove role se kasnije dodjeljuju korisnicima – user-ima koji se kreiraju na nivou baze unutar SQL Server instance
Kod Oracle-a takođe se sreće pojam role. Za Oracle, najviši nivo privilegije se dodjeljuje dba roli. Oracle ima poseban tip privilegija sysdba i sysoper. Kod Oraclea korisnik kojem budu dodjeljene sysdba ili sysoper privilegije može izvršavati administrativne operacije (kao što su spuštanje i podizanje baze podataka npr). Oracle dozvoljava da se rolama dodjeljuju (prenose) prava iz drugih rola – rola može da sadrži rolu. Na ovaj način se može formirati čitava hijerarhija rola.
Kao što je rečeno ranije, SQL Server login može biti ili trusted (Windows nalog) ili non-trusted. To znači da SQL Server korisnik može biti provjeravan preko 2 tipa autentifikacije: trusted ili non-trusted. Kada se koristi trusted ili Windows autentifikacija, SQL Server će se ponašati kao da je korisnik koji se prijavljuje na bazu već provjeren od strane Windows operativnog sistema. Ukoliko korisnik ima validan nalog u AD ili na lokalnom Windows Serveru, SQL Server neće od tog korisnika tražiti da se autentifikuje ponovo kroz username/password prilikom povezivanja na SQL Server bazu podataka. Kod non-trusted metoda authentifikacije, SQL Server će zahtjevati od korisnika da unese username i password. SQL Server će tada uporediti unešene podatke za username/password sa svojim vlastitim podacima u svojoj internoj listi korisnika.
Kod Oracle-a, korisnik može biti autentifikovan na 3 različita načina:
Data Dictionary
Password File
Operativni sistem (ovo je različito od SQL Server trusted connection methoda autentifikacije)
Među pobrojanim načinima, data dictionary metod je najčešći metod za autentifikaciju korisnika na Oracle bazu podataka. Ostala 2 metoda se koriste kada baza podataka nije raspoloživa ili kada se ne izvršava instanca baze podataka. Kada se obični korisnik pokušava povezati na Oracle bazu podataka koja je već otvorena, njegovi autentifikacioni podaci se provjerava u odnosu na informacije koje se čuvaju u data dictionary Oracle baze podataka. Mada ovaj metod radi dobro za obične korisnike, DBA i sistem administratori koji imaju potrebu da kreiraju, otvore, startuju ili spuste bazu ne mogu biti verifikovani kroz ovakav metod autentifikacije. Ovo je zato što Oracle instanca može da radi a da pri tome baza podataka nije podignuta ili čak nije ni kreirana. DBA će biti jedini korisnik koji može kreirati ili otvoriti bazu podataka iz instance. Pošto u tom trenutku nije aktivan data dictionary za autentifikaciju, Oracle ima potrebu za nekim oblikom eksterne autentifikacije korisnika. Ovaj oblik eksterne autentifikacije može biti realizovan kroz password file ili kroz operativni sistem na kojem hostuje Oracle instanca.
Password file sadržii parove username/password za naloge kojima su dodjeljene sysdba ili sysoper privilegije. Kada se Oracle korisnik konektuje na bazu podataka sa nekom od ove dvije privilegije, on može startovati, monitrati, otvoriti, zatvoriti, demontirati, spustiti ili promjeniti strukturu baze podataka.
Ukoliko je korisnik direktno prijavljen na server na kojem hostuje Oracle instanca, tada takav korisnik može koristiti i autentifikaciju operativnog sistema. Sa validacijom na nivou operativnog sistema, Oracle provjerava da li korisnik koji pokušava da se prijavi član grupe unutar operativnog sistema koja je vlasnik Oracle softvera. Na windows serveru, to je lokalna windows grupa ora_dba, za Unix sisteme, to je tipično dba grupa. Ukoliko je taj korisnik član privilegovane grupe, nije potrebno da se prijavljuje sa posebnim username i password.


ERROR LOG VS ALERT LOG
SQL Server u svom fajl sistemu održava jedan fajl - tekući log za snimanje informacija o uspješnosti izvršenja određenog skupa operacija nad bazom podataka. Ovaj log uključuje informacije u vezi sa događajima koji su nastali prilikom pokretanja baze podataka, prilikom izvršenja recovery-a, zatim događaje koji su u vezi sa korisničkim aktivnostima, izvršenjem backup-a, aktivnostima koje uzrokuju promjenu konfiguracije baze podataka, događaje koji se odnose na nekorektno prijavljivanje na bazu podataka, razne vrste grešaka i upozorenja itd. Svaki put kada se startuje SQL Server kreira se i novi log file.Ovaj log file je poznat kao SQL Server Error Log.
Error log je primarni izvor podataka za DBA prilikom otkrivanja uzroka nastanka raznih incidenata na bazi podataka. “Po defaultu” SQL Server čuva posljednjih 6 error logova a kada se napuni posljednji – šesti log, sljedeći kandidat za upis novih podataka o radu sistema postaje u tom trenutku najstariji error log file. Ovo “po defaultu” ponašanje se može promjeniti. SQL Server se može konfigurisati da čuva unaprijed definisani broj fajlova za error logove. Tekući error log nosi ime ERRORLOG (bez ikakve druge dodatne ekstenzije) dok se error log koji je stariji od tekućeg imenuje kao ERRORLOG.1, onaj koji je još stariji dobija ime ERRORLOG.2 itd.
Oracle-ov ekvivalenat za SQL Server Error Log se zove Alert Log file. Alert Log sadrži informacije o podizanju i spuštanju baze podataka, oporavku instance, izmjenama konfiguracije, internim greškama u bazi podataka, vrijednostima inicijalnih parametara itd.
Za razliku od SQL Server error loga, Oracle alert log ne vrši kreiranje novog file-a svaki put kada se uradi restart instance. U stvari, Oracle održava samo jedan Alert Log file koji može rasti neograničeno a u mjeri u kojoj se informacije pohranjuju u njega.. Alert Log će uvjek imati ime koje se formira u obliku alert_<instance>.log gdje je <instance> Oracle-ovo ime instance. Poput DBA kod SQL Servera, Oracle DBA koriste Alert Log kako bi provjerili postojanje potencijalnih problema u radu baze podataka.
I SQL Server error log i Oracle alert log su obični ASCII tekst fajlovi koji se mogu otvoriti u bilo kojem tekst editoru. SQL Server Management Studio obezbjeđuje pregled error log-ova kroz Windows interfejs. Oracle-ov Enterprise Manager Database Control obezbejeđuje pregled alert logova, takođe kroz web interface.
Lokacija SQL Server error log-a je uslovljena definicijom u registry-u. “Po defaultu” lokacija je ispod LOG direktorijuma SQL Server instalacionog foldera. Kada je riječ o Oracle-u, lokacija alert log-a je odredjena inicijalizacionim parametrom koji ima naziv BACKGROUND_DUMP_DEST a njegova najčešća definicija je bdump folder ispod $ORACLE_HOME direktorijuma.


TRANSACTIONAL CONSISTENCY I POINT-IN-TIME RECOVERY
Microsoft SQL Server i Oracle imaju ugradjen mehanizam za zaštitu korisničkih transakcija. Ideja koja se nalazi iza “transactional consistency” je da se promjene koje se izvrše nad podacima ne upisuju odmah u fajlove na disku operativnog sistema. Umjesto toga, izmjene se upisuju u operativnu memoriju servera u dio koji se naziva buffer cache. Iz buffer cache-a se podaci periodično prenose u fajl na disku u kojem su konkretni podaci za konkretne tabele baze podataka. Pored buffer cache-a, u memoriji servera postoji još jedan buffer. To je log buffer i on je vezan za čuvanje promjena nad podacima koje urade korisnici. Log buffer se koristi za kontinualno i sekvencijalno zapisivanje svih izmjena koje su izvršene nad podacima u bazi podataka. Sadržaj ovog buffera se upisuje u odvojeni – nezavisni fajl baze podataka na disku. Pošto imamo dva nezavisna bafera sa podacima (buffer keš i log buffer keš) postoje i 2 odvojena pozadinska procesa koji upisuju sadržaje ovih bufera u fizičke fajlove na disku. Log buffer keš se mnogo češće upisuje na disk nego buffer keš. To je zato što je operacija koja izvršava prenos log buffera mnogo brža – log file je obični sekvencijalni fajl kod koga se na njegov kraj dodaju novi slogovi sa podacima o promjenama na tabelama u redosljedu u kojem su se promjene dešavale (uočiti da je prepis iz buffer keša u fajl sa stvarnim podacima mnogo sporiji jer se za taj prepis uvjek mora prvo naći tačna pozicija u fajlu, tačna tabela i slog na koji se upisivanje odnosi i zbog toga se ovaj upis mnogo ređe izvršava)
Ukoliko korisnik uspješno komituje transakciju, promjene će postojati u buffer cache-u ali promjene iz buffer cache-a neće biti odmah upisane u data fajlove na diskovima. Promjene će se snimiti i u sekvencijalni log buffer. Sadržaj log buffer-a će biti prenešen na log file na disku prije nego što korisnik dobije signal od RDBMS-a da je uspješno uradjen commit njegove operacije.
SQL Server ovaj log fajl naziva Transaction Log. Kod Oracle-a se ovaj log naziva Redo Log. U SQL Server terminologiji, prostor u memoriji koji sadrži izmjene nad podacima je poznat pod imenom Log Buffer. Oracle za tu namjenu koristi termin Redo Buffer. Bez obzira na razliku u imenovanju, funkcije log fajlova su identične: ukoliko server “neočekivano padne”, database server će pretražiti sadržaj log fajla poslije restarta baze podataka. Ukoliko server u log fajlu pronadje komitovane transakcije koje nisu prenešene u fajl sa podacima, on će izvršiti izmjene na podacima u data fajlu na disku tako da će se izmjene jednom učinjene od strane korisnika prije pada sistema reflektovati u bazi podataka poslije njenog restarta. Ukoliko server prilikom restarta u log fajlu pronadje transakcije koje su nekompletne ili transakcije nad kojima je uradjen roll back, server će uraditi roll back i na podacima u data fajlovima. Prva faza obrade se naziva redo faza a druga je poznata pod nazivom undo.
SQL Server održava po jedan transaction log za svaku od svojih baza na hostu. Database transaction log može imati jedan ili više transakcionih log fajlova koji su mu dodjeljeni. Transactioni log fajlovi se kreiraju u vrijeme kreiranja baze podataka a dodatni fajlovi se mogu kreirati kasnije. Kod Oracle-a, koncept baze podataka obuhvata sve fizičke data fajlove i logičke tablespace-ove kojima on upravlja. Oracle-ovi redo logovi se kreiraju kao set fajlova koji prihvataju promjene koje se načine u svim tablespace-ovima. Svaka Oracle baza podataka mora da ima najmanje 2 redo log file-a za svoje operacije. Može postojati više od 2 redo log file-a, ali su 2 minimum.
Jedna očigledna razlika izmedju SQL Server transaction logova i Oracle redo logova je da SQL Server transaction log fajlovi nisu grupisani ni na koji logičan način. Redo log fajlovi kod Oracle-a su dodjeljeni dvjema ili većem broju redo log grupa. Svaka Oracle baza podataka mora imati najmanje 2 redo log grupe a svaka grupa mora imati jedan ili više redo logova. Log fajlovi u svakoj grupi se nazivaju članovima (members) grupe.
Oracle upisuje redo ulaze iz svog log buffer-a u jednom trenutku u jednu redo grupu. Kada se redo ulaz upiše u redo log grupu, svaki member fajl groupe se update-uje u isto vrijeme. Postojanje više od jednog fajla u redo log fajl grupi obezbjeđuje zaštitu od eventualnog oštećenja fajla u grupi i naziva se multiplexing. Kada se log grupa napuni sa redo slogovima, Oracle će startovati upis logova u narednu redo grupu. Ovaj postupak se naziva log switching. Jednom, kada se grupa napuni, upis će se prosljediti na sljedeću grupu i tako redom. Ako je baza u NoArchiveLog modu, kada se napune sve redo grupe (bez obzira da li ih ima 2 ili više), Oracle će isprazniti prvu redo log grupu u lancu i početi ponovo upis u nju. Ukoliko Oracle baza radi u tzv Archive log modu i ukoliko su napunjeni svi ulazi u redo log bufferima, a pri tom iz redo log buffera koji je naredni za upis izmjenjenih podataka na disk podaci nisu preneseni na redo log fajl na disku, korisnička(e) transakcija(e) će biti zaustavljene dok se redolog buffer ne isprazni. Pražnjenje podataka iz Redo log buffer-a se najčešće vrši automatski (mada se može insistirati i na ručnom prenosu). Konfiguracionim parametrima na nivou baze se može promjeniti način izvršenja ove operacije.
SQL Server transakcioni log baze podataka se takodje puni u sekvenciajlnom redu. Log se ne briše automatski osim ako baza podataka ne radi u tzv simple recovery modu ili ukoliko se ne uradi backup transaction loga. Ukoliko logički transaction log za bazu podataka postane pun i ukoliko se log fajl za koji je logički transakcioni log vezan toliko velik da se više ne može povećavati, baza podataka postaje neraspoloživa za bilo kakvu korisničku aktivnost. Ako se izvrši backup transakcijskih logova, SQL Server će dozvoliti da se preko backup-ovanih ulaza u log fajl uradi novi upis novih transakcija.
Sljedeći detalj koji treba da primjetimo jeste da se SQL Serverov transaction log fajl može konfigurisati na način da se povećava automatski kada postoji potreba za većim prostorom u njemu. Oracleov redo log fajl se kreira sa predefinisanom veličinom i ona se ne povećava automatski osim ako se ta veličina kasnije ručno ne promjeni..
Sa stanovišta raspoloživosti podataka, obadvije platforme omogućuju point-in-time recovery. Ukoliko se želi, ova osobina može biti isključena. Kada je SQL Server baza podataka konfigurisana da radi u point-in-time recovery modu, kaže se da je ona u full recovery mode-u. Svaka modifikacija podataka u bazi podataka se evidentira u tranasction log-u nezavisno od toga koji se recovery moda primjenjuje za bazu podataka. Ovi log slogovi ostaju u fajlu dok se ne uradi transaction log backup. Baza podataka može biti i u simple recovery mode u kom slučaju slogovi u transaction logu ostaju dok se ne izvrši checkpoint. Checkpoint snima modifikovane podatke iz buffer keša i log keša u data fajlove i log fajlove na disku. Ukoliko baza podataka radi u tzv. simple recovery modu, svi slogovi u transaction logu prije najstarije otvorene transakcije se brišu poslije izvršenog checkpoint-a. Ovo se radi zato što SQL Server zna da su sve komitovane transakcije prije najstarije otvorene transakcije već bile upisane u fajl sa podacima.
Ukoliko se desi greška (fizička ili logička) u bazi podataka, prvo se treba restaurirati njen posljednji full backup a zatim se trebaju primjeniti svi backup-ovani tranasakcioni logovi koji su uradjeni poslije posljednjeg full backupa baze podataka. Ovo svojstvo omogućava da se vrati stanje baze podataka u bilo koji momenat vremena u prošlosti. Treba imati u vidu da ova funkcionalnost radi samo ako je baza podataka u full recovery mode.
Oracle ima sličan koncept. Oracle baza podataka može biti u ARCHIVELOG ili NOARCHIVELOG modu. Kada radi u NOARCHIVELOG modu, sadržaj prve redo log grupe se ponovo koristi za upis čim se posljednja log grupa u lancu napuni. Pošto se sadržaj redo log grupa ne arhivira u ovom režimu rada, sistem se neće moći vratiti na stanje u nekoj vremenskoj tački u prošlosti koja je izbrisana u redolog-u. Funkcionalno, ovo je ekvivalentno SQL Serveru koji je u simple recovery modu.
Ako Oracle baza podataka radi u ARCHIVELOG modu, jedan ili više archive procesa će raditi u pozadini. Arhiv procesi vrše backup sadržaja redo log grupa kada one postanu pune i pohranjuju ih u odvojene fajlove na disk podsistemu. Ove sačuvane kopije redo log grupa su poznate pod imenom archived log. Jednom kada se log grupa arhivira, može se ponovo koristiti za upis podataka. Ako pokušamo naći paralelu, arhiving kod Oracle redo log grupa je funkcionalno ekvivalentno SQL Server transaction log backup-u a ARCHIVELOG mod je ekvivalentan full recovery modu.
Za Oracle, kao i za SQL Server, archivirani logovi se mogu primjeniti na bazu podataka tek pošto se uradi restore nekog full backup-a. Razlika između ova dva sistema za upravljanje bazama podataka je da kod SQL Servera, transaction log backup task treba biti postavljen manuelno – ručno kao scheduled job, dok kod Oracle-a archiver proces automatski vodi računa o backup-u kada se baza podataka jednom konfiguriše za tu namjenu.
Konačno, ekvivalencija izmedju ovih platformi može biti uočena takođe i u terminologiji recovery time. Kao što je napomenuto ranije, kada se startuje DB engine, cijeli proces će ići kroz redo i undo fazu. Ukupno vrijeme provedeno u uvim dvijema fazama je poznato kao recovery interval. Očigledno je da će DBA želiti da recovery interval bude što je moguće manji. Kod SQL Servera, DBA može konfigurisati ovaj interval izvršavajući komande date u nastavku:
sp_configure ‘show advanced option’, 1
reconfigure
sp_configure ‘recovery interval’, <time-in-minutes>
reconfigure
Ova komanda modifikuje sistemske konfiguracione parametre. Kada se postavi recovery interval, SQL Server će upodobiti frekvenciju svog checkpoint procesa na svakoj od baza podataka tako da vrijeme provedeno tokom recovering-a svake od baza podataka ne premašuje ovaj interval. Recovery interval se specificira u minutama.
Oracle-ov ekvivalenat za recovery interval je Mean Time To Recover (MTTR). Ovaj parametar se može setovati mjenjajući Oracleov inicijalni parametar FAST_START_MTTR_TARGET. Ovaj parametar može biti postavljen za tzv fine-tune checkpoint frequency. Njegova vrijednost odredjuje koliko mnogo sekundi će Oracle provesti u database recovery poslije pada servera. Parameter može biti postavljen koristeći komandu poput sljedeće:
ALTER SYSTEM SET FAST_START_MTTR_TARGET=<number_of_seconds> SCOPE=spfile;






























--Kreirati bazu podataka sa tabelama: Grad(SifraGrada, NazivGrada,Drzava),
--Korisnik(JMBG, Ime, Prezime, Adresa, Grad, DatumRodjenja), Drzava(id,nazivDrzave)

Create database Vjezba11sedmica
Use Vjezba11sedmica

Create table Drzava (
Id char(3) primary key,
NazivDrzave nvarchar(25),
Skracenica char(3)
)
Insert into Drzava values('D11','Bosna i Hercegovina','BiH')
Insert into Drzava values('D22','Hrvatska','CRO')
Insert into Drzava values('D33','Srbija','SRB')
Insert into Drzava values('D44','Crna Gora','MNE')

Select * from Drzava


Create table Grad(
SifraGrada char(3) primary key,
NazivGrada nvarchar(20),
BrStanovnika int,
Drzava char(3) references Drzava(Id),
)
Insert into Grad values('G11','Sarajevo',1500255,'D11')
Insert into Grad values('G22','Brèko',50048,'D11')
Insert into Grad values('G33','Zagreb',209348,'D22')
Insert into Grad values('G44','Dubrovnik',44326,'D22')
Insert into Grad values('G55','Beograd',200935,'D33')
Insert into Grad values('G66','Novi Sad',80993,'D33')
Insert into Grad values('G77','Podgorica',99283,'D44')
Insert into Grad values('G88','Budva',33945,'D44')

Select * from Grad

--1. Pri unosu države unijeti DrzavaId i Naziv, zatim automatski da se popuni skraæenica (prvih 3 karaktera)

Alter trigger tr_Drzava_ForInsert
on Drzava
FOR Insert
as
Begin

declare @ID as char(3)

Select @ID=Id from inserted
Update Drzava set Skracenica=substring(NazivDrzave,1,3) where @ID=Id
Select * from inserted
Select * from Drzava

end
Insert into Drzava(Id,NazivDrzave) values('D66','Belgija')

Select * from Drzava



--2.U tabelu grad dodati novu kolonu Budžet koja se raèuna kao:
---if BrStanovnika <10 000 => BrStanovnika*10 000
---if BrStanovnika >=10 000 => BrStanovnika*12 000

Alter table Grad
Add Budzet int

Alter trigger tr_BrStanovnikaGrad_ForInsert
on Grad
for insert
as
begin

Declare @SifraGrada as char(3)
Declare @BrStanovnika as int
Declare @Budzet as int
Select @SifraGrada=SifraGrada from inserted
Select @BrStanovnika=BrStanovnika from inserted
Select @Budzet=Budzet from inserted
If @BrStanovnika<10000
Update Grad Set Budzet=@BrStanovnika*10 where SifraGrada=@SifraGrada
If @BrStanovnika>=10000
Update Grad Set Budzet=@BrStanovnika*20 where SifraGrada=@SifraGrada


Select * from inserted
Select * from Grad

end

Select * from Grad
Insert into Grad(SifraGrada,BrStanovnika) values('G12',13526)


--3. Pri update-u Grada ako se update-uje Drzava ispisati poruku:
---Promijenili ste drzavu grada iz _______ u ________.


--Create table Zapis(
--@StaraDrzava
--@NovaDrzava
--)



Create trigger tr_GradPromjenaDrzave_ForUpdate
on Grad
For update
as
begin
Declare @StariID as char(3)
Declare @NoviID as char(3)
Declare @StaraDrzava as nvarchar(25), @NovaDrzava as nvarchar(25)

Select @NovaDrzava=NazivDrzave, @NoviId=Id from inserted
inner join Drzava on inserted.Drzava=Drzava.Id
Select @StaraDrzava=NazivDrzave, @StariId=Id from deleted
inner join Drzava on deleted.Drzava=Drzava.Id

if (@NoviID<>@StariID)
print 'Promijenili ste drzavu iz ' + @StaraDrzava + ' u ' + @NovaDrzava

end

--4.Pri unosu novog Grada provjeriti da li postoji drzava u tabeli drzava
---Da -> Uspješno ste unijeli grad
---Ne -> Neuspješan unos. Drzava ne postoji ---Error

--Alter view vWZadatak4
--as
--Select SifraGrada,NazivGrada,BrStanovnika,Drzava.NazivDrzave from Grad
--inner join Drzava on Grad.Drzava=Drzava.Id
--Select * from vWZadatak4


Create trigger tr_Grad_InsteadOfInsert
on grad
Instead of insert
as
begin
Declare @ID as char(3)
Select @ID=Id from inserted inner join Drzava on inserted.Drzava=Drzava.Id
if (@ID is null)
begin
raiserror ('Neuspješan unos. Drzava ne postoji',16,1)
return
end

insert into grad(SifraGrada,NazivGrada,BrStanovnika,Drzava,Budzet)
Select * from inserted

print 'Uspješno ste odradili unos'
End

Select * from grad
insert into grad(SifraGrada,NazivGrada,BrStanovnika,Drzava) values('G83','Tuzla',32455,'D65')


--5. Ogranièiti unos broja stanovnika od 2.000 do 2.000.000

Alter trigger tr_OgranicenBrStanovnika
on Grad
Instead of insert
as
begin
Declare @SifraGrada as char(3)
Declare @BrStanovnika as int
Select @SifraGrada=SifraGrada, @BrStanovnika=BrStanovnika from inserted
if (@BrStanovnika not between 2000 and 2000000)
begin
raiserror('Broj stanovnika nije u predviðenom opsegu.',16,1)
return
end
insert into grad(SifraGrada,NazivGrada,BrStanovnika,Drzava,Budzet)
Select * from inserted
end

Insert into grad(SifraGrada,BrStanovnika) values('G75',3000)
Select * from Grad


















--Kreirati tabelu Klijent (JMBG, Ime, Prezime, Grad, Adresa, Telefon)
Create table Klijent(
JMBG char(13) primary key,
Ime nvarchar(25),
Prezime nvarchar(25),
Grad nvarchar(20),
Adresa nvarchar(30),
Telefon char(15)
)

--Insert 3 zapisa
Insert into Klijent values('1111111111111','Azra','Drapić','Brčko','Adresa1','062/080-165')
Insert into Klijent values('2222222222222','Edin','Eminović','Tuzla','Adresa2','061/230-355')
Insert into Klijent values('3333333333333','Zijad','Lejlić','Sarajevo','Adresa3','065/039-158')

--Kreirati f-ju koja računa Ukupno_vratiti=Iznos+(iznos*kamatnastopa/100). Pozvati f-ju.
Create function fn_Ukupno_Vratiti
(
@Iznos decimal(4,1),
@Kamatna_Stopa decimal(4,1)
)
returns decimal(15,1)
as
begin
return (@Iznos+(@Iznos*@Kamatna_Stopa/100))
end

Select dbo.fn_Ukupno_Vratiti(980.7,4.2)

--Kreirati tabelu:
--Kredit (KreditID,Klijent,Iznos,Br_Rata,Kamatna_Stopa,Ukupno_Vratiti - pozvati prethodno kreiranu f-ju)
Create table Kredit(
KreditID char(3) primary key,
Klijent char(13) references Klijent(JMBG),
Iznos decimal(4,1),
Br_Rata int,
Kamatna_Stopa decimal(4,1),
Ukupno_Vratiti as dbo.fn_Ukupno_Vratiti(Iznos,Kamatna_Stopa)
)

--Unijeti 3 zapisa, ne unositi Ukupno_Vratiti
Insert into Kredit values('K11','1111111111111',850.7,6,4.2)
Insert into Kredit values('K22','2222222222222',995.2,12,4.2)
Insert into Kredit values('K33','3333333333333',580.4,4,4.2)
Insert into Kredit values('K44','1111111111111',678.3,5,4.2)

Select * from Kredit

--Kreirati f-ju koja na osnovu JMBG-a vraća ukupan iznos i prosječnu vrijednost podignutih kredita za tu osobu
--Forma za ispis Ukupan iznos za klijenta _____ je ____, a prosječan iznos: _____.
Alter function fn_IznosIKredit
(
@JMBG as char(13)
)
returns nvarchar(100)
as
begin
declare @Podaci as nvarchar(100)
declare @UkupnoKredit as decimal(8,1)
declare @ProsjecnoKredit as decimal(8,1)
Select @UkupnoKredit=sum(Iznos), @ProsjecnoKredit=avg(Iznos) from Kredit where Klijent=@JMBG

return 'Ukupan iznos za klijenta '+ @JMBG +' je '+ CAST(@UkupnoKredit as nvarchar(25)) + ', a prosječan iznos:' + CAST(@ProsjecnoKredit as nvarchar(25))

end

Select dbo.fn_IznosIKredit('1111111111111')

--Ispisati KreditID,JMBG, Ime + Prezime, Grad, Iznos, UkupnoVratiti, Zarada -> =UkupnoVratiti - Iznos

Alter function fn_Ispis ()
returns table
as
return
Select KreditID, JMBG, Ime+ ' '+Prezime as 'Ime i prezime',Grad,Iznos,Ukupno_Vratiti, Ukupno_Vratiti-Iznos as Zarada
from Kredit inner join Klijent on Kredit.Klijent=Klijent.JMBG

Select * from dbo.fn_Ispis()

--Isto kao prethodni zadatak ali složena f-ja. Zarada se računa putem Update-a.

Create function fn_IspisSlozeni()
returns @IspisSlozeni table
(
KreditID char(3),
JMBG char(13),
ImeiPrezime nvarchar(25),
Grad nvarchar(20),
Iznos decimal(4,1),
Ukupno_Vratiti decimal(15,1),
Zarada decimal(15,1)
)
as
begin
Insert into @IspisSlozeni
Select KreditID, JMBG, Ime+ ' '+Prezime ,Grad,Iznos,Ukupno_Vratiti,0
from Kredit inner join Klijent on Kredit.Klijent=Klijent.JMBG

Update @IspisSlozeni set Zarada=Ukupno_Vratiti-Iznos
return
end

Select * from fn_IspisSlozeni()



















--1. Kreirati f-ju fnLoan koja prihvata LoadId vrijednost i vraća rezultat kroz prethodno definisanu tabelu sa kolonama
--   LoadID,FistName,LastName,Amount, Interest, Total za pomenutu vrijednost LoanID-a. R*Total se racuna kao Amount+Interest

Create procedure fnLoan
(
@LoanID as int
)
returns @t table
(
LoanID int,
FistName nvarchar(50),
LastName nvarchar(50),
Amount decimal(15,2),
Interest decimal(10,2),
Total decimal(20,2)
)
as
begin
Insert into @t
Select LoanID, FirstName,LastName,Amount,Interest,Total,0
from Loan inner join Customer on Loan.CustomerID=Customer.CustomerID
where LoanID=@LoadID
Update @t set Total==Amount+Interest
return
end


--2. Kreirati f-ju LoanMonthlyRate koja prihvata parametre Amount,Interest  PeriodMOnths iz tabele Loan
--   a vraća vrijednost koju je potrebno naknadno preko Updat-a unijeti u kolonu MonthlyRate preko formule
--   MonthlyRate=(Amount+Interest)/PeriodMonths

Create function Loan_MonthlyRate
(
@Amount as decimal(15,2),
@Interest decimal(10,2),
@PeriodMonths int
)
returns decimal(15,2)
as
begin
declare @Broj as decimal(15,2)
select @Broj=(@Amount+@Interest)/@PeriodMonths
return @Broj
end
update Loan set MonthlyRate=dbo.Loan_MonthlyRate(Amount,Interest,PeriodMonths)


--3.  Kreirati pogled vW_Customers koji će davati rezultat kao na slici...
--    Zatim kreirati INSERT i UPDATE okidače kako bi bilo moguće raditi navedene akcije direktno
--    na pogledu, a gdje bi se navedene akcije sprovodile na tabeli Customer.
--    U slučaju da pri unosu i promjeni korisnika grad koji se unosi ne postoji u tabeli City
--    treba da se ispiše greška "Unijeli ste pogrešan grad."

Create view vW_Customers
as
Select FirstName,LastName,City.City from Customer inner join City on Customer.City=City.CityID

Create trigger vW_Customers_Insert
on vW_Customers
instead of insert
as
begin
declare @CityID as char(5)
Select @CityID=CityID from City inner join inserted on City.City=inserted.City  --ovdje spajamo preko naziva a ne preko primarnog ključa
if (@CityID is null)
begin
Raiserror ('Pogresan grad',16,1)
return
end

insert into Customer(FirstName,LastName,City)
Select FirstName,LastName,@CityID from inserted
end


Create trigger vW_Customers_Update
on vW_Customers
instead of Update
as
begin
declare @CityID as char(5)
Select @CityID=CityID from City inner join inserted on City.City=inserted.City

if(Update(FirstName))
begin
Update Customer set FirstName=inserted.FirstName from inserted inner join customer
on inserted.customerID=Customer.CustomerID
end

if(Update(LastName))
begin
Update Customer set LastName=inserted.LastName from inserted inner join customer
on inserted.customerID=Customer.CustomerID
end

if (@CityID is null)
begin
Raiserror ('Pogresan grad',16,1)
return
end
Update Customer set City=@CityID
from inserted inner join customer on inserted.customerID=Customer.CustomerID
end