SQL Server & BI Blog by Andreas Wolter

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

(DE)
In einer meiner letzten Master-Classes tauchte die nicht ganz ernste aber dennoch interessante Fragestellung auf:

Wie viele Zeilen passen eigentlich maximal auf eine Datenseite? – wenn die Datensätze/Records so klein wie möglich sind.

Zunächst, Part 1, was ist der kleinstmögliche Datensatz in SQL Server?

Um das zu beurteilen, ist es gut, die Datentypen sowie die Struktur eines Datensatzes genau zu kennen.
Man könnte versucht sein, anzunehmen, dass eine Spalte vom Datentyp bit der kleinstmögliche Datensatz ist. Der erfahrene SQL‘er wird wissen, dass ein bit allein auch immer mindestens 1 byte in einem Record benötigt – erst bei mehreren Spalten dieses Datentyps, kommt ein Platzersparnis ins Spiel (bit (Transact-SQL)).

Der nächste Kandidat sind Datentypen, die laut der Liste in Books Online nur 1 byte Speicherplatz benötigen. Das wären folgende:

(EN)
In one of my last Master classes, a not quite so serious but nevertheless interesting question was brought up:

How many rows maximally do actually fit on a data page? – if the data sets/records are as small as possible.

First of all, part 1, what is the smallest possible data set in SQL Server?

In order to assess this, it is commendable to know exactly the data types as well as the structure of a data set. 

One could be tempted to assume that a column of the data type bit is the smallest possible data set. The experienced SQL people will know that a bit alone always also requires at least 1 byte in one record – only with several columns of this data type, the place-saving aspect comes into play (bit (Transact-SQL)).

The next candidate are data types which according to the list in Books Online use only 1 byte of storage. These would be the following:

Tatsächlich benötigen in diesem besonderen Fall, einer einzigen Spalte je Datensatz, auch Records mit Nettowert von 2 Bytes, 9 Bytes auf der Datenseite:

As a matter of fact, in this particular case of a single column per record, also records with a net value of 2 bytes use 9 bytes on the data page:

Wie kommt das?
Das liegt an der Struktur der Datensätze. Diese ist hinlänglich in diversen Blogs dokumentiert (z.B. hier Inside the Storage Engine: Anatomy of a record und hier: SQL Server Storage Internals 101 ), jedoch eher für allgemeine Zwecke und dieser Sonderfall (eine 1-byte-Spalte) ist dabei weniger im Fokus.

Theoretisch würden für einen Datensatz 8 Bytes ausreichen:

4 Bytes: Datensatzkopf (Typ + Zeiger auf Null-Bitmap)
1 Byte: Daten
2 Bytes: Anzahl der Spalten im Record
1 Byte: NULL Bitmap

Dazu kommt noch der 2 Bytes große Zeiger im Page-Offset, was dann 10 Bytes ergeben würde.
Dennoch belegt ein solcher Datensatz 9 Bytes auf der Seite/Page + Offset.

Woran das liegt, sehen wir uns an.

How come?
This has to do with the structure of the data sets, which is sufficiently documented in various blogs (e.g. here: Inside the Storage Engine: Anatomy of a record and here: SQL Server Storage Internals 101 ), but rather for general purposes, and this special case (a 1-byte-column) is less focused on here.

Theoretically, 8 bytes would be sufficient for a data set:

4 bytes: data set head (type + pointer to NULL-bitmap)
1 byte: data
2 bytes: number of columns in record
1 byte: NULL bitmap

Add to that the 2-bytes-pointer in the page offset, which would then result in 10 bytes.
Despite this, such a data set uses 9 bytes on the page/page + offset.

We will now look at the reason for this.

Im Folgenden definiere ich 2 Tabellen mit jeweils einer bzw. 2 Spalten von Datentyp tinyint, der ja bekanntlich einen Byte benötigt:

In the following, I am defining 2 tables with one and 2 columns each of the data type tinyint, which is known to use 1 byte:

Danach füge ich zuerst Datenätze mit dem Wert „255“ bzw „255, 255“ ein, und danach einige mit Wert „NULL“.

