SQL Server Setup – Empfehlungen für SharePoint

Die Standardeinstellungen eines SQL-Servers sind alles andere als Optimal für die SharePoint Umgebung. In diesem Artikel habe ich versucht das für den SharePoint optimale SQL Server Setup und die Einstellungen des SQL-Servers zusammenzutragen.

Das SQL Server Setup sollte nur als Orientierungshilfe (primär als Notizen für mich selbst) dienen, denn natürlich ist jede SharePoint-Umgebung sehr individuell und erfordert auch angepasstes SQL-Server-Setup.

SQL Server Setup – Empfehlungen für SharePoint – Optimale Einstellungen – Bessere Performance

SQL-Server Hardware Empfehlungen

RAM und CPU

RAM: 16 bis 64+ GB

CPUs: 4 bis 8+

RAID-Level: RAID 10

Windows Server Konfiguration

Festplatten Konfiguration

Festplattenanzahl

5 bis 7+ Festplatten auf unterschiedlichen LUNs verteilen (für TempDB, TempDB Logs, SP DB, SP DB Logs, OS, SQL Install., Suche)

nach Geschwindigkeitspriorität:

  1. TempDB (Data + Log). Könnte man evtl. auch noch aufteilen.
  2. Content DB (Data)
  3. Search DB (Data + Log)
  4. Conent DB (Log)
  5. OS + Swap-File (meistens lokale Festplatte)

Falls mehr Inhalte geschrieben als gelesen werden, dann die Positionen 2 und 4 austauschen.

NTFS-Clustergröße

Auf 64K einstellen (64 Kilobytes * 1024=65536 bytes)

Performance-Gewinn bis zu 30%

Zuordnungseinheitengröße kann z.B. entweder mit

chkdsk <Laufwerk>: anzeigen lassen

Die dritte Zeile von unten zeigt die Clustergröße.

NTFS-Clustergröße 65536 Bytes - 64K - 64 Kilobytes - Bytes in jeder Zuordnungseinheit - Korrekte Einstellung für SQL-Server.png
64K ist die richtige Clustergröße für SQL-Server
Standard-NTFS-Clustergröße 4096 Bytes - 4K - Bytes in jeder Zuordnungseinheit
4K ist die falsche Clustergröße für SQL-Server

Oder mit fsutil anzeigen

fsutil fsinfo ntfsinfo c:

fsutil fsinfo ntfsinfo - Clustergroesse - Zuordnungseinheit Groesse - Bytes per Cluster - 4096 - 4K
Clustergröße = Bytes pro Cluster = 4096 = 4K = Schlecht für SQL-Server.

(Standard-NTFS-Clustergröße ist 4096 Bytes bzw. 4K und ist für den SQL-Server nicht gut geeignet)

Um die NTFS-Clustergröße (Allocation Unit Size) zu ändern, muss die Festplatte leider formatiert werden.

Entweder per GUI formatieren (und auf 64 Kiobytes einstellen) – dabei gehen die Daten verloren

SQL Server Empfehlungen für SharePoint - Festplatte formatieren - NTFS-Clustergröße 64K wählen - 64 Kilobytes * 1024 = 65536 bytes - SQL-Server Performance-Gewinn

oder per Kommandozeile formatieren.

Achtung:
Beim nächsten Schritt wird die Festplatte ohne Nachfrage formatiert und alle Daten darauf gehen verloren!

CMD mit erhöhten Rechen (als Admin) starten und folgendes eingeben (Werte anpassen).

format D: /Q /FS:NTFS /A:64K /V:Data /Y

  • D: für den zu formatierenden Laufwerksbuchstaben steht.
  • /Q mach Schnellformat (Quick Format)
  • /FS:NTFS wählt NTFS als Dateisystem (File System)
  • /A:64K steht für Clustergröße 64K (Allocation Unit Size)
  • /V:Data benennt die Festplatte als „Data“ (Volume Label)
  • /Y Bestätigt die Frage
Alignment prüfen

