• Artykuł

          

Tworzenie bazy danych w SQL Server - czyli na co zwracać uwagę podczas tworzenia i konfigurowania bazy danych 

Udostępnij na: Facebook

Autor: Paweł Wilkosz

Opublikowano: 2011-06-08

Wprowadzenie

W poprzednich publikacjach czytelnik zapoznał się ze sposobami tworzenia baz danych, tabel oraz procedur i funkcji w przestrzeni SQL Server. Kolejna część cyklu skupiać się będzie na wewnętrznych mechanizmach organizacji plików danych w zasobach dyskowych (Files and Filegroups), konfigurowania podstawowych opcji oraz zarządzania integralnością dazy danych. Wiedza z tego zakresu jest niezwykle przydatna w następnym milowym kroku do tworzenia wysoko wydajnych i skalowalnych rozwiązań bazodanowych.

Pliki oraz grupy plików

Dane, których przechowywanie zleca się SQL Server, składowane są na dysku twardym komputera w jednym bądź wielu plikach. SQL Server w tym celu wykorzystuje trzy rodzaje plików:

  • pliki podstawowe – punkt początkowy każdej bazy danych, zawierający informacje o lokalizacji pozostałych plików. Każda baza posiada jeden plik podstawowy. Pliki przechowywane są w strukturze o rozszerzeniu .MDF;
  • pliki pomocnicze – dane przechowywane w bazie oraz pozostałe informacje, które nie są zapisywane w pliku podstawowym. Pliki pomocnicze przechowywane są w strukturze o rozszerzeniu .NDF.
  • pliki logów dziennika transakcji – informacje z wykonywanych operacjach w ramach zdefiniowanej transakcji. Dane zawarte w tym pliku wykorzystywane są do odzyskiwania bazy danych po awarii. Pliki logów przechowywane są w strukturze o rozszerzeniu .LDF.

Ponieważ zarządzanie plikami danych oraz logów z poziomu systemu operacyjnego byłoby bardzo kłopotliwe, serwer SQL udostępnia warstwę abstrakcyjną, pośredniczącą pomiędzy systemem operacyjnym a silnikiem SQL – nazywaną Filegroup (grupa plików). Mechanizm ten łączy ze sobą łatwość konfiguracji oraz prostotę w utrzymaniu struktury plikowej. 

Patrząc z perspektywy mechanizmów wewnętrznych SQL Server, Filegroup stanowi mapę obiektów bazodanowych skojarzonych z miejscem ich przechowywania na dysku twardym. Rzeczą oczywistą jest, że dana grupa plików może przechowywać jeden lub więcej elementów. Logiczny schemat Filegroup został przedstawiony na rysunku 1.

Programista baz danych posiada możliwość zdefiniowania, do jakiej grupy plików będzie docelowo należał obiekt bazodanowy. W tym celu należy posłużyć się klauzulą ON języka Transact-SQL. Przykładowo, aby stworzyć bazę danych ‘Test_Database’ z plikiem podstawowym ‘my_database_primary’, zlokalizowanym w C:\Temp\Database\my_database_primary.mdf, plikiem pomocniczym ‘my_database_secondary’ na grupie plików Filegroup1, zlokalizowanej w C:\Temp\Database\Secondary\my_database_secondary.ldf oraz z plikiem logów dziennika transakcji ‘my_database_log.ldf’, znajdującym się w ścieżce C:\Temp\Database\Log\my_database_log.ldf, należy użyć polecenia zgodnie z listingiem 1. Dodatkowo dla każdego pliku można określić rozmiar (SIZE) oraz przyrost (FILEGROWTH).

Rys. 1. Logiczna struktura przechowywania danych w SQL Server.

Listing 1.

CREATE DATABASE [Test_Database] ON PRIMARY
(
NAME = N'my_database_primary',
FILENAME = N'C:\Temp\Database\my_database_primary.mdf',
SIZE = 3072KB,
FILEGROWTH = 1024KB),

FILEGROUP [Filegroup1]
(
NAME = N'my_database_secondary',
FILENAME = N'C:\Temp\Database\Secondary\my_database_secondary.ndf',
SIZE = 3027KB,
FILEGROWTH = 1024KB)

