Azure BlockBlob Kosten reduzieren -> auf ins Archiv

Hallo zusammen,

heute gibt es (ja, wieder seit langer Zeit) einen kleinen Post von mir. Dieses mal aber nicht zum Thema SQL Server. Stattdessen mache ich einen kleinen Ausflug in die Azure Welt.

Wie ja einigen bekannt ist, bin ich zwischendurch gerne mal mit der Kamera unterwegs und halte auch Veranstaltungen wie eine SQLKonferenz oder einen SQLSaturday ein wenig in Bildern fest.

Von meinem Workflow her schaut es nachgelagert so aus, dass die Bilder erst einmal auf meinem Notebook landen und ich diese dann ein klein wenig mit dem Programm Photoshop Lightroom von Adobe bearbeite.  Dabei ist es so, dass die ursprünglichen Bilddateien im Rohdatenformat (engl.: RAW format) erhalten bleiben, da Lightoom non-destruktiv arbeitet. Die Änderungen, die ich an den Bildern vornehme, werden in der Datenbank (dies ist übrigens eine SQLite DB) von Lightroom gespeichert. Bei jedem Export werden dann meine Änderungen angewandt. Dies ergibt am Ende die Bilder, die Ihr hier und da bestimmt schon mal gesehen habt.

So, wie komm ich jetzt aber auf das Thema Azure und BlockBlobs? Nun, die gerade erwähnten RAW Dateien liegen zuerst auf meinem Rechner auf einer flotten SSD:

BilderRAWDateienLokal

Wenn ich mit der Bearbeitung fertig bin, dann verschiebe ich über Adobe Lightroom (jedwede Datenorganisation sollte man grundsätzlich in Lightroom erledigen) die Bilder auf meine kleine Network-Attached-Storage (NAS). Hier nutze ich ein kleines 2 Platten-System von Synology. Die beiden Platten bilden ein einfaches RAID-1, sind also gespiegelt.

Es sollte ja jedem klar sein, dass ein RAID kein Backup ist (…und ein Backup nur so gut wie ein Restore…, aber das ist NICHT Thema dieses Posts). Daher bleiben die RAW Dateien nicht nur einfach auf der NAS liegen.

SynologyBilderAblage

Zwischendurch packe ich immer mal eine USB Platte an die NAS und stoße dann eine Sicherung an. Die Platte kommt danach an anderer Stelle daheim in eine Schublade. Aber so ein richtiges „Off-Side“ Backup ist das ja auch noch nicht. Daher werden die RAWs über einen Backup Job auch noch in Richtung Azure geschickt.

Dies ist bei Synology recht einfach über das Management Frontend der NAS zu bewerkstelligen.

syno module.png

Hier gibt es einen Bereich Datensicherung & Replikation, wo man recht fix seinen Backup Job konfigurieren kann und netterweise auch in Richtung Cloud.

cloudtarget

Das Einzige, was man im Anschluss braucht, ist der Speicherkonto-Name und der Access Key.

azuretarget

Und genau die Infos findet man natürlich in seinem Azure-Portal.

Privat ist es an der Stelle bei mir überschaulich. Ich hab eine kleine Subscription mit einer nutzungsbasierten Bezahlung, unter der ich nur ein Speicherkonto betreibe.

Also kurz ins Portal gehüpft und auf das entsprechende Speicherkonto, wo später mal die RAW Dateien hin sollen. Hier finde ich einen Bereich mit den sog. Access-Keys:

access keys

Wenn man nun dem Link folgt, dann kommt man an die benötigten Infos heran für den oben gezeigten Wizard.

Bei mir schaut es so aus, dass ich ein Speicherkonto habe mit mehreren Containern. Diese sind einfach nur nach Jahren unterteilt und hier landen am Ende die ganzen RAW Dateien.

raw container

Und jetzt wird das Ganze so langsam spannend.

Ich hab mich damals (das Speicherkonto hab ich gefühlt schon ewig) für den Access Tier Standard / Cool entschieden. Der ist ausgelegt für unregelmäßige Zugriffe und Speicherung von Daten für mindestens 30 Tage. Von 2006 bis 2019 haben sich inzwischen ja eine ganze Menge an Dateien angesammelt, sodass ich auf etwas mehr als 400GB komme. Der Einfachheit halber bleibe ich einfach mal bei den 400GB.

Wenn man sich im Azure Preisrechner die Kosten anguckt, dann zahle ich pro Monat für den „kalten“ Storage um die 3,40€.  Hierbei belasse ich es jetzt auch einfach mal. Es kommt natürlich noch was hinzu für Schreibvorgänge, LIST Befehle usw., aber der Großteil sind tatsächlich die Kosten meiner Blobs.

KostenStorage

Interessant wird es, wenn ich im Preisrechner jetzt mal auf die Zugriffsebene Archiv wechsel:

archivkosten

33 Eurocent! Na das ist doch mal ein gewaltiger Unterschied.

Da ich in die Sicherungen 2006 – 2018 nichts mehr rein schreibe, könnte ich diese Daten ja auf ein Archiv-Tier setzen (nur in Hot und Cold kann direkt reingeschrieben werden).

Aber wie geht das konkret?

Über das Portal bewegt man sich in Richtung seiner Container (siehe oben) und muss einmal bis zur Blob-Ebene runter.  Hier kann ich dann den access tier anpassen:

changeTier

changeTier2

Jetzt ist es natürlich ein klein wenig umständlich, dass man per Portal jeden Blob einzeln angehen muss, um auf Archiv umstellen zu können. In den höreren Ebenen, wo man eine Ordner-Ansicht hat, geht das nämlich gar nicht. Und auch über den Azure Storage Explorer hat man keine Chance. Also bleibt einem am Ende nur eines: PowerShell!

Aber damit wird das Anpassen des Access Tier tatsächlich zum Kinderspiel. Hier ist die entsprechende Befehlsreihenfolge, mit der ich die PASSCamp Bilder von 2016 in den Archiv-Level gebracht habe:

Am Anfang kommt einfach nur die Anmeldung, um den Zugriff  auf mein Azure Konto zu erlangen. Im Anschluss fülle ich in die ersten 2 Variablen meine Infos bzgl. des Storage Accounts (Name und Key).

Mit diesen Infox kann ich dann den StorageContext „ziehen“ und im Verlauf weiter arbeiten.  In Zeile 8 des Scriptes hole ich mir einfach noch mal die Container heran, die auf meinem Storage Account existieren:

vscode output

Dann eine neue Variable, wo ich jetzt einfach nur den Namen eines Containers hinterlege. In Zeile 14 hole ich mir dann alle Blobs heran. Zeile 15 hab ich im Skript auskommentiert, da dies auch nur noch mal als Kontrolle für mich gedient hat. Aber in dem Objekt $blob hab ich nun folgende Infos drin:

blob Griedview out

Anstatt jetzt, wie bereits erwähnt, im Portal jeden Blob anklicken zu müssen, lasse ich nur den kleinen Befehl in Zeile 20 laufen:

$blob.icloudblob.setstandardblobtier(„Archive“)
Damit werden nun alle Blobs, die ich in meinem Objekt $blob erfasst habe, auf den Access-Tier Archiv gebracht.
Dieser Vorgang benötigte für knapp 90 Dateien dann auch nur 3 Sekunden:

runtime

Am Ende dann nochmal kurz überprüft:

blobalsarchiv.png

Über den gleichen Weg kann ich übrigens dann auch Dateien wieder hochstufen. Hier gilt es dann nur zu bedenken, dass Kosten verursacht werden. Von der ArchivStufe in Richtung Cold/Hot werden die Leseoperationen in Rechnung gestellt.

Ein Hochstufen würde mich dann etwas mehr als 9 Euro kosten:

archivLesevorgaenge kosten

 

Also bleibt an der Stelle nur zu hoffen, dass ich nie in die Lage komme, dass ich mein Offside Backup aus Azure holen muss.

Der wichtige Punkt dieses Beitrages ist aber: ohne den Einsatz von ein paar Zeilen PowerShellCode schafft man es nicht in adäquater Zeit seine Blobs ins Archiv runter zu stufen und in Hinblick auf andere Einsatz-Szenarien, wo solche Vorgänge wohlmöglich automatisiert werden sollen, kommt man sowieso heutzutage nicht daran vorbei.

Vielen Dank für’s Lesen.

 

Veröffentlicht unter Azure, Freizeit, PowerShell, SQL Community | Verschlagwortet mit , , | Kommentar hinterlassen

Jetzt hat er auch noch gesprochen…..oder: wie kam ich zum Datagrillen 2019?

 

Hallo zusammen,

ja, ich mache meinen Ruf als unregelmäßiger Blog-Post Verfasser alle Ehre. Ist der letzte Beitrag doch schon wieder Ewigkeiten her (und ich diesen Beitrag schon so gut wie fertig hatte, dann aber irgendwie bei den Entwürfen liegen geblieben ist…).

Doch jetzt hab ich mal wieder “Futter” für einen kleinen Beitrag. Ich hab es nämlich tatsächlich getan, ich hab das erste mal auf einem Event gesprochen!

Am 20.6. war es so weit: ich durfte einen Vortrag beim DataGrillen 2019 in Lingen halten.

Wer das Event (immer) noch nicht kennt: William Durkin (b|t) hat dieses kleine und feine Event vor einigen Jahren (ich meine es war 2015) als “SQLGrillen: Datenbanken, Bratwurst, Bier” ins Leben gerufen. Ok, damals war es noch das 1jährige Bestehen der Regionalgruppe Emsland, aber die Idee, eine Veranstaltung rund um die Data Platform Themen zu halten und den Abend mit Grillen und lecker Bierchen abzurunden, reifte und wurde fortgeführt. Und in 2018 waren es dann schon 180 Besucher! Was für eine Entwicklung.

Und irgendwie bin ich als Sprecher da auch hineingeraten. Doch wie ist es dazu gekommen?

Ich bin schon seit langer, langer Zeit von diversen Leuten aus der Community gefragt worden, wann ich denn endlich mal einen Vortrag halte. Beim SQLGrillen 2017 wurde es schon etwas konkreter, als diverse Sprecher meinten: „2018 bist Du mit an Bord“. Aber 2018 kollidierte die Veranstaltung mit dem Geburtstag meiner Tochter und dem Sommerurlaub. Von daher konnte ich um den “Eisberg” nochmal herumschippern.

Jedoch gab es 2 Punkte 2018, die mich dann quasi “rein geritten” haben:

1. Das Nerdycorn! Wer das Nerdycorn nicht kennt: es ist eines der Firmen-Maskotttchen von Ben Weissmanns (b|t) Firma Solisyon.

SQLSaturday856_MainCon_DiHo_008

Vom Nerdycorn hatte ich früher ein paar Sticker mit nach Hause gebracht, die meine Tochter (2 Jahre zu dem Zeitpunkt) total klasse fand. Auf dem SQLSaturday München hatte Ben dann auch NerdyCorn T-Shirts mit dabei. Ich bekam eines für meine Tochter. Ben scherzte (oder war es sein voller Ernst?) dabei: „Im Gegenzug reichst Du eine Session für das Datagrillen ein“, worauf ich ein „Ja gut, schauen wir mal“ zurückgab . Daheim angekommen, mit dem Gedanken im Hinterkopf, dass jetzt wohl eine Session einreichen muss, übergab ich das T-Shirt meiner kleinen Tochter. Sie quittierte die Übergabe übrigens mit einem: “Mag ich nicht!”

2. Das PASSCamp 2018. An ersten Camp-Abend saßen Frank Geisler (b|t), Ben Weissmann und Tillmann Eitelberg (b|t) im Bibliothekszimmer vom Lufthansa Conference Center zusammen, um eine Folge Ihres tollen Podcasts Please Talk Data To Me aufzunehmen. Ich saß ebenfalls in der Bibliothek und machte ein paar Bilder von der Aufzeichnung.

PASSCamp2018_041

Und dann meinte der Ben auf einmal, dass wir ja auch über meinen geplanten Vortrag beim DataGrillen sprechen können.  Mitgehangen, mitgefangen….  (Man kann sich das Ganze auch hier https://www.pleasetalkdatatome.de/2018/12/06/episode-007-es-weihnachtet-sehr/ noch mal anhören.)  Und so ist es dann gekommen, dass ich tatsächlich eine Session eingereicht habe. Meine Idee hierbei war, dass ich quasi aus dem “Nähkästchen” über Dinge plaudere, die ich rund um den SQL Server schon erlebt oder gehört habe, die man aber in der Regel eben nicht so macht oder halt nicht stimmen.

Komischerweise wurde die auch direkt genommen. (Ich weiß bis heute nicht, welche dunklen Mächte hier am Werk gewesen sind…)

Keine Woche später kam dann auch die Bestätigung, dass meine Session als eine der Newcomer Sessions ausgesucht wurde und von nun an tickte die Uhr. Immerhin hatte ich nur ein halbes Jahr, alles hin zu bekommen. Man muss ehrlich sein, William und Ben sind bei der Auswahl wirklich fix, weswegen jeder Sprecher genug Vorlaufzeit hat, um wirklich alles unter einen Hut zu bekommen. Zudem wurde mir Frank als Mentor zugewiesen, um mich bei meinen Vorbereitungen zu unterstützen.

Tatsächlich hatte ich kurz vor Weihnachten dann schon die ersten Ideen grob zusammengefasst und Frank die ein oder andere Email zu dem Thema geschickt, bzw. eine recht große Auswahl an kleineren Themen, von denen ich dann einige zu einem Vortrag zusammen packen wollte. Dann war aber erst einmal Weihnachtszeit, Urlaub usw., weswegen ich das Thema Präsentationsvorbereitung bewusst hab etwas ruhen lassen.

Auf der anderen Seite hatte ich mir als Deadline Ende April, Anfang Mai gesetzt, da ich Anfang Juni (direkt vor dem DataGrillen) im Urlaub gewesen bin und ich bis dahin den Vortrag stehen haben wollte. Mit Frank hatte ich mich dann in dem Zeitraum bis Juni insgesamt 4 Mal getroffen, 2 Mal online per Skype und 2 Mal war ich in seiner Firma.

Die 1.Besprechung Mitte März war das “Eindampfen” der diversen Ideen, hatte ich Frank Ende Dezember ja insgesamt 9 verschiedene Punkte “um die Ohren” gehauen. Danach hab ich mich schon an die Vorbereitung der ersten Folien gemacht.. Darüber hinaus hab ich mir eine Laborumgebung aufgebaut unter Hyper-V auf meinem Windows 10 Rechner. Hierfür hab ich im Übrigen auf das PowerShell Projekt AutomatedLab zurückgegriffen, sodass ich mich mit den “Basics” der Setups gar nicht großartig auseinandersetzen musste.

Bei der 2. Besprechung Ende März hatte ich von den insgesamt 5 Themen, auf die ich am Ende mit Frank gekommen bin, tatsächlich 4 schon fertig, schon die ersten Folien (noch ohne jegliches Design) und die ersten Demos zeigen können. Mitte April hab ich dann bei Frank das erste Mal einen kompletten Durchlauf meiner Präsentation gemacht und direkt Korrekturen an den Formulierungen auf den Slides vorgenommen. Anfang Mai war dann die Generalprobe mit einem kompletten Durchlauf und nur noch das optische Aufwerten der Folien. Als ich diese Generalprobe dann hinter mir hatte, konnte ich erst einmal ruhigen Gewissens in Urlaub fahren. Ich hab auch bewusst das Notebook nicht mitgenommen, um vielleicht zwischendurch doch noch mal Etwas anzupacken.

Ich muss jedoch gestehen, dass ich während meines Urlaubs gedanklich die Themen dann doch zwischendurch mal überdacht habe und muss ehrlich gestehen: ich war doch schon extrem nervös, trotz einer, in meinen Augen, recht guten Vorbereitung.

Mitte Juni bin ich dann quasi direkt aus dem Urlaub heraus nach Lingen gefahren und an dem Mittwoch gefühlt 1000 Tode gestorben, meine Nervosität war bestimmt schon nicht mehr gesund. Ich bekam jedoch von so vielen Sprechern Zuspruch, was mir enorm geholfen hat.

Und am 20.6. um 13:15 war es dann so weit, ich als Newcomer Speaker auf einem großen Event bzw. überhaupt mein erster Auftritt als Sprecher.

image4

Ich denke, alles in Allem kann ich mit dem Vortrag schon zufrieden sein, gerade als absoluter Newcomer. Ich werde mir aber auch die konstruktive Kritik zu Herzen nehmen und den Vortrag nochmal etwas überarbeiten, da gerade das Thema “Mein 1. Backup Szenario” , so wie ich es damals kennen gelernt hab, schon sehr speziell gewesen ist. Hier muss ich die Zuhörer etwas mehr abholen.  Die Slides finden sich im Übrigen auf GitHub unter https://github.com/DiHo78/2019/tree/master/Sessions/Dirk%20Hondong.

 

Mein besonderer Dank geht an der Stelle noch mal an Frank, der mir dabei geholfen hat,  die Präsentation vorzubereiten. Ansonsten hätte das Alles bestimmt nicht so gut geklappt.

Darüber hinaus muss ich mich aber auch bei Gabi Münster (t) bedanken. Sie hat es immer wieder geschafft, sogar noch 5 Minuten vor Sessionbeginn, mich zu beruhigen und mir gut zuzureden, sodass ich meine “Feuertaufe” gemeistert hab.

Veröffentlicht unter Freizeit, SQL Community | Kommentar hinterlassen

Vollsicherung, Logsicherung,Vollsicherung,Logsicherung… – oder: ist die Backupsoftware verwirrt?

Für die tägliche Datenbanksicherung der von mir anvertrauten Systeme wird eine Drittherstellersoftware eingesetzt.

Darüber kann oder mag man sich streiten, ob man jetzt native Datenbanksicherungen selbst einrichtet, Wartungspläne verwendet oder die Lösung von Ola Hallengren verwendet. Es gibt ja bekanntlich viele Wege nach Rom und Hauptsache ist, dass es überhaupt Backups gibt und sich diese auch problemlos wiederherstellen lassen. (Der Klassiker schlechthin: ja, wir haben Backups….)

Auf einem Test-System, welches ebenfalls durch die Software abgedeckt ist, hatte ich jetzt ein interessantes Verhalten:

Anstatt des zu erwartenden Sicherungsablaufes

Vollsicherung, Logsicherung, Logsicherung, Logsicherung, Logsicherung  ..usw  hatte ich für eine Datenbank folgenden Ablauf:

Vollsicherung, Logsicherung,Vollsicherung,Logsicherung und so weiter.

Die Software erzwang bei jeder 2. Log Sicherung eine Vollsicherung der Datenbank, da ein Mechanismus namens „Log Gap Detection“ gegriffen hat.  Hiermit soll sichergestellt werden, dass keine Logsicherung fehlt. Dies kann mitunter ja passieren, wenn nativ eine Logsicherung gezogen wird oder vielleicht sogar unterschiedliche Sicherungsprodukte auf ein System losgelassen werden (ich hoffe nicht…)

Was hier konkret passiert: die Sicherungssoftware speichert eigenen Metadaten über die erfolgten  Datenbankensicherungen, darunter auch Log Sequence Number (LSN) Informationen. Oder wie es in der Übersetzung heisst: Protokollfolgenummern.

Ich hab mich dann folgender Abfrage bedient, um mal zu gucken, ob die LSNs wirklich nicht zusammen passen:

DECLARE @db_name VARCHAR(100)
SELECT @db_name = 'MeineDB'
SELECT
BS.recovery_model AS [Recovery Model]
,(CAST(BS.backup_size / 1000000 AS INT)) AS [Size of Backup (MB)]
,CASE BS.[type] WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS [Type of Backup]
,BS.backup_start_date AS [Backup Date]
,BS.first_lsn AS [First LSN]
,BS.last_lsn AS [Last LSN]
,bs.database_backup_lsn
FROM msdb.dbo.backupset BS
WHERE BS.database_name = @db_name
ORDER BY backup_start_date DESC;

Ergebnis:

LSN_Query1

Wenn man sich jetzt mal die Transaction Log Sicherungen genau anschaut, dann erkennt man, dass die „Last LSN“ der ersten Log Sicherung  und die „First LSN“ der nächsten übereinstimmen (125000000177500001). Da ist keine Lücke.

Was macht also die Sicherungssoftware?

Wenn die Sicherungssoftware eine Logsicherung macht, dann werden die Metadaten der Software nachgelagert wie folgt aktualisiert (diese Info konnte ich dem Support aus der Nase kitzeln):

SELECT TOP 1 last_lsn AS last_log_backup_lsn
FROM msdb..backupset WHERE database_name=N'%s'
AND TYPE LIKE 'L'
ORDER BY last_lsn DESC;

(Ggf. fällt dem einen oder anderen hier schon etwas auf… )

Beim nächsten Log Backup prüft die Backup Software die LSN, welche sie in den eigenen Metadaten gespeichert hat, wie folgt:
SELECT last_log_backup_lsn FROM sys.database_recovery_status
WHERE database_id = DB_ID(N'%s')

In dieser DMV ist immer die letzte Transaktionslogsicherungs – LSN hinterlegt bzw., um es direkt richtig zu formulieren: Hier findet sich die Startsequenznummer der kommenden Transaktionslogsicherung.

Und jetzt die spannende Sache: was ist denn, wenn ich ggf. meine Sicherungshistorie nicht so ganz sauber pflege um alte Einträge zu bereinigen? Oder aber ich hab so etwas doch implementiert, halte aber die letzten 30,60, 90 Tage an Informationen vor? Oder es gab eine Wiederherstellung und meine Datenbank hat jetzt den Stand von vor 3, 5 oder 9 Tagen? Ist dann die oben erwähnte Abfrage der Backup Software gegen msdb..backupset mit einem „Top 1“ und „order by last_lsn desc“  noch eine gute Prüfung?

Die Antwort lautet nein!

Was passiert denn mit der Tabelle msdb..backupset, wenn ich einen älteren Stand der Datenbank wiederherstelle? Genau, gar nichts. Der geneigte DBA verwendet ein „RESTORE… WITH REPLACE“ und das Thema ist erledigt.   Ich gebe zu, ich bereinige an der Stelle eben nicht die Sicherungs- Historie. Ich habe das automatisiert durch einen SQL Server Agent Job, der das für mich einmal am Tag erledigt und Einträge älter als X Tage löscht.

Würde ich vorher über die GUI die Datenbank löschen, dann hab ich ja die Option zu sagen: Lösche mir auch die Backup Historie.

LSN_Query2_optionen

Oder ich mache diesen Aufruf:
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = 'MeineDatenbank'

Aber nochmal: machen wir so was manuell? Meistens nicht.

Bei meinem speziellen Fall ist es nun so, dass die Sicherungssoftware mit Hilfe der „Log Gap Detection“  jetzt zum Beispiel etwas ermittelt wie:

LSN_Query3_results

Die LSNs stimmen nicht überein und die Sicherungssoftware entscheidet sich jetzt für eine Vollsicherung.

Das die erste Logsicherung nach einer Vollsicherung funktioniert liegt, so vermute ich, ebenfalls an den Metadaten der Sicherungssoftware. Wahrscheinlich haben die Programmierer gedacht: zuletzt wurde eine Vollsicherung gemacht, ergo ist dies die erste Transaktionslogsicherung und das passt.

Beim Hersteller hab ich das ganze Thema natürlich auch schon eingekippt und ein entsprechender HotFix ist jetzt in Arbeit.

Als Tipp habe ich mit auf den Weg gegeben, das ORDER By last_lsn DESC durch ein ORDER by backup_finish_date zu ersetzen. Dann bekommt man auch die passende LSN zurück, die zur Sicherungskette passt.

 

 

Veröffentlicht unter DMVs, Maintenance, SQL Server | Verschlagwortet mit , , , , , , | Kommentar hinterlassen

SQL Server 2016 und die SMOs…. (wenn Version 130 nicht so recht will…)

Hallo alle miteinander,

ich hab mich vor Kurzem ein wenig mit den SQL Server SMOs auseinandergesetzt. Die SMOs sind ja dazu gedacht einen SQL Server programmatisch zu managen. Weiterführende Infos finden sich hier.

Nun, warum hab ich mich überhaupt näher mit den SMOs beschäftigt? Die Backup-Software mit der ich arbeite nutzt im Hintergrund für einige Operationen, wie zum Beispiel das Browsen einer Instanz und dem Auflisten der diversen Datenbanken, eben diese SMOs.

Bis dato hatte ich auch gar keine Probleme gehabt, bis ich auf einem SQL Server 2016 eine Datenbank offline gesetzt habe. Ein Log oder Fullbackup über die Software lief zwar weiterhin, aber um einiges länger als ich es gewohnt war. Zudem bekam ich auf einmal im SQL Server Log lauter Fehlermeldungen mit Fehlercode 18456 und der Nachricht „Failed to open explicitly specified database“. Auf einem SQL Server 2014 jedoch lies sich dieses Verhalten nicht reproduzieren.

Das war der Grund meiner kleinen Recherche. Zuerst hatte ich den Hersteller der Backup Software angeschrieben.

Nach kurzem hin und her kam dann vom Hersteller die Aussage, dass die Entwicklungsabteilung das Problem nachstellen konnte. Darüber hinaus wurde aber auch gesagt: das Entwicklungsteam glaubt, dass es ein Microsoft Problem ist.

Ich hab dann einen PowerShell Dreizeiler erhalten, womit man das Verhalten nachstellen konnte:

Den oben stehenden Code habe ich dann kurz in Hinblick auf Instanzname und Datenbankname angepasst und direkt auf meinem SQL Server 2016 laufen lassen. Das Ergebnis waren lauter login failed Meldungen im SQLServer Log und keine Reaktion mehr von der PowerShell Session.

Dann dachte ich mir: ok, ggf. hat der Dirk eine „strubbelige“ SQL Server Installation und ggf. irgendetwas ausgelassen. Also war mein nächster Schritt schnell einen SQL Server 2016 aus dem Azure Marketplace zu schnappen und bereitzustellen.

Sobald die Maschine bereit stand hab ich eine Datenbank namens OfflineDB angelegt und das obenstehende Script ausgeführt. Zu meiner Verwunderung lief es problemlos durch. Die Erklärung hatte ich aber auch schnell parat. Der Unterschied zu der Azure Marketplace VM und meinem Server war, dass die Azure VM inklusive einem SQL Server Management Studio daher kommt, in dem Fall ein SSMS 17.4. Somit kommen auch die Assemblies vom SQL Server 2017 mit auf das System:

AssemblyPfad

Dann hab ich noch schnell überlegt, wie ich in einer PowerShell Session jetzt explizit ein Assembly ansprechen kann. Dies geht ganz einfach mit Add-Type -Path

Somit hab ich dann folgende Code Schnipsel ausprobiert:

In dem oberen Beispiel spreche ich das 2016er Assembly an, im unteren das 2017er. Auch nochmal hier links (2016 / 130) und rechts (2017 / 140) zu sehen:

SMOTest

Im linken Fenster passierte dann auch nichts mehr und ich hab auch wieder „brav“ meine failed logins produziert:

LoginFailed

Meine Interimslösung für meinen SQL Server 2016 war jetzt das SQL Server Management Studio 17.x zu installieren. Somit hab ich dann die aktuellen 2017er Assemblies auf dem Server gehabt.

Diese werden nun auch von der BackupSoftware angesprochen und jetzt läuft das Backup der Instanz mit einer offline Datenbank wieder problemlos.

Dies ist natürlich nicht der Weisheit letzter Schluß, denn eigentlich bin ich kein Fan davon, dass SSMS auf einem Server zu installieren und dies ist jetzt erst einmal auch nur ein Workaround. Microsoft hat jedoch etwas in Hinblick auf die Verteilung der SMOs geändert. Diese finden sich ab der Version 2017 nicht mehr in dem SQL Server Feature Pack wieder. Stattdessen wir nun ein NuGet Package angeboten. Sie auch hier: https://docs.microsoft.com/de-de/sql/relational-databases/server-management-objects-smo/installing-smo

Wie ich das jetzt problemlos auf einem Windows Server 2012 R2 oder 2016 bekomme muss ich noch herausfinden.

Bis dahin bedanke ich mich für’s Lesen.

Veröffentlicht unter Azure, PowerShell, SQL Server, SQL Server Administration, SSMS | Verschlagwortet mit , , , , | 2 Kommentare

SQL Server Perf Baseline Reports – nächstes Issue und Fix

Hallo zusammen,

und weiter geht es mit dem Thema Performance Baseline Reports. Vor kurzem habe ich ja bereites über meine kleine „Odyssee“ geschrieben bzgl. der Laufzeitfehler und meine Fehlerbehebung.

Meine Lösung in der Prozedur spGetPerfCountersFromPowerShell die Kommas mit Punkten zu tauschen funktioniert soweit auch ganz gut und der entsprechende SQL Agent Job „DBA – PerfMon Counter Collection“ läuft in der Regel auch fehlerfrei.

ABER: manchmal halt leider doch noch nicht. Zwischendurch fällt der Job immer noch auf die Nase, dann aber mit dem Fehler

Cannot convert a char value to money. The char value has incorrect syntax.

Natürlich habe ich hier auch wieder ein wenig geknobelt. Dafür hab ich mir die  Variante der Prozedur wieder eingestellt, wo die Konvertierung zu varchar (20) erfolgt. Macht eigentlich keinen Sinn, da ja die Reports nachgelagert nicht zwingend funktionieren.  Aber wenn ich nun einen convert char to money Fehler habe, dann scheint ja auf einmal sich irgendein Buchstabe oder sonstiges Zeichen eingeschlichen zu haben.

Und dem ist auch wirklich so:

negativerExponent

Die Abfrage der Performancecounter liefert hin und wieder so kleine Werte zurück, dass das Windows System dann einfach eine Zahl mit einem negativen Exponenten zurückgibt. Dadurch, dass wir nun einen Buchstaben in unseren Datensatz drin haben können wir nicht zum Datentyp Money konvertieren.

Jetzt hatte ich mir überlegt, ob man sich das noch irgendwie schön rechnen soll. Der Wert 8,9999918995165E-05 zum Beispiel ist ja  8,9999918995165 / 100000. Das ergibt die wahnsinnig hohe Zahl von 0,000089999918995165. Wenn man das jetzt zum Datentyp Money konvertiert, welcher nur 4 Dezimalstellen hat, dann landen wir bei 0,0001 (bei dem Datentypen erfolgt eine Rundung). Da hab ich mir überlegt, ob es wirklich Sinn macht so einen Wert wegzuschreiben oder dann nicht einfach zu sagen der Wert ist 0.

Welchen Aufwand man betreiben muss, um den passenden Zahlenwert zur Laufzeit zu bekommen hab ich mal kurz nachgestellt:

  DECLARE @wert VARCHAR (50)
  SET @wert = '8,9999918995165E-05'

  SELECT @wert AS Ausganswert, LEFT(@wert,5)   AS Zusammenstauchen, 
  REPLACE (LEFT(@wert,5),',','.')  AS KommaRaus, 
  CAST(REPLACE (LEFT(@wert,5),',','.')  AS DECIMAL(5,4)) AS MachmalDezimal,
  CAST(REPLACE (LEFT(@wert,5),',','.')  AS DECIMAL(5,4)) / 100000 AS Exponentwegrechnen, 
  CONVERT (money,CAST(REPLACE (LEFT(@wert,5),',','.')  AS DECIMAL(5,4)) / 100000) AS Zielwert

VarcharZuMoney

(Vielleicht gibt es hier ja auch einen eleganteren Weg und ich war hier betriebsblind.)

Daher mein pragmatischer Ansatz in der entsprechenden Prozedur:

CASE
 WHEN [ct2].[output] LIKE '%E-%'
 THEN '0'
 ELSE CONVERT(MONEY, REPLACE([ct2].[output], ',', '.'))
 END AS [CounterValue]

Dies ist ggf. ein klein wenig geschummelt, aber den Reports macht es an der Stelle nichts aus, wenn ein Wert 0,0001 oder 0 ist.

Wer meine neue Variante ausprobieren möchte, der wird auf Github fündig. https://github.com/DiHo78/tigertoolbox/tree/DiHo78-TT-SQLPerformanceBaseline-ProcedureFix?files=1

Ich würde mich über Tester freuen.
 

Veröffentlicht unter Performance Baselining Reports, SQL Server, SQL Server Administration, Uncategorized | Verschlagwortet mit , , , , | Kommentar hinterlassen

Integration Services Catalog – Wenn die Validierung nicht will….

Oder auch: DTS_E_OLEDBERROR. An OLE DB error has occurred. Error Code: 0x80004005

Hallo zusammen,

heute will ich kurz über ein Problem berichten, welches mich doch einige Stunden an Recherche gekostet hat.

Ich bin inzwischen ein Fan der Integration Services Catalogs. Es macht die Bereitstellung von SSIS Paketen / Projekten doch recht einfach und die Parametrisierung ist nahezu ein Kinderspiel. Wie schnell so ein Katalog eingerichtet ist hat Mark Broadbent vor Kurzem gebloggt: https://tenbulls.co.uk/2017/04/27/deploying-ssiscat/ .

Vor Allem gefällt mir, dass ich nun ohne großen Mehraufwand auch direkt auf ein Projekt im Katalog Berechtigungen vergeben kann. Oder aber ich setze direkt auf mehrere Projekte die Berechtigungen, indem ich auf einen übergeordneten Ordner die Berechtigungen vergebe. Je nachdem für welchen Aufbau ich mich entschieden habe.

SSISDB:

SSISDB_Baum

Ordner:2017-05-04 13_21_14-Folder Properties

Projekt:ProjectProperties_ssisCat

 

Jetzt hatte ich vor Kurzem folgende Situation:

Ein Benutzer hatte das Projekt …V2 abgelegt bzw. hatte ich die Vorbereitungen getroffen, dass der Benutzer auf dem Projektordner die Berechtigungen Read, Modify und Execute hat. Wie auch auf dem Screenshot ersichtlich. Eine Bereitstellung ist kein Problem gewesen. Aber: die Validierung eines bestimmten Paketes schlug immer fehl:

ErrorSSISValidation

Wenn ich mit meinem Konto das Projekt bzw. das besagte Paket validierte, dann war alles in Ordnung.

Bei dem besagten Paket war eine der verwendeten Datenquellen eine Exceldatei. Zuerst war ja mein Verdacht, dass es irgendwas mit dem Microsoft.ACE.OLEDB.12.0 Treiber zu tun hatte. Bei einer Meldung wie DTS_E_OLEDBERROR nicht ganz abwegig… Aber ich konnte mit meinem Konto das Paket problemlos prüfen. Also weiter ran getastet. Die Zugriffe auf den UNC Pfad, wo die Quelldatei lag, waren ebenfalls korrekt gesetzt.

Es gab aber auf dem Host System, wo der SSIS Katalog lag, einen Unterschied zwischen den Berechtigungen der unterschiedlichen Benutzerkonten: ich war lokaler Admin, der andere Benutzer eben nicht.

Hierzu sei auch einmal schnell erwähnt: im Normalfall sollten die Rollen Windows Admin und SQL Server Admin getrennt sein. Im Falle der Entwicklungsumgebung hatte ich hier aber eine Ausnahme gemacht.

Ich habe dann den Benutzer mal in die Gruppe der Remote Desktop Benutzer aufgenommen, sodass er sich auf den Server anmelden konnte. Dann eben auf den Katalog verbunden und das Paket validiert: das ging problemlos.

Dann die Validierung vom lokalen Management Studio wieder angeworfen: Validierung ebenfalls erfolgreich. Danach den Benutzer wieder abgemeldet und nur vom lokalen Client aus die Validierung angestoßen: es schlug wieder fehl mit dem altbekannten Fehler.

Um nun dem Fehler weiter auf die Schliche zu kommen habe ich mich des Tools Process Monitor aus der SysInternals Suite bedient. Mit dem Tool lassen sich ja eine Unmenge an Prozessaktivitäten, Dateizugriffen und Ähnlichem in Echtzeit überwachen.

Also hatte ich kurz den ProcMon angeworfen, die Validierung vom lokalen Client angestoßen und dann die Zeitstempel der Validierung mit der ProcMon Ausgabe abgeglichen. Und da bin ich endlich fündig geworden:

ACCESS_IS_DENIED_PRocMon

Es wurde ein ACCESS DENIED Fehler geworfen bei einer CreateFile Operation unter C:\Users\Default\AppData\Local\Microsoft\Windows\InetCache\Content.MSO\…

Warum jetzt auf das Default user Profile zurückgegriffen wurde konnte ich bis gerade nicht sagen. Wo ich aber gerade diesen Artikel schreibe und noch mal quer gegoogelt habe, bin ich auf folgenden Post

http://www.alankoo.com/2012/09/strange-error-loading-excel-files-xlsx.html

und einer ganz wichtigen Aussage gestoßen:“…ACE uses the impersonated user’s Windows temp folder to read-write its data. Therefore if your application is using impersonation with an account that does not have a profile on the server (not an uncommon situation), then ACE will not be able to create its temp files.”

Das bedeutet also auch, wenn ich mit meinem lokalen Management Studio remote einen SSIS Katalog prüfe, dann habe ich zu dem Zeitpunkt auf dem Server eben auch kein valides Profil.

Im Nachgang habe ich mir auf dem Server eine entsprechende SSIS_User Gruppe angelegt. Hier dann meinen Testbenutzer und das Konto des Entwicklers hinzugefügt und Schreibberechtigungen auf ..\Content.MSO gegeben.  Dies war für mich die Lösung, damit ein Paket oder Projekt problemlos remote validiert werden kann.

Falls jemand hier eine andere, ggf. sogar bessere Lösung kennt, dann immer her damit. Ich werde dies nachgelagert festhalten.

 

Lesson learned von meiner Seite:

doch mal genauer schauen, was so bei den Downloads geschrieben steht: Download ACE

und die Leute überzeugen ggf. doch eher CSVs zu verwenden. Das macht weniger Umstände ;-)

Veröffentlicht unter SQL Server, SQL Server Administration, SSIS | Verschlagwortet mit , , , | 2 Kommentare

SQL Server Baseline Reports – PerfMon Counter Collection Job schlägt fehl

Hallo zusammen,

in meinem letzten Beitrag habe ich ja vom MSSQL Tiger Team die SQL Performance Baseline Collection und die Reports kurz vorgestellt.

Heute nun folgt ein kleiner Tipp, um einen Fehler zu beheben, denn ich (interessanterweise) sporadisch hatte, nämlich der Abbruch des SQL Server Agent Jobs „DBA – PerfMon Counter Collection“. Darüber hinaus schildere ich aber auch meine Debug-Odyssee.

Zuerst war es ja nur die nicht aktivierte xp_cmdshell, (im vorherigen Post schon erwähnt). Im Anschluss hatte ich aber folgende Meldung in der Job Historie:

„Executed as user: mydomain\mysvcuser. Arithmetic overflow error converting expression to data type money. [SQLSTATE 22003] (Error 8115)  The statement has been terminated. [SQLSTATE 01000] (Error 3621).  The step failed.“

Also noch mal geschaut, ob es dazu schon eine Info gibt und siehe da: jemand hatte da schon auf der GitHub Seite ein entsprechendes Issue eingestellt:

image

Auf der Seite war dann auch schon eine Lösung vorgeschlagen:

The following fixed it for me:
This is due to CONVERT(money,ct2.[output]) in [dbo].[spGetPerfCountersFromPowerShell].
Replacing this by CONVERT(varchar(20),ct2.[output]) seems to do the job.

Blauäugig wie ich war, habe ich genau diese Anpassung am Anfang auch gemacht und der Job lief einwandfrei. Nachgelagert jedoch hat sich dann jedoch das Problem ergeben, dass die Reports nicht so recht wollten. Stattdessen bekam ich Folgendes zu sehen:

image

Also schnell im ReportServer Service Log nachgeschaut, was denn da nun schon wieder los war. Ergebnis:

“—> System.Data.SqlClient.SqlException: Error converting data type varchar to float.”

Ja wie jetzt? Schon wieder Ärger mit der Konvertierung? Also die SQL Server Data Tools geöffnet, den entsprechenden Report geschnappt (es war übrigens der “Start” Report SQL Performance Dashboard) und die Abfrage im entsprechenden Data Set angesehen (den gelb markierten Bereich habe ich auskommentiert, da dieser für’s Debugging unerheblich war und ich ja auch schnell schauen wollte was los ist):

DECLARE @CPUCOUNT INT
SELECT @CPUCOUNT=cpu_count FROM sys.dm_os_sys_info
SELECT 
Date,
ROUND([processor(_total)\% processor time],0) AS 'Processor Utilization',
ROUND(([processor(_total)\% privileged time]*[processor(_total)\% processor time])/100,0) AS 'Priviledged Utilization',
ROUND(([processor(_total)\% processor time]*[process(sqlservr)\% processor time])/100/@CPUCOUNT,0) AS 'SQL Utilization',
ROUND([sql statistics\batch requests/sec],0) AS 'Batch Request per second',
ROUND([sql statistics\sql compilations/sec],1) AS 'SQL Compilations per second',
ROUND([sql statistics\sql re-compilations/sec],1) AS 'SQL Recompilations per second',
ROUND([General Statistics\User Connections],0) AS 'User Connections',
ROUND([logicaldisk(_total)\avg. disk sec/read]*1000.0,1) AS 'Disk Read Latency', 
ROUND([logicaldisk(_total)\avg. disk sec/write]*1000.0,1) AS 'Disk Write Latency',
ROUND([memory\available mbytes],0) AS 'Available Memory In MB',
ROUND([transactions\free space in tempdb (kb)]/1024.0/1024.0,0) AS 'Free Space in Tempdb'
FROM
(
SELECT 
CounterName,
CONVERT(VARCHAR(20),DateSampled,101) AS 'Date',
ROUND(CounterValue,7) AS 'CounterValue'
FROM PerformanceCounter
WHERE CounterName IN ('processor(_total)\% processor time',
'processor(_total)\% privileged time',
'process(sqlservr)\% processor time',
'sql statistics\batch requests/sec',
'sql statistics\sql compilations/sec',
'sql statistics\sql re-compilations/sec',
'General Statistics\User Connections',
'logicaldisk(_total)\avg. disk sec/read', 
'logicaldisk(_total)\avg. disk sec/write', 
'memory\available mbytes',
'transactions\free space in tempdb (kb)')
--and DateSampled between @StartDate and @EndDate

) AS p
PIVOT 
(
AVG(CounterValue)
FOR 
CounterName IN ([processor(_total)\% processor time],
[processor(_total)\% privileged time],
[process(sqlservr)\% processor time],
[sql statistics\batch requests/sec],
[sql statistics\sql compilations/sec],
[sql statistics\sql re-compilations/sec],
[General Statistics\User Connections],
[logicaldisk(_total)\avg. disk sec/read],
 [logicaldisk(_total)\avg. disk sec/write], 
 [memory\available mbytes],
 [transactions\free space in tempdb (kb)])
) AS pivottable
ORDER BY [Date]

Die Abfrage warf den gleichen Fehler zurück: Error converting data type varchar to float. Also weiter ran getastet und nur das Sub-Select geschnappt:

SELECT 
CounterName,
CONVERT(VARCHAR(20),DateSampled,101) AS 'Date',
ROUND(CounterValue,7) AS 'CounterValue'
FROM PerformanceCounter
WHERE CounterName IN ('processor(_total)\% processor time',
'processor(_total)\% privileged time',
'process(sqlservr)\% processor time',
'sql statistics\batch requests/sec',
'sql statistics\sql compilations/sec',
'sql statistics\sql re-compilations/sec',
'General Statistics\User Connections',
'logicaldisk(_total)\avg. disk sec/read', 
'logicaldisk(_total)\avg. disk sec/write', 
'memory\available mbytes',
'transactions\free space in tempdb (kb)')





Und ja, der Fehler kam natürlich. Also noch weiter ran getastet und einfach mal stumpf die Tabelle dba_local.dbo.PerformanceCounter

 image

des entsprechenden Zielservers abgefragt. Dabei ist mir dann auf- (und wieder eingefallen), dass in der Spalte Countervalue Zahlenwerte mit Kommas stehen

image

und dass das mit der Funktion ROUND() irgendwie nicht so recht wollte.

image

Also mal schnell einen Zahlenwert aus der Tabelle gepickt und aus dem Komma ein Punkt gemacht und tada… kein Fehler mehr

image

Jetzt ging die Überlegung weiter: Packe ich jeden einzelnen Report an und korrigiere die DataSets, indem ich ein REPLACE einbaue?

ROUND(REPLACE(CounterValue,',','.'),7) AS 'CounterValue'

Wäre zwar möglich, aber wer weiß, wo es dann nachgelagert schon wieder “geknallt” hätte. Jedes Mal nach dem Ausführen des Jobs ein Update auf die Tabelle fahren und dann das Komma durch einen Punkt ersetzen? Auch nicht wirklich sinnig. Also einmalig die Tabelle dba_local.dbo.PerformanceCounter “bereinigt”, indem ich das Update..Replace gemacht habe und dann die entsprechende Anpassung in der Prozedur dba_local.dbo.spGetPerfCountersFromPowerShell gemacht (entsprechende Stelle wieder gelb hervorgehoben).

USE [dba_local]
GO
/****** Object:  StoredProcedure [dbo].[spGetPerfCountersFromPowerShell]    Script Date: 26.04.2017 22:42:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Adrian Sullivan, af.sullivan@outlook.com
-- Create date: 2016/12/12
-- Description:	Taking away the need for PS1 files and script folder
--
-- Modification date:  2017/04/25
-- Modification Author: Dirk Hondong, dirk@hondong.de
-- Modification: Replace , with .  before converting ct2.output to MONEY
-- =============================================
ALTER PROCEDURE [dbo].[spGetPerfCountersFromPowerShell]
AS
BEGIN

   DECLARE @syscounters NVARCHAR(4000)
   SET @syscounters=STUFF((SELECT DISTINCT ''',''' +LTRIM([counter_name])
   FROM [dba_local].[dbo].[PerformanceCounterList]
   WHERE [is_captured_ind] = 1 FOR XML PATH('')), 1, 2, '')+'''' 

   DECLARE @cmd NVARCHAR(4000)
   DECLARE @syscountertable TABLE (id INT IDENTITY(1,1), [output] VARCHAR(500))
   DECLARE @syscountervaluestable TABLE (id INT IDENTITY(1,1), [value] VARCHAR(500))

   SET @cmd = 'C:\WINDOWS\system32\WindowsPowerShell\v1.0\powershell.exe "& get-counter -counter '+ @syscounters +' | Select-Object -ExpandProperty Readings"'
   INSERT @syscountertable
   EXEC MASTER..xp_cmdshell @cmd; 

   INSERT [dba_local].[dbo].[PerformanceCounter] (CounterName, CounterValue, DateSampled)
   SELECT  REPLACE(REPLACE(REPLACE(ct.[output],'\\'+@@SERVERNAME+'\',''),' :',''),'sqlserver:','')[CounterName] ,  
   CONVERT( MONEY, REPLACE([ct2].[output], ',', '.')) [CounterValue], 
   GETDATE() [DateSampled]
   FROM @syscountertable ct
   LEFT OUTER JOIN (
   SELECT id - 1 [id], [output]
   FROM @syscountertable
   WHERE PATINDEX('%[0-9]%', LEFT([output],1)) > 0  
   ) ct2 ON ct.id = ct2.id
   WHERE  ct.[output] LIKE '\\%'
   ORDER BY [CounterName] ASC
END

Vielleicht gibt es ja auch eine noch elegantere Lösung, aber für mich was dies die einfache Variante.

Jedenfalls habe ich auf GitHub mal eine sogenannte Fork der TigerToolBox gemacht, um dann nachgelagert eine Modifikation des Skriptes CREATEOBJECTS.sql abzulegen.  Um da dran zu kommen, geht einfach auf

https://github.com/DiHo78/tigertoolbox/tree/master/SQL-Performance-Baseline

Danach müsst Ihr noch in den entsprechenden Branch wechseln

image

und Ihr findet die funktionierende Version.

 

Nachtrag (10.05.2017):

Ich habe heute die aktuelle Version von Microsoft in meinen Fork gezogen. Es gab in der Zwischenzeit nämlich eine andere Anpassung an der entsprechenden Prozedur. Nachgelagert habe ich dann meine oben erwähnte Änderung wieder mit eingebaut. Diese ist nun jedoch nicht mehr in dem Branch zu finden, sondern direkt in meiner master Version.:

2017-05-10 23_14_20-tigertoolbox_SQL-Performance-Baseline at master · DiHo78_tigertoolbox

Darüber hinaus stehe ich via GitHub auch mit jemanden vom TigerTeam in Kontakt. Ggf. findet meine Anpassung ja Ihren Weg in die master Version des Microsoft Projektes.

Nachtrag (12.05.2017):

Mit GitHub stehe ich noch ein wenig auf Kriegsfuss. Der oben erwähnte Fix findet sich momentan unter https://github.com/DiHo78/tigertoolbox/tree/DiHo78-TT-SQLPerformanceBaseline-ProcedureFix und hat auch die hier beschriebenen Änderungen inkludiert.

Veröffentlicht unter Maintenance, Performance Baselining Reports, SQL Server, SQL Server Administration, SSRS | Verschlagwortet mit , , , , , , | Kommentar hinterlassen