Mit DBCC Page kann man sich den Header der Datenseiten beider Tabellen ansehen, und findet eine kleine Überraschung (Ergebnis reduziert):

Next, I am inserting, first, data set of the value “255” or “255, 255” and then a few of the value “NULL.”

With the DBCC page, one can look at the header of the data pages of both tables, and one will find a small surprise (reduced result). 

Obwohl die Größe der Daten fixer Länge (pminlen) mit 5 bzw. 6 unterschiedlich angegeben wird, ist der freie Speicherplatz auf der Seite (m_freeCnt) identisch! (rot)

Der Datensatz belegt in beiden Fällen jedoch 9 Bytes im Page-body (blau).

So sieht die Tabelle, bestehend aus einer Spalte, mit einigen Datensätzen gefüllt, auf der Festplatte aus:

Even though the size of the fixed-length data (pminlen) is specified differently, with 5 and 6 respectively, the free storage on the page (m_freeCnt) is identical! (red)

The record, however, uses in both cases 9 bytes in the page body. (blue)

This is what the table, consisting of one column, filled up with a few records, looks like on the hard drive:

Man sieht, dass 9 Bytes belegt sind, jedoch nur, da nach der NULL Bitmap noch ein Byte jedem Datensatz anhängig ist (gelb markiert).

Hier die Tabelle mit 2 Spalten:

One can see that 9 bytes are used, but only because after the NULL bitmap, one extra byte is attached to each data set (marked in yellow).

Below, see the table with 2 columns:

Auch hier also 9 Bytes, mit dem Unterschied, wie das letzte Byte verwendet wird.

Noch etwas deutlicher wird es im Binärformat. Das ist die Tabelle mit 2 Spalten – auch diese benötigt 9 Bytes, und man sieht unten, wie die NULL Bitmap zu ihrem Namen kommt:

Here, too, it is 9 bytes, with the difference being how the last byte is used.

It becomes a bit clearer in the binary format.
This is the table with 2 columns –this one uses 9 bytes as well, and you can see below how the NULL bitmap gets its name:

Dieser eine Byte, der für mich keinen klaren Nutzen hat, führt also zu dem Ergebnis, das beide Tabellen letztlich 9 Bytes je Record auf der Festplatte benötigen.

This one byte, which to me does not have any clear purpose, is what leads to the result that both tables ultimately use 9 bytes per record on the hard drive.

Die minimale Datensatzgröße ist daher in der Tat 9 Bytes. Der zweitgrößte Datensatz ist jedoch auch 9 Bytes. :-D

Dabei darf beliebig gewählt werden zwischen 2 Spalten à 1 Byte oder 1 Spalte à 2 Bytes :-)
Daher die Liste:

The minimal record size is thus in fact 9 bytes. The second biggest record, however, is also 9 bytes. :-D

Here, one may freely choose between 2 columns à 1 byte or 1 column à 2 bytes :-).
Hence the list:

Kommen wir nun zu Part 2:

Wie viele Datensätze passen maximal auf eine Datenseite, wenn wir jetzt wissen, dass jeder Datensatz mindestens 9 Bytes + Offset benötigt?

Let us now look at Part 2:

How many records fit maximally on a data page if we now know that every data set requires a minimum of 9 bytes + offset?

Wir testen mit einer Tabelle, bestehend aus einer Spalte mit Datentyp tinyint – wohlwissend, dass es dasselbe Ergebnis bringen würde, wenn wir smallint oder etwas anderes aus der Liste oben nehmen würden.

We are testing with one table of one column with datatype tinyint – knowing full well that it would have the same outcome if we were to take smallint or something else from the list above.

Sehen wir uns an, wie voll die Datenseite geworden ist, und welche Page_ID diese hat:

Then, we will check again how full the data page has become, and which Page_ID it has:

Ausgehend davon, dass eine Datenseite 8192 Bytes groß ist und abzüglich Header 8096 Bytes für Daten zur Verfügung stehen, bedeutet ein Füllgrad von ~95,107%, das noch gut ~396 Bytes zur Verfügung stehen. Durch 11 ergibt das 36. - 36 Datensätze haben also noch Platz!

Was sagt DBCC Page dazu?

