Tag: "sql server 2014"

Artikel „SQL Server 2014 – Neues Fundament“ in iX Ausgabe 5/2014, Richtigstellungen zu In-Memory OLTP und ColumnStore Indexes und warum AlwaysOn nicht gleich Always On ist

Article “SQL Server 2014 – New Fundament” in iX Issue 5/2014, corrections in In-Memory OLTP and ColumnStore Indexes, and why AlwaysOn is not the same as Always On.

(DE)
In der Mai-Ausgabe der iX ist auf Seite 56 ein Artikel von mir zu finden, den ich zusammen mit den Kollegen Volker Heck (Cloud- und BI-Part) und Holger Schwichtenberg (Lektorat) geschrieben habe.

(EN)
In the May-issue of the iX, on page 56, there is an article by me which I wrote together with my colleagues Volker Heck (Cloud- and BI-Part) and Holger Schwichtenberg (copy-editing).

In der finalen Version, die ich auch erst am Kiosk zu sehen bekommen habe, sind leider einige Ungenauigkeiten enthalten. Um Missverständnisse auszuschließen, möchte ich diese hier kurz geraderücken, bzw. ein korrektes Verständnis sicherstellen.

Es geht los mit der Einleitung:
1)

„…Nach zwei Jahren Entwicklungszeit stellt Microsoft die neue Version seines Datenbankservers vor...“

Richtigstellung: Ich bin mir nicht sicher, wann der Startschuss für den SQL Server 2014 war, aber ziemlich sicher war das nicht 2 Jahre vor dem Release-Datum (1.4.2014), wie dieser Satz impliziert. Dass die In-Memory OLTP Engine XTP bereits 2009 mit ihrem ersten Patent untermauert wurde, schreibe ich etwas später noch im Artikel. Wann genau feststand, dass es einen SQL Server 2014 geben würde, und der Code entsprechend gebranched wurde, ist mir nicht bekannt. Wenn ich eine Vermutung abgeben würde, wäre dies eher ca. 3 Jahre vor dem Release.

Unfortunately, in the final version, which I have actually only set eyes on at the newspaper kiosk, there are a few inaccuracies. In order to avoid misunderstandings, I will correct them here shortly, or rather ensure a correct understanding.

Starting with the introduction:
1)

”…After two years of developing, Microsoft introduces the new version of its database server…”

Correction: I am not sure as to when the starting shot was made for the SQL Server 2014, but it is quite certain that it was not 2 years before the release date (1 April 2014), as implied by this sentence. A little later, in the article I also say that the In-Memory OLTP Engine XTP was confirmed with its first patent already in 2009. I am not aware of when exactly it was certain that there would be a SQL Server 2014, and that the code would be branched accordingly. If I was to speculate, I would say it was more like 3 years before its release.

 

2)

„…Wichtigste Neuerung ist das Ablegen relationaler Daten im Hauptspeicher statt auf der Festplatte….“

Richtigstellung: Diejenigen, die sich bereits ein wenig mit dieser neuen Technologie auseinandergesetzt haben, wissen es natürlich: Die Daten werden sowohl in RAM als auch auf Festplatte gespeichert – es sei denn man arbeitet mit „Schema_Only“-Tabellen. Später im Artikel wird das auch noch deutlich, mag aber hier verwirren.

2)

„…The most important innovation is the storing of relational data in the main storage instead of the hard drive.”

Correction: Those who have already familiarized themselves a little with this new technology will know of course: The data are stored both in RAM and in the hard drive – unless you work with “schema_only”-tables. This will become clear later in the article, but may cause some confusion here.

 

3)

„…Stored Procedures in Maschinensprache
­­­… „Native Kompilierung“ ... Dafür erzeugt der Server beim ersten Ausführen aus der jeweiligen Prozedur eine DLL. Diese Bibliotheken überstehen den Neustart von Datenbank oder Server jedoch nicht, müssen also danach erneut erstellt werden…“

Das kann man leicht falsch verstehen.
Richtigstellung: Genau gesagt werden diese DLLs nach jedem Neustart von Datenbank oder Datenbankserver neu generiert (bei erster Verwendung). – Man muss diese DLLs oder gar die Prozeduren also nicht selber neu erstellen.

3)

             „…Stored Procedures in machine language.“

… “Native compiling” …”Before the first run, the server produces a DLL from the respective procedure for this. These libraries, however, do not last through the restart of database or server, so they have to be generated again afterwards…”

This can be easily misunderstood.

Correction: To be precise, these DLLs are regenerated after each restart of the database or database server (at first usage). – Thus, one does not have to generate these DLLs or even the procedures new oneself.

4)

„(Nativ kompilierte Prozeduren)…Solche Prozeduren … erlauben noch nicht alle T-SQL-Sprachelemente. Es fehlen beispielsweise Raiseerror und Begin Transaction, einige Funktionen sowie Query Hints.“

Auch das könnte jemanden auf eine falsche Fährte führen.
Richtigstellung: Besser ausgedrückt: „Zum Beispiel kann man bestimmte Befehle wie Raiseerror oder Begin Transaction, anstelle dessen ein „Atomic“-Block erforderlich ist, nicht nutzen.“ - Der Atomic-Block startet bereits eine Transaktion, daher ist ein zusätzliches „Begin Transaction“ ohnehin fehl am Platz. - Einige Query Hints werden übrigens tatsächlich unterstützt.

4)

„(Natively complied procedures) … Such procedures … do not yet allow for all T-SQL language elements. For instance, Raiseerror and Begin Transaction are missing, as well as a few functions and Query Hints.”

This, too, could be misleading.

Correction: Put more precisely: “For instance, it is not possible to use particular commands such as Raiseerror or Begin Transaction, instead of which an “atomic” block is required.” The Atomic-Block already starts a transaction, so an additional “begin transaction” is superfluous in any case. – By the way, a few Query Hints are actually supported.  

 

5)

„(neue Parallelitätskontrolle „multi-versioned, timestamped optimistic concurrency control“)… Dazu ergänzt der Server alle Datensätze um einen bei jeder Änderung automatisch aktualisierten Zeitstempel, anhand dessen er Konflikte erkennt…“

Das kann man auch leicht falsch interpretieren und einen glauben lassen, dass immer der selbe Datensatz aktualisiert wird. Der Hintergrund von „multi-versioned, timestamped optimistic concurrency control“ ist aber gerade, das es pro Version einen neuen Datensatz gibt, was sich in ausführlichen Tests von Microsoft Research in realitätsnaheren Testreihen (mit komplexeren Transaktionen im Mix mit längeren Lesezugriffen und Hotspot-Szenarien) als effizienter als „Single-version locking“ herausgestellt hat. (Quelle: „High-Performance Concurrency Control Mechanisms for Main-Memory Databases“, Microsoft, University of Wisconsin – Madison)
- Single-Version Locking wird beispielsweise von Oracle TimesTen und IBM’s solidDB eingesetzt.
Richtigstellung: Genauer ist also zu sagen, dass es pro Version einen Datensatz gibt, und die “Alten Versionen” durch ein End-Timestamp als solche markiert werden.

5)

„(new concurrency control, „multi-versioned, timestamped optimistic concurrency control“)… For this, the server complements all data sets by an automatically updated timestamp created with each change, with the help of which it recognizes conflicts…”

This can also be easily misinterpreted and may make believe that always the same data set is being updated. However, the background to “multi-versioned, timestamped optimistic concurrency control” is in fact that there is a new data set per version, which comprehensive tests in realistic test series by Microsoft Research (with more complex transactions combined with longer read access and hotspot scenarios) have shown to be more efficient than “Single-version locking.” (Source: “High-Performance Concurrency Control Mechanisms for Main-Memory Databases,” Microsoft, University of Wisconsin – Madison)

