Projekt SQL auf VMware Teil 3 – VMware Guest Konfiguration

Im dritten Teil meiner Projektbeschreibung geht es nun um die Detail Konfiguration der einzelnen virtuellen Maschinen.

Teil 1: Einführung oder das „Warum?“
Teil 2: Konfiguration VMware Umgebung

Diese SQL Server VMware Umgebung ist nun die dritte eigenständige VMware-Umgebung. Alle Umgebungen bestehen jeweils aus drei ESXi Hosts die an ein oder zwei SANs angeschlossen sind.

Bisher haben wir uns in der zweiten oder auch „Anwendungs-VMware“ genannten Umgebung noch nicht so detaillierte Gedanken zu den VMs und deren möglichen Performance Anforderungen gemacht.
Das hat sich aber mit der Einführung dieser SQL Server on VMware Umgebung geändert, da wir nun Hochleistungs-Produktivserver von Physik in Virtuell umwandeln wollten ohne Geschwindgkeitseinbußen.
Grundsätzlich ging es, wie im ersten Teil beschrieben, natürlich um die Ablösung vorhandener Hardware durch neue – in Form von VMware Hosts. Dahinter stand dann aber natürlich bei der Umsetzung auch die Anforderung dass es auf keinen Fall langsamer werden sollte. 🙂

