Über unsMediaKontaktImpressum
09. Februar 2016

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.

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

Für Tabellen, die über einen Clustered Columnstore-Index verfügen, können nun ab SQL Server 2016 weitere (nonclustered) B-Tree-Indizes definiert werden. Solche einfachen Secondary-Indizes sorgen dann bei geeigneten Abfragen für weitere Performance und machen den Clustered Columnstore-Index für große Tabellen noch attraktiver.

Außerdem ist der Tuple Mover nun in der Lage, die beim Einfügen von Daten entstehenden Deltastores bei Reorganisation einheitlich zusammenzufassen, auch wenn dabei mehrere Threads an der Arbeit beteiligt sind.

R goes SQL Server

Die beliebte Programmiersprache R für statistische Rechnungen wurde mit SQL Server 2016 eingebaut. Eine kleine Countdown-Funktion sieht in R so aus:

countdown <- function(from)
{
  print(from)
  while(from!=0)
  {
    Sys.sleep(1)
    from <- from - 1
    print(from)
  }
}
countdown(5)

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].

Alles, was existiert, ist ein Objekt.
Alles, was passiert, ist ein Funktionsaufruf.

PolyBase

Mittels PolyBase können T-SQL Abfragen verwendet werden, um semistrukturierte Daten in Hadoop oder Azure Blob Storage abzufragen. Damit werden ETL-Tools oder andere Programme zum Importieren solcher Daten überflüssig [2].

Quellen
  1. Wikipedia: R (Programmiersprache)
  2. Microsoft: PolyBase
Autor

Thorsten Kansy

Thorsten Kansy ist als unabhängiger Software-Architekt, -Entwickler und -Trainer international tätig. Er spricht regelmäßig auf Konferenzen und schreibt Bücher und Artikel rund um die Entwicklung komplexer Anwendungen.
>> Weiterlesen
Bücher des Autors:

botMessage_toctoc_comments_9210