SQL Server 2016 CTP 2: Das ist neu

Next Version SQL
Kommentare

Seit Anfang Juni steht die Microsoft SQL Server 2016 CTP 2 nun zum Download bereit. Im Gegensatz zur aktuellen Version 2014 wird es diesmal eine ganze Reihe von neuen Features geben, die für Entwickler von Interesse sind. Zeit also, einen Blick auf den aktuellen Entwicklungsstand zu werfen, auch wenn bei Weitem noch nicht alle Features eingebaut sind. Dieser Artikel stellt einige spannende Funktionen für Entwickler vor, die bereits jetzt funktionieren.

Nachdem der knapp 3 GB große Download (ISO/CAB/Azure) bewältigt wurde, steht der Installation (fast) nichts mehr im Wege, sofern mindestens Windows Server 2012 oder Windows 8 und 2 GB freier Festplattenplatz sowie sinnvollerweise 4 (besser 8) GB Hauptspeicher vorhanden sind, und: Oracles JRE (Java Runtime Environment) 7 Update 51 oder höher. Wird für die Installation „Database Engine Services/PolyBase Query Service for External Data“ abgewählt, entfällt diese für Microsoft neuartige Anforderung.

Was ist neu?

Kurz: Verdammt vieles! SQL Server 2016 wird ein mächtiges Release. Großes Thema ist zum Beispiel die Mobile-BI-Strategie, die u. a. mit dem Kauf von Datazen und der Unterstützung unterschiedlicher Geräte, sei es Apple-iOS-, Google-Android- oder Windows-betriebene Hardware, verfolgt wird. Auch die Sicherheit wird mit den drei neuen Features „Row Level Security“, „Dynamic Data Masking“ und „Always Encrypted“ verstärkt. Zudem soll die Verfügbarkeit des SQL Server durch die Ausweitung auf DTC und SSIS verbessert werden. Zusätzlich wird die Skalierbarkeit ausgebessert, indem die tempdb automatisch mit einer Datei pro CPU-Kern betrieben wird – ein Performancekniff, der schon lange Gültigkeit hat, der bis dato jedoch manuell administriert werden musste. Und natürlich wird Windows Server 2016 unterstützt werden.

Ebenfalls ein großes Thema (im wahrsten Sinne des Wortes) ist die Anbindung an Big-Data-Quellen (z. B. Hadoop oder SQL Azure Federation). Weitere Themen sind die Integration der statistischen Sprache R, Stretch Database, Real-Time Operational Analytics und In-Memory OLTP. Eine vollständige Liste finden Sie hier.

Da es  sich insgesamt um recht viele unterschiedliche Themen handelt, widmet sich dieser Artikel lediglich einer Auswahl der (für den Autor) interessanten Entwicklerthemen mit Features, die bereits jetzt funktionsfähig sind.

Ein erster Blick

Nach der Installation bietet sich unter den neu installierten Diensten sowie im SQL Server Management Studio (SSMS) der gewohnte Anblick. Es sind keine neuen Dienste hinzugekommen und der Unterschied zu den Vorgängerversionen im SSMS offenbart sich erst im zweiten Blick. Nur die interne Versionsnummer der SQL Server 2016 CTP 2 (13.0.200) zeigt an, dass das Programm mit der neuen Version verbunden ist.

Am Rande ein wenig erstaunlich: Die Versionsnummer 13 wird, bis dato zumindest, nicht übersprungen, sondern ist die interne Version des SQL Server 2016 – hoffen wir, dass das kein schlechtes Omen ist.

Erst ein Blick in die Struktur einer Datenbank zeigt im Objekt-Explorer einige neue Elemente. So gibt es nun die Möglichkeit, auf Big-Data-Quellen (z. B. Hadoop oder SQL Azure Federation) als „External Resources“ zuzugreifen und deren unstrukturierte Daten als „External Table“ verfügbar zu machen. Die Skripte der hier vorgestellten Beispiele finden sich auf dem Block des Autors.

Integrierter JSON-Support

Bereits die CTP 2 verfügt über einen Teil der geplanten Unterstützung für JSON (JavaScript Object Notation). Ähnlich wie für XML mit der FOR XML-Klausel bei SELECT-Statements, gibt es nun auch eine FOR JSONKlausel, die die Ausgabe im JSON-Format liefert. So liefert z. B. die Abfrage

SELECT * FROM INFORMATION_SCHEMA.TABLES FOR JSON PATH;