Single-Version Locking, for example, is applied by Oracle TimesTen and IBM’s solidDB.

Correction: It is thus more precise to say that there is one data set per version, and the “old versions” are marked as such by an end-timestamp.

6)

„(Clustered ColumnStore Indexe)…Diese erweiterte Variante der Hauptspeicher-Index-Technik wurde für die 2013 erschienene PDW-Variante (Parallel Data Warehouse) des SQL Server 2012 entwickelt und ist dort bereits im Einsatz…“

Die Wortwahl lässt vermuten, dass diese (Columnstore) Indexe, wie auch bei In-Memory optimierten Tabellen & Indexen, lediglich im Hauptspeicher liegen. Das stimmt natürlich nicht.
Richtigstellung: Besser sollte hier stehen: „Hauptspeicher-optimierte Indexe“

6)

 “(Clustered ColumnStore Indexes)… This enhanced type of the Main-Memory Index Technique was developed for the PDW-version (Parallel Data Warehouse) of the SQL Server 2012 made available in 2013 and is already being applied there…”

The choice of words suggests that these (Columnstore) indexes, just as with the In-Memory optimized tables & indexes, are located in the main memory only. This is of course not the case.  

Correction: More precise would be to say: “Main-Memory-optimized indexes”

7)

Und last but not least leider hat sich auch in diesem Artikel ein häufiger Fehler eingeschlichen:

Die Lösung für hohe Verfügbarkeit und Notfallwiederherstellung, welche im SQL Server 2012 neu eingeführt wurde, schreibt sich natürlich „AlwaysOn“, und weder „Always On“ noch „Always-On“.
„Always On“ (mit Leerzeichen) wurde bereits in SQL Server 2005 eingesetzt, um Speicher-Hardware für SQL Server zu zertifizieren. Dazu gehört z.B.:

  • die korrekte Umsetzung der API’s, des Write-Ahead Logging (WAL) Protokolls für sowohl Transaktionsprotokolle als auch Daten- und Backup-Dateien
  • der Optionen FILE_FLAG_WRITETHROUGH und FlushFileBuffers beim Öffnen von Dateien
  • der Unterstützung von asynchronem I/O
  • Write ordering
  • Das korrekte Übermitteln der Sektor-Größen an die Windows API’s, um Sektor-Größen-Versatz und Torn Writes zu verhindern
  • Die NTFS-Fähigkeiten wie z.B. Sparse Files, File Streams, Encryption, Compression, sämtliche Sicherheitseigenschaften

Über „Always On” lässt sich z.B. hier nachlesen: www.dell.com/downloads/global/solutions/
dell_pv_sql_always_on_tech_note_v_1_5.pdf

Im SQL Server 2008 wurde „Always On“ für die gesamte Palette der Hochverfügbarkeitstechniken verwendet. Dazu gehörten Database Mirroring, Log Shipping, Failover Clustering, Peer-to-Peer Replication, Backup und Restore (!), Database Snapshots, selbst Partitionierung und weiteres. (Hier nachzulesen: High Availability – Always On Technologies) Das hat also nicht mit dem neuen Features AlwaysOn-Verfügbarkeitsgruppen/Availability Groups und AlwaysOn-Failoverclusterinstanzen zu tun.
Und Feature-Namen werden nicht einfach „eingedeutscht“, genauso wenig wie man SharePoint auseinanderschreibt – Nein, ich werde das selbst aus Demozwecken nicht tun ;-)

7)

And last but not least, I’m afraid in this article, too, a common mistake has slipped in:

The solution for high availability and emergency restoration reintroduced in SQL Server 2012 is spelled “AlwaysOn,” of course; – neither “Always On” nor “Always-On.” “Always On” (with space) was already applied in SQL Server 2005 in order to certify storage hardware for SQL Server. It includes, for example:

  • The correct implementation of the APIs, the Write-Ahead Logging (WAL) protocol for both transaction protocols and data and backup files.
  • The options FILE_FLAG_WRITETHROUGH and FlushFileBuffers when opening files.
  • The support of asynchronous I/O.
  • Write ordering.
  • The correction transmission of the sector sizes to the Windows APIs in order to avoid sector size mismatches and Torn Writes.
  • The NTFS-abilities such as Sparse Files, File Streams, Encryption, Compression and all security properties.

You can read more on “Always On” here, for example:

www.dell.com/downloads/global/solutions/
dell_pv_sql_always_on_tech_note_v_1_5.pd

In SQL Server 2008, “Always On” was used for the entire range of high availability techniques. These include Database Mirroring, Log Shipping, Failover Clustering, Peer-to-Peer Replication, Backup and Restore (!), Database Snapshots, even partitioning, and more. (Read more here: High Availability – Always On Technologies) So this does not have anything to do with the new features AlwaysOn-Availability Groups or AlwaysOn-Failoverclusterinstances.

What is more, feature names are not simply „Germanized,“ just as you do not spell SharePoint separately – and no, I will not even do this for demonstration purposes ;-).

 

Soweit habe ich nun meinem Genauigkeitsempfinden genüge getan ;-)

 

Da das folgende Diagramm es leider nicht in den Artikel geschafft hat, möchte ich es hier zumindest mit meinen Lesern teilen:

For now, I feel like I have satisfied my sense of accuracy ;-)

 

Since the following graph has unfortunately not made it into the article, I would like to share it with my readers here at least:  

 

 

 

Das ist das Ergebnis eines Performance-Vergleiches einer schematisch so gut wie identischen „on-Disk“-Tabelle gegenüber den verschiedenen In-Memory OLTP Varianten. Der Test wurde auf Standard-Hardware durchgeführt: Intel i7-3529 (2,9Ghz), 2 Cores hyperthreaded, 16GB RAM und SSDs. Das Ergebnis kann sich sehen lassen und entspricht Microsofts Versprechung, das neue Hardware nicht zwingend erforderlich ist, um spürbare Performance-Gewinne durch den Einsatz der XTP-Engine zu erhalten.

This is the result of a performance comparison of a schematically virtually identical “on-disc”-table compared to the different In-Memory OLTP variants. The test was carried out with standard hardware: Intel i7-3529 (2,9Ghz), 2 Cores hyperthreaded, 16GB RAM and SSDs. The result is quite impressive and matches Microsoft’s promise that new hardware is not imperative in order to obtain tangible performance gains through the application of the XTP-Engine.

Und hier sind auch nochmal die begleitenden Links zu dem Artikel:

These are the corresponding links to the article:

 

 

Ich hoffe, die genannten Punkte sind für ein besseres Verständnis nicht nur des Artikels sondern auch von SQL Server 2014 allgemein hilfreich.

Kommentare oder Nachfragen können gern hier über meinen Blog hinterlassen werden.

I hope the points above are helpful for a better understanding not only of the article but also of SQL Server 2014 in general.

You are welcome to leave comments or questions in my blog.

 

 

Andreas Wolter

 

PS: Leider sind in meiner Master-Class Workshop In-Memory OLTP & ColumnStore - New Storage Engines in SQL Server 2014 (XTC) keine Plätze mehr verfügbar (!). Im Sommer wird sicher die Entscheidung für eine Neuauflage im 2. Halbjahr 2014 oder doch erst wieder im 1. HJ 2015 fallen. – Im 2. HJ stehen wieder viele Konferenzen, inklusive MVP Summit, PASS Summit und PASS Camp an, so dass es da wirklich eng wird. Aussichtsreicher ist da meist eine Inhouse-Schulung auf Anfrage.

P.S.: Unfortunately, there are no spots left (!) in my Master-Class Workshop In-Memory OLTP & ColumnStore - New Storage Engines in SQL Server 2014 (XTC). The decision for a remake either in the second half of 2014, or only in the first half of 2015, will probably be made in summer. – For the second half of 2014, many conferences, including MVP Summit, PASS Summit and PASS Camp, are lined up, so it is already quite cramped. The prospects may thus often be better for an in-house-training on request.

 