Das „Alignment“ muss geprüft und korrekt sein, da ansonsten pro Zugriff mehr Cluster gelesen/geschrieben werden als nötig. Das schadet der Performance.

Um herauszufinden, ob das „Alignment“ richtig eingestellt ist, muss „StartingOffset“ der Partition durch die Clustergröße geteilt werden. Ist das Ergebnis eine runde Zahl, dann ist die Einstellung richtig. Ist das Ergebnis eine Kommazahl, dann ist die Alignment-Einstellung falsch.

Die Formel ist: Offset/Clustergröße = Falls gerade Zahl, dann richtig, falls Kommazahl dann falsch.

Beispiel

Offeset herausfinden per PowerShell
Get-Disk | Get-Partition

PowerShell - Get-Disk - Get-Partition - PartitionNumber - DriveLetter - Offset - Size - Type

oder per CMD
wmic partition get BlockSize, StartingOffset, Name, Index

CMD - wmic partition get BlockSize - StartingOffset Name - Index

Unsere Partition hat ein Offset von 525336576

Unsere Clustergröße (herausfinden wie oben beschrieben) beträgt 4096

525336576/4096=128256 Gerade Zahl, also ist Alignment richtig.

Für eine SQL-Server Festplatte, die im Idealfall mit 64K (64*1024=65536 Byte) formatiert ist, müsste man folgende Rechnung machen.

525336576/65536=8016 Auch hier keine Kommazahl, deshalb das Alignment richtig eingestellt.

Anderenfalls muss die Partition neu erstellt und formatiert werden (z.B. mit dem folgenden Kommandos).

diskpart
list disk
select disk
create partition primary align=1024
exit

 

Windows-Server – Visuelle Effekte ausschalten

System-Fenster öffnen indem

[WIN]+[Pause] drücken oder

Rechte Maustaste auf „Computer“ im Windows-Explorer > Eigenschaften

Windows-Explorer - rechte Maustaste - Computer Eigenschaften - Kontextmenü - Windows-Server 2012

Erweiterte Systemeinstellungen > Reiter „Erweitert“ > Leistung Einstellungen > Reiter „Visuelle EffekteFür optimale Leistung anpassen > OK

System - Erweiterte Systemeinstellungen - Leistung Einstellungen - Für optimale Leistung anpassen - Visuelle Effekte deaktivieren - Windows Server 2012

Windows-Server – Auslagerungsdatei Größe

Standardmäßig wird die Auslagerungsdatei (virtueller Speicher) von Windows selbst verwaltet. Die Einstellung ist nicht optimal, da sich die Auslagerungsdatei durch die dynamische Größenanpassung mit der Zeit fragmentiert. Aus diesem Grund sollte man die Größe fest auf 1,5fache oder 2fache des eingebauten RAM einstellen.

System-Fenster öffnen indem

[WIN]+[Pause] drücken oder

Rechte Maustaste auf „Computer“ im Windows-Explorer > Eigenschaften

Windows-Explorer - rechte Maustaste - Computer Eigenschaften - Kontextmenü - Windows-Server 2012

Im Fenster „System“ nachschauen, wie viel Arbeitspeicher eingebaut ist. In unserem Fall: Installierter Arbeitsspeicher (RAM): 6,00 GB

Erweiterte Systemeinstellungen > Reiter „Erweitert“ > Leistung Einstellungen > Reiter „Erweitert“ > Ändern

6,00 GB x 1024 = 6144 MB

6144 MB x 1,5 = 9216 MB für die Auslagerungsdatei

System - Erweiterte Systemeinstellungen - Reiter Erweitert - Leistung Einstellungen - Reiter Erweitert - Ändern - Auslagerungsdatei - Virtueller Arbeitsspeicher - RAMx1,5

Das PageFile kann auch per Skript eingestellt werden (nur für OS auf Englisch)