diese Rückgabe vom Server (für eine bessere Lesbarkeit ein wenig (manuell) mit Zeilenumbrüchen formatiert):

[{"TABLE_CATALOG":"dotnetconsulting",
  "TABLE_SCHEMA":"dbo",
  "TABLE_NAME":"Person_InMemory",
  "TABLE_TYPE":"BASE TABLE"},
 {"TABLE_CATALOG":"dotnetconsulting",
  "TABLE_SCHEMA":"dbo",
  "TABLE_NAME":"Person_InMemory_Schema_only",
  "TABLE_TYPE":"BASE TABLE"},
 {"TABLE_CATALOG":"dotnetconsulting",
  "TABLE_SCHEMA":"dbo",
  "TABLE_NAME":"Person_Disk",
  "TABLE_TYPE":"BASE TABLE"}]

Die Zeichenkette ist also ordnungsgemäß formatiert und kann direkt von einem entsprechenden (Web-)Client verwendet werden. Mit dem Zusatz PATH kann außerdem noch der Name der umschließenden Arrays festgelegt werden. Die Abfrage

SELECT * FROM INFORMATION_SCHEMA.TABLES FOR JSON AUTO, ROOT('Tables');

benennt das zurückgelieferte Array passenderweise Tables.

Besondere Zeichen wie CR/LF, Tab, Quotes etc. werden korrekt berücksichtigt. Allerdings sind keine Indizes auf JSON-Inhalte (wie sie z. B. für XML-Inhalte existieren) und wohl auch kein dedizierter JSON-Datentyp für Tabellenspalten geplant. Ab CTP 3 soll es aber möglich sein, dediziert auf einzelne Werte, die im JSON-Format vorliegen, zuzugreifen.

Row-Level-Security

Im Zuge der neuen Sicherheitsfeatures präsentiert CPT 2 nun ein Konzept für zeilenbasierte Sicherheit, das heißt anhand einer frei definierbaren Funktion (hier FILTER PREDICATE genannt) kann festgelegt werden, welche Zeilen einer Tabelle/Sicht zurückliefert werden sollen und welche nicht. Eine solche Funktion, die eine Inline Table-Valued-Funktion sein muss, wird für jede in Frage kommende Zeile aufgerufen und kann auf anderen Funktionen (z. B. GETDATE(), USER_NAME() etc.) und Spalten der Tabelle/Sicht zugreifen, um zu entscheiden, ob die Zeile zurückgeliefert werden soll oder nicht.

Das Verbindungsglied stellt eine so genannte Security-Policy dar, die bei Bedarf auch einfach deaktiviert werden kann. Listing 1 zeigt ein komplettes Beispiel, die neuen und wichtigen Elemente sind fett formatiert.

Listing 1

-- Zum Test drei unterschiedliche User anlegen
CREATE USER Thorsten WITHOUT LOGIN;
CREATE USER Ursula WITHOUT LOGIN;
CREATE USER Steffen WITHOUT LOGIN;

-- Tabelle erstellen und befüllen
CREATE TABLE dbo.Secrets
(
  OrderID INT IDENTITY(1,1),
  [User] SYSNAME,
  [Secret] NVARCHAR(200)
);

INSERT dbo.Secrets 
([User], [Secret])
VALUES 
('Ursula', 'Ursulas Geheimnis 1'),
('Ursula', 'Ursulas Geheimnis 2'),
('Steffen', 'Steffen heiratet Doro'),
('Steffen', 'Steffens Geheimnis 2');

-- Zugriff auf die Tabelle für Jeden
GRANT SELECT ON dbo.Secrets TO Public;
GO

-- Funktion für Sicherheitsfilter erstellen
CREATE FUNCTION dbo.fn_SecurityFilter(@User AS sysname)
  RETURNS TABLE
  WITH SCHEMABINDING
AS
RETURN
(
  -- Zugriff gestatten wenn der ausführende User "Thorsten" ist oder der 
  -- Wert des Parameters, also der Wert der User-Spalte
  SELECT 1 'Allowed' WHERE @User = USER_NAME() OR USER_NAME() = 'Thorsten'
)
GO

CREATE SECURITY POLICY SecretFilter
ADD FILTER PREDICATE dbo.fn_SecurityFilter([User]) 
ON dbo.Secrets
WITH (STATE = ON);

Werden nun SELECT-Anweisungen mit den unterschiedlichen Usern ausgeführt, greift die Funktion, und es werden nur die passenden Zeilen geliefert (Abb. 1).

Abb. 1: Row-Level-Security in Action

Abb. 1: Row-Level-Security in Action

Solange eine Security-Policy aktiv ist, besteht keine Möglichkeit, an die Daten heranzukommen, die von der Funktion herausgefiltert werden. Bei Sichten ist zu beachten, dass das Schema gebunden sein (WITH SCHEMABINDING) und daher die entsprechenden Anforderungen erfüllen muss. Des Weiteren kann eine solche Security-Policy mehr als nur eine einzelne Tabelle/Sicht umfassen, jeweils mit einer eigenen Filterfunktion. Damit lässt sich durch Deaktivierung der Security-Policy die zeilenbasierte Sicherheit gleichzeitig für eine ganze Gruppe zusammenhängender Tabellen ausschalten.

Im SQL Server Management Studio (SSMS) sind die Security-Policies innerhalb einer Datenbank unter Security/Security Policies zu finden. Zwar kann eine ähnliche Funktionalität bereits vor SQL Server 2016 mithilfe von Sichten realisiert werden, doch dieser neue Ansatz ist sicherlich transparenter und einfacher zu realisieren.

Durch die gewählte Realisierung der Row-Level-Security wird sich ein nachträglicher Einbau in bestehende Datenbanken recht einfach gestalten – vorausgesetzt, die Tabellen/Sichten verfügen über die notwendigen Daten.

Dynamic Data Masking

Mittels dynamischer Datenmaskierung wird die Offenlegung vertraulicher Daten eingeschränkt, indem sie für nicht berechtigte Benutzer maskiert werden. Das heißt, neben dem Recht, den Inhalt einer Tabelle lesen zu dürfen, gibt es nun das Recht, sensible Daten „demaskieren“ zu dürfen. Liegt dieses Recht nicht vor, werden die Daten vor der Rückgabe maskiert. Für Filter, Sortierungen und einige Arten von Ausdrücken stehen die Daten unverändert zur Verfügung – ein Umstand, der nicht ganz unproblematisch ist, wie später noch zu lesen ist. Die Aktivierung der dynamischen Datenmaskierung und Erstellung einer entsprechenden Tabelle wird in Listing 2 gezeigt.

Listing 2

-- Dynamic Data Masking einschalten
DBCC TRACEON(209, 219, -1);

-- Tabelle anlegen
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 = 'default()') NOT NULL,
  Telefon NVARCHAR(20) MASKED WITH (FUNCTION = 'default()') NULL,
  EMail NVARCHAR(100) MASKED WITH (FUNCTION = 'email()') NULL
);

-- Ein paar Daten einfügen
INSERT dbo.Mitarbeiter (Name, Gehalt, Telefon, EMail) VALUES 
('Thorsten Kansy', 999.9, '1234567', 'tkansy@dotnetconsulting.eu'),
('James Bond', 12000.0, '1234567', 'jbond@dotnetconsulting.eu'),
('Bob', 9.99, '1234567', 'bob@dotnetconsulting.eu');

Wird nun eine Abfrage auf diese Tabelle durchgeführt, ohne dass das UNMASK-Recht für den User vorliegt,

SELECT * FROM dbo.Mitarbeiter;
SELECT * FROM dbo.Mitarbeiter WHERE Name = 'Thorsten Kansy';

so sieht die Rückgabe wie in Abbildung 2 aus.

Abb. 2: Dynamic Data Masking versteckt sensible Details

Abb. 2: Dynamic Data Masking versteckt sensible Details

Zu sehen ist, dass der Filter trotz Maskierung korrekt arbeitet. Für die Maskierungsfunktion gibt es derzeit mehrere Optionen, die in Tabelle 1 kurz zusammengefasst sind.

Tabelle 1: Maskierungsfunktionen

Tabelle 1: Maskierungsfunktionen

Wie schon beschrieben, werden die Daten erst kurz vor der Rückgabe maskiert. Das eröffnet bei Ad-hoc-Abfragen aktuell die eine oder andere Sicherheitslücke, um doch die unmaskierten Werte zu sehen. Die folgende Abfrage beispielsweise „castet“ den Datentyp der Namensspalte unwesentlich und liefert so die ursprünglichen Daten unmaskiert: SELECT Name, CAST(Name AS CHAR(100)) FROM dbo.Mitarbeiter;.

Eine Sortierung nach der Gehaltsspalte offenbart zwar nicht den Wert, liefert aber die korrekte Sortierung, was wiederum gewisse Abschätzungen zulässt. Damit gibt es einen weiteren Grund, mit Ad-hoc-Abfragen von Anwendern noch vorsichtiger zu sein.

Always Encrypted

Wird mit extrem sensiblen Daten gearbeitet, so reicht ein einfaches Maskieren zum Schutz wahrscheinlich nicht mehr aus. Die Daten müssen auch verschlüsselt werden; oftmals vielleicht nicht gleich alle Spalten, aber meist eine Auswahl. Um dies ohne große Infrastruktur zu ermöglichen, muss ein Column Master Key erzeugt werden, der zum Verschlüsseln des oder der Column Encryption Key(s) verwendet wird. Diese wiederum wird zum Verschlüsseln der Werte in den Spalten benutzt. Klingt kompliziert, ist es aber nicht. Listing 3 zeigt ein Beispiel. Dabei liegen die Daten verschlüsselt in der Datenbank, werden verschlüsselt an den Client übertragen und erst dort entschlüsselt. Und das Schöne an der gewählten Technik sind zwei besondere Punkte: Zum einen funktionieren Filter trotz Verschlüsselung, d. h. es kann nach verschlüsselten Werten gefiltert werden, zum anderen sind nur wenige Änderungen in einer .NET-Anwendung nötig, um dieses Feature nutzen zu können.

Listing 3

-- 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 Encryption Key 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 
);

Auch wenn in Listing 3 lediglich textbasierte Spalten verschlüsselt werden, ist dies ohne Weiteres auch mit anderen Datentypen für Zahlen und Datum/Uhrzeit möglich.

Column Master Key und Column Encryption Key können derzeit schon via GUI administriert werden. Entsprechende Container befinden sich in einer Datenbank unter Security/Always Encrypted. Ob der Tabellendesigner zukünftig eine Unterstützung für verschlüsselte Spalten bieten wird, ist aktuell noch unklar. Das Management Studio scheint insgesamt noch nicht recht mit dieser neuen Funktion umgehen zu können. Dem Autor ist es nicht gelungen, Daten einzufügen (INSERT) oder entschlüsselt auszulesen.

Dies funktioniert dafür aber umso einfacher mit einer .NET-4.6-Anwendung, die das erweiterte ADO.NET 4.6 verwendet. Um das Column Encryption Setting Token zu erweitern und den Wert enabled anzugeben, reicht die Verbindungszeichenfolge. Eine Verbindungszeichenfolge sieht dann so aus:

Data Source=.; Initial Catalog=dotnetconsulting; Integrated Security=true; Column Encryption Setting=enabled;

Kurz zusammengefasst sorgt Always Encrypted dafür, dass die Daten ohne passenden Schlüssel nur in verschlüsselter Form verfügbar sind (in der Datenbank und bei der Übertragung zum Client). Selbst ein Blick im SQL Server Profiler oder über Extended Events zeigt nur die verschlüsselte Version.

Stellen Sie Ihre Fragen zu diesen oder anderen Themen unseren entwickler.de-Lesern oder beantworten Sie Fragen der anderen Leser.

System-versioned Table

Mit System-versioned Table (aka „temporale Tabellen“) werden automatisch Änderungen an Inhalten einer Tabelle überwacht und in einer Historisierungstabelle gepflegt – inklusive zweier Zeitstempel, die zeigen, von wann bis wann die Daten in dieser Form vorlagen. Listing 4 zeigt, wie eine solche Tabelle inklusive der Schattentabelle erzeugt wird.

Listing 4

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 NOT NULL,
    EndTime datetime2(7) GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME(StartTime, EndTime),
    CONSTRAINT Werte_PK PRIMARY KEY (ID)
)
WITH
(
    SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Werte_History)
)

Nach Ausführung dieser Anweisung finden Sie beide Tabellen in einer kleinen Hierarchie im Objekt-Explorer.

Werden nun Zeilen eingefügt, geändert und wieder gelöscht, sieht es so aus, dass die Originaltabelle (dbo.Werte) alle aktuellen Daten enthält und die Historisierungstabelle (dbo.Werte_History) die historischen Daten (Listing 5). Abbildung 3 zeigt, wie das Ergebnis aussieht.

 

-- Ein paar Zeilen einfügen, ändern und löschen
INSERT [dbo].[Werte] (Wert1, Wert2) VALUES
('A1','A2'), ('B1','B2'), ('C1','C2');
UPDATE [dbo].[Werte] SET Wert1 = 'A1_neu' WHERE ID = 1;
DELETE [dbo].[Werte] WHERE ID = 2;

 

