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.
 

Advertisements
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 , , , | Kommentar hinterlassen

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

SQL Server Performance Baseline Reports – für lau

Hallo zusammen,

falls Ihr noch nicht darüber gestolpert seid, das MSSQL Tiger Team hat auf GitHub unter der TigerToolBox ein Projekt namens SQL-Performance-Baseline liegen, auf dass man ohne Probleme zugreifen kann.

Das Projekt besteht aus einigen wenigen Skriptdateien für die zu überwachenden SQL Server und einem SSRS Projekt und hilft Euch Eure SQL Server zu überwachen.

Warum Performance Baselining wichtig ist und wie die Lösung funktioniert wird in folgendem Artikel beschrieben:

https://blogs.msdn.microsoft.com/sql_server_team/sql-server-performance-baselining-reports-unleashed-for-enterprise-monitoring/

Die Schritte zur Implementierung sind aber wirklich einfach:

1. Bei GitHub die TigerToolBox herunterladen,

image

Zip File entpacken und dann nach dem Ordner SQL-Performance-Baseline Ausschau halten.

2. Die Scripte CREATEDATABASE.sql, CREATEOBJECTS.sql und CREATECollectionJob.sql auf den zu überwachenden SQL Servern ausführen.

Die Dateinamen sind selbstsprechend. Wir legen eine Datenbank namens dba_local in der zu überwachenden Instanz an, erstellen in dieser DB die notwendigen Tabellen und Prozeduren um danach 3 SQL Server Agent Jobs zu haben, welche für uns die Performancedaten sammeln.

Ich habe heute Abend dies mal eben auf die Schnelle eingerichtet. Hat keine 2 Minuten gedauert. Natürlich sollte man vor dem Einsatz in Produktivumgebungen ruhig noch mal einen Blick riskieren.

Am Ende schaut das Ergebnis jedenfalls so oder so ähnlich aus:

image

Damit der Job Load SystemHealthSession einwandfrei funktioniert muss noch das Skript CreateSystemhealthDBAndSchema.sql ausgeführt werden. Dies hat das Tiger Team sowohl auf der GitHub Seite als auch im Blogartikel vergessen oder ich hab es überlesen.

Weiterer Wermutstropfen: der Job DBA – PerfMon Counter Collection verwendet die cmdshell. Da im Normalfall diese immer abgedreht sein sollte, wird Euch der Job natürlich auf die Nase fallen. Ich habe mir einfach damit geholfen, dass die xp_cmdshell nur zur Laufzeit aktiviert und danach wieder deaktiviert wird. Wahrscheinlich gibt es auch noch eine elegantere und sichere Lösung, aber da muss ich noch ein wenig recherchieren und ausprobieren.

3. Die Report.zip entpacken und die Solution Datei des SSRS Projektes öffnen (SQL Server Data Tools sollten verständlicherweise installiert sein).

image

Dann schnell die Projekteigenschaften installieren und Eure Reporting Services Instanz Eurer Wahl auswählen und das Projekt bereitstellen.

image

image

 

Auch dies ist eine Sache von wenigen Minuten und schon habt Ihr Zugriff auf die Reports:

image

Eine Sache ist jedoch wichtig: Die Reports sind alle so gestrickt, dass diese mit integrierter Authentifizierung arbeiten.  Wenn Ihr also von Eurem Client PC aus auf die Reporting Services zugreift und dann auf die dba_local eines SQL Servers, dann ergibt sich daraus ein Double-Hop. Ergo müssen Eure involvierten Systeme alle sauber für Kerberos konfiguriert sein. Die Themen mit gescheiten SPNs, trust for delegation usw. würde hier jedoch jetzt den Rahmen sprengen. Smiley

Alternativ müsst Ihr die Reports entsprechend anpacken, dass Ihr entweder nach Euren Credentials gefragt werdet oder Ihr hinterlegt einen Servicebenutzer, den ich für das Reporting verwenden wollt.

image

Und zum Schluss noch folgender Tipp: euer Browser sollte die US englische Lokalisierung haben.  Andernfalls bekommt man mitunter solche Fehlermeldungen:

image

Da ich jetzt nicht der SSRS Spezi bin, habe ich hier noch keine Lösung gefunden, sondern nur den gerade erwähnten Workaround.

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

Alle Datenbankbackups aus einem Ordner wiederherstellen

Hallo zusammen,

ich tippe mal, dass das Thema für viele schon ein alter Hut ist. Dennoch wollte ich ganz kurz darüber schreiben.

Folgender Hintergrund: Es soll ein neues Testsystem mit einer SQL Server 2016 Installation aufgebaut werden. Dabei sollen sämtliche Testdatenbanken, welche sich derzeit noch auf einem SQL Server 2008 R2 befinden, überführt werden.

Nun gibt es ja mehrere Wege, dies zu erreichen. Ich kann z.B. den Copy Database Wizard verwenden, welcher mir ein SSIS Paket erzeugt. Dieses Paket sorgt dann für das Kopieren der Datenbanken, offline (DETACH Methode) oder online (mit Hilfe von SMO’s). Generell bin ich aber irgendwie kein Fan von diesem Wizard und der SSIS Lösung. Zumal ich es irgendwie noch nie geschafft habe, dass ein Paket auf Anhieb durchläuft wenn zum Beispiel 10 oder mehr Datenbanken ausgewählt wurden.

Eine weitere Methode wäre die vorhandene Backuplösung zu verwenden. In der Regel hat man ja einen aktuellen Sicherungsstand im Zugriff und könnte so bequem die Datenbanken auf das neue System spielen.

Dies hat in meinem Fall aber nicht geklappt, da die eingesetzte Version des Backupprogrammes den SQL Server 2016 noch nicht unterstützt und somit eine Wiederherstellung nicht möglich ist.

Was habe ich also in dem Fall gemacht? Kurz Google bemüht und bei der Suche bin ich auf folgenden Artikel von Tibor Karaszi gestoßen:

„Restore all databases from a number of backup files“

Tibor stellt eine Prozedur bereit, welche aus einem vorgegebenen Ordner sämtliche Backupdateien ermittelt und mit Hilfe einiger temporären Tabellen und den Befehlen RESTORE HEADERONLY und RESTORE FILELISTONLY werden auf sehr einfache und schnelle Weise die eigentlichen Restore Skripte generiert. (Und Dank der Maintenance Solution von Ola Hallengren kann man auch schnell mal native „copy_only“ backups ziehen.)

Jedoch lässt sich die Prozedur nicht so ohne Weiteres auf einem SQL Server 2016 anwenden.  Mit dem SQL Server 2016 ist bei dem Befehl RESTORE FILELISTONLY ein weiteres Ausgabefeld namens SnapshotUrl hinzugekommen. welches beim Anlegen / Befüllen der temporären Tabelle noch nicht berücksichtigt ist.  Laut MSDN Doku beinhaltet das Feld die URL für den Azure Snapshot.

Lange Rede, kurzer Sinn. Hier ist die von mir angepasste Version von Tibors Prozedur:

CREATE PROCEDURE [dbo].[sp_RestoreFromAllFilesInDirectory]
 @SourceDirBackupFiles nvarchar(200), @DestDirDbFiles nvarchar(200), @DestDirLogFiles nvarchar(200)
AS
--Originally written by Tibor Karaszi 2004. Use at own risk.
--Restores from all files in a certain directory. Assumes that:
--  There's only one backup on each backup device.
--  Each database uses only two database files and the mdf file is returned first from the RESTORE FILELISTONLY command.  <<
--  edited by Dirk Hondong. Multiple Database files are supported (in a simple way)
--Sample execution:
-- EXEC sp_RestoreFromAllFilesInDirectory 'C:\Mybakfiles\', 'D:\Mydatabasesdirectory\' ,’C:\MylogDirectory\’
SET NOCOUNT ON
--Table to hold each backup file name in
CREATE TABLE #files(fname varchar(200),depth int, file_ int)
INSERT #files
EXECUTE master.dbo.xp_dirtree @SourceDirBackupFiles, 1, 1
--Table to hold the result from RESTORE HEADERONLY. Needed to get the database name out from
CREATE TABLE #bdev(
 BackupName nvarchar(128)
,BackupDescription nvarchar(255)
,BackupType smallint
,ExpirationDate datetime
,Compressed tinyint
,Position smallint
,DeviceType tinyint
,UserName nvarchar(128)
,ServerName nvarchar(128)
,DatabaseName nvarchar(128)
,DatabaseVersion bigint
,DatabaseCreationDate datetime
,BackupSize numeric(20,0)
,FirstLSN numeric(25,0)
,LastLSN numeric(25,0)
,CheckpointLSN numeric(25,0)
,DatabaseBackupLSN numeric(25,0)
,BackupStartDate datetime
,BackupFinishDate datetime
,SortOrder smallint
,[CodePage] smallint
,UnicodeLocaleId bigint
,UnicodeComparisonStyle bigint
,CompatibilityLevel tinyint
,SoftwareVendorId bigint
,SoftwareVersionMajor bigint
,SoftwareVersionMinor bigint
,SoftwareVersionBuild bigint
,MachineName nvarchar(128)
,Flags bigint
,BindingID uniqueidentifier
,RecoveryForkID uniqueidentifier
,Collation nvarchar(128)
,FamilyGUID uniqueidentifier
,HasBulkLoggedData bigint
,IsSnapshot bigint
,IsReadOnly bigint
,IsSingleUser bigint
,HasBackupChecksums bigint
,IsDamaged bigint
,BegibsLogChain bigint
,HasIncompleteMetaData bigint
,IsForceOffline bigint
,IsCopyOnly bigint
,FirstRecoveryForkID uniqueidentifier
,ForkPointLSN numeric(25,0)
,RecoveryModel nvarchar(128)
,DifferentialBaseLSN numeric(25,0)
,DifferentialBaseGUID uniqueidentifier
,BackupTypeDescription nvarchar(128)
,BackupSetGUID uniqueidentifier
,CompressedBackupSize bigint
,Containment bigint
)
--Table to hold result from RESTORE FILELISTONLY. Need to generate the MOVE options to the RESTORE command
CREATE TABLE #dbfiles(
LogicalName nvarchar(128)
,PhysicalName nvarchar(260)
,[Type] char(1)
,FileGroupName nvarchar(128)
,Size numeric(20,0)
,MaxSize numeric(20,0)
,FileId bigint
,CreateLSN numeric(25,0)
,DropLSN numeric(25,0)
,UniqueId uniqueidentifier
,ReadOnlyLSN numeric(25,0)
,ReadWriteLSN numeric(25,0)
,BackupSizeInBytes bigint
,SourceBlockSize bigint
,FilegroupId bigint
,LogGroupGUID uniqueidentifier
,DifferentialBaseLSN numeric(25)
,DifferentialBaseGUID uniqueidentifier
,IsReadOnly bigint
,IsPresent int
,TDEThumbprint uniqueidentifier
,SnapshotUrl nvarchar (300)
)
DECLARE @fname varchar(200)
DECLARE @dirfile varchar(300)
DECLARE @LogicalName nvarchar(128)
DECLARE @PhysicalName nvarchar(260)
declare @fileid int
DECLARE @type char(1)
DECLARE @DbName sysname
DECLARE @sql nvarchar(2000)
DECLARE files CURSOR FOR
SELECT fname FROM #files
DECLARE dbfiles CURSOR FOR
SELECT LogicalName, PhysicalName, Type, FileId FROM #dbfiles
OPEN files
FETCH NEXT FROM files INTO @fname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @dirfile = @SourceDirBackupFiles + @fname
--Get database name from RESTORE HEADERONLY, assumes there's only one backup on each backup file.
TRUNCATE TABLE #bdev
INSERT #bdev
EXEC('RESTORE HEADERONLY FROM DISK = ''' + @dirfile + '''')
SET @DbName = (SELECT DatabaseName FROM #bdev)
--Construct the beginning for the RESTORE DATABASE command
SET @sql = 'RESTORE DATABASE [' + @DbName + '] FROM DISK = ''' + @dirfile + ''' WITH REPLACE, MOVE '
--Get information about database files from backup device into temp table
TRUNCATE TABLE #dbfiles
INSERT #dbfiles
EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @dirfile + '''')
OPEN dbfiles
FETCH NEXT FROM dbfiles INTO @LogicalName, @PhysicalName, @type, @fileid
--For each database file that the database uses
WHILE @@FETCH_STATUS = 0
BEGIN
IF @type = 'D' and @fileid = 1
SET @sql = @sql + '''' + @LogicalName + ''' TO ''' + @DestDirDbFiles + @DbName + '.mdf'', MOVE '
ELSE IF @type = 'D' and @fileid > 2
SET @sql = @sql + '''' + @LogicalName + ''' TO ''' + @DestDirDbFiles + @DbName + '.ndf'', MOVE '
ELSE IF @type = 'L'
SET @sql = @sql + '''' + @LogicalName + ''' TO ''' + @DestDirLogFiles + @DbName + '_log.ldf'''
FETCH NEXT FROM dbfiles INTO @LogicalName, @PhysicalName, @type, @fileid
END
--Here's the actual RESTORE command
PRINT @sql
--Remove the comment below if you want the procedure to actually execute the restore command.
--EXEC(@sql)
CLOSE dbfiles
FETCH NEXT FROM files INTO @fname
END
CLOSE files
DEALLOCATE dbfiles
DEALLOCATE files
GO

 

Ich hab Tibor vor einigen Wochen (ja, dieser Blogpost war schon etwas länger in der Pipeline) kontaktiert. Inzwischen hat er sich gemeldet und hat nun auch selbst eine 2016er Version bereit gestellt. Diese findet sich unter dem bereits oben erwähnten Link.

 

Jetzt muss ich aber das Thema noch etwas weiter ausführen, da es zu der „Problematik“ noch eine viel charmantere Lösung gibt: dbatools.io

Dies ist eine der tollsten PowerShelllösung, die ich seid kurzer Zeit kenne.

Wenn man Datenbanken von A nach B bringen muss, dann ist dass mit Hilfe der dbatools nur noch ein Einzeiler in Powershell und die Datenbanken werden per Backup/Restore überführt.

Oder man stellt, wenn man die Maintenance Solution von Ola verwendet, die Datenbanken einfach aus dem Verzeichnis wieder her mit dem Befehl:

Restore-SqlBackupFromDirectory -SqlServer MeinZielServer -Path \\MeineBackupLokation\MeinServer\MeineDatenbank

Einfacher geht es nun wirklich nicht.
Veröffentlicht unter Maintenance, PowerShell, SQL Server | Verschlagwortet mit , , , , | Kommentar hinterlassen

CREATE OR ALTER mit SQL 2016 SP1

Wie sich die Zeiten doch ändern. Vor einem Jahr habe ich kurz über das Thema  DROP…CREATE geschrieben, bzw. das Thema DROP..IF EXISTS angeschnitten, als es gerade neu war in der CTP des 2016ers.

Und jetzt, mit dem SP1, kommt tatsächlich die CREATE OR ALTER Syntax ins Haus geflattert.

Für mich als DBA ist das natürlich großartig zu wissen. Wenn man Datenbanken unter sich hat mit einer granularen Sicherheit, dann ist dieser Befehl nun echt Gold wert. Etwaige Applikationsupdates, welche auch die DB im Hintergrund anpacken, „zerschießen“ einem so nicht mehr die Berechtigungen, welche evtl. auf einzelnen Objekten vorhanden sind.

Also, schaut in Euren Skriptsammlungen und Quellcodeverwaltungen nach und überarbeitet Eure Skripte, wenn Ihr schon für den 2016er entwickelt.

Natürlich gibt es noch viele andere Neuerungen die mit dem SP1 kommen (neue query hint Optionen, Partitionierung und In-Memory Funktionalitäten in allen Editionen und und und…). Aber hier waren schon Andere schneller, was das bloggen angeht und mitunter auch schon richtig ausführlich. Hier nenne ich nur mal schnell den Andreas Wolter und Niko Neugebauer.

Und nun geht’s an Testen:

2016-11-17-10_07_42-sql-server-2008-r2-diagnostic-information-queries-cy-2016-sql-readonlysql

 

 

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

Sicherheitsupdates (MS16-136) für SQL Server 2012, 2014, 2016 veröffentlicht

Hallo zusammen,

gestern frisch von Microsoft veröffentlicht:

Microsoft-Sicherheitsbulletin MS16-136 – Hoch

Dabei handelt es sich um Sicherheitslücken, wo der potentielle Angreifer erweiterte Berechtigungen am SQL Server erlangen kann.

Für den SQL Server 2016 bedeutet es, dass es das CU3 gibt.

Happy patching and testing…

 

Veröffentlicht unter SQL Server, SQL Server Administration | Verschlagwortet mit , , , | 1 Kommentar