Aus diesem Grund habe ich mich intensiv mit den Anforderungen von SQL Servern auf einer solchen Umgebung beschäftigt und diverse Anleitungen bzw. Best Practices gelesen. Diese gibt es von Microsoft, VMware und noch diversen anderen Quellen (einen Teil davon habe ich unten verlinkt). Besonders hilfreich war dabei eine Artikelserie von David Klee auf der Seite sqlservercentral.com der sich „Stairway to SQL Server Virtualization“ (1) nennt.
Der Großteil meiner nun folgenden Einstellungen / Konfigurationen ist diesem Artikel entnommen da dort, anders als bei den anderen Quellen, direkt beispielhaft und nachvollziehbar die wichtigsten Punkte hervorgehoben sind.

  1. Storage / Festplattenaufteilung
    Hier gilt im Prinzip das gleiche was auch für die physikalische Hardware zählt: Aufteilung der Zugriffe auf möglichst viele Platten bzw. hier SAN Pfade (LUNs). Da es sich hier oftmals immer wieder um das gleiche Ziel auf dem SAN handelt, nur unterschieden durch unterschiedliche RAIDs (6 oder 10), ist als entscheidender Unterschied nur der Puffer jedes einzelnen Pfades (LUN) zu nennen.
    Das bedeutet auch, die richtigen RAIDs müssen über die unterschiedlichen LUNs an die VM durchgereicht werden. Dementsprechend ist die Festplattenkonfiguration und -zuweisung genauso komplex wie im „physikalischen Leben“.
    Das sieht dann zum Beispiel so aus – eng angelehnt an die Empfehlungen von David Klee (1):
    VM Guest Drive Konfiguration
    Wie im dem Artikel erwähnt, ist es auch von besonderer Wichtigkeit die richtigen Festplattenadapter, sprich Controller (vgl. SCSI ID) zu verwenden, also nicht die Standard sondern die Paravirtualisierten Adapter (Paravirtual SCSI Driver – PVSCSI). Für die Partitionen C: (OS) und Y: (Pagefile) in der Tabelle oben kann das bei dem Standard LSI SAS Controller bleiben.
    Der Rest sollte auf den erwähnten PVSCSI eingerichtet werden. Der entsprechende Controller und damit die Laufwerke sind allerdings erst in der VM vorhanden wenn die VMware Tools installiert sind.
    Die Zuordnung der Festplatten zum passenden Controller funktioniert mittlerweile leider nur noch in dem vSphere Webclient.

    Als Festplattentyp sind feste Diskgrößen (Thick-Provision Lazy-Zeroed bzw. Eager-Zeroed) als statische VHDs zu verwenden – Thin-Provision sind zwar am Anfang sparsamer beim Verbrauch des Plattenplatzes, verbrauchen dafür aber zusätzliche Ressourcen beim vergrößern der VMDK Dateien im Hintergrund wenn der Platz gebraucht wird. Das ist ähnlich wie beim Autogrowth einer Datenbank meistens der genau falsche Zeitpunkt und verursacht dann ggf. Probleme in der VM.
  2. CPU
    Die CPU Einstellungen gestalten sich ebenfalls etwas komplizierter.
    Es gilt bei dem ganzen Thema zu beachten, wie viele physikalische CPUs der ESX Host hat auf dem die VMs laufen sollen. Das sollte als Maximum angesehen werden was in den VMs vergeben werden sollte – ein Überbuchen der CPU Ressourcen, d.h. zuweisen von mehr CPU als der Host zur Verfügung hat, ist für SQL Server nicht sinnvoll.
    Zusätzlich könnte man noch im Zusammenhang mit der Größe des Speichers (Punkt 3) das Thema NUMA (Non Uniform Memory Access) betrachten. Das bedeutet, der Zugriff auf den einer CPU direkt zugeordneten Speicher geht schneller als wenn auf „weiter entferneten“ Speicher zugegriffen werden muss. In wiefern das Auswirkungen auf die Leistung der VMs hat, kann ich bisher nur abschätzen, da ich bisher keine Informationen von Dell bekommen habe, die mir die richtige Konfiguration bei unseren verwendeten Servern verrät.

    Die grundsätzlichen CPU Einstellungen verteilen sich einfach auf die virtuellen Sockets (Prozessoren) sowie Cores pro Socket (Kerne je Prozessor).
    Im Screenshot sieht man, dass damit bei dieser VM 12 Kerne zugeordnet sind, die dann auch nach aktuellen Lizenzbedingungen für den SQL Server zu lizenzieren sind (keine Garantie!).

  3. RAM
    Die Zuordnung des notwendigen Arbeitsspeichers für eine VM hängt hauptsächlich von der Größe der Datenbanken ab, die sich aus Performance Gründen möglichst vollständig im Arbeitsspeicher befinden sollten. Zu addieren ist dann noch ein Anteil für OS und SQL Server selber. In dem Screenshot oben sind aktuell 192 GB zugeordnet.
    Wie bei CPU schon angedeutet gibt es beim Thema NUMA noch fehlende technische Details die für die Konfiguration fehlen.
  4. Netzwerk
    Für das Netzwerk sind Adapter des Typs VMXNET3 zu verwenden, die ebenfalls erst nach Installation der VMware Tools in der VM ankommen.Grunsätzlich ist noch über ein Teaming von zwei Netzwerkkarten in der VM nachzudenken –  nicht unbedingt als Redundanz Thema aber wieder mit zwei Puffern als zusätzlicher Geschwindigkeitsvorteil – wenn auch wahrscheinlich minimal. Das gilt es noch zu testen. Dieser Punkt kam erst später durch ein weiteres Dokument von Idera „Moving SQL Server to a virtual Platform“ (6)  hinzu. Aus diesem Grund ist das auch nicht in der ursprünglichen Konfiguration mit enthalten.

Die gesamte Konfiguration bezieht sich auf die Einstellungen einer VM. Natürlich ist die komplette Installation inkl. SQL Server zum Schluss in eine Vorlage gewandert um daraus schnell einen weiteren SQL Server erstellen zu können.

Weitere Einstellungen:

  • Powerplan auf höchste Performance – in der VM als auch bei den ESXi Hosts.
    Die Kontrolle für die VM geht, neben dem Weg über die Energieeinstellungen, auch mit den Powershell dbatools mit dem Kommando Test-DbaPowerPlan (https://dbatools.io/functions/test-dbapowerplan )
    Weiteres zu den dbatools im nächsten Artikel „Migration“.

Quellen:

  1. Stairway to SQL Server Virtualization von David Klee auf sqlservercentral.com
    http://www.sqlservercentral.com/stairway/112551/
  2. Grundlegendes zu NUMA – https://technet.microsoft.com/de-de/library/ms178144(v=sql.105).aspx
  3. VMware Best Practices SQL Server – http://www.vmware.com/content/dam/digitalmarketing/vmware/en/pdf/solutions/sql-server-on-vmware-best-practices-guide.pdf
  4. VMware Performances Best Practices
  5. Moving SQL Server to a virtual Platform von Idera (Registrierung erforderlich)
  6. Brent Ozars Seite zu Virtualization Best Practices: https://www.brentozar.com/sql/virtualization-best-practices/ (die Seite scheint an dieser Stelle nicht mehr zu existieren)

Themen für die noch folgenden Beiträge zum Projekt

  • Migration eines SQL Servers mit dbatools beschreiben, W2K16, SQL 2016 SP1
  • Performance Vergleich physikalisch <-> virtuell

Danke fürs Lesen,
Volker

Projekt SQL auf VMware Teil 2 – Konfiguration VMware

Projekt SQL auf VMware – Teil 2 – Konfiguration VMware

Wir ersetzten sieben Hardware SQL Server durch eine neue VMware Umgebung.

Im Teil 1 ging es um das Warum, die Auswahl und die darauf folgende Bestellung.

In diesem Teil soll es um die Konfiguration der VMware Umgebung gehen, die während der Grundinstallation durch einen Dell Remote Techniker ausgeführt wurde.
Das war eine Premiere da wir ansonsten bisher eine Vor Ort Installation mit gebucht hatten.

Die Technik hinter der Umsetzung:

Die drei Server (Dell PowerEdge R730) enthalten jeweils 2 CPUs mit 12 Cores, das sind insgesamt 72 Cores und jeweils 512 GB RAM. In den Servern stecken zwei micro SD Karten für das Betriebssystem (VMware 6.0 – 6.5 wird vom SAN noch nicht unterstützt). In dem Compellent SC4020 SAN sind 9 SSD mit jeweils 3,82 TB, davon eine als HotSpare konfiguriert. Die Volumes sind als RAID 6 oder 10 verfügbar.
Server und SAN sind über die Dell S4048T Switche mit je 4 x10G angebunden.

Zuerst erfolgte die physikalische Installation der Umgebung im Rechenzentrum. Dabei ergab sich das Problem, dass die Switche ohne OS geliefert wurden. Es war also notwendig für die Switche und dann auch die beiden Controller des SAN serielle Anschlüsse zur Einrichtung zur Verfügung zu stellen. Dafür stellte mein Kollege mir einen 4-fach seriellen auf USB/Netzwerk Adapter auf Basis eines Raspberry zur Verfügung. Auf diesem waren dann gleichzeitig noch FTP, TFTP und diverse andere Server die sich für die Einrichtung als sehr hilfreich erwiesen.
Die Server und  das SAN waren gleichzeitig noch über ihre Management Ports (Dell iDRAC) erreichbar, was ebenfalls für die Grundinstallation notwendig war.

Anhand des prinzipiellen Aufbaus (Bild unten) kann man die Architektur der Umgebung gut erkennen. Oben zwei Server, darunter die beiden Switche woran dann auch das SAN angeschlossen ist. Natürlich wird alles doppelt verkabelt um eine größtmögliche Ausfallsicherheit zu erreichen.

 

URL aus dem Installationsguide von Dell für das Compellent SAN SC 4020
(Quelle und Copyright der Grafik liegen bei Dell.)

Die im Bild dargestellte Umgebung unterscheidet sich nur dadurch von unserer, dass hier nur zwei Server verwendet werden, bei uns aber drei Server als Hosts. Natürlich existieren noch weitere interne Verbindungen zwischen Servern, Switchen und dem SAN um zum Beispiel VMware, VMotion, iSCSI und sonstigen Traffic vom eigentlichen Traffic zum restlichen Netzwerk zu trennen. Dessen Verbindung geschieht dann über zwei Breakout Cable mit 4 x 10G welche direkt an unseren zentralen Core Switchen angeschlossen sind.

Die Grundinstallation erfolgte dann durch den erwähnten Dell Remote Techniker.

  • Server Firmware Updates
  • VMware Grundinstallation auf den SD Karten
  • Switch OS Installation und Konfiguration
  • SAN Einrichtung, Erstellung Volumes
  • iSCSI Einrichtung in der VMware und Verbindung der SAN Volumes

Während der darauffolgenden Tests der verschiedenen Ausfallszenarien (Switch, Server, SAN Controller) stellte sich ein Fehler beim Reboot eines der Switche heraus. Der Support von Dell hat den Switch dann direkt ausgetauscht.

Im nächsten Teil 3 folgt dann die Detail-Konfiguration der VMs.

Danke fürs Lesen,
Volker

Migration SSRS 2008R2 -> 2016 – Renderformat für Excel hat sich geändert

Migration SSRS 2008R2 -> 2016 – Renderformat für Excel hat sich geändert

Bei der Migration unseres SQL Server Reporting Servers von 2008 R2 auf 2016 ergab sich gerade die Schwierigkeit, dass die zu versenden Excel Dateianhänge nicht erstellt werden können.
Die Reports und die Abos bleiben grundsätzlich erhalten wenn die Datenbanken ReportServer und ReportServerTempDB einfach mit umgezogen werden. Die Jobs werden nach Aktivierung des alten Berichtsserver-Schlüssels automatisch wieder erstellt.

Hintergrund der Fehlermeldung zu Excel ist dann das Renderformat. Das stand bei 2008R2 noch auf Excel 2003. Das Format gibt es nun aber nicht mehr.
Das bedeutet es muss in allen Reportabos das Format des Dateianhangs umgestellt werden.

Entweder manuell für jeden Report einzeln oder für alle Reports gleichzeitig per Skript.
Die Basis des Skripts ist für die Änderung von Email Adressen der Empfängern innerhalb der Reports (Quelle s.u.).
Ich habe das für mich angepasst so dass nun das Renderformat geändert werden kann.
Es muss dann anstatt EXCEL einfach EXCELOPENXML heißen:

DECLARE  @OldParameter      VARCHAR(100) = '<Value>EXCEL</Value>'
DECLARE  @NewParameter    VARCHAR(100) ='<Value>EXCELOPENXML</Value>'

BEGIN TRANSACTION
      UPDATE Subscriptions
            SET ExtensionSettings = CONVERT(NTEXT,REPLACE(CONVERT(VARCHAR(MAX),ExtensionSettings),@OldParameter,@NewParameter))
        FROM ReportServer.dbo.Subscriptions
        WHERE CONVERT(VARCHAR(MAX),ExtensionSettings) LIKE '%' + CONVERT(VARCHAR(100),@OldParameter) + '%'
COMMIT TRANSACTION

So hat es bei uns geklappt.

Danke fürs Lesen,
Volker

Quelle: http://www.sqlservercentral.com/blogs/briankmcdonald/2010/07/19/updating-email-addresses-in-ssrs-subscriptions/

Projekt SQL auf VMware Teil 1 – Einführung oder das „Warum“

Projekt SQL auf VMware – Teil 1 – Einführung oder das „Warum“?

Wir ersetzten die sieben Hardware SQL Server durch eine neue VMware Umgebung.

Dieses Projekt möchte ich hier in einigen Schritten beschreiben vom Warum über die Auswahl bis hin zur (hoffentlich) erfolgreichen Umsetzung.
Das Projekt läuft seit November 2016 mit der Auswahl, die Bestellung ist Ende Januar 2017 erfolgt und jetzt ab März erfolgt die Umsetzung. Geplantes Projektende ist Ende Juli 2017. Bis dahin sollen die Server auf die VMware umgezogen sein.

Zum Anfang aber noch einmal zurück zum
Warum:

  • die physikalischen Server laufen nach und nach aus dem Support und müssten ersetzt werden.
  • Reduzierung der Kosten für den Neukauf aller physikalischen Server
  • Reduzierung der SQL Server 2016 Core Lizenzen – weniger Cores zu lizenzieren
  • bessere Hochverfügbarkeit (HA).
  • kürzere Wiederherstellungszeiten im Fall eines Disasters (Disaster Recovery – DR)
    – wir werden das noch genauer betrachten müssen.
  • weniger ungenützte Ressourcen auf den physikalischen Servern

Auswahl:

  • Zur Analyse und zur Auswahl der Hardware haben wir mit einem Dell Tool (DPack ) die Leistungsdaten der physikalischen Server über eine Woche aufgezeichnet.
  • Als ersten Eckpfeiler sahen wir dort ein Maximum von rund 11.000 IOPS welches wir auch entsprechend in der VMware umgesetzt bekommen müssten.
  • IOPS pro Sekunde

    IOPS pro Sekunde

  • Das ist nur ein Peak und deshalb als Maximalwert zu betrachten. Der Mittelwert bei 95% liegt bei 2475 IOPS
  • Hier zu sehen ist die Zusammenfassung für die 7 Server mit den ermittelten Daten:DPack Zusammenfassung der betroffenen Server
  • Das ist ebenfalls aus dem DPack-Report, was zwar hauptsächlich für Dell Server gedacht ist, aber meines Wissens durchaus auch für andere Hardware funktioniert.

Darufhin wurde dann eine Auswahl von 3 Dell Poweredge Servern (R730), zwei Dell 10G-Switchen (S4048T) sowie zwei SANs (Compellent SC4020) als zentralem Storage vorgeschlagen.

Für die VMware Version reicht uns an der Stelle die Essentials Plus Variante, da dieses eine komplett eigenständige Umgebung werden soll. Als Betriebssystem in den VMs dient uns Windows 2016 Datacenter mit der richtigen Core Anzahl.
Die SQL Server sind mit SQL Server 2016 Standard inkl. SA in der Core Variante lizenziert, ein einzelner wird auf Enterprise 2016 angehoben für einen Mobile Reports Reporting Server (SSRS).

Bestellung

Leider wurde für die Bestellung aus finanziellen Gründen eines der beiden SANs gestrichen was den Gesamtpreis um ca. 50K reduzierte. Das fehlende SAN beeinträchtigt das Thema HA in nur geringem Maß, ist allerdings für das Disaster Recovery entscheidend.
Bei einem kompletten Ausfall des SANs sind die unternehmenskritischen Produktiv-Datenbanken nun nicht mehr in kürzester Zeit auf dem zweiten SAN verfügbar sondern müssen aus dem Backup wiederhergestellt werden. Hier ist das Ziel der kürzeren Disaster Recovery Zeiten meines Erachtens wieder in weite Ferne gerückt.

Fortsetzung in Teil 2.

Danke fürs Lesen,
Volker

dbWarden – Monitoring – Änderung für SQL Server > 2012 notwendig

Ich bin immer noch ein Fan von dem SQL Skript „dbWarden“ welches ich nach wie vor zum Monitoring meiner SQL Server verwende.
Ich hatte das ja auch in den PASS Regionalgruppen Rheinland und Ruhrgebiet vorgestellt.
Die Weiterentwicklung ist leider zum Stillstand gekommen, die ursprünglichen Autoren haben schon länger keine Erweiterung mehr vorgestellt.

Seit Version 2012 des SQL Server gibt es Fehlermeldungen in dem täglichen Health Report:

Msg 213, Level 16, State 7, Procedure sp_replmonitorhelppublication, Line 322
Column name or number of supplied values does not match table definition.

Ein User in dem „Supportforum“ von SqlServerCentral für das Projekt hat das Problem erkannt und gelöst.

https://www.sqlservercentral.com/Forums/1441532/dbWarden-A-Free-SQL-Server-Monitoring-Package?PageIndex=18

After some digging I found that the problem is on section
/* Replication Publisher */   of the rpt_HealthReport stored procedure.

More specifically, the TEMP table #PUBINFO is populated with the output of sp_replmonitorhelppublication (on the distribution db) but it lacks the last column named publisher.
I added the missing column with a single line
,publisher NVARCHAR(128) — due to inconsistency with sp_replmonitorhelppublication’s outut right after line 998 on rpt_HealthReport and the Health Report was generated without any issue!
* DON’T FORGET THE COMMA AT THE BEGINNING

Auch wenn die Zeile in der Stored Procedure bei mir etwas anders ist, die Stelle ist zu finden.

Mit dieser Änderung funktioniert das Monitoring auch mit SQL 2012 und 2014 – 2016 steht in kürze an – ich werde dazu weiter berichten.

Danke fürs Lesen,
Volker

PS: Ältere Artikel dazu finden sich auch hier im Blog unter dem Schlagwort dbWarden

Optimierung Reporting Server MS SQL 2008 R2 Teil 2

Nach der Optimierung des Reporting Servers aus Teil 1 http://blog.volkerbachmann.de/2015/01/31/optimierung-reporting-server/ war es nur wenige Monate ruhig um den Reporting Server.
Die Anzahl der Reports steigt nahezu täglich und die Reports können nicht weit genug auseinander gezogen werden damit diese sich nicht in die Quere kommen.

Deshalb haben wir die Analyse mit der Unterstützung von Dell – als Lieferant des SQL Servers – noch einmal durchgeführt.

Ausgangslage: Reports brauchen bis zu 2 Stunden bis sie versendet werden

Analyse durch Dell mit DPack Tool
Flaschenhals:
1. Festplatten IOPS
2. RAM

Lösungsmöglichkeiten
1. Mehr IOPS zur Verfügung stellen
a. Vorhandene 6 Festplatten durch SSD ersetzen
b. Zwei SSD zusätzlich im RAID 1 und Verlagerung der meist frequentierten DBs auf diese SSD
c. Eine oder zwei SSD als CacheCade (erweiterter Cache des RAID Controllers) einrichten
2. Mehr Arbeitsspeicher zur Verfügung stellen
a. Zusätzlicher Speicher inkl. Enterprise Lizenz und ggf. Neuinstallation des Servers.

Das waren die Lösungsansätze bezüglich Hardware.

Weitere Möglichkeit:
3. Optimierung der Abfragen mit Unterstützung eines SQL Server Spezialisten

Kosten für die Umbauten:Kosten Optimierung Reporting Server 2008 R2

Durchgeführte Maßnahmen

1. Arbeitsspeicher +32GB / Windows Server Enterprise Edition
2. Eine SSD im RAID 0 für ReportServerTempDB
3. Optimierungen der Reports
durch Snapshot Technologie

Der Performance Monitor zeigt die Antwortzeiten für die TempDB Dateien sowie die 100% Aktivität der entsprechenden Festplatten im RAID 10.

Vor dem Umbau:

Performance Monitor vorher

Nach dem Umbau:

Performance Monitor nachher

Deutlich zu sehen ist dass die Antwortzeiten für die TempDB-Dateien (nun auf der SSD) auf „0“ runter gegangen und auch die Aktivität auf der SSD (Partition S:) ist auf 3% runter gegangen ist.

Als abschließender Vergleich nun noch die Laufzeiten ausgewählter Reports: Laufzeiten der Reports im Vergleich

Ergebnis: Seit Einbau der SSD sind die Laufzeiten der Reports deutlich runter gegangen. Auch die Snapshot Technologie hat für einige Reports deutliche Verbesserungen gebracht.

Der Einbau des Arbeitsspeichers alleine hat nicht dafür gesorgt dass die Reports schneller ausgeliefert werden konnten, hat aber dafür gesorgt, dass der Server im Ganzen nicht mehr so eng an den Grenzen des vorhandenen Arbeitsspeichers arbeitet, sondern noch ein wenig Luft hat.

Bisher ist das Ergebnis soweit ausreichend, dass keine weiteren Maßnahmen mehr notwendig sind.

Weitere Möglichkeiten wären:

  1. Umsetzung der Snapshot Techniken für weitere Reports
    2. Verlagerung weiterer (temporärer) Datenbanken auf die SSD
    1. ReportServer TempDB
    2. ReportCodes DB
    3. Sonstiges
    1. Optimierung von Abfragen, Indizes
    2. Ggf. In-Memory / Columnstore Index

Danke fürs Lesen.
Über Kommentare würde ich mich sehr freuen.

Viele Grüße
Volker

Studying for Exam 70-461 (MCSA: SQL Server 2012)

Just beginning to collect information and material for Studying the Exam 70-461

Reminder: there will be no Exam for MCSA SQL Server 2014 just for 2012. Only the Exams for the MCSE SQL Server are upgraded to 2014!

First of all a List of Links that helped me decide how to learn – hopefully, I’m in the process right now.

Tipp from Kendra in the above Video

  • Create your own Test Lab on a Laptop/Remote Session (Azure?) to be accessible from everywhere.
    => Windows 10 Laptop with Hyper-V? Lenovo X300 doesn’t work with Win 10 and Hyper-V!

How to learn?

Microsoft is testing to do online certification tests  – „Online proctored“ – maybe that’s a choice?

Do you have additional ressources? Feel free to contact me..

Regards,
Volker

Base Monitoring

TSQLTuesdayT-SQL Tuesday #66: Base Monitoring

Cathrine Wilhemsen (b|t) is hosting the T-SQL Tuesday blog party of this month.

I will describe what’s my understanding of Base Monitoring for SQL Server.

Last Thursday 5/7/2015 I presented these thoughts at a PASS regional Chapter in Cologne/Germany.

„My“ Base Monitoring consists of monitoring all Events that come up from too few hard disk space, memory or CPU. It is also about monitoring of locking, blocking or deadlocks, long running queries/jobs and failure in logins.
I will also do additional tasks that didn’t belong to the monitoring itself but help in failure check and reorganization of indexes and so on (…)

All this was arranged and written down after an installation of a new SQL Server.
These are the steps I did:

1. create a DBA Database for several scripts and tables (see below)
2. install the main script from dbWarden for Monitoring
3. install Integrity check and Maintenance scripts from Ola Halengren into DBA Database
4. create GetErrorLogEvent Stored Procedures and Job for Mails out of the SQL Errorlog into DBA Database
5. create Jobs for recycling ErrorLogs
6. Performance Monitoring, Event Logs with an external Tool called Host Monitor

The dbWarden Script (2.) is a great ressource for real time monitoring blocking, deadlocks, long running querys, jobs and so on – best of all: it is free of charge (!). It also brings a detailed and configurable health report of the server that will be sent every morning.

The scripts from Ola Halengren (3) are well known in the SQL Server Community for bringing us perfect maintenance tasks for index and statistic updates with rebuild online/offline or reorganize where it is neccessary.
Additionally there is a script for integrity check – and backup of course. 🙂

The GetErrorLogEvent Script (4.) was originally written by Jonathan Kehayias and published on an MSDN Archive site. I didn’t found the site any more on MSDN so the script is attached together with the presentation (Sorry, only german these days) below.

For recycling the Error Log (5.) it is only neccessary to create a job with the two commands (exec dbo.sp_cycle_errorlog and exec dbo.sp_cycle_agent_errorlog).

The only tool that is not free of charge is the last one, called Host Monitor.
It is perfect for more monitoring especially for long term monitoring.

Ressources

dbWarden
Description: http://www.sqlservercentral.com/articles/Monitoring/98106/
Download, Wiki: http://sourceforge.net/projects/dbwarden/

Ola Halengren
https://ola.hallengren.com/

Hostmonitor (€)
http://www.ks-soft.net

GetErrorLogEvents and Basis Monitoring Presentation from the PASS Chapter in Cologne (German)
BaseMonitoring_20150507

Thanks for reading,
Volker

Optimierung Reporting Server MS SQL 2008 R2

Meldung der Report-Abteilung: täglich zwischen 9 und 9:20 Uhr ist der Reporting Server völlig ausgelastet, d.h. die Reaktionszeiten beim Erstellen und Bearbeiten von Reports sind inakzeptabel.

Meine Recherchen nach den Ursachen:

1. Ausgangssituation

  • Aus Performancegründen wurde vor 1 ½ Jahren ein neuer Reporting Server angeschafft.
  • Danach lief der Restore der Datenbanken (Befüllung des Reporting Server über Backup&Restore aus den Produktiv Datenbanken) über Nacht und ebenso sämtliche Abfragen deutlich schneller.
  • Ein Zwang zu weiterer Optimierung der Abfragen usw. war damit nicht mehr notwendig

2. Analyse

  • Datenbank und Tabellen sind für OLTP ausgelegt, d.h. Eingabe von Daten, Update usw.
  • Auf dem Reporting Server handelt es sich um OLAP, d.h. Analyse von Daten inkl. Aggregierung
    => andere Art der Abfrage => andere Art der Indizierung notwendig
  • Es laufen Reports gleichzeitig die auf die gleichen Daten zugreifen – mit der fehlenden Indizierung laufen diese Reports dann so lange, dass sie sich gegenseitig blockieren.
    => Ergebnis: ~ 400.000 Wartende Tasks, mäßige Prozessorauslastung, viele SperrenAktivitätsmonitor

3. Lösungsmöglichkeiten

  1. Anzahl der Reports verkleinern.
  2. Reports besser verteilen damit diese sich zeitlich nicht so sehr in die Quere kommen.
  3. Views (general_view usw.) materialisieren, d.h. in Tabellen umwandeln und darauf dann Indizes anwenden.
    Erklärung: Für die Abfrage über alle Datenbanken wurde ein View entworfen, der einen Union All über alle Datenbanken macht. Die Idee ist, das Ergebnis aus diesem View, der mittlerweile in fast allen Reports (~300 Stück) verwendet wird, in eine Tabelle zu speichern, damit bei jeder Abfrage nur noch die Tabelle verwendet wird und nicht mehr sämtliche Datenbanken. Passende Indizes gehören dann dazu.
  4. Vorhandene Indizes durch OLAP Indizes ersetzen.
  5. zusätzliche SQL Instanz vom Server verlagern -> zusätzlicher SQL Server 2008R2 notwendig.
  6. Replikation zur Verringerung der Wiederherstellungszeiten und für Intraday Reports.
    Erklärung: durch Backup und Restore als Aktualisierung des Reporting Servers ist der Server während der entsprechenden Restore-Zeit nicht für Reports verfügbar. Da wir an dieser Stelle aber abhängig vom Backup auf den Produktiv Servern sind, kann der Restore nicht früher laufen.
    Lösungsansatz: Backup und Restore durch Replikation ersetzen.
  7. größerer Server mit mehr Speicher – Investition ca. 20.000€

4. Umsetzung

Mittlerweile sind die Punkte 1-3 realisiert. Schon alleine durch konsequentes auseinanderziehen der Reports konnte die Verfügbarkeit des Servers zu den angegebenen Zeiten eindeutig verbessert werden.
Zuletzt wurde dann noch Punkt 3 umgesetzt. Vermutlich sollten noch weitere Indizes hinzugefügt werden um die Geschwindigkeit der Abfragen deutlich zu erhöhen, aber das ist so schon um längen besser als vorher.

Die weitern Punkte werden vermutlich nicht umgesetzt werden, da die ersten 3 Punkte schon eine deutliche Verbesserung gebracht haben. Zusätzlich ist das Thema DBA nicht mein einziges Thema und ich kann mich nicht so darauf konzentrieren, wie ich gerne würde.

Danke fürs Lesen,

Volker