SQL Server Archistruktura (objašnjeno)

MS SQL Server je klijent-poslužiteljska arhitektura. Proces MS SQL Servera započinje slanjem zahtjeva od strane klijentske aplikacije. SQL Server prihvaća, obrađuje i odgovara na zahtjev s obrađenim podacima. Detaljno ćemo raspraviti cijelu arhitekturu prikazanu u nastavku:

Kao što je prikazano na donjem dijagramu, SQL Server ima tri glavne komponente. Architekstura:

1. Sloj protokola
2. Relacijski motor
3. Motor za pohranu podataka

Sloj protokola – SNI

Sloj protokola SQL Servera, također poznat kao Server Network Interface (SNI), podržava tri vrste klijent-poslužitelj arhitekture. Svaki protokol služi drugačijem mrežnom scenariju. Razumijevanje ovih protokola ključno je prije istraživanja načina na koji se upiti interno obrađuju.

Zajedničko sjećanje

Razmotrimo scenarij razgovora u ranim jutarnjim satima. Tom i njegova mama su na istom logičnom mjestu, njihovoj kući. Tom traži kavu, a mama je poslužuje izravno. Slično tome, SQL Server pruža protokol dijeljene memorije kada klijent i poslužitelj rade na istom računalu. Oba komuniciraju putem dijeljene memorije bez ikakvih mrežnih opterećenja.

Analogija: Tom se preslikava na Klijenta, Mama se preslikava na SQL Server, Dom se preslikava na Stroj, a verbalna komunikacija se preslikava na protokol dijeljene memorije.

Napomene o konfiguraciji: In SQL Management Studio, opcija "Naziv poslužitelja" za lokalnu vezu može biti ".", "localhost", "127.0.0.1" ili "Machine\Instance".

TCP / IP

Sada zamislite da Tom želi kavu iz kafića udaljenog 10 km. Tom je kod kuće, a kafić se nalazi na prometnoj tržnici. Komuniciraju putem mobilne mreže. Slično tome, SQL Server pruža TCP / IP protokol kada se klijent i SQL Server nalaze na odvojenim računalima spojenim preko mreže.

Analogija: Tom se preslikava na Klijenta, kafić se preslikava na SQL Server, dom i tržnica se preslikavaju na udaljene lokacije, a mobilna mreža se preslikava na TCP/IP protokol.

Napomene o konfiguraciji: U SQL Management Studiju, opcija "Naziv poslužitelja" za TCP/IP vezu mora biti "Stroj\Instanca poslužitelja". SQL Server prema zadanim postavkama koristi port 1433 za TCP/IP veze.

Nazvane cijevi

Konačno, Tom želi zeleni čaj od svoje susjede Sierre. Nalaze se na istoj fizičkoj lokaciji, susjedi su i komuniciraju putem intra-mreže. Slično tome, SQL Server pruža protokol Named Pipe kada su klijent i poslužitelj povezani putem lokalne mreže (LAN).

Analogija: Tom se mapira na klijenta, Sierra na SQL Server, susjedi se mapiraju na LAN, a intra-mreža se mapira na protokol Named Pipe.

Napomene o konfiguraciji: Imenovane cijevi su onemogućene prema zadanim postavkama i moraju se omogućiti putem SQL Configuration Managera.

Što je TDS?

Sada kada su tri vrste klijent-poslužitelj arhitekture jasne, evo pogleda na TDS:

• TDS je kratica za Tabular Data Stream.
• Sva tri protokola koriste TDS pakete.
• TDS je enkapsuliran u mrežnim paketima, omogućujući prijenos podataka s klijentskog računala na poslužiteljsko računalo.
• TDS je prvi put razvio Sybase, a sada je u vlasništvu Microsoft.

Sljedeća tablica uspoređuje tri protokola povezivanja SQL Servera:

svojstvo	Zajedničko sjećanje	TCP / IP	Nazvane cijevi
Opseg mreže	Isti stroj	Udaljeno (WAN/Internet)	Samo LAN
Zadani priključak	N / A	1433	445
Izvođenje	Najbrži (bez mrežnih troškova)	Dobro (optimizirano za WAN)	Dobro (optimizirano za LAN)
Omogućeno prema zadanim postavkama	Da	Da	Ne
Najbolji slučaj upotrebe	Lokalni razvoj i testiranje	Udaljeni pristup produkciji	Pouzdana LAN okruženja

S obzirom na to da protokolarni sloj obrađuje mrežnu komunikaciju, sljedeći korak u arhitekturi SQL Servera je obrada samog upita. Tu relacijski mehanizam preuzima kontrolu.

Relacijski motor

Relacijski mehanizam poznat je i kao procesor upita. Sadrži komponente SQL Servera koje određuju što upit treba učiniti i kako se to može najučinkovitije izvršiti. Odgovoran je za izvršavanje korisničkih upita traženjem podataka od mehanizma za pohranu i obradom vraćenih rezultata.

Kao što je prikazano na arhitektonskom dijagramu, postoje tri glavne komponente relacijskog mehanizma:

CMD parser

Podaci primljeni od protokolnog sloja prosljeđuju se relacijskom mehanizmu. CMD parser je prva komponenta koja prima podatke upita. Njegov glavni zadatak je provjeriti upit na sintaktičke i semantičke pogreške, a zatim generirati stablo upita.

Sintaktička provjera: Kao i svaki drugi programski jezik, SQL Server ima unaprijed definirani skup ključnih riječi i gramatičkih pravila. SELECT, INSERT, UPDATE i mnogi drugi pripadaju unaprijed definiranom popisu ključnih riječi. CMD Parser provjerava slijedi li unos ta pravila. Ako korisnikov unos odstupa od očekivane sintakse, parser vraća pogrešku.

Primjer: Zamislite Rusa koji ulazi u japanski restoran i naručuje na ruskom. Konobar razumije samo japanski i ne može obraditi narudžbu. Slično tome, ako korisnik upiše „SELECR“ umjesto „SELECT“, CMD Parser vraća grešku jer ne prepoznaje ključnu riječ.

Semantička provjera: To izvodi Normalizator. Provjerava postoje li nazivi stupaca, nazivi tablica i ostali objekti koji se upituju doista u shemi. Ako postoje, Normalizator ih veže s upitom. Ovaj proces je također poznat kao vezanje. Kada korisnički upiti sadrže VIEW, Normalizator ga zamjenjuje interno pohranjenom definicijom prikaza.

Primjer: Trčanje SELECT * from USER_ID bi uzrokovalo da parser izbaci grešku tijekom semantičke provjere ako tablica USER_ID ne postoji u bazi podataka.

Napravi stablo upita: Ovaj korak generira različita stabla izvršenja koja predstavljaju različite načine na koje se upit može pokrenuti. Sva stabla proizvode isti željeni izlaz.

optimizaciju

Optimizator stvara plan izvršenja za korisnički upit. Ovaj plan određuje kako će se upit izvršiti. Nisu svi upiti optimizirani. Optimizacija se odnosi na DML (Data Modification Language) naredbe poput SELECT, INSERT, DELETE i UPDATE. DDL naredbe poput CREATE i ALTER nisu optimizirane, već se kompajliraju u interni oblik.

Cijena upita izračunava se na temelju čimbenika poput korištenja CPU-a, korištenja memorije i potreba za ulazno/izlaznim operacijama. Uloga optimizatora je pronaći najjeftiniji isplativi plan izvršenja, ne nužno i apsolutno najbolji.

Primjer: Zamislite da želite otvoriti online bankovni račun. Jednoj banci potrebno je najviše 2 dana. Također imate popis od 20 drugih banaka kojima je potrebno manje vremena, ali i manje. Pretraživanje svih 20 banaka možda neće pronaći bržu opciju, a sama pretraga oduzima vrijeme. Bilo bi bolje odabrati prvu banku. Slično tome, SQL Optimizer koristi iscrpne i heurističke algoritme kako bi smanjio vrijeme izvođenja upita.

Optimizator pretražuje u tri faze:

Faza 0: Potraga za trivijalnim planom

Ovo je faza prije optimizacije. Za neke upite postoji samo jedan praktičan plan, poznat kao trivijalni plan. Nema potrebe za daljnjim pretraživanjem jer bi svako dodatno pretraživanje pronašlo isti plan izvršenja uz dodatne troškove.

Faza 1: Traženje planova obrade transakcija

To uključuje pretraživanje jednostavnih i složenih planova. Pretraživanje jednostavnog plana koristi statističku analizu podataka stupaca i indeksa, obično ograničenu na jedan indeks po tablici. Ako se ne pronađe jednostavan plan, provodi se složenije pretraživanje koje uključuje više indeksa po tablici.

Faza 2: Paralelna obrada i optimizacija

Ako prethodne strategije ne daju adekvatan plan, Optimizer traži mogućnosti paralelne obrade na temelju mogućnosti obrade stroja. Ako paralelna obrada nije moguća, započinje završna faza optimizacije koja koristi sve preostale opcije kako bi pronašla najbolji mogući plan izvršenja.

Izvršitelj upita

Izvršitelj upita poziva metodu pristupa (Access Method) u mehanizmu za pohranu (storage engine). On pruža plan izvršenja koji sadrži logiku dohvaćanja podataka potrebnu za izvršenje. Nakon što se podaci prime od mehanizma za pohranu (storage engine), rezultat se objavljuje na sloju protokola (protocol Layer) i šalje krajnjem korisniku.

Nakon što Relacijski mehanizam odredi kako izvršiti upit, Mehanizam za pohranu podataka obrađuje operacije s fizičkim podacima. Ovaj sloj upravlja načinom pohrane, predmemoriranja i dohvaćanja podataka s diska.

Motor za pohranu podataka

Storage Engine odgovoran je za pohranu podataka u sustavu za pohranu poput diska ili SAN-a i njihovo dohvaćanje kada je potrebno. Prije ispitivanja komponenti Storage Enginea, važno je razumjeti kako se podaci fizički pohranjuju.

Datoteke i proširenja podataka

Datoteke s podacima fizički pohranjuju podatke u obliku podatkovnih stranica, pri čemu svaka stranica ima veličinu od 8 KB. Ovo je najmanja jedinica za pohranu u SQL ServerStranice s podacima logički su grupirane u opsege. Niti jednom objektu nije izravno dodijeljena pojedinačna stranica; umjesto toga, održavanje se obavlja putem opsega. Svaka stranica ima zaglavlje stranice (96 bajtova) koje sadrži metapodatke kao što su vrsta stranice, broj stranice, korišteni prostor, slobodni prostor i pokazivači na sljedeću i prethodnu stranicu.

Vrste datoteka

Primarna datoteka: Svaka baza podataka sadrži jednu primarnu datoteku. U njoj se pohranjuju svi važni podaci vezani uz tablice, prikaze, okidače i ostale objekte. Ekstenzija je obično .mdf, ali može imati bilo koju ekstenziju.

Sekundarna datoteka: Baza podataka može, ali i ne mora sadržavati više sekundarnih datoteka. One su opcionalne i sadrže podatke specifične za korisnika. Ekstenzija je obično .ndf, ali može biti bilo koja.

Datoteka zapisnika: Također poznati kao zapisnici unaprijed pisanja. Ekstenzija je .ldf. Datoteke zapisnika koriste se za upravljanje transakcijama, oporavak od neželjenih instanci i vraćanje nepotvrđenih transakcija.

Storage Engine ima tri glavne komponente. Svaka igra specifičnu ulogu u upravljanju pristupom podacima i integritetom.

Način pristupa

Metoda pristupa djeluje kao sučelje između izvršitelja upita i Buffer Zapisnici upravitelja ili transakcija. Ne izvršava sam zadatak, već određuje vrstu upita:

• Ako je upit SELECT naredba (DML), prenosi se na Buffer Voditelj za daljnju obradu.
• Ako je upit Naredba koja nije SELECT (DDL i DML), prosljeđuje se Upravitelju transakcija. To uglavnom uključuje naredbe UPDATE, INSERT i DELETE.

Buffer Voditelj

The Buffer Upravitelj upravlja osnovnim funkcijama za predmemoriju plana, parsiranje podataka i rukovanje prljavim stranicama.

Predmemorija plana

Postojeći plan upita: The Buffer Upravitelj provjerava postoji li plan izvršenja u pohranjenoj predmemoriji plana. Ako postoji, predmemorirani plan upita i njegova pridružena predmemorija podataka koriste se izravno.

Plan za prvu predmemoriju: Ako je plan izvršavanja prvog upita složen, pohranjuje se u predmemoriju plana. To osigurava bržu dostupnost sljedeći put kada SQL Server primi isti upit.

Raščlanjivanje podataka: Buffer Predmemorija i pohrana podataka

The Buffer Upravitelj omogućuje pristup potrebnim podacima. Moguća su dva pristupa ovisno o tome postoje li podaci u predmemoriji:

Buffer Predmemorija – meka parsacija

The Buffer Voditelj traži podatke u Buffer Predmemorija. Ako su podaci prisutni, izvršavatelj upita ih izravno koristi. To poboljšava performanse jer dohvaćanje podataka iz predmemorije zahtijeva manje I/O operacija u usporedbi s dohvaćanjem s diskovnog prostora.

Pohrana podataka – Hard parsiranje

Ako podaci nisu prisutni u Buffer Predmemorija, potrebni podaci se pretražuju u pohrani podataka na disku. Podaci se zatim također pohranjuju u predmemoriju podataka za buduću upotrebu.

Upravitelj transakcija

Upravitelj transakcija poziva se kada metoda pristupa utvrdi da upit nije SELECT naredba. Osigurava konzistentnost i trajnost podataka putem nekoliko podkomponenti:

Log Manager

Upravitelj dnevnika čuva track svih ažuriranja izvršenih u sustavu putem zapisnika pohranjenih u zapisnicima transakcija. Svaki unos zapisnika sadrži redni broj zapisnika zajedno s ID-om transakcije i zapisom o izmjeni podataka. Ovaj mehanizam tracks potvrđenih i poništenih transakcija.

Upravitelj zaključavanja

Tijekom transakcije, povezani podaci u pohrani ulaze u zaključano stanje. Upravitelj zaključavanja obrađuje ovaj proces, osiguravajući konzistentnost i izolaciju podataka. Ta su svojstva poznata i kao ACID (Atomnost, dosljednost, izolacija, trajnost).

Proces izvršenja

Proces izvršenja slijedi ove korake:

1. Upravitelj zapisnika započinje zapisivanje, a Upravitelj zaključavanja zaključava povezane podatke.
2. Kopija podataka se čuva u Buffer cache.
3. Kopija podataka koji se ažuriraju čuva se u zapisniku Bufferi svi događaji ažuriraju podatke u Podacima Buffer.
4. Stranice koje pohranjuju modificirane podatke poznate su kao Prljave stranice.

Kontrolne točke i zapisivanje unaprijed

Proces kontrolne točke izvodi se otprilike jednom u minuti i označava sve prljave stranice za pisanje na disk. Međutim, stranica se prvo prebacuje na podatkovnu stranicu datoteke dnevnika iz Buffer Zapisivanje. Ovaj mehanizam poznat je kao zapisivanje unaprijed. Prljave stranice ostaju u predmemoriji čak i nakon što se zapišu na disk.

lijen Writer

Kada SQL Server primijeti veliko opterećenje i potrebna je međuspremnička memorija za nove transakcije, oslobađa prljave stranice iz predmemorije. Lijeni Writer radi na LRU (Least Recently Used - najmanje korišteno) algoritmu za čišćenje stranica iz međuspremnika na disk.

Kako SQL Server obrađuje upit od početka do kraja

Razumijevanje svakog sloja pojedinačno je vrijedno, ali vidjeti kako funkcioniraju zajedno razjašnjava potpunu sliku. Kada klijentska aplikacija šalje SQL upit, događa se sljedeći slijed:

The Sloj protokola prima zahtjev putem dijeljene memorije, TCP/IP-a ili imenovanih cijevi i omotava ga u TDS paket. Relacijski motor zatim preuzima: CMD parser provjerava sintaksu i semantiku, optimizator generira najjeftiniji plan izvršenja, a izvršavatelj upita započinje s dohvaćanjem podataka.

Izvršitelj upita poziva Motori za pohranu Metoda pristupa koja usmjerava SELECT upite prema Buffer Upravitelj i upiti za izmjene Upravitelju transakcija. Buffer Upravitelj provjerava predmemoriju plana i Buffer Prvo se kešira (meka parsacija). Ako podaci nisu keširani, izvodi se čitanje s diska (tvrda parsacija). Za operacije pisanja, Upravitelj transakcija koordinira Upravitelja zapisnika, Upravitelja zaključavanja i proces kontrolnih točaka kako bi se osigurala usklađenost s ACID-om.

Nakon što Storage Engine vrati tražene podatke, Relational Engine formatira skup rezultata, a Protocol Layer ga vraća klijentskoj aplikaciji putem istog TDS protokola.

Kako odabrati pravi protokol za veze sa SQL Serverom

Odabir ispravnog protokola ovisi o fizičkom odnosu između klijenta i poslužitelja, kao i o zahtjevima performansi.

Koristi dijeljenu memoriju kada se klijentska aplikacija izvodi na istom računalu kao i SQL Server. Ovo je najbrža opcija jer eliminira sve mrežne troškove. Idealna je za lokalni razvoj, testiranje i implementacije na jednom računalu.

Koristi TCP/IP kada su klijent i poslužitelj na različitim računalima povezanima putem WAN-a ili interneta. Ovo je najčešće korišteni protokol u produkcijskim okruženjima. SQL Server prema zadanim postavkama osluškuje port 1433, a ovaj protokol podržava šifrirane veze putem TLS-a.

Koristi imenovane cijevi kada su klijent i poslužitelj na istoj pouzdanoj lokalnoj mreži i performanse na internim mrežama su prioritet. Imenovane cijevi su onemogućene prema zadanim postavkama i moraju se omogućiti putem SQL Server Configuration Managera. Manje su uobičajene u modernim implementacijama, ali ostaju korisne za naslijeđene intranet aplikacije.

Pitanja i odgovori