SQL Server & BI Blog by Andreas Wolter

Liebe Leser
dieser Blog ist hiermit nur noch „Archiv“ und wird nicht mehr weiter gepflegt.
Seit August 2017 finden sich neue Artikel ausschließlich unter der neuen URL:
http://andreas-wolter.com/blog/

Dear Readers
this blog is now merely an „archive“ and no longer being updated.
Since August 2017 new articles are exclusively available under the new URL:
http://andreas-wolter.com/en/blog/

Die aufwändige Mehrsprachigkeit (Deutsch und Englisch professionell manuell übersetzt) wird beibehalten – aber Layout-technisch anders gelöst. Damit dürfte ich immer noch den einzigen mehrsprachigen IT-Blog weltweit betreiben.
Ich hoffe, das neue Design gefällt Ihnen.

The complex multilingualism (German and English professionally manually translated) is being continued – but solved differently in terms of layout. With that I most likely still operate the only multilingual IT-Blog worldwide.
I hope you like the new design.

Mein aktueller Artikel, der erstmalig ausschließlich auf der neuen Website zu finden ist, lautet: Optimieren von Workflows mit In-Memory und nativ kompilierten Objekten - oder wie es nicht funktioniert

My currently last article, which is exclusively available at the new website for the first time, is Optimizing workflows with In-Memory and Natively Compiled Objects - or how it does not work

(EN)
It is no secret that I have been very active in the Asian region the last years – mainly in Singapore, Malaysia and India. So, it may not be a surprise that this summer I will be giving a full day PreCon at the now called “Data Platform Summit” in India’s hub for information technology: Bangalore, aka the Silicon Valley of India.

I am personally very honored and happy to be asked again to present this PreCon - especially after last year’s drop out due to “Delhi Belly” – which only sounds cute, but was zero fun at all, I can assure my fellow readers.

The Indian community has embraced me with very warm welcoming from the very first year of “SQL Server Geeks Summit” on (as it was then called), and I know this event will be a joy as before for sure.

(DE)

Es ist längst kein Geheimnis, dass ich in den letzten Jahren sehr aktiv im asiatischen Raum war – hauptsächlich in Singapur, Malaysia und Indien. So wird es wohl auch nicht überraschen, dass ich diesen Sommer einen kompletten Tag PreCon auf der inzwischen in „Data Platform Summit“ umbenannten Konferenz in Indiens Zentrum für Informationstechnologie geben werde: Bangalore, auch bekannt als das Silikon Valley von Indien.

Ich fühle mich sehr geehrt und bin glücklich, dass ich wieder gebeten wurde, diese PreCon zu präsentieren – besonders nach meinem Ausfall im letzten Jahr aufgrund von „Delhi Belly“ – was zwar niedlich klingt, aber keinerlei Spaß machte, kann ich meinen Lesern versichern.

Die indische Community hat mich seit dem ersten Jahr des „SQL Server Geeks Summit“ (wie es damals noch hieß) mit einem warmen Empfang willkommen geheißen, und ich weiß, dass auch dieses Event wieder viel Freude machen wird.

And what can my audience look forward to?

A full day of diving into the latest trend in IT Technology, specifically data storage: In-Memory optimization (storage and computation). If you are still thinking traditional row-store indexes, it is time to level up. Here is your chance for a very low price to learn from the first steps unto the pitfalls of reality:

Und worauf kann sich mein Publikum diesmal freuen?

Auf einen ganzen Tag, an dem in die neuesten Trends in der IT-Technologie eingetaucht wird, spezifisch in Sachen Datenspeicherung: In-Memory-Optimierung (Speicherung und Berechnung). Wenn ihr noch traditionell in Row-basierten Indexen denkt, ist es Zeit, aufzuleveln. Hier ist eure Chance, zu einem sehr günstigen Preis von den ersten Schritten bis zu den Fallstricken der Realität zu lernen:

“In-Memory Technologies in SQL Server – From 0 to Operational Analytics Master”