LOG ON
(
NAME = N'my_database_log',
FILENAME = N'C:\Temp\Database\Log\my_database_log.ldf',
SIZE = 1024KB,
FILEGROWTH = 10%)
GO

Po wykonaniu zapytania z Listing1 można zauważyć, iż plik podstawowy został utworzony w katalogu Database (rysunek 2), natomiast pliki pomocnicze i logów kolejno w Sceondary (rysunek 3) i Log (rysunek 4).

Rys. 2. Plik MDF w Temp\Database.

Warto zwrócić uwagę na wielkość utworzonych plików. Są one zgodne z deklaracją, jaka została wprowadzona w zapytaniu.

Podczas tworzenia  bazy danych w lokalizacji innej, niż domyślna, użytkownik może otrzymać błąd następującej treści:

Msg 5133, Level 16, State 1, Line 1

Directory lookup for the file "C:\Temp\Database\my_database_primary.mdf" failed with the operating system error 2(failed to retrieve text for this error. Reason: 15100).

Msg 1802, Level 16, State 1, Line 1

**CREATE DATABASE failed. Some file names listed could not be created.Check related errors.

W skrócie błąd polega na tym, iż SQL Server chciał założyć pliki we wskazanej przez użytkownika lokalizacji, jednak system operacyjny nie pozwolił mu na dokonywanie modyfikacji wokół wskazanej ścieżki. Najprawdopodobniej błąd ten będzie występował częściej, jeżeli zapytanie z listingu 1 nie będzie wykonywane z poziomem uprawnień administratora systemu. Rozwiązaniem powyższej sytuacji jest dokładne sprawdzenie, czy ścieżka, w której będą przechowywane pliki MDF, NDF i LDF, istnieje. Jeżeli nie, należy zbudować odpowiednią strukturę folderów i spróbować ponownie.

Rys. 3. Plik NDF w Temp\Database\Secondary.

Rys. 4. Plik LDF w Temp\Database\Log.

Oczywiście istnieje również możliwość zmiany domyślnej grupy plików dla istniejącej już bazy danych w systemie SQL Server. W tym przypadku wystarczy użyć polecenia MODIFY FILEGROUP w klauzuli ALTER DATABASE, tak jak to zostało przedstawione na listingu 2.

Listing 2.

ALTER DATABASE my_database MODIFY FILEGROUP Filegroup2 DEFAULT

Zapytanie wykona się poprawnie tylko wtedy, gdy grupa plików Filegroup2 będzie istniała w SQL Server.

Grupy plików oraz ich rozmieszczenie na dysku twardym komputera może być monitorowane z poziomu funkcji i obiektów wewnętrznych SQL Server. Najbardziej przydatne opcje wraz z przykładami użycia zostały przedstawione w tabeli 1.

Wejście Wyjście

DB_NAME (id_bazy_danych)

SELECT DB_NAME() --Nazwa aktualnej bazy danych

SELECT DB_NAME(3) -- Nazwa bazy o id 3

 Nazwa bazy danych

FILE_ID (plik_bazy_danych)

USE Test_Database;

GO

SELECT FILE_ID(Test_Database_Data);

GO

 ID pliku bazy danych

FILE_NAME (id_pliku)

USE Test_Database;

GO

SELECT FILE_NAME(1);

GO

Nazwa pliku bazy danych

Argumenty:

1 – plik podstawowy

2 – plik logów

3 – plik pomocniczy

FILEGROUP_ID (grupa_plików)

USE Test_Database;

GO

SELECT FILEGROUP_ID(‘PRIMARY’)

GO

 Identyfikator grupy plików bazy danych

FILEGROUP_NAME (id_pliku)

USE Test_Database;

GO

SELECT FILEGROUP_NAME(1)

GO

 Nazwa grupy plików bazy danych

FILEGROUPPROPERTY (grupa_plików, właściwość)

USE Test_Database;

GO

SELECT FILEGROUPPROPERTY(‘PRIMARY’, ‘IsDefault’)

GO

 Informacje o grupie plików

FILEPROPERTY (plik_bazy_danych, właściwość)

USE Test_Database;

GO

SELECT FILEPROPERTY(‘Test_Database_Data’, ‘IsDefault’)

GO

 Informacje o pliku

Tab. 1. Podstawowe funkcje monitorowania plików bazodanowych.

Dla potrzeb prac administracyjnych wszystkie informacje o plikach bazy danych należących do poszczególnych grup plików można wylistować zapytaniem łączącym ze sobą dwa widoki systemowe – sysfiles oraz sysfilegroups (Listing 3).

USE Test_Database;
GO;

SELECT sysfiles.fileid, sysfiles.size, sysfiles.name, sysfilegroups.groupname
FROM sysfiles 
INNER JOIN sysfilegroups ON
 sysfiles.groupid = sysfilegroups.groupid
ORDER BY sysfilegroups.groupname;
GO

Jako wynik użytkownik powinien otrzymać następujący zbiór informacji dla bazy Test_Database (rysunek 5):

Rys. 5. Informacje n.t. plików bazy Test_Database.

 

Dlaczego rozmieszczenie danych w grupach plików jest takie ważne?

Przed przystąpieniem do projektowania fizycznej struktury bazy danych, oprócz dobrego schematu logicznego zgodnego z zasadami postaci normalnej, należy również uważnie przemyśleć sposób rozmieszczenia plików danych na maszynach będących hostem dla SQL Server. Jeżeli tylko zasoby sprzętowe na to pozwalają, nie należy tworzyć wszystkich obiektów bazodanowych na podstawowej grupie plików z następujących powodów:

  1. Rozmieszczenie plików na różnych, niezależnych od siebie partycjach przyczyni się do zmniejszenia obciążenia poszczególnego dysku poprzez minimalizację operacji odczytu/zapisu, które wbrew pozorom są niezwykle kosztowne.
  2. Izolacja podstawowej grupy plików (PRIMARY - MDF) od grupy pomocniczej (NDF) ogranicza zapis na dysk, na którym znajduje się partycja podstawowa, przez co zwiększa się wydajność dostępu do bazy danych (jak zapewne czytelnik pamięta, pliki MDF zawierają informację o stanie bazy).
  3. Równomierne rozłożenie grupy plików, do których dostęp jest ciągły, minimalizuje ryzyko wąskich gardeł związanych z odczytem i zapisem danych.
  4. Rozgrupowanie informacji podnosi również niezawodność bazy danych i minimalizuje ryzyko utraty całości informacji po awarii.
  5. Prawidłowo zaprojektowana struktura partycjonowania pozwoli na rozdzielenie danych archiwalnych od bieżących.

Z dobrych praktyk projektowania partycji dla baz danych zaleca się m.in., aby:

  1. przechowywać indeksy na innym dysku twardym niż tabele w celu zwiększenia wydajności wykonywania zapytań;
  2. dane typu read-only (tylko do odczytu, np. tabele słownikowe, informacje historyczne) lokalizować w jednej grupie, odizolowanej od danych wymagających częstych operacji I/O związanych z zapisem;
  3. grupy tabel oraz innych obiektów bazodanowych wymagających podobnej polityki dostępu gromadzić na jednej grupie plików. Ułatwi to operacje związane z rutynową kontrolą oraz automatyzacją zadań ze strony administratora.

Dziennik logów transakcji

SQL Server oferuje dwa rodzaje transakcji – Implicit oraz Explicit. Transakcje Implicit wykonywane są zawsze wtedy, gdy co najmniej jedna z następujących komend jest wykonywana: ALTER TABLE, CREATE, DELETE, DROP, FETCH, GRANT, OPEN, REVOKE, SELECT, TRUNCATE, UPDATE. Natomiast Explicit jest wykonywana zawsze wtedy, gdy programista w kodzie T-SQL zadeklaruje chęć wykonania danego zapytania (bloku zapytań) w ramach transakcji. Takie zapytanie, jak już zapewne Czytelnik wie, opatrzone musi być klauzulami: BEGIN TRANSACTION / COMMIT TRANSACTION.

Sposób zapisu do dziennika logów

Proces logowania transakcji do wcześniej wskazanego pliku przebiega w kilku etapach. W pierwszym z nich użytkownik uruchamia zapytanie, np. dokonujące modyfikacji danych (UPDATE). Podczas uruchomienia kodu aplikacja sczytuje dane biorące udział w manipulacji do specjalnego miejsca w pamięci operacyjnej RAM, zwanego buforem. Operacja ta nie przebiega w przypadku, gdy dane znajdują się już w buforze. Następnie SQL Server dokonuje zmian w danych, uważnie notując każdą operację w pliku logów. Zmiany są logowane zanim dokona się fizyczna manipulacja (tzw. write-ahead log). Jeżeli transakcja zakończy się sukcesem, checkpoint zapisze zmiany w plikach MDF oraz NDF. Przepływ logowania informacji do dziennika transakcyjnego został przedstawiony na rysunku 8.

Z technicznego punktu widzenia plik dziennika transakcji składa się z grupy plików logicznych (tzw. Virtual Log Files), na które podzielony jest plik .LDF. Aby wyświetlić listę tych plików, należy wykonać zapytanie:

DBCC LOGINFO()
GO
)

Co w rezultacie zwróci wynik zgodny z rysunkiem 6.

Rys. 6. Struktura VLF.

Natomiast wyświetlenie wszystkich wpisów dokonanych w ramach pliku dziennika transakcji przebiega po wykonaniu zapytania (rezultat na rysunku 7):

SELECT * FROM ::fn_dblog(NULL, NULL);

Rys. 7. Zapis logu transakcyjnego.

Ponieważ prawie każda operacja na bazie danych powoduje odpowiedni wpis do dziennika logów, może niekiedy wydawać się, szczególnie w kontekście pierwszej części niniejszej publikacji, że najrozsądniejszym rozwiązaniem byłoby podzielenie tegoż pliku na kilka mniejszych i ulokowanie ich na różnych maszynach. Otóż zapis do dziennika transakcji odbywa się w sposób sekwencyjny (jedna operacja po drugiej), zatem pod względem wzrostu wydajności odczytu/zapisu taki podział nic nie wniesie. Jeżeli tego typu plik swoim rozmiarem zaczyna przeszkadzać, należy wziąć pod uwagę rozwiązanie zaproponowane w dalszej części artykułu.

Rys. 8. Uproszczony schemat procedury wykonywania transakcji.

  

Backup dziennika transakcji

Na początek warto przyjrzeć się poniższemu zapytaniu:

DBCC SQLPERF(LOGSPACE)
GO

Jako wynik zwróci ono dokładne informacje na temat logów wszystkich baz danych przechowywanych w serwerze SQL, m.in. ich rozmiar, wykorzystane miejsce na dysku, % użycia oraz status (rysunek 9).

Rys. 9. Informacje o wielkości dziennika transakcji dla baz danych.

Jeżeli informacje wynikające z listingu 7 uznamy za wystarczające, aby podjąć działania w celu zmniejszenia pliku, można przystąpić do czyszczenia logu. Zanim jednak wykona się polecenie usunięcia wpisów, należy zrobić backup istniejącego pliku. Jego utrata spowoduje niemożność odtworzenia bazy danych po awarii!

Backup dziennika transakcji można wykonać poleceniem:

BACKUP LOG Test_Database
TO DISK = N’D:\Bak\Test_Database_Log.bak’

Czyszczenie pliku logów następuje po wykonaniu zapytania:

DBCC SHRINKFILE(N’Test_Database_log’);
GO

Podsumowanie

Dobry projekt logiczny struktury bazodanowej do dopiero początek pracy przy tworzeniu eleganckich i wysoko wydajnych architektur bazodanowych. W niniejszej publikacji chciałem przybliżyć Czytelnikowi problem partycjonowania oraz zarządzania dziennikiem transakcyjnym oraz pokazać, dlaczego wiedza na ten temat jest niezwykle istotna. Prawdą jest, iż ustawienia domyślne pomagają przyspieszyć proces pracy z daną technologią, czy jak w tym wypadku – z bazą danych, lecz nie zawsze stanowią najwłaściwsze rozwiązanie z punktu widzenia poprawy jakości współczynników wydajnościowo-skalowalnych.