Strona korzysta z plików cookies w celu realizacji usług i zgodnie z Polityką Plików Cookies.



26.10.2020

Nowa wersja nVision

Można już pobierać nową wersję nVision
26.10.2020

Monitorowanie infrastruktury

Vertiv Environet Alert
23.10.2020

Telefonia w chmurze

NFON Cloudya
23.10.2020

Nowości w EDR

Bitdefender GravityZone
23.10.2020

Wykrywanie anomalii

Flowmon ADS11
23.10.2020

Mobilny monitor

AOC 16T2
22.10.2020

HP Pavilion

HP zaprezentowało nowe laptopy z linii Pavilion.
22.10.2020

Inteligentny monitoring

WD Purple SC QD101
22.10.2020

Przełącznik 2,5GbE

QNAP QSW-1105-5T

Dostępność i bezpieczeństwo SQL Server 2019

Data publikacji: 20-02-2020 Autor: Marcin Szeliga
Rys. 1. Sześć z 56 testów...

Nowe funkcje serwera SQL Server 2019 zapewniają ciągłą dostępność, bezpieczeństwo oraz wydajność baz danych. W pierwszej części prezentacji nowości skupiliśmy się na mechanizmach inteligentnego wykonywania zapytań. Teraz nadszedł czas, by zaprezentować wybrane funkcje ciągłej dostępności oraz zabezpieczenia najnowszej wersji serwera SQL firmy Microsoft.

 

Serwery relacyjnych baz danych, w tym SQL Server, gwarantują trwałość transakcji. Oznacza to, że efekty każdej zatwierdzonej transakcji muszą zostać zapisane w bazie, a wszystkie skutki przerwanych lub wycofanych transakcji muszą zostać z niej usunięte. Wymóg trwałości transakcji musi zostać pogodzony z koniecznością wydajnego przetwarzania dużych zbiorów danych.


> PRZYSPIESZONE ODZYSKIWANIE BAZ DANYCH


Ponieważ natychmiastowe zapisywanie wszystkich zmian na dyskach byłoby rozwiązaniem wolnym i nieskalowalnym, serwer SQL zapisuje dane potrzebne do ewentualnego wycofania lub ponownego wykonania transakcji w dzienniku transakcyjnym. Zastosowany w serwerze SQL protokół WAL (Write-ahead-logging) gwarantuje, że opis transakcji trafi na dysk, konkretnie do pliku dziennika transakcyjnego, zanim zmodyfikowane przez nią dane zostaną zapisane w pliku danych i zanim serwer SQL potwierdzi wykonanie transakcji.


Jeżeli przetwarzane dane znajdowały się już w pamięci, to rozwiązanie eliminuje wpływ dysków zawierających pliki z danymi na wydajność serwera (z tego powodu serwer SQL stara się buforować wszystkie dane). Ograniczeniem wydajności wykonywania zapytań pozostaje jedynie szybkość, z jaką zapisywany jest na dysku dziennik transakcyjny. A że danych opisujących wykonywane transakcje nie trzeba (z pewnymi wyjątkami) zbyt długo przechowywać, asynchroniczny zapis danych uzupełniony o synchroniczny zapis dziennika transakcyjnego jest wydajnym i skalowalnym rozwiązaniem. Protokół WAL jest jednym z podstawowych mechanizmów serwera SQL, a konsekwencje jego zastosowania widoczne są na każdym kroku:

 

  • Ponieważ dziennik transakcyjny musi umożliwić wycofanie lub ponowne wykonanie transakcji, ich opis zawiera m.in. oryginalną wersję zmodyfikowanych danych. W efekcie opis transakcji, w ramach której zmodyfikowano lub usunięto dużo wierszy, może zająć w dzienniku wiele megabajtów.
  • Opisów wszystkich późniejszych transakcji nie można usunąć z dziennika, zanim transakcja nie zostanie zakończona (o takiej transakcji mówimy, że jest aktywna). Z tego powodu plik dziennika rośnie i nie może zostać zmniejszony (obcięty) przez administratora.
  • Wycofanie transakcji może trwać znacznie dłużej, niż trwało jej wykonanie, bo serwer SQL musi na podstawie dziennika transakcyjnego przywrócić stan bazy sprzed jej rozpoczęcia.
  • Podczas uruchamiania serwera lub odtwarzania bazy z kopii zapasowej konieczne jest przeanalizowanie jej dziennika transakcyjnego i ponowne wykonanie tych transakcji, których efekty nie zostały zapisane, oraz usunięcie efektów niezakończonych transakcji.

 

Proces odtwarzania spójności bazy na podstawie jej dziennika transakcyjnego nazywa się odzyskiwaniem bazy danych, a czas jego trwania ma bezpośredni wpływ na obniżenie dostępności bazy – jeżeli dziennik transakcyjny odtwarzanej bazy zawierał wiele aktywnych transakcji, to jej odzyskanie może zająć wiele godzin, a nawet dni. Ponadto im więcej aktywnych transakcji, tym więcej czasu zajmuje przełączenie węzłów klastra serwera SQL. Wprowadzony w wersji 2019 mechanizm przyspieszonego odtwarzania baz danych rozwiązuje powyższe problemy. Sprawdźmy jego działanie – w ramach jawnie rozpoczętej transakcji usuniemy wszystkie wiersze tabeli Fact.OrderHistory:


BEGIN TRAN;
DELETE FROM [Fact].[OrderHistory];


Usunięcie 3 702 592 wierszy zajęło testowemu serwerowi półtorej minuty. Dlaczego tak dużo? Sprawdźmy wielkość dziennika transakcyjnego:


SELECT used_log_space_in_bytes/1024/1024 AS used_log_space_in_MB
FROM sys.dm_db_log_space_usage;
---
8881


Przed usunięciem wierszy dziennik liczył kilkadziesiąt megabajtów, więc wykonanie jednej instrukcji DELETE spowodowało zapisanie w nim prawie 9 GB danych. W związku z tym że transakcja, w ramach której usunęliśmy wiersze, pozostaje aktywna, próba obcięcia dziennika transakcyjnego (przykładowa baza danych działa w trybie odzyskiwania SIMPLE – wykonanie punktu kontrolnego powoduje zapisanie wszystkich zmian w pliku danych i obcięcie dziennika) nie przyniesie rezultatów:


CHECKPOINT;
GO
SELECT used_log_space_in_bytes/1024/1024 AS used_log_space_in_MB
FROM sys.dm_db_log_space_usage;
---
8881
Spróbujmy teraz wycofać tę transakcję:
ROLLBACK TRAN;


Po półtorej minuty transakcja została wycofana, co oznacza, że wszystkie usunięte wiersze zostały z powrotem wstawione do tabeli Fact.OrderHistory. Ponieważ transakcja została zakończona, jej opis (w tym oryginalna wersja zmodyfikowanych danych) może zostać usunięta z dziennika transakcyjnego:


CHECKPOINT;
GO
SELECT used_log_space_in_bytes/1024/1024 AS sed_log_space_in_MB
FROM sys.dm_db_log_space_usage;
---
23


Przekonajmy się, jak na czas wycofania transakcji wpłynie włączenie mechanizmu przyspieszonego odzyskiwania baz danych:


ALTER DATABASE WideWorldImportersDW
SET ACCELERATED_DATABASE_RECOVERY = ON;


Ponownie usunęliśmy w ramach jawnie rozpoczętej transakcji wszystkie wiersze tabeli Fact.OrderHistory. Czas wykonania tej operacji się nie zmienił, ale ilość zalogowanych danych już tak:


SELECT used_log_space_in_bytes/1024/1024 AS sed_log_space_in_MB
FROM sys.dm_db_log_space_usage;
---
2198


Opis tej samej transakcji zajął 2,1 GB, a nie 8,8 GB, jak przed włączeniem opisywanej opcji. Ale to nie wszystko. Tym razem próba obcięcia dziennika transakcji zakończyła się sukcesem, chociaż transakcja, w ramach której usunięto wiersze, nadal jest aktywna:


CHECKPOINT;
GO
SELECT used_log_space_in_bytes/1024/1024 AS used_log_space_in_MB
FROM sys.dm_db_log_space_usage;
---
279


Jeszcze bardziej spektakularne jest skrócenie czasu wycofania transakcji z półtorej minuty do zera milisekund:


SET STATISTICS TIME ON
GO
ROLLBACK TRAN;
---
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.


Mechanizm przyspieszonego odzyskiwania baz danych wykorzystuje trwały magazyn wersji. Wersjonowanie wierszy zostało wprowadzone w wersji 2005. Pozwalała ona przełączyć bazę danych w tryb READ COMMITTED SNAPSHOT albo SNAPSHOT – w obu tych trybach modyfikowane wiersze są zapisywane w systemowej bazie tempdb. Omawiany mechanizm działa podobnie, z tym że modyfikowane dane są zapisywane w tej bazie, dla której został on włączony (grupę plików, do której trafi magazyn wersji, można wskazać, wykonując instrukcję ALTER DATABASE … SET ACCELERATED_DATABASE_RECOVERY = ON). W rezultacie zmiana wiersza poskutkuje zapisaniem jego oryginalnej wartości w magazynie, a do zmienianego wiersza dodany zostaje 14-bajtowy wskaźnik pozwalający ją odczytać.


Jeżeli transakcja, w ramach której wiersz został zmodyfikowany, musi zostać wycofana (ma to miejsce, gdy użytkownik wykonuje instrukcję ROLLBACK albo podczas odzyskiwania bazy danych), to serwer SQL nadpisuje bieżący wiersz jego kopią odczytaną z trwałego magazynu wersji. Nie tylko jest to znacznie szybsze od wykonania zapisanych w dzienniku transakcyjnym operacji kompensujących, ale również nie blokuje na czas wycofania transakcji dostępu do danych innym użytkownikom.


Dodatkowymi elementami mechanizmu przyspieszonego odzyskiwania baz danych jest nowy typ dziennika transakcyjnego (sLog), w którym zapisywane są wyłącznie te operacje, które nie powodują zapisania wierszy w trwałym magazynie wersji. Podzielenie zapisywanych transakcji pozwoliło efektywniej obcinać dziennik transakcyjny. Kolejną nowością jest asynchroniczny proces automatycznego usuwania niepotrzebnych już danych z trwałego magazynu wersji.


> OCENA ZAGROŻEŃ


Model bezpieczeństwa serwera SQL jest przykładem implementacji strategii dogłębnej obrony i polega na niezależnych zabezpieczeniach poszczególnych warstw systemu bazodanowego: od serwera poprzez bazy danych, komunikację z aplikacjami klienckimi, po dane. Najprostszym sposobem sprawdzenia, czy w pełni korzystamy z wbudowanych zabezpieczeń, jest przeprowadzenie oceny zagrożeń za pomocą konsoli SSMS. Choć przedstawiana funkcja nie jest nowością (w ten sposób możemy ocenić zagrożenia serwerów SQL od wersji 2012), a także nie jest mechanizmem serwera SQL, tylko konsoli SSMS od wersji 17.4, to otrzymany raport będzie dobrym punktem wyjścia do omówienia dwóch nowych zabezpieczeń serwera SQL 2019. Żeby wygenerować raport, należy kliknąć nazwę bazy danych prawym przyciskiem myszy i z menu kontekstowego wybrać Tasks/Vulnerability Assessment/Scan for Vulnerabilities... (rys. 1).

 

[...]

 

Pracownik naukowy Wyższej Szkoły Bankowej w Poznaniu Wydział Zamiejscowy w Chorzowie; jest autorem książek poświęconych analizie danych i posiada tytuł Microsoft Most Valuable Professional.

Pełna treść artykułu jest dostępna w papierowym wydaniu pisma.

.

Transmisje online zapewnia: StreamOnline

All rights reserved © 2019 Presscom / Miesięcznik "IT Professional"