Microsoft SQL Server

Serwer SQL

Informacja

Opracowany przez	Microsoft
Pierwsza wersja	24 kwietnia 1989
Ostatnia wersja	2019 (4 listopada 2019 r.)
Napisane w	C++ , C i C #
System operacyjny	Linux , Microsoft Windows i Windows Server
Czytaj formaty	Język definicji raportów usług SQL Server Reporting Services ( d ) i język definicji raportów usług SQL Server Reporting Services (UTF-8) ( d )
Pisemne formaty	Język definicji raportów usług SQL Server Reporting Services ( d ) i język definicji raportów usług SQL Server Reporting Services (UTF-8) ( d )
Rodzaj	System zarządzania relacyjnymi bazami danych ( w )
Licencja	Zastrzeżona licencja i EULA
Stronie internetowej	www.microsoft.com/sql-server

Microsoft SQL Server to system zarządzania bazą danych (DBMS) w języku SQL zawierający między innymi RDBMS ( relacyjny DBMS ) opracowany i sprzedawany przez firmę Microsoft . Działa pod systemami Windows i Linux OS (odmarzec 2016), ale możliwe jest uruchomienie go w systemie Mac OS przez Docker , ponieważ jest wersja do pobrania na stronie Microsoft.

Historyczny

Mimo, że pierwotnie został opracowany wspólnie przez Sybase i Microsoft, Ashton-Tate był również związany z jego pierwszą wersją, wydaną w 1989 roku. Ta wersja została wydana na platformach Unix i OS/2 . Microsoft od tego czasu przeniósł ten system baz danych do systemu Windows .

Kiedy został utworzony, Sybase SQL Server odziedziczył zasady silnika Ingres pierwotnie opracowanego przez University of Berkeley.

W 1994 roku, po zerwaniu partnerstwa między tymi dwiema firmami, Microsoft wypuścił wersję 6.0, a następnie 6.5 na platformę Windows NT.

Microsoft kontynuował sprzedaż silnika bazy danych jako SQL Server
Natomiast Sybase, aby uniknąć nieporozumień, zmienił nazwę Sybase SQL Server na Sybase Adaptive Server Enterprise.

Microsoft SQL Server jest teraz częścią strategii technicznej firmy Microsoft dotyczącej baz danych. Silnik MSDE, będący podstawą SQL Server, powinien docelowo zastąpić silnik Jet (ten, który zarządza bazami danych Access ) w aplikacjach takich jak Exchange i Active Directory .

Wersja 2005 SQL Server została wydana w dniu3 listopada 2005 r.w tym samym czasie co Visual Studio 2005 . Obsługa systemów Windows Vista i Windows Server 2008 nie została dodana do dodatku Service Pack 2 (SP2). Obecnie dostępny jest dodatek Service Pack 3. Optymalizator został całkowicie przerobiony, podobnie jak silnik relacji.

Wersja 2008 SQL Server (nazwa kodowa Katmaï) została wydana w sierpniu 2008 . Mniejsza wersja 2008 R2 została wydana w 2010 roku.

Wersja 2012 programu SQL Server została wydana w kwietniu 2012 roku .

Wersja 2014 programu SQL Server została wydana w dniu 1 st kwiecień 2014 z silnikiem „w pamięci” komplementarnym do silnika relacyjnego.

Wersja SQL Server 2016 została wydana w dniu 1 st czerwiec 2016.

W trakcie rozwoju wersji 2005 projekt był wówczas jednym z największych projektów informatycznych na świecie. W kodowaniu jądra uczestniczyło ponad 1600 programistów i szacuje się, że ponad 10 000 innych osób pracowało nad nim bezpośrednio lub pośrednio ( interakcje człowiek-maszyna , dokumentacja, tłumaczenie itp.).

Cechy szczególne

SQL Server wyróżnia się na tle konkurencji ogromnym bogactwem, które nie wymaga żadnych dodatkowych płatnych opcji w ramach limitu wybranej wersji.

Wiele baz, wiele schematów

W porównaniu do swoich konkurentów Oracle , MySQL lub PostgreSQL , SQL Server wyróżnia fakt, że jest to RDBMS (Relational Database Management System) pierwotnie multibase i multi schematu . Możliwe jest tworzenie natywnych zapytań międzybazowych. Na przykład poniższe zapytanie łączy dwie tabele z dwóch różnych baz danych:

SELECT * FROM BASE_A.dbo.TABLE1 AS T1 INNER JOIN BASE_B.dbo.TABLE2 AS T2 ON T1.ID = T2.ID;

Optymalizator jest w stanie stworzyć idealnie zoptymalizowany plan zapytania, nawet jeśli zapytanie konsultuje dane z kilku baz danych ...

Chociaż PostGreSQL jest multibase i multischema, ta możliwość jednoczesnego odpytywania nie jest natywna i konieczne jest ominięcie pośrednika „dblink”, który zabrania łączeń, a tym samym jakiejkolwiek możliwości optymalizacji… Oracle ze swoją wersją 12 stara się to zintegrować ta sama koncepcja multibase (zwana multi-tenant ), ale cierpi na ten sam problem, co PostGreSQL. MySQL to pojedynczy schemat, wielobazowy.

Zarządzanie schematem SQL

Elastyczność zarządzania schematami SQL umożliwia przeniesienie obiektu z jednego schematu do drugiego za pomocą jednego polecenia ALTER SCHEMA.

Właściciele różnią się od schematów i za pomocą polecenia można przenieść prawo własności do bazy danych, schematu lub obiektu z jednego użytkownika SQL na drugiego ALTER AUTHORIZATION.

Równoległość

SQL Server natywnie działa równolegle. Gdy tylko zapytanie zostanie oszacowane, że przekroczy próg kosztu, przy którym plan zapytania może być zrównoleglony, SQL Server przepisuje plan przy użyciu algorytmów wielowątkowych, a jeśli nowy plan okaże się tańszy, zastąpienie następuje automatycznie. Ta funkcja istnieje we wszystkich edycjach i nie jest płatnym modułem do dodania, jak ma to miejsce w przypadku Oracle.

Fizyczne operacje odczytu i zapisu również korzystają z systematycznego paralelizmu, ponieważ operacje IO są wykonywane bezpośrednio przez SQL Server, a nie przez warstwę systemową, jak ma to miejsce w przypadku PostGreSQL lub MySQL.

Kompresja

W wersji Enterprise silnik jest w stanie kompresować dane na poziomie wiersza lub strony w tabelach oraz w indeksach. Kompresja kopii zapasowej jest dostępna w wersji standardowej od wersji 2008 R2.

Gorąca kopia zapasowa

Począwszy od SQL Server 7 (1998), SQL Server umożliwia tworzenie kopii zapasowych „otwartej bazy danych” bez przerywania obsługi danych i bez zakłócania aktywności użytkownika. Tę kopię zapasową można wykonać globalnie, według grupy plików, według pliku lub za pośrednictwem dziennika transakcji.

Wyjątkowy... z wyjątkiem NULL!

W SQL Server ograniczenia unikalności uwzględniają znacznik NULL jako wartość i dlatego zabraniają obecności więcej niż jednego NULL w kolumnach takiego ograniczenia. Nie jest to zgodne ze standardem SQL, ponieważ NULL jest brakiem wartości, unikalność nie powstaje. Istnieje jednak prosty sposób na obejście tego problemu, który polega na stworzeniu unikalnego filtrowanego indeksu, który będzie odnosił się do wycenianych danych.

Przykład, niech tabela:

CREATE TABLE T_EMPLOYE_EMP (EMP_ID INT PRIMARY KEY, EMP_NOM VARCHAR(32) NOT NULL, EMP_MATRICULE CHAR(8));

Ponieważ numer osobowy musi być unikalny dla znanych wartości, stworzenie unikalnego indeksu filtrowanego według wartości wyrażonych w kolumnie numer osobowy umożliwia rozwiązanie problemu:

CREATE UNIQUE INDEX X_EMP_MATRICULE ON T_EMPLOYE_EMP(EMP_MATRICULE) WHERE EMP_MATRICULE IS NOT NULL;

Ponowne wprowadzanie wyzwalaczy (tabela „mutant”)

W wyzwalaczach SQL Server możliwa jest aktualizacja tabeli docelowej zdarzenia w miejscu pochodzenia wyzwalacza, w przeciwieństwie do wielu baz danych, które zabraniają takiej możliwości (błąd „tabela mutantów” w Oracle). Należy pamiętać, że to Sybase pierwotnie wymyślił koncepcję wyzwalacza w 1986 roku, po raz pierwszy umieszczając kod wykonywalny w relacyjnej bazie danych.

Na przykład wyzwalacz UPDATEw tabeli może modyfikować dane oczekujące na modyfikację:

CREATE TRIGGER E_U_PERSONNE ON T_PERSONNE_PRS FOR UPDATE AS IF UPDATE(PRS_NOM) UPDATE T_PERSONNE_PRS SET PRS_NOM = UPPER(PRS_NOM) WHERE PRS_ID IN (SELECT PRS_ID FROM inserted);

W powyższym przykładzie nazwy nowo wstawionych lub zmienionych nazw są pisane wielkimi literami.

Ponieważ reentrance może prowadzić do rekurencji, różne ustawienia umożliwiają rozwiązanie tego problemu na poziomie bazy danych, a także na poziomie instancji.

Zautomatyzowane widoki indeksowane

W przeciwieństwie do Oracle lub PostGreSQL, w których zmaterializowane widoki muszą być przez większość czasu odświeżone przed użyciem, widoki indeksowane SQL Server są zawsze synchroniczne. Dodatkowo optymalizator jest w stanie podmienić w locie widok indeksowany dla całości lub części zapytania (wersja Enterprise) co jest bardzo praktyczne, gdy DBA nie ma dostępu do kodu programów klienckich (przypadek aplikacji redaktorów) .

Automatyczne łączenie

W przypadku aplikacji zaprojektowanych w .net i korzystających z łącznika ADO SQL Server wykonuje automatyczne łączenie. Prosty fakt rozróżnienia dwóch łańcuchów połączeń wystarczy, aby utworzyć dwie różne pule. Aby buforowanie było skuteczne, połączenia muszą zostać zamknięte po zakończeniu przetwarzania komputerowego.

Scentralizowana administracja

Na różne sposoby SQL Server umożliwia centralne zarządzanie zestawem serwerów. Możesz stworzyć scentralizowany serwer administracyjny i dodać listę firmowych serwerów SQL w celu uruchomienia zapytań wysyłanych jednocześnie na wszystkich serwerach lub stworzyć lub wykonać tę samą procedurę na wszystkich serwerach jednocześnie, to w narzędziu SSMS (Studio zarządzania serwerem SQL).

Podobnie harmonogram zadań i menedżer alertów (SQL Agent) umożliwia definiowanie zadań wieloserwerowych, takich jak tworzenie kopii zapasowych, defragmentacja indeksów, przeliczanie statystyk czy sprawdzanie fizycznej integralności przestrzeni.

Funkcje do rozwoju

Silnik SQL Server OLTP ma wiele funkcji, które trudno byłoby wymienić na wszystkie. Oto kilka, które robią różnicę w porównaniu z lżejszymi systemami DBMS, takimi jak MySQL lub PostGreSQL ...

Tabela, indeks i procedura „W pamięci”

SQL Server został wyposażony w silnik „In Memory” od wersji 2014. Tabele i indeksy mogą być całkowicie w pamięci (struktura i dane: dane i schemat) lub trwałe na poziomie struktury (tylko dane). Procedury, w dobrze zdefiniowanych granicach, mogą być kompilowane natywnie, a nie interpretowane. Aktualizacje pozostają transakcyjne, bez konieczności zapisywania w plikach dziennika transakcji (wersjonowanie „w pamięci”), co pozwala na znaczną poprawę wydajności przetwarzania złożonych obliczeń dotyczących dużych ilości danych. Typowym przykładem jest obliczanie wyników finansowych cen giełdowych w czasie rzeczywistym.

Połączenie wewnętrzne (APPLY)