New Permissions in SQL Server 2014: IMPERSONATE ANY LOGIN, SELECT ALL USER SECURABLES, CONNECT ANY DATABASE and the old CONTROL SERVER

(DE)
SQL Server 2014 bringt insgesamt 5 neue Berechtigungen. Zwei von diesen sind auf Datenbank-Ebene und nur in der Windows Azure SQL Database Edition verfügbar – nicht im „Box-Produkt“.
(Danke an Erland Sommarskog für die Bestätigung und Hinweis auf die recht versteckte Notiz in der Dokumentation: GRANT Database Permissions)
Die neuen Berechtigungen sind wie folgt:

(EN)
SQL Server 2014 brings altogether 5 new permissions. Two of those are on database level and only available in the Windows Azure SQL Database Edition – not in the box-version (Thanks Erland Sommarskog for confirming this and pointing me to the quite hidden note in the documentation: GRANT Database Permissions)
The new permissions are as follows:

 

Class Desc.

Permission Name

Type

Parent Covering Permission Name

DATABASE

ALTER ANY DATABASE EVENT SESSION

AADS

ALTER ANY EVENT SESSION

DATABASE

KILL DATABASE CONNECTION

KIDC

ALTER ANY CONNECTION

SERVER

CONNECT ANY DATABASE

CADB

 

SERVER

IMPERSONATE ANY LOGIN

IAL

 

SERVER

SELECT ALL USER SECURABLES

SUS

 

 

Und wofür und wie können wir diese neuen Berechtigungen auf Server Ebene verwenden?

 

IMPERSONATE ANY LOGIN

 

Erinnert Ihr Euch an das Problem mit CONTROL SERVER?

Das größte Problem war, das dieses Recht auch die Impersonifizierung eines jeden Kontos, inklusive der Privilegien Erweiterung zum sysadmin erlaubte.

Die Details und auch andere Probleme mit CONTROL SERVER habe ich hier umfassend dokumentiert:

CONTROL SERVER vs. sysadmin/sa: permissions, system procedures, DBCC, automatic schema creation and privilege escalation - caveats

 

SQL Server 2014 gibt uns mit der Einführung der IMPERSONATE ANY LOGIN-Berechtigung Munition, dieses Problem anzugehen.

-           Diese Berechtigung erlaubt es, jeden Login und User zum impersonieren(!).

 

Wenn wir dieses mit einem DENY gegenüber dem Principal mit CONTROL SERVER Recht verwenden, verhindert es diesen, irgendeinen Login direkt zu impersonifizieren. (Warum sage ich “direkt”? –  Das sehen wir ein Stück weiter unten.)
Also sehen wir uns an, wie man einen Login mit CONTROL SERVER an einer Pivilegienerweiterung hindert, mithilfe der neuen Berechtigung

So, what for and how can we use those permissions on Server level?

 

IMPERSONATE ANY LOGIN

 

Do you remember the problem with CONTROL SERVER?
The biggest flaw of this permission was, that this permission also allowed Impersonation of any account, including privilege elevation to any sysadmin.
I have documented this and other problems with CONTROL SERVER in detail here:

CONTROL SERVER vs. sysadmin/sa: permissions, system procedures, DBCC, automatic schema creation and privilege escalation - caveats

 

Now in SQL Server 2014, by introducing the permission IMPERSONATE ANY LOGIN, gives us ammunition to tackle this problem.

-           This Permission permits to impersonate any Login and User(!).

 

If we DENY this to the Principal with CONTROL SERVER permission, it prevents him from impersonating any Login directly. (Why do I say “directly”? – We’ll see a bit further down.)

So let’s see how to prevent a Login with CONTROL SERVER from elevating privileges by impersonating another login with help of the new permission:

 

USE [master]

GO

 

CREATE LOGIN DBA_TheDude WITH PASSWORD=N'www', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

 

CREATE SERVER ROLE [Role_DBA]

 

ALTER SERVER ROLE [Role_DBA]

ADD MEMBER DBA_TheDude

 

GRANT CONTROL SERVER TO [Role_DBA]

GO

DENY IMPERSONATE ANY LOGIN TO [Role_DBA]

GO

 

CREATE DATABASE ControlServer_Schema_Demo

GO

 

-- ====================

-- === Test

 

EXECUTE AS LOGIN = 'DBA_TheDude'

 

-- Attempt impersonation:

EXECUTE AS LOGIN = 'sa';

-->

Msg 15406, Level 16, State 1, Line 9

Cannot execute as the server principal because the principal "sa" does not exist, this type of principal cannot be impersonated, or you do not have permission.

//

Die Ausführung als Serverprinzipal ist nicht möglich, weil der Prinzipal 'sa' nicht vorhanden ist, für diesen Typ von Prinzipal kein Identitätswechsel möglich ist, oder Sie nicht die erforderliche Berechtigung haben.

 

 

USE ControlServer_Schema_Demo

 

EXECUTE AS USER = 'dbo';

 

-->

Msg 15517, Level 16, State 1, Line 15

Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.

//

Die Ausführung als Datenbankprinzipal ist nicht möglich, weil der Prinzipal 'dbo' nicht vorhanden ist, für diesen Typ von Prinzipal kein Identitätswechsel möglich ist, oder Sie nicht die erforderliche Berechtigung haben.

 

Hurra!(?)

Privilege-Escalation-Risiko:

Wirklich? Immer noch?
Natürlich.

Wir laufen immer noch unter dem Kontext DBA_TheDude:

Hooray!(?)

Privilege-Escalation-risc:

Really? Still?
Of course.

 

Still we are running under the context of DBA_TheDude:

 

 

USE master;

 

CREATE LOGIN UtilizeMe WITH PASSWORD=N'www', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

 

GRANT CONTROL SERVER TO UtilizeMe

GO

 

Wir können den Login “UtilizeMe” nicht impersonifizieren, aber wir können und einfach mit seinem Passwort anmelden!

-           Nebenbei ein weiterer Grund, SQL Authentifizierung nicht zu verwenden, da er ansonsten die Credentials eines validen Windows-Login’s finden müsste – viel schwieriger, als einfach seinen eigenen Backdoor-account anzulegen.

We cannot Impersonate the “UtilizeMe” Login, but we can just Log On using his password!

-           Another reason to not use SQL authentication by the way, as he would then need to find a valid Windows-Login’s Credentials – much harder to just creating his own backdoor-account.

 

 

Um also unseren Administrator wirklich daran zu hindern, seine Privilegien zum Sysadmin zu erweitern, müssen wir auch mit DENY ALTER ANY LOGIN  und ALTER ANY SERVER ROLE arbeiten.

So in order to further prevent our Administrator from elevating privileges to sysadmin, we also need to work with DENY ALTER ANY LOGIN  and ALTER ANY SERVER ROLE.

 

Und kann DANN CONTROL SERVER endlich sicher verwendet werden?

NEIN!

 

Tatsächlich gibt es noch ein paar andere Dinge, die man tun kann, um die Berechtigungen von einem CONTROL SERVER-berechtigten Konto zu erweitern. Etwas trickreicher vielleicht, aber ein Angreifer mit einem guten Wissen über SQL Server (ich spreche also nicht von „Raketenwissenschaft“), wird in der Lage sein soetwas durchzuführen.

Mir ist bewusst, dass das “Separation of Duties in SQL Server 2014”-Whitepaper (Enthalten im Microsoft® SQL Server® 2014 Product Guide) die Kombination von GRANT CONTROL SERVER + DENY IMPERSONATE ANY tatschlich als Best Practice listet, aber dennoch…

 

Also, empfehle ich die Verwendung in irgendeiner Weise?
Das ist für mich persönlich eine harte Frage, da ich gerne viel weniger Leute sehen würde, die sa/sysadmin für tägliche Aufgaben verwenden/vergeben.

Leider ist es jedoch weit davon entfernt, perfekt zu sein, und in Sicherhit-belangen, alles, was nicht lupenrein ist, ist ein Risiko.
Aber ich sehe es durchaus als ersten Schritt, um Leute davon abzuhalten, von Anfang an die höchsten Berechtigungen zu verwenden, da viele einfach nicht die Zeit und Kenntnisse haben werden, dort auszubrechen.
Ich empfehle es in Kombination mit soliden Überwachung und Alarmen.

Wer das also anstelle von sa/sysadmin verwendet, verdient dennoch Applaus, da es zeigt, dass man sich kümmert und es wagt, Berechtigungen einzuschränken.

Can we THEN finally use CONTROL SERVER completely safely?

 

NO!

 

In fact there are a few other things one can do to elevate permissions from a CONTROL SERVER-permitted account. More tricky in a way, but an attacker with some good knowledge about SQL Server (note, I am not saying “rocket-scientist”) will be able to do that.

I am aware that the “Separation of Duties in SQL Server 2014”-Whitepaper (Contained in the Microsoft® SQL Server® 2014 Product Guide) does in fact list the combination of GRANT CONTROL SERVER + DENY IMPERSONATE ANY LOGIN as a best practice, but yet…

 

So do I recommend using it in any way?

That is a hard question for me personally, as I would like to see much less people using/granting sa/sysadmin for daily tasks, and this permission had the potential to make an end to it.

Unfortunately it is far from perfect, and in security-terms, anything not flawless, is a risk.

But in terms of getting people away from using the highest privileges from the very beginning, I do see it as a step, since many may just not have the time and skills to break out of it.

I do recommend using it in combination with some solid Auditing and alerts in place.
So anyone using this instead sa/sysasdmin still gets applause, as it shows you care and dare to limit permissions.

 

SELECT ALL USER SECURABLES


Diese Berechtigung kann verwendet werden, um einen hochgradig berechtigten Principal, der z.B. Troubleshooting/Analysen des Servers durchführt daran zu hindern, Nutzer-Daten auszulesen. – Vergesst nicht, auch EXECUTE in alle Nutzerdatenbanken zu verbieten, ansonsten kann derjenige immer noch alle gespeicherten Prozeduren (sofern vorhanden) ausführen, um an die Daten zu gelangen.
Auch das ist nicht Bombenfest, wie wir bereits von CONTROL SERVER und seinen Einschränkungen wissen.

Was sicherer ist, ist die Verwendung für eine Art Auditor, der ALLE Daten lesen (aber nicht ändern) können soll – ohne den Aufwand, in sämtlichen Nutzerdatenbanken Benutzer und Rechte zu vergeben.

SELECT ALL USER SECURABLES


This permission can be used for preventing a highly privileged Principal that may be troubleshooting/analyzing the server from reading any user data. - Do not forget to also deny EXECUTE in all User databases though, otherwise he can just execute the stored procedures (if any exist) to get to the data.
Also this is not bullet-proof as we already know from CONTROL SERVER and it’s restrictions.

What’s more safe, is the use for an Auditor that needs to read ALL data, but not change it - without the effort of creating users and permissions in all user databases.

 

CONNECT ANY DATABASE

 

Diese Berechtigung kann gut für Logins verwendet werden, die sich im Wesentlichen mit jeder Datenbank verbinden können and zum Beispiel Code Reviews durchführen sollen – indem man diese mit der VIEW ANY DEFINITION Berechtigung kombiniert.
Das ist in meinen Augen tatsächlich sehr gut verwendbar für viele Szenarien.

CONNECT ANY DATABASE

 

This permission can be used quite well for having logins that can basically connect to any database and for example do code reviews - by combining it with the VIEW ANY DEFINITION permission.
I do think this is actually of quite some use for many scenarios.

 

Happy “Server controlling”,

 

Andreas

New Extended Events for Tracing in SQL Server 2014

Neue “Erweiterte Ereignisse” (XEvents) für Tracing in SQL Server 2014

(de)
Dieser Artikel enthält eine Liste der neuen Erweiterte Ereignisse (“Extended Events”), dem Tracing Framework in SQL Server in dem neuen 2014 Release.

Zunächst gibt es 3 neue Pakete („Packages“), die alle zu der neuen Arbeitsspeicher(„In-Memory“) -OLTP Engine: XTP, aufgeteilt auf 3 DLLs:

(en)
This article contains a list of new Extended Events, the tracing framework in SQL Server in the new 2014 release.

To begin with there are 3 new packages, all belonging to the new In-Memory OLTP Engine: XTP, split up into 3 dlls:

 

Package

description

Module name

XtpRuntime

Extended events for the XTP Runtime

{InstanceDir}\MSSQL\Binn\
hkruntime.dll

XtpEngine

Extended events for the XTP Engine

{InstanceDir}\MSSQL\Binn\
hkengine.dll

XtpCompile

Extended events for the XTP Compile

{InstanceDir}\MSSQL\Binn\
hkcompile.dll

 

Die Anzahl der Extended Events ist von 625 in SQL Server 2012 (SP1) auf 870 in SQL Server 2014 (RTM) angestiegen. Das sind 245 neue Events! – Reichlich Möglichkeiten, in Internas einzutauchen :-)
2 Events sind umbenannt worden:

recovery_incremental_checkpoint in

recovery_indirect_checkpoint

und hekaton_slow_param_passing in

natively_compiled_proc_slow_parameter_passing

Ja, richtig gelesen. Es gab bereits im SQL Server 2012 eine Anzahl an Events für die XTP Engine. Es kommt recht häufig vor, dass man Bits von zukünftigen Entwicklungen in der aktuellen Release findet.

Nebenbei: SQL Trace wurde nicht im geringsten angefasst. Die Anzahl von SQL Trace Events bleibt mit 180 seit SQL Server 2008 stabil. – Here ist ein Überblick von Extended Events gegen SQL Trace Events in den verschiedenen SQL Server Versionen: Extended Events vs SQL Trace im Vergleich – oder warum SQL Trace & Profiler einfach von gestern sind :-)

Weiter zu XEvents: es gibt keine neuen Prädikate, aber eine Anzahl neuer Maps, was natürlich nicht überrascht bei der Menge neuer Events.
Targets, Actions, Typen und Session Optionen  sind unverändert.

Und hier ist die Liste der neuen Extended Events:

The number of Extended Events increased from 625 in SQL Server 2012 (SP1) to 870 in SQL Server 2014 (RTM). That makes 245 new events! – Plenty of opportunities to look into internals :-)
2 events have been renamed:

recovery_incremental_checkpoint into

recovery_indirect_checkpoint

and hekaton_slow_param_passing into

natively_compiled_proc_slow_parameter_passing

Yes, you read correctly. There have already been a couple of events for the new XTP engine within SQL Server 2012. It’s quite common that you can find bits of future developments within the current release.

By the way: SQL Trace has not been touched by the slightest bit. The number of SQL Trace events stays at 180 since SQL Server 2008. – here you find an overview of the number of Extended Events vs SQL Trace events in the various SQL Server versions: Comparing Extended Events vs SQL Trace – or why SQL Trace & Profiler are just a thing of the past :-)

Continuing with XEvents: There are no new predicates, but a couple more maps, which is of course not surprisingly considering the amount of new events.
Targets, actions, types and session options are unchanged.

And here is the list of all new extended events:

 

Package
name
Event name Description
qds query_store_async_shutdown_failed Fired when query store encounters an error during async query store shutdown
qds query_store_background_cleanup_task_failed Fired if the background task for cleanup of QDS stale data is not completed successfully
qds query_store_background_task_creation_failed Fired if the background task for QDS data persistence could not be created
qds query_store_background_task_initialization_failed Fired if the background task for QDS data persistence could not be initialized
qds query_store_background_task_persist_failed Fired if the background task for QDS data persistence is not completed successfully
qds query_store_begin_persist_runtime_stat Fired immediately before current runtime statistics for a query plan is persisted to the database.
qds query_store_bloom_filter_false_positive Fired if the Bloom filter for QDS statement texts gives a false positive result
qds query_store_check_consistency_init_failed Fired when check consistency task fails during initialization
qds query_store_database_initialization_failed Fired if initialization of the Query Store for database has failed. The Query Store will be disabled for this database
qds query_store_db_cleanup__finished Fired if cleanup of QDS stale data finished for particular database.
qds query_store_db_cleanup__started Fired if cleanup of QDS stale data started for particular database.
qds query_store_disk_size_check_failed Fired when a check against Query Store on-disk size limit fails
qds query_store_disk_size_info Fired when a check against QDS on-disk size is performed
qds query_store_execution_runtime_info Fired when runtime information is sent to the query store.
qds query_store_execution_runtime_info_discarded Fired when runtime information sent to the query store is discarded.
qds query_store_execution_runtime_info_evicted Fired when runtime information sent to the query store is evicted.
qds query_store_flush_failed Fired when query store failed to flush dirty data
qds query_store_loaded Fired when query store is loaded
qds query_store_notify_dirty_shutdown_on_partition_startup Fired when previous instance of query store for the partition is shutdown by force in order to allow creation of a new instance
qds query_store_notify_force_failure_failed Fired when query store failed to notify force failure
qds query_store_persist_task_init_failed Fired when persist task fails during initialization
qds query_store_plan_forcing_failed Occurs when forcing of plan from qds fail
qds query_store_plan_persistence_failure Fired if there's a failure to persist plan
qds query_store_plan_removal Fired when plan is removed
qds query_store_query_persistence_failure Fired if there's a failure to persist query
qds query_store_read_write_failed Fired if the read/write to Query Store internal tables failed
qds query_store_statement_not_found Fired in case when statement couldn't be found due to race condition or ambiguous user request.
qds query_store_unloaded Fired when query store is unloaded from memory
sqlos nonpreemptive_long_syncio record long sync io operation in nonpreemptive worker
sqlos stuck_dispatcher_callback_executed Stuck dispatcher callback executed
sqlos wait_completed Occurs when there is a wait completed on a SQLOS controlled resource.  Use this event to track wait completion.
sqlserver after_natively_compiled_proc_entry_removal_on_drop Fired after the procedure cache entry is flushed when dropping a natively compiled procedure.
sqlserver availability_replica_state Occurs when the Availability Replica is starting or shutting down.
sqlserver before_natively_compiled_proc_entry_removal_on_drop Fired before the procedure cache entry is flushed when dropping a natively compiled procedure.
sqlserver before_redo_lsn_update Occurs just prior to the update of the EOL LSN
sqlserver buffer_pool_eviction_thresholds_recalculated Lazywriter and/or worker clock has wrapped the BUF array and thresholds are re-calculated.
sqlserver buffer_pool_extension_pages_evicted Page is evicted from the buffer pool extension cache.
sqlserver buffer_pool_extension_pages_read Page is read from the buffer pool extension cache.
sqlserver buffer_pool_extension_pages_written Page or contiguous set of pages evicted into the buffer pool extension cache.
sqlserver check_phase_tracing Occurs when DBCC CHECK enters a new phase of the checking. Use this event to trace the phases of DBCC CHECK process.
sqlserver check_thread_message_statistics Occurs when a phase of DBCC CHECK is finished. Use this event to collect the number of messages a DBCC CHECK thread has sent or received.
sqlserver check_thread_page_io_statistics Occurs when a phase of DBCC CHECK is finished. Use this event to collect the number of logical, physical, and read-ahead IOs a DBCC CHECK thread has performed.
sqlserver check_thread_page_latch_statistics Occurs when a phase of DBCC CHECK is finished. Use This event to collect the number and time of page latch and IO latch waits.
sqlserver clustered_columnstore_index_rebuild Occurs when clustered index on the table was rebuilt. This event is raised three times for ALTER index rebuild operation on CCSI. The event is raised when the operation takes lock on index rebuild resource, when lock is taken on the table and when S lock on the table is upgraded to SCH_M lock to switch indexes in metadata.
sqlserver column_store_code_coverage Code coverage Xevent for columnstore code.
sqlserver column_store_index_build_low_memory Occurs when Storage Engine detects low memory condition and the rowgroup size is reduced.
sqlserver column_store_index_build_process_segment Occurs when a segment is processed
sqlserver column_store_index_build_throttle Shows the statistics of columnstore index build parallelism throttling
sqlserver columnstore_tuple_mover_begin_compress Occurs when column store tuple mover starts compressing a deltastore.
sqlserver columnstore_tuple_mover_end_compress Occurs when column store tuple mover is done compressing a deltastore.
sqlserver database_xml_deadlock_report Produces a deadlock report for a victim, with information scoped to the victim's database.
sqlserver db_lock_acquired_from_cache Occurs when a DB lock is acquired from the XactWorkspace DB lock cache.
sqlserver db_lock_released_from_cache Occurs when a DB lock is released from the XactWorkspace DB lock cache.
sqlserver ddl_with_wait_at_low_priority A DDL statement was executed using the WAIT_AT_LOW_PRIORITY options
sqlserver diag_quantum_end Occurs when the diag is notified of quantum end.
sqlserver dyn_throttle_checkpoint Occurs when checkpointing has been dynamically throttled with a new Checkpoint rate.
sqlserver feature_extension Occurs when received and parses data for a feature in feature extension.
sqlserver file_handle_in_use Fired when a file handle we're trying to delete is in use and we don't expect it to be. The typical response is dumping all the handles in the system via SysInternals HANDLE.EXE
sqlserver file_read_enqueued File read enqueued
sqlserver file_read_throttled File read throttled
sqlserver file_write_enqueued File write enqueued
sqlserver file_write_throttled File write throttled
sqlserver ghost_cleanup_task_packet_enqueue A task packet is enqueued
sqlserver ghost_cleanup_task_process_packet A task packet is dequeued and processed
sqlserver ghost_cleanup_task_process_pages_for_db_packet Purge the pages that contain ghost records found in a database
sqlserver ghost_cleanup_task_start Ghost cleanup task start
sqlserver ghost_cleanup_task_suspend Ghost cleanup task suspend
sqlserver hadr_tds_synchronizer_payload_skip Hadron Tds Listener Synchronizer skipped a listener payload because there were no changes since the previous payload.
sqlserver lock_request_priority_state The priority state of a lock request
sqlserver log_cache_write_block Writing a log block to the log cache
sqlserver metadata_ddl_add_column Occurs when an ALTER TABLE ADD column operation is updating base index.
sqlserver metadata_ddl_alter_column Occurs when an ALTER TABLE ALTER column operation is updating base index.
sqlserver metadata_ddl_drop_column Occurs when an ALTER TABLE DROP column operation is updating base index.
sqlserver mixed_extent_activation Track mixed extent activation and deactivation operations.
sqlserver mixed_extent_allocation Track mixed extent allocation operations
sqlserver mixed_extent_deallocation Track mixed extent deallocation operations.
sqlserver mixed_page_allocation Track mixed page allocation operations
sqlserver mixed_page_deallocation Track mixed page allocation operations
sqlserver mixed_page_scan_file Track the activity of SGAM scans for mixed page allocation
sqlserver mixed_page_scan_page Track the activity of SGAM scans for mixed page allocation
sqlserver mixed_page_skipextent Track the activity of SGAM scans for mixed page allocation
sqlserver natively_compiled_proc_execution_started Fired before a natively compiled procedure is executed.
sqlserver natively_compiled_proc_slow_parameter_passing Occurs when a Hekaton procedure call dispatch goes to slow parameter passing code path
sqlserver process_killed_by_abort_blockers A process is killed by an ABORT = BLOCKERS DDL statement
sqlserver query_execution_batch_hash_aggregation_finished Occurs at the end of batch hash aggregation.
sqlserver query_execution_batch_hash_children_reversed Occurs each time when hash join reverses build and probe side while processing data spilled to disk.
sqlserver query_execution_batch_hash_join_spilled Occurs each time when hash join spills some data to disk in batch processing.
sqlserver query_optimizer_estimate_cardinality Occurs when the query optimizer estimates cardinality on a relational expression.
sqlserver query_optimizer_force_both_cardinality_estimation_behaviors Both traceflags 2312 and 9481 were enabled, attempting to force both old and new cardinality estimation behaviors at the same time. The traceflags were ignored. Disable one or both of the traceflags.
sqlserver query_store_failed_to_capture_query Fired if the Query Store failed to capture query. The Query Store will not track statistics for this query
sqlserver query_store_failed_to_load_forced_plan Fired if the query failed to load forced plan from QDS. Forcing policy will not be applied
sqlserver query_store_persist_on_shutdown_failed Occurs when SQL Server fails to store dirty entries in Query Store on database shutdown.
sqlserver recovery_indirect_checkpoint Chose to enqueue an indirect checkpoint as we are near our recovery target
sqlserver remove_database_cache Remove database cache
sqlserver server_max_workers Occurs when a request enqueue fails because a worker was not guaranteed.
sqlserver session_recoverable_state_change Occurs when the server determines a state change in term of session recovery on a connectionresilency-enabled connection.
sqlserver sql_transaction_commit_single_phase Occurs when a sql transaction is committed with single phase commit.
sqlserver xfcb_blob_properties_obtained Windows Azure Storage blob property is obtained from response header.
sqlserver xfcb_failed_request Failed to complete a request to Windows Azure Storage.
sqlserver xfcb_header_obtained Response header is obtained from request to Windows Azure Storage.
sqlserver xfcb_read_complete Read complete from Windows Azure Storage response.
sqlserver xfcb_request_opened A request is opened to Windows Azure Storage.
sqlserver xfcb_send_complete Request send to Windows Azure Storage is complete.
sqlserver xfcb_write_complete Request send to Windows Azure Storage is complete.
sqlserver xtp_create_procedure Occurs at start of XTP procedure creation.
sqlserver xtp_create_table Occurs at start of XTP table creation.
sqlserver xtp_db_page_allocation_allowed Indicates that page allocations for the database are allowed.
sqlserver xtp_db_page_allocation_disallowed Indicates that page allocations for the database are disallowed due to memory pressure.
sqlserver xtp_deploy_done Occurs at completion of XTP object deployment.
sqlserver xtp_matgen Occurs at start of MAT generation.
sqlserver xtp_offline_checkpoint_scan_start Fired by XTP offline checkpoint when the checkpoint thread begins.
sqlserver xtp_offline_checkpoint_scan_stop Fired by XTP offline checkpoint when the checkpoint thread stops.
sqlserver xtp_recover_done Occurs at completion of log recovery of XTP table.
sqlserver xtp_recover_table Occurs at start of log recovery of XTP table.
sqlserver xtp_storage_table_create Occurs at just before the XTP storage table is created.
ucs ucs_connection_rejected_by_proxy_whitelist After a connection attempt to the UCS proxy endpoint is rejected by whitelist check
ucs ucs_proxy_connect_next_hop UCS proxy next hop connection
ucs ucs_proxy_receive_proxy_connect_message UCS proxy receive proxy connect message
ucs ucs_proxy_route_add UCS proxy route added
ucs ucs_proxy_route_disable UCS proxy route disabled
ucs ucs_proxy_route_refresh UCS proxy route refreshed
ucs ucs_proxy_send_proxy_connect_message UCS proxy send proxy connect message
XtpCompile cgen Occurs at start of C code generation.
XtpCompile invoke_cl Occurs prior to the invocation of the C compiler.
XtpCompile mat_export Occurs at start of MAT export.
XtpCompile pitgen_procs Occurs at start of PIT generation for procedures.
XtpCompile pitgen_tables Occurs at start of PIT generation for tables.
XtpEngine after_changestatetx_event Fires after transaction changes state.
XtpEngine alloctx_event  
XtpEngine attempt_committx_event Is raised when a transaction is asked to commit.
XtpEngine before_changestatetx_event Fires before transaction changes state.
XtpEngine dependency_acquiredtx_event Raised after transaction takes a dependency on another transaction.
XtpEngine endts_acquiredtx_event Fires after transaction acquires an end timestamp.
XtpEngine gc_base_generation_evaluation Indicates that an evaluation of updating the GC base generation has been made.
XtpEngine gc_base_generation_updated Indicates that the oldest active transaction hint used for calculating the GC base generation has been updated.
XtpEngine gc_cycle_completed Indicates that a GC notification has been enqueued.
XtpEngine gc_notification Indicates that GC is processing a notification.
XtpEngine redo_single_hk_record Redo on a HK log record
XtpEngine trace_add_delta_filter_begin Adding delta filter.
XtpEngine trace_add_duplicate_delta_filter Adding duplicate delta filte.
XtpEngine trace_adding_tx_filter Adding tx filter.
XtpEngine trace_begin_close_ckpt_processing Beginning close checkpoint processing: checkpoint flush fence = $2 ($3 state).
XtpEngine trace_cfd_entry_deleted Cleaned up data CFD entry for file $2 $3.
XtpEngine trace_cfd_rows_created Successfully created CFD rows.
XtpEngine trace_cfdtable_dump CfdTable dump
XtpEngine trace_cfp_handles_opened Opened CFP handles for {data: $2 delta: $3} in section $4.
XtpEngine trace_cfp_removed_from_freelist Removed CFP from freelist for destination file.
XtpEngine trace_checkpoint_file_flush Indicating that a file has started a flush operation.
XtpEngine trace_checkpoint_load_begin Starting Checkpoint load with checkpoint timestamp=$2.
XtpEngine trace_checkpoint_write_io Logs an event indicating that an IO has started (when event_type == IoStarted) or that an IO has completed (when event_type == IoComplete).
XtpEngine trace_checksum_validation_succeeded Checksum validation for Hekaton checkpoint file during backup succeeded.
XtpEngine trace_ckpt_close_begin Closing checkpoint in active/passive state.
XtpEngine trace_ckpt_close_signaled Signaling checkpoint close. The checkpoint policy object is flagged to close the current checkpoint
XtpEngine trace_ckpt_closed Checkpoint closed
XtpEngine trace_ckpt_load_thread_stats Statistics of the checkpoint load thread.
XtpEngine trace_ckpt_mrt_dump Mrt dump
XtpEngine trace_ckpt_serialization_state_dump Checkpoint serialization state dump.
XtpEngine trace_ckpt_stream_io_stats Io statistics for this particular stream.
XtpEngine trace_close_file_log_rec_created Successfully created close file log record.
XtpEngine trace_data_file_pages_txs This event gives the number of pages and the transaction range for a data file.
XtpEngine trace_data_from_page_deserialized Deserialized data from page $2 in file $4.
XtpEngine trace_data_row_modified Data row modified/deleted.
XtpEngine trace_delete_cfd_rows_begin Matching MRT not found for the Data CFD. Deleting corresponding CFD Rows.
XtpEngine trace_delete_DeletedObjectTable_row_begin Deleting row from DeletedObjectTable in the context of transaction: $5.
XtpEngine trace_delete_merged_source_file Deleting merged source $7 file $5:$6. DeleteLSN = $2:$3:$4.
XtpEngine trace_delete_mrtrow_for_target_begin Deleting mrt row for target.
XtpEngine trace_delta_entry_skipped Skipping delta {$3, $4, $5} because it is beyond the snapshot checkpoint: $6.
XtpEngine trace_delta_file_future_count_rows_pages_update Updating future count pages and count rows for delta file.
XtpEngine trace_delta_file_lastgood_count_rows_pages_update Updating lastgood count pages and count rows for delta file.
XtpEngine trace_delta_file_pages_txs This event gives the number of pages and the transaction range while reading a delta file for a particular operations.
XtpEngine trace_delta_file_processed Completed processing of Delta file.
XtpEngine trace_delta_file_set_clean_or_dirty Delta file set clean or dirty.
XtpEngine trace_delta_filter_removed Removing delta filter from DeltaFilterMap due to transaction filter.
XtpEngine trace_delta_watermark_removed Removed delta watermark.
XtpEngine trace_delta_watermark_updated Updated delta watermark row for FileId: $4.
XtpEngine trace_deserialize_chained_records Info pertaining to the deserialized content of the mrtRecords chain.
XtpEngine trace_deserialize_data_from_page_begin Deserializing data from page $2 in file $4.
XtpEngine trace_deserialize_source_delta_file_begin Deserializing source delta file $4: page range [$2, $3).
XtpEngine trace_dump_cfd_row Dump cfd row
XtpEngine trace_dump_mrt_row Merge Request
XtpEngine trace_file_close_skipped Skipping file close for $2 as the cfp had been merged, It could not be found in the Storage array.
XtpEngine trace_file_skipped Skipping file $4 due to reason $5.
XtpEngine trace_file_skipped_with_tx_range Skipping file $5 with TX range { $3 -> $4 } due to MaxTxId == InvalidTxId.
XtpEngine trace_first_page_header_flushed Flushed first page header for file.
XtpEngine trace_flush_io_operation_for_delta_file This trace is trying to catch pages issued for flush that didn't make it to the file, or were lacking the correct DeltaWatermark Timestamp.
XtpEngine trace_flush_skipped_for_closed_data_file Skipping flush for data file ('$2') as it is closed.
XtpEngine trace_future_count_pages_update Updating future count pages.
XtpEngine trace_initial_merge Doing initial merge for destination $3 at CheckpointTs: $2.
XtpEngine trace_insert_DeletedObjectTable_row_begin Inserting row to DeletedObjectTable in the context of transaction: $5.
XtpEngine trace_intialize_worker_file_begin Processing file close.
XtpEngine trace_mark_data_file_closed Marking the active data file as closed to prevent the offline worker fom flushing them.
XtpEngine trace_merge_cancelled Merge was cancelled for destination $2.
XtpEngine trace_merge_complete_log_rec_ignored MergeCompleteLogRecord ignored for destination file $3.
XtpEngine trace_merge_complete_log_rec_processed Successfully processed MergeCompleteLogRecord.
XtpEngine trace_mrt_dump Mrt dump.
XtpEngine trace_mrt_row_inplace_update Mrt row updated in place.
XtpEngine trace_mrtrow_not_found Could not find row with Destination ID: $2 in MRT table.
XtpEngine trace_new_delta_watermark_inserted Inserted new delta watermark row for FileId $3.
XtpEngine trace_offline_process_stale_merge_item Offline processing of stale merge item {$2, $3} Destination $4.
XtpEngine trace_pending_tx Pending Transaction
XtpEngine trace_populate_storage_array Populating the storage array.
XtpEngine trace_post_writefile This event is fired after issuing IO on the proxy.
XtpEngine trace_process_file_close_begin Processing file close.
XtpEngine trace_process_merge_complete_log_record Process merge complete log record.
XtpEngine trace_process_merge_request Process Merge Request
XtpEngine trace_process_source_file Processing source $4 file $2.
XtpEngine trace_process_target_file Processing target $4 file $2.
XtpEngine trace_queue_merge_work_item Queue work item: {$2, $3} $5 - $6 with CheckpointTs as $4.
XtpEngine trace_recovered_existing_file Recovered exising files files allocated during forward processing that are not already part of the CCB.
XtpEngine trace_resyncing_state_of_file Resyncing state of file $4 ('$5') post recovery:  $2 pages, $3 rows.
XtpEngine trace_serialization_object_cleaned Cleaned serialization object.
XtpEngine trace_serialize_abort_transaction_begin Serializing the abort transaction filter to the delta cache corresponding to its own transaction range.
XtpEngine trace_serialize_tx_begin Serialize TS begin for completing the checkpoint in active state.
XtpEngine trace_serialize_tx_end Serialize TS end for completing the checkpoint.
XtpEngine trace_serialized_delta_rows_to_file Serialized delta rows to file.
XtpEngine trace_source_file_load_stats Load stats for source files in merge.
XtpEngine trace_storage_refreshed_for_file Storage refreshed for file.
XtpEngine trace_uninitialize_file uninitializing_file
XtpEngine trace_wrote_delta_watermark Wrote delta watermark for file: $3, checkpointTs: $2.
XtpEngine waiting_for_dependenciestx_event Raised when we have explicitly waited for dependencies to clear.
XtpEngine xtp_before_create_log_record Fired before submitting a request to the host to serialize a non-transactional log record.
XtpEngine xtp_checkpoint_file_flush Indicates the point at which a given file has been flushed to disk.
XtpEngine xtp_checkpoint_file_flush_complete Indicates the point at which all in-flight buffers have been flushed to disk.
XtpEngine xtp_checkpoint_file_read Indicates reading of a file in XTP checkpoint recovery.
XtpEngine xtp_checkpoint_worker_active_log Indicates that the checkpoint worker has started processing the log records in the active portion of the log.
XtpEngine xtp_checkpoint_worker_started Indicates that the checkpoint worker has started processing the log records.
XtpEngine xtp_checkpoint_worker_stopped Indicates that the checkpoint worker has started processing the log records.
XtpEngine xtp_checkpoint_write_io Indicates that the checkpointing subsystem has issued or completed a write IO.
XtpEngine xtp_complete_checkpoint Indicates the begin and end of complete checkpoint processing.
XtpEngine xtp_create_log_record Fires when the XTP engine creates a log record.
XtpEngine xtp_delta_marked_dirty Indicates that a delete/update was serialized and marked a delta file dirty
XtpEngine xtp_merge_complete_log_record Indicates merge complete log record is posted to the log.
XtpEngine xtp_merge_log_complete_log_record Indicates merge log record was interpreted by the offline worker.
XtpEngine xtp_merge_process_log_record Indicates merge log record was interpreted by the offline worker.
XtpEngine xtp_merge_request_log_record Indicates merge request log record is posted to the log.
XtpEngine xtp_merge_request_started Indicates merge request has been picked up by the merge worker thread.
XtpEngine xtp_root_deserialized Indicates that the load of a checkpoint root is complete.
XtpEngine xtp_root_serialized Indicates that the write of the checkpoint root is complete.
XtpRuntime bind_md Occurs prior to binding metadata for a memory optimized table.
XtpRuntime bind_tables Occurs prior to binding tables for a natively compiled procedure.
XtpRuntime create_table Occurs prior to creating memory optimized table.
XtpRuntime deserialize_md Occurs prior to deserializing metadata.
XtpRuntime load_dll Occurs prior to loading the generated DLL.
XtpRuntime recover_done Occurs at completion of checkpoint recovery of a memory optimized table.
XtpRuntime recover_table Occurs at start of checkpoint recovery of a memory optimized table.
XtpRuntime serialize_md Occurs prior to serializing metadata.
XtpRuntime unload_dll Occurs prior to unloading the generated DLL.

Happy tracing,

 

Andreas

Die Agenda der SQL Server Konferenz 2014 ist online!

Vom 10.-12. Februar 2014 findet in Darmstadt die erste Deutsche SQL Server Konferenz statt. Zeitgleich feiert die PASS Deutschland e.V. ihr 10-jähriges Bestehen. Also Geburtstag :-)

Seit heute steht auch die Agenda fest.
Es gibt 4 Tracks:

  • Administration (DBA)
  • Business Intelligence (BI)
  • Entwicklung (DEV)
  • Big Data & Information Management (BD)

Ich darf im Admin-Track gleich in der ersten Session In-Memory OLTP mit SQL Server 2014 XTP (Project Hekaton, jetzt "EXtreme Transactional Processing") vorstellen.

- Nachdem ich mich bereits intensiv mit Columnstore Indexes beschäftigt habe, ist das die zweite wichtige und komplett neue Technologie, in die ich mich rechtzeitig zur ersten Präsentation von Hekaton/XTP im November 2013 auf Microsoft’s TechNet Conf eingearbeitet habe. „Mission accomplished“. :-)
An dieser Stelle ein Dankeschön an die Veranstalter für das in mich gesetzte Vertrauen.

Danach geht es gleich weiter mit neuem heißen Content: mein geschätzter Kollegen Niko Neugebauer, hält einen Deep Dive in die neuen Rewritable Columnstore Indexes. Eine In-Memory Analytics Engine & Memory-Optimized Columnstore Index, die die Architektur von künftigen DataWarehouses verändert wird, wie ich schon diesen Sommer „prophezeite“. (The Columnstore Indexes & Batch Mode Processing (R)Evolution in SQL Server 2014)