REM Setzt PageFile auf das 1,5 fache bzw. auf das doppelte Groesse des RAM.
REM Funktioniert nur für OS auf Englisch (wegen find string und der Tausender-Trennzeichen in RAM-Ausgabe)


REM -------------------------------------------------------------------------------------------------------
REM GET THE PHYSICAL MEM SIZE AS DIGITS (EN OS)
REM --------------------------------------------------------------------------------------------------------
FOR /F "eol=; tokens=1,2,3,4,5 delims=:, " %%i IN ('"systeminfo |find /i "Total Phy""') DO @Set /a m=%%l%%m

Echo RAM = %m%

REM -------------------------------------------------------------------------------------------------------
REM SET INITIAL MEMORY SIZE 200%of PHYSICAL MEMORY
REM --------------------------------------------------------------------------------------------------------
REM set /a x=%m%*3/2 REM 150%
set /a x=%m%*2


REM -------------------------------------------------------------------------------------------------------
REM SET MAXIMUM MEMORY 200% OF PHYSICAL MEMORY
REM --------------------------------------------------------------------------------------------------------
set /a y=%m%*2


REM -------------------------------------------------------------------------------------------------------
REM CHANGE PAGE FILE TO MANUAL
REM --------------------------------------------------------------------------------------------------------
wmic computersystem where name="%computername%" set AutomaticManagedPagefile=False


REM -------------------------------------------------------------------------------------------------------
REM SET PAGEFILE SIZE
REM --------------------------------------------------------------------------------------------------------
wmic pagefileset where name="C:\\pagefile.sys" set InitialSize=%x%,MaximumSize=%y%


pause

 

Auch sollte man die Auslagerungsdatei (PageFile) auf eine dedizierte Festplatte verlegen.

Danach muss der Computer neu gestartet werden.

Windows-Server – Energieoptionen – „Höchstleistung“ aktivieren

Start > „Systemsteuerung“ suchen > Einige Einstellungen sind momentan nicht verfügbar

Systemsteuerung - Energieoptionen - Power Options - Einige Einstellungen sind momentan nicht verfügbar - Change settings that are currently unavailable - Windows Server 2012

Höchstleistung“ aktivieren.

Systemsteuerung - Energieoptionen - Power Options - Höchstleistung - High performance - Windows Server 2012

Lock Pages in Memory (gpedit.msc)

Der SQL-Server Dienstkonto sollte das Recht „Sperren von Seiten im Speicher“ bzw. „Lock pages in memory“ haben.

Ausführen“ bzw. „Run“ Fenster z.B. durch folgende Tastenkombination öffnen.

[WIN] + [r]

Windows - Ausführen - Run

gpedit.msc eintippen und mit OK oder [Enter] bestätigen.

Es öffnet sich „Editor für die lokale Gruppenrichtlinien„.

Computerkonfiguration > Windows-Einstellungen > Sicherheitseinstellungen > Lokale Richtlinien > Zuweisen von Benutzerrechten > Sperren von Seiten im Speicher

bzw.

Computer Configuration > Windows Settings > Security Settings > Local Policies > User Rights Assignment > Lock pages in memory.

Benutzer oder Gruppe hinzufügen…
(„Add User or Group“)

gpedit.msc - Editor für die lokale Gruppenrichtlinien - Sperren von Seiten im Speicher - Lock pages in memory - Benutzer oder Gruppe hinzufügen

Das Dienstkonto des SQL-Server eintragen. Sprich den Account, unter dem Sqlservr.exe aufgerufen wird.

Das Dienstkonto kann unter „Dienste“ (Run > services.msc) nachgeschaut werden.

Dienste SnapIn - .services.msc - Sqlservr.exe - SQL Server Dienst - Anmelden als

Danach den SQL-Server-Dienst neustarten.

Performance Volume Maintenance Tasks (gpedit.msc)

in System in deutscher Sprache heißt diese Berechtigung „Durchführen von Volumewartungsaufgaben„.