Based on the fact that a data page is 8192 bytes in size and that, less the header, 8096 bytes are available for data, a fill degree of ~95,107% means that some ~396 are still available. Divided by 11 this equals 36 – there is still room for 36 records!

What does DBCC Page have to say to this?

Auch hier: 396 Bytes frei – na dann war unsere Überschlagsrechnung gar nicht so schlecht. :-)

Das heißt rein rechnerisch müssten weitere 36 Datensätze auf die Seite passen.
Also „rauf damit“.

396 bytes free – well, then, our back-of-the-envelope calculation wasn’t so bad at all. :-)

That is to say that in purely arithmetical terms, a further 36 records should fit on the page.
So “let’s put them on.”

2 Seiten, direkt ab dem 701. Datensatz.
Was ist da los?
Im Hex Editor betrachten wir „das Grauen“:

2 pages, directly from the 701^st data set.
What is going on there?
In the hex editor, we are looking at “the horror”:

Freier Platz! – Fragmentierung, „Igitt“ ;-)
Was machen wir da?
Ein Rebuild der Tabelle, was sonst.

Free space! – Fragmentation, „yuck“ ;-)
What to do?
A rebuild of the table, what else.

Und schon ist die Seite zu glatten 100% gefüllt: 736 Datensätze.
Und das sagt der Header:

And just like that, the page is filled with a sleek 100%: 736 records.
And this is what the header says:

Ja, die Page_ID ist eine andere – aber nicht, weil ich gemogelt hätte, sondern weil die Storage Engine für den Rebuild neuen Platz reserviert, und den alten nach getaner Arbeit freigegeben hat.

Und auch auf Platte sieht es jetzt so aus, wie es sein sollte – bis zum Ende aufgefüllt:

Yes, the Page_ID is a different one – but not because I might have cheated, but because the storage engine allocated new space for the rebuild, and released the old one after the done work.

On the drive, too, it now looks exactly how it’s supposed to – filled up to the end:

Übrigens: Wenn ich anstelle der Heap-Speicherung der Tabelle einen Clustered Index als Struktur gewählt hätte, wären die Daten in den allermeisten Fällen sofort auf der einen Seite zu 100% abgelegt worden
– aber was tut man nicht alles für die Spannungskurve :-)

Ansonsten gilt auch hier die Regel: „Niemals auf etwas verlassen, was man nicht selbst getestet hat“ ;-)

By the way, had I chosen a Clustered Index as structure instead of the Heap-storage, in most cases, the data would have been stored immediately to a 100% on the one page
– but what’s the fun in that! :-)

Otherwise, the rule also applies here: “Never rely on something you haven’t tested yourself” ;-)

Ergebnis:
Die Antwort auf die Frage lautet daher nicht 700, nicht 732, oder gar 809, sondern: 736 Datensätze passen maximal auf eine Seite.

- und dabei macht es noch nicht einmal einen Unterschied, ob man eine Spalte mit 1 oder 2 Bytes Größe, oder gar 2 Spalten mit je einem Byte Größe verwendet.

Da jeder Datensatz 9 Bytes zzgl. 2 Bytes Record Offset benötigt, haben wir damit die maximale Kapazität einer SQL Server Datenseite mit 8096 Bytes exakt ausgereizt! :-)

- Nicht zu verwechseln mit der maximalen Zeilenlänge von 8060 Bytes.

Result:
The answer to this question, therefore, is not 700, not 732, or even 809, but: a maximum of 736 data records fits on one page.

- and it does not even make a difference, if one uses one column with 1 or 2 bytes in size, or even 2 columns with one byte in size.

Since every data record uses 9 bytes plus 2 bytes record offset we will have exactly exhausted the maximum capacity of an SQL Server data page with 8096 bytes! :-)

- Not to be confused with the maximal row length of 8060 bytes.

(DE)
Das Jahresende nähert sich in raschen Schritten. In den nächsten 3 Monaten stehen wieder mehrere Konferenzauftritte an.

(EN)
The end of year is approaching fast. For the next three months, several conferences are scheduled.