SQL Server jako pierwszy w wersji 2005 wprowadził operator intra-join w APPLYcelu ułatwienia pobierania danych zawartych w tabeli wirtualnej z kolumny tabeli, tak jak ma to miejsce w przypadku użycia funkcji tabeli w zapytaniu, w którym jeden z argumentów pochodzi z jednej z tabel w zapytaniu lub gdy XML jest stabelaryzowany. Operator APPLYmusi być poprzedzony słowem kluczowym CROSSlub w OUTERcelu zdefiniowania jego zachowania. Ze CROSSjeśli argument jest null, wierszy są usuwane z wyniku, gdy z OUTERnich są zachowane.

Operator APPLYjest podobny do operatora LATERALstandardu SQL, ale jego bardziej rozszerzona składnia umożliwia wykonywanie operacji, LATERALktórych nie można wykonać. Firma Oracle niedawno wprowadziła ten operator w wersji 12.1 (2014).

SELECT top 10 * FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) ORDER BY total_worker_time DESC

W tym przykładzie używamy funkcji tabelowej sys.dm_exec_query_plan, która zwraca plan wykonania z „uchwytu” planu dostarczonego przez argument „plan_handle” znajdujący się w widoku sys.dm_exec_query_stats. To zapytanie pozwala znaleźć 10 zapytań, które zajęły najwięcej czasu procesora od momentu uruchomienia instancji.

Przekąski (dosłowne zestawienie)

SQL Server posiada jedną z najbogatszych kolekcji zestawień (3887 w wersji 2014), pozwalającą na kontrolę 101 języków, wśród których oprócz francuskiego są bretoński i korsykański! Zestawienia pozwalają na przeprowadzanie wyszukiwań z uwzględnieniem wielkości liter (duże, małe - CS/CI), akcentów i innych znaków diakrytycznych (akcenty, cedilla, ligatury... - AS/AI), szerokość znaków (2 = 2 ? - WS) lub różnych typów kana (katakana i hiragana z japońskiego - KS), a nawet do wykonywania porównań binarnych z uwzględnieniem lub nie kodu szesnastkowego. Porównania są całkowicie niezależne od zestawów znaków (zestawy Iso_1), chociaż umożliwiają ich symulację (ASCII, UNICODE, UTF16, EBCDIC ...). Obsługa sortowania realizowana jest na czterech poziomach: serwer (1), baza (2), kolumna tabeli lub widoku (3) i wreszcie predykat (4) obsługujący ciągi znaków i sortujący kolumny tekstowe w zapytaniach.

SELECT * FROM T_UTILISATEUR_APPLICATIF_UTA WHERE UTA_CONNEXION = 'Éric Blüm' COLLATE French_CI_AI AND UTA_MOT_DE_PASE = 'a²E3wx+Z' COLLATE Latin1_General_CS_AS_WS

W poniższym przykładzie szukamy nazwy połączenia z „luźnym” sortowaniem (niezależnie od wielkości liter czy akcentów) oraz hasła z „ciasnym” sortowaniem (binarnie):

Listę zestawień dostępnych na serwerze można uzyskać poprzez zapytanie:

SELECT * FROM sys.fn_helpcollations()

Dane XML

Od wersji 2005 SQL Server obsługuje typ XML (fragment) i pozwala manipulować XML za pomocą zapytań XQuery / XPath. Kolumnę typu XML można wpisać za pomocą kolekcji schematów XML (XSD — definicja schematu XML). Poniższy przykład pokazuje tworzenie kolekcji schematów XML z jednym elementem, a następnie tworzenie tabeli z wpisaną kolumną XML z tą kolekcją schematów:

-- création d'un schéma XML pour validation des données d'une colonne XML CREATE XML SCHEMA COLLECTION XSC_DONNEES_ENTREPRISE as N' <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:company="http://www.exemple.com/entreprise" targetNamespace="http://www.exemple.com/entreprise" elementFormDefault="qualified"> <xs:element name="personnel"> <xs:complexType> <xs:choice maxOccurs="unbounded"> <xs:element name="employe"> <xs:complexType> <xs:all> <xs:element name="nom" type="xs:string" /> </xs:all> <xs:attribute name="matricule" type="xs:ID" /> </xs:complexType> </xs:element> </xs:choice> </xs:complexType> </xs:element> </xs:schema>'; -- création d'une table qui possède une colonne de type XML dont les données doivent se conformer au schéma ci avant CREATE TABLE T_ENTREPRISE_EMPLOIS_EEP (EEP_ID INTEGER PRIMARY KEY IDENTITY, EEP_NAME VARCHAR(12), EEP_XML_DATA XML (XSC_DONNEES_ENTREPRISE) --> création d'une colonne XML typée par XSC_DONNEES_ENTREPRISE (collection de schémas XML) );

Wyodrębniające zapytanie SELECT może generować dane wyjściowe XML zamiast wyników tabelarycznych za pomocą klauzuli Transact-SQL „FOR XML”, dla której istnieje wiele opcji prezentacji. Poniższy przykład służy do przedstawienia hierarchii XML wymieniającej ograniczenia tabeli w postaci elementów XML:

SELECT TABLE_SCHEMA AS schemaSQL, TABLE_NAME AS nomTable, CONSTRAINT_NAME AS nomContrainte, CONSTRAINT_TYPE AS typeContrainte FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS contrainte WHERE TABLE_NAME = 'DepartementFrancais' FOR XML AUTO, ELEMENTS, ROOT('Contraintes');

To zapytanie zwróci na przykład następujące dane:

<Contraintes> <contrainte> <schemaSQL>Reference</schemaSQL> <nomTable>DepartementFrancais</nomTable> <nomContrainte>pk_DepartementFrancais</nomContrainte> <typeContrainte>PRIMARY KEY</typeContrainte> </contrainte> <contrainte> <schemaSQL>Reference</schemaSQL> <nomTable>DepartementFrancais</nomTable> <nomContrainte>chk_DepartementFrancais_ForceSTSRid</nomContrainte> <typeContrainte>CHECK</typeContrainte> </contrainte> <contrainte> <schemaSQL>Reference</schemaSQL> <nomTable>DepartementFrancais</nomTable> <nomContrainte>UK_DepartementFrancais_NomChefLieu</nomContrainte> <typeContrainte>UNIQUE</typeContrainte> </contrainte> <contrainte> <schemaSQL>Reference</schemaSQL> <nomTable>DepartementFrancais</nomTable> <nomContrainte>UK_DepartementFrancais_Nom</nomContrainte> <typeContrainte>UNIQUE</typeContrainte> </contrainte> </Contraintes>

Wreszcie możliwe jest bezpośrednie przetwarzanie danych XML w procedurach (na przykład pochodzących z plików), za pomocą funkcji i procedur OPENXML, sp_xml_preparedocument, sp_xml_removedocument...

Zintegrowany System Informacji Geograficznej (GIS)

Od wersji 2008 SQL Server integruje GIS we wszystkich wersjach. Pozwala to na konstruowanie obiektów geometrycznych (geometria euklidesowa) lub geograficznych (z uwzględnieniem przestrzennej krzywizny skorupy ziemskiej) z wykorzystaniem typów GEOMETRIA i GEOGRAFIA (PUNKT, LINESTRING, WIELOKĄT i kombinacje…). Opiera się na standardzie OGC , podobnie jak przestrzenny dodatek PostGIS do PostgreSQL . wersja 2012 dodała zakrzywione obiekty (CircularStrings, CompoundCurves, CurvePolygons…) i większą precyzję obliczeń (48 bitów zamiast 27 poprzednio).

Indeksy przestrzenne umożliwiają tworzenie niektórych metod, takich jak STContains, STDistance, STEquals, STIntersects, SToverlaps, STTouches, STWithin ...

Zgodnie ze standardem SQL, który oferuje DATALINK, SQL Server implementuje podobne narzędzie o nazwie FileStream pozwalające na przechowywanie plików jako plików w systemie operacyjnym, ale pod odpowiedzialnością i kontrolą serwera bazy danych (serializacja, transakcja, niezawodność, bezpieczeństwo). ...). Pozwala to między innymi na zintegrowane i synchroniczne tworzenie kopii zapasowych danych relacyjnych i nierelacyjnych, na przykład obrazów produktów na stronie internetowej, takich jak elektroniczne instrukcje obsługi w formacie PDF dla maszyn we flocie w celu konserwacji. To narzędzie jest szczególnie używane do EDM. Pliki elektroniczne, które mogą podlegać indeksowaniu tekstowemu.

Ponadto, od wersji 2012, SQL Server oferuje koncepcję FileTable, która jest niczym innym jak wirtualną tabelą powiązaną z punktem wejścia (katalogiem) systemu komputerowego, aby umożliwić dwukierunkowe zarządzanie plikami elektronicznymi: plikami obsługiwanymi przez język SQL jest dostępny w systemie plików Windows i odwrotnie: plik lub katalog można wstawić do tabeli za pomocą zapytania SQL, a ten ostatni będzie widoczny w drzewie systemowym.

Narzędzie do zarządzania wiedzą Sharepoint masowo wykorzystuje tę koncepcję do zarządzania zdeponowanymi tam dokumentami elektronicznymi i wszystkimi wersjami przejściowymi.

Usługi internetowe

Wprowadzona w wersji 2008 możliwość tworzenia natywnych usług internetowych bezpośrednio na serwerze przy użyciu punktów końcowych SOAP/HTTP została uznana za przestarzałą w wersji 2008 R2 i wycofana w wersji 2012. Jednak SQL Server umożliwia tworzenie i używanie wewnętrznie wielu punktów końcowych dla poszczególnych usług, takich jak dublowanie baz danych czy broker usług...

Usługa brokerska

Celem usługi Service Broker jest zapewnienie narzędzia do obsługi komunikatów bazy danych do zarządzania przepływami danych między serwerami SQL w sposób asynchroniczny, serializowany i transakcyjny za pośrednictwem kolejek, które są tabelami systemowymi. Umożliwia to tworzenie SODA (Service Oriented Database Architecture), czyli baz danych różnych instancji, które komunikują się ze sobą na poziomie danych, a nie na poziomie aplikacji, jak to ma miejsce w przypadku SOA.

Umożliwia to tworzenie rozproszonych, współpracujących i rozproszonych baz danych, „przetwarzania siatkowego”, a nawet WorkFlow, i na przykład zapewnia lepszą powierzchnię ataku dla bardzo konkurencyjnych dostępów. System ten różni się od replikacji danych tym, że informacje są przekazywane głównie w postaci XML (zaszyfrowane), w warstwie HTTP (np. Internet) za pomocą usług sieci Web oraz że nie ma obowiązku baz danych pochodzenia i przeznaczenia oraz tabele mają być strukturalnie identyczne.

Ciekawym przykładem jest zastąpienie tradycyjnych narzędzi do przesyłania wiadomości, takich jak MQ Series, takim urządzeniem, znacznie bardziej niezawodnym…

Service Broker może być używany za pośrednictwem bezpłatnej edycji SQL Server Express, jest on używany zwłaszcza w branży do zarządzania informacjami produkcyjnymi wielu maszyn tej samej floty w celu przesłania tych danych do centralnego serwera. Dotyczy to w szczególności łańcucha produkcji szczepionek słynnego francuskiego instytutu.

Funkcje dla administratora

Oprócz funkcji dla programisty SQL Server udostępnia wiele narzędzi ułatwiających administrację. Tu jest kilka.

Replikacja danych

Nie mylić z „wysoką dostępnością” polegającą na zduplikowaniu całej bazy danych, SQL Server posiada 8 trybów replikacji danych (replikacja niektórych informacji z określonych tabel pochodzących z baz danych do innych baz na innych serwerach), w tym transakcyjny, punkt-punkt , migawki lub tryby scalania. Replikacja zi do Oracle jest również obsługiwana w wersji Enterprise.

Witryny sprzedaży online, takie jak Cdiscount , Fnac.com lub Vente-privee.com, wykorzystują replikację, zarówno do oddzielenia danych od FO (Front Office - zasadniczo dane witryny), jak i BO (Back Office - zarządzanie), faktur, przesyłek, zapasów. ..), ale także poszerzyć powierzchnię ataku, aby wchłonąć obciążenie wielu użytkowników.

