SQL Server 2016 – Neuerungen / New Features
Die nächste SQL Server-Version steht schon so gut wie in den Startlöchern. Zeit also, einen kurzen Blick auf die Neuerungen zu werfen, die diese mit sich bringen wird. Bevor es losgeht an dieser Stelle kurz der Hinweis, dass dieser Artikel mit der aktuellen CTP 3.3 geschrieben wurde. Zurzeit ist SQL Server noch nicht final, als geplanter Zeitraum der Veröffentlichung wird derzeit das 1. Halbjahr 2016 gehandelt. Des Weiteren sei erwähnt, dass es sich hierbei um Neuerungen rund um die relationale Datenbank handelt. Andere Produkte aus dem SQL Server-Umfeld wie SSRS, SSIS und SSAS finden keine Berücksichtigung. Und leider kann auch nicht jede "neue Schraube" angesprochen werden, der Autor musste bei den vielen großen und kleinen Neuigkeiten eine Auswahl treffen.
Dynamic Data Masking
Dynamic Data Masking ermöglicht es, Daten in sensiblen Spalten für bestimmte User ohne entsprechende Berechtigung mit einer Maske zu versehen und damit zu anonymisieren. In der Definition einer Tabelle ist dabei verankert, wie diese Maskierung auszusehen hat.
CREATE TABLE dbo.Mitarbeiter ( ID INT IDENTITY PRIMARY KEY, Name NVARCHAR(100) MASKED WITH (FUNCTION = 'partial(1,"-",2)') NULL, Gehalt DECIMAL(12,2) MASKED WITH (FUNCTION = 'random(1, 1999)') NOT NULL, Telefon NVARCHAR(20) MASKED WITH (FUNCTION = 'default()') NULL, EMail NVARCHAR(100) MASKED WITH (FUNCTION = 'email()') NULL );
Das Recht, Daten unmaskiert sehen zu dürfen, heißt UNMASK und gilt für alle Tabellen in der Datenbank. Hat der User das Recht nicht, so sieht die Rückgabe wie in Abb.1 aus.
Video
Row Level Security im MS SQL Server
Unter dem Name Row Level Security bietet der SQL Server einen Weg per eigener Funktion Zeilen vor der Rückgabe zu filtern und zu verhindern, dass Änderungen vorgenommen werden können, so dass Zeilen aus diesem Filter fallen. Die Funktion kann dabei auf Werte von Spalten zugreifen und wenn sie eine Zeile liefert, gilt der Filter als passiert.
CREATE TABLE [dbo].[Mitarbeiter] ( [ID] [int] PRIMARY KEY CLUSTERED IDENTITY(1,1) NOT NULL, [Name] [varchar](100) NULL, [Address] [varchar](256) NULL, [Email] [varchar](256) NULL, [Gehalt] [decimal](18,2) NOT NULL, [SecurityDescriptor] [varchar](128) NULL ); CREATE FUNCTION dbo.fn_AccessFilter(@RoleOrUsername AS sysname) RETURNS TABLE WITH SCHEMABINDING -- Muss angegeben werden AS RETURN ( SELECT 1 'Granted' WHERE -- Rückgabe einer Zeile => Datenzeile passiert Filter! USER_NAME() = @RoleOrUsername OR IS_MEMBER(ISNULL(@RoleOrUsername, 'PUBLIC')) = 1 );
Die Tabelle und die Funktion werden in sogenannten Security Policies zusammengebracht. Eine solche Policy besteht aus einem oder mehreren Filter Predicates (für SELECT) und Block Predicates (für INSERT, DELETE und UPDATE)
CREATE SECURITY POLICY SecurityFilter ADD FILTER PREDICATE dbo.fn_AccessFilter([SecurityDescriptor]) ON [dbo].[Mitarbeiter], ADD BLOCK PREDICATE dbo.fn_AccessFilter([SecurityDescriptor]) ON [dbo].[Mitarbeiter] AFTER INSERT, ADD BLOCK PREDICATE dbo.fn_AccessFilter([SecurityDescriptor]) ON [dbo].[Mitarbeiter] BEFORE DELETE, ADD BLOCK PREDICATE dbo.fn_AccessFilter([SecurityDescriptor]) ON [dbo].[Mitarbeiter] BEFORE UPDATE;
Es können insgesamt mehrere Security Policies zur gleichen Zeit existieren und aktiv sein, jedoch darf es für jede Tabelle nur eine aktive Security Policy geben.
MS SQL Server - Always Encrypted
Bei der Arbeit mit sensiblen Daten reicht ein einfacher Schutz ab einem bestimmten Punkt nicht mehr aus. Das Always Encrypted Feature sorgt daher für die notwendige Verschlüsselung. Und das sowohl auf den Massenspeichern des Servers als auch während der Übermittlung zum Client. Dies kann ohne große Infrastruktur realisiert werden, indem ein "Column Master Key" erzeugt wird, der zum Verschlüsseln des oder der "Column Encryption Key(s)" verwendet wird. Diese wiederrum werden zum Verschlüsseln der Werte in den Spalten benutzt.
-- Column Master Key anlegen CREATE COLUMN MASTER KEY DEFINITION [MasterColumnKey] WITH ( KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE', KEY_PATH = N'CurrentUser/My/74E3849307794DF85FD7D7832C86AE9F4FC8DF5A' ); -- Column EncryptionKey anlegen, dabei den Column Master Key verwenden CREATE COLUMN ENCRYPTION KEY [ColumnEncryptionKey] WITH VALUES ( COLUMN MASTER KEY DEFINITION = [MasterColumnKey], ALGORITHM = 'RSA_OAEP', ENCRYPTED_VALUE = 0x016E00000163007500720072006..(gekürzt)..FD1F3FF ); -- Tabelle mit zwei verschlüsselten Spalten anlegen CREATE TABLE [dbo].[Secrets]( [ID] INT IDENTITY PRIMARY KEY, [User] NVARCHAR(50) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = ColumnEncryptionKey) NOT NULL, [Secret] NVARCHAR(50) ENCRYPTED WITH (ENCRYPTION_TYPE = RANDOMIZED, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = ColumnEncryptionKey) NOT NULL, -- Weitere, weniger sensible und daher unverschlüsselte Spalten [Unwichtig] NVARCHAR(100) NULL );
Besonders interessant wird Always Encrypted durch zwei besondere Punkte: Zum einen funktionieren Filter, Sortierungen, etc. trotz Verschlüsselung. Zum anderen sind die Änderungen in einer .NET-Anwendung (ab 4.6) um dieses Feature zu nutzen minimal – gerade mal der Connection String muss um das "Column Encryption Key"-Token erweitert werden.
Data Source=.; Initial Catalog=dotnetconsulting; Integrated Security=true; Column Encryption Setting=enabled;
Wichtig ist allerdings, dass variable Werte – nach gutem Best Practice – als Parameter übergeben werden und nicht direkt in den T-SQL-String eingefügt werden.
JSON Support
Neben XML wird SQL Server 2016 nun zusätzlich auch JSON direkt unterstützen. Dies umfasst sowohl das Erzeugen von JSON aus vorhandenen Daten als auch den Zugriff auf Informationen via JSON Path.
Analog zu dem FOR XML-Zusatz bei Abfragen lautet die neue Syntax FOR JSON.
SELECT name, database_id, source_database_id FROM sys.databases ORDER BY database_id FOR JSON AUTO;
Auf Wunsch kann auch der Name für das erzeugte Array mit angegeben und NULL-Werte, die sonst ausgelassen werden, eingefügt werden.
SELECT name, database_id, source_database_id
FROM sys.databases ORDER BY database_id FOR JSON PATH, ROOT('Databases'), INCLUDE_NULL_VALUES;Mittels zweier neuer Funktionen ist der Zugriff via JSON Path auf Informationen aus JSON realisiert worden. Ein einzelner Wert wird durch die JSON_VALUE()-Funktion ermittelt.
SELECT JSON_VALUE(@json, 'strict$.Databases[0].name'); Ganze Arrays werden mittels OPENJSON() als Tabelle geliefert. SELECT * FROM OPENJSON (@json, '$.Databases') WITH ( name VARCHAR(200), database_id INT, source_database_id INT ) AS Databases ORDER BY name;
Für Daten im JSON-Format wird es keinen eigenen Datentyp geben, wie es für XML der Fall ist. D. h. sollen in einer Tabellenspalte Daten im JSON-Format gespeichert werden, muss dessen formelle Korrektheit mit einer CHECK CONSTRAINT und der neuen ISJSON()-Funktion sichergestellt werden.
CREATE TABLE dbo.Servers ( ID INT PRIMARY KEY IDENTITY(1,1), Servername SYSNAME, DatabasesJSON VARCHAR(4000), CONSTRAINT chkJSON_Databases CHECK (ISJSON(DatabasesJSON)=1), Details AS (CONVERT(VARCHAR(128), JSON_VALUE(DatabasesJSON, 'lax.$.Databases[0].name'))) );
Einen speziellen JSON-Index gibt es ebenfalls nicht. Wird ein Index benötigt, so kann dieser auf eine entsprechende berechnete Spalte gesetzt werden.
CREATE INDEX idx_Details1 ON dbo.Servers(Details);
Temporal Table
Mit Temporal Tables, auch System Versioned Tables genannt, werden Änderungen an den Inhalten einer Tabelle automatisch historisiert. Immer wenn Änderungen vorgenommen werden, wird dafür der ursprüngliche Inhalt in eine Historisierungstabelle verschoben, die auf Wunsch bei Aktivierung der Historisierung automatisch erzeugt wird.
CREATE TABLE dbo.Werte ( ID INT IDENTITY(1,1) NOT NULL, Wert1 NVARCHAR(10) NULL, Wert2 NVARCHAR(10) NULL, StartTime datetime2(7) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL, EndTime datetime2(7) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL, PERIOD FOR SYSTEM_TIME(StartTime, EndTime), CONSTRAINT Werte_PK PRIMARY KEY (ID) ) WITH ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Werte_History) );
Beide Tabellen haben automatisch den gleichen Aufbau – inklusive der beiden Spalten für die Gültigkeit einer Zeile (StartTime & EndTime).
SQL Server 2016 unterstützt außerdem für Abfragen den ANSI SQL 2011 AS OF {Datum}-Zusatz mit dessen Hilfe die Daten in der Tabelle so vorliegen, wie sie zu dem angegebenen Zeitpunkt vorlagen – einer kleinen Zeitreise in Ihren Daten steht also nichts mehr im Wege.
SELECT * FROM dbo.Werte FOR SYSTEM_TIME AS OF '2016-01-25 14:00:00';
Wird der Zusatz nicht angegeben, so wird automatisch der aktuelle Zeitpunkt angenommen. Zum Zweck der Wartung kann die Versionierung (zeitweise) deaktiviert oder die Historisierungstabelle gewechselt werden.
SESSION_CONTEXT
SQL Server 2008 hat bereits eine CONTEXT_INFO eingeführt, die es erlaubt, einen Wert Session weit verfügbar zu machen. Der Nachteil war, dass es nur ein einzelner Wert war und es schwer wurde, mehrere Informationen unterzubringen. Mit SESSION_CONTEXT steht nun ein Schlüssel/Wert Dictionary für jede Session zur Verfügung, der dieses Manko ausmerzt.
-- Festlegen des Wertes mit dem Schlüssel „Department“ EXEC sp_set_session_context @key = 'Department', @value='Sales'; -- Abruf des Wertes SELECT SESSION_CONTEXT(N'Department'); -- Löschen des Wertes EXEC sp_set_session_context @key = 'Department', @value=NULL;
SESSION_CONTEXT wird beim Ende oder Zurücksetzen der Verbindung automatisch gelöscht und ist nur innerhalb einer Session verfügbar.
DROP IF EXISTS
Klein aber fein ist diese T-SQL-Erweiterung, die es gestattet, nur dann Elemente zu löschen, wenn diese auch wirklich existieren und die keinen Fehler mehr erzeugt, wenn dies nicht der Fall ist. Erlaubt ist dabei der Zusatz IF EXISTS für alle Objekte die via DROP gelöscht werden können.
DROP IF EXISTS DATABASE MyDb; DROP IF EXISTS TABLE MyTable;
Damit sind die Zeiten vorbei, im Zweifelsfall vor jedem DROP eine Prüfung einzubauen.
Live Statistics
Live Statistics bietet die Möglichkeit, Abfragen noch während der Ausführung zu begutachten und zu analysieren, welche Schritte wie ausgeführt werden. Im SQL Server Management Studio kann diese Funktion bei Abfragen und über den Activity Monitor genutzt werden.
Die notwendige Datensammlung wird für die aktuelle Session mit SET STATISTICS XML ON oder SET STATISTICS PROFILE ON (was das SQL Server Management Studio automatisch erledigt) und serverweit mit dem query_post_execution_showplan Extended Event aktiviert.
Eine Aktivierung bedeutet aber mitunter einen Performanceverlust von ca. 30 Prozent, so dass von einem prophylaktischen Einsatz abzuraten ist.
Multiple TempDB Database Files
Mit SQL Server 2016 wird die TempDB bei der Installation automatisch so konfiguriert, dass die Anzahl der Datendateien gleich der Anzahl der aktiven CPU-Kerne entspricht. Dies ermöglicht es, der Database Engine die temporäre Datenbank besonders performant zu betreiben.
Stretch Database
Wer aus Kostengründen Speicherplatz in Datenbanken sparen möchte, könnte auf die Idee kommen, historische Daten auf günstigere, wenn auch vielleicht langsamere Speicher zu verschieben. Mit Stretch Database kann SQL Server 2016 historische Daten in eine Azure SQL Datenbank auslagern. Dabei befinden sich die Daten nicht in einem "Cold Storage" sondern stehen in einem "Warm Storage" für Abfragen bereit. Welche Daten dabei als historisch zu betrachten sind, kann durch eine eigene Funktion festgelegt werden.
Query Store
Das Query Store Feature ermöglicht es, Administratoren einen Einblick in Ausführungspläne und Perfomancedaten von ausgeführten Abfragen zu werfen und diese zu analysieren. Damit vereinfacht sich die Suche nach Ursachen für Performanceeinbrüche.
Mit dieser Anweisung ALTER DATABASE {Datenbank} SET QUERY_STORE = ON; wird die dazu nötige Datensammlung eingeschaltet und im SQL Server Management Studio befindet sich unterhalb der Datenbank ein weiterer Eintrag für den Zugriff auf die Auswertungstools.
In-Memory-Verbesserungen
Die mit SQL Server 2014 eingeführte In-Memory-Technologie erfährt mit 2016 einige interessante Verbesserungen: Persistierte Memory Optimized Tables können nun 2 TB groß sein, LOBs (varbinary(max), [n]varchar(max)) werden unterstützt, DML Trigger können erstellt werden, der Tabellenentwurf im SQL Server Management Studio ist nun möglich und – besonders wichtig – bestehende In-Memory-Tabellen können verändert werden!
ALTER TABLE dbo.InMemoryTable ALTER INDEX idxHash REBUILD WITH (BUCKET_COUNT = 900000);
Des Weiteren sind mit dieser Version Fremdschlüssel, Check/Unique Constraints und Transparent Data Encryption (TDE) für In-Memory-Tabellen erlaubt.
Programmierer die mit dem Entity Framework arbeiten, freuen sich sicherlich über die neue Unterstützung von Multiple Active Result Sets (MARS) für Memory Optimized Tables.
Clustered Columnstore Index
R goes SQL Server
countdown <- function(from)Laut einem Zitat von John M. Chambers, dem Entwickler der Statistiksoftware S und deren Nachfolger R, kann man R leicht verstehen, wenn man folgendes bedenkt [1].
{
print(from)
while(from!=0)
{
Sys.sleep(1)
from <- from - 1
print(from)
}
}
countdown(5)
Alles, was existiert, ist ein Objekt.
Alles, was passiert, ist ein Funktionsaufruf.
PolyBase
- Wikipedia: R (Programmiersprache)
- Microsoft: PolyBase
