SQL Server & BI Blog by Andreas Wolter

Liebe Leser
dieser Blog ist hiermit nur noch „Archiv“ und wird nicht mehr weiter gepflegt.
Seit August 2017 finden sich neue Artikel ausschließlich unter der neuen URL:
http://andreas-wolter.com/blog/

Dear Readers
this blog is now merely an „archive“ and no longer being updated.
Since August 2017 new articles are exclusively available under the new URL:
http://andreas-wolter.com/en/blog/

Die aufwändige Mehrsprachigkeit (Deutsch und Englisch professionell manuell übersetzt) wird beibehalten – aber Layout-technisch anders gelöst. Damit dürfte ich immer noch den einzigen mehrsprachigen IT-Blog weltweit betreiben.
Ich hoffe, das neue Design gefällt Ihnen.

The complex multilingualism (German and English professionally manually translated) is being continued – but solved differently in terms of layout. With that I most likely still operate the only multilingual IT-Blog worldwide.
I hope you like the new design.

Mein aktueller Artikel, der erstmalig ausschließlich auf der neuen Website zu finden ist, lautet: Optimieren von Workflows mit In-Memory und nativ kompilierten Objekten - oder wie es nicht funktioniert

My currently last article, which is exclusively available at the new website for the first time, is Optimizing workflows with In-Memory and Natively Compiled Objects - or how it does not work

(DE)
Mit dem Aufkommen der Columnstore Technologie und auch der In-Memory Technologie in SQL Server 2014 sind insgesamt 4 neue Indextypen zu den althergebrachten page-basierten hinzugekommen.

Wo es früher nur die Auswahl an „Clustered oder Non-Clustered“ (Deutsch auch „gruppiert oder nicht-gruppiert“) gab, gibt es mittlerweile auch Clustered Columnstore, Non-Clustered Columnstore, Nonclustered Hash und Nonclustered Range-Indexe.

Und seit SQL Server 2016 sind diese Indextypen untereinander noch weiter kombinierbar.
Vereinfacht ausgedrückt lassen sich nun sowohl die Page-basierten Indexe als auch die Memory-optimierten Tabellen mit Columnstore Indexen kombinieren.
Page(„Disk“)-basiert + Memory-optimiert funktioniert jedoch nicht.

(EN)
With the emergence of the Columnstore technology and the In-Memory technology in SQL Server 2014, a total of 4 new index types have been added to the conventional page-based types.

When previously there had only been the choice of “Clustered or Non-Clustered,” now there are also Clustered Columnstore, Non-Clustered Columnstore, Nonclustered Hash und Nonclustered Range-Indexes.

And since SQL Server 2016, these index types can be further combined. Simply put, now it is possible to combine both page-based indexes and memory-optimized tables with Columnstore indexes.
However, Page (“Disk”)-based + Memory-optimized does not work.

Um auf schnelle Weise sehen zu können, welche Kombination an Indexen möglich ist, habe ich eine Matrix erstellt, welche die Kombinationsmöglichkeiten darstellt.
Angereichert mit Zusatzinformationen kann man den „Index-Spickzettel“ in Form einer pdf herunterladen. Und so sieht er dann aus:

In order to quickly determine which combination in indexes is possible, I created a matrix that illustrates the combination options.

Completed with additional information, you can download the “index cheat sheet” as a PDF document. This is how it looks:

Download

Herunterladen kann man es hier bei Technet:

Download

You can download it here at Technet:

Insgesamt sind gibt es also mittlerweile 4 verschiedene Basis-Strukturen:

1. Heaps
2. Clustered Indexe page-basiert
3. Clustered Columnstore Indexe und
4. sogenannte varHeaps, die Struktur der memory-optimierten Tabellen.

In total, there are now 4 different basic structures:

1. Heaps
2. Clustered page-based indexes
3. Clustered Columnstore indexes and
4. so-called varHeaps, the structure of memory-optimized tables

- Nicht enthalten sind: Indexed Views, XML-Indexe und Räumliche (Spatial) Indexe, sowie die Implikationen bei Sparse-Columns.
Prinzipiell basieren auch diese alle noch auf den page-basierten b-Tree Indexen, sind durch die teilweise auf der CLR basierenden Datentypen jedoch wesentlich eingeschränkter.

- Unique Indexe habe ich nicht gesondert betrachtet, da sich diese für meine Betrachtung nicht anders als ihre Nicht-Unique Pedanten verhalten.

- Not included are: Indexed Views, XML-Indexes and spatial indexes, as well as the implications in Sparse-Columns.

Generally, the latter are all still based on the page-based b-Tree indexes, but are significantly more restricted due to the data types partially based on CLR.

- I did not consider Unique Indexes separately as for the purpose of my consideration they do not behave differently to the way their non-unique counterparts do.

Zusätzlich habe ich noch einige Grundregeln und Höchstgrenzen mit aufgenommen.

Dem aufmerksamen Leser wird dabei vielleicht auffallen, dass die maximale Anzahl der Index-Spalten in SQL Server 2016 von 16 auf 32 angehoben worden ist. – Bitte seht das aber nicht als Einladung an, diese Limit auch nur Ansatzweise auszunutzen!

In addition, I have included a few basic rules and maximum limits.

The attentive reader may notice that the maximum number of index columns in SQL Server 2016 has increased from 16 to 32. – But please do not regard it as an invitation to even attempt to exploit these limits!

Ich hoffe diese Übersichten sind eine hilfreiche Erinnerungsstütze nicht  nur für Einsteiger, sondern auch für solche, die sich nicht immer an jede Regel erinnern können. Zum Weiterlesen sind auch einige Links auf BOL hinterlegt.

I hope these overviews are a helpful mnemonic device not only for beginners but also for those who don’t always remember every single rule. Links for further readings at BOL are also listed.

Hinweis: Die Übersicht basiert auf dem aktuellen Stand der Technik: SQL Server 2016.

Bereits gegenüber SQL Server 2014 gibt es wesentliche Unterschiede, und viel mehr noch zu 2012.
Kurz zusammengefasst gab es in der Version 2012 noch keine Memory-optimierten Tabellen und Clustered Columnstore Indexe. Diese kamen erst 2014 in den SQL Server. Und erst im 2016er gibt es die Kombinationsmöglichkeiten.

Folgende Artikel gab es zu diesen Themen bereits von mir:

Note: This overview is based on the current technical state: SQL Server 2016.

There are already significant differences to SQL Server 2014, and even more to 2012.
In a nutshell, in the 2012 version, there had not been any memory-optimized tables or Clustered Columnstore indexes. They only made it into the SQL Server in 2014. And only with the 2016 SQL Server there are the combination possibilities.

The following articles on these topics I have published before:

(en)
Today I redeemed a promise made long ago: To provide the deadlock-collector framework, which I developed for use at my customers’, as a Codeplex project.

Until now I have only given it out to attendees of my SQL Server Master-Classes and a few fellow Microsoft Certified Masters. From today on it is freely available to everyone.

(de)
Heute habe ich ein Versprechen eingelöst, das ich vor langer Zeit gemacht habe: Und zwar das Framework für den Deadlock-Collector, das ich zum Einsatz bei meinen Kunden entwickelt habe, als Codeplex-Projekt bereitzustellen.

Bisher habe ich es nur an Teilnehmer meiner SQL Server Master-Classes und einige Microsoft Certified Master-Kollegen herausgegeben. Ab heute ist es für jeden frei verfügbar.

If you just want to get it real quick, here is the link to the Codeplex project where you can download the necessary objects:

>> sqldeadlockcollector.codeplex.com <<

For more information on how to set up the solution and what kind of analysis it enables read below.

Wenn Sie ihn einfach schnell haben möchten, ist hier der Link zum Codeplex-Projekt, wo Sie die nötigen Objekte herunterladen können:

>> sqldeadlockcollector.codeplex.com <<

Weitere Informationen dazu, wie man die Lösung aufstellt und welche Art von Analyse sie ermöglicht, finden Sie im Folgenden.

Contents

1. Description
2. Technical Implementation
3. Supported SQL Server Versions & Editions
4. Setup High-level
5. Setup the Jobs
6. Integration of the Reports
7. Usage for Analysis
8. The Reports
9. Availability
10. Notes & Recommendations
11. Call to Action: Quick Poll

 Inhalte

1. Beschreibung
2. Technische Umsetzung
3. Unterstützte SQL Server Versionen & Editionen
4. Setup High-level
5. Setup der Aufträge
6. Integration der Berichten
7. Nutzung zur Analyse
8. Die Berichte
9. Verfügbarkeit
10. Hinweise & Empfehlungen
11. Call to Action: Kurze Umfrage

Description

The SQL Server Deadlock-Collector collects information which can be read out from the ring buffer in the memory of the SQL Server, and stores this information in a database-table.

Aloof from the pure deadlock graph, the data is parsed, and the first 2 respective processes of the deadlock graph are contrasted in 2 columns, respectively, with information such as “process ID, SQL/procedure text, host name, application name” etc. (procedure name is only available from SQL Server 2012 onwards because of incomplete xml in the former version).

Beschreibung

Der SQL Server Deadlock-Collector sammelt Informationen, die aus dem Ring-Buffer im Speicher des SQL Server ausgelesen werden können und speichert diese Informationen in einer Datenbanktabelle.

Abseits vom reinen Deadlock-Graphen werden die Daten zerlegt und die ersten 2 jeweiligen Prozesse des Deadlock-Graphs werden in jeweils 2 Spalten gegenübergestellt, mit Informationen wie z.B. „Prozess-ID, SQL/Prozedur Text, Hostname, Anwendungsname“ etc. (Prozedur-Name ist aufgrund unvollständigem xml in der vorherigen Version erst ab SQL Server 2012 verfügbar).

This way, one obtains a tabular representation of the deadlock processes. Since there may be more than 2 involved processes, the complete graph is always also present. (red in the image below)

Auf diese Weise erhält man eine tabellarische Darstellung der Deadlock-Prozesse. Da es mehr als 2 beteiligte Prozesse geben kann, ist der komplette Graph auch immer präsent. (rot in der Abbildung unten)

With the aid of a few sample reports, administrators will obtain a quick overview on the frequency of the occurring deadlocks in the respective databases and be able to pass on the data for further analysis to the developers or external analysis even by simply providing a copy/backup of the database.

Developers will thus obtain a valuable and quickly interpretable version of the occurring deadlock combination and be able to implement prevention strategies with this information.

Mithilfe einiger Beispielberichte erhalten Administratoren eine schnelle Übersicht über die Häufigkeit der auftretenden Deadlocks in den jeweiligen Datenbanken, und können die Daten darüber sogar durch das einfache Bereitstellen einer Kopie/eines Backups der Datenbank an die Entwickler oder externe Analysten weitergeben.

Entwickler erhalten damit eine wertvolle und schnell interpretierbare Version der auftretenden Deadlock-Kombinationen, und können mit diesen Informationen Vermeidungsstrategien umsetzen.

Technical Implementation

 The complete SQL Deadlock Collector & Parser -solution consists of 3 (small) databases, a SQL Server Agent job and 3 example Reporting Services Reports for Management Studio integration.

Through the utilization of the information already existing in memory by the Extended Events system_health-session, the SQL Deadlock Collector & Parser is absolutely lightweight and has scarcely any impact on a productive system. Solely at execution time of the data collection job (which is configurable at free will, every 6-24 hours are common), minimal resources are required.

Technische Umsetzung

Die komplette SQL Deadlock Collector & Parser – Lösung besteht aus 3 (kleinen) Datenbanken, einem SQL Server Agent Auftrag und 3 Beispiel- Reporting Services Berichten Management Studio Integration.

Durch die Nutzung der durch die system_health-Extended Events Session bereits im Arbeitsspeicher existierenden Informationen ist der SQL Deadlock Collector & Parser absolut leichtgewichtig und hat kaum eine Auswirkung auf ein produktives System. Lediglich zur Ausführungszeit des Datensammlungsauftrags (der frei konfigurierbar ist, alle 6-24 Stunden sind dabei gängig) werden minimale Ressourcen erfordert.

Supported SQL Server Versions & Editions

The SQL Deadlock Collector & Parser is designed to run on all SQL Server versions and editions from SQL Server 2008 SP2 onwards.

The provided example reports for Management Studio Integration only work with Management Studio 2012 onwards. Since SSMS is available in a free Express Edition, being mostly backwards compatible to SQL Server 2008 / R2, this is a minor constraint.

Here you can get SSMS Express in Version 2014:

www.microsoft.com/en-US/download/details.aspx?id=42299

Unterstützte SQL Server Versionen & Editionen

Der SQL Deadlock Collector & Parser ist so ausgelegt, dass er auf allen SQL Server Versionen und Ausgaben ab SQL Server 2008 SP2 läuft.

Die bereitgestellten Beispielberichte für Management Studio Integration funktionieren nur mit Management Studio 2012 und höher. Da SSMS als kostenlose Express Edition verfügbar ist, und dabei rückwärts kompatibel mit SQL Server 2008 / R2 ist, ist dies eine geringe Einschränkung.

Hier können Sie die SSMS Express als Version 2014 erhalten:

www.microsoft.com/de-DE/download/details.aspx?id=42299

Setup High-level

The SQL Deadlock Collector & Parser is available in the form of backup files of the respective databases which are 3 in total:

• SQL_Analysis_Data
• SQL_Analysis_Code
• SQL_Analysis_Reporting

As can be seen by means of the denominations, in this relatively small project, too, it is being proceeded according to proven Sarpedon Quality Lab methodology of separating data, code and reporting. Thus, updates are being facilitated. 

The 3 databases must be set up on the same server.

 Setup High-level

Der SQL Deadlock Collector & Parser ist in Form von Backup-Dateien der jeweiligen Datenbanken verfügbar, insgesamt 3:

• SQL_Analysis_Data
• SQL_Analysis_Code
• SQL_Analysis_Reporting

Wie anhand der Bezeichnungen zu erkennen ist, wird auch in diesem relativ kleinen Projekt nach bewährter Sarpedon Quality Lab Methodik der Trennung von Daten, Code und Reporting vorgegangen. So werden Updates erleichtert.

Die 3 Datenbanken müssen auf demselben Server eingerichtet werden.

Setup instructions:

1. Recover the 3 databases in the respective version (2008 or 2012) onto your Server
1. The “2008”-Version targets all Systems from SQL Server 2008 SP2 up to SQL Server 2008 R2 SP2
2. The “2012”-Version targets all Systems from SQL Server 2012 RTM CU3 up to SQL Server 2014 RTM CU6
3. All databases have to reside on the same monitored SQL Server Instance. If multiple Instances are target of collection, the deadlock collector’s databases and jobs have to be installed on each single instance. Multi-Instance-collection is not implemented
2. Set up the collection Job
3. Add a schedule for the collection
4. Repeat the same for the maintenance job that removes old collected deadlocks after a specified timeframe
5. Optional: integrate sample custom reports in SSMS for Database ”SQL_Analysis_Reporting”

If multiple Instances are to be monitored, repeat each step for each instance

 Setup Anweisungen:

1. Stellen Sie die 3 Datenbanken in der jeweiligen Version (2008 oder 2012) auf Ihrem Server wieder her
1. Die “2008”-Version ist für alle Systeme ab SQL Server 2008 SP2 bis zum SQL Server 2008 R2 SP2 da
2. Die “2012”-Version ist für alle Systeme ab SQL Server 2012 RTM CU3 bis zum SQL Server 2014 RTM CU6 da
3. Alle Datenbanken müssen auf derselben überwachten SQL Server Instanz liegen. Wenn mehrere Instanzen Ziel der Datenerfassung sind, müssen die Datenbanken und Aufträge des Deadlock Collectors auf jeder einzelnen Instanz installiert werden. Multi-Instance-Collection ist nicht implementiert
2. Richten Sie den Sammler-Auftrag ein
3. Fügen Sie einen Zeitplan für die Datenerfassung hinzu
4. Wiederholen Sie das gleiche für den Maintenance Job, der alte erfasste Deadlocks nach einem festgesetzten Zeitrahmen entfernt
5. Optional: Integrieren der Beispiel-Custom Berichte in SSMS für die Datenbank ”SQL_Analysis_Reporting”

Wenn mehrere Instanzen überwacht werden sollen, wiederholen Sie alle Schritte je Instanz

Setup the Jobs

Subsequently, two SQL Server Agent Jobs should be installed which will be configured as follows:

Einrichten der Jobs

Danach sollten zwei SQL Server Agent Aufträge eingerichtet werden, die wie folgt konfiguriert werden:

Please note that the instructions in bold must be used exactly as stated above; all others are suggestions and can be changed to your according naming styles.

In addition to that, set up a time schedule (as always I recommend a dedicated but shared schedule). For instance, every 6-12 hours, depending on the number of errors in the system_health session which works in FIFO-mode.

Bitte beachten Sie, dass die fettgedruckten Anweisungen genau wie oben angegeben verwendet werden müssen; alle anderen sind Vorschläge und können Ihren Namensrichtlinien angepasst werden.

Stellen Sie zusätzlich dazu einen Zeitplan (ich empfehle wie immer einen dedizierten aber Shared Schedule). Zum Beispiel alle 6-12 Stunden, je nach Anzahl der Fehler in der system_health Session, die im FIFO-Modus arbeitet.

After installing the databases the first time, you will find a few rows of example deadlocks in the Date-Database – also accessible via the views from the Reporting-Database.
The sole purpose of those is to help understand how the SQL Deadlock Collector & Parser works.

Nachdem Sie die Datenbanken das erste Mal eingerichtet haben, werden Sie einige Zeilen an Beispiel-Deadlocks in der Data-Datenbank finden – ebenfalls erreichbar über die Sichten aus der Reporting-Datenbank.
Der alleinige Zweck dieser ist es, besser zu verstehen zu helfen, wie der SQL Deadlock Collector & Parser funktioniert.

You can delete those manually or by using the maintenance-procedure Locking.del_DeadLock as shown in the SQL Agent job-example.

Sie können diese manuell oder mithilfe der Wartungs-Prozedur Locking.del_DeadLock, wie im SQL-Agent Auftragsbeispiel gezeigt, löschen.

Integration of Reports

In SQL Server management Studio navigate to the database “SQL_Analysis_Reporting” and there in the menu go to Reports – Custom reports, to choose the 3 provided rdl-files (separate download) for integration.

Integration der Berichte

Navigieren Sie im SQL Server Management Studio zur Datenbank “SQL_Analysis_Reporting” und dort im Menü zu Berichte – Benutzerdefinierte Berichte, um die 3 bereitgestellten rdl-Dateien (gesonderter Download) zur Integration auszuwählen. 

When the warning pops up you need to say “run” in order to execute the reports.

Wenn die Warnung eingeblendet wird, müssen Sie „Ausführen“ auswählen, um die Berichte auszuführen. 

After that the 3 reports will show up in the list. For more information on Custom Reports in Management Studio read here: Custom Reports in Management Studio  

Danach werden die 3 Berichte in der Liste angezeigt. Für mehr Informationen zu benutzerdefinierten Berichten in Management Studio können Sie hier weiterlesen: Custom Reports in Management Studio  

Usage for Analysis

When deadlocks are collected, these can be analyzed in different ways with the help of sample views located in the database SQL_Analysis_Reporting. These range from general statistics down to detailed statements, resources and execution plans.

Verwendung zur Analyse

Wenn Deadlocks gesammelt werden, können diese auf verschiedenen Wegen mithilfe der Beispielsichten in der Datenbank SQL_Analysis_Reporting analysiert werden. Diese reichen von allgemeinen Statistiken bis zu detaillierten Statements, Ressourcen und Ausführungsplänen.

A special feature is the inclusion of the original query plans (provided that they are available in the cache at the moment of collection) of the first 2 involved processes – very handy for analysis of cause.

Ein besonderes Feature ist die Einbeziehung des originalen Ausführungsplanes (vorausgesetzt, dass diese zum Zeitpunkt der Sammlung im Cache verfügbar sind) der ersten beiden involvierten Prozesse – sehr praktisch bei der Ursachenanalyse.

A SQLHandle of 0xFF will be shown when the handle from the deadlock-graph cannot be resolved any more. This happens for trivial plans or when the plan has been evicted from cache since the deadlock occurred.

The complete deadlock graph can also be used to get a visual representation of the deadlock graph.

Ein SQLHandle von 0xFF wird angezeigt, wenn das Handle aus dem Deadlock-Graphen nicht mehr aufgelöst werden kann. Das geschieht bei trivialen Plänen oder wenn der Plan seit Auftreten des Deadlock aus dem Cache geworfen wurde.
Der komplette Deadlock-Graph kann auch verwendet werden, um eine visuelle Repräsentation des Deadlock Graphen zu erhalten.

Example of a simple deadlock with 2 processes.

 Beispiel eines einfachen Deadlocks mit 2 Prozessen.

Example of a multi-victim-deadlock with 4 processes.
Since a deadlock can involve an unforeseeable number of processes, but a database table by nature has a strict design, I not only keep the original complete deadlock graph but also extract the list of victims, processes and execution stacks into one xml-field each for simplified analysis.

Beispiel eines Multi-Victim-Deadlocks mit 4 Prozessen.
Da ein Deadlock eine unvorhersehbare Anzahl an Prozessen involvieren kann, aber eine Datenbanktabelle von Natur aus ein striktes Design hat, behalte ich nicht nur den originalen kompletten Deadlock-Graphen sondern extrahiere auch die Liste der Victims/Opfer, Prozesse und Execution Stacks in jeweils ein XML-Feld zur vereinfachten Analyse.

Clicking on the victim-list for a deadlock with 4 victims opens a new xml-window containing just the node of victims with their respective process-id for further, manual matching with the execution stack, for example

Klickt man auf die Victim-Liste eines Deadlocks mit 4 Victims öffnet sich ein neues xml-Fenster, welches nur den Knoten der Victims mit ihrer jeweiligen Prozess-ID für weiteres manuelles Verknüpfen mit zum Beispiel dem Execution Stack.

The Reports

On top of the views one can implement custom reports for reoccurring analysis. 3 example reports are included in the project and can be integrated into Management Studio directly. (Version 2012 and 2014)

Die Berichte

Basierend auf den Sichten kann man nutzerdefinierte Berichte für wiederholte Analysen implementieren. 3 Beispielberichte sind in dem Projekt enthalten und können direkt in Management Studio integriert werden. (Version 2012 und 2014)

Availability

The SQL Deadlock Collector & Parser is available as open source project under Microsoft Public License (Ms-PL) at codeplex:

sqldeadlockcollector.codeplex.com

Verfügbarkeit

Der SQL Deadlock Collector & Parser ist als Open Source Project unter Microsoft Public License (Ms-PL) auf Codeplex verfügbar:

sqldeadlockcollector.codeplex.com

Notes & Recommendations

1) The event-time in SQL Server 2008 may strongly deviate (hours and days!) due to a mistake in the XML-code of the system_health session. Please also see the following Connect Item:

Hinweise & Empfehlungen

1) Die Event-Zeit unter SQL Server 2008 kann aufgrund eines Fehlers im XML-Code der system_health session stark abweichen (Stunden und Tage!). Siehe dazu das folgende Connect Item:

2) In order to make sure that larger deadlock graphs are also captured, I recommend increasing the maximal memory of the system_health session as one sees fit.

2) Um sicherzustellen, dass auch größere Deadlock-Graphen erfasst werden, empfehle ich, den maximalen Speicher der system_health session nach eigenem Ermessen zu erhöhen.

3) Der Umfang der Data-Datenbank kann mit der Zeit stark wachsen. Verwenden Sie die Aufräum-Prozedur (Locking.del_DeadLock) in der Code-Datenbank, um alte Deadlock Einträge zu entfernen.

Call to Action: Quick Poll

I absolutely invite you to use my code free of charge and comment on any problems you may have or improvements you would like to see.

All I would love for you to do is send me a high level statistics of what kind of deadlocks you see on your systems.

Simply run the following query after you have collected new deadlocks, and send me the result, purely consisting of the Number of affected processes and number of deadlocks.

Call to Action: Kurze Umfrage

Ich lade Sie dazu ein, meinen Code kostenlos einzusetzen und Probleme, die Sie antreffen sollten zu kommentieren.

Alles, worüber ich mich freuen würde, wäre, wenn Sie mir eine allgemeine Statistik darüber zusenden, welche Art von Deadlock Sie auf Ihren Systemen sehen.

Dafür können Sie einfach, nachdem Sie neue Deadlocks gesammelt haben, die folgende Abfrage ausführen, und mir das Ergebnis zusenden, welches lediglich aus der Anzahl der betroffenen Prozesse und Anzahl Deadlocks besteht.

You can use comments or also send me an Email if you have access to it. (due to spam bots I will not post it here openly though)

Thank you for all sendings!

Sie können Kommentare verwenden, oder mir eine E-Mail schicken, wenn Sie auf diese Zugriff haben (aufgrund der Spam-Bots werde ich meine Email-Adresse hier jedoch nicht öffentlich posten).

Ich bedanke mich für alle Zusendungen!

I hope you find my tool useful, and good luck with the analysis of your hopefully not too many deadlocks.

Ich hoffe, Sie können mein Tool gut verwenden und wünsche Ihnen viel Erfolg bei der Analyse Ihrer hoffentlich nicht allzu vielen Deadlocks.

(en)
You may remember the survey on database ownership which I launched several months ago.

In the following, I am now presenting the results and giving my official recommendation for a best practice for security in terms of database ownership.
First, if you still need the script:

(de)
Ihr erinnert Euch vielleicht an die Umfrage zu Datenbankbesitz, die ich vor einigen Monaten gestartet habe.
Hier präsentiere ich nun die Ergebnisse und gebe meine offiziellen Empfehlungen für Sicherheits-Best Practice hinsichtlich Datenbankbesitz.
Zunächst, wer noch den Script benötigt:

Now first the results.
I received data from 58 different servers and 905 databases altogether.
That’s not bad, and sufficient for my purpose of giving you, my readers, the opportunity to find out how others configure their servers.

Many thanks to all those who submitted!
You may still share results but I can’t promise how soon I can include them. (Here is the survey plus the script for collection)
So now to the details. I put the most interesting data in charts.
The most obvious issue is that of the external owner’s account, which is most often and not very surprisingly sa:

Nun zuerst einmal die Ergebnisse.

Ich habe insgesamt von 58 verschiedenen Servern und 905 Datenbanken Daten erhalten.

Das ist nicht schlecht. Und es ist ausreichend für meine Zwecke – Euch, meinen Lesern, die Gelegenheit zu gegeben herauszufinden, wie andere ihre Server konfigurieren.

Vielen Dank an alle, die ihre Daten eingereicht haben!

Ihr könnt eure Ergebnisse immer noch mit mir teilen, aber ich kann euch nicht versprechen, wie bald ich sie mit einschließen kann. (Hier sind die Umfrage sowie das Skript für die Datenerfassung.)

Nun zu den Details. Ich habe die interessantesten Daten in Diagramme gesetzt.

Die offensichtlichste Frage ist die des Kontos des externen Besitzers, das am häufigsten und nicht überraschenderweise sa ist:

57% of all databases belong to sa himself. Actually, this is better than expected. But let’s dive further – what’s the server role behind the remaining 42%?

57% aller Datenbanken gehören sa selbst. Dies ist sogar besser als erwartet. Aber schauen wir mal genau hin – was ist die Server-Rolle hinter den verbleibenden 42%?

Ok, that changes the picture quite a bit. Almost 80% of all Database owners are sysadmin. So that is by no means any better than sa.
Then some other accounts follow, which means those have low privileges (“excellent”), and then comes dbcreator, securityadmin, that are later followed by some other high privileged server roles, though with much less power.

So in other words: only 7% of all those databases have been looked at with security in mind by only using low privileged accounts as owners.

If you are interested in the plain numbers, here they go:

Ok, das verändert das Bild schon entscheidend. Fast 80% aller Datenbankenbesitzer sind sysadmin. Das ist also keineswegs besser als sa.
Es folgen einige andere Konten, das bedeutet, dass diese niedrige Privilegien (“hervorragend”) haben, und dann folgen dbcreator, securityadmin, auf die später einige andere hochprivilegierten Serverrollen folgen, doch mit weitaus weniger Privilegien.

Mit anderen Worten: nur 7% aller dieser Datenbanken wurden im Hinblick auf Sicherheit betrachtet, indem von Besitzern nur niedrigprivilegierte Konten verwendet wurden.

Wenn euch die genauen Zahlen interessieren, hier sind sie:

I did include some of the security-wise critical database- & server configurations:

1. Is the database set to be “Trustworthy”?
2. Is the database set to have “Database chaining on”?
3. Is the Server set to have “cross database chaining on”?

Those are actually the even more important results.

Since the system databases need to have a different setting by default, I am excluding them, making it a total of 847 User databases.
Of which 30 have the trustworthy bit set to on, and 35 have the database chaining.
What you can’t see in this graph, but what I can tell from the raw data, is that those 30 “trustworthy” databases all are owned by a sysadmin.
And THIS now is the biggest security-hole in this area!
Here a graph on that:

Ich habe einige der sicherheitstechnisch kritischen Datenbank- und Serverkonfigurationen mit eingeschlossen:

1. Ist die Datenbank auf „Trustworthy“ eingestellt?
2. Ist in der Datenbank „Datenbank-Verkettung an“ eingestellt?
3. Ist im Server „cross database chaining on“ eingestellt?

Diese sind eigentlich die wichtigeren Ergebnisse.

Da die Systemdatenbanken standardmäßig eine andere Einstellung haben müssen, schließe ich sie in meiner Bewertung aus, so dass ich auf insgesamt 847 Nutzer-Datenbanken komme.
30 von ihnen haben das Trustworth-Bit eingestellt, und 35 haben die Datenbanken-Verkettung eingeschaltet.
Was ihr in dieser Grafik nicht sehen könnt, aber was ich aus den Rohdaten erkennen kann, ist, dass diese 30 „vertrauenswürdigen“ Datenbanken alle im Besitz von einem sysadmin sind.
Und DAS ist das größte Sicherheitsloch in diesem Bereich!
Hier ein Diagramm dazu:

In the interest of time I will focus this post on recommendation rather than explaining all the risks involved. At the end though I will provide some links for further reading.

Aus Zeitgründen werde ich diesen Eintrag auf Empfehlungen beschränken, als alle Risiken zu erklären. Am Ende werde ich jedoch einige Links für weiterführende Lektüre angeben.

Possibilities

So what are the general variations of database ownership?
Let me start with the most common and actually WORST possibilities (Yes, I mean it exactly as I say ;-) ):

1. SA-Account
2. Some other SQL-Account with sysadmin privileges
3. Windows Login with sysadmin privileges

A first improvement(? – really?):

      4. Any of the above with Status = Disabled

 And then:

     5.   A ”shared” account without any special server role or permissions (aka “1 Account per Server”)

     6.   1 Account per Database

     7.    1 Account per Application

     8.   1 Account per Group of databases

 + all of them not only Disabled but with a Denied Connect-Permission

Möglichkeiten

Was sind also die allgemeinen Variationen von Datenbanken-Besitztum?

Fangen wir mit den häufigsten und eigentlich SCHLECHTESTEN Möglichkeiten an (Ja, das meine ich genau so, wie es hier steht ;-) ):

1. SA-Konto
2. Irgendein anderes SQL-Konto mit sysadmin-Privilegien
3. Windows Login mit sysadmin-Privilegien

 Eine erste Verbesserung(? – wirklich?):

     4.   Alle der oben angegebenen mit Status = Deaktiviert

 Und dann:

     5.     Ein „geteiltes“ Konto ohne eine spezielle Serverrolle oder Rechte (Alias „1 Konto pro Server“)

     6.   1 Konto pro Datenbank

     7.   1 Konto pro Anwendung

     8.   1 Konto pro Datenbank-Gruppe

 + alle davon nicht nur Deaktiviert sondern mit einer verweigerten Verbindungs-Berechtigung 

My Recommendation:

Depending on your environment: Any of 5, 6, 7 or 8:

Create a specific Login without any extra permissions + Deny Connect.

The most simple approach and yet better than sa is: one database owner per server.
Example for (5):

• Database1 owned by DBOwner
• Database2 owned by DBOwner
• Database3 owned by DBOwner

 Simple and self-explanatory.

The other extreme and most secure is: per database.
Example for (6):

• Database1 owned by DBOwner_Database1
• Database2 owned by DBOwner_Database2
• Database3 owned by DBOwner_Database3
• Database4 owned by  DBOwner_Database4

 Some applications use a number of different databases. For them it’s perfectly fine to use the same database owner account. So create an account per application.

Example for (7):

• App1Database1 owned by DBOwner_App1
• App1Database2 owned by DBOwner_App1
• App2Database1 owned by DBOwner_App2
• App2Database owned by  DBOwner_App2

 Another approach is kind of a compromise between 1 Database-Owner Account per Server and One per database: Define the level of security needed per database. Then create a dedicated account for the most critical Databases. And for the others use a shared owner/account, possibly divided in 2 or more groups.

Example for (8):

• CriticalDatabase1 owned by DBOwner_Level1Dedicated1
• CriticalDatabase2 owned by DBOwner_ Level1Dedicated2
• Level2Database1 owned by DBOwner_Level2
• Level2Database2 owned by DBOwner_Level2

 I hope my samples give you an idea. :-)

So why this effort?
Let me put it this way: ”Why not sa?”.
First: If you think about it, it actually makes little sense that the highest privileged account in SQL Server is being recommended by so many, even professionals + in Whitepapers (!) – when security is the focus. It is really wrong, as wrong as it could possibly get.
I mean, as you can see, there are other options out there.
The top reason why SA keeps getting recommended is administration itself: It eases the setup for failover and regular database restores, since SA is always available at any server and hence a broken database owner can be avoided with almost no extra work.
But that’s “only” from a perspective of maintenance.
With regard to security it is totally on contrary to the Principle of least privilege.

It may not matter a lot, if everything else is tightened, but that’s hardly a thing to rely on especially in bigger environments where things change and many people have access and permissions to.
Especially in the context of the trustworthy-setting for a database, this completely opens the system for privilege escalation attacks from inside. It is then a piece of cake to gain system level permissions once you are for example in the db_owner database group – like many applications are, if they are not sysadmin already.
- Remember: the owner of a database cannot be denied anything inside and with his database. So he can change structure, create backups, break log-backup-chain and also drop it completely.

And since the attack starts from inside, it really doesn’t matter whether the sa/sysadmin account is disabled as you may now realize.

Having a dedicated account with zero special permissions as database owner prevents database principals from gaining system level permissions as a sysadmin has, even in the case of the database being trustworthy.
And trustworthy is one of the dirty little shortcuts for developers implementing CLR code inside the database and avoiding the hassle of having to use certificates under certain conditions. The same is often done for code that needs to get server-level data from inside the database.

Meine Empfehlung:

Abhängig von eurer Umgebung: eine von 5, 6, 7 oder 8:

Ein spezifisches Login errichten ohne extra  Rechte + Deny Connect.

Die einfachste Herangehensweise und doch besser als sa ist: ein Datenbankbesitzer pro Server.

Beispiel für (5):

• Datenbank1 im Besitz von DBOwner
• Datenbank2 im Besitz von DBOwner
• Datenbank3 im Besitz von DBOwner

 Einfach und selbsterklärend.

 Das andere Extrem und dabei die sicherste ist: pro Datenbank.

Beispiel für (6):

• Datenbank1 in Besitz von DBOwner_Database1
• Datenbank2 in Besitz von DBOwner_Database2
• Datenbank3 in Besitz von DBOwner_Database3
• Datenbank4 in Besitz von DBOwner_Database4

Einige Anwendungen verwenden eine Reihe von unterschiedlichen Datenbanken. Für sie ist es völlig ausreichend, das gleiche Datenbankbesitzerkonto zu verwenden. Erstellt also ein Konto pro Anwendung.

Beispiel für (7):

• App1Database1 in Besitz von DBOwner_App1
• App1Database2 in Besitz von DBOwner_App1
• App2Database1 in Besitz von DBOwner_App2
• App2Database in Besitz von DBOwner_App2

Eine andere Herangehensweise ist eine Art Kompromiss zwischen 1 Datenbankenbesitzerkonto pro Server und einem pro Datenbank: Definiere das Sicherheitslevel, das je Datenbank gebraucht wird. Dann erstelle ein spezielles Konto für die kritischsten Datenbanken. Und für die anderen Besitzer einen gemeinsamen Besitzer-/Konto verwenden, möglicherweise in 2 oder mehr Gruppen geteilt.

Beispiel für (8):

•  CriticalDatabase1 in Besitz von DBOwner_Level1Dedicated1
• CriticalDatabase2 in Besitz von DBOwner_ Level1Dedicated2
• Level2Database1 in Besitz von DBOwner_Level2
• Level2Database2 in Besitz von DBOwner_Level2

 Ich hoffe, meine Beispiele geben euch eine Vorstellung. :-)

Aber warum diese Mühe?
Lasst es mich so ausdrücken: “Warum nicht sa?”
Zuallererst: Denkt man darüber nach, ergibt es eigentlich wenig Sinn, dass das höchstprivilegierte Konto beim SQL Server von so vielen empfohlen wird, selbst von Profis + in Whitepapers (!) – wenn Sicherheit im Fokus steht. Es ist wirklich falsch, so falsch wie es nur irgend sein kann.

Schließlich gibt es da draußen, wie ihr sehen könnt, noch andere Optionen.

Die Grund Nr. 1, warum SA immer wieder empfohlen wird, ist die Administration selbst: Es erleichtert das Einrichten für Failover und regelmäßige Datenbankenwiederherstellungen, da SA immer auf jedem Server verfügbar ist und damit ein kaputter Datenbankbesitzer mit wenig zusätzlichem Aufwand verhindert werden kann.
Aber das ist „nur“ aus Sicht der Wartung.
Was die Sicherheit angeht, steht es völlig im Gegensatz zum Prinzip des geringsten Privilegs.

Es mag nicht viel ausmachen, wenn alles andere straff sitzt, aber darauf sollte man sich nicht verlassen, besonders in größeren Umgebungen, wo sich Dinge ändern und viele Leute Zugriff und Befugnisse haben.

Besonders im Kontext der Trustworthy-Einstellung für eine Datenbank öffnet dies das System komplett für privilege escalation-Angriffe von innen. Dann ist es ein Kinderspiel, Systemlevel-Befugnisse zu erlangen, wenn man einmal z.B. in der db_owner Datenbankengruppe ist – wie es viele Anwendungen sind, wenn sie nicht bereits sysadmin sind.

Denkt dran: dem Datenbankenbesitzer kann weder innerhalb noch mit seiner Datenbank etwas verweigert werden. Er kann also die Struktur verändern, Backups erstellen, Log-Backup-Chain brechen und sie auch komplett löschen.

Und da der Angriff von innen anfängt, ist es wirklich egal, ob das sa/sysadmin Konto deaktiviert ist, wie ihr jetzt realisiert haben werdet.
Ein spezielles Konto mit Null speziellen Befugnissen als Datenbankbesitzer zu haben hindert Datenbank-Prinzipale daran, System-Level-Befugnisse zu erlangen, wie sie ein sysadmin hat, selbst in dem Fall, dass die Datenbank vertrauenswürdig ist.
Und „trustworthy“ ist eine der unsauberen kleinen Abkürzungen für Entwickler, die CLR-Code im Innern der Datenbank ausführen und sich dabei die Umstände sparen, unter bestimmten Bedingungen Zertifikate benutzen zu müssen. Dasselbe wird oft für Code gemacht, der Server-Level-Daten aus dem Innern der Datenbank erreichen muss.

Call for actions:

Check your databases. You can find my script here: Security-Check-Script & Survey: SQL Server Security - Database-Owners, critical Permissions and role membership
Now when you start with securing your databases from database-ownership standpoint, you have to make sure that the very account does exist at any sever where this database gets restored/failed over. Usually you will have a technique in place already to synchronize your server-level principals to your other servers. So this is just one or several more of them.

Also make sure you fully understand your environment and possibly application needs before you just change the owner of your databases. You can start by reading through the links at the bottom.

Vote for an improvement in SQL Server:
I have created a suggestion as Connect Item which tackles this problem. My idea is having Microsoft include a special “DBOwner” Account at server level by default, which not only pre-exists and has not permissions, but also never compares to another. I think this would make it much easier to get rid of the habit of “sa” everywhere by also making it simple to maintain.
Please vote here: Providing a special Server principal for Database Ownership

Handlungsaufruf:

Überprüft eure Datenbanken. Ihr könnt meinen Skript hier finden: Sicherheitsprüfungs-Script & Umfrage: SQL Server Datenbankbesitzer, kritische Rechte und Rollenmitgliedschaft

Wenn ihr jetzt anfangt, eure Datenbanken aus der Perspektive von Datenbanken-Besitz zu sichern, müsst ihr dabei sicherstellen, dass dasselbe Konto auf jedem Server existiert, wo diese Datenbank wiederhergestellt/failed over wird. Normalerweise werdet ihr bereits eine Technik haben, wie ihr eure Server-Level-Prinzipale mit euren anderen Servern synchronisiert. Das sind also nur eine oder einige mehr davon.

Stellt außerdem sicher, dass ihr eure Umgebung und möglicherweise Anwendungsbedürfnisse vollständig versteht, bevor ihr den Besitzer eurer Datenbanken einfach ändert. Ihr könnt damit anfangen, indem ihr euch unten aufgelisteten Links durchlest.

Abstimmen für eine Verbesserung im SQL Server:
Ich habe einen Vorschlag als Connect Item erstellt, der dieses Problem behandelt. Meine Vorstellung ist es, Microsoft dazu zu bringen, standardmäßig ein spezielles „DBOwner“ Konto auf Server-Level auszuliefern, das nicht nur bereits immer vorab existiert und keine Rechte hat, sondern auch nie mit anderen vergleichbar ist. Ich denke, dass dies es viel einfacher machen würde, die allgegenwärtige Gewohnheit des „sa“ loszuwerden und es gleichzeitig auch einfach Wartbar machen würde.
Bitte hier Eure Stimme abgeben: Providing a special Server principal for Database Ownership

I hope this was helpful.

If you have any questions feel free to comment.
Let me finish up with some links for further readings:

Ich hoffe, das war hilfreich.

Wenn ihr noch Fragen habt, kommentiert gern.
Zum Abschluss einige Links für weiterführende Lektüre:

(de)
In dieser Umfrage möchte ich einmal in einem größeren Kreis ermitteln, welche Accounts typischerweise als Datenbankbesitzer eingetragen werden. Die kumulierten Ergebnisse werde ich anschließend hier veröffentlichen um sie mit der Community zu teilen, zusammen mit einigen Empfehlungen hinsichtlich Sicherheits-Härtung.

Dabei interessieren vor allem bestimmte serverweite Berechtigungen nicht nur des verwendeten Logins selber, sondern auch, falls er Mitglied einer (custom) Server Role ist, bestimmte, kritische Rechte dieser Rolle.

Wer meine Vorträge zum Thema „Sicherheit in SQL Server“ in den letzten Jahren, und insbesondere der im Sommer 2013 gestarteten Reihe „SQL Server Attacked“ verfolgt hat, wird sich vielleicht hat erinnern, wie ich dort auch gezeigt habe, wie man aus einer Datenbank ausbrechen und volle Systemrechte erlangen kann („Privilegien Erweiterung“) – basierend auf einer Kombination von Datenbankbesitzer, -Konfiguration und Impersonierung.

Hier stelle ich nun einen T-SQL Script zur Verfügung, welcher

• Den jeweiligen Datenbankbesitzer aller Datenbanken ermittelt
• Ungültige/Fehlende Datenbankbesitzer erkennt
• Anzeigt, ob der Besitzer direkt über sicherheitskritische systemweite Rechte verfügt
• Mitgliedschaft in hochprivilegierten Server-Rollen anzeigt – inklusive der benutzerdefinierten Serverrollen (ab SQL 2012 möglich)
• Die kritischen Datenbankeinstellungen „Vertrauenswürdig“ und „Datenbankverkettung“ anzeigt.

Und hier ist der Script:

(en)
In this survey, I would like to explore in a greater radius which accounts are typically used as database owners. I will subsequently publish the cumulated results here to share them with the community together with some recommendations for hardening security.

In this instance, particular server-wide permissions both of the used account as well as, in case of membership of a (custom) Server Role, critical permissions of that role, are of interest.

Those who have followed my presentations on the topic “Security in SQL Server” in the last years, and especially the „SQL Server Attacked“ series launched in the summer of 2013, may remember how I had also demonstrated there how to break out of a database and be granted full system rights (“Elevation of privileges”), - based on a combination of database owner, configuration and impersonation.

Here, I am providing a T-SQL Script which

• Identifies the respective database owners of all databases
• Detects invalid/missing database owners
• Indicates whether the owner directly possesses security-critical system-wide rights
• Indicates membership in high privilege Server Roles – including the user defined Server Roles (possible since SQL 2012)
• Indicates the critical database configurations “trustworthy” and “database chaining”.

Here you can find the script:

- Er sollte auf allen SQL Servern ab der Version 2005 funktionieren (getestet 2008-2014).

Zum Zwecke der Umfrage bitte ich Euch, die anonymisierten Ergebnisse (am Besten in Excel-Format) an die Email survey@SarpedonQualityLab.com zu senden, oder auch hier in den Kommentaren zu posten.
Zum Zwecke der Anonymisierung kann man einfach die letzten 3 Spalten aus dem Resultset auslassen – diese haben für eine statistische Erhebung auch keinen Nutzen sondern sind nur für Eure internen Zwecke bestimmt :-).

Hier seht ihr ein Beispiel-Ergebnis:

-           It should be working on all SQL Servers since the 2005 version (tested: 2008-2014).

For the purpose of the survey, I would like to ask you to send the anonymized results (preferably in excel-format) to the email survey@SarpedonQualityLab.com, or as well post them here in the comments.

For anonymization purposes, you can simply leave out the last three columns from the result set – these are not of use to a data collection and only meant for your internal usage :-).

Here, you can see a sample result:

Mehrere Einträge für dieselbe Datenbank entstehen, wenn der Datenbankbesitzer – oder seine Serverrolle(!) über mehrere, der von mir als sicherheitskritisch klassifizierten Rechte verfügt. Diese stehen dann in der Spalte login_permission bzw. role_permission. Hinter den Kürzeln verbergen sich:
ALLG = ALTER ANY LOGIN
ALSR = ALTER ANY SERVER ROLE
CL  = CONTROL SERVER
XA  = EXTERNAL ACCESS ASSEMBLY
(mehr dazu auch hier: CONTROL SERVER vs. sysadmin/sa: permissions, system procedures, DBCC, automatic schema creation and privilege escalation - caveats).

Die Inhalte der Spalten DatabaseNo, database_owner und external_owner sind absichtlich allgemein gehalten – die exakten Namen sind wie gesagt in den letzten 3 Spalten zu finden.

Die Spalte db_owner_valid zeigt an, ob die Datenbank einen gültigen Besitzer hat. In diesem Fall besteht ein match zwischen dem „externen Besitzer“ und dem in der Datenbank selber hinterlegten. In der Spalte external_owner steht nur ob dieser „externe Besitzer“ sa ist oder nicht – der genaue Login steht dann in *External_Owner.Der Script speichert keine Daten dauerhaft im System ab und beseitigt seine temporären Zwischenergebnisse selbst.

Ich hoffe, ihr findet die Auswertung hilfreich – und ich hoffe, ihr könnt die (anonymisierten) Ergebnisse mit der Community teilen. Ich werde keine Email speichern oder verwenden, sondern nur die anonymisierten Abfrageergebnisse.

Ich bedanke mich für eure Teilnahme und Unterstützung der Community!
Ich verspreche, die kumulierten Ergebnisse in den nächsten Monaten zusammen mit entsprechenden Hinweisen auf Best & Bad Practices sowie auf möglichen Einfallstoren für Privilegienerweiterungs-Angriffen zu veröffentlichen.

Multiple entries for the same database occur when the database owner – or his serverrole(!) possess multiple of the permissions which I classified as security-wise critical. These are listed in the column login_permission respectively role_permission. The codes stand for:
ALLG = ALTER ANY LOGIN
ALSR = ALTER ANY SERVER ROLE
CL  = CONTROL SERVER
XA  = EXTERNAL ACCESS ASSEMBLY
(you can read more on that here: CONTROL SERVER vs. sysadmin/sa: permissions, system procedures, DBCC, automatic schema creation and privilege escalation - caveats).

The contents of the columns DatabaseNo, database_owner and external_owner are left general on purpose – the exact names can be found in the last 3 columns s mentioned above.

The column db_owner_valid indicates, whether the database has a valid owner. In this case there is a match between the „external owner“ and the one stored within the database itself. The column external_owner only shows whether that „external owner“ is sa or not –the exact Login is shown in *External_Owner.

The script does not permanently save any data in the system and removes its temporary intermediary results by itself.

I hope you find the report useful and I hope you can share the (anonymized) results with the community.
I will not store or use any email but only use the anonymized Query-results.

 Thank you in advance for your participation and support of the community!
I promise to publish the cumulated results with according details on best & bad practices as well as on to possible gateways to elevation of privilege attacks in the next months.