Anmelden
Ich möchte für die nächsten 30 Tagen angemeldet bleiben
Deutsch
Several pages in the usergroup are available in English. Click on english to visit these pages.

Blogs

10.06.2015
SQL Server für DNN installieren - Best Practices (Michael Tobisch)

Ich habe ja schon viele Blogbeiträge über die Installation von DNN gelesen. Meist wird dort davon ausgegangen, dass IIS und SQL-Server bereits installiert sind, am ehesten wird noch auf die Einrichtung von IIS eingegangen. Beim SQL-Server hört und liest man zumeist nur, dass man eine Datenbank anlegen und einen Benutzer (in den letzten IIS-Versionen die Identität des Application Pools) entsprechend berechtigen muss.

Ich möchte hier einmal auf die Installation des SQL-Servers eingehen, auch weil ich in den letzten Jahren immer wieder feststellen musste, dass viele von euch zwar DNN ganz gut verwalten können, tolle Module programmieren und wertvolle Arbeit als Integratoren leisten - den SQL Server im Hintergrund aber etwas stiefmütterlich behandeln. Das liegt natürlich auch an der Politik von Microsoft, das Ding lässt sich einfach installieren und läuft - was soll man da schon noch großartig tun?

Diese Beschreibung ist aber kein "Wie installiere ich SQL Server richtig?"-Tutorial, sondern wendet nur einige Best Practices an, die für die Nutzung von SQL-Server als Datenbank für DNN Sinn machen - ich beschreibe das also nur unter diesem Aspekt.
Ich habe alle diese Maßnahmen mit SQL Server 2014 getestet. Für frühere Versionen weiß ich nicht, inwieweit diese gültig sind (wahrscheinlich schon) - bin aber auch zu "faul", um das alles nachzukontrollieren.

Festplatten

Installiert man SQL-Server mit der Weiter-Weiter-Fertigstellen-Taktik, dann werden alle Daten-Dateien in einem Ordner der C:-Platte (C:\Program Files\Microsoft SQL Server\MSSQLxx.SQLEXPRESS\MSSQL\DATA) abgelegt - und zwar sowohl die Datenbankdateien (*.mdf) als auch die Transaktionslogs (*.ldf). Soferne man - wie bei den meisten kleineren Websites - das einfache Wiederherstellungsmodell verwendet und einmal am Tag eine Datenbanksicherung durchführt können diese beiden Dateien auch ruhig im gleichen Verzeichnis liegen - aber Daten unter C:\Program Files\...?

Sinnvoller ist es, für die diese Dateien eine eigene Festplatte (keine Partition!) zur Verfügung zu stellen, wenn man das vollständige Wiederherstellungsmodell verwendet auch zwei oder drei - jedenfalls eine für die Datenbanken und eine für die Transaktionslogs. Eine weitere Festplatte sollte zum Sichern der Datenbanken vorgesehen werden, falls diese Sicherungen nicht durch irgendeine Software direkt auf Band geschrieben werden. Folgende Organisation der Felstplatten (und noch einmal: keine Partitionen, sondern richtige Festplatten!) wäre also möglich:
Festplatten formatieren

Bevor wir SQL Server installieren sollten wir uns noch ein Internum vor Augen halten: SQL-Server legt Daten in 64k großen Blöcken (sogenannten "Seiten" oder "Pages") ab. Diese Seiten werden immer als gesamtes eingelesen, und stellen quasi die kleinste Einheit im Dateisystem eines SQL-Servers dar. Sinnvoll ist es daher, auch die Festplatten (zumindest die Festplatten für die Datenbanken und Log-Dateien!) mit eben dieser Blockgröße zu formatieren, das erhöht die Performanz schon einmal ganz kräftig.

 

Sinnvollerweise sollte auf diesen Festplatten aber sonst nichts landen. Jede auch noch so kleine Datei würde einen ganzen Block (also 64kB) auf der Festplatte benötigen, das wollen wir wieder nicht, da es doch eher unter "Verschwendung" fällt.

Formatieren wir die Festplatte(n) also mit folgenden Einstellungen, bevor wir die Installation von SQL Server beginnen:
Festplatten formatieren
(Natürlich kann man diese Dinge auch im Nachhinein erledigen, sie erfordern allerdings etwas Aufwand!)

.Net Framework

Hier kann man nicht viel optimieren, die verscheidenen Frameworks sind allerdings Voraussetzungen für die diversen SQL-Server und müssen zum Teil manuell nachinstalliert werden. So benötigt etwa SQL Server 2014 das .Net-Framework 3.5, welches unter Windows Server 2012 R2 nicht automatisch mitintalliert wird.

Windows Updates

Bevor man SQL Server installiert sollte man auch sämtliche Windows-Updates einspielen.

SQL Server Service Packs und kumulative Updates

Ebenfalls sollte man sich das neueste Servicepack für den jeweiligen SQL Server und (falls neuer als das Servicepack) das letzte kumulative Update herunterladen. Diese Dinge werden unmittelbar nach der Installation installiert. Eine gute Übersicht samt Download-Links findet man hier.

SQL Server Installation

Nun wird's spannend. Hier sind einige wesentlich Punkte, die beachtet werden sollten. Auf alle Punkte gehe ich nicht ein, viele davon sind für wirklich große Datenbanken wichtig, und wer diese administriert sollte schon etwas fortgeschrittener sein. Ich möchte hier nur "Grundnotwendigkeiten" behandeln.

Deutsch?

Wenn man wirklich einen deutschen Windows-Server installiert hat, so kann man ruhig auch einen deutschen SQL-Server installieren. Ich persönlich bevorzuge zwar immer noch ein englisches Betriebssystem am Server (und auch eine englische SQL-Server-Version), ich glaube aber, dass dies wirklich nur mehr eine Frage des Geschmacks ist. Sollte man aber unter Windows in diesem Fall nicht "Deutsch - Deutschland (de-DE)" als Sprache eingestellt haben, dann läuft man in Probleme - siehe hier.

Serverkonfiguration

Hier sollte man das Dienstkonto, unter dem SQL Server (und andere Dienste) gestartet wird, ändern. Am besten, man legt dafür einen (Windows-)Benutzer an (in einer Domäne ein Domänenkonto, sonst ein lokales Konto). Dieses Konto muss nur in der Gruppe der Benutzer sein und benötigt keine administrativen Rechte. Das Recht zum Starten von Diensten wird automatisch zugewiesen.
Dienstkonto für SQL Server
Wichtig: Benutzer muss Kennwort bei der nächsten Anmeldung ändern muss deaktiviert sein, die Optionen Benutzer kann Kennwort nicht ändern und Kennwort läuft nie ab sollten aktiviert sein.

Dieser Benutzer wird dann als Kontoname eingetragen:
Dienstkonto für SQL Server

Was an dieser Stelle oft wichtig ist, ist die Registerkarte Sortierung - hier kann die Standard-Sortierreihenfolge für die Datenbanken angegeben werden. Für unsere DNN-Zwecke ist Latin1_General_CI_AS (CI=Case insensitive, also Groß-/Kleinschreibung ignorieren und AS=Accent sensitive, also Akzente beachten - damit wird z.B. ein e von einem è oder ein U von einem Ü unterschieden). Auf jeden Fall überprüfen, ob diese Sortierung gewählt ist (aber normalerweise ist sie das ja).

Datenbankmodulkofiguration

Hier mache ich meistens folgendes: Unter SQL-Server-Administratoren ist mein eigenes Konto (ich als Person, die SQL Server installiert) angegeben. Diesen Eintrag lösche ich und ersetze ihn durch die Gruppe der lokalen Administratoren:
Administratoren

Datenverzeichnisse

Im Register Datenverzeichnisse werden hier die Laufwerke und Ordner angegeben, in die SQL Server die Datenbanken ablegt. Ich gehe jetzt einmal von einer Maximalaufteilung aus, da sehen die Laufwerke wie folgt aus:

  • C: - System und Programme
  • D: - Systemdatenbanken
  • E: - Benutzerdatenbanken
  • F: - Transaktionslogs
  • G: - Backup

Die Laufwerke D:, E: und F: sind dabei mit 64k Blockgröße formatiert, der Rest ist default. Ich gebe noch einen Ordnernamen ("MSSQL") an, damit die Dateien nicht im Stammverzeichnis landen. So ist auch dafür gesorgt, dass die Zugriffsberechtigungen richtig vergeben werden.
Datenverzeichnisse

Der Rest der Installation kann dann mit Weiter-Weiter-Fertigstellen-Taktik erledigt werden.

Service Packs und kumulative Updates

Nach dem Abschluss der Installation sollten nun Servicepacks und kumulative Updates eingespielt werden - siehe oben.

Weitere Einstellungen

Max Memory

Damit wird eingestellt, wie viel Arbeitsspeicher der SQL Server für seinen Buffer-Pool maximal verwenden darf. Normalerweise schnappt sich dieser alles, was zur Verfügung steht, und das kann auf Kosten anderer Prozesse sowie der Performance des Betriebssystems gehen. Natürlich ist es sinnvoll, dass der SQL-Server genügend Speicher bekommt, um zu cachen was das Zeug hält, aber alles ist sicher zu viel.
Einen Richtwert für diese Einstellung findet man auf dieser Seite. Allerdings muss man dazusagen: Das gilt für dedizierte SQL-Server, wenn also z.B. auch IIS darauf läuft (und bei vielen Webservern ist das ja üblich) kann man ruhig auch noch etwas weniger nehmen. Diese Einstellung kann im SQL Server Management Studio (SSMS) unter den Eigenschaften des Servers festgelegt werden.
Max Memory
Danach muss der SQL-Serverdienst neu gestartet werden.

Gruppenrichtlinien

Es gibt zwei Einstellungen der Gruppenrichtlinien, die auf keinem SQL-Server fehlen sollten: Lock Pages in memory (Sperren von Seiten im Speicher) und Perform Volume Maintenance Tasks (Durchführen von Volumewartungsarbeiten). Zum Bearbeiten dieser Einstellungen startet man den Gruppenrichtlinieneditor (einfach ein DOS-Fenster öffnen, in den Ordner C:\Windows\System32 wechseln und "mmc gpedit.msc" eingeben und starten). Dann navigiert man im linken Bereich zu

  • Local Group Policy (Richtlinien für lokale Computer)
    • Computer Configuration (Computerkonfiguration)
      • Windows Settings (Windows-Einstellungen)
        • Security Settings (Sicherheits-Einstellungen)
          • Local Policies (Lokale Richtlinien)
            • User Rights Assignments (Zuweisen von Benutzerrechten)

Das Benutzerkonto, unter welchem der SQL-Server läuft sollte bei beiden Einstellungen hinzugefügt werden:
Gruppenrichtlinien

Konfiguration der Temp-DB

Die Konfiguration der TempDB (im SSMS unter Datenbanken :: Systemdatenbanken) kann durchaus für die Performance relevant sein. Best Practice ist hier: Je Prozessorkern eine Datendatei. Vorgabe ist eine Datendatei, mit der Schaltfläche Hinzufügen legt man weitere an. Als Anfangsgröße legt man 1 GB fest, das Wachstum stellt man fix mit 500 MB ein. Achtung beim Anlegen auf die Pfade (in der vorletzten Spalte) - diese sollten alle im gleichen Ordner liegen.
Auch bei der Transaktionslogdatei sollte die Anfangsgöße auf 1 GB und das Wachstum fix auf 500 MB eingestellt werden.
TempDB

Trace-Flags 1117 und 1118 setzen

Diese Einstellungen sind nicht ganz unproblematisch, weil sie von Microsoft nicht in den SQL Server Books online dokumentiert sind. Nachdem sie allerdings im Knowledge-Base-Artikel 2154845 erwähnt sind, sind sie doch klar definert. Es ist eine bewährte Praktik, diese zu setzen, allerdings gewissermaßen auf eigene Gefahr!

Trace Flag 1117 bewirkt, dass alle Dateien einer Dateigruppe vergrößert werden, wenn eine Datei dieser Dateigruppe vergrößert wird. Nachdem wir zumindest bei der TempDB mehrere Datendateien zugeordnet haben (ich gehe schon davon aus, dass wir mehr als einen Prozessorkern haben!) hat diese Einstellung zumindest hier Auswirkungen.

Trace Flag 1118 bewirkt, dass SQL Server keine gemischten Blöcke (mixed extents), sondern nur vollständige Blöcke (full extents) verwenden soll. Das bedeuted, dass jedes neu allozierte Objekt in jeder Datenbank der Instanz seine eigenen 64k Daten erhält. Genaue Informationen zu Seiten und Blöcken findet man hier.

Diese Einstellungen werden im SQL Server Konfigurations-Manager gesetzt. Daz klickt man mit der rechten Maustaste auf das Service, wählt Eigenschaften und dann das Register Startparameter. man fügt -t1117 und -t1118 hinzu.
Trace Flags
Danach ist der SQL-Server neu zu starten.

Datenbank anlegen

Beim Anlegen einer Datenbank sind ein paar Kleinigkeiten zu bedenken, die vom SQL-Standard abweichen - wahrscheinlich aus historischen Gründen, aber wer weiß.
Die folgenden Größenangaben sind Schätzungen und treffen wahrscheinlich für Websites von KMUs, Vereinen, etc. zu, soferne hier nicht Shoplösungen, große Benutzermengen, reger Datenverkehr etc. zu erwarten sind.

Eine DNN-Datenbank benötigt einmal ca. 50 MB nachdem DNN installiert wurde, und je nach benötigten weiteren Modulen und Daten kommt dann noch einiges dazu. Eine Anfangsgröße von 100MB einzustellen ist daher wahrscheinlich nicht so falsch, wenn mehr erwartet wird auch 200 oder 300 MB.
Auch die Vergrößerung wird mit dem Defaultwert 1 MB etwas zu klein dimensioniert sein, abhängig von den erwarteten Datenmengen dürfen es gerne 20 oder 50 MB sein.

Das Transaktionslog sollte auch etwas größer dimensioniert werden als die voreingestellten 1 MB - hier sind 20 MB sicher angebracht, bei regem Datenverkehr auch mehr.
Wichtig: Das Wachstum des Transaktionslogs sollte hier auch von prozentuell auf einen fixen Wert eingestellt werden, sonst können diese Dateien wenn sie größer werden massiv anwachsen. Ein Wert von 10 MB oder 20 MB ist hier wahrscheinlich sinnvoll.

Überprüft werden sollte auch noch der Pfad zur Datendatei und dem Transaktionslog, diese sollten auf den Festplatten zu liegen kommen, die wir im Setup eingestellt haben.
Datenbank anlegen

Optionen

Was beim Anlegen einer Datenbank beachtet werden sollte ist das Wiederherstellungsmodell, welches aus der Sicherungsstrategie abgeleitet werden sollte.

  • Einfach bedeutet, dass man in regelmäßigen Abständen (z.B. einmal täglich) eine Gesamtsicherung der Datenbank durchführt. Im Falle eines Verlusts kann man die Datenbank mit dem Stand der letzten Sicherung wederherstellen.
  • Vollständig bedeuted, dass man in regelmäßigen Abständen (z.B. einmal täglich) eine Gesamtsicherung und dazwischen in kürzeren Abständen (z.B. alle 15 Minuten) eine Sicherung des Transaktionsprotokolls durchführt. Im Falle eines Verlustes kann man mit der Gesamtsicherung und den vorhandenen Transaktionslog-Sicherungen die Datenbank zu jedem beliebigen Stand (Uhrzeit) zwischen Gesamtsicherung und letzter Transaktionslogsicherung wiederherstellen.

Für kleinere Websites reicht sicher das einfache Wiederherstellungsmodell, die Frage die man sich stellen muss ist aber immer die gleiche: In welchem Ausmaß ist ein Datenverlust akzeptabel?
Datenbank anlegen

Zusammenfassung

Man sieht, dass man schon mit einfachen Schritten einiges mehr aus SQL Server herausholen kann. Ich hoffe, dass dies dem einen oder anderen hilft und überlege mir, einmal (m)eine Backup-Strategie für DNN zu beschreiben - bitte um Kommentare, ob das gewollt ist oder nicht...

Happy DNNing!
Michael

Kommentare: 3

Andreas Decke meint

Vielen Dank für Deinen Beitrag. Konnte die eine oder andere Info gut gebrauchen.
# 23.06.2015 09:12

Michael Tobisch meint

Ergänzung: Die Einstellung AUTO_CLOSE sollte für jede Datenbank auf OFF gesetzt werden. Siehe http://sqlmag.com/blog/worst-practice-allowing-autoclose-sql-server-databases
# 05.10.2015 10:32

Michael Tobisch meint

Update zum SQL Server 2016: 1) Die beiden angesprochenen Trace-Flags sind nicht mehr notwendig, da sie nun im Standard integriert sind - siehe https://blogs.msdn.microsoft.com/psssql/2016/03/15/sql-2016-it-just-runs-faster-t1117-and-t1118-changes-for-tempdb-and-user-databases/ und 2) Die Berechtigung "Grant Perform Volume Maintenance Task" kann nun während der Installation angehakt werden - leider nicht die Berechtigung "Lock Pages in Memory", und leider mit einem schweren Bug: Es wird immer dem vom Installationsprogramm vorgeschlagenen User erteilt, auch wenn man diesen ändert, so wie wir das hier getan haben.
# 21.11.2016 16:16