Abb. 3: Aktuelle und historische Daten

Abb. 3: Aktuelle und historische Daten

Unveränderte Zeilen sind nicht in der Historisierungstabelle zu finden, gelöschte Zeile dagegen ausschließlich in der Historisierungstabelle, während geänderte, jedoch nicht gelöschte Zeilen, in beiden Tabellen zu finden sind. Der zwingend vorhandene Primärschlüssel der Originaltabelle sorgt für die notwendige Beziehung zwischen den einzelnen Zeilen.

Aber Achtung! Seit jeher führt SQL Server auch Änderungen durch, die keine Daten verändern. Wird z. B. folgende Anweisung mehrfach ausgeführt,

UPDATE [dbo].[Werte] SET Wert1 = 'A1' WHERE ID = 1;

so werden auch mehrfache Einträge historisiert, obwohl der Inhalt eigentlich nicht verändert wurde. Für eine sinnvolle Historisierung muss die eigene Programmierung also selbst prüfen, ob wirklich ein Update notwendig ist. Dieses Verhalten ist aber wie gesagt nicht neu, und auch ein entsprechender Trigger würde mehrfach gefeuert werden.

Eine System-versioned Table bringt jedoch auch eine Reihe von Einschränkungen mit sich. So kann die Struktur einer solchen Tabelle nicht nachträglich modifiziert werden. Ist sie einmal erstellt, führt eine solche Anweisung zwangsläufig zu einem Fehler:

ALTER TABLE dbo.Werte ADD Wert3 NVARCHAR(10) NULL;

Ebenfalls zum Scheitern verurteilt ist eine TRUNCATE TABLE-Anweisung. Des Weiteren ist eine Replikation solcher Tabellen nicht möglich.

Die Führung der automatischen Historisierung durch den SQL Server wird seinen Preis haben. DML-Operationen werden im Vergleich zu nicht versionierten Tabellen mehr Ressourcen (I/O-Aktivitäten und CPU) benötigen.

Und auch diese Funktionalität wäre schon mit früheren Versionen mittels Trigger realisierbar gewesen, allerdings bei Weitem nicht so einfach und mit allen Komplikationen, die Trigger so mit sich bringen.

Multi-Core-tempdb-Files

Mit SQL Server 2016 wird die tempdb automatisch so konfiguriert, dass die Anzahl der Data-Dateien der Anzahl der aktiven CPU-Kerne entspricht. Dies ermöglicht der Database Engine, die temporäre Datenbank besonders performant zu betreiben. Und mehr ist in diesem Fall immer gut.

Die Anzahl der Kerne wird automatisch beim Start der SQL-Server-Instanz ermittelt und die tempdb entsprechend konfiguriert. Damit ist dies ein interessanter Automatismus für virtuelle Server, die CPUs je nach Auslastung zugewiesen bekommen; für alle anderen ein willkommener Performanceschub.

Live Query Statistics

Wer bis dato Abfragen tunen möchte, dem stand (und steht) der geschätzte und der tatsächliche Ausführungsplan nach Anschluss zur Verfügung. SQL Server 2016 wird mittels Live Query Statistics bereits zur Laufzeit einer Abfrage Informationen dazu bereitstellen, welcher Verarbeitungsschritt wie viele Ressourcen verbraucht. Für die Visualisierung steht sowohl im Abfragefenster eine entsprechende Schaltfläche zur Verfügung, als auch im Activity-Monitor ein Reiter, der die Daten der aktuell laufenden Abfragen anzeigt (Abb. 4).

Abb. 4: Active Expensive Queries

Abb. 4: Active Expensive Queries

Somit lassen sich die aktuellen Leistungsfresser mit einem Blick erkennen – und das serverweit.

Zusammenfassung

In der SQL Server 2016 CTP 2 ist also bereits viel Spannendes verfügbar und ein Blick lohnt sich auf jeden Fall. Dieser Artikel hat lediglich ein paar dieser Neuerungen im Detail vorgestellt. Wichtig ist noch zu erwähnen, dass die CTP 2 nicht „Features complete“ ist – es kann sich also noch eine Menge ändern, und damit bleibt es spannend.

Aufmacherbild: business, technology and internet concepts von Shutterstock / Urheberrecht: Alexander Supertramp

Unsere Redaktion empfiehlt:

Relevante Beiträge

Meinungen zu diesem Beitrag

X
- Gib Deinen Standort ein -
- or -