The abstract goes as follows:

“In-Memory Technologies im SQL-Server – Von 0 zum Operational Analytics Master”

Die Beschreibung lautet wie folgt:

Since SQL Server 2016 Service Pack 1, most programming features have been available in all editions, including the two In-Memory technologies: Columnstore Indexes and In-Memory OLTP.

Columnstore indexes, which have been existing since SQL Server 2012 (actually PDW v2008), are mainly optimized for big amounts of data (millions of rows) and offer blazingly fast OLAP-style queries, which is made possible by their special structure (columnar storage), sophisticated compression, and batch-mode processing for much more efficient CPU-usage than traditional row-store-queries.

The In-Memory OLTP engine, which will be the second topic of this full day, came into the product with SQL Server 2014 and has since then been extensively improved in terms of both scalability and T-SQL language support, taking away many of the relevant limitations for adaption of version 1 in a similar way as the Columnstore technology over the course of its development.

Thirdly, the so-called In-Memory Operational Analytics are supported by the possibility to create Columnstore Indexes on memory optimized tables!

All those improvements will make In-Memory technologies a viable option in many projects. For Datawarehouses, many say that Columnstore will become the default storage type for all objects in the near future. And it can be foreseen that over the years the same will happen for OLTP-tables that have to support highly concurrent workloads, which will all be based on memory optimized tables.

It’s time to extend your skills to embrace those technologies, and learn how to implement and support those new types of storage that are coming to our databases, addressing the challenge of ever more data being stored and queried and performance demands and (real time) analytic requirements going up.

During this full-day training session, Microsoft Certified Master for the Data Platform Andreas Wolter, familiar with SQL Server’s In-Memory technologies from the early bits on, will give a complete picture on the current state of technology. Attendees will learn how and where to use either In-Memory OLTP or Columnstore or even both for efficient queries and data storing, but also which problems still exist in real-world projects that sometimes make it hard to find the right solution design to profit from those technologies, and cover the important bits and pieces both from a developer’s and administrator’s perspective.

Seit SQL Server 2016 Service Pack 1 sind die meisten Programmierfunktionen in allen Editionen verfügbar, einschließlich der zwei In-Memory-Technologien: Columnstore Indexe und In-Memory OLTP.

Columnstore Indexe, die es seit SQL Server 2012 (eigentlich PDW v2008) gibt, sind hauptsächlich für große Datenmengen (Millionen von Zeilen) optimiert und bieten blitzschnelle Abfragen im OLAP-Stil, was durch ihre spezielle Struktur (spaltenweise Speicherung), raffinierte Komprimierung und Bearbeitung im Batch-Mode für eine weitaus effizientere CPU-Auslastung als traditionelle Row-basierende Abfragen ermöglicht wird. 

Die In-Memory OLTP-Maschine, die das zweite Thema dieser Ganztags-PreCon sein wird, kam mit SQL Server 2014 in das Produkt und wurde seitdem umfassend sowohl hinsichtlich Skalierbarkeit als auch T-SQL-Sprachunterstützung verbessert, wobei viele der relevanten Einschränkungen bei der Anpassung von Version 1 auf ähnliche Weise wegfielen wie bei der Columnstore-Technologie im Verlauf ihrer Entwicklung.

Drittens werden die sogenannten In-Memory Operational Analytics durch die Möglichkeit unterstützt, Columnstore Indexe auf Memory-optimierten Tabellen zu erstellen!

All diese Verbesserungen werden In-Memory-Technologien zu einer praktikablen Option bei vielen Projekten machen. Was Data-Warehouses anbetrifft, sagen viele, dass Columnstore in der nahen Zukunft zur Standardspeicherart für alle Objekte werden wird. Und es lässt sich vorhersehen, dass mit den Jahren dasselbe für OLTP-Tabellen eintreten wird, die hohe gleichzeitige Workloads unterstützen müssen, die dann alle auf Memory-optimierten Tabellen basiert sein werden.

Es ist Zeit, deine Fähigkeiten zu erweitern und dir diese Technologien zu eigen zu machen: zu lernen, wie man diese neuen Speichertypen, die in unsere Datenbanken kommen, implementiert und unterstützt, und sich der Herausforderung immer mehr gespeicherter und abgefragter Daten sowie Performance-Anforderungen und steigenden analytischen Anforderungen (in Echtzeit) zu stellen.

Während dieses ganztägigen Trainings wird Microsoft Certified Master for the Data Platform Andreas Wolter, der mit den In-Memory-Technologien von SQL-Server von den frühen Anfängen an vertraut ist, einen kompletten Überblick über den derzeitigen Technologiestand geben. Teilnehmer werden lernen, wie und wo man entweder In-Memory OLTP oder Columnstore oder sogar beides für effiziente Queries und Datenspeicherung verwendet. Sie werden auch lernen, welche Probleme noch in realen Projekten existieren, die es manchmal erschweren, das richtige Lösungskonzept zu finden, um von diesen Technologien zu profitieren. Dabei werden auch wichtige Aspekte und Details sowohl aus der Perspektive des Entwicklers als auch der des Administrators behandelt.

Modules/Topics

• Columnstore Storage Engine and compression internals
• What is the benefit for OLAP performance
• When to use Clustered or Nonclustered Columnstore Indexes
• XTP Engine internals for In-Memory OLTP performance benefits
• Memory optimized Tables, Indexes and Variables
• Natively compiled stored procedures & triggers
• Combination of Row-Store, Columnstore/xVelocity and XTP engine for operational analytics

Module/Themen

• Columnstore Storage Engine und Komprimierungs-Interna
• Was ist der Nutzen für OLAP- Performance
• Wann man Clustered oder Nonclustered Columnstore Indexe benutzt
• XTP-Engine Interna für In-Memory OLTP Performance-Vorteile
• Memory-optimierte Tabellen, Indexe und Variablen
• Nativ kompilierte gespeicherte Prozeduren & Trigger
• Kombination von Zeilenspeicherung, Columnstore/xVelocity und XTP Engine für operative Analytik

Key Takeaways

• How the new storage engines Columnstore & XTP work behind the covers
• What are the strengths and weaknesses of these alternate storage engines and how can they be played out best
• How to get a quick start with In-Memory optimized objects in almost any environment
• What are the typical performance patterns that these technologies address
• How to build highly performing Datawarehouse tables
• How to improve OLTP hotspot tables with In-Memory technologies
• How to enable real-time analytics of operational data
• What’s important from a file management perspective for administrators
• How to maintain Columnstore and In-Memory Hash- & Range-indexes
• What challenges arise from those technologies

Kernpunkte

• Wie die neuen Speicher-Engines Columnstore & XTP hinter der Bühne funktionieren
• Was die Stärken und Schwächen dieser alternativen Speicher-Engines sind und wie sie am besten umgesetzt werden können
• Wie man einen schnellen Start mit In-Memory-optimierten Objekten in fast jeder Umgebung erhalten kann
• Was die typischen Performance-Muster sind, die diese Technologien behandeln
• Wie man hoch performante Data Warehouse-Tabellen baut
• Wie man OLTP Hotspot Tabellen mit In-Memory Technologien verbessert
• Wie man Echtzeit-Analytik von operativen Daten ermöglicht
• Was aus einer Dateimanagement-Perspektive für Administratoren wichtig ist
• Wie man Columnstore und In-Memory Hash- & Range-Indexe wartet
• Welche Herausforderungen sich aus diesen Technologien ergeben

Demos

• Performance Improvements for OLAP workloads with Nonclustered Columnstore indexes
• Clustered Columnstore indexes
• Performance Improvements for OLTP workloads with memory optimized tables, indexes and code
• Operational analytics on row store vs. operational analytics on In-Memory under different workload types
• How Columnstore indexes handle updates to data under the cover
• How In-Memory optimized objects look like on disk

 Demos

• Performance-Verbesserungen für OLAP-Workloads mit Nonclustered Columnstore Indexen
• Clustered Columnstore Indexe
• Performance-Verbesserungen für OLTP-Workloads mit memory-optimized Tabellen, Indexen und Code
• Operative Analytik bei Zeilen-Speicherung vs. operative Analytik bei In-Memory unter verschiedenen Workload-Typen
• Wie Columnstore-Indexe mit Updates bei Daten unter der Haube umgehen
• Wie In-Memory optimierte Objekte auf der Disk aussehen

Sign-up here as long as seats are available! http://dataplatformgeeks.com/dps2017/pre-conference-seminars/

Hier anmelden, solange es noch freie Plätze gibt!

http://dataplatformgeeks.com/dps2017/pre-conference-seminars/

After the PreCon I will give 2 more sessions at DPS 2017:

Troubleshooting Availability Groups
and
SQL Server Security for Developers

Nach der PreCon werde ich 2 weitere Sessions bei der DPS 2017 geben:

Troubleshooting Verfügbarkeitsgruppen und
SQL Server Sicherheit für Entwickler

Also, I have been asked by KD, the founder of KDSSUG (Knowledged Dedicated SQL Server User Group) to present at their Event, “KDSSG MSSQL Tech Unite 2017” on August 20. Session topic TBD.

Außerdem wurde ich von KD, dem Gründer von KDSSUG (Knowledged Dedicated SQL Server User Group) gebeten, auf ihrem Event „KDSSG MSSQL Tech Unite 2017“ am 20. August zu sprechen. Das Thema wird noch festgelegt.

If Singapore is easier for you:

The weekend after, August 26 at SQLSaturday Singapore I will have a session on the new SQL Server 2017 which is due sometime this year: SQL Server 2017: Better HA & DR

Falls Singapur für euch einfacher sein sollte:

Am Wochenende darauf, am 26. August, werde ich auf der SQLSaturday Singapore eine Session zum neuen SQL-Server 2017, der irgendwann dieses Jahr erscheint, halten: SQL Server 2017: Better HA & DR

Next stops after that: SQLSaturday Denmark in Copenhagen October 7 with another full day PreCon Oct. 6th: “Practical Performance Monitoring & Troubleshooting”. Save the date and register soon as my previous events on that subject have quickly filled up.

Die nächsten Stationen, die folgen werden: SQLSaturday Denmark in Kopenhagen am 7. Oktober mit einer weiteren Ganztags-PreCon am 6. Okt.: “Practical Performance Monitoring & Troubleshooting”. Merkt euch den Termin vor und meldet euch bald an, da meinen vorangegangenen Events zu diesem Thema schnell voll waren. 

(EN)
This time, we are dealing with the „MDW“, short for Management Data Warehouse,(msdn.microsoft.com/en-us/library/bb677306.aspx), which I like to recommend as a minimal performance logging-approach.

From time to time, and most recently in the context of my PASS Essential „SQL Server Analysis tools & Techniques for Performance und general Monitoring“, the question arises as to whether the MDW operates together with the High Availability technologies Database Mirroring and AlwaysOn Availability Groups, and if so, how so.

(DE)
Diesmal geht es um das Management Data Warehouse, kurz „MDW“ (msdn.microsoft.com/de-de/library/bb677306.aspx), welches ich gerne als minimalen Performance-Protokollierungs-Ansatz empfehle.

Hin und wieder, zuletzt im Zusammenhang mit meinem PASS Essential „SQL Server Analysetools & Techniken für Performance und allg. Monitoring“ kommt die Frage auf, ob das MDW mit den Hochverfügbarkeitstechnologien Datenbankspiegelung und AlwaysOn Hochverfügbarkeitsgruppen zusammenspielt, und wenn, wie.

The short answer is: Yes, it does.
The MDW operates both with Database Mirroring as well as with AlwaysOn Availability Groups.

The following graph illustrates a possible setup using the latter:

Die kurze Antwort lautet: Ja.
Das MDW funktioniert sowohl mit Datenbankspiegelung als auch mit den AlwaysOn Hochverfügbarkeitsgruppen.

Das folgende Schaubild zeigt ein mögliches Setup unter Verwendung der letzteren Variante:

The server (0) holding the MDW database is located outside of the high availability nodes. The databases to be monitored are located in the AlwaysOn Availability Groups in the servers 1-3.
Keeping the MDW highly available is not the objective. It is simply about being able to see the performance data of all databases, no matter in which server they are active at the moment.

Der Server (0), der die MDW-Datenbank vorhält, liegt außerhalb der Hochverfügbarkeitsknoten. Die zu überwachenden Datenbanken liegen in AlwaysOn Hochverfügbarkeitsgruppen auf den Servern 1-3.
Das MDW hochverfügbar zu halten ist nicht das Ziel. Es geht nur darum, die Performance-Daten aller Datenbanken einsehen zu können, gleich auf welchem Server sie gerade aktiv sind.

Part 1: Databases in secondary role

If you set up the MDW as standard you will realize that after a failover, the data of the respective databases disappear from the “Disc Usage” reports of the server, while these were previously still present in the primary role.

The background to this is that after a failover, the respective databases now are present in a different server in the primary role, and now are no longer readable in the secondary, in the standard setting.

In this moment, the System Data Collection Set “Disc Usage”, or the underlying job “collection_set_1_noncached_collect_and_upload” cannot collect data for this database. In contrast on the new primary node these database will now reappear as long as they are active in the primary role there. In principle, this behavior is comprehensible: The Data Collector can no longer find any information on this database and assumes that the latter is no longer relevant – as if it was deleted. One may certainly wish for a possibility of intervention here; however, the MDW is currently not flexible in this regard.

The new report “Transaction Performance Analysis Overview” which is enriched through the newly existent “Transaction Performance Collection Set” in SQL Server 2014 also displays data for no longer active databases.

Having clarified this background, the possible solution is self-evident: The databases must remain readable.

With AlwaysOn High Availability Groups, this is in principal easily done:

Part 1: Datenbanken in Secondary-Rolle

Wenn man nun das MDW standardmäßig einrichtet, wird man feststellen, dass nach einem Failover die Daten der jeweiligen Datenbanken aus den „Disk Usage“-Berichten des Servers verschwinden, wo diese bis zuvor noch in der Primary-Role vorlagen.

Hintergrund ist, dass nach einem Failover die jeweiligen Datenbanken nun auf einem anderen Server in der Primary-Role vorliegen, und auf dem nun Secondary, in der Standardeinstellung nicht lesbar sind.

In diesem Moment kann das System Data Collection Set „Disk Usage“, bzw. der dahinterliegende Job „collection_set_1_noncached_collect_and_upload“ zu dieser Datenbank keine Daten auslesen. Auf dem neuen Primary-Knoten hingegen werden diese Datenbanken nun neu erscheinen, solange sie dort in der primären Rolle aktiv sind.

Prinzipiell ist dieses Verhalten nachvollziehbar: Der Data Collector kann keine Informationen zu dieser Datenbank mehr finden und geht davon aus, dass diese nicht mehr relevant ist – als ob sie gelöscht sei. Sicherlich kann man sich hier eine Eingriffsmöglichkeit wünschen, derzeit ist das MDW aber in dieser Hinsicht nicht flexibel.
- Der neue Bericht „Transaction Performance Analysis Overview“, der über das neu im SQL Server 2014 existierende „Transaction Performance Collection Set“ angereichert wird, zeigt auch Daten für bereits nicht mehr aktive Datenbanken an.

Wenn nun dieser Hintergrund klar ist, liegt die mögliche Lösung nahe: Die Datenbanken müssen lesbar bleiben.
Mit AlwaysOn Hochverfügbarkeitsgruppen (Availability Groups) ist das prinzipiell auch leicht gemacht:

However, one needs to be aware of the fact that these databases are now released for all reading access – which should be taken into consideration in respect to application architecture, performance as well as in terms of license. Hence, for the purpose of data collection for performance evaluation alone I CANNOT recommend it.

If however the business applications are supposed to maintain reading access to the secondary point anyway, the data collector is covered with this as well.

One more advice: The setting “Read-Intent only” unfortunately does not work with the MDW since one cannot manually adapt the Connection String accordingly.
Database Mirroring does not support this option at all.

Jedoch muss man sich hierüber im Klaren sein, dass diese Datenbanken nun für sämtliche Lesezugriffe freigegeben sind, was sowohl hinsichtlich Applikationsarchitektur, Performance als auch Lizenztechnisch genau bedacht werden sollte. Allein zum Zweck der Datensammlung zu Performance-Auswertung kann ich das also NICHT empfehlen.
Wenn die Geschäftsanwendungen aber ohnehin Lesezugriffe auf den Sekundärknoten erhalten sollen, dann ist damit der Datensammler ebenfalls abgedeckt.
Noch ein Hinweis: Die Einstellung „Read-Intent only“ funktioniert mit dem MDW bisher leider nicht, da man den Connection String nicht entsprechend manuell anpassen kann.
Datenbankspiegelung unterstützt diese Option gar nicht.

Part 2: Configuration of the MDW-Clients

Since the databases run on a different node after a failover, the MDW reports must be set up in all servers in which the Availability Group is running. Here, one needs to ensure that access to the central MDW-Server is possible from all servers.

To do this (before SQL Server 2014) the SQL Server Agent Account of the client-instance must be included in the mdw_writer role on the MDW-Server (mdw_admin is not necessary) when configuring the MDW through the “Configure Management Data Warehouse Wizard:

Part 2: Konfiguration der MDW-Clients

Da die Datenbanken nach einem Failover auf einem anderen Knoten laufen, müssen die MDW-Berichte auf allen Servern, auf denen die Availability Group läuft, eingerichtet werden.

Dabei muss sichergestellt werden, dass von allen Servern auf den zentralen MDW-Server zugegriffen werden kann.

Dazu muss (vor SQL Server 2014) bei der Konfiguration des MDW über den „Configure Management Data Warehouse Wizard“ der SQL Server Agent Account der Client-Instanz auf dem MDW-Server in die mdw_writer-Rolle aufgenommen werden (mdw_admin ist nicht notwendig):

From SQL Server 2014, at the configuration of the data collection in the client, it is possible to provide a SQL Server Agent Proxy of the type “Operating System (CmdExec)” as account for the access to the central MDW-Server:

Ab SQL Server 2014 kann man bei der Konfiguration der Data Collection auf dem Client einen SQL Server Agent Proxy vom Typ „Operating System (CmdExec)“ als Konto für den Zugriff auf den zentralen MDW-Server hinterlegen:

In this case, it is of course required to authorize the underlying Windows account in the server, instead of the agent itself, as „mdw-writer“.

In diesem Fall muss auf dem Server natürlich der dahinterstehende Windows-Account anstelle des Agents selber als „mdw_writer“ berechtigt werden.

As soon as all clients are authorized accordingly, one can read the data of all SQL Server AG nodes in the central management server. Depending on which server a database is currently present in in the primary role, it will then appear in the according subreport.
- This proceeding also works with mirrored databases in Database Mirroring scenarios – there one can only have one “partner instance” though.

Sobald alle Clients entsprechend berechtigt sind, kann man auf dem zentralen Management Server die Daten aller SQL Server Knoten der AG auslesen. Je nachdem auf welchem Server eine Datenbank gerade in der Primary-Rolle vorliegt, erscheint diese dann in dem entsprechenden Subreport.
- Diese Vorgehensweise funktioniert auch mit gespiegelten Datenbanken bei Datenbankspiegelungs-Szenarien – dort gibt es jedoch maximal eine „Partner-Instanz“.