Diese Berechtigung ermöglicht dem SQL-Server schnellere Operationen auf Dateiebene (z.B. erstellen neuer DB-Files, Restore, Autovergrößerung der Dateien etc.) indem der Speicherplatz nicht komplett „ausgenullt“ werden muss, sondern sehr schnell im Dateisystem reserviert wird. Diese Funktion nennt sich „Instant File Initialization“ (IFI)

Hinweis:

Ab SQL-Server-Version 2016 kann diese Berechtigung im Zuge der Installation eingeräumt werden.

Um die Berechtigung einzurichten müssen nachfolgende Schritte durchgeführt werden.

Ausführen“ bzw. „Run“ Fenster z.B. durch folgende Tastenkombination öffnen.

[WIN] + [r]

Windows - Ausführen - Run

gpedit.msc eintippen und mit OK oder [Enter] bestätigen.

Es öffnet sich „Editor für die lokale Gruppenrichtlinien„.

Computerkonfiguration > Windows-Einstellungen > Sicherheitseinstellungen > Lokale Richtlinien > Zuweisen von Benutzerrechten > Durchführen von Volumewartungsaufgaben

bzw.

Computer Configuration > Windows Settings > Security Settings > Local Policies > User Rights Assignment > Perform volume maintenance tasks.

Zeile doppelklicken und kontrollieren, ob das Dienstkonto des SQL-Servers bzw. der SQL-Instanz eingetragen ist.

 

gpedit.msc - Editor für die lokale Gruppenrichtlinien - Durchführen von Volumewartungsaufgaben - Perform volume maintenance tasks - Benutzer oder Gruppe hinzufügen

Falls das Dienstkonto noch nicht eingetragen ist dann auf „Benutzer oder Gruppe hinzufügen…“ („Add User or Group“) klicken.

Das Dienstkonto des SQL-Server eintragen. Sprich den Account, unter dem Sqlservr.exe aufgerufen wird.

Das Dienstkonto kann unter „Dienste“ (Run > services.msc) nachgeschaut werden.

Dienste SnapIn - .services.msc - Sqlservr.exe - SQL Server Dienst - Anmelden als

Danach den SQL-Server-Dienst neustarten.

Ordner im Antivirenprogramm ausschließen

SQL Server Ordner von Antivirus ausschließen

Im Antivirensoftware müssen mindestens folgende SQL-Server-Ordner vom Scan ausgeschlossen werden.

  • Ordner „DATA
  • Ordner mit den LDF-Dateien (transaction log)
  • Ordner „Backup

Oder den ganzen SQL-Server Instanzordner:
<Laufwerk>:\MSSQLxx.<Instanz-Name>

SharePoint Ordner von Antivirus ausschließen
  • <Laufwerk>:\Program Files\Common Files\Microsoft Shared\Web Server Extensions
  • <Laufwerk>:\Windows\Microsoft.NET\Framework64\v4.0.30319 (Für SP2010 …\v2.0.50727\…)
    • \Temporary ASP.NET Files
    • \Config
  • <Laufwerk>:\Users (Alle Accounts aller SP-Dienste und App-Pools)
    • \Default\AppData\Local\Temp
    • \<SpServiceXAccount>\AppData\Local\Temp
    • \<SearchServiceAccount>\AppData\Local\Temp (vom Suchdienst wird hier Ordner „Gthrsvc_spsearch4“ für permanente Schreibzugriffe benutzt.)
  • <BLOB Cache Ordner> – Ordner der für „Binary Large Object“ Cache konfiguriert ist.
  • <Laufwerk>:\Program Files\Microsoft Office Servers (falls man den ganzen Ordner nicht ausschließen will, dann s.u.)
    • \xx.x\Data (Ordner für SearchService Index)
    • \xx.x\Logs
    • \xx.x\Bin
    • \xx.x\Synchronization Service
  • <Laufwerk>:\ProgramData\Microsoft\SharePoint
  • <Laufwerk>:\WINDOWS\System32\LogFiles
  • <Laufwerk>:\WINDOWS\Syswow64\LogFiles
  • <Laufwerk>:\inetpub\wwwroot\wss\VirtualDirectories
  • <Laufwerk>:\inetpub\temp\IIS Temporary Compressed Files

