Mark Aslan Kuschels Blog

SQL Server, Azure, Business Intelligence, Smart Home

How to deploy IBM DB2 Driver into Azure Data Factory SSIS Integration Runtime

In Microsofts Azure Cloud the Azure Data Factory (ADF) is the state of the art tool to transfer higher amounts of data between database systems. To leverage a compability with classic ETL project, there is also an Azure Data Factory SSIS-Integration Runtime (ADF SSIS-IR).

 

The SSIS-IR is running on one or several virtual machines, depending on chosen tarif, as a variant of SQL Server Integration Services (SSIS). As all other PaaS you do not need to care about patching or managend of operation system and application, which also means there is no direct access to the operation system.
This can lead to challenges when installing drivers.

 

To install extensions and drivers you need to upload installation files and scripts to Blobstorage-Account. For a series of drivers, there are samples available on GitHut, the process is briefly descriveb at Microsoft Docs.

Unfurtanetly there is no sample for installing IBM DB2 drivers.

 

To install these drivers, you need:

  • Access to IBM DB2 Installation medium
  • Access to a system with administration rights

 




At first, you install the DB2 driver locally or on a VM, to create all the configuration files required for an unattended installation.
The following screenshots show some steps of the installation (Text is in German): 

 

 

After finishing a file PROD_CLIENT.RSP is generated, you need to put this file into the installation of the driver in SSIS-IR. 
In my sample the content looks like this:

PROD=CLIENT
LIC_AGREEMENT=ACCEPT
FILE=C:\Program Files\IBM\SQLLIB\
INSTALL_TYPE=CUSTOM
 
COMP=BASE_CLIENT
COMP=DOTNET_DATA_PROVIDER
COMP=SPATIAL_EXTENDER_CLIENT_SUPPORT
COMP=JDBC_SUPPORT
COMP=JDK
COMP=LDAP_EXPLOITATION
COMP=ODBC_SUPPORT
COMP=OLE_DB_SUPPORT
COMP=REPL_CLIENT
COMP=SQLJ_SUPPORT
 
 
LANG=EN
LANG=DE
 
 
INSTANCE=DB2
DB2.NAME=DB2
DEFAULT_INSTANCE=DB2
DB2.TYPE=CLIENT
DB2_EXTSECURITY=NO
DB2_USERSGROUP_NAME=DB2USERS
DB2_ADMINGROUP_NAME=DB2ADMNS
DB2_COMMON_APP_DATA_TOP_PATH=C:\ProgramData
DB2_COPY_NAME=DB2COPY1
DEFAULT_COPY=YES
DEFAULT_CLIENT_INTERFACE_COPY=YES

PROD_CLIENT.RSP needs to be uploaded together with main.cmd, which looks in my sample as follows:

The DB2-installation package is placed in the subfolder DB2.

 

Code of main.cmd:

 

@echo off

echo "start main.cmd"
time /T
echo "Installation DB2 64 Bit Treiber"

%cd%\DB2\setup.exe /u %cd%\PROD_CLIENT.rsp /l .\output.log

echo "Setup Completed"

net stop DB2MGMTSVC_DB2COPY1
md %SystemDrive%\ProgramData\IBM\DB2\DB2COPY1\DB2\SQLDBDIR
md %SystemDrive%\ProgramData\IBM\DB2\DB2COPY1\DB2\SQLNODIR
copy %cd%\DB2\SQLDBDIR\*.* %SystemDrive%\ProgramData\IBM\DB2\DB2COPY1\DB2\SQLDBDIR\
copy %cd%\DB2\SQLNODIR\*.* %SystemDrive%\ProgramData\IBM\DB2\DB2COPY1\DB2\SQLNODIR\
net start DB2MGMTSVC_DB2COPY1

echo "Configuration Completed"

In the script you also see server configuration to be imported - but this needs to be created first.
Executing db2cmd.exe in Azure SSIS-IR is not possible, since it opens a second interactive window.
The solution for this is quite simple. You just need to configure all nodes and catalogs locally. After executiong all db2 catalog and db2cmd commands the configuration is saved in SQLDBDIR and SQLNODIR.

 

These can be placed in the DB2 subfolder on Azure blob storage and can be copied by main.cmd inside the SSIS-IR.  First the DB2 management service needs to be stopped.

After starting it again, accessing DB2 from SSIS is working đŸ˜Š

Bereitstellen von IBM DB2 Treiber in der Azure Data Factory SSIS Integration Runtime

In Microsofts Azure Cloud ist die Azure Data Factory (ADF) das mittel der Wahl um größere Datenmengen zwischen Datenbanksystemen zu transferieren. Um eine Kompatibilität mit klassischen ETL-Prozessen zu sichern, gibt es die Azure Data Factory SSIS-Integration Runtime (ADF SSIS-IR).

 

Die SSIS-IR besteht aber aus einer oder mehreren virtuellen Maschinen, je nach Tarif, auf denen eine Variante von SQL Server Integration Services ausgeführt wird. Wie bei allen anderen PaaS Diensten muss man sich nicht um die Verwaltung oder das Patchen kümmern, hat damit einhergehend jedoch auch keinen Zugriff auf das Betriebssystem.

Dies kann bei der Installation von Treibern durchaus unangenehm werden.
 

Das einzig mögliche Verfahren ist das Hochladen eines Installationsskriptes sowie der Installationsdateien auf ein BlobStorage-Konto. Für eine Reihe von Treibern gibt es Beispiele auf GitHub, das vorgehen ist in Microsoft Docs gut beschrieben.

Jedoch gibt es kein Beispiel für die Installation von IBM DB2 Treibern.

 

Um diesen Treiber erfolgreich installieren zu können, werden die folgenden Komponenten ermöglicht:

  • Zugriff auf IBM DB2 Installationsmedien
  • Zugriff auf ein System mit Administrationsrechten

 

Zuerst muss der IBM DB2 Treiber lokal oder auf einer VM installiert werden, um die Konfiguration für eine unbeaufsichtigte Installation zu erzeugen.

Die folgenden Screenshots zeigen mehrere Schritte der Installation:

 

 

 

 

 

Am Ende wird eine Datei PROD_CLIENT.RSP erzeugt, diese wird auch für die SSIS-IR Installation benötigt.
Ich verwende folgenden Inhalt:

PROD=CLIENT
LIC_AGREEMENT=ACCEPT
FILE=C:\Program Files\IBM\SQLLIB\
INSTALL_TYPE=CUSTOM
 
COMP=BASE_CLIENT
COMP=DOTNET_DATA_PROVIDER
COMP=SPATIAL_EXTENDER_CLIENT_SUPPORT
COMP=JDBC_SUPPORT
COMP=JDK
COMP=LDAP_EXPLOITATION
COMP=ODBC_SUPPORT
COMP=OLE_DB_SUPPORT
COMP=REPL_CLIENT
COMP=SQLJ_SUPPORT
 
 
LANG=EN
LANG=DE
 
 
INSTANCE=DB2
DB2.NAME=DB2
DEFAULT_INSTANCE=DB2
DB2.TYPE=CLIENT
DB2_EXTSECURITY=NO
DB2_USERSGROUP_NAME=DB2USERS
DB2_ADMINGROUP_NAME=DB2ADMNS
DB2_COMMON_APP_DATA_TOP_PATH=C:\ProgramData
DB2_COPY_NAME=DB2COPY1
DEFAULT_COPY=YES
DEFAULT_CLIENT_INTERFACE_COPY=YES

Die PROD_CLIENT.RSP muss zusammen mit der main.cmd hochgeladen, welche bei mir folgenden Inhalt hat:

Das DB2-Installationspaket liegt dabei im Unterordner DB2.

 

Code der main.cmd

 

@echo off

echo "start main.cmd"
time /T
echo "Installation DB2 64 Bit Treiber"

%cd%\DB2\setup.exe /u %cd%\PROD_CLIENT.rsp /l .\output.log

echo "Setup Completed"

net stop DB2MGMTSVC_DB2COPY1
md %SystemDrive%\ProgramData\IBM\DB2\DB2COPY1\DB2\SQLDBDIR
md %SystemDrive%\ProgramData\IBM\DB2\DB2COPY1\DB2\SQLNODIR
copy %cd%\DB2\SQLDBDIR\*.* %SystemDrive%\ProgramData\IBM\DB2\DB2COPY1\DB2\SQLDBDIR\
copy %cd%\DB2\SQLNODIR\*.* %SystemDrive%\ProgramData\IBM\DB2\DB2COPY1\DB2\SQLNODIR\
net start DB2MGMTSVC_DB2COPY1

echo "Configuration Completed"

Der zweite Teil des Skriptes Dienst des Imports der Server-Konfiguration - diese muss jedoch erst erzeugt werden.
Das Aufrufen der db2cmd.exe aus dem Skript heraus ist jedoch nicht möglich, da ein ein zweites Interaktives Fenster geöffnet wird.
Die Lösung hierfür ist dennoch denkbar einfach. Es müssten einfach alle Knoten und Kataloge lokal registriert werden. Nach dem Ausführen aller db2 catalog und db2cmd Befehle wird die Konfiguration in den Ordnern SQLDBDIR und SQLNODIR abgelegt.

 

 

Diese können in den DB2 Unterordner abgelegt werden und per Copy-Befehl in der SSIS-IR abgelegt werden, zuvor muss jedoch der DB2 Management Dienst gestoppt werden.
Nach einem Start des Dienstes ist nun das Verbinden von SSIS-Paketen beim Ausführen innerhalb der SSIS-IR möglich
😊

PASS Regionalgruppe in Bremen/Oldenburg

In Bremen haben sich in den letzten Jahren erfolgreich eine SharePoint und eine Agile Usergroup etabliert – nun soll auch eine SQL Server Usergroup dazu bekommen.
Die Gruppe wird von mir und Julio Cerezo organisiert.

Zusammen mit dem Verein PASS werden vorerst drei Treffen organisiert:

Mittwoch, 08.04. bei der Firma HEC (Bremen-Überseestadt)
Dienstag, 19.05.
Mittwoch, 10.06.

Am 08.04. haben wir Christoph Seck aus der PASS Gruppe Hannover/Göttingen zu Gast und wird einen Vortrag zum Thema Dimension Security in OLAP und Tabular halten.

Wer noch Interesse hat zu kommen, kann sich gerne anmelden.

Kerberos und SharePoint: Anleitung für die Trennung von Datenbank und Frontend mit Reporting Services und Performance Point Services

Vor LÀngerer Zeit habe ich diesen Beitrag im PTS Group Blog veröffentlicht. Passend zu meinem Vortrag morgen, möchte ich diesen Artikel nun auch in meinem Blog veröffentlichen.

In einem frĂŒheren Beitrag wurde im PTS Blog bereits ĂŒber die Installation eines SharePoint 2010 Foundation Servers  berichtet. Wer sich fĂŒr fĂŒr einen SharePoint 2010 Enterprise Server entscheidet, tut dies meistens um einen oder mehrere der zahlreichen dort enthaltenen Dienste zu nutzen. Wird die Installation etwas grĂ¶ĂŸer und eine Trennung der Dienste erforderlich, kann dem Administrator bei der Konfiguration der Sicherheit doch schon mal schnell der Kopf rauchen, denn Kerberos hat es in sich.

Das Szenario

Im Folgenden beschreibe ich wie die Netzwerksicherheit mit Kerberos in einer Umgebung einzurichten ist, in der folgende Server und Dienste existieren:

Server 1: SQL Server
- Datenbankdienste mit SQL Server Agent
- Analysis Services mit SQL Server Browser Dienst

Server 2: SharePoint Server
- Reporting Services im SharePoint integrierten Modus
- SharePoint 2010 Server
- Performance Point Services aktiviert
- Excel Services aktiviert

Wird entweder der SharePoint Server oder der SQL Server - oder gar Beides – nicht richtig fĂŒr die Verteilung konfiguriert, wird die Verbindung zwischen den Systemen nicht immer einwandfrei funktionieren. Im “normalen” SharePoint Alltag, d.h. dem Umgang mit Listen, Verwalten von Dateien, ListeneintrĂ€gen, etc. fĂ€llt dies zunĂ€chst nicht auf. Wird jedoch eine Datenquelle in Reporting Services angelegt und versucht auf den SQL Server zuzugreifen, erhĂ€lt man diese Meldung:

rsErrorOpeningConnection: An existing connection was forcibly closed by the remote host.
image
Hat man im SharePoint sogar schon eine Webpart Seite angelegt und versucht dort den Bericht aufzurufen gibt es die gleiche Meldung, direkt von ASP.Net (links) oder anders von Performance Point (rechts).
imageimage

Schuld daran sind jedoch nicht fehlende Berechtigungen des Benutzers auf die Datenbank, oder eine Fehlende Netzwerkverbindung, sondern die Authentifizierung im Netzwerk. Damit diese auch mitmacht muss Kerberos konfiguriert werden.

Über Kerberos

Kerberos ist ein Authentifizierungsprotokoll, dass es erlaubt sich im Windows-Netzwerk ĂŒber mehrere Server hinweg an Diensten zu authentifizieren – und das auch noch vollkommen sicher. Damit dies korrekt funktioniert, muss Ordnung im Netzwerk herrschen, denn standardmĂ€ĂŸig vertrauen sich Server nicht gegenseitig und verweigern die Kommunikation wenn es um Kerberos geht. Um dies zu Ă€ndern, muss im Active Directory fĂŒr jeden Server und jedes Dienstkonto ein Secure Principal Name (SPN) hinterlegt werden, und dann wiederrum ist es möglich einzustellen wem dieser Dienst vertraut (bzw. an wen er Kerberos delegieren darf).

Das klingt ja erst mal gar nicht so schwer, oder?
In der Praxis steckt der Teufel bei Kerberos im Detail, denn der kleinste Fehler fĂŒhrt dazu, dass nichts funktioniert.
Ebenso fĂŒhrt die Verwendung von Kerberos eine BeschrĂ€nkung ein: Je Hostname darf kĂŒnftig nur noch ein Dienst verwendet werden. D.h. laufen auf Ihrem SharePoint Server z.B. Reporting Services im integrierten Modus und der SharePoint Dienst, erreicht der Nutzer beides ĂŒber die Webadresse der SiteCollection. Wenn jetzt aber beide Dienste verschiedene Dienstkonten haben, mĂŒssten beide Dienstkonsten auf die Adresse der SiteCollection einen SPN haben – und das geht leider nicht.

Schauen wir uns einfach mal, wie Kerberos im oben genannten Szenario fĂŒr Reporting Services konfiguriert werden muss

Kerberos Konfiguration: Datenbankserver

Als erstes mĂŒssen die Dienste fĂŒr ihren SQL Server SPNs bekommen. Dazu mĂŒssen Sie wissen, unter welchem Dienstkonto die Dienste laufen. Unter welchem Benutzer die Dienste laufen verrĂ€t einem der SQL Server Configuration Manager, wie hier:
image
Wichtig: FĂŒr den Zugriff ĂŒber Kerberos darf der SQL Server nicht unter dem lokalen Systemkonto oder dem Netzwerkdienst laufen. Es muss schon ein Active Directory Benutzerkonto sein.

Angenommen, sowohl der Datenbankdienst, als auch Analysis Services, laufen unter einem Dienstkonto mit dem Namen sqlsrv, können Sie wie folgt feststellen, ob das Dienstkonto bereits ĂŒber SPNs verfĂŒgt:

  • Über die Eingabe des folgenden Befehls in die Konsole:
    setspn –l DOMAIN\sqlsrv
  • Aufrufen des in der Verwaltungskonsole fĂŒr Benutzer und Computer und prĂŒfen, ob das Tab Delegierung vorhanden ist
    image

Ist alles bereits richtig konfiguriert, mĂŒssten folgende SPNs fĂŒr dem Benutzer sqlsrv hinterlegt sein, unter der Annahme, dass der Server MeinSQLServer heißt und in der DomĂ€ne dev.local Mitglied ist:

MSSQLSvc/MeinSQLServer:1433
MSSQLSvc/MeinSQLServer.dev.local:1433
MSOLAPDisco.3/MeinSQLServer
MSOLAPSvc.3/MeinSQLServer.dev.local
MSOLAPSvc.3/MeinSQLServer
MSOLAPDisco.3/MeinSQLServer.dev.local
MSOLAPSvc.3/MeinSQLServer.dev.local:1433
MSOLAPSvc.3/MeinSQLServer:1433

Fehlen diese Werte, lassens ich diese ĂŒber den Kommandozeilenbefehl SetSPN –U –A setzen, also:

setspn –U –a MSSQLSvc/MeinSQLServer:1433 DEV\sqlsrv
setspn –U –a MSSQLSvc/MeinSQLServer.domain.local:1433 DEV\sqlsrv
setspn –U –a MSOLAPDisco.3/MeinSQLServer DEV\sqlsrv
setspn –U –a MSOLAPSvc.3/MeinSQLServer.domain.local DEV\sqlsrv
setspn –U –a MSOLAPSvc.3/MeinSQLServer DEV\sqlsrv
setspn –U –a MSOLAPDisco.3/MeinSQLServer.domain.local DEV\sqlsrv
setspn –U –a MSOLAPSvc.3/MeinSQLServer.domain.local:1433 DEV\sqlsrv
setspn –U –a MSOLAPSvc.3/MeinSQLServer:1433 DEV\sqlsrv

Das wĂ€r es fĂŒr den Datenbankserver. Gehen wir nun zum SharePoint Server ĂŒber

Kerberos Konfiguration: SharePoint Server

Bevor wir, wie beim SQL Server, die SPNs registrieren, mĂŒssen eine ganze Reihe von Voraussetzungen im SharePoint und in Reporting Services geschaffen werden. Als erstes muss sichergestellt werden, dass die Anwendungspools und der Reportserver mit dem selben Dienstkonto betrieben werden. Da in unserem Szenario Reporting Services und SharePoint auf dem selben Server liegen, wĂŒrde eine andere Konfiguration sonst zu doppelten, und somit widersprĂŒchlichen, SPNs fĂŒhren.

Den Benutzer des Reporting Services Dienstes erfahren Sie, wie oben, ĂŒber den SQL Server Configuration Manager. Die Dienstkonten der SharePoint Dienste mĂŒssen Sie in der Zentraladministration unter folgendem Pfad ĂŒberprĂŒfen:
Central Administration > Security > Configure Service Accounts.
Stellen Sie hier sicher, dass die Dienste und die Web Applikation ĂŒber das selbe Konto, wie Reporting Services verwenden.
image

Fehlt das Benutzerkonto, klicken Sie unten auf "Register new managed account”

Damit wĂ€re eine der Voraussetzungen erfĂŒllt, es geht jedoch noch weiter.

Claims to Windows Token Service

FĂŒr Reporting Services reicht die aktuelle Einstellung bereits aus. Excel Services und Performance Point Services brauchen zur Weitergabe von Kerberos Tokens allerdings auch den Claims to Windows Token Service (C2WTS). Dieser Dienst kann basierend vertrauenswĂŒrdigen Anfragen, Sicherheitstokens generieren – somit kann der Dienst fĂŒr Operationen die IdentitĂ€t des Benutzers annehmen und mit seinen Berechtigungen arbeiten.
Damit es gelingt, muss dieser Dienst seinen Zielen, wo das Token hingeschickt wird, aber auch vertrauen können. Das wird mittels SPNs und Delegierungen festgelegt.

Stellen Sie als erstes sicher, dass dieser Dienst unter einem DomĂ€nenbenutzerkonto arbeitet. Dies kann auch ĂŒber die SharePoint-Maske Configure Service Accounts (siehe oben) erledigt werden. Nun sind da jedoch ein paar Berechtigungen mehr, die dieser Benutzer erfordert. Diese lassen sich in der Verwaltung der Systemsteuerung ĂŒber die Lokale Sicherheitsrichtlinie einstellen:

  • Er muss Mitglied der lokalen Administratorengruppe sein
  • Er muss in der Lokalen Sicherheitsrichtlinie folgende Rechte haben:
    • Einsetzen als Teil des Betriebssystems (Act as part of the operating system)
    • Als Dienst anmelden (Logon as a service)
    • Annehmen der ClientidentitĂ€t nach Authentifizierung (Impersonate a client after authorization)

image

Soweit, so gut, jetzt sind die meisten HĂŒrden geschafft und es kann an das Erstellen der SPNs und Delegierungen

SharePoint SPNs und Delegierungen

Angenommen, der Claims to Windows Token Service lĂ€uft unter einem Benutzer DEV\SPC2WTS, können wir folgenden SPN anlegen. Der Text SP/C2WTS kann beliebig sein, da auf den Dienst von außen nicht zugegriffen wird. Der SPN wird nur fĂŒr die Delegierung benötigt.

SetSPN –U –A SP/C2WTS DEV\SPC2WTS

Nun die SPNs fĂŒr das Konto des Anwendungspools:

SetSPN –U –A http/MeinSharePointServer DEV\SPXAPD
SetSPN –U –A http/intranet DEV\SPXAPD
SetSPN –U –A http/intranet.domain.local DEV\SPXAPD

Und jetzt kommt der wichtigste Teil, damit die Kerberos Tickets auch weitereicht werden dĂŒrfen: Die Delegierung.
Öffnen Sie dazu auf Ihrem DomĂ€nencontroller die Eigenschaften des Benutzers SPC2WTS, dort wird das Tab Delegierung angezeigt. Dort mĂŒssen “Benutzer bei Delegierungen angegebener Dienste vertrauen”, “Beliebiges Authentifizierungsprotokoll verwenden” aktiviert sein, dann können Sie auf “HinzufĂŒgen
” klicken.
Es öffnet sich ein neues Fenster, dort gibt es den Button “Benutzer oder Computer”, dort suchen Sie jetzt nach ihrem SharePoint Anwendungspool Benutzer, wenn alles richtig gelaufen ist, erhalten sie dann folgende Anzeigen:

imageimage

Nun vertrat der User DEV\SPC2WTS dem Benutzer DEV\SPAPD im Kontext des Protokolls http auf dem Server MeinSharePointServer. D.h. SPC2WTS delegiert Kerberos Tokens an http/MeinSharePointServer fĂŒr den Benutzer SPAPD.
Das wiederholen Sie nun fĂŒr den Datenbankserver.

Der Benutzer SPAPD muss nÀmlich das Token weiter delegieren können an den Dienst MSSQLSvc, MSSQLOlapDisco.3 und MSSQLOlapSvc.3 auf dem Datenbankserver.

Weitere Links und Literatur

Wenn Sie diese Einstellungen vorgenommen, sollten Sie Performance Point und Excel Services mit einer erfolgreichen Verbindung belohnen. Ist dies nicht der Fall, prĂŒfen Sie noch die allgemeine Konfiguration dieser Dienste, dazu empfehle ich die folgende Literatur und Links:

Buch: SharePoint 2010 Performance Point Services unleashed
MSDN: Configure Performance Point Services
MSDN: Übersicht ĂŒber den Claims to Windows Token Service

SSDs vs. SAS Festplatten – Ein Performancevergleich

Beim Betreiben eines SQL Servers spielt die Hardware eine entscheidende Rolle fĂŒr die Performance des Systems. Microsoft bietet zusammen mit Hardware-Partnern zwar Fast-Track-Server an, in vielen FĂ€llen ist es jedoch nicht möglich eine solche Konfiguration einzusetzen.

Wer sich seinen Server selbst zusammenstellt wird heutzutage immer wieder die Frage gestellt, ob es sich lohnt SSDs einzusetzen oder ob ein Àlteres System durch den Einsatz von SSDs beschleunigt werden kann. In diesem Performancevergleich habe ich SSDs gegen SAS Platten antreten lassen.

Das Setup

Zum Einsatz kam schon ein Ă€lterer Server: HP ProLiant DL 380 G5 mit einem SmartArray P400 Controller. Dieser Controller unterstĂŒtzt nur einen maximalen Durchsatz von 300 MB/s, weshalb in der Messung auch nicht der maximale Durchsatz der SSD erreicht wurde. Das spielt bei SQL Server Workloads jedoch meist nur eine untergeordnete Rolle.

Ansonsten:
Arbeitsspeicher: 14 GB
SAS Controller Cache: 512 MB
Erster Plattenstapel: 2x146GB 10k SAS Platten im RAID-1
Zweiter Plattenstapel: 2x72GB 10k SAS Platten im RAID-1
Dritter Plattenstapel: 2x500 GB Samsung EVO 840 im RAID-1
SQL Server 2014 Enterprise mit CU4
Windows Server 2012 R2 Standard

Gemessen habe ich mit dem SQLIO-Tool die IOs/sek, Datendurchsatz in MB/sek sowie durchschnittliche Zugriffszeit verschiedener Laststufen.

Messergebnisse

IOs/sek

image image

Die SSDs zeichnen sich im Bereich der IOs besonders bei zufĂ€lligen Lesezugriffen mit teilweise 10x höherer Performance gegenĂŒber den Festplatten aus (8k Zugriffe). Dies verwundert nicht, denn Festplatten mĂŒssen bei zufĂ€lligen Zugriffen immer eine Umdrehung abwarten, bevor Daten gelesen werden können. Mit wachsender GrĂ¶ĂŸe der gelesenen / geschriebenen Datenmenge schmilzt der Vorteil dahin. Bei 256 KB ist der Performancegewinn noch im Faktor 1,6 bzw. 2,2 gegenĂŒber den 146GB bzw. 72GB SAS Festplatten.

Dass die Unterschiede im Bereich des Schreibens nicht so groß ausfallen ist auf die Verwendung des Cache Moduls im RAID Controller zurĂŒck zu fĂŒhren.

MB/sek

imageimage

Bei der Betrachtung der Transferraten wird weiter deutlich, dass die SSD ihre StĂ€rken bei zufĂ€lligen Lesen und Schreiben ausspielen kann. Beim zufĂ€lligen Lesen erreichen die SSDs sogar die 300MB Grenze des Controllers, sodass davon ausgegangen werden kann, dass bei Verwendung eines neueren Controllers noch höhere Werte erzielt wĂŒrden. Die hier beobachteten Unterschiede fallen jedoch nicht so stark aus, wie unter beim Vergleich der IOs pro Sekunde.

Durchschnittliche Zugriffszeit in Millisekunden

imageimage

Der VollstĂ€ndigkeit halber fĂŒge ich auch noch die Zugriffszeit in Millisekunden ein, auch wenn diese Werte fĂŒr den Betrieb eines SQL-Servers nicht so relevant sind. Auch hier ist – wenig Überraschend – die SSD mit deutlich kĂŒrzeren Zugriffszeiten vertreten. Im Bereich der 8 KB großen Pakete war die Zugriffszeit so gering, dass sie nicht ermittelt werden konnte.

Betrachtung

Die Messergebnisse lassen bereits nach einer schnellen Betrachtung folgende RĂŒckschlĂŒsse zu:

  • Die höhere Datendichte der 146 GB SAS Festplatten ermöglichen höhere Transferraten und kĂŒrzere Zugriffszeiten, insbesondere bei großen Datenblöcken, gegenĂŒber den kleineren 72 GB SAS Festplatten
  • Die SSDs haben die Leistung der Festplatten in sĂ€mtlichen Disziplinen in den Schatten gestellt. Ihre StĂ€rken haben die SSDs insbesondere im Bereich der kleinen Blöcke und bei zufĂ€lligen Lese/SchreibvorgĂ€ngen

Heißt dies nun, man sollte in jedem Fall auf SSDs setzen? Folgende weitere Eigenschaften sollte man auch noch in Betracht ziehen:

  • SSDs verfĂŒgen bei intensiver Nutzung ĂŒber eine deutlich KĂŒrzere Lebensdauer, als Festplatten. Das hĂ€ufigere Austauschen von Laufwerken verursacht Kosten
  • Ebenfalls sind SSDs in der Anschaffung teurer, als Festplatten
    Die hier im Test verwendeten SSDs sind zwar relativ gĂŒnstig, jedoch auch nur auf Belastungen in handelsĂŒblichen PCs und Laptops ausgelegt. Preise fĂŒr Enterprise SSDs liegen nochmal deutlich höher

Es stellt sich darĂŒber hinaus auch die Frage, welche Art Workload ihr SQL Server verarbeiten muss. Wird eine OLTP Anwendung mit vielen kleinen Schreib- und Lesezugriffen betrieben oder handelt es sich um eine Datawarehouse-Lösung?

Im Bereich der OLTP-Anwendungen werden eher kleinere Datenblöcke gelesen und geschrieben, weshalb die Performancesteigerung sehr hoch sein dĂŒrfte. OLTP Anwendungen sind aber auch besonders auf DatenintegritĂ€t angewiesen. Verlorene DatensĂ€tze in der Buchhaltung oder einem Warenwirtschaftssystem können ein ganzes Unternehmen ruinieren.
Daher mĂŒsste bei einem Umstieg auf SSDs in jedem Fall, sofern noch nicht vorhanden, eine Backup-Lösung eingesetzt werden, welche einfaches Page-Restoring ermöglicht und möglichst viele Sicherungen am Tag durchfĂŒhrt.

In Datewarehouse-Umgebungen sind Lese- und SchreibvorgĂ€nge eher sequentiell, da oft große Datenmengen am StĂŒck gelesen werden. Der Performancegewinn einer SSD ist somit nicht so signifikant, wie bei einer OLTP Anwendung, und rechtfertig die zusĂ€tzlichen Kosten wahrscheinlich nicht.

Anders sieht dies wiederrum fĂŒr die Cube-Aufbereitung von Analysis Services aus. Die Analysis Services legen Dimensionen, Attribute und Measuregruppen in vielen einzelnen Dateien ab, wodurch auch viele kleine Zugriffe auf das Dateisystem erfolgen. Bei sehr großen Analysis Services Datenbanken kann der Einsatz einer SSD die Aufbereitungszeiten signifikant verkĂŒrzen.

Fazit

GegenĂŒber 10k SAS-Festplatten haben selbst handelsĂŒbliche SSDs deutlich messbare Performancevorteile.

Je nach Einsatzszenario rechtfertigt dies allein jedoch nicht den Einsatz von SSDs, da die höheren Kosten und kĂŒrzere Austauschintervalle ebenfalls eine Rolle spielen.

Interessant wĂ€re es sicher auch diesen Vergleich fĂŒr 15k SAS Platten durchzufĂŒhren, da diese nochmal ĂŒber deutlich kĂŒrzere Zugriffszeiten verfĂŒgen, in der Anschaffung aber auch deutlich teurer, als 10k Festplatten, sind.

WinInet Fehler bei FTP Zugriff in Visual Studio 2012 + 2013 beheben

In Visual Studio gibt es bereits seit vielen Versionen die Möglichkeit FTP-Webseiten direkt zu bearbeiten. Doch seit Visual Studio 2012 gibt es einen Bug, welcher diese Funktion stören kann.

In meinem Szenario habe ich einen Windows Server 2012 R2 Webserver mit IIS sowie aktivierten FTP nur auf Port 21. Die Verbindung muss daher im aktiven Modus betrieben werden.

In FileZilla lÀsst sich die Verbindung problemlos herstellen, so jedoch nicht in Visual Studio. Dort erscheint diese Fehlermeldung:

FTPVisualStudio1

Des RĂ€tsels Lösung ist eine fehlende Firewall-Regel. In der Windows Firewall muss eine neue eingehende Regel fĂŒr Visual Studio erstellt werden. Dazu ist eine Regel vom Typ Programm fĂŒr devenv.exe zu erstellen.

FTPVisualStudio2FTPVisualStudio3

Die Änderungen werden sofort aktiv, danach glĂŒckt gleich der nĂ€chste Versuch.

Das Verhalten habe ich bei Microsoft Connect gemeldet.

Windows 8.1 Update 1: UMTS/LTE HotSpot erstellen

Vor kurzem hat Microsoft das Windows 8.1 Update 1 veröffentlicht, im Rampenlicht stehen neben der offensichtlichsten Neuerung Modern Style Apps in der Taskbar nun Nutzen zu können auch diverse Kleinigkeiten, welche die Bediendung der Modern-Welt mit der Maus deutlich erleichtern.

Mein persönliches Highlight habe ich gestern Abend beim Zug fahren entdeckt:
Im Charms-Bereich fĂŒr Netzwerke gibt es nun eine Option, um Verbindungeinstellungen anzuzeigen.
image
In der nun folgenden Anzeige muss die Modemverbindung ausgewĂ€hlt werden, das ist in meinem Beispiel die Mobile Breitbandverbindung ĂŒber das Telekom-Netzwerk
image

Jetzt muss bloß noch der Schieberegler auf Ein gesetzt werden und es wird eine WLAN Verbindung erstellt (Voraussetzung ist, dass WLAN aktiviert ist), mit der sich weitere GerĂ€te verbinden können. Das Passwort lĂ€sst sich natĂŒrlich beliebig Ă€ndern.

image

In diesem Sinne:  viel Spaß beim Surfen. Smiley

Folien zum PASS Vortrag über Extended Events in Analysis Services

Vergangene Woche habe ich am Dienstag bei der Regionalgruppe der PASS in Hamburg sowie am Freitag bei der Regionalgruppe der PASS Hannover/Göttingen einen Vortrag zu meinem letzten Artikel Ermitteln der Measurenutzung in SQL Server Analysis Services gehalten.

AngehĂ€ngt an diesen Post findet ihr die Folien zum Download ebenso wie die Code-Beispiele. Das SSIS Paket ist nun auch verbessert, sodass es fĂŒr jede Abfrage nun mehrere Zeilen mit allen Measures ausgibt (Danke an Sascha fĂŒr den Hinweis).

Ebenso möchte ich mich noch fĂŒr den Tipp bedanken, dass nun die neuen SQL Server Data Tools Business Intelligence fĂŒr Visual Studio 2012 verfĂŒgbar sind, denn der Fly-To-The-Moon-Bug ist tatsĂ€chlich behoben worden.

Zum Schluss noch der Link zum Buch SQL Server Internals von Kalen Delaney, das mir als Wertvolle Quelle fĂŒr die AblĂ€ufe im SQL Server dient: http://www.amazon.de/gp/product/0735658560/ref=as_li_ss_tl?ie=UTF8&camp=1638&creative=19454&creativeASIN=0735658560&linkCode=as2&tag=irgenddasvisu-21 

Download Folien & Code: PASS_XE_SSAS.zip (1,3MB)

Ermitteln der Measurenutzung in SQL Server Analysis Services

Business Intelligence Projekte erzeugen in der Regel einen oder mehrere Cubes. Jeder kennt die Problematik, dass es fĂŒr den internen oder externen Kunden sehr schwierig ist seine Anforderungen derart zu abstrahieren, dass sich Measures im Vorfeld genau definieren lassen. Manchmal versteht der Kunde sogar erst wĂ€hrend des Projektes, wie die Technologie wirklich funktioniert.
Die Folge: Anforderungen werden ĂŒber den Haufen geworfen und das System wird an diversen stellen erweitert – sowohl noch im Projekt, als auch spĂ€ter durch die Wartung. Über die Jahre hinweg werden die Strukturen des Cubes immer komplexer. Doch wie ermittelt man, welche wirklich noch gebraucht werden?

Der Klassiker: OLAPQueryLog

In Analysis Services ist ein entsprechendes Werkzeug theoretisch bereits eingebaut. Bei der Entdeckung des QueryLogs mögen sicher schon viele Jubelschreie von sich gegeben haben, denn das QueryLog ermöglicht es Anfragen an die Analysis Services aufzuzeichnen. Beim Ausprobieren zeigt sich jedoch, dass neben dem Namen der AS-Datenbank, der verwendeten Measuregruppe nur noch ein kryptisches Dataset mit aufgezeichnet wird.

image_thumb

EnthĂ€lt die Measuregruppe mehr als ein Measure, was nicht gerade unĂŒblich ist, ist diese Information unscharf und somit nur begrenzt hilfreich.

Ein Versuch: SQL Server Profiler

Etwas detaillierte Daten lassen sich ermitteln, indem man den SQL Server Profiler laufen lĂ€sst. Der Profiler klinkt sich direkt in den SQL Server ein und zeichnet die gewĂ€hlten Ereignisse mit allen Details auf. Beim Erstellen des Traces ist darauf zu Achten, dass alle Ereignisse, außer “Query End” abgewĂ€hlt werden. Ebenso ist als Ziel eine Tabelle anzugeben, damit die Daten spĂ€ter auch ausgewertet werden können.
Zum Profiler muss man jedoch immer sagen, dass der Einsatz Auswirkungen auf die Performance des Systems hat. In Produktivumgebungen sollte man daher immer prĂŒfen, ob es nicht noch einen anderen Weg gibt.

image_thumb4image_thumb5

Als Ergebnis stehen in nun in der Tabelle einige Spalten, interessant ist hier die Spalte TextData, die enthÀlt die komplette MDX Abfrage und somit auch alle benutzten Measures!
Jetzt fehlt nur noch ein Weg aus der Abfrage eine Liste der Measures zu erzeugen.

Um dies zu lösen habe ich ein SSIS Paket geschrieben und mit dem folgenden Skript Task eine Liste von Measures aus dem Text extrahiert. KernstĂŒck ist der regulĂ€re Ausdruck, ĂŒber den die Abfrage aufgeteilt wird. Das Ergebnis ist eine Komma-separierte Liste mit den verwendeten Measures.

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

    Dim _strMdx As String

    If (Row.TextDataString_IsNull = False) AndAlso Row.TextDataString.IndexOf("SELECT") > -1  
     Then

        _strMdx = Row.TextDataString.Substring(Row.TextDataString.IndexOf("SELECT"))

 

        Dim _strEscaped As String

        Dim _strMatch As MatchCollection

        Dim _strLine As String

        Dim usedMeasures As String = String.Empty


        _strEscaped = Regex.Escape(_strMdx)

        _strMatch = Regex.Matches(_strEscaped, "\[Measures.*?(,|\)|\})")

 

        For Each _line As Match In _strMatch

            _strLine = _line.ToString.Substring(0, _line.ToString.Length - 1).Trim()

            usedMeasures &= Regex.Unescape(_strLine) & ","

        Next

 

        If usedMeasures.Length > 1 Then

            Row.Measures = usedMeasures.Substring(0, usedMeasures.Length - 1)

        Else

            Row.Measures = String.Empty

        End If

    Else

        Row.Measures = String.Empty

    End If

End Sub


 

In meinem Beispiel wird die Liste erst in eine temporĂ€re Tabelle geschrieben und dann Schlussendlich werden die einzelnen Measures in die Zieltabelle ĂŒberfĂŒhrt.

image16_thumb
Dies ist die temporÀre Tabelle

image_thumb11
Und dies die Zieltabelle

Eine einfache Abfrage zeigt nun welches Measure wie oft genutzt wurde:
SELECT [Measure],

      [DatabaseName],

      COUNT(*)

  FROM [log_olap_trace_measures]

  GROUP BY [Measure],

      [DatabaseName]

image24_thumb

 

Neue Welten: Extended Events

Seit SQL Server 2012 hat Microsoft den SQL um Extended Events erweitert. Im Gegensatz zum Profiler lÀuft bei Extended Events nicht permanent ein Trace mit, sondern nur bei bestimmten ausgewÀhlten Ereignissen wird etwas protokolliert. Dies erfolgt mit einem nur recht geringen Overhead und kann daher auch auf einem Produktivsystem ohne Probleme eingesetzt werden.

FĂŒr die Datenbankengine ist es möglich Extended Event-Sitzungen direkt im Objekt Explorer des Management Studios anzulegen:
image_thumb15

Bei Analysis Services mĂŒssen Extended Events per XMLA definiert werden.
Ein sehr guter Artikel findet sich dazu im Blog von Bill Anton.
Um unser Profiler-Beispiel mit Extended Events nachzubauen kann das XMLA Skript aus Bills Post verwendet werden. Hier ist dann die Zeile mit dem Event “QueryEnd” auszukommentieren und ein gĂŒltiger Dateipfad muss angegeben werden. Das Schreiben in eine Tabelle ist von den Extended Events aus leider nicht möglich.

GlĂŒcklicherweise kann die xel Datei per T-SQL ausgelesen werden. Jedoch gibt es auch hier eine weitere HĂŒrde, die fĂŒr uns relevante MDX-Abfrage verbirgt sich in der letzten XML Spalte, diese enthĂ€lt das Element “TextData”. Das Schreiben eines XML-Parsers oder das Laden dieser Daten in ein XML Objekt ĂŒber Integretation Services ist zum GlĂŒck nicht nötig. Das Zauberwort heißt hier XQuery!
Zugegeben, XQuery ist sehr gewöhnungsbedĂŒftig und ich tue mich mit der Syntax etwas schwer, die MĂŒhe wird aber am Ende belohnt. Nach etwas Experimentieren habe ich folgende Abfrage erstellt:
SELECT  *,
       CAST(event_data AS XML).query('(/event/data[@name="TextData"]/value)[1]') AS 'event_data_XML'
FROM   sys.fn_xe_file_target_read_file(
                    'D:\Trace\OlapTrace_0_130334860612250000.xel'
                    ,NULL
                    ,NULL
                    ,NULL
             )

Die Spalte event_data_XML enthĂ€lt nun die MDX-Abfrage, eingekapselt in <value></value>-Tags. Das braucht uns bei der weiteren Verwendung fĂŒr den vorhandenen ETL-Prozess nicht zu stören, da der regulĂ€re Ausdruck diesen Abschnitt ignoriert.
image_thumb2

Und nun wĂŒnsche ich viel Freude beim Analysieren der Nutzung Ihres Cubes!

Weitere Informationen

Artikel im Blog der PTS Group AG
Blog von Bill Anton
MSDN: Query
MSDN: Extended Events

SSAS_MeasureTracking.zip (24,00 kb)