Am 2. Tag der Hauptkonferenz geht es im Administrationstrack um den Bereich SQL Azure, Virtualisierung und Hochverfügbarkeitsarchitekturen mit AlwaysOn. Hierzu werden sogar Sprecher vom SQL CAT Team präsentieren.

Und auch die Tracks Business Intelligence, Entwicklung und Information Management bieten hochkarätige Themen und international erfahrene Sprecher.
Herausheben möchte ich an dieser Stelle das Gespann der Kollegen Georg Urban & Olivia Klose von Microsoft zu Big Data mit HDInsight oder Tillmann Eitelberg zu SSIS und HDInsight.

Artus Krohn-Grimberghe & Martin Vach stellen mit Predictive Analytics mit SQL Server & Project Passau ein DataMining Projekt vor

Wem die jeweils einstündigen Vorträge nicht genügen, sondern gleich richtig in ein Thema einsteigen möchte, dem empfehle ich eine der 3 PreCons, die am 10.2. stattfinden. – PreCons sind ganztägige Veranstaltungen, die sich intensiv einem bestimmten Themenkomplex widmen.
Und das ist die Auswahl:

Power Workshop 1: Azure Platform Immersion Day: Building Highly Available and Scalable Solutions in a Hybrid World
Trainer: tbd

Power Workshop 2: In-Memory Technologies im SQL Server 2014 – Die Performance Booster
Trainer: Niko Neugebauer & Andreas Wolter

-           In diesem “gemischten Doppel” zeigen wir die neuen Storage Engines im SQL Server 2014: ColumnStore Indexes und In-Memory OLTP

Power Workshop 3: Microsoft Self-Service BI Landschaften – Professionell implementiert
Trainer: Kay Unkroth


Die vollständige Agenda findet sich hier: www.sqlkonferenz.de/Agenda.aspx

 

Ich hoffe man sieht sich in im Februar in Darmstadt

 

Andreas

SQL Server 2014 CTP2 auf PASS Summit Released / öffentlicher Download / public download

(de)
Wie ich erwartet hatte, wurde heute am 16.10. auf dem SQL PASS Summit in Charlotte USA, die CTP 2 des SQL Server 2014 veröffentlicht. :-)
Die Final erwarte ich noch im ersten Quartal 2014 herauszukommen.

Hier ist der Link zum Download:

(en)
As I have expected, today, Oct. 16th the CTP 2 of SQL Server 2014 was published at dem SQL PASS Summit in Charlotte USA. :-)
I am expecting the final to come along in the first quarter of 2014.

Here is the link to the download:

 

technet.microsoft.com/en-US/evalcenter/dn205290

 

SQL_Server_2014_CTP2_VersionInfo

 

Ein kurzer Überblick über die Neuheiten im Bereich der Datenbank-Engine in diesem Release – zusätzlich zu den Neuerungen aus CTP1, über die ich hier schon berichtete:

 

  • AlwaysOn
    • Bis zu 8 Sekundäre Replikas
    • Bei Quorum-Verlust bleiben read-only Replicas zum Lesen verfügbar
    • Failover cluster instances (FCIs) können Cluster Shared Volumes als cluster shared disks in Windows Server 2012 und höher verwenden
  • Datenbank Engine
    • Single Partition Rebuilding
    • Lock Priority Management für Online-Operationen
    • Incrementelle Statistiken
    • Resource Governor Unterstützung für physical IO
    • Neue „Erweiterte Ereignisse“ (XEvents): 775 insgesamt, 152 mehr als SQL 2012 SP1, 12 seit CTP1
  • In-Memory OLTP aka Extreme Transactional Processing (XTP, vormals Codename “Hekaton”)
    • B-tree Indexe (Hash-Indexe seit CTP1)
    • Delayed Transaction Durability

A short overview on the New Features in the Database-Engine part in this release – cumulative to the new features from CTP1, which I have listed here already before:

 

  • AlwaysOn
    • Up to 8 secondary replicas
    • In case of loss of quorum, read-only replicas remain available for read-access
    • Failover cluster instances (FCIs) can use Cluster Shared Volumes as cluster shared disks in Windows Server 2012 and above
  • Database Engine
    • Single Partition Rebuild
    • Lock Priority Management for Online-Operations
    • Incremental Statistics
    • Resource Governor support for physical IO
    • New Extended Events: 775 total, 152 more than SQL 2012 SP1, 12 since CTP1
  • In-Memory OLTP  aka Extreme Transactional Processing (XTP, formerly Codename “Hekaton”)
    • B-tree indexes (Hash indexes since CTP1)
    • Delayed Transaction Durability

 

 SQL_Server_2014_CTP2_Delayed_Durability_Database_Option

 

  • Sicherheit:

 Security:

  • Encrypted Backups
  • New permissions (more on that soon in my blog, especially also in addition to the „CONTROL SERVER“-Topic

 

 SQL_Server_2014_CTP2_Backup_Encryption_SSMS

 

In Reporting Services und Integration Services gibt es keine Änderungen zu vermelden.

Die komplette Liste ist hier zu finden: msdn.microsoft.com/de-de/library/bb510411%28v=sql.120%29.aspx

Dazu kommen viele Neuerungen im Zusammenspiel mit SQL Azure und im BI & Information Services Bereich. Über diese in Detail zu Berichten, überlasse ich meinen geschätzten Kollegen. Ich werde hier auf die entsprechenden Blog-Posts verweisen sobald diese fertig sind.

Hinweis: Ein Upgrade der CTP1 zur CTP2 ist nicht unterstützt.

In Reporting Services and Integration Services there are no changes.

The complete list can be found here: msdn.microsoft.com/en-us/library/bb510411(v=sql.120).aspx

Added to that there are many new enhancements connected with SQL Azure and in the area of BI & Information Services. I will leave detailed reports on those to my valued colleagues and will link to the respective blog-posts once ready.

Note: Upgrading from CTP1 to CTP2 is not supported.

 

Have fun with SQL Server 2014 CTP2

 

Andreas

1 2