SQL-Server Einstellungen (Empfehlung)

Allgemeine SQL-Server Empfehlungen für SharePoint

Vom SharePoint wird nur eine „Sortierung“ (Collation) untersttützt: Latin1_General_CI_AS_KS_WS

Dedizierten SQL-Server bzw. dedizierte SQL-Instanz für den SharePoint benutzen. Falls die Suchdienstanwendungsdatenbank zu groß werden sollte, dann auch für die Suche eine extra SQL-Instanz benutzen.

Server memory

An dieser Stelle könnte man bei Bedarf den minimalen und maximalen Arbeitsspeicherverbrauch für die SQL-Instanz festlegen.

SSMS > Server- bzw. Instanzeigenschaften > Arbeitsspeicher

SQL Servereigenschaften - Arbeitsspeicheroptionen für den Server - Minimaler Maximaler Serverarbeitsspeicher in MB

Hierbei sollte man mindestens 3GB für das Betriebssystem übrig lassen.

Default Index fill factor

SQL Servereigenschaften > Datenbankeinstellungen > Standardfüllfaktor für Indizes (Default Index fill factor): 80

SQL Server Empfehlungen für SharePoint - SQL Servereigenschaften - Datenbankeinstellungen - Standardfüllfaktor für Indizes - Server Properties - Database Settings - Default Index fill factor - 80 - SharePoint.png

Compress backup

An dieser Stelle (Datenbankeinstellungen) könnte man auch die Option „Sicherung komprimieren“ bzw. „Compress backup“ aktivieren.

Max Degree of Parallelism

SQL Servereigenschaften >Erweitert > Parallelität

Max . Grad an Parallelität: 1
(Max Degree of Parallelism: 1)

SQL-Server Datenbanken Empfehlungen

Max. DB-Größe

Die maximale Größe der Inhaltsdatenbanken sollte lt. Empfehlung von Microsoft nicht mehr als 200 GB pro Datenbank betragen. Wird diese maximal empfohlene Größe erreicht, dann müssen neue Inhaltsdatenbanken erstellt und die vorhandenen Websitesammlungen evtl. verschoben werden.

Die maximale Anzahl der Websitesammlungen sollte 5000 pro Inhaltsdatenbank nicht übersteigen.

Mit folgenden PowerShell Zeilen können die Inhaltsdatenbanken inklusive DB-Größe und Anzahl der Sites aufgelistet werden.

# SP-Module laden
Write-Verbose "SharePoint-Module werden geladen..."
Add-PSSnapin Microsoft.SharePoint.PowerShell

# Inhaltsdatenbanken inkl. Sitecollections und DB-Größen auflisten
Get-SPContentDatabase | ft Name, CurrentSiteCount, Status, @{label="DiskSizeRequired in MB";Expression={[math]::round($_.DiskSizeRequired/1MB, 2)}}, WebApplication, Id -auto

SQL-Aliase benutzen

Von Anfang an sollten SQL-Aliase benutzt werden, damit spätere Datenbankumzüge einfacher vonstatten gehen.

Wie man SQL-Aliase einrichtet findet man z.B. hier.

„model“ Einstellungen Empfehlung

Die model-DB ist ja eine Art Vorlage für die Erstellung neuer Datenbanken.

SQL Server - model DB - Datenbankeigenschaften - model - Dateien - Anfanggröße - Automatische Vergrößerung - Maximale Größe - Standard

model > Eigenschaften > Dateien > Anfangsgröße
(Database Properties > Files > „Initial Size„)
so einstellen, dass diese dem erwartetem Jahreswachstum der Content-DB entspricht. Z.B. Daten auf 500MB und Log auf 125MB (25%)

model > Eigenschaften > Dateien > Automatische Vergrößerung
(Database Properties > Files > „Auto growth„)
In MB (und nicht in Prozent) einstellen und zwar möglichst die Hälfte von Initial Size. Falls also Initial Size 500 MB ist, dann soll Autogrowth auf 250 MB, Unlimited eingestellt werden.

Bei großen Datenbanken kann die automatische Vergrößerung auch gerne auf 5 GB eingestellt werden.

SQL Server Empfehlungen für SharePoint - Initial Size - Autogrowth - model DB - Datenbankeigenschaften - model - Dateien - Anfanggröße - Automatische Vergrößerung - Maximale Größe - Jahreswachstum - Optimal

model > Eigenschaften > Optionen > Automatisch …

  • Automatisch schließen (Auto Close): False
  • Statistiken automatisch erstellen (Auto Create Statistics): False
  • Automatisch verkleinern (Auto Shrink): False
  • Statistiken automatisch aktualisieren (Auto Update Statistics): True
  • Statistiken automatisch asynchron aktualisieren (Auto Update Statistics Asynchronously): False
SQL Server - model - DB - Datenbank Eigenschaften - Optionen - Automatisch - Statistiken - Statistics - Auto Shrink - Automatisch verkleinern - DE
Datenbankeigenschaften – model (DE)
SQL Server - model - DB - Datenbank Eigenschaften - Options - Automatisch - Statistiken - Statistics - Auto Shrink - Automatisch verkleinern - EN
Database Properties – model (EN)

 

Hinweis:
SharePoint 2013 (und 2010) wird beim Erstellen der Inhalts- bzw. der Dienstanwendungsdatenbanken zwar die Einstellungen für „Initial Size“ aber nicht für „Autogrowth“ von der model db übernehmen.
Dies müsste man dann z.B. monatlich per Hand, falls man zwischendurch neue DBs erstellt hat, oder jedes Mal direkt nach dem Erstellen der DB einstellen.

 

„tempdb“ Einstellungen Empfehlung

tempdb > Eigenschaften > Dateien > Anfangsgröße
(Database Properties > Files > „Initial Size„)
Die Größe auf 25% der größten DB einstellen.

tempdb > Eigenschaften > Dateien > Automatische Vergrößerung
(Database Properties > Files > „Auto growth„)
Die Autovergrößerungsgröße kann 10% bis 50% der „Anfangsgröße“ betragen.
In MB (nicht in Prozent) einstellen.

Datenbankdateien aufteilen

Damit die Daten von der CPU parallel verarbeitet und bei Bedarf auf unterschiedliche Festplatten verteilt werden können, kann die Datenbank in mehrere Datenbankdateien aufgeteilt werden (nicht verwechseln mit der Aufteilung der Inhalte auf mehrere Inhaltsdatenbanken).

Die Anzahl der Dateien sollte dabei der Anzahl der Prozessorkerne entsprechen und mindestens 4 und maximal 8 Dateien betragen.

Die Dateigröße sollte bei allen Datendateien dieselbe sein.

In SSMS rechte Maustaste auf die entsprechende Datenbank.

DB > Rechte Maustaste > Eigenschaften > Dateien > Hinzufügen

Felder „Logischer Name“ und „Dateiname“ ausfüllen.

Links

  1. absolute-sharepoint.com: Free Whitepaper: Maximizing SQL 2012 Performance for SharePoint 2013 Whitepaper
  2. sharepointszu.com: SQL Script für die Erstellung von Best Practice SharePoint Datenbanken
  3. techtask.com: SQL Server Best Practices for SharePoint Server 2016
  4. techtask.com: SQL database maintenance plan for SharePoint
  5. support.microsoft.com: How to reduce paging of buffer pool memory in the 64-bit version of SQL Server
  6. docs.microsoft.com/de-de: Datenbankdatei-Initialisierung
  7. support.microsoft.com: Certain folders may have to be excluded from antivirus scanning when you use file-level antivirus software in SharePoint
  8. sharepoint-wiese.de: SQL Server Empfehlungen für SharePoint

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert