Dienstag, 13. Dezember 2011

Skripts für das Sichern und Pflegen von Datenbanken

Was sind die drei Kardinalpflichten eines jeden Datenbankadministrators? Richtig:
  • Sichern
  • Indexe pflegen
  • Konsistenz prüfen
Bei kleinen und mittleren Datenbanken ist das mit den Wartungsplänen auch leicht möglich. In vielen Fällen sind die entsprechenden Wartungsplantasks eine einfache und gute Lösung. Aber es gibt Ausnahmen:
  • Backup und Pflege von SQL Server Express Datenbanken - diese Edition hat keinen SQL Server Agent und infolgedessen auch keine Wartungspläne.
  • Indexpflege bei sehr großen Datenbanken - Wartungspläne erstellen immer alle Indexe neu, aber das kostet bei großen Datenbanken viel zu viel Zeit und I/O. Hier ist ein differenziertes Vorgehen erforderlich.
In den Workshops zum Optimieren von SQL Server erstellen wir SQL-Skripts für diese Aufgaben. Eine gute Sache, aber professionelle, stabile Skripts zu erstellen, kostet viel Zeit. Zum Glück hat Ola Hallengren der Community freie und sehr professionelle Skripts zur Verfügung gestellt, die uns diese Aufgabe abnehmen! Das sind preisgekrönte, gut nachvollziehbare gespeicherte Prozeduren, die wir einfach verwenden dürfen.
Danke, Ola!

http://ola.hallengren.com

Freitag, 25. November 2011

Berichtsvorlagen erstellen

Was Reporting Services noch fehlt, um als unternehmensweite Berichtslösung wirklich professionell zu werden, wären dynamische Berichtsvorlagen. Diese sind jedoch leider auch in der Version 2012 noch immer nicht vorgesehen.
Aber wenn Sie ein Berichtsprojekt starten, können Sie sich mit einer Berichtsvorlage das Leben einfacher machen. Diese können Sie ganz einfach so erstellen:
  1. Erstellen Sie einen Bericht, der nur das Gerüst enthält, also Kopfzeile, Fußzeile, Logo usw.
  2. Kopieren Sie die .rdl Datei dieses Berichts in folgendes Verzeichnis:
    C:\Program Files\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject\
  3. Wenn Sie nun mit einem Rechtsklick im Solution Explorer auf "Reports" den Punkt "Add New Item..." auswählen, dann wird Ihnen die Berichtsvorlage zur Auswahl angeboten.
Das spart eine Menge Arbeit, weil jeder Bericht schon mal dieselbe "Grundausstattung" hat.
Schade ist nur, dass Sie bei einer Änderung des Berichtslayouts (zum Beispiel des Logos) jeden bis dahin erstellten Bericht einzeln anpassen müssen.

Daher der Tipp:
Erstellen Sie so wenige Berichte wie möglich und realisieren Sie Unterschiede zwischen Berichtsvarianten durch die Verwendung von Parametern.

Speicherort der Berichtsvorlage


Auswahl der Berichtsvorlage im Report Designer

Montag, 21. November 2011

SSRS Berichte in SharePoint darstellen

SharePoint ist schon eine gute Sache: Alle Mitarbeiter im Unternehmen nutzen dieselbe Benutzeroberfläche für Dokumente, Workflows - und eben auch Berichte. Die Umsetzung erfolgt mit Hilfe des Report Viewer Web Parts. Berechtitge SharePoint Anwender klicken eine WebPart-Seite zusammen und nutzen darin das Report Viewer Web Part, um Berichte anzuzeigen.

Der Weg dahin ist allerdings etwas holprig. Nachfolgend finden Sie ein paar Hinweise, die Ihnen hoffentlich den Start erleichtern.
  1. Zunächst sollten Sie klären, ob der Report Server, auf dem die Berichte bereitgestellt werden, im "Native Mode" oder im "SharePoint Integrated Mode" betrieben wird. Native Mode bedeutet, dass die Berichte außerhalb von SharePoint mit der Web-Anwendung "Report Manager" verwaltet werden. SharePoint Integrated Mode bedeutet, dass die Berichte in SharePoint Bibliotheken verwaltet werden.
  2. Warum ist das wichtig? Nun, es gibt dummerweise zwei unterschiedliche Web Parts in SharePoint, die gleich heißen, sich aber dennoch deutlich unterscheiden! Um Berichte von einem Report Server darzustellen, der im Native Mode betrieben wird, benötigen Sie das Report Viewer Web Part 2.0. Um hingegen Berichte von einem Report Server darzustellen, der im Integrated Mode betrieben wird, benötigen Sie das Report Viewer Web Part 3.0! Nicht gerade intuitiv...
  3. Wenn Sie nun mit diesem Wissen ausgestattet in SharePoint eine WebPart-Seite erstellen möchten, treffen Sie auf die nächste Hürde: Es wird nur das WebPart 3.0 angeboten (siehe Screenshots weiter unten).
  4. Um die Version 2.0 zu installieren, folgen Sie dieser Dokumentation: http://msdn.microsoft.com/de-de/library/ms159772.aspx Diese Anleitung bietet gute Hintergrundinformationen, ist aber in einem wesentlichen Punkt nicht vollständig. Dort ist beschrieben, dass Sie die zu installierende CAB-Datei in diesem Verzeichnis finden: C:\Program Files\Microsoft SQL Server\100\Tools\Reporting Services\SharePoint. Das gilt allerdings nur für 32-Bit Systeme. Mal im Ernst: Wer installiert heute noch einen Server mit 32-Bit Betriebssystem? Bei 64-Bit Systemen ist der Pfad anders C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Reporting Services\SharePoint. Im Nachhinein nachvollziehbar, aber ich wünsche Ihnen, dass Sie diesen Hinweis lesen, bevor Sie so viel Zeit in die Suche stecken, wie ich das getan habe.
Hier nun noch einige Bilder:
Die Web Parts Report Explorer 2.0 und Report Viewer 2.0 finden Sie in der Kategorie "Miscelaneous".
Das Web Part Report Viewer 3.0 finden Sie in der Kategorie "SQL Server Reporting"

Installation von SQL Server 2012 RC0

Pünktlich zum Beginn des Wochenendes hat Microsoft die Vorab-Version SQL Server 2012 RC0 (Release Candidate 0) veröffentlicht! Leider habe ich einige Zeit mit einem nicht funktionsfähigen Setup vertan.
Hier meine Erkenntnisse, damit es Ihnen nicht genauso ergeht.
Es gibt zwei Möglichkeiten, das Setup für SQL Server 2012 RC0 herunterzuladen:

  1. Über die MSDN, wenn Sie ein entsprechendes Abonnement haben.
  2. Über http://www.microsoft.com/sqlserver 
Die Variante 2. hat bei mir nicht funktioniert. Ich habe es auf unterschiedlichen, frisch installierten Betriebssystemen versucht (Windows 7, Server 2008, Server 2008 R2) und bin immer auf denselben unspezifischen Fehler gestoßen. Nach dem Bestätigen der Lizenzbedingungen bricht die Installation ab mit der Meldung "Incorrect Function".

Fazit: Installieren Sie SQL Server 2012 RC0 ausschließlich über das MSDN Download. Das ging bei mir problemlos.

Dienstag, 4. Oktober 2011

Blick ins Transaktionsprotokoll

Das Transaktionsprotokoll ist der Dreh- und Angelpunkt für die Transaktionsverarbeitung im SQL Server. Außerdem spielt es eine zentrale Rolle bei der zeitnahen Wiederherstellung einer Datenbank (point-in-time recovery). Kein Wunder also, dass im Rahmen von Schulung und Beratung stets viele Fragen zu diesem Thema zu klären sind. Meistens ist es völlig ausreichend, die Funktionsweise mit den SQL Befehlen BACKUP und RESTORE sowie mit den Berichten im SQL Server Management Studio (SSMS) zu verdeutlichen.

Was jedoch, wenn die Fragen tiefer gehen? Zum Beispiel: "Ich möchte gerne sehen, was genau der SQL Server in das Transaktionsprotokoll schreibt." Die sonst von mir so hochgelobte Dokumentation "Books Online" schweigt sich hierzu aus. Aber es gibt eine undokumentierte Funktion, mit der Sie den Inhalt des Transaktionsprotokolls anzeigen lassen können: fn_dblog().

SELECT * FROM fn_dblog (NULL, NULL);

Das Ergebnis ist eine sehr breite Tabelle mit allen Informationen über den Inhalt des Transaktionsprotokolls:


Paul Randal hat ein Video veröffentlicht, das sehr schön die feinen Unterschiede verdeutlicht, die es beim Loggen von ALTER INDEX REBUILD in den beiden Wiederherstellungsmodellen SIMPLE und FULL gibt. Wie alles von Paul Randal ist auch dieses Video sehr lohnenswert, wenn man einen tiefen Blick hinter die Kulissen der SQL Server Oberfläche werfen möchte.

Dienstag, 30. August 2011

Demos und Videos zu PerformancePoint Services 2010

Microsoft bietet manchmal unglaublich gute Dinge völlig kostenlos an - das Problem ist oft nur, dass man sie finden muss. In diesem Fall ging es mir darum, das Erstellen von Scorecards in PerformancePoint Services 2010 zu verstehen. Auf der Suche nach geeigneten Demos stieß ich auf folgende Perle:

SQL Server 2008 R2 Update for Developers Training Kit (May 2011 Update)

Hinter diesem Wortungetüm verbirgt sich eine unglaublich reichhaltige Sammlung von Videos, PowerPoint Dateien und Demo-Anleitungen zu allen Neuerungen, die mit SQL Server 2008 R2 ausgeliefert wurden. Neben den PerformancePoint Services (Warum die wohl hier herein gerutscht sind, obwohl sie eigentlich ein Bestandteil von SharePoint 2010 sind?) finden sich hier unter anderen auch Demos zu folgenden Themenbereichen:
  • Date and Time Data Types
  • Spatial Data Types
  • FILESTREAM Blob Storage
  • .NET CLR Integration
  • Data-tier Application Framework
  • StreamInsight
  • Reporting Services
  • Excel 2010 and PowerPivot
Einzige kleine Einschränkung: Das alles wird ausschließlich in Englisch angeboten.

So, jetzt muss ich mich aber wieder in die Übung vertiefen...

Montag, 29. August 2011

Firewall Konfiguration für Analysis Services

Dieser Beitrag ist mal wieder mehr eine Gedächtnisstütze für mich, da ich nun zum wiederholten Mal darüber gestolpert bin. Es geht um die Konfiguration der Firewall für SSAS.
Nach Installation einer benannten Instanz wird die Portnummer zufällig generiert. Da der Server in aller Regel hinter einer Firewall betrieben wird, ist das nicht so gut. Also sind die nächsten Schritte klar:
  • Vergeben einer festen Portnummer für die SSAS Instanz.
  • Freigeben des TCP/IP Ports in der Firewall.
Was nicht so offensichtlich ist (aber in Books Online natürlich beschrieben wird ;-) ist der erforderliche dritte Schritt:
  • Freischalten des TCP/IP Ports 2382 in der Firewall. Dieser wird benötigt, damit der Client den SQL Server Browser erreicht und ihn "fragen" kann, auf welchem Port die SSAS Instanz ihre Dienste anbietet.
Die folgenden Bilder sollen das veranschaulichen:
Eigenschaften der SSAS Instanz: Port 15101
Inbound Rule in der Firewall des Servers: Ports 15101 und 2382

Sonntag, 21. August 2011

Glauben Sie alles, was Sie hier lesen?

Paul S. Randal hat in einem großartigen Blogeintrag darauf hingewiesen, dass wir kritisch mit allen Informationen umgehen sollten, die wir über SQL Server erhalten. Das betrifft alle Quellen:
  • gelegentlich sogar White Papers von Microsoft
  • SQL Server Tools von Drittanbietern
  • Blog-Einträge
  • Foren im Internet
Zu diesen falschen Hinweisen bietet er in seinem Beitrag einige lesenswerte Beispiele.
Der Tenor des Artikels ist: Gehen Sie kritisch mit den Informationen um, die Sie über all diese Quellen erhalten und holen Sie sich vor Entscheidungen eine zweite Meinung ein.

Diese Haltung von Paul kann ich nur unterstreichen! In meinem Blog schreibe ich nur über Dinge, die ich selbst ausprobiert habe und von denen ich denke, dass ich sie verstanden habe. Nichtsdestotrotz kann es sein, dass sich Fehler einschleichen und dass sich vielleicht auch die Voraussetzungen im Lauf der Entwicklungsgeschichte von SQL Server verändern.

Daher meine Aufforderung an Sie als Leser:
Falls Ihnen Dinge, über die ich schreibe, nicht plausibel vorkommen oder wenn Sie andere Meinungen dazu kennen, dann lassen Sie mich das wissen. Ich freue mich über alle kritischen Fragen und lerne gerne Neues, das ich dann als Berater, als Trainer oder in diesem Blog wieder mit Ihnen teile.

Viel Spaß beim Lesen von Paul Randals Artikel - hier noch mal die vollständige URL:
http://www.sqlskills.com/BLOGS/PAUL/post/Do-yourself-a-favor-Trust-No-One.aspx

Freitag, 1. Juli 2011

Richtig indizieren - ein paar Grundregeln

Indexe sind wichtig um Datenbankabfragen zu beschleunigen - so weit, so klar. Mit den richtigen Indexen werden Benutzerabfragen blitzschnell beantwortet. Datenbankentwickler und Datenbank-Administrator können sich dann so lässig zurücklehnen wie diese Dame.
Feger (c) F. Vahlbruch
Welches sind die richtigen Indexe für meine Datenbank?

Nun liefert Microsoft mit dem SQL Server keine harten, vorgegebenen Regeln für die Indexerstellung. Das ist alleine deswegen schon nicht möglich, weil es von den Benutzerabfragen abhängt, was gerade der "richtige" Index ist. Aber es gibt einige Grundregeln, deren Verständnis Ihnen das Arbeiten mit Indexen erleichtern wird.
Bevor ich diese Regeln aufliste, möchte ich noch kurz in Erinnerung rufen, dass SQL Server zwei grundsätzlich unterschiedliche Indextypen kennt:
  • Gruppierter Index (clustered index) - definiert die Reihenfolge der Datensätze in der Tabelle. Daher kann es immer auch nur einen gruppierten Index pro Tabelle geben.
  • Nichtgruppierter Index (nonclustered index) - es kann beliebig viele nichtgruppierte Indexe zu einer Tabelle geben.

Regeln für gruppierte Indexe
  1. Jede Tabelle sollte einen gruppierten Index (clustered index) haben. Der gruppierte Index gibt die Reihenfolge vor, mit der die Datensätze in der Tabelle gespeichert werden. Die Spalte, auf die der gruppierte Index erstellt wird, sollte folgende Eigenschaften aufweisen:
    1. Sie sollte häufig in Abfragen verwendet werden.
    2. Sie sollte eine hohe Selektivität aufweisen, d. h. in dieser Spalte sollten möglichst wenige doppelte Werte vorkommen. Zum Beispiel ist der Nachname eines Kunden nicht so selektiv wie die Kundennummer.
    3. Der Wert in dieser Spalte sollte sich möglichst nicht ändern. Jede Änderung eines Wertes in einer Spalte, die in einem gruppierten Index enthalten ist, zieht eine Aktualisierung aller nonclustered-Index-Einträge des betreffenden Datensatzes nach sich.
  2. SQL Server erstellt standardmäßig einen gruppierten Index auf die PRIMARY KEY Spalte(n). Das ist in den meisten Fällen auch sinnvoll, denn auf den Primärschlüssel treffen alle vorstehenden Eigenschaften zu. Sie sollten sich aber darüber klar sein, dass Sie bei Bedarf auch einen gruppierten Index auf andere Spalten erstellen können (DROP INDEX und CREATE INDEX).
Allgemeine Regeln für Indexe
  1. Ein Index sollte nicht mehr Spalten enthalten als nötig. Je "breiter" ein Index ist, desto mehr Arbeitsspeicher ist erforderlich um die Indexinformationen zu verarbeiten.
  2. Erstellen Sie nicht mehr Indexe als nötig. Jeder INSERT, UPDATE und DELETE Befehl erfordert eine Aktualisierung aller Indexe der Tabelle. Meist wiegt der Geschwindigkeitsvorteil beim SELECT den Aufwand für die Aktualisierung mehr als auf - aber geschenkt bekommen wir diesen Vorteil nicht.
  3. Aus demselben Grund ist es nicht sinnvoll mehrere Indexe zu haben, welche dieselben Spalten beinhalten. Sie kosten nur Speicherplatz und Performance bei Datenänderungen.
  4. Ebenfalls sollten Sie die Indexe entfernen, die nicht mehr benutzt werden. Hierfür gibt es einen Datenbank-Bericht (Rechtsklick auf die Datenbank, Menüpunkt "Berichte") mit dem Namen "Statistik zur Indexverwendung". Dort können Sie die Indexe identifizieren, die sehr lange nicht mehr verwendet wurden. Aber Vorsicht: sicherheitshalber sollten Sie vor das CREATE INDEX Skript vorher erstellen und an einem Ort speichern, wo Sie es wiederfinden - man weiß ja nie...
Werkzeuge zur Indexerstellung

Weil das Auffinden der "richtigen" Indexe nicht trivial ist und von den Abfragen abhängt, die an die Datenbank gesendet werden, enthält SQL Server ein hervorragendes Werkzeug: Den Datenbankoptimierungsratgeber (Database Engine Tuning Advisor). Sie können damit sowohl für einzelne SQL Statements als auch für eine aufgezeichnete Arbeitslast (workloads) Indexempfehlungen generieren lassen. Machen Sie sich mit diesem Werkzeug vertraut - es lohnt sich. Oder besuchen Sie meinen Workshop zur Optimierung von SQL Server :-)
Aber bei allem Lob für das Werkzeug ist Vorsicht angebracht, denn welche der Vorschläge für neue Indexe Sie umsetzen und welche nicht, das müssen Sie entscheiden. Ich hoffe, die vorstehenden Punkte helfen Ihnen bei dieser Entscheidung.

Freitag, 24. Juni 2011

SQL Client konfigurieren

Wie überprüfe ich, mit welchem Protokoll ein Client die Verbindung zum SQL Server aufbauen möchte? Wo kann man das "mal eben" nachsehen? Die Antwort ist (wie fast immer) einfach, wenn man das richtige Werkzeug kennt. In diesem Fall hat das Werkzeug den kryptischen Namen cliconfg.exe.

Auswahl der Protokolle mit cliconfg.exe
Es wird mit verschiedenen Produkten installiert (z. B. SQL Server) und ist in der MDAC 2.1 ab SP2 enthalten. Daher ist es in der Regel auf allen Clients installiert.

Mit cliconfg.exe lassen sich einerseits die Protokolle einstellen, über die der Client Kontakt zum SQL Server aufnimmt. Wenn Sie im Unternehmen ausschließlich TCP/IP einsetzen, dann brauchen Clients erst gar nicht zu versuchen, über das Named Pipes Protokoll eine Verbindung zum SQL Server herzustellen.
Darüber hinaus kann das Programm auch verwendet werden, um Alias-Namen für SQL Server zu erstellen. Das kann nützlich sein, wenn eine Client Anwendung einen bestimmten Servernamen erwartet, dieser sich aber geändert hat. Das folgende Bild zeigt zwei konfigurierte Alias-Namen.

Alias-Namen für Serververbindungen

Der untere Alias zum Beispiel heißt "Testserver" und verweist auf die SQL Server Instanz LEV\INST1. Beim Konfigurieren einer ODBC Verbindung kann nun "Testserver" als Servername verwendet werden, wie das folgende Bild zeigt.

ODBC Verbindung zum SQL Server "Testserver"

Tatsächlich wird die ODBC Verbindung zum SQL Server LEV\INST1 hergestellt. Als Kommunikationsprotokoll verwendet der Client TCP/IP.

Übrigens ist der Alias auch eine Möglichkeit um zusätzlich zum SQL Servernamen auch noch die Portnummer zu hinterlegen. Der Standard-Port ist ja 1433, aber erstens muss das nicht immer so sein und zweitens wird bei benannten Instanzen in der Regel ein anderer Port verwendet. Die eleganteste Lösung hierfür ist der Einsatz des Browser Dienstes (siehe auch die Anmerkung am Ende dieses Artikels ), aber wenn dieser nicht eingesetzt werden kann, dann ist der Client Alias eine mögliche Lösung.

Die mit cliconfg.exe gemachten Einstellungen (Reihenfolge der Protokolle und Alias-Namen) werden übrigens in der Registry gespeichert:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo

Links
http://support.microsoft.com/kb/289573/en-us

Mittwoch, 22. Juni 2011

Fehler beim Erstellen eines Failover Clusters

Ich wollte in diesem Blog über merkwürdige Dinge berichten - hier ist etwas wirklich Merkwürdiges!
Zum Einrichten eines Failover Clusters mit zwei Knoten startete ich den Cluster Manager auf auf dem ersten Knoten und führte dort den Assistenten zur Validierung aus. Dabei erhielt ich die folgende Meldung:

Unable to determine if you have administrator privileges on server 'servername'. Please ensure sure that the server service and remote registry services are enabled, and that the firewall is properly configured for remote access.

Weil mich Fehlermeldungen in einem Cluster immer besonders beunruhigen und ich auch mit dem Setup nicht weiter kam, ging ich der Sache auf den Grund. Die genannten Dienste liefen beide und auch ein vorübergehendes komplettes Ausschalten der Firewalls beider Knoten brachte keine Änderung

Um es kurz zu machen:
Auf einem der beiden Knoten hatte ich das Protokoll "File and Printer Sharing for Microsoft Networks" deaktiviert.


Das Bild zeigt die Einstellung, mit der es funktioniert. Anscheinend braucht der Assistent zur Validierung eines Clusters dieses Protokoll, um zu ermitteln, ob die Anmeldung, mit der der Assistent ausgeführt wird, über ausreichend Rechte verfügt.

Fazit:
Ein schwer zu findender Fehler, der viel Zeit kosten kann. Die Fehlermeldung ist nicht sehr hilfreich, da sie nicht alle Fehlerursachen auflistet.

Dienstag, 21. Juni 2011

Wie erhält der SQL Server Dienst seine Rechte?

Die Botschaft ist nicht neu aber wichtig: Der SQL Server Dienst sollte mit einem Benutzerkonto ausgeführt werden, das mit minimalen Rechten ausgestattet ist. Um das zu erreichen, erstellen Sie ein Windows Benutzerkonto (lokal oder in der Domäne) und weisen mit Hilfe des Programms SQL Server Configuration Manager (SSCM) dieses Konto dem SQL Server Dienst zu. Das funktioniert einfach und sicher.

Aber was genau macht der Configuration Manager?

Der erfahrene Berater antwortet: "Das kommt drauf an..."
Es kommt auf die SQL Server Version und auf das Betriebssystem an.
  • Fall 1: SQL Server 2005 oder Betriebssystem XP / Windows 2003 Server
  • Fall 2: SQL Server 2008 (und neuer) und Betriebssystem Vista / Windows 2008 (und neuer)
Fall 1
Dieser Fall trifft immer zu, wenn Sie SQL Server 2005 installieren. Er trifft aber auch zu, wenn Sie SQL Server 2008 (und neuer) auf einem "Pre-Vista" Betriebssystem installieren (XP oder Windows 2003 Server).
In diesem Fall werden bei der Installation der SQL Server Instanz Windows Gruppen angelegt, die lange aber sprechende Namen haben, zum Beispiel: SQLServer2005MSSQLUser$AC$SQL2005
In diesem Fall ist AC der Name der Windows Maschine und SQL2005 der Name der SQL Server Instanz. Dieser Windows Gruppe werden alle Berechtigungen erteilt, die der SQL Server Dienst benötigt. Das sind Zugriffe auf Ordner, Dateien und Registry Einträge. Sie können das zum Beispiel sehen, wenn Sie sich die Berechtigungen auf die Datei sqlservr.exe ansehen:
ACL für sqlservr.exe (Fall 1)
Die Windows Gruppe SQLServer2005MSSQLUser$AC$SQL2005 hat auf diese Datei Lese- und Ausführungs-Berechtigung. Wenn Sie mit SSCM das Dienstkonto ändern, dann wird dieses Konto Mitglied der Windows Gruppe SQLServer2005MSSQLUser$AC$SQL2005 und hat somit alle erforderlichen Berechtigungen.
Der technische Ausdruck für die Erteilung der Berechtigungen heißt übrigens Access Control List (ACL). Das obige Bild ist die grafische Darstellung dieser Zugriffsliste für die Datei sqlservr.exe
Das folgende Bild zeigt, dass das Dienstkonto von SQL Server Mitglied in der Gruppe SQLServer2005MSSQLUser$AC$SQL2005 ist.
Mitglieder der Windows Gruppe MSSQLUser (Fall 1)

Fall 2
Etwas anders sieht es aus, wenn Sie SQL Server 2008 (oder neuer) auf einem aktuellen Betriebssystem (ab Vista / Windows Server 2008) installieren. Auch dann erhält die Windows Gruppe die erforderlichen Berechtigungen auf Ordner, Dateien und Registry Einträge:
ACL für sqlservr.exe (Fall 2)
In diesem Fall heißt die Windows Gruppe SQLServerMSSQLUser$LEV$MSSQLSERVER, weil der Windows Server den Namen LEV hat und die SQL Server Instanz die Standardinstanz ist. So weit, so gut. Aber wenn wir nun nachsehen, wer Mitglied dieser Windows Gruppe ist, erleben wir eine Überraschung:
Mitglieder der Windows Gruppe MSSQLUser (Fall 2)
Mitglied dieser Gruppe ist nicht das SQL Server Dienstkonto, sondern der SQL Server Dienst selbst! Man sagt auch, es handelt sich um die Service-SID. SID ist die Abkürzung für Security-ID; das ist die Nummer S-1-5-80-..., die hinter dem Namen NT Service\MSSQLSERVER steht.
Und was bedeutet diese Änderung gegenüber Fall 1?
  • Die Service-SID ändert sich nicht, wenn das Dienstkonto geändert wird. SSCM muss also nicht mehr das Dienstkonto in die Windows Gruppe aufnehmen.
  • Das Dienstkonto bekommt keine Berechtigungen mehr auf die Ordner, Dateien und Registry Einträge der SQL Server Instanz. Die Folge: höhere Sicherheit. Denn selbst wenn nun ein Eindringling das Passwort des Dienstkontos in Erfahrung bringt, erhält er damit keine Zugriffsberechtigungen auf die Dateien der SQL Server Instanz.

Änderungen bei sysadmin
Diese Änderung betrifft auch die Mitglieder in der Serverrolle sysadmin. Beim SQL Server 2005 (genauer: im Fall 1) sind die Anmeldungen der Windows Gruppen MSSQLUser und SQLAgentUser Mitglieder dieser kritischen Rolle. Wer also das Passwort des Dienstkontos von SQL Server oder SQL Server Agent errät, der kann mit der SQL Server Instanz tun und lassen, was er/sie möchte!
Mitglieder der sysadmin-Rolle (Fall 1)
Seit SQL Server 2008 (genauer: im Fall 2) sind die Service-SID von SQL Server und SQL Server Agent Mitglieder dieser Rolle. Eine Angriffsmöglichkeit weniger!
Mitglieder der sysadmin-Rolle (Fall 2)


Links
Dieser Beitrag von Dan Jones hat mir beim Lösen des Rätsels geholfen:
http://blogs.msdn.com/b/dtjones/archive/2010/12/15/changing-service-account-amp-service-account-password.aspx

Was ist Datenbankdatei-Initialisierung?

Wenn Sie große Datenbanken administrieren, haben Sie sicherlich schon mehr als einmal auf den SQL Server gewartet, zum Beispiel beim
  • Erstellen von Datenbanken
  • Vergrößern von Datendateien
  • Hinzufügen neuer Datendateien
  • Wiederherstellen einer Datenbank aus einem Backup
Es gibt ein recht unbekanntes Feature, mit dem Sie diese Wartezeiten deutlich verkürzen können. Dieses hat den Namen Sofortige Dateiinitialisierung (Instant File Initialization). Um einen Eindruck von der Beschleunigung zu bekommen, habe ich auf meinem Notebook eine 20GB große Datenbank angelegt. Hier die Zeiten:
  • ohne sofortige Dateiinitialisierung: 2:10 Minuten
  • mit sofortiger Dateiinitialisierung: 0:02 Minuten
Wie funktioniert es?
Normalerweise füllt SQL Server eine neue Daten- oder Transaktionsprotokoll-Datei direkt nach der Erstellung mit "0"-Werten. Wenn sofortige Dateiinitialisierung aktiviert ist, dann werden bei Datendateien keine "0"-Werte geschrieben (bei Transaktionsprotokoll-Dateien wird das in jedem Fall gemacht). Eigentlich wird die Datendatei also nicht initialisiert, wenn sofortige Dateiinitialisierung aktiv ist - aber die Namensgebung ist nun einmal so.

Wie wird es aktiviert?
Der Grund, warum dieses Feature so wenig bekannt ist, ist wahrscheinlich darin begründet, dass es nicht eine Option des SQL Servers ist, sondern dass es von den Berechtigungen des SQL Server Dienstkontos abhängig ist. Um die Berechtigung zu erteilen, starten Sie das Programm secpol.msc.
Hier wählen Sie unter "Sicherheitseinstellungen" - "Zuweisen von Benutzerrechten" - "Durchführen von Volumenverwaltungsaufgaben" und fügen im nachfolgenden Dialog das Benutzerkonto, mit dem der SQL Server Dienst ausgeführt wird, hinzu.
Falls das Konto bereits Mitglied der lokalen Administratoren-Gruppe ist, dann ist dieser Schritt nicht erforderlich, denn diese Windows Gruppe hat bereits diese Berechtigung. Aber mal ehrlich: so etwas haben Sie doch nicht gemacht, oder? Um SQL Server sicher zu betreiben, ist es äußerst wichtig, dass er mit einem Dienstkonto ausgeführt wird, das minimale Berechtigungen hat. Und die Mitgliedschaft in der Gruppe lokaler Administratoren ist ziemlich genau das Gegenteil von minimalen Rechten ;-)
Erstellen Sie also für jede SQL Server Instanz ein eigenes neues Benutzerkonto (Domänenkonto oder lokales Konto) und nutzen Sie dann das Programm SQL Server Configuration Manager, um dieses Konto dem SQL Server Dienst zuzuweisen.
Der Configuration Manager sorgt dafür, dass das SQL Server Dienstkonto mit allen erforderlichen Berechtigungen ausgestattet wird (Zugriff auf Registry Einträge, Zugriff auf Dateien, etc.). Es ist also ganz einfach, ein Dienstkonto mit minimalen Berechtigungen auszustatten.

Wo ist der Haken?
Für jeden Vorteil müssen wir bezahlen. Beim SQL Server bezahlen wir in der Regel entweder mit Geld (Hardware, Lizenzen) oder indem wir Nachteile an anderer Stelle in Kauf nehmen. In diesem Fall ist der Preis eine verringerte Sicherheit. Dadurch, dass die Datendatei nicht initialisiert wird, enthält sie die Inhalte der Festplatte, die vorher dort gespeichert waren. Diese Inhalte kann man sich anschauen:
  • mit DBCC PAGE (undokumentierte Option) bei laufender Datenbank
  • mit einem Dateieditor, nachdem die Datendatei abgehängt wurde
  • in der Datensicherung
Ist das für Ihre SQL Server Instanz ein Problem? Das müssen Sie entscheiden. Ich denke, dass dies in den allermeisten Fällen akzeptabel ist, da Sie ja sowieso dafür sorgen, dass Datendateien und Backups nicht in falsche Hände gelangen können.

Links
Hier habe ich die Informationen für diesen Beitrag gefunden:
http://msdn.microsoft.com/de-de/library/ms175935.aspx
http://www.sqlskills.com/blogs/kimberly/post/Instant-Initialization-What-Why-and-How.aspx
http://www.brentozar.com/sql/blitz-minute-sql-server-takeovers/

Mittwoch, 15. Juni 2011

Reguläre Ausdrücke im SQL Server

So mächtig die Sprache SQL auch ist - reguläre Ausdrücke zum Mustervergleich sind nicht Bestandteil der ANSI Spezifikation. Wenn es also darum geht, mit SQL das Format einer Telefonnummer oder einer E-Mail Adresse zu überprüfen, dann wäre eine Erweiterung des Sprachumfangs hilfreich.
Genau das können mit .NET programmierte benutzerdefinierte Funktionen leisten. Schließlich bietet das .NET Framework eine Klasse RegularExpressions, die genau die hierfür benötigten Methoden enthält.

Das Problem

Wie erstelle ich eine benutzerdefinierte Funktion mit .NET Code, um sie anschließend im SQL Server zu verwenden?

Die Lösung
  1. Erstellen Sie in Visual Studio ein Projekt vom Typ "SQL Server Projekt". Dafür benötigen Sie Visual Studio Professional - die mit SQL Server ausgelieferte Version "Business Intelligence Development Studio" kann das nicht.
  2. Veröffentlichen Sie die Funktion im SQL Server
  3. Nutzen Sie diese Funktion in Ihren SQL Statements.
Nachdem Sie in Visual Studio ein neues SQL Server Projekt angelegt haben, wird folgender Rahmen erstellt:





Erweitern Sie nun diesen Rahmen:
  • Fügen Sie den Namespace System.Text.RegularExpressions hinzu. Darin sind die Methoden zum Verarbeiten regulärer Ausdrücke vorhanden.
  • Wir verwenden in diesem Beispiel die Methode Match(), um zu überprüfen, ob der Parameter matchString dem Muster pattern entspricht.


Das Veröffentlichen der Funktion erledigt Visual Studio vollautomatisch. Hinter den Kulissen führt es dabei folgende Schritte aus:
  1. Es kompiliert die DLL-Datei (Assembly) mit dem CLR-Code der neu erstellten Klasse UserDefinedFunctions.
  2. Es lädt diese Assembly wird in den SQL Server:
    CREATE ASSEMBLY <name der klasse> FROM <dateipfad>
  3. Dann deklariert es die Methode udf_RegExMatch als Funktion, deren Programmcode in dieser Assembly liegt:
    CREATE FUNCTION udf_RegExMatch (@pattern nvarchar(4000), @matchString nvarchar(4000))
    RETURNS int
    AS EXTERNAL NAME <name der methode>
Wohlgemerkt - diese Schritte müssen Sie nicht manuell ausführen, weil Visual Studio das für Sie erledigt. Sie können das überprüfen mit den folgenden Anweisungen:

select * from sys.assembly_files
select * from sys.assembly_modules

Danach finden Sie diese Funktion im SQL Server Management Studio (Object Explorer) unter "Programmierbarkeit" - "Funktionen". Bevor Sie diese Funktion testen können, muss zunächst die Ausführung von .NET Code zugelassen werden. Das ist standardmäßig nicht erlaubt.

exec sp_configure 'clr enabled', 1;
reconfigure


Sie können die Funktion nun testen, zum Beispiel mit folgendem Statement:

SELECT [DemoDB].[dbo].[udf_RegExMatch] (
   '[a-zA-Z0-9_\-]+@([a-zA-Z0-9_\-]+\.)+(com|org|edu|de)'
  ,'v.heck@netcologne.de')


Das Select Statement liefert den Wert 1 zurück, wenn die E-Mail Adresse v.heck@netcologne.de dem vorgegebenen Muster entspricht; ansonsten liefert sie den Wert 0 zurück. Probieren Sie es aus!
Mit dem folgenden Statement beispielsweise können Sie alle E-Mail Adressen in der Tabelle Person.EmailAddress in der AdventureWorks2008R2 Datebank überprüfen:

select
 EmailAddress,
 DemoDB.dbo.udf_RegExMatch

   ('[a-zA-Z0-9_\-]+@([a-zA-Z0-9_\-]+\.)+(com|org|edu|de)', 
    EmailAddress)
from AdventureWorks2008R2.Person.EmailAddress


Fazit

Anhand dieses Beispiels ließ sich zeigen, wie eine Funktion in .NET programmiert werden kann, die anschließend als Funktion im SQL Server zur Verfügung steht. Wie so oft beginnt die eigentliche Arbeit im Projekt dort, wo diese Demo aufhört. Insbesondere haben wir noch keinerlei Fehlerbehandlung hinzugefügt. Und auch die Performance-Aspekte haben wir bisher außer acht gelassen. Denn so, wie wir die Aufgabenstellung bisher gelöst haben, wird bei jedem Aufruf der Funktion die Regex neu instanziiert. Bei größeren Datenmengen (siehe zweites SELECT Statement) kann das zu einem echten Performance-Engpass werden.
Mein Ziel war es, hier das Prinzip aufzuzeigen. Weiter führende Informationen finden Sie zum Beispiel hier:

http://msdn.microsoft.com/de-de/magazine/cc163473.aspx
http://blogs.msdn.com/b/sqlclr/archive/2005/06/29/regex.aspx

Dienstag, 31. Mai 2011

Poster mit Perfmon Leistungsindikatoren

Mit Perfmon lassen sich die Leistungsindikatoren von SQL Server beobachten und aufzueichnen. Ein großartiges Werkzeug, um Performance-Probleme aufzudecken und zu analysieren! Zudem ja auch die Indikatoren des Betriebssytems gemeinsam mit denen von SQL Server aufgezeichnet werden können, um so Korrelationen bei Engpässen zu entdecken.


Das Problem dabei ist nur: Es gibt eine solche Vielzahl von ihnen, dass es nicht offensichtlich ist, welche Indikatoren man beobachten sollte. Hat man dann die vermeintlich richtigen Leistungsindikatoren für die jeweilige Analyse herausgefunden, stellt sich direkt die nächste Frage: Was sind jeweils zulässige Werte und welche Werte weisen auf ein Problem hin?

Die Hilfe:

Bei Quest gibt es ein großartiges, von MVP Kevin Kline erstelltes Poster mit den für SQL Server interessantesten Leistungsindikatoren. Die Indikatoren sind in Gruppen zusammengefasst, so dass man schnell eine Übersicht gewinnt. Dazu kommt noch: Die Aussagekraft einzelner Indikatoren hängt vom Umfeld ab. Beispielsweise stellen sich die I/O-bezogenen Werte unterschiedlich dar, wenn die Dateien auf SAN Laufwerken oder auf lokalen Platten liegen. Kevin Kline beleuchtet in diesem Poster die bisher von Microsoft empfohlenen Grenzwerte kritisch und erläutert davon abweichende Empfehhlungen.

Fazit:

Hilfreich, lehrreich - unbedingt herunterladen!

Samstag, 30. April 2011

Architektur sicherer und wartbarer Datenbank-Anwendungen

Nie mehr Spaghetti als Code
So gerne ich Nudeln mag - in datenbankbasierten Anwendungen haben sie nichts zu suchen. In diesem Beitrag möchte ich beleuchten, wie sie trotzdem oft ihren Weg in den SQL Server finden und wie man sie wieder los wird.
Nudelleserin (c) F. Vahlbruch
In den Standard SQL Server Seminaren wird eine Menge über die Funktionsweise und den sicheren Betrieb von SQL Server vermittelt. Das ist wichtiges Handwerkszeug für jeden Administrator und Entwickler. Was meiner Ansicht nach jedoch oft zu kurz kommt, ist die richtige Architektur von SQL Server Anwendungen. Die Programmierumgebungen (zum Beispiel Visual Studio oder Report Builder) lassen dem Entwickler mehrere Wege offen. Also wird oft der direkte Weg gewählt und die Anwendungen greifen mit SELECT, INSERT, UPDATE oder DELETE direkt auf die Datenbanktabellen zu.

Ich bekenne: Lange Zeit meines Entwicklerdaseins habe ich das so gemacht.

Warum sollte man es auch anders machen?
  1. Weil dieses Vorgehen dazu führt, dass der SQL Code irgendwo in der Anwendung versteckt ist. SQL Code und Andwendungslogik werden miteinander vermengt. Auch wenn es Ihnen als Entwickler zu Beginn noch ganz klar erscheint, so direkt vorzugehen - kennen Sie nicht das Unbehagen, wenn Sie nach einem halben Jahr eine solche Anwendung erweitern müssen?
  2. Weil dieses Vorgehen zu Sicherheitslücken führt:
    • Es verleitet dazu, den SQL Code dynamisch zusammen zu bauen. Das wiederum macht Anwendungen anfällig für SQL Injection. Welch dramatische Sicherheitsprobleme dadurch entstehen können, werde ich in einem meiner nächsten Beiträge zeigen.
    • Die Anwendung erhält direkten Zugriff auf die Tabellen. Wenn ein Angreifer die Verbindungsinformationen, mit denen die Anwendung sich gegenüber dem SQL Server autentifiziert, herausfindet, dann sind die Folgen unabsehbar.
Und wie geht das besser?

Wir ziehen eine Zwischenschicht ein und erlauben externen Anwendungen nur hierauf den Zugriff. Externe Benutzer greifen nicht direkt auf die Tabellen zu, sondern über Views und Stored Procedures.

Wie das Bild zeigt, liegen die für externe Anwender (user_extern) sichtbaren Views und Stored Procedures in einem eigenen Schema, das ich Schnittstelle genannt habe. Die Tabellen hingegen liegen in einem anderen Schema (hier Anwendung genannt), auf das user_extern keine Berechtigungen hat. Der Eigentümer beider Schemas ist der Benutzer user_intern. Dieser erteilt dem von außen zugreifenden user_extern die SELECT-Berechtigung für Views und die EXECUTE-Berechtigung für Stored Procedures.
Dazu noch zwei Anmerkungen:
  • Wenn user_intern dem user_extern die SELECT Berechtigung auf Views erteilt, dann kann dieser "automatisch" auf die Tabellen im Schema Anwendung zugreifen. Technischer Hintergrund ist die hier dokumentierte Besitzkette.
  • Damit die Stored Procedures auf die Tabellen im Schema Anwendung zugreifen können, brauchen sie die Option WITH EXECUTE AS OWNER. Hier finden Sie weitere Informationen.

Was gewinnen wir durch die Zwischenschicht?

  1. Sicherheit - Externe Benutzer haben keine Möglichkeit, direkt auf die Tabellen zuzugreifen. Der Zugewinn an Sicherheit ist beträchtlich.
  2. Übersichtlichkeit - Sie können jetzt in den Views und ganz besonders in den Stored Procedures beliebig komplexe Zugriffslogik programmieren. Denken Sie nur an einen SSRS Bericht mit einem seitenlangen SELECT Statement: Sie programmieren, kommentieren und testen dieses ganz komfortabel in der Stored Procedure. Im Bericht hingegen steht lediglich der Name der Stored Procedure und ihre Parameter. Das ist viel übersichtlicher, als wenn der komplexe SQL Code im Dataset des Berichts stehen würde.
  3. Wartbarkeit - Wenn das SQL Statement überarbeitet werden muss, weil sich zugrunde liegende Tabellen geändert haben, dann brauchen Sie lediglich die Stored Procedure zu ändern. Der Bericht bleibt unverändert.
  4. Aufgabenteilung - Beim Arbeiten in einem größeren Team können Sie die Aufgaben besser trennen: SQL Programmierer bearbeiten Views und Stored Procedures. Berichtsdesigner bearbeiten Berichte. Anwendungsentwickler arbeiten mit ihrer vertrauten Hochsprache.
Viele Gründe also, Spaghetticode loszuwerden und durch eine übersichtliche, sichere Architektur abzulösen.

Samstag, 9. April 2011

Fehler bei Verwendung von Unterberichten

Gestern habe ich zum ersten Mal einen Anwendungsfall für den Einsatz von Unterberichten mit Reporting Services gesehen. Bisher konnte ich - im Unterschied zum Arbeiten mit Access - alle Berichtsanforderungen mit dem Tablix-Element abdecken. Nun aber hatte ein Kunde die Anforderung, Formblätter zu erstellen. Bei Formblättern ist die Anordnung der Ausgabefelder abhängig vom Typ des zu dokumentierenden Objekts.
Überträgt man diese Anforderung auf die AdventureWorks Datenbank, könnte man sich folgende Aufgabenstellung vorstellen: Zu jeder Rechnungsposition sollen Detailinformationen dargestellt werden. Das Layout der Detailinformationen von Fahrrädern soll völlig anders gestaltet sein als das Layout der Detailinformationen von Bekleidung.
Die Idee
In einem Listenelement wird abhängig davon, ob die Detailinformationen zu "Bikes" oder "Clothing" gehört, ein Unterbericht dargestellt.

Listenelement mit zwei Unterberichten
So weit, so gut. Nun wollten wir nur noch über die Eigenschaft "Hidden" eines jeden Unterberichts mit Hilfe einer Expression dafür sorgen, dass immer nur einer dieser Unterberichte im Bericht angezeigt wird.
Die Überraschung
Das funktioniert nicht! Die Steuerung der Sichtbarkeit funktioniert immer nur für den ersten Unterbericht - der zweite wird nie sichtbar.
Die Lösung
Wir haben über eine "AND" Bedingung in der WHERE Klausel des Subreport-Datasets dafür gesorgt, dass das Dataset im Unterbericht "Bikes" keine Datensätze enthält, wenn die Rechnungsposition kein "Bike" ist. Ebenso haben wir dafür gesorgt, dass das Dataset im Unterbericht "Clothing" keine Datensätze enthält, wenn die Rechnungsposition kein "Clothing" ist.
Auf diese Weise rendert SSRS den Bericht wie gewünscht.

Mittwoch, 9. März 2011

FreeNAS als iSCSI Target

Für Demo- und Testzwecke wollte ich mir einen Windows 2008 R2 Cluster mit virtuellen Maschinen einrichten. Dank eines gut ausgestatteten Rechners (64-Bit mit 8GB RAM) und VMware Workstation 7.1.3 sollte das eigentlich möglich sein. Als das größte Problem stellte sich die Einrichtung eines iSCSI Servers heraus, der die shared disks für die Clusterknoten bereitstellt.

Ein sehr interessantes Produkt ist sicherlich iStorageServer von KernSafe. Dieser bietet allerdings in der kostenfreien Version lediglich Zugriff für 1 Verbindung, was für einen Cluster mit 2 Knoten nicht ausreicht. Die 21-Tage Testversion hat diese Einschränkung nicht, aber ich möchte meine Cluster-Konfiguration etwas länger betreiben. 100 US-Dollar hätten das Problem gelöst, doch mir stand der Sinn nach einer kostenfreien Variante.

Die Lösung fand ich schließlich in dem Produkt FreeNAS. Dies funktioniert auf Basis des Unix Derivats FreeBSD und ermöglicht die Einrichtung eines sehr vielseitig konfigurierbaren NAS (network attached storage) Servers. Es beherrscht die Protokolle CIFS (Samba), FTP, NFS, TFTP, AFP, RSYNC, Unison, iSCSI und UPnP. Daher ist die Konfiguration beim ersten Mal nicht gerade einfach, aber dank einer hervorragenden Anleitung von Dinesh Kumar kein Problem.
Was mir besonders positiv auffält: Die Installation ist sehr schlank - die virtuelle Maschine verbraucht gerade mal 1 GB Festplattenplatz und kommt problemos mit 512 MB RAM aus.

Eine Warnung sei allerdings angebracht: Installieren Sie nicht die brandneue Version 8 (Stand 09.03.2011), sondern laden Sie sich die letzte stabile 7er Version herunter! Die 8er Version hat abgesehen von einer neuen Benutzerführung einfach noch zu viele Anzeigeprobleme.

Fazit
Hatte ich mich anfangs dagegen gesträubt, meinen Windows-Zoo auch noch um eine virtuelle Unix-Maschine zu bereichern, gefällt mir diese Lösung nun ganz besonders gut. Schließlich ist das die Realität in vielen Unternehmensnetzwerken - und da praktisch die gesamte NAS Konfiguration über eine Web-Oberfläche durchgeführt wird, fällt das zusätzliche Betriebssystem gar nicht so schwer ins Gewicht.

Jetzt kann ich endlich nach Herzenslust den SQL Server in allen Varianten clustern!

Donnerstag, 24. Februar 2011

AdventureWorks Datenbanken

Die AdventureWorks Beispiel-Datenbanken werden seit geraumer Zeit auf http://www.codeplex.com/, der Open Source Projektseite von Microsoft, angeboten. Da die Struktur der Seite etwas unübersichtlich ist, wenn man "nur mal eben" die passende Version braucht, habe ich hier die erforderlichne Links zusammengestellt.

Die Einstiegsseite liefert einen Überblick über alle Datenbanken und Codebeispiele für SQL Server.

Wenn Sie eine der Beispieldatenbanken brauchen, erreichen Sie den Download über die folgenden Links:
Außerdem gibt es zu den Versionen 2005 bis 2008 auch ER-Diagramme, die beim Verstehen der Datenbankstruktur sehr hilfreich sind. Diese finden Sie hier als Visio-Diagramme.

Sonntag, 20. Februar 2011

Parametrisierte Abfragen - die Kehrseite

In meinem letzten Blogbeitrag habe ich vehement für die Verwendung parametrisierter Abfragen geworben. Gerade dann, wenn viele Anwender immer wieder sehr änliche SQL Statements ausführen (solche, die sich nur in der WHERE Klausel unterscheiden), profitiert eine SQL Server Instanz sehr davon, dass für diese Abfragen nicht immer wieder ein neuer Ausführungsplan generiert werden muss. Manchmal bereiten parametrisierte Abfragen aber auch Probleme.

Wo ist das Problem?
Nun, beim ersten Kompilieren des Statements (= beim ersten Erstellen eines Ausführungsplans) erstellt der SQL Server den optimalen Ausführungsplan für die bei der ersten Ausführung übergebenen Parameter. In vielen Fällen ist das völlig ok, weil dieser Ausführungsplan auch dann gut funktioniert, wenn andere Parameter übergeben werden.
Aber was, wenn beim ersten Aufruf Parameter übergeben werden, die völlig untypisch sind für die nachfolgenden Aufrufe? Sie ahnen es: SQL Server speichert diesen "exotischen" Ausführungsplan und verwendet ihn bei allen weiteren Aufrufen.
Für das nachfolgende Beispiel habe ich einen zusätzlichen nichtgruppierten Index auf die Tabelle Person.Person in der Datenbank AdventureWorks2008R2 angelegt:

create index vhe_Index_Demo on Person.Person (LastName, FirstName)

Danach habe ich dann die folgende Anweisung ausgeführt:

exec sp_executesql
  @stmt = N'select FirstName, LastName from Person.Person where LastName like @p'
 ,@params = N'@p nvarchar(40)'
 ,@p = '%'

Wenn sp_executesql zum ersten Mal ausgeführt wird, dann sorgt der Parameterwert @p='%' dafür, dass ein Ausführungsplan erzeugt wird, der alle Datensätze liefert. Der beste Operator dafür ist ein Index Scan auf vhe_Index_Demo.



Wenn bei den nachfolgenden Ausführungen spezifischere Werte angegeben werden, zum Beispiel @p='Abel', dann ist ein Table Scan bestimmt nicht der beste Operator. Ein Index Seek wäre erheblich effizienter, aber da der Plan nun bereits einmal kompiliert wurde, verwendet SQL Server stur immer wieder den schlechteren Plan.
Je komplexer die Abfrage ist, desto dramatischer fällt der Unterschied aus. So habe ich in einem Fall gesehen, dass die Laufzeitunterschiede zwischen optimaler Ausführung und Ausführung mit wiederverwendetem Ausführungsplan einen Unterschied von 2 Sekunden zu 5 Minuten(!) ausmachte.

Und nun?
In einem solchen Fall müssen wir zunächst einmal herausfinden, wie ein guter Ausführungsplan aussieht. Ersetzen Sie dafür die Parameter durch typische Werte und lassen Sie sich im SQL Server Management Studio den Ausführungsplan anzeigen.

select FirstName, LastName from Person.Person where LastName like 'Abel'

Wie erwartet, generiert SQL Server hierfür einen Ausführungsplan mit einem Seek auf den Index vhe_Index_Demo:



Plan Guides
Mit der Prozedur sp_create_plan_guide lönnen wir dem Optimierer "unter die Arme greifen". Die Prozedur enthält unter anderem das SQL Statement und die Hinweise (@hints), die wir dem Optimierer mit auf den Weg geben möchten.

exec sp_create_plan_guide
  @name = N'PlanGuide_PersSuche'
 ,@stmt = N'select FirstName, LastName from Person.Person where LastName like @p'
 ,@type = N'SQL'
 ,@module_or_batch = null
 ,@params = N'@p nvarchar(40)'
 ,@hints = N'OPTION (table hint(Person.Person, FORCESEEK, INDEX(vhe_Index_Demo)))'

Der Plan Guide wird dafür sorgen, dass immer (unabhängig von den übergebenen Parametern) der erzeugte Ausführungsplan einen SEEK auf den Index vhe_Index_Demo enthält. Das ist in unserem Fall ein guter Tipp für den Optimizer.
Beim Erstellen eines Plan Guides ist es ganz wichtig, dass das SQL Statement exakt so geschrieben wird, wie das Statement, das mit sp_execute ausgeführt wird. Es kommt dabei auf Groß- und Kleinschreibung genauso an, wie auf die Leerzeichen und Zeilenumbrüche.

Hier noch ein Praxistipp:
Wenn Sie einen Plan Guide für ein SQL Statement erzeugen, dann wird der Ausführungsplan für dieses Statement sofort aus dem Query Cache entfernt. Bei der nächsten Ausführung der Abfrage wird also unter Verwendung des Plan Guides ein neuer Ausführungsplan erstellt.

Fast hätte ich es vergessen, Ihnen zu zeigen, wie Sie einen einmal erstellten Plan Guide wieder los werden:

execute sp_control_plan_guide
  @operation = N'DROP'
 ,@name = N'PlanGuide_PersSuche'

Dienstag, 1. Februar 2011

Hallo Programmierer - verwendet doch bitte parametrisierte Abfragen!

In den vergangenen Wochen habe ich gleich zwei größere SQL Server Installationen erlebt, die beide unter demselben Problem litten: Die Anwendungsentwickler generieren ihre SQL Statements für jede Abfrage neu und zwingen damit bei großen Benutzerzahlen den Datenbankserver in die Knie. Was genau steckt dahinter?
Betrachten wir einmal das folgende SQL Skript:

dbcc freeproccache         -- Cache und Buffer leeren
dbcc dropcleanbuffers
declare @i int             -- Schleifenvariable
       ,@m int             -- Obergrenze der Schleifenvariablen
       ,@cmd nvarchar(500) -- Generiertes SQL Statement
set @i = 1
select @m = MAX(BusinessEntityID) from Person.Person
while (@i < @m)
begin
   -- SQL Statement wird bei jedem Durchlauf neu generiert
   set @cmd = 'declare @Lname nvarchar(50); ' +
              ' select @Lname = LastName ' +
              'from Person.Person where ' +
              'BusinessEntityID = ' + cast(@i as nvarchar(10))
   exec (@cmd)
   set @i = @i + 1
end
go

Mein Laptop benötigt zur Ausführung dieser Schleife etwa 18 Sekunden. In jedem Schleifendurchlauf wird das SQL Statement neu generiert und die WHERE-Klausel sieht ein klein wenig anders aus. Für den SQL Server bedeutet das: Er muss für jedes Statement einen Ausführungsplan erzeugen. Und so kommt es, dass im Plan Cache so viele Varianten dieses Statements stehen, wie es Schleifendurchläufe gibt, nämlich 20.779. Über die folgende Abfrage lässt sich das ermitteln:

select entries_count
from sys.dm_os_memory_cache_counters
where type = 'CACHESTORE_SQLCP'

Das wirklich Dumme an der Sache ist also nicht nur, dass für das Erzeugen des (immer gleichen) Ausführungsplans eine Menge CPU-Leistung verbraten wird. Außerdem läuft auch noch der Plancache voll, in dem SQL Server einmal erzeugte Ausführungspläne aufbewahrt in der Hoffnung, dass sie bald wieder verwendet werden können. Das können Sie sehr gut in dem Bericht "Memory Consumption" sehen, den Sie erhalten, wenn Sie im Management Studio mit der rechten Maustaste auf die Instanz (Oberster Knoten im Object Explorer) klicken und dann "Reports - Standard Reports - Memory Consumption" auswählen.

Cache mit immer wieder neu generierter Abfrage
Das Bild zeigt den Zustand des Plancache nach Ausführung des obigen Skripts: Er ist angefüllt mit über 20.000 nutzlosen Ausführungsplänen ("Stolen Pages"). Halten Sie sich bitte vor Augen, dass dafür alle anderen zwischengespeicherten Daten und Pläne aus dem Cache entfernt wurden - das ist unter Performance-Aspekten sehr bitter.

Und wie kann man das besser machen?

Schauen Sie sich das folgende modifizierte Skript an:

dbcc freeproccache         -- Cache und Buffer leeren
dbcc dropcleanbuffers
declare @i int             -- Schleifenvariable
       ,@m int             -- Obergrenze der Schleifenvariablen
       ,@cmd nvarchar(500) -- SQL Statement
       ,@Lname nvarchar(50)
set @i = 1
select @m = MAX(BusinessEntityID) from Person.Person
-- Hier ist der Unterschied: Das SQL Statement bleibt
-- durch die Verwendung eines Parameters konstant.
set @cmd = 'select @Lname = LastName ' +
           'from Person.Person where ' +
           'BusinessEntityID = @Param1'
while (@i < @m)
begin
   exec sp_executesql @cmd
                     ,N'@Param1 int, @Lname nvarchar(50) OUTPUT'
                     ,@Param1 = @i
                     ,@Lname = @Lname OUTPUT;
   set @i = @i + 1
end
 
Das SQL Statement wird hier nur einmal vor Beginn der Schleife erzeugt und dann immer wieder unverändert ausgeführt. Der Parameter @Param1 erhält bei jedem Schleifendurchlauf den aktuellen Wert der Zählervariablen zugewiesen. Dieses SQL Skript braucht auf meinem Rechner keine 2 Sekunden, ist also ungefähr 10-mal schneller als die erste Variante.
Wie sieht die Veränderung im Cache aus? Wieder hilft uns die bereits oben verwendete Abfrage weiter:

select entries_count
from sys.dm_os_memory_cache_counters
where type = 'CACHESTORE_SQLCP'

Sie zeigt, dass jetzt gerade einmal 2 Ausführungspläne im Cache liegen. Und hier liegt der wichtigste Vorteil, denn der Cache wurde nun nicht mit unbrauchbaren Ausführungsplänen überflutet. Die Grafik zeigt das sehr schön:

Cache nach Parametrisierung der Abfrage
 Je mehr Clients parallel gleichartige Abfragen stellen, desto größer fällt der Performance-Vorteil aus. Das gilt übrigens nicht nur für den SQL Server, sondern gleichermaßen auch für andere Datenbanksysteme (z. B. Oracle).

Bleibt nur noch eine Frage zu klären:
Wie erzeugt man parametrisierte SQL Abfragen mit Hochsprachen?

Am Beispiel eines ADO.NET Fragments sehen Sie, wie einfach das geht:
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "select LastName from Person.Person where BusinessEntityID = @Id";
cmd.Parameters.Add("@Id", SqlDbType.Int);

Wie Sie sehen, ist der Aufwand gar nicht groß. Ich finde sogar eher, dass der Code dadurch übersichtlicher wird. Und die Performance-Vorteile können enorm sein.

Montag, 24. Januar 2011

Berichte in SQL Server Management Studio werden nicht angezeigt

Nachtrag 27.01.2012:
Kai Gerlach ist auf das gleiche Phänomen gestoßen, nachdem er bei einer SQL Server 2008 Instanz das Service Pack 3 installierte. Hier sah die Lösung allerdings so aus, dass er das Microsoft Report Viewer Redistributable 2008 installieren musste.

Heute durfte ich ein hoch interessantes Performance Problem einer größeren SQL Server 2008 Installation untersuchen (und lösen!). Zu Beginn wollte ich mir wie immer zunächst mit den Berichten im SQL Server Management Studio einen Überblick verschaffen. Dabei stellte sich direkt das erste Problem: Anstelle der erwarteten Berichte wurde eine Fehlermeldung angezeigt, dass der Report Viewer nicht installiert sei:

The file 'Microsoft.ReportViewer.WinForms, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' cannot be opened. Do you want to remove the reference from the Recent list?

Wie sich herausstellte, lag das daran, dass es sich um eine 64-Bit Installation von SQL Server 2008 handelte. Dabei wird das Report Viewer Control, eine 32-Bit Anwendung, nicht installiert. Das lässt sich zum Beispiel daran erkennen, dass der folgende Pfad nicht vorhanden ist:
C:\Program Files (x86)\Microsoft Visual Studio 9.0\ReportViewer

Die Lösung ist eigentlich ganz einfach: Download und Installation des Microsoft Report Viewer 2005 Redistributable. Ja, es ist richtig, dass der Report Viewer 2005 für SQL Server 2008 installiert werden muss.
Danach ließen sich alle Standardberichte in SSMS wieder problemlos anzeigen und ich konnte mit der Performance-Analyse beginnen.

Diesen Tipp verdanke ich Volkmar Scheidt - danke Volkmar!