Nach der Vorstellung des SQL Server 2014 (SQL Server 2014 - Highlights in der Datenbank-Engine im Überblick) auf der BASTA im September in Mainz, geht es weiter im Oktober auf dem Microsoft Technical Server Summit in Düsseldorf mit einem Vortrag zu Neue Speicherformen in SQL Server 2014:

After the introduction of SQL Server 2014 at the BASTA in September in Mainz/Germany, I am continuing in October with a presentation on New Storage-Types in SQL Server 2014 at the Microsoft Technical Server Summit Düsseldorf/Germany:

Anfang November folgt dann das alljährliche Highlight: Nach dem MVP Summit, wo ich hoffe die neuesten Entwicklungen hinsichtlich der nächsten Version des SQL Server zu erfahren, bin ich wie seit 2009 jedes Jahr auf dem PASS Summit in Seattle/USA.
Der Summit ist die erste Anlaufstelle für alle diejenigen, die immer auf dem Neusten Stand sein möchten. Was hier verkündet wird, wird die Inhalte der nächsten 1-2 Jahre auf anderen, kleineren Konferenzen und den Regionalgruppen der PASS weltweit bestimmen.
Dazu kommt der wertvolle direkte Kontakt zu den Entwicklern des SQL Servers vor Ort.
Auch dieses Jahr trage ich wieder selber vor, allerdings nur einen Kurzvortrag, und zwar zu dem Reporting Services Map Reports & Dynamic ZOomiNG:

This is followed by the annual highlight at the beginning of November: After the MVP Summit, at which I’m hoping to learn about the most recent developments in terms of the forthcoming SQL Server, I will be attending the PASS Summit in Seattle/USA, which has become an annual habit since 2009.
The summit is the first point of contact for all those who want to always be up-to-date.  The topics raised here will determine the content of the next one to two years at other, smaller-scale conferences as well at the regional groups of PASS worldwide.

Furthermore, the summit provides the valuable opportunity to connect directly with the developers of SQL Server on site.

This year, too, I will be presenting myself; however, just a short presentation, which will be on Reporting Services Map Reports & Dynamic ZOomiNG:

Kaum zurück in Deutschland bin ich in Berlin auf dem Microsoft Technical Summit, wo auch der neue CEO von Microsoft, Satya Nadella eine Keynote halten wird.
Dort werde ich zusammen mit Patrick Heyde, Microsoft (Blog), das neueste zu der nächsten SQL Server Version präsentieren, soweit bis dahin schon für die Öffentlichkeit freigegeben ist. Zusätzlich dazu werde ich einen Deep Dive-Vortrag in In-Memory geben.

Once back in Germany, my next stop will be the Microsoft Technical Summit in Berlin where Microsoft’s new CEO, Satya Nadella, will be giving a keynote speech.

There, I will be presenting the latest on the forthcoming SQL Server version together with Patrick Heyde, Microsoft (Blog) - as far as already released for the public. Additionally I will be giving a Deep Dive-presentation in In-Memory.

Im Dezember der würdige Abschluss mit dem alljährlichen PASS Camp, ebenfalls zum Thema In-Memory: In-Memory vNext and lessons learned
Hier spreche ich seit 2011 das 4. Mal in Folge.

December will see the worthy finale with the annual PASS Camp, likewise on the topic of In-Memory: In-Memory vNext and lessons learned. Here I am speaking the fourth time in a row since 2011

(EN)
This time, we are dealing with the „MDW“, short for Management Data Warehouse,(msdn.microsoft.com/en-us/library/bb677306.aspx), which I like to recommend as a minimal performance logging-approach.

From time to time, and most recently in the context of my PASS Essential „SQL Server Analysis tools & Techniques for Performance und general Monitoring“, the question arises as to whether the MDW operates together with the High Availability technologies Database Mirroring and AlwaysOn Availability Groups, and if so, how so.

(DE)
Diesmal geht es um das Management Data Warehouse, kurz „MDW“ (msdn.microsoft.com/de-de/library/bb677306.aspx), welches ich gerne als minimalen Performance-Protokollierungs-Ansatz empfehle.

Hin und wieder, zuletzt im Zusammenhang mit meinem PASS Essential „SQL Server Analysetools & Techniken für Performance und allg. Monitoring“ kommt die Frage auf, ob das MDW mit den Hochverfügbarkeitstechnologien Datenbankspiegelung und AlwaysOn Hochverfügbarkeitsgruppen zusammenspielt, und wenn, wie.

The short answer is: Yes, it does.
The MDW operates both with Database Mirroring as well as with AlwaysOn Availability Groups.

The following graph illustrates a possible setup using the latter:

Die kurze Antwort lautet: Ja.
Das MDW funktioniert sowohl mit Datenbankspiegelung als auch mit den AlwaysOn Hochverfügbarkeitsgruppen.

Das folgende Schaubild zeigt ein mögliches Setup unter Verwendung der letzteren Variante:

The server (0) holding the MDW database is located outside of the high availability nodes. The databases to be monitored are located in the AlwaysOn Availability Groups in the servers 1-3.
Keeping the MDW highly available is not the objective. It is simply about being able to see the performance data of all databases, no matter in which server they are active at the moment.

Der Server (0), der die MDW-Datenbank vorhält, liegt außerhalb der Hochverfügbarkeitsknoten. Die zu überwachenden Datenbanken liegen in AlwaysOn Hochverfügbarkeitsgruppen auf den Servern 1-3.
Das MDW hochverfügbar zu halten ist nicht das Ziel. Es geht nur darum, die Performance-Daten aller Datenbanken einsehen zu können, gleich auf welchem Server sie gerade aktiv sind.

Part 1: Databases in secondary role

If you set up the MDW as standard you will realize that after a failover, the data of the respective databases disappear from the “Disc Usage” reports of the server, while these were previously still present in the primary role.

The background to this is that after a failover, the respective databases now are present in a different server in the primary role, and now are no longer readable in the secondary, in the standard setting.

In this moment, the System Data Collection Set “Disc Usage”, or the underlying job “collection_set_1_noncached_collect_and_upload” cannot collect data for this database. In contrast on the new primary node these database will now reappear as long as they are active in the primary role there. In principle, this behavior is comprehensible: The Data Collector can no longer find any information on this database and assumes that the latter is no longer relevant – as if it was deleted. One may certainly wish for a possibility of intervention here; however, the MDW is currently not flexible in this regard.

The new report “Transaction Performance Analysis Overview” which is enriched through the newly existent “Transaction Performance Collection Set” in SQL Server 2014 also displays data for no longer active databases.

Having clarified this background, the possible solution is self-evident: The databases must remain readable.

With AlwaysOn High Availability Groups, this is in principal easily done:

Part 1: Datenbanken in Secondary-Rolle

Wenn man nun das MDW standardmäßig einrichtet, wird man feststellen, dass nach einem Failover die Daten der jeweiligen Datenbanken aus den „Disk Usage“-Berichten des Servers verschwinden, wo diese bis zuvor noch in der Primary-Role vorlagen.

Hintergrund ist, dass nach einem Failover die jeweiligen Datenbanken nun auf einem anderen Server in der Primary-Role vorliegen, und auf dem nun Secondary, in der Standardeinstellung nicht lesbar sind.

In diesem Moment kann das System Data Collection Set „Disk Usage“, bzw. der dahinterliegende Job „collection_set_1_noncached_collect_and_upload“ zu dieser Datenbank keine Daten auslesen. Auf dem neuen Primary-Knoten hingegen werden diese Datenbanken nun neu erscheinen, solange sie dort in der primären Rolle aktiv sind.

Prinzipiell ist dieses Verhalten nachvollziehbar: Der Data Collector kann keine Informationen zu dieser Datenbank mehr finden und geht davon aus, dass diese nicht mehr relevant ist – als ob sie gelöscht sei. Sicherlich kann man sich hier eine Eingriffsmöglichkeit wünschen, derzeit ist das MDW aber in dieser Hinsicht nicht flexibel.
- Der neue Bericht „Transaction Performance Analysis Overview“, der über das neu im SQL Server 2014 existierende „Transaction Performance Collection Set“ angereichert wird, zeigt auch Daten für bereits nicht mehr aktive Datenbanken an.