Duża dostępność

Powielanie baz danych w celu stworzenia wysoce odpornego systemu odbywa się na cztery różne sposoby:

„ Log Shipping ” poprzez wysłanie kopii zapasowej dziennika transakcji i ponowne zastosowanie transakcji w sytuacjach awaryjnych. Asynchroniczny ręczny system przechylania. Straty ograniczone do opóźnienia wysyłania logów. Działa baza po bazie.
„ Odbicie lustrzane ” poprzez wysyłanie impulsowych zmienionych stron dziennika transakcji do trybu gotowości. Przejrzysty system dla aplikacji (.net lub ODBC) w trybie synchronicznym lub asynchronicznym z ręcznym lub automatycznym przełączaniem (jeśli jest synchroniczny) baza po bazie. Zero opóźnień w przypadku przełączania awaryjnego w trybie synchronicznym iw trybie automatycznym. Tolerowany jest tylko jeden serwer lustrzany na bazę danych, a do automatycznego przełączania awaryjnego konieczny jest serwer-świadek (na przykład bezpłatna wersja Express), aby uniknąć syndromu „rozszczepionego mózgu”.
„ Klastrowanie ” przez nadmiarowość serwerów (do 64 aktywnych lub pasywnych węzłów), które współdzielą wnękę na dysk ( Share Nothing ), do której można dodać system replikacji IO wnęki, aby uniknąć SPOF ( Single Point Of Failure ). Pozwala na ręczne lub automatyczne przełączanie całej instancji zgodnie z zaimplementowanymi regułami. Zawsze synchroniczne dane. Czas zmiany w zależności od ustalonego scenariusza, ale generalnie od kilkudziesięciu sekund do kilku minut.
Technologia „ AlwaysOn ” : połączenie mirroringu i Failover Cluster (FCI dla Failover Cluster Instance ) pozwalająca na powiązanie różnych baz w ramach grup dostępności, co zapewnia zbiorcze przełączanie awaryjne wszystkich baz w przypadku awarii jednej z nich w ramach grupy. Jeśli chodzi o dublowanie, system jest przezroczysty dla aplikacji (.net lub ODBC) w trybie synchronicznym lub asynchronicznym z ręcznym lub automatycznym przełączaniem (jeśli jest synchroniczny) grupa po grupie. Opóźnienie przełączania awaryjnego wynosi zero, jeśli przyjęto tryb synchroniczny. Liczba „replik” tej samej grupy dostępności jest ograniczona do 10: 8 asynchronicznych i 2 synchronicznych. Pojedyncza grupa dostępności, która może zawierać ponad sto baz danych.

Zarządzanie strategią (polityki administracyjne - zarządzanie polityką)

Możliwe jest tworzenie reguł biznesowych i planowanie ich działania. Ma to na celu zweryfikowanie atrybutu aspektu za pomocą reguły, którą można zweryfikować na różne sposoby. Kilka przykładów:

sprawdzaj raz dziennie, czy każda baza danych została zarchiwizowana
wymusić nazewnictwo tabel, widoków, procedur
zabronić systematycznego używania domyślnego schematu SQL dbo

Migawka bazy danych

Proces ten umożliwia utworzenie nowej bazy danych z istniejącej bazy, która reprezentuje dane w momencie uruchomienia „migawki”. Ta kopia tylko do odczytu jest wykonywana natychmiast, niezależnie od wielkości początkowej bazy danych. Pozwala to na przykład:

uzyskać z pewnością wszystkie dane z bazy danych w określonym czasie
możliwość porównania w opracowaniu danych początkowych i końcowych po wykonaniu procedury;
wróć albo przez żądanie modyfikacji między bazami danych, albo przez przywrócenie bazy danych z migawki

Partycjonowanie

Obecne od wersji 2005 w edycji Enterprise partycjonowanie umożliwia dzielenie tabel i indeksów według kryterium danych tabeli. rozwiązanie wielokrotnego użytku jest bardzo proste i składa się z trzech etapów:

Tworzenie kluczowych wartości partycjonowania za pomocą funkcji partycjonowania (CREATE PARTITION FUNCTION ...);
Stworzenie schematu wentylacji partycji w różnych przestrzeniach magazynowych (CREATE PARTITION SCHEME ...);
Utworzenie tabeli lub indeksu wykorzystującego schemat partycjonowania przy użyciu kryterium, które musi być kolumną inną niż NULL obecną w definicji obiektu.

Po wykonaniu tych kroków funkcja i schemat partycjonowania mogą być ponownie użyte dla innego obiektu w celu wyrównania partycji, takiego jak tabela faktur i podrzędne szczegóły lub tabela wierszy faktury.

Możliwe jest wtedy zarządzanie partycjami (przenoszenie, scalanie, dzielenie...) gorącymi za pomocą ALTER PARTITION FUNCTION ... SPLIT RANGE / MERGE RANGE i ALTER TABLE ... SWITCH PARTITION ... poleceń, które natychmiast renderują rozdanie i działaj na niskim poziomie, w tle, aby operować ruchami.

Plan prac porządkowych

Microsoft SQL Server zapewnia wiele kreatorów do wykonywania często niewdzięcznych zadań, takich jak import / eksport danych lub tworzenie kopii zapasowych / przywracanie. Ale jeśli jest przydatne dla początkujących, jest to narzędzie do zarządzania planem konserwacji, które pozwala za pomocą kilku kliknięć wykonać wszystkie zadania niezbędne do przetrwania i utrzymania wydajności bazy. Narzędzie to pozwala m.in. na cykliczne wykonywanie następujących zadań:

utworzyć kopię zapasową;
kontrola integralności fizycznej;
defragmentacja indeksów;
przeliczenia statystyk.

...i zaplanuj jego wykonanie.

Zarządca Zasobów

Ponieważ współżycie na równej stopie wszystkich użytkowników tej samej bazy może szybko stwarzać problemy, SQL Server integruje zarządcę zasobów, który pozwala przydzielić mniej lub więcej zasobów (pamięci RAM, procesory, dyski...) do określonej grupy użytkowników. Klasyczny przypadek to mieszanka użytkowników lubiących kokpity i wszelkiego rodzaju raporty (na przykład kontrolerów zarządzających), którzy zużywają dużo danych, penalizując produkcję danych od użytkowników dokonujących wpisów jednostkowych ...

Zarządzanie jakością danych

SQL Server integruje dwa odrębne moduły do zarządzania danymi jakościowymi w bazach danych:

Data Quality Services (DQS): pozwala interaktywnie i poprzez bazę wiedzy zarządzać jakością danych w relacyjnych i decyzyjnych bazach danych: czyszczenie, korekta, wzbogacanie, standaryzacja, deduplikacja itp.
Master Data Services (MDS): to rozwiązanie do zarządzania danymi głównymi programu SQL Server.

Zintegrowana usługa wysyłania e-maili

Database Mail to narzędzie do zarządzania wysyłką SQL Server i jest oparte na Service Broker. System ten umożliwia zdefiniowanie kilku profili wysyłania poczty (np. jeden dla aplikacji, a drugi dla administracji) z możliwą redundancją serwerów SMTP. Wysyłanie wiadomości e-mail jest w szczególności używane przez agenta SQL, aby czasami powiadamiać o powodzeniu, ale często o niepowodzeniu zaplanowanych zadań (na przykład, że nie można wykonać kopii zapasowej). Procedura msdb.dbo.sp_send_dbmail służy do wysyłania wiadomości e-mail, w tym z załącznikami, wynikiem zapytania, treścią w tekście lub wzbogaconą formą...

Narzędzia audytu

SQL Server jest bogaty w narzędzia do audytu, zarówno w zakresie bezpieczeństwa, ewolucji danych, jak i wydajności. Opiszemy niektóre z nich.

Audyt bezpieczeństwa (Audyt Bazy Danych)

Oprócz tego, że audyt C2 jest przestarzały i należy go zastąpić wspólnymi kryteriami (ISO 15408 ), SQL Server oferuje możliwość śledzenia wszystkiego, co związane z bezpieczeństwem poprzez audyt bazy danych, oraz wszelkich możliwych działań zarówno na poziomie serwera, jak i na poziomie konkretna baza danych.

System ten jest dopracowany do tego stopnia, że możliwe jest automatyczne zatrzymanie serwera w przypadku awarii systemu audytu, tak aby intruzi nie wykorzystali tego stanu awarii identyfikowalności do wykonywania niedozwolonych manewrów!

Ewolucja danych

SQL Server posiada dwa tryby pozwalające na automatyczne śledzenie ewolucji danych (INSERT, UPDATE, DELETE...):

ŚLEDZENIE ZMIAN: dostępne z edycji Standard, umożliwia wskazanie, które wiersze których tabel uległy zmianie w docelowym i konfigurowalnym okresie czasu.
CHANGE DATA CAPTURE (CDC): dostępne w wersji Enterprise, kopiuje informacje o aktualizacji do tabel roboczych ze wskazaniem daty i czasu dokonanych zmian.

CHANGE TRACKING wymusza skanowanie tabel w bazie danych, ale zmniejsza ogólną objętość, podczas gdy CDC nie wysyła zapytań do danych produkcyjnych w celu zwolnienia blokowania.

Te dwa moduły są zazwyczaj używane, jeden lub drugi, do zasilania w trybie różnicowym dużych hurtowni danych .

Przedstawienia

Ilość tego narzędzia jest tak duża, że wielu użytkowników je ignoruje, szukając gdzie indziej w narzędziach innych firm - często płatnych - za to, co już mają i za darmo na wyciągnięcie ręki.

Widok zarządzania danymi (DMV)

Są to dane systemowe prezentujące statystyki wykonania silnika SQL, które mogą być wykorzystywane w formie tabelarycznej przez pseudowidoki (w rzeczywistości najczęściej wewnętrzne funkcje zwracające tabelaryczny stan danych zgromadzonych w pamięci). Umożliwia to ustalenie różnych diagnoz, takich jak:

czas oczekiwania: sys.dm_os_wait_stats
aktualna aktywność na serwerze: sys.dm_exec_connections, sys.dm_exec_sessions, sys.dm_exec_requests;
wykorzystanie i fragmentacja indeksów: sys.dm_db_index_usage_stats, sys.dm_db_index_operational_stats, sys.dm_db_index_physical_stats;
szacunkowe brakujące indeksy: sys.dm_db_missing_index_details, sys.dm_db_missing_index_groups, sys.dm_db_missing_index_group_stats;
najdroższe przetwarzanie: sys.dm_exec_query_stats, sys.dm_exec_procedure_stats, sys.dm_exec_trigger_stats;
transakcje (i ich następstwa blokady): sys.dm_os_tran_ * (ponad 10 DMV) ...
zarządzanie pamięcią podręczną: sys.dm_os_memory_ * (ponad 10 DMV) ...
zarządzanie plikami: sys.dm_db_file_space_usage, sys.dm_db_log_space_usage, sys.dm_io_virtual_file_stats, sys.dm_os_volume_stats ...
wątki: sys.dm_os_threads, sys.dm_os_tasks, sys.dm_os_workers, sys.dm_os_dispatcher_pools, sys.dm_os_nodes ...

Raporty

W oparciu o DMV raporty wykorzystują i prezentują dane w sposób syntetyczny. Są one dostępne na różnych poziomach drzewa Eksploratora obiektów SQL Server Management Studio, głównie na poziomie serwera (wystąpienia) i podstawowym.

Liczniki wydajności

SQL Server publikuje wiele liczników wydajności, które można odpytywać bezpośrednio w SQL lub analizować za pomocą monitora wydajności (perfmon.exe). Te liczniki można odczytać w widoku sys.dm_os_performance_counters.

Alerty

SQL Server umożliwia konfigurowanie alertów o błędach lub metrykach przekraczających próg za pośrednictwem agenta SQL przez odpytywanie danych licznika wydajności lub za pośrednictwem usługi WMI (Instrumentacja zarządzania Windows). Ten rodzaj alertu pozwala np. być informowanym mailem o nasyceniu logów transakcji lub zapełnieniu dysków serwera.

Doradca dostrajania aparatu bazy danych

