Opiekując się serwerem MSSQL prędzej lub później przyjdzie nam „założyć” jakąś bazę danych. Operacja ta w najprostszej postaci nie należy do skomplikowanych – wpisujemy polecenie składające się z trzech słów i gotowe.
create database test;
Teraz wystarczy pododawać sobie potrzebne tabele i można już działać. Proste prawda? Zagadnienia dotyczące obsługi baz danych zwykle nie są jakoś specjalnie proste, więc moglibyśmy podejrzliwie zapytać czy jest tu może jakiś haczyk? Od razu odpowiem, że nie. Dowodem na to niech będzie fakt, iż utworzona przez nas w ten sposób baza jest od razu gotowa do „zabawy” o czym można się samemu bardzo łatwo przekonać. Czy zatem proces tworzenia nowej bazy jest faktycznie taki prosty? Tutaj niestety odpowiedź nie jest już taka prosta. Z jednej strony chciałoby się powiedzieć, że tak – w końcu to co zrobiliśmy działa. Z drugiej strony jednak kiedy zaczynamy drążyć temat to okazuje się, że co prawda baza została utworzona i nawet działa ale może nie do końca spełniać nasze oczekiwania (i bardzo chętnie byśmy tam coś poprawili). W tym wpisie zajmiemy się tylko jedną z rzeczy, które przy tworzeniu bazy oraz tabel mogą wydawać się w naturalny sposób interesujące – powiemy sobie mianowicie o tym gdzie baza przechowuje nasze dane. Gdyby jednak kogoś interesowało zagadnienie dotyczące tworzenia bazy danych jako całości to zachęcam do zapoznania się np. z tym wpisem na stronie Microsoftu.
Wykonana przez nas powyżej operacja tworzenia bazy danych utworzyła na dysku dwa pliki (za chwilę powiemy sobie gdzie dokładnie). Jeden zawiera dane i można go rozpoznać po rozszerzeniu MDF. Drugi natomiast to plik logu transakcyjnego, który rozpoznajemy po jego typowym rozszerzeniu LDF. W naszym przypadku wspomniane pliki będą się nazywać odpowiednio test.mdf oraz test_log.ldf (są to nazwy nadane domyślnie przez serwer). Skoro już wiemy jak nasze pliki się nazywają to gdzie możemy je znaleźć? Jeśli musimy sobie zadać takie pytanie to znaczy, że niestety nie wiemy (lub po prostu nie pamiętamy) zbyt wiele o konfiguracji naszego serwera i musimy zerknąć do ustawień (lub do tzw. RunBooka jeśli ktoś takowego posiada 😉 ). Pełną ścieżkę znajdziemy oczywiście w oknie właściwości bazy danych w zakładce Files. Ja proponuję jednak zajrzeć w inne miejsce – do właściwości instancji serwera do zakładki Database Settings. Tam na dole zobaczymy domyślne ścieżki dla poszczególnych typów plików (danych, logów transakcyjnych oraz kopii zapasowych). Jeśli nie ustawiono tego podczas instalacji oraz konfiguracji instancji to katalogi będą wskazywać domyślnie na lokalizacje znajdujące się w miejscu instalacji instancji serwera. Czy jednak jest to na prawdę odpowiednie miejsce dla naszych danych? Jeśli serwer składa się z jednego dysku to oczywiście to miejsce będzie równie dobre jak każde inne (chociaż osobiście uważam, że nawet w takim wypadku warto oddzielić strukturę katalogów samej aplikacji od danych). Inną sytuację będziemy jednak mieli jeśli do dyspozycji mamy więcej dysków niż systemowy. Szczególnie jeśli ktoś doszedł wcześniej do wniosku, że o ile system ruszy „na czymkolwiek” to w przypadku plików baz danych warto zainwestować w jakiś lepszy sprzęt i mamy do dyspozycji całkiem przyzwoity oddzielny fizyczny napęd (lub napędy). Warto również podkreślić, iż jeśli nawet ten drugi dysk nie jest w niczym lepszy od systemowego to i tak warto trzymać na nim dane choćby tylko z korzyści jaką daje odseparowanie operacji I/O.
Wróćmy jednak do naszej bazy. Poza lokalizacją plików zastanówmy się jeszcze czy domyślne nazwy plików nadane przez serwer są dla nas zadowalające czy może wolelibyśmy inne? Jeśli chcielibyśmy mieć wpływ zarówno na lokalizację plików, jak i ich nazwy (np. nie podoba nam się to „_log” w nazwie fizycznego pliku logu transakcyjnego), to musimy niestety nieco skomplikować nasze polecenie tworzenia bazy danych.
create database test on ( name = 'test', filename = 'D:\data\test.mdf' ) log on ( name = 'test_log', filename = 'E:\log\test.ldf' );
W powyższym kodzie widać, iż każdy z plików składa się niejako z dwóch elementów definiowanych przez nazwy name oraz filename. Pierwsza z nich jest nazwą logiczną pliku (baza danych odnosząc się do pliku używa i wymaga właśnie tej nazwy – musi być ona nadana zgodnie z zasadami serwera SQL dla nazw), natomiast drugą jest pełna ścieżka do pliku znajdującego się gdzieś na jakimś dysku (w odróżnieniu do nazw logicznych nazwa fizyczna musi spełniać wyłącznie wymogi dla nazw plików w systemie operacyjnym). Warto przy tej okazji wspomnieć, iż określając ścieżkę dla pliku danych należy upewnić się czy serwer bazy ma tam odpowiednie uprawnienia do zapisu i odczytu danych. Dlaczego warto zwrócić na to uwagę? Ponieważ przeważnie baza danych pracuje w kontekście innego użytkownika niż tego, na którym pracujemy w systemie. Wtedy wygląda to przeważnie tak, że na naszym koncie wszystko działa (mamy dostęp do lokalizacji, możemy utworzyć tam pliki, itp.) a serwer SQL mimo wszystko rzuca błędami i nie wiemy dlaczego.
Teraz pozwolę sobie wtrącić jeszcze szybkie wyjaśnienie tego skąd się wzięło to _log w domyślnej nazwie pliku logu transakcyjnego, która została nadana przez serwer. Chodzi mianowicie o to, iż w bazie nie mogą istnieć dwa pliki o takiej samej nazwie logicznej (nawet jeśli chodzi o pliki innego typu – w sensie plików zawierających dane i logi transakcyjne) więc owo _log serwer dodaje sobie domyślnie sam jeśli nie wskazaliśmy mu żadnej innej (i zapewne lepszej) nazwy.
Zapewne ktoś teraz mógłby się zapytać, że skoro wiemy już w jaki sposób wskazać pliki dla naszej bazy, aby wiedziała gdzie ma zapisywać dane, to czy jest jeszcze coś więcej do powiedzenia w temacie plików? Aby odpowiedzieć na to pytanie musimy powiedzieć sobie więcej o tym w jaki sposób są zorganizowane dane w bazach MSSQL.
Mówiąc o zorganizowaniu danych moglibyśmy zacząć od pojedynczych wartości przechowywanych w poszczególnych polach tabel. Na szczęście nie musimy wnikać w temat aż tak głęboko i skupimy się od razu na samych tabelach. Wiemy już, że utworzona przeze nas powyżej baza składa się z dwóch plików, przy czym dane znajdują się tylko w jednym z nich (jest to spore uproszczenie i skrót myślowy wobec funkcji logu transakcyjnego). Ponieważ w takiej bazie możemy utworzyć wiele tabel to oczywiste staje się również, że w tym jednym pliku może znajdować się więcej niż jedna tabela. Zadajmy sobie jednak dwa dodatkowe pytania – czy w bazie może znajdować się więcej niż jeden plik z danymi a jeśli tak, to czy jedną tabelę można zapisać w wielu plikach jednocześnie?
Odpowiedź na pierwsze pytanie może być dla niektórych zaskakująca – tak, baza danych może przechowywać dane w więcej niż jednym pliku. Dlaczego piszę, że może to być zaskakujące? Ktoś może mi w tej chwili zarzucić, że mało jeszcze w życiu widziałem baz danych ale oświadczam, iż te które do tej pory widziałem (a trochę ich jednak było) w ogormnej większości (poza na prawdę pojedynczymi wyjątkami) nigdy nie wychodziły z danymi poza wspomniany wcześniej jeden plik MDF. Nie zdziwiłbym się teraz gdyby któraś z owych „zaskoczonych” osób zapytała, że niby po co trzymać dane w kilku plikach skoro przy jednym pliku wszystko działa? Otóż powodów jest zapewne wiele – ja pozwolę sobie wymienić tutaj dwa chyba najbardziej oczywiste:
– po pierwsze, dopuszczalny rozmiar pojedynczego pliku jest ograniczony i przy na prawdę bardzo dużych bazach zdarza się, że te limity mogą stać się istotnym ograniczeniem,
– po drugie, wydzielenie plików na kilka oddzielnych fizycznych napędów przyspiesza operacje I/O, co jest bardzo mile widziane przy optymalizowaniu szybkości działania zapytań.
Jeśli drogi czytelniku nadal zastanawiasz się nad maksymalnym rozmiarem pliku na dysku (tak – tu na prawdę chodzi o ogromne ilości danych) to mógłbyś teraz chcieć zadać jeszcze takie pytanie – skoro plik może mieć maksymalny rozmiar to czy oznacza to, że pojedyncza tabela również ma związany z tym limit? Jeśli obawiasz się, czy wystarczy miejsca na którąś z Twoich tabel to spieszę z wyjaśnieniem, iż na szczęście takiego limitu nie ma i ograniczać nas będzie wyłącznie ilość dostępnego miejsca na dyskach. Jaki „cud” sprawia, że jest to możliwe? Odpowiedź na to pytanie jest bardzo prosta – otóż pojedynczą tabelę można zapisać w więcej niż jednym pliku. Dodam również, iż pliki te nie muszą być nawet zapisane na tym samym dysku fizycznym.
Zanim przejdziemy dalej chciałbym wtrącić w tym miejscu kilka słów odnośnie „konwencji nazewniczej” plików danych w bazie. Wcześniej napisałem, że plik z danymi ma rozszerzenie MDF. Czy jednak wszystkie pliki z danymi również wykorzystują takie samo rozszerzenie (przypominam, iż takie rozszerzenie wynika wyłącznie z konwencji a nie przymusu – pliki te mogą mieć tak na prawdę dowolne rozszerzenia; pragnę jednak zachęcić do używania przyjętej konwencji co na pewno ułatwi życie innym administratorom 😉 )? Otóż nie – przyjęło się kolejne pliki oznaczać rozszerzeniem NDF pozostawiając MDF tylko dla pliku „głównego” bazy. Czy jest zatem jakiś powód dla którego plik główny jest wyróżniany innym rozszerzeniem? Otóż jest, ale zanim to wyjaśnię chciałbym Ciebie drogi czytelniku zapoznać z jeszcze jednym zagadnieniem – grupami plików.
O pojęciu grupy plików można wg mnie powiedzieć najprościej, iż jest formą abstrakcji oddzielającej fizyczną warstwę zapisu danych na dysku od sposobu wykorzystania tej warstwy po stronie serwera. Mówiąc inaczej – jeśli chcielibyśmy wskazać miejsce gdzie nasza tabela ma być fizycznie przechowywana to wygodniej jest posługiwać się jedną nazwą wskazującą cały fizyczny obszar na dyskach niż listą wszystkich pojedynczych plików.
Każda baza posiada zawsze grupę plików o nazwie PRIMARY, w której serwer SQL przechowuje wszystkie obiekty systemowe. Grupa ta powiązana jest z plikiem głównym bazy, który odróżniamy od innych rozszerzeniem MDF. W grupie tej tworzone są również domyślnie inne obiekty (np. tabele) o ile nie zdecydujemy wskazać się dla nich innego miejsca. Istnieje dobra praktyka, która mówi aby w tej grupie (oraz w ogóle w pliku głównym) nie trzymać niczego innego poza owymi obiektami systemowymi. W naszym przypadku zastosujemy się do tej konwencji i nasze dane wyprowadzimy do osobych grup plików.
Aby pokazać tworzenie grup plików na konkretnym przykładzie przyjmijmy sobie, iż nasza baza składa się z dwóch rodzajów danych – zwykłych „tabelek” oraz tabel zawierających np. „ciężkie” rekordy gdzie składujemy np. pliki. Załóżmy sobie również, iż nasze dane zmieszczą się w jednym pliku, natomiast w przypadku „plików” wolelibyśmy rozdzielić tabelę na grupę składającą się z dwóch plików. Zobaczmy teraz jak będzie wyglądało tworzenie takiej bazy danych jeśli dane chcielibyśmy umieścić w katalogu D:\Data a pliki logów transakcyjnych np. w E:\Log.
create database Test
on primary
( name = Test_main,
filename = 'D:\Data\test.mdf'
),
filegroup Test_tabelki
( name = Test_tabelki,
filename = 'D:\Data\test_tabelki.ndf'
),
filegroup Test_pliki
( name = Test_pliki1,
filename = 'D:\Data\test_pliki1.ndf'
),
( name = Test_pliki2,
filename = 'D:\Data\test_pliki2.ndf'
)
log on
( name = test_log,
filename = 'E:\Log\test_log.ldf'
)
Nasze tabelki moglibyśmy teraz utworzyć w następujący sposób (proszę nie zwracać specjalnej uwagi na definicje pól – są tylko dla prezentacji samego tworzenia tabeli).
use Test; create table Dane ( idDane int, Wartosc nvarchar(2048) ) on Test_tabelki; create table Pliki ( idPliku int, Zawartosc varbinary(max) ) on Test_pliki;
Jak widać utworzenie tabel z przypisaniem ich do konkretnych grup plików nie jest wcale trudne. Podobnie jak samo tworzenie plików, w których nasza baza będzie zapisywać dane.
Wcześniej wspomniałem, iż jeśli nie wskażemy inaczej to wszystkie nowe obiekty będą tworzone domyślnie w grupie PRIMARY. Czy istnieje sposób aby uczynić inną grupę domyślną dla np. nowych tabel? Otóż jest, możemy np. wskazać naszą grupę Test_tabelki jako grupę domyślną przy pomocy następującego polecenia
alter database Test modify filegroup Test_tabelki default;
Tak jak wspomniałem wcześniej, powyższy wpis dotyczył zaledwie jednego z zagadnień związanych z tworzeniem baz danych – samego miejsca przechowywania naszych danych. W oczywisty sposób nie wyczerpuje to nawet tematu samych plików. Nie wspomnieliśmy sobie np. o rozmiarach plików, o czym pisałem szerzej tutaj. Mam jednak nadzieję, iż udało mi się przynajmniej zaciekawić Ciebie drogi czytelniku samym zagadnieniem dotyczącym miejsca przechowywania danych w bazach SQL.