Wenn nun dieser Hintergrund klar ist, liegt die mögliche Lösung nahe: Die Datenbanken müssen lesbar bleiben.
Mit AlwaysOn Hochverfügbarkeitsgruppen (Availability Groups) ist das prinzipiell auch leicht gemacht:

However, one needs to be aware of the fact that these databases are now released for all reading access – which should be taken into consideration in respect to application architecture, performance as well as in terms of license. Hence, for the purpose of data collection for performance evaluation alone I CANNOT recommend it.

If however the business applications are supposed to maintain reading access to the secondary point anyway, the data collector is covered with this as well.

One more advice: The setting “Read-Intent only” unfortunately does not work with the MDW since one cannot manually adapt the Connection String accordingly.
Database Mirroring does not support this option at all.

Jedoch muss man sich hierüber im Klaren sein, dass diese Datenbanken nun für sämtliche Lesezugriffe freigegeben sind, was sowohl hinsichtlich Applikationsarchitektur, Performance als auch Lizenztechnisch genau bedacht werden sollte. Allein zum Zweck der Datensammlung zu Performance-Auswertung kann ich das also NICHT empfehlen.
Wenn die Geschäftsanwendungen aber ohnehin Lesezugriffe auf den Sekundärknoten erhalten sollen, dann ist damit der Datensammler ebenfalls abgedeckt.
Noch ein Hinweis: Die Einstellung „Read-Intent only“ funktioniert mit dem MDW bisher leider nicht, da man den Connection String nicht entsprechend manuell anpassen kann.
Datenbankspiegelung unterstützt diese Option gar nicht.

Part 2: Configuration of the MDW-Clients

Since the databases run on a different node after a failover, the MDW reports must be set up in all servers in which the Availability Group is running. Here, one needs to ensure that access to the central MDW-Server is possible from all servers.

To do this (before SQL Server 2014) the SQL Server Agent Account of the client-instance must be included in the mdw_writer role on the MDW-Server (mdw_admin is not necessary) when configuring the MDW through the “Configure Management Data Warehouse Wizard:

Part 2: Konfiguration der MDW-Clients

Da die Datenbanken nach einem Failover auf einem anderen Knoten laufen, müssen die MDW-Berichte auf allen Servern, auf denen die Availability Group läuft, eingerichtet werden.

Dabei muss sichergestellt werden, dass von allen Servern auf den zentralen MDW-Server zugegriffen werden kann.

Dazu muss (vor SQL Server 2014) bei der Konfiguration des MDW über den „Configure Management Data Warehouse Wizard“ der SQL Server Agent Account der Client-Instanz auf dem MDW-Server in die mdw_writer-Rolle aufgenommen werden (mdw_admin ist nicht notwendig):

From SQL Server 2014, at the configuration of the data collection in the client, it is possible to provide a SQL Server Agent Proxy of the type “Operating System (CmdExec)” as account for the access to the central MDW-Server:

Ab SQL Server 2014 kann man bei der Konfiguration der Data Collection auf dem Client einen SQL Server Agent Proxy vom Typ „Operating System (CmdExec)“ als Konto für den Zugriff auf den zentralen MDW-Server hinterlegen:

In this case, it is of course required to authorize the underlying Windows account in the server, instead of the agent itself, as „mdw-writer“.

In diesem Fall muss auf dem Server natürlich der dahinterstehende Windows-Account anstelle des Agents selber als „mdw_writer“ berechtigt werden.

As soon as all clients are authorized accordingly, one can read the data of all SQL Server AG nodes in the central management server. Depending on which server a database is currently present in in the primary role, it will then appear in the according subreport.
- This proceeding also works with mirrored databases in Database Mirroring scenarios – there one can only have one “partner instance” though.

Sobald alle Clients entsprechend berechtigt sind, kann man auf dem zentralen Management Server die Daten aller SQL Server Knoten der AG auslesen. Je nachdem auf welchem Server eine Datenbank gerade in der Primary-Rolle vorliegt, erscheint diese dann in dem entsprechenden Subreport.
- Diese Vorgehensweise funktioniert auch mit gespiegelten Datenbanken bei Datenbankspiegelungs-Szenarien – dort gibt es jedoch maximal eine „Partner-Instanz“.

(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: