Eksplorowanie wydajności i zabezpieczeń

• 13 min

Ekosystem platformy Azure oferuje kilka opcji wydajności i zabezpieczeń dla wystąpienia SQL Server na maszynie wirtualnej platformy Azure. Każda opcja zapewnia kilka możliwości, takich jak różne typy dysków, które spełniają wymagania dotyczące pojemności i wydajności obciążenia.

Zagadnienia dotyczące magazynu

SQL Server wymaga dobrej wydajności magazynu w celu zapewnienia niezawodnej wydajności aplikacji, niezależnie od tego, czy jest to wystąpienie lokalne, czy zainstalowane na maszynie wirtualnej platformy Azure. Platforma Azure oferuje szeroką gamę rozwiązań magazynu, które spełniają potrzeby obciążenia. Platforma Azure oferuje różne typy magazynów (obiektów blob, plików, kolejek, tabeli) w większości przypadków SQL Server obciążenia będą korzystać z dysków zarządzanych platformy Azure. Wyjątki są następujące, że wystąpienie klastra trybu failover można skompilować na magazynie plików, a kopie zapasowe będą używać magazynu obiektów blob. Dyski zarządzane platformy Azure działają jako urządzenie magazynu na poziomie bloku, które jest prezentowane maszynie wirtualnej platformy Azure. Dyski zarządzane oferują szereg korzyści, w tym dostępność 99,999%, skalowalne wdrożenie (w przypadku awarii można mieć maksymalnie 50 000 dysków maszyn wirtualnych na subskrypcję) oraz integrację z zestawami dostępności i strefami, aby zapewnić wyższy poziom odporności.

Wszystkie dyski zarządzane przez platformę Azure oferują dwa typy szyfrowania. Szyfrowanie po stronie serwera platformy Azure jest dostarczane przez usługę magazynu i działa jako szyfrowanie magazynowane dostarczane przez usługę magazynu. Usługa Azure Disk Encryption używa funkcji BitLocker w systemie Windows i DM-Crypt w systemie Linux w celu zapewnienia szyfrowania dysków systemu operacyjnego i danych wewnątrz maszyny wirtualnej. Obie technologie integrują się z usługą Azure Key Vault i umożliwiają korzystanie z własnego klucza szyfrowania.

Każda maszyna wirtualna będzie mieć co najmniej dwa dyski skojarzone z nim:

• Dysk systemu operacyjnego — każda maszyna wirtualna będzie wymagać dysku systemu operacyjnego zawierającego wolumin rozruchowy. Ten dysk będzie dyskiem C: w przypadku maszyny wirtualnej platformy Windows lub /dev/sda1 w systemie Linux. System operacyjny zostanie automatycznie zainstalowany na dysku systemu operacyjnego.

• Dysk tymczasowy — każda maszyna wirtualna będzie zawierać jeden dysk używany do przechowywania tymczasowego. Ten magazyn ma być używany w przypadku danych, które nie muszą być trwałe, takie jak pliki stron lub pliki wymiany. Ponieważ dysk jest tymczasowy, nie należy go używać do przechowywania żadnych krytycznych informacji, takich jak bazy danych lub pliki dziennika transakcji, ponieważ zostaną one utracone podczas konserwacji lub ponownego uruchomienia maszyny wirtualnej. Ten dysk zostanie zainstalowany jako D:\ w systemie Windows i /dev/sdb1 w systemie Linux.

Ponadto możesz i należy dodać dodatkowe dyski danych do maszyn wirtualnych platformy Azure z systemem SQL Server.

• Dyski danych — termin dysk danych jest używany w Azure Portal, ale w praktyce są to tylko dodatkowe dyski zarządzane dodane do maszyny wirtualnej. Te dyski można pulować w celu zwiększenia dostępnych operacji we/wy i pojemności magazynu przy użyciu Miejsca do magazynowania w systemie Windows lub zarządzania woluminami logicznymi w systemie Linux.

Ponadto każdy dysk może być jednym z kilku typów:

Najlepsze rozwiązania dotyczące SQL Server na platformie Azure zalecają używanie dysków w warstwie Premium w puli w celu zwiększenia liczby operacji we/wy na sekundę i pojemności magazynu. Pliki danych powinny być przechowywane we własnej puli z buforowaniem odczytu na dyskach platformy Azure.

Pliki dziennika transakcji nie będą korzystać z tego buforowania, więc te pliki powinny przejść do własnej puli bez buforowania. Baza danych TempDB może opcjonalnie przejść do własnej puli lub używać dysku tymczasowego maszyny wirtualnej, co zapewnia małe opóźnienie, ponieważ jest fizycznie dołączone do serwera fizycznego, na którym działają maszyny wirtualne. Prawidłowo skonfigurowany dysk SSD w warstwie Premium będzie widzieć opóźnienie w milisekundach z jedną cyfrą. W przypadku obciążeń o krytycznym znaczeniu, które wymagają mniejszego opóźnienia, należy rozważyć użycie dysków SSD w warstwie Ultra.

Zagadnienia dotyczące bezpieczeństwa

Istnieje kilka branżowych przepisów i standardów, które platforma Azure spełnia, dzięki czemu można utworzyć zgodne rozwiązanie z SQL Server działającym na maszynie wirtualnej.

Usługa Microsoft Defender dla usługi SQL

Microsoft Defender dla programu SQL udostępnia Azure Security Center funkcje zabezpieczeń, takie jak oceny luk w zabezpieczeniach i alerty zabezpieczeń.

Usługa Azure Defender for SQL może służyć do identyfikowania i ograniczania potencjalnych luk w zabezpieczeniach w wystąpieniu SQL Server i bazie danych. Funkcja oceny luk w zabezpieczeniach może wykrywać potencjalne zagrożenia w środowisku SQL Server i ułatwiać ich korygowanie. Zapewnia również wgląd w stan zabezpieczeń i kroki umożliwiające podjęcie działań w celu rozwiązania problemów z zabezpieczeniami.

Azure Security Center

Azure Security Center to ujednolicony system zarządzania zabezpieczeniami, który ocenia i oferuje możliwości poprawy kilku aspektów zabezpieczeń środowiska danych. Azure Security Center zapewnia kompleksowy wgląd w kondycję zabezpieczeń wszystkich zasobów chmury hybrydowej.

Zagadnienia dotyczące wydajności

Większość istniejących funkcji wydajności SQL Server lokalnych jest również dostępna na maszynach wirtualnych platformy Azure. Wśród oferowanych opcji jest kompresja danych, która może zwiększyć wydajność obciążeń intensywnie korzystających z operacji we/wy przy jednoczesnym zmniejszeniu rozmiaru bazy danych. Podobnie partycjonowanie tabel i indeksów może poprawić wydajność zapytań dużych tabel, jednocześnie zwiększając wydajność i skalowalność.

Partycjonowanie tabeli

Partycjonowanie tabel zapewnia wiele korzyści, ale często ta strategia jest brana pod uwagę tylko wtedy, gdy tabela staje się wystarczająco duża, co rozpoczyna obniżanie wydajności zapytań. Określenie, które tabele są kandydatami do partycjonowania tabel, jest dobrym rozwiązaniem, które może prowadzić do mniejszej liczby zakłóceń i interwencji. Podczas filtrowania danych przy użyciu kolumny partycji uzyskuje się dostęp tylko do podzbioru danych, a nie całej tabeli. Podobnie operacje konserwacji w tabeli partycjonowanej zmniejszają czas trwania konserwacji, na przykład przez kompresowanie określonych danych w określonej partycji lub ponowne kompilowanie określonych partycji indeksu.

Podczas definiowania partycji tabeli wymagane są cztery główne kroki:

• Tworzenie grup plików, które definiuje pliki związane z tworzeniem partycji.
• Tworzenie funkcji partycji, która definiuje reguły partycji na podstawie określonej kolumny.
• Tworzenie schematu partycji, który definiuje grupę plików każdej partycji.
• Tabela do partycjonowania.

W poniższym przykładzie pokazano, jak utworzyć funkcję partycji dla 1 stycznia 2021 r. do 1 grudnia 2021 r. i dystrybuować partycje w różnych grupach plików.

-- Partition function
CREATE PARTITION FUNCTION PartitionByMonth (datetime2)
    AS RANGE RIGHT
    -- The boundary values defined is the first day of each month, where the table will be partitioned into 13 partitions
    FOR VALUES ('20210101', '20210201', '20210301',
      '20210401', '20210501', '20210601', '20210701',
      '20210801', '20210901', '20211001', '20211101', 
      '20211201');

-- The partition scheme below will use the partition function created above, and assign each partition to a specific filegroup.
CREATE PARTITION SCHEME PartitionByMonthSch
    AS PARTITION PartitionByMonth
    TO (FILEGROUP1, FILEGROUP2, FILEGROUP3, FILEGROUP4,
        FILEGROUP5, FILEGROUP6, FILEGROUP7, FILEGROUP8,
        FILEGROUP9, FILEGROUP10, FILEGROUP11, FILEGROUP12);

-- Creates a partitioned table called Order that applies PartitionByMonthSch partition scheme to partition the OrderDate column  
CREATE TABLE Order ([Id] int PRIMARY KEY, OrderDate datetime2)  
    ON PartitionByMonthSch (OrderDate) ;  
GO  

Kompresja danych

SQL Server oferuje różne opcje kompresji danych. Podczas gdy SQL Server nadal przechowuje skompresowane dane na 8 KB stron, gdy dane są kompresowane, na danej stronie można przechowywać więcej wierszy danych, co pozwala na odczytywanie mniejszej liczby stron przez zapytanie. Odczytywanie mniejszej liczby stron ma mniejszą korzyść: zmniejsza ilość wykonanych operacji we/wy fizycznych i umożliwia przechowywanie większej liczby wierszy w puli buforów, dzięki czemu wydajniejsze wykorzystanie pamięci. Zalecamy włączenie kompresji strony bazy danych, jeśli jest to konieczne.

Kompromisy związane z kompresją są to, że wymaga niewielkiego nakładu pracy procesora CPU, jednak w większości przypadków operacje we/wy magazynu znacznie przewyższają wszelkie dodatkowe użycie procesora.

Na powyższej ilustracji przedstawiono tę korzyść z wydajności. Te tabele mają te same indeksy bazowe; Jedyną różnicą jest to, że indeksy klastrowane i nieklastrowane w tabeli Production.TransactionHistory_Pagesą skompresowane na stronie. Zapytanie względem skompresowanego obiektu strony wykonuje 72% mniej operacji odczytu logicznego niż zapytanie korzystające z nieskompresowanych obiektów.

Kompresja jest implementowana w SQL Server na poziomie obiektu. Każdy indeks lub tabelę można skompresować indywidualnie i można skompresować partycje w tabeli partycjonowanej lub indeksie. Za pomocą procedury składowanej systemu sp_estimate_data_compression_savings można ocenić ilość miejsca do zaoszczędzenia. Przed SQL Server 2019 r. ta procedura nie obsługiwała indeksów magazynu kolumn ani kompresji archiwalnej magazynu kolumn.

• Kompresja wierszy — kompresja wierszy jest dość podstawowa i nie wiąże się z dużym obciążeniem; jednak nie oferuje tej samej ilości kompresji (mierzonej procentowo zmniejszenie wymaganego miejsca do magazynowania), którą może zaoferować kompresja strony. Kompresja wierszy zasadniczo przechowuje każdą wartość w każdej kolumnie w wierszu w minimalnej ilości miejsca potrzebnego do przechowywania tej wartości. Używa formatu magazynu o zmiennej długości dla typów danych liczbowych, takich jak liczba całkowita, zmiennoprzecinkowa i dziesiętna, oraz przechowuje ciągi znaków o stałej długości przy użyciu formatu zmiennej długości.

• Kompresja strony — kompresja strony jest nadzbiorem kompresji wierszy, ponieważ wszystkie strony będą początkowo kompresowane wiersze przed zastosowaniem kompresji strony. Następnie do danych są stosowane kombinacje technik nazywanych prefiksem i kompresją słownika. Kompresja prefiksu eliminuje nadmiarowe dane w jednej kolumnie, przechowując wskaźniki z powrotem do nagłówka strony. Po tym kroku kompresja słownika wyszukuje powtarzające się wartości na stronie i zastępuje je wskaźnikami, co jeszcze bardziej zmniejsza ilość miejsca do magazynowania. Im większa nadmiarowość danych, tym większe oszczędności miejsca podczas kompresowania danych.

• Kompresja archiwalna magazynu kolumn — obiekty magazynu kolumn są zawsze kompresowane, jednak można je dodatkowo skompresować przy użyciu kompresji archiwalnej, która używa algorytmu kompresji Microsoft XPRESS na danych. Ten typ kompresji jest najlepiej używany w przypadku danych, które są rzadko odczytywane, ale należy je przechowywać ze względów prawnych lub biznesowych. Chociaż te dane są dodatkowo kompresowane, koszt procesora CPU dekompresji zwykle przewyższa wszelkie korzyści z wydajności wynikające z redukcji operacji we/wy.

Opcje dodatkowe

Poniżej znajduje się lista dodatkowych funkcji i akcji SQL Server, które należy wziąć pod uwagę w przypadku obciążeń produkcyjnych:

• Włącz kompresję kopii zapasowych
• Włącz natychmiastowe inicjowanie plików dla plików danych
• Ogranicz automatyczne zwiększanie bazy danych
• Wyłączanie autoshrink/autoclose dla baz danych
• Przenoszenie wszystkich baz danych na dyski danych, w tym systemowych baz danych
• Przenoszenie katalogów plików SQL Server dziennika błędów i śledzenia na dyski danych
• Ustawianie maksymalnego limitu pamięci SQL Server
• Włącz strony blokady w pamięci
• Włączanie optymalizacji pod kątem obciążeń adhoc w środowiskach o dużym obciążeniu OLTP
• Włącz magazyn zapytań.
• Planowanie zadań agenta SQL Server do uruchamiania zadań DBCC CHECKDB, reorganizacji indeksu, ponownego kompilowania indeksu i aktualizowania zadań statystyk
• Monitorowanie kondycji i rozmiaru plików dziennika transakcji i zarządzanie nimi

Aby uzyskać więcej informacji na temat najlepszych rozwiązań dotyczących wydajności, zobacz Najlepsze rozwiązania dotyczące SQL Server na maszynach wirtualnych platformy Azure.