To narzędzie służy do wykonywania diagnostyki indeksowania, statystyk, partycjonowania lub tworzenia widoków indeksowanych, gdy jest dostarczane z partią zapytań.

Profilowanie SQL

Profiler SQL umożliwia wykrywanie zapytań przed i po wykonaniu, aby zapewnić metryki do celów analizy, głównie w celu poprawy wydajności (chociaż istnieje tryb „przestarzały”, aby wyświetlić przestarzały kod z niektórych aplikacji).

Dane te są zapisywane w tabeli lub pliku (z możliwością przeniesienia do tabeli) i muszą być następnie przeanalizowane. Różne szablony pozwalają na większy lub mniejszy poziom finezji skanowania.

Szablon „replay” umożliwia uchwycenie obciążenia i jego metryk, które mogą być odtwarzane w tych samych warunkach (symulacja różnych użytkowników równolegle) dla celów benchmarkingowych, w szczególności dla zmian w wersjach SQL Server, jak w sprzęcie. Aby odtworzyć i porównać, musisz użyć pakietu RML dostępnego bezpłatnie w witrynie Microsoft.

Rozszerzone wydarzenia (XE: eXtended Events)

Jako uzupełnienie i przyszłe zastąpienie profilera, XE umożliwia głębsze wtargnięcie niż profiler, ponieważ może skanować prawie wszystkie metryki operacji w pamięci, w wątkach lub dyskowych operacjach we/wy (na poziomie systemu). Jest to dodatek do tego, co już robi profiler.

Kolekcjoner danych

Ponieważ dane DMV są w zasadzie ulotne (istnieją tylko w pamięci, są tracone przy każdym restarcie) lub ograniczone (w pierścieniu pamięci), postanowiono stworzyć to narzędzie, które cyklicznie zapisuje różne dane z różnych źródeł (głównie z DMV) w celu uzyskania informacji zwrotnych za pośrednictwem określonych raportów. W tym celu należy utworzyć bazę danych kolekcji i uruchomić różne predefiniowane kolektory lub utworzyć własne kolektory.

Rozproszona powtórka

To urządzenie umożliwia odtworzenie aktywności serwera przechwyconego przez ślad SQL Profiler, symulując aktywność zbliżoną do rzeczywistości, za pomocą dystrybucji żądań na różne stacje robocze, a wszystko to zaaranżowane przez kontroler. Celem jest np. porównanie zgodności wykonania aplikacji na nowej wersji SQL Server lub podczas zmiany maszyny (poprawa wydajności).

Operacja

Microsoft SQL Server oferuje kilka funkcji:

Języki

W przypadku zapytań SQL Server używa języka SQL w jednym z najbardziej zgodnych z SQL dialektów. Microsoft za punkt honoru stawia sobie poprawianie z wersji na wersję niektórych przeszłych błędów odziedziczonych po silniku Sybase. Używany dialekt to T-SQL (Transact-SQL), implementacyjny język SQL, który obsługuje procedury składowane, funkcje zdefiniowane przez użytkownika lub UDF (funkcja zdefiniowana przez użytkownika) i wyzwalacze ( trigger ). Języki XQuery i XPath są używane na różnych poziomach do manipulowania danymi XML w zapytaniach SQL.

Do przesyłania danych SQL Server wykorzystuje format TDS (Tabular Data Stream), który został zaimplementowany w innych bazach danych (zwłaszcza w jego odpowiedniku Sybase ) i którego specyfikacje są publiczne. Dostępna jest implementacja open source klienta TDS, która stanowi podstawę klienta SQL Server dla projektu Mono : FreeTDS .

SQL Server wyposażony w dwa silniki bazodanowe, jeden relacyjny, a drugi decyzyjny, umożliwia wykonywanie zapytań w języku MDX lub DMX specyficznym dla analizy danych dla baz decyzyjnych.

Języki R i Python są zintegrowane od wersji 2016. Możliwe jest również tworzenie procedur SQL (UDF, procedury, wyzwalacze) przy użyciu języka .net, takiego jak VB for .net lub C#.

Linki do uczciwości

W silniku relacyjnym SQL Server możliwe jest definiowanie powiązań między tabelami poprzez ograniczenia deklaratywne w celu silnego zagwarantowania integralności danych między tabelą referencyjną a tabelą podrzędną. Te łącza integralności mogą służyć do modyfikowania lub usuwania połączonych linii w łańcuchu i obsługi reguł ewolucji typu NO ACTION, CASCADE, SET DEFAULT i SET NULL.

Transakcje

SQL Server to transakcyjny DBMS . Jest w stanie przygotowywać modyfikacje danych w bazie danych oraz zatwierdzać lub anulować je atomowo, czyli "wszystko albo nic". Gwarantuje to spójność i integralność informacji przechowywanych w bazie danych. Podczas transakcji zestawy danych zawierające wiersze danych modyfikowanych przez tę transakcję są blokowane. Pozostali użytkownicy muszą poczekać na zakończenie transakcji, aby móc je ponownie zmodyfikować. Z drugiej strony, współbieżni użytkownicy mogą opcjonalnie odczytywać dane, nawet jeśli zostały one zablokowane do modyfikacji przez inną transakcję, w zależności od wybranego poziomu izolacji.

Poziom izolacji i blokowanie

Blokady są wykonywane na poziomie wiersza, strony, rozszerzenia, tabeli lub bazy danych. SQL Server blokuje tylko potrzebne zasoby (domyślnie wiersze) i w razie potrzeby może blokować na wyższym poziomie (partycja lub tabela). Eliminuje to konieczność oczekiwania przez użytkowników na zakończenie transakcji w celu zaktualizowania wierszy danych, na które nie wpłynęła zmiana, i pomaga zmniejszyć ilość zużywanych zasobów. Blokowanie może być pesymistyczne — współbieżni użytkownicy nie będą mogli uzyskać dostępu do wierszy w tej transakcji — lub optymistyczne — w takim przypadku równolegli użytkownicy będą mogli uzyskać dostęp do najnowszej wersji wierszy.

Aby to zadziałało, SQL Server umożliwia kontrolowanie poziomu izolacji na sześć różnych sposobów. 4 poziomy przewidziane przez standard SQL

PRZECZYTAJ BEZPOŚREDNIO
CZYTAJ ZAANGAŻOWANO
POWTARZALNE CZYTAJ
SERIALIZOWANY

... który opiera się na pesymistycznym blokowaniu i dwóch „optymistycznych” poziomach:

MIGAWKA
PRZECZYTAJ ZATWIERDZONY ZDJĘCIE

Ponieważ poziom izolacji jest dynamiczny, może się zmienić podczas tej samej transakcji. Poziom izolacji READ COMMITTED SNAPSHOT działa podobnie jak Oracle lub PostGreSQL, zastępując poziom izolacji READ COMMITTED przez wersjonowanie wierszy w celu optymistycznego blokowania.

Rejestrowanie transakcji

SQL Server umożliwia odtwarzanie transakcji na trzy różne sposoby:

walidacja automatyczna: natychmiastowa walidacja lub anulowanie (w przypadku błędu) każdego uruchomionego zlecenia SQL (domyślnie);
tryb niejawny: połączenie rozpoczyna transakcję, a finalizacja (instrukcja COMMIT lub ROLLBACK) kończy transakcję, aby rozpocząć nową (SET IMPLICIT_TRANSACTIONS ON);
tryb jawny: inicjalizacja transakcji musi być wykonana za pomocą polecenia BEGIN TRANSACTION.

W przeciwieństwie do Oracle lub PostGreSQL, SQL Server obsługuje nawet zlecenia DDL (CREATE, ALTER, DROP ...) i DCL (GRANT, REVOKE ...).

Możliwe jest zdefiniowanie punktów kontrolnych transakcji (polecenie SAVE TRANSACTION) w celu umożliwienia częściowego anulowania (ROLLBACK ...).

Transakcje zapisywane są w dzienniku transakcji („dziennik REDO”), modyfikacje danych są natychmiast dostępne w pamięci i będą zapisywane w plikach danych podczas okresowych i asynchronicznych punktów kontrolnych. Możliwe jest jednak wymuszenie punktu kontrolnego za pomocą instrukcji CHECKPOINT.

Dziennik transakcji rejestruje dane w różny sposób i robi to na trzy różne sposoby:

Tryb pełny: wszystkie zmiany są zapisywane w logu. Zakończone transakcje z danymi zapisanymi na dysku zostaną usunięte z dziennika transakcji podczas tworzenia kopii zapasowych transakcyjnych.
Tryb rejestrowania zbiorczego (BULK LOGGED): minimalnie rejestruje powtarzalne operacje (na przykład wstawianie danych do plików, budowanie indeksów itp.). Tryb ten umożliwia przyspieszenie działania dziennika. Ale tak jak w trybie pełnym, dziennik będzie musiał zostać usunięty przez transakcyjną kopię zapasową.

Tryb prosty: Podobny tryb do trybu kronikowania zbiorczego, z tą różnicą, że dziennik jest automatycznie czyszczony w każdym punkcie kontrolnym.

W trybach pełnego i zbiorczego dziennika, dziennik transakcji musi być okresowo zapisywany ("dziennik REDO"), w przeciwnym razie będzie rósł w nieskończoność. A dzięki backupowi transakcyjnemu możliwe jest przywrócenie bazy danych w stanie, w jakim była w dowolnym momencie z dokładnością do sekundy lub do najbliższej transakcji (z pewnymi ograniczeniami dla trybu kronikowania zbiorczego).

Możliwe jest zagnieżdżanie transakcji, ale ponieważ pojęciem transakcji jest stan transakcji, nie jest możliwe dokonywanie w ten sposób częściowych anulowań. Istnieją jednak inne sposoby zachowania niektórych danych z wycofania transakcji, w tym poprzez zmienne tabeli.

SQL Server obsługuje transakcje rozproszone za pośrednictwem Koordynatora transakcji bazy danych (DTC), który implementuje „zatwierdzanie dwufazowe” zgodnie z modelem XA.

Akta

Bazy danych są fizycznie zawarte w plikach. Pliki mają zazwyczaj rozszerzenia:

MDF (Main Database File) dla pierwszego pliku danych, w tym kilka ważnych stron technicznych, w tym struktura przechowywania bazy danych
NDF (Next Database File) dla innych plików danych
LDF (Log Database File) dla plików dziennika transakcji

Pliki są podzielone na 8196 bajtowe bloki zwane stronami i zorganizowane w zestawy 8 kolejnych stron zwanych rozszerzeniami. Do wersji 2000 wiersze były ograniczone do maksymalnego rozmiaru 8060 bajtów (kolumny typu LOBs (obraz, tekst, ntext) nie są wliczane do tego limitu). Od wersji 2005 możliwe jest znaczne przekroczenie tego limitu i zwiększenie do 2 miliardów bajtów. Z drugiej strony użyteczny rozmiar strony to 8096 bajtów .

Bazy danych mogą działać tylko wtedy, gdy wszystkie pliki są obecne, ale odczyt jest możliwy podczas przywracania dzięki koncepcji przywracania fragmentarycznego.

Pliki danych są logicznie pogrupowane w bazie danych w grupy plików, które stanowią miejsce docelowe obiektów bazy danych (tabela, indeks itp.) Grupa plików jest uważana za jednostkę pamięci dowolnej tabeli lub indeksu i jeśli to grupa zawiera kilka plików, następnie SQL Server dystrybuuje dane we wszystkich plikach (odpowiednik RAID 0), przy czym operacje odczytu i zapisu są przeprowadzane równolegle w różnych plikach, pod bezpośrednią kontrolą silnika SQL Server storage. Kopie zapasowe tych plików i grup plików można tworzyć niezależnie, nawet jeśli istnieje logiczna współzależność między obiektami jednego pliku i drugiego (w szczególności integralność referencyjna).

bezpieczeństwo

Ze względu na aspekt wielobazowy, SQL Server ma dwa poziomy bezpieczeństwa: poziom serwera poprzez konta logowania oraz poziom podstawowy poprzez użytkowników SQL.

Począwszy od wersji 2005, w zasadach SQL Server zaszły znaczące zmiany w zakresie bezpieczeństwa.

Ponadto strategia bezpieczeństwa obejmuje również funkcjonalność serwera. Na przykład, procedura składowana xp_cmdshell służąca do uruchamiania poleceń systemowych jest wyłączona i tylko administrator DBA może ją ponownie włączyć . Podobnie, gdy tylko serwer zostanie otwarty, wszystkie pliki wszystkich baz danych są blokowane do odczytu i zapisu, aby zapobiec niepożądanemu dostępowi.

Konta serwisowe

Usługi uruchamiające każdą instancję SQL Server używają konta usługi (konta systemowego do uruchamiania usługi Windows). Te ostatnie muszą być wybierane z rozwagą, aby uniknąć możliwych naruszeń bezpieczeństwa na serwerze. Mogą to być: Usługa systemowa, Usługa lokalna (od Windows 2003), Usługa sieciowa (od Windows 2003), lokalne konto użytkownika Windows, konto użytkownika domeny. Zdecydowanie zaleca się zablokowanie uprawnień na kontach usług. W tym sensie, począwszy od wersji 2012, podczas instalacji dla każdej z usług SQL Server (SQL Server, SQL Agent, SSAS, SSIS, SSRS itp.) tworzone są określone konta z bardzo ograniczonymi uprawnieniami.

Zaloguj się do kont

Konto połączenia służy do łączenia się z wystąpieniem SQL Server. W przypadku tych kont należy zdefiniować uprawnienia mające zastosowanie na poziomie serwera. Minimalnym uprawnieniem jest uprawnienie „CONNECT”. Przykładem uprawnienia na poziomie serwera jest możliwość tworzenia dowolnej bazy danych. W tym przypadku jest to uprawnienie CREATE ANY DATABASE.

SQL Server domyślnie opiera się na systemie uwierzytelniania Windows ( Kerberos lub Native), który umożliwia dostęp do instancji SQL Server przez grupę Windows lub użytkownika Windows. Podczas łączenia się z bazą danych, użytkownik jest identyfikowany przez jego login Windows. W związku z tym nie musi podawać swojego hasła podczas fazy uwierzytelniania serwera.

Ponieważ zdarzają się przypadki, w których użytkownik nie może zostać zidentyfikowany przez jego login Windows (używanie GNU/Linux, strony WWW...) można zaimplementować metodę bezpośredniej identyfikacji do SQL Server. Musi to zrobić jawnie administrator instancji. Zakłada to utworzenie nazwy konta SQL i hasła, a dla tego ostatniego można modelować politykę potwierdzania hasła na zasadach systemu Windows.

Uzyskując dostęp do zasobu zewnętrznego, proces SQL Server działa na 3 różne sposoby: poprzez personifikację, gdy warstwa Windows jest poprawnie skonfigurowana, użytkownik może uzyskać dostęp do zasobów tylko poprzez SQL Server, do których byłby uprawniony, gdyby miał bezpośredni dostęp; przez konto usługi instancji, gdy użytkownik jest sysadminem i dla niektórych zadań; we wszystkich innych przypadkach nie umożliwia dostępu. W przypadku kont logowania SQL oraz dostępu do zasobu zewnętrznego (np. wysłanie faksu w procedurze składowanej) SQL Server dodaje możliwość tworzenia informacji logowania, które są w tym celu zarejestrowanymi kontami Windows. Dlatego użytkownicy SQL mogą być powiązani z tymi kontami Windows w celu uzyskania dostępu do zasobów zewnętrznych.

Użytkownik SQL

Konto logowania może być mapowane na użytkownika SQL w określonej bazie danych, w kilku lub we wszystkich. Nazwa konta logowania i nazwa użytkownika mogą się różnić (i lepiej, żeby tak było). Użytkownikowi SQL w bazie danych można nadać uprawnienia, które pozwolą mu na uruchamianie różnych poleceń na różnych obiektach. Uprawnienia można ustanowić bezpośrednio na obiekcie bazy danych lub na „kontenerze” takim jak schemat SQL lub cała baza danych.

Przykład nadawania różnych uprawnień na poziomie schematu SQL użytkownikowi o nazwie USR_BANQUE:

GRANT SELECT, INSERT, UPDATE, EXECUTE ON SCHEMA::FINANCE TO USR_BANQUE; Role

SQL Server ma wstępnie ustalone role na poziomie serwera oraz na poziomie podstawowym. Możliwe jest nawet tworzenie własnych ról (nowość na poziomie serwera od 2012 roku). Rola będąca zbiorem uprawnień przypisywanych kontu połączenia na poziomie serwera lub użytkownikowi SQL, gdy ta rola została utworzona dla określonej bazy danych.

Schemat SQL

Schemat będący kontenerem obiektów w bazie danych, SQL Server pozwala zdefiniować dowolną liczbę schematów SQL, aby zorganizować różne obiekty w bazie danych, a zwłaszcza zarządzać bezpieczeństwem na poziomie schematu w sposób proaktywny. W ten sposób tworzenie nowych obiektów, jeśli te obiekty zostaną umieszczone na właściwych diagramach, automatyzuje implementację zabezpieczeń przez dziedziczenie. W związku z tym nie ma żadnego polecenia, które można by przekazać z punktu widzenia zarządzania uprawnieniami, więc zabezpieczenia są natychmiast stosowane.

Wreszcie, w przeciwieństwie do niektórych RDBMS (na przykład Oracle), nie ma prywatności ani pomyłek między schematem SQL a jego właścicielem (użytkownikiem, który go utworzył), co na przykład znacznie ułatwia migrację „obiektu z jednego diagramu do drugiego i gwarantuje lepsze bezpieczeństwo.

Przykład migracji tabeli z jednego schematu SQL do drugiego:

ALTER SCHEMA BANQUE TRANSFER dbo.T_CLIENT; Szyfrowanie

SQL Server umożliwia szyfrowanie danych w tabeli za pomocą różnych algorytmów wbudowanych w SQL Server. Klucze szyfrujące i certyfikaty mogą być importowane z zewnętrznego pliku lub tworzone bezpośrednio w bazie danych, przy czym SQL Server jest własnym urzędem certyfikacji. Istnieje jednak trzecia możliwość, polegająca na wykorzystaniu elektronicznej skrzynki do generowania i przechowywania kluczy o nazwie HSM ( Hardware Security Module ), która ma zagwarantować nienaruszalność kluczy. Te urządzenia elektroniczne są umieszczane w sieci i ulegają samozniszczeniu w przypadku próby nielegalnego dostępu, fizycznego lub logicznego.

Kopia zapasowa bazy danych zawiera wszystkie klucze i certyfikaty, aby zapewnić szyfrowanie i deszyfrowanie. Ale przywrócenie bazy danych na innym serwerze niż oryginalny wymaga odszyfrowania, a następnie ponownego zaszyfrowania. Elementy składające się na architekturę szyfrowania są współzależne od tajnego klucza przechowywanego na poziomie instancji. Jednak, aby kontynuować szyfrowanie lub deszyfrowanie, konieczne jest otwarcie klucza poprzez posiadanie certyfikatu lub podanie hasła.

SQL Server umożliwia szyfrowanie danych tabeli lub szyfrowanie kodu procedur.

Wreszcie, SQL Server umożliwia szyfrowanie przechowywania danych, a nie danych przez TDE (Transparent Data Encryption). Pliki (dzienniki transakcji, dane tabel i indeksy) są szyfrowane podczas fizycznego zapisywania na dyskach i odszyfrowywane podczas fizycznego odczytu z dysków do pamięci. Dane są więc wyświetlane w pamięci, co pozwala zachować wydajność przy szyfrowaniu całej bazy danych.

Wersja 2016 dodała szyfrowanie end-to-end, to znaczy, że deszyfrowanie kontrolowane przez serwer może odbywać się na poziomie aplikacji, a nie w bazie danych.

Obiekty bazy danych

Stoły

Tabele to obiekty, które faktycznie zawierają dane w bazie danych. Są dwojakiego rodzaju:

Tabele systemowe zawierają informacje pozwalające na poprawne funkcjonowanie bazy danych. Domyślnie w SQL Server 2000 nie można ich modyfikować. W SQL Server 2005 pojawiają się nowe tabele systemowe, ale nie są one dostępne dla użytkowników. Aby zawsze mieć dostęp do tych informacji, zespół programistów dodał dużą liczbę widoków systemowych. Zastępują one stare tabele systemowe i niektóre polecenia DBCC. Dodano również dużą liczbę nowych widoków systemowych. W SQL Server wszystkie obiekty systemowe są częścią schematu sys i są przechowywane w bazie danych o nazwie SystemResource.
Te tabele użytkowników zawierają dane użytkownika.

Tabela może zawierać do 30 000 kolumn, których suma rozmiarów nie przekracza 2 miliardów bajtów . Kolumny typów BLOBów (VARCHAR (max), NVARCHAR (max), VARBINARY (max)) przechowywane poza wierszem.

Tabele mogą mieć klucz podstawowy . Klucz podstawowy jest zawsze indeksowany . Inne ograniczenia są również dostępne na poziomie tabeli , takie jak: ograniczenia unikatowe (indeksowane automatycznie), wartości domyślne, ograniczenia sprawdzające, klucze obce .

Wyświetlenia

Widok to nazwane zapytanie bazy danych. Jest odpytywany w taki sam sposób jak tabela.

Dane zwracane przez widok są rekonstruowane z danych zawartych w tabelach przy każdym wywołaniu widoku (z wyjątkiem widoków indeksowanych, które przechowują wyniki). SQL Server zastępuje nazwy widoków w zapytaniu, które zostaną wykonane, ich definicjami, a wynikowe zapytanie jest kompilowane, optymalizowane i wykonywane przez silnik. Uzyskany w ten sposób skompilowany plan zapytania jest następnie przechowywany w celu umożliwienia ponownego wykorzystania podczas następnego połączenia. Ponieważ SQL Server ma optymalizację semantyczną oprócz optymalizacji statystycznej, gałęzie sprzężeń, które są niepotrzebne do działania widoku, są systematycznie wycinane w celu optymalizacji, jeśli jest to możliwe.

W określonych warunkach możliwa jest aktualizacja wartości w widoku. Przykładami tych warunków są:

Widok wielu tabel może modyfikować tylko jedną z tych tabel.
Jeśli widok zawiera funkcje, używa "DISTINCT" lub "GROUP BY", modyfikacja nie zadziała.
Nie może być transformacji danych kolumny (operacja, wyrażenie, wywołanie funkcji itp.)
Widok posiada wyzwalacz typu INSTEAD OF (w tym drugim przypadku logika modyfikacji to ta pokazana w kodzie wyzwalacza).

SQL Server implementuje specjalne typy widoków, które są widokami partycjonowanymi i widokami indeksowanymi (odpowiednik synchronicznych widoków zmaterializowanych Oracle).

Indeks

Indeksowanie w SQL jest dostarczane na cztery sposoby: indeksowanie relacyjne, indeksowanie pionowe, indeksowanie tekstowe i indeksowanie specyficzne dla niektórych typów obiektów.

Indeksowanie relacyjne

Odnosi się do pełnej treści kolumn. Wewnętrzny mechanizm tych wskaźników opiera się na pojęciu drzewa zrównoważonego (B-Tree).

Istnieją dwa rodzaje indeksów :

Indeksy klastrowe (INDEKS CLUSTERED)
INDEKS NIESKLASTROWANY

Może istnieć tylko jeden indeks klastrowy na tabelę, ponieważ ten typ indeksu jest w rzeczywistości „sortowaniem” tabeli. Wiersze tabeli są zatem logicznie posortowane w kolejności indeksu klastrowego, co wyjaśnia, dlaczego może być tylko jeden. Innymi słowy, skoro indeks klastrowy jest b-drzewo , liście indeksu są bezpośrednio wierszami w tabeli.

Indeks nieklastrowy nie odzwierciedla fizycznej kolejności wierszy w tabeli.

Oba typy indeksów są zbudowane z klucza składającego się z kilku kolumn. Pozwalają szybko znaleźć fragment danych z wszystkich lub części kolumn tego klucza.

Tak więc, jeśli weźmiemy pod uwagę klucz (A, B, C), możemy przeszukiwać pola (A), (A, B) i (A, B, C). Pola są rozpatrywane w kolejności klucza; dlatego nie jest możliwe wyszukiwanie np. (B, C) lub (C) za pomocą tego indeksu. Z drugiej strony wyszukiwanie według (A, C) z łatwością skorzysta z indeksowania kolumny A.

Tabela może zawierać 999 indeksów. Każdy indeks może zawierać 16 kolumn, których suma rozmiarów nie przekracza 900 bajtów . Indeksy zawierają oprócz danych kolumn indeksu zakładkę do danych tabeli. Może to być wskaźnik do linii lub jeden z kluczy indeksu klastrowego (w języku angielskim: clusterised index ).

Optymalizator programu SQL Server wybiera indeks do użycia. To nie może być za pomocą utworzonego indeksu ponieważ koszty poszukiwań przez tego indeksu może być większa niż czytanie całą tabelę. Aby dokonać tych wyborów, optymalizator wykorzystuje między innymi statystyki tabeli oraz obecność lub brak ograniczeń. Można jednak wymusić na optymalizatorze użycie indeksu z klauzulą WITH (nazwa_indeksu), jednak taka praktyka nie jest zalecana.

Indeks jest zorganizowany w drzewo-b . Pozwala to na szybsze sklasyfikowanie informacji, niż gdyby trzeba było je wstawić do tabeli sekwencyjnej. Ponieważ ten układ wymaga dużej ilości pamięci, wersja 2005 programu SQL Server umożliwia oddzielenie danych wyszukiwania i przetwarzania danych zintegrowanych z indeksem.

SQL Server umożliwia tworzenie kolumn obliczeniowych, które, jeśli są trwałe (PERSISTED), mogą być indeksowane.

Nie ma innej struktury indeksu niż b-tree . Na przykład nie ma indeksu bitmapy , co ułatwiłoby wyszukiwanie w polach o mało zróżnicowanej zawartości, ale wystarczy utworzyć tabelę referencyjną symulującą indeks bitmapy. Podobnie nie ma indeksu skrótu. Ale po prostu utwórz obliczoną kolumnę mieszającą (funkcja CHECKSUM) i zindeksuj ją, aby uzyskać podobny efekt. Wreszcie nie jest możliwe utworzenie indeksu na wyrażeniu, ale po prostu utwórz kolumnę obliczeniową z takim wyrażeniem i zindeksuj ją, aby uzyskać ten sam efekt.

Indeksowanie pionowe

SQL Server umożliwia tworzenie tzw. indeksów „pionowych” (indeks kolumnowy) na zbiorze kolumn umożliwiających dostęp do wszystkich kolumn indeksowanych na tym samym planie. W rzeczywistości w wielokolumnowym indeksie relacyjnym kolejność każdej kolumny w kluczu indeksu dopuszcza tylko określone kombinacje wyszukiwań. Nie dotyczy to indeksowania pionowego, które pozwala na wszystkie kombinacje wyszukiwań. Na przykład za pomocą klawisza (A, B, C) można wyszukiwać zarówno po (A), (B) lub (C), ale także po dowolnej kombinacji dwóch lub trzech kolumn (A, B ), ( B, A), (A, C), (C, A), (B, C), (C, B), (A, B, C), (A, C, B), (B , A, C), (B, C, A), (C, A, B), (C, B, A).

W wersji 2012 indeksy te są tylko do odczytu i dlatego są przeznaczone głównie dla baz danych typu DataWarehouse na potrzeby analizy biznesowej. W wersji 2014 indeksy te można modyfikować.

Indeksowanie tekstowe i semantyczne

Indeksowanie tekstowe (a właściwie katalog indeksowania) umożliwia wyszukiwanie w postaci zwykłego tekstu („full text”, zgodnego ze standardem SQL z predykatem CONTAINS) lub na sposób Google z predykatem FREETEXT. Dozwolone jest wyszukiwanie różnych słów w tym samym dokumencie (tekst kolumn tabeli lub dokument elektroniczny przechowywany w tabeli) według równości, początku, bliskości, ale także odmiennej formy (koniugacja, liczba mnoga, żeńska itp.) , synonimizacja, rozszerzenie (szczególnie dla akronimów) ... a także z wagami względnymi (funkcja tabeli CONTAINSTABLE i FREETEXTTABLE).

Indeksowanie tekstowe może odnosić się do ciągów znaków lub kolumn typu XML, ale także do plików elektronicznych, niezależnie od tego, czy są przechowywane bezpośrednio w tabeli (w BLOB), czy jako plik za pośrednictwem FILESTREAM lub w FILETABLE . SQL Server rozpoznaje natywnie ponad 50 formatów plików i umożliwia dodawanie wielu innych typów plików elektronicznych za pomocą „ifilter”.

SQL Server obsługuje większość języków i umożliwia wielojęzyczne wyszukiwanie połączone. W tym celu można utworzyć listy „czarnych słów” dla każdego z indeksowanych języków. Ponadto SQL Server indeksuje wszystkie słowa bez wyjątku, w tym czarne słowa, gdzie niektóre RDBMS są ograniczone do słów o minimalnej długości (na przykład MySQL) lub wykluczają czarne słowa (na przykład PostgreSQL). Wreszcie, jeśli wielkość liter nie jest istotna dla indeksowania tekstu, użytkownik może wybrać, czy chce indeksowania tekstu uwzględniającego znaki diakrytyczne, takie jak akcenty lub ligatury. Indeksowanie tekstu umożliwia również wyszukiwanie metadanych w plikach elektronicznych, np. autora pliku Word.

W wersji 2012 SQL Server dodał indeksowanie semantyczne, które, jak sama nazwa wskazuje, pozwala na wyszukiwanie semantyczne. Funkcjonalność ta wymaga dodania bazy danych przetwarzania semantycznego przeznaczonej do tworzenia statystyk indeksowanych tekstów. badania semantyczne skupiają się na znaczeniu dokumentów. Umożliwia to automatyczne wyodrębnianie znaczników, wykrywanie powiązanych treści i hierarchiczne nawigowanie po dokumentach o treści podobnej do tekstu referencyjnego. Praktycznym przykładem jest sprawdzenie indeksu podobieństwa dokumentów w celu zidentyfikowania w partii życiorysów, które najlepiej pasują do opisu stanowiska.

Indeksowanie obiektów

SQL Server umożliwia indeksowanie określonych typów danych obiektów, takich jak XML (cztery typy indeksów) lub przestrzenne (typy geometry i geography ). Ponieważ istnieje możliwość dodawania własnych obiektów, pod warunkiem opisania ich w postaci .NET, możliwe jest również ich indeksowanie, pod warunkiem ustalenia, że są one uporządkowane bajtowo w organizacji ich przechowywania.

Możliwe jest zdefiniowanie procedur składowanych zakodowanych w Transact-SQL oraz w .NET (SQL CLR). Procedura składowana to seria instrukcji, które mogą modyfikować dane bazy danych i hermetyzować transakcje oraz zwracać jedną lub więcej wartości.

Procedury przechowywane w SQL Server mogą przyjmować jako parametry i/lub zwracać liczby całkowite, ciągi znaków, daty, kursory, tabele, tabele wirtualne i dowolny inny typ zdefiniowany w SQL Server domyślnie lub przez użytkowników.

Główne zalety korzystania z procedur składowanych to:

złożona transakcja przetwarzania (kilka transakcji może być powiązanych lub nakładanych)
zwiększone bezpieczeństwo (możliwość depersonalizacji kodu procedury)
lepsze ponowne wykorzystanie skompilowanych planów
możliwość zarządzania zależnościami między kodem SQL a obiektami silnika.

Wyzwalacze

Wyzwalaczem jest przechowywany kod, który jest wykonywany po (PO) lub zamiast (ZAMIAST) określonej akcji (wstawianie, modyfikowanie, usuwanie) w tabeli lub widoku. Możliwe jest zakodowanie tylu wyzwalaczy AFTER, ile chcesz w jednej akcji (WSTAW, AKTUALIZUJ lub USUŃ) tej samej tabeli lub widoku. W przypadku wielu wyzwalaczy w tej samej akcji tej samej tabeli, kolejność wyzwalaczy jest dowolna, z wyjątkiem pierwszych 3, które można narzucić. Wyzwalacze INSTEAD OF muszą być unikalne dla każdego zdarzenia/tabeli.

Tworzone są dwie wirtualne tabele do obsługi wstawionych, zmodyfikowanych lub usuniętych danych: wstawione (do wstawienia i modyfikacji) i usunięte (do usunięcia i modyfikacji). Nie ma możliwości modyfikowania zawartości tych wirtualnych tabel. W przeciwieństwie do tego, SQL Server umożliwia modyfikowanie tabeli docelowej wyzwalacza, na przykład wycofywanie niejawnej transakcji, która wywołała wyzwalacz, poprzez wycofanie transakcji (ROLLBACK).

Konkretny wyzwalacz INSTEAD OF może być użyty, gdy ktoś chce przechwycić zdarzenie, aby nie dopuścić do jego wystąpienia, ale zakodować inną akcję. Ten typ wyzwalacza może być używany na przykład w kontekście mapowania relacyjnego / obiektowego, gdzie manipulujemy widokami złożonymi z wielu złączeń, gdzie chcemy, aby użytkownik mógł dokonywać aktualizacji (INSERT, UPDATE, DELETE ) bezpośrednio na tych widokach. W takim przypadku dane INSERT, UPDATE lub DELETE są dzielone dla każdego ze stołów w grze, a seria zleceń specyficznych dla każdego stołu, które mają być wyświetlane, jest uruchamiana poprzez transakcję.

Od SQL Server 2005 dodano możliwość tworzenia wyzwalaczy DDL. Te wyzwalacze DDL działają po wystąpieniu zdarzeń typu CREATE, ALTER lub DROP, a zdarzenia te mogą zostać przechwycone na poziomie serwera (na przykład CREATE DATABASE) lub na poziomie bazy danych (na przykład DROP TABLE). Aby uzyskać szczegółowe informacje o przechwyconym zdarzeniu, pakiet danych XML (uzyskany przez funkcję EVENTDATA()) podaje wiele szczegółów dotyczących pochodzenia i charakteru zdarzenia. Istnieje nawet zapytanie SQL, które go wywołało.

Określony typ wyzwalacza służy do przechwytywania połączeń z serwerem (wyzwalacz FOR LOGON).

Funkcje (UDF: funkcja zdefiniowana przez użytkownika)

Od SQL Server 2000 możliwe jest tworzenie funkcji w Transact SQL, a od 2005 w .net (SQL CLR).

Te funkcje są 3 rodzaje:

Skalar: kilka instrukcji zwraca następnie wartość prostego typu
Tabela z wieloma instrukcjami: kilka instrukcji, a następnie zwraca tabelę
Tabela online (widok parametryczny): instrukcja typu SELECT zwraca tabelę

Istnieją jednak ograniczenia dotyczące funkcjonalności, których można użyć w treści funkcji. Nie jest możliwe użycie funkcji niedeterministycznych, a funkcja nie może trwale lub trwale modyfikować danych lub parametrów systemowych. Zabrania to na przykład użycia INSERT w tabeli lub użycia funkcji GETDATE().

Dodano SQL Server 2005:

możliwość tworzenia funkcji agregacji (obliczenia statystyczne) za pomocą CLR, czyli kodowania przy użyciu języka platformy .net;
specyficzne funkcje związane z partycjonowaniem danych (FUNKCJA PARTYCJI).

W przeciwieństwie do procedury składowanej funkcja nie może zawierać:

zlecenie określające granice transakcji (BEGIN TRANSACTION, COMMIT, ROLLBACK)
kolejność aktualizacji danych (INSERT, UPDATE, DELETE, SELECT), z wyjątkiem funkcji wieloinstrukcyjnych i tylko dla tabeli zwrotów.
kod niedeterministyczny (bariera częściowo zniesiona w SQL Server 2005, funkcje takie jak GETDATE są dozwolone, ale RAND jest nadal zabronione)
dynamiczny SQL
kursor
wywołanie procedury składowanej

Ma to sens, ponieważ celem funkcji jest przede wszystkim użycie w zapytaniu.

Funkcje tabel wbudowanych, działające jako widoki sparametryzowane, mogą aktualizować tabele bazowe.

Aspekty przedmiotowe

Od wersji 2005 SQL Server zawiera aspekty obiektowe, w tym:

możliwość realizacji złożonych typów ustrukturyzowanych za pomocą języka zewnętrznego, takiego jak .net i wykorzystania ich zarówno jako kolumny tabeli, jak i typu zmiennej w procedurze (procedura, wyzwalacz lub funkcja).
Typ XML można przeszukiwać za pomocą XQuery / XPath i indeksować. Serwer SQL integrujący parser XML i procedury obsługi strumieni XML.
możliwość wygenerowania kanału XML z wyniku zapytania SELECT zamiast „zestawu danych”.
typ hierarchyId (pojawił się w wersji 2008) ułatwiający strukturyzację danych hierarchicznych (drzewa).
prawdziwy GIS (Geographic Information System - pojawił się w wersji 2008) wraz z wejściem typów GEOMETRIA i GEOGRAPHY do standardu OGC z interfejsem w postaci WKT (Well Known Text) lub WKB (Well Known Binary) i wyposażony w wiele metody manipulacji.
implementacja usług internetowych, które można tworzyć bezpośrednio w RDBMS.
przechowywanie plików pod wyłączną lub częściową odpowiedzialnością RDBMS (FILESTREAM) i ich manipulację transakcyjną umożliwiającą spójną kopię zapasową danych relacyjnych i nierelacyjnych (bardzo przydatne na przykład dla EDM) - @ Od 2008.
przejrzysta reprezentacja w postaci tabel (FILETABLE) drzew przechowywania plików (katalogów i plików) systemu operacyjnego Windows oraz ich manipulacja za pomocą zapytań SQL - od 2012 r.

Przybory

SQL Server Management Studio (SSMS)

To narzędzie jest dołączone do płatnych wersji SQL Server. Umożliwia łączenie i administrowanie różnymi silnikami SQL Server (SSRS, SSIS, SSAS i silnikiem relacyjnym). Pozwala to silnikowi relacyjnemu na tworzenie skryptów TSQL, z możliwością grupowania ich wszystkich w ramach rozwiązania (jak w Visual Studio). Można je również zapisać za pomocą Visual SourceSafe lub Team Foundation Server. W przypadku wersji Express (bezpłatnej) uproszczone narzędzie SQL Server Management Studio Express Edition (SSMSE) jest dostępne bezpłatnie.

Przed 2005 rokiem narzędzie to było podzielone na dwa moduły: „Enterprise Manager” i „Query Analyzer”.

SQL Server Business Intelligence Development Studio / SQL Server Data Tools

Podobnie jak SSMS, to narzędzie jest dostarczane z płatnymi wersjami SQL Server. Jest to nakładka ogólnego narzędzia programistycznego Microsoft Visual Studio, odpowiednia do tworzenia projektów BI (Analysis Services, Integration Services lub Reporting Services). Wszystkie te projekty można znaleźć w grupie „Projekty Business Intelligence”.

Profiler (generator profili)

To narzędzie pozwala rejestrować aktywność bazy danych. Umożliwia administratorom sprawdzanie czasochłonnych pozycji i zapytań w bazie danych. Opcjonalnie możliwe jest odtworzenie przechwytywania na serwerze testowym. Jest stopniowo zastępowany przez system „rozszerzonych wydarzeń” (eXtended Events).

Usługi i silniki

W rzeczywistości MS SQL Server to pakiet składający się z pięciu głównych usług:

Aparat relacyjny (OLTP) o nazwie SQL Server;
Silnik decyzyjny (OLAP) o nazwie SSAS ( SQL Server Analysis Services ), w tym silnik pamięci masowej dla kostek, algorytmy wiercenia ( eksploracja danych ) i różne narzędzia BI (Business Intelligence);
ETL (Extract Transform and Load) zwany SSIS (SQL Server Integration Services) przeznaczony do ustawiania logiki przepływu danych, w szczególności do zasilania hurtowni danych ;
Narzędzie do generowania raportów o nazwie SSRS (SQL Server Reporting Services) umożliwiające tworzenie raportów w różnych formach oraz wykorzystanie zasobów silnika decyzyjnego (bazy danych "resportServer...") zarówno do przechowywania raportów, jak i ich ukrywania. dane w celu wykonania „rozgrzewki”;
System planowania pracy i zarządzania alertami o nazwie Agent SQL, który również korzysta z usług silnika SQL (baza msdb).

Inne usługi są z nim związane dla określonych potrzeb:

Program uruchamiający demona filtru pełnotekstowego SQL : do ładowania niektórych dokumentów zewnętrznych (tezaurus i ifilter biblioteki dll) wymaganych do wyszukiwania „zwykłego tekstu”
Przeglądarka SQL : usługa dostarczania usług SQL mająca na celu ułatwienie wyszukiwania usług SQL w sieci
MS DTC (Data Transaction Coordinator): rozproszony koordynator transakcji, używany do aktualizacji zatwierdzania dwufazowego (2PC) łączących różne serwery transakcyjne, niekoniecznie ze świata Microsoft ( XA , X/Open , TIP, WS...)

SQL Server zawiera również wiele narzędzi programistycznych:

dla transakcyjnych baz danych, poprzez SQL Server Management Studio (SSMS)
do baz decyzyjnych, eksploracji danych, raportowania i ETL, poprzez nakładkę Visual Studio o nazwie BIDS ( Business Intelligence Development Studio ), a następnie SQL Server Data Tools od wersji 2012.

Dostępnych jest ponad sto narzędzi peryferyjnych podczas instalacji (SQL Profiler, Database Tuning Advisor, Data Collector itp.) lub bezpośrednio w witrynie firmy Microsoft (SQLdiag, SQLioSim, SQL Server Best Practices Analyzer...) lub za pośrednictwem witryna społeczności opensource Codeplex (RML Utilities, PAL, Open DBDiff ...).

Wystąpienie SQL Server to instalacja niektórych lub wszystkich usług SQL Server na komputerze z systemem Windows i może obsługiwać wiele baz danych. Ten sam system operacyjny obsługujący do 50 różnych instancji (co nie jest zalecane w środowisku produkcyjnym).

SQL Server 2014 nie jest ograniczony liczbą rdzeni czy pamięci RAM, a jedynie pojemnością systemu Windows. Baza danych jest ograniczona do 524 peta bajtów (Po), ale ponieważ SQL Server może obsługiwać wiele baz danych i 32760 baz w tej samej instancji, całkowita pojemność systemu może teoretycznie obsłużyć 17166240 danych Po . Przy 50 instancjach na tej samej maszynie ta teoretyczna granica zostaje przesunięta z powrotem do 858 312 000 Po ...

SQL Server istnieje w różnych edycjach: CE (Compact Edition - wbudowane rozwiązanie dla smartfonów), Express (kilka wersji - darmowe), Web (w trybie SPLA: Service Provider License Agreement - z hostami internetowymi), Standard, BI i Enterprise - dla produkcja. Wersja Developper (odpowiednik wersji Enterprise) jest przeznaczona do programowania.

Serwer SQL

To jest silnik bazy danych. Każde wystąpienie SQL Server ma odpowiednią usługę Windows SQL Server. Usługa ta znajduje się w usługach Windows pod nazwami MSSQL $Instance_name dla instancji nazwanych i MSSQLServer dla instancji domyślnej.

Każda utworzona instancja ma początkowo od 4 do 6 systemowych baz danych. Silnik SQL Server opiera się na głównej bazie danych systemu, która zawiera definicje innych baz danych.

Pozostałe podstawy systemu to:

msdb : używany przez agenta SQL Server, replikację , usługi transformacji danych, usługi integracyjne ...
model : używany jako model do tworzenia nowych baz danych. Każda nowa baza danych jest jej kopią.
tempdb : Tymczasowa baza danych, służy do przechowywania tymczasowych danych tworzonych przez użytkowników lub generowanych przez kursory, tworzenie indeksów, podczas dużego sortowania. W SQL Server 2005 zawiera również wersje rekordów wygenerowane przez poziomy izolacji SNAPSHOT, zawartość wstawionych i usuniętych pseudotabeli używanych w wyzwalaczach oraz te wygenerowane przez operacje indeksu wbudowanego. Ta systemowa baza danych jest systematycznie opróżniana po uruchomieniu usługi SQL Server.
dystrybucja : obecne tylko w kontekście replikacji, jeśli serwer pełni rolę dystrybutora.
mssqlsystemresource : obecny tylko z SQL Server 2005. Nie jest bezpośrednio dostępny i zawiera wszystkie obiekty systemowe (z wyjątkiem tabel).

Agent serwera SQL

To jest agent konserwacji dla instancji SQL Server. Każde wystąpienie SQL Server ma odpowiednią usługę Windows Sql Server Agent. Tę usługę można znaleźć w usługach Windows pod nazwami SQLAgent $ InstanceName dla instancji nazwanych i SQLAgent dla instancji domyślnej. Aparat SQL Server Agent jest oparty na bazie danych msdb . Ten silnik służy do zarządzania kopiami zapasowymi, planami konserwacji, zaplanowanymi pracami, monitorowaniem baz danych, alertami administracyjnymi...

Agent pełni również rolę monitorowania usługi SQL Server i automatycznego wyzwalania jej ponownego uruchomienia w przypadku nieoczekiwanego zamknięcia.

Wyszukiwanie pełnotekstowe

Indeks pełnotekstowy / wyszukiwarka.

Koordynator transakcji rozproszonych

Znany również jako MS DTC, służy do zarządzania transakcjami rozproszonymi. Oznacza to transakcje między kilkoma serwerami transakcyjnymi SQL Server, między serwerem SQL Server a innymi serwerami baz danych, między kilkoma różnymi źródłami danych, niezależnie od tego, czy są to silniki baz danych, czy proste komponenty.

Usługi powiadamiania

Usługa, która pojawiła się w SQL Server 2000 umożliwia regularne odpytywanie bazy danych i, w zależności od tych żądań, powiadamianie grup subskrybujących te zdarzenia. Ta usługa nie jest już oferowana jako standard od SQL Server 2008. SQL Server o nazwie kodowej Denali oferuje jednak podobną funkcjonalność w usługach SQL Server Reporting Services i Sharepoint 2010, znanych jako Alerting.

Podejmowanie decyzji

Platforma analizy biznesowej SQL Server składa się z ETL , który pojawił się wraz z SQL 7, wielowymiarowego silnika, który pojawił się również z SQL 7, oraz z silnika raportowania dodanego w 2004 r. w SQL 2000. Z SQL Server w wersji 2005, studio programistyczne dla Business Intelligence został zintegrowany z Visual Studio 2005: BI Development Studio.

Mapowanie

SQL Server 2008 obsługuje nowy typ danych pierwotnych: typ geograficzny, w którym występuje rzutowanie (szerokości i długości geograficzne) oraz typ geometryczny bez rzutowania w celu manipulowania danymi związanymi z punktami, liniami i wielokątami. Pierwszym oprogramowaniem obsługującym SQL Server jest oprogramowanie Manifold System (en) oraz MapInfo, GeoMedia.

Usługi integracyjne (SSIS: SQL Server Integration Services)

Usługa, która pojawiła się w SQL Server 2005, jest zamiennikiem Data Transformation Services (DTS). ETL został całkowicie przebudowany i konkuruje z innymi profesjonalnymi narzędziami ETL na rynku.

Celem jest integracja firmy poprzez jej dane. SQL Server Integration Services (SSIS) to kompleksowa platforma integracji danych. SSIS nie jest prostym ETL. Jest to kompletna platforma integracji danych, oferująca szereg graficznych narzędzi programistycznych i zarządzających, usług, obiektów programowalnych oraz interfejsów API ( interfejsów programowania aplikacji). SSIS zawiera silnik przepływu pracy, który obsługuje złożoną logikę i może być używany do szerokiej gamy operacji konserwacji bazy danych i zaawansowanych operacji przesyłania danych. Architektura SSIS składa się zasadniczo z dwóch części: silnika wykonawczego transformacji danych, który zarządza przepływem sterowania pakietem, oraz silnika przepływu danych lub silnika potoku transformacji danych, który zarządza przepływem danych ze źródeł danych poprzez transformacje, i do celów docelowych.

Usługi analityczne

SQL Server Analysis Services (SSAS) to usługa, która pojawiła się w SQL Server 7, znana wówczas jako Usługi OLAP . Umożliwia generowanie kostek OLAP , danych zagregowanych i wielowymiarowych. Umożliwia również implementację algorytmów Data Mining .

Usługi raportowania

Ta usługa, która pojawiła się w SQL Server 2000, jest silnikiem generowania raportów. Składają się na nią dwie usługi sieciowe, jedna umożliwiająca administrowanie, druga generowanie, subskrypcja, renderowanie raportów. Rendery są wykonywane w Excelu, PDF, HTML i Word (od wersji 2008).

Edycje

Edycja dla centrum danych

Pojawił się z SQL Server 2008 R2, będzie obsługiwać do 256 procesorów logicznych, a także nieograniczoną wirtualizację i nieograniczoną ilość pamięci.

Wersja Enterprise

Obsługuje nieograniczoną liczbę procesorów i rozmiar pamięci RAM (ograniczony przez system operacyjny). Dostępna jest wersja 32-bitowa (x86) i 2 wersje 64-bitowe (ia64 i x64, x64 tylko dla SQL Server 2005) i nie ma limitu rozmiaru baz danych. Można go zainstalować tylko w wersji serwerowej Windows . Obejmuje wszystkie funkcje aparatu, w tym funkcje wysokiej dostępności. Funkcje analizy biznesowej obejmują generowanie raportów z usługami Reporting Services, korzystanie z kostek OLAP w usługach Analysis Services, a także przesyłanie danych z usługami Integration Services (SQL Server 2005) lub Data Transformation Services (SQL Server 7) i 2000). . Może również działać w klastrze do 8 węzłów.

Wersja deweloperska

To jest wersja dla programistów, która ma taką samą funkcjonalność jak wersja Enterprise. Licencja zawiera jednak ograniczenia dotyczące jej używania. Licencja zakazuje pracy w produkcji. Jest przeznaczony wyłącznie do użytku testowego. Jednak nie ma ograniczeń co do dostępnych produktów.

Wersja standardowa

Obsługuje do 4 procesorów i nieograniczony rozmiar pamięci RAM (ograniczony przez system operacyjny). Dostępna jest wersja 32-bitowa (x86) i 2 wersje 64-bitowe (ia64 i x64, obie tylko dla SQL Server 2005) i nie ma limitu rozmiaru baz danych. Jednak SQL Server 2005 pozwala na jego instalację na desktopowych systemach Windows . Obejmuje wszystkie funkcje aparatu z wyjątkiem niektórych funkcji wysokiej dostępności. Funkcjonalności dla business intelligence obejmują: generowanie raportów za pomocą Reporting Services (w przypadku SQL Server 2000 ten składnik musi być ładowany osobno) po wykorzystanie kostek OLAP z Analysis Services, w tym transfer danych za pomocą Integration Services (SQL Server 2005) lub usług transformacji danych (SQL Server 7 i 2000). Może również działać w klastrze do 2 węzłów od wersji 2005.

Edycja dla grup roboczych

Nowa edycja pojawiła się wraz z SQL Server 2005. Obsługuje do 2 procesorów i 3 GB pamięci RAM. Na razie jest dostępny tylko w wersji 32-bitowej i nie ma limitu rozmiaru baz danych. Zawiera większość funkcjonalności silnika, w tym możliwość uczestniczenia w replikacji jako edytor. Dołączona funkcja analizy biznesowej jest ograniczona do raportowania za pomocą usług Reporting Services. Ta edycja znika wraz z wersją 2012 programu SQL Server.

Edycja internetowa

Nowa edycja pojawiła się w SQL Server 2008. Zbliżona do wersji Standard pod względem funkcjonalności, nie obsługuje jednak ani mirroringu, ani komponentów decyzyjnych SQL Server. Jego licencja pozwala jedynie na używanie go jako serwera bazy danych dla witryny internetowej lub usługi. Jest dostępny jako miesięczna subskrypcja, szczególnie dla gospodarzy, a także bardziej konwencjonalnie dostępny do zakupu.

Edycja osobista

Istniejące Edition z SQL Server 7 i SQL Server 2000. Obsługuje maksymalnie dwa procesory i 2 GB of RAM . Dostępne tylko w wersji 32-bitowej i nie ma limitu rozmiaru baz danych. Zawiera większość funkcjonalności silnika, w tym możliwość uczestniczenia w replikacji jako subskrybent. Nie posiada funkcjonalności do analizy biznesowej. Jeśli chodzi o licencjonowanie, posiadanie licencji SQL Server Client Access License (CAL) jest wystarczające do korzystania z tej edycji.

Wewnętrzna baza danych systemu Windows / wbudowany serwer SQL

Podobnie jak w wersji SQL Server Express, z usuniętymi ograniczeniami dotyczącymi pamięci, liczby procesorów i rozmiaru bazy danych, jednak może być używany tylko lokalnie i może obsługiwać tylko bazy danych aplikacji firmy Microsoft. Korzystają z niej produkty takie jak Sharepoint lub niektóre funkcje Windows Active Directory.

MSDE/Express Edition

Microsoft opublikował bezpłatną edycję składającą się wyłącznie z relacyjnego silnika bazy danych, ograniczoną do 4 GB miejsca na dysku (10 GB w wersji 2008 R2). Można go jednak wykorzystać do stworzenia do 32760 różnych baz danych, czyli ponad 320 TB danych z wersji 2008 R2.

Edycja kompaktowa

Poprzednio nazywane: Pocket PC / Mobile / Everywhere Edition. Jest to lekka edycja odpowiednia do użytku na urządzeniach PDA lub smartfonach wyposażonych w system Windows Mobile . Wersja Compact (wersja 3.5), która zastępuje edycje Mobile, jest otwarta na klasyczne stacje robocze z systemem Windows . Ta edycja może uczestniczyć w replikacji jako subskrybent.

Licencje

SQL Server obsługuje 5 systemów licencjonowania:

Licencja na użytkownika : SQL Server może korzystać ze wszystkich procesorów na serwerze (do wersji 2008 R2, maksymalnie 16 (wersja Standard) lub 20 (wersja Enterprise) rdzeni w wersji 2012), ale jest ograniczone do określonej liczby użytkowników. Każda osoba fizyczna korzystająca z aplikacji korzystającej z SQL Server jest uznawana za użytkownika bazy danych.
Licencja na urządzenie (od SQL Server 2005): SQL Server może korzystać ze wszystkich procesorów na serwerze (do wersji 2008 R2, maksymalnie 16 (wersja Standard) lub 20 (wersja Enterprise) rdzeni w wersji 2012), ale jest ograniczona do liczby urządzeń określony. Każde urządzenie fizyczne, które bezpośrednio lub pośrednio uzyskuje dostęp do SQL Server, jest uważane za użytkownika bazy danych
Licencja na procesor : SQL Server wykorzystuje liczbę procesorów określoną w licencji i może akceptować dowolną liczbę użytkowników. Ten tryb licencji jest ważny do wydania 2008 R2 i później znika.
Licencja na rdzeń : Musisz kupić tyle licencji na rdzeń, ile jest rdzeni na serwerze lub w maszynie wirtualnej. Liczba użytkowników jest nieograniczona. Ten tryb licencji jest ważny tylko od edycji 2012. Cena jest liczona od par rdzeni i zaczyna się od 4.
Licencja SPLA : w przypadku edycji internetowej jest to licencja na wynajem. Niska cena pozwala konkurować z „darmowymi” RDBMS-ami takimi jak MySQL czy PostGreSQL.

Typy licencji, które mogą być brane pod uwagę w przypadku korzystania z programu SQL Server za interfejsem internetowym, to licencja na procesor lub rdzeń lub tryb SPLA ograniczony do edycji internetowej.

Dodatkowe narzędzia integrujące serwer SQL

Warto wspomnieć o niektórych narzędziach związanych z SQL Server...

BizTalk

Rozwiązanie firmy Microsoft do integracji danych EAI (Enterprise Application Integration) i B2B (business-to-business).

Sharepoint

Globalne rozwiązanie do zarządzania wiedzą biznesową (KB - Baza wiedzy) umożliwiające zarządzanie treścią (za pośrednictwem strony internetowej i forum) oraz udostępnianymi dokumentami elektronicznymi, wyszukiwanie informacji, a także możliwość tworzenia formularzy i prezentacji statystyk decyzyjnych takich jak dashboardy, kluczowe wskaźniki ( KPI — kluczowy wskaźnik wydajności) ...

Dynamika

To jest ERP (Enterprise Resource Planning) firmy Microsoft.

TFS (serwer Team Foundation Server)

To kuźnia oprogramowania pozwalająca na zarządzanie kodem źródłowym, kompilacje, monitorowanie zasobów, planowanie zadań...

SCOM (System Center Operations Manager)

System do scentralizowanego zarządzania, nadzoru i utrzymania systemu informatycznego (serwery, komputery PC, peryferia online) oraz infrastruktury sieciowej.

WSUS (usługi aktualizacji systemu Windows Server)

Usługa dystrybucji dla Microsoft OS i aplikacji do aktualizacji różnych maszyn Windows w parku komputerowym (wykorzystuje darmową wersję Express)

Dodatkowe narzędzia, które mogą korzystać z serwera SQL

StreamInsight

StreamInsight to platforma przeznaczona do tworzenia i wdrażania na dużą skalę aplikacji do kompleksowego przetwarzania zdarzeń (CEP). Opiera się na dwóch podstawowych komponentach: StreamInsight Server do zarządzania przepływami danych oraz StreamInsight Framework do tworzenia interfejsów.

EDF używa StreamInsight do zarządzania incydentami dla całego swojego sprzętu IT.

SQL Server i Big Data

Firma Microsoft, bardzo zaangażowana w ten ruch, szczególnie za pośrednictwem platformy Hadoop, oferuje różne rozwiązania do zarządzania dużymi danymi.

HDInsight (Hadoop)

HDInsight to rozwiązanie Hadoop oferowane w chmurze platformy Azure lub lokalnie (do celów programistycznych).

Narzędzia językowe Map / Reduce, Hive, Sqoop i Pig ułatwiają przesyłanie i manipulowanie danymi z SQL Server do Hadoop. Zestaw SDK oparty na platformie .NET umożliwia pisanie partii przy użyciu programu Visual Studio. Lokalna instalacja usługi Hadoop przeznaczona dla deweloperów jest dostępna za pośrednictwem serwera HDInsight Server.

Polibaza

Polybase to narzędzie przeznaczone do atakowania różnych źródeł danych relacyjnych (SQL Server), decyzyjnych (SQL Server Parallel Data Warehouse - PDW) lub big data, poprzez różne procesy, takie jak Map Reduce lub MPP (Massive Parallel Processive)

Bibliografia

„ Edycje i obsługiwane funkcje SQL Server 2019 (15.x) ” ,4 listopada 2019 r.(dostęp 23 grudnia 2020 r. )
(w) „ Docker: Zainstaluj kontenery dla programu SQL Server w systemie Linux — SQL Server ” w witrynie microsoft.com (dostęp 4 września 2020 r . ) .
FreeTDS
Domyślnie w SQL Server 2014 nie jest możliwe uruchomienie polecenia systemowego z SQL Server
https://technet.microsoft.com/fr-fr/library/ms175609%28v=sql.90%29.aspx
„ Usługi przetwarzania w chmurze / Microsoft Azure ” w witrynie microsoft.com (dostęp 4 września 2020 r . ) .
„ Usługi przetwarzania w chmurze / Microsoft Azure ” w witrynie microsoft.com (dostęp 4 września 2020 r . ) .
SQL Server 2012, co nowego?