Bernd Jungbluth: Seminar - SQL Server Reporting Services

Es gibt einen neuen Termin für das Seminar SQL Server Reporting Services:

30. September 2014 im Hotel Best Western Premier Bellevue Rheinhotel in Boppard am Rhein

Mit den SQL Server Reporting Services bietet Microsoft eine zentrale Informationsplattform für Unternehmen, die aktuelle und applikationsübergreifende Informationen in interaktiven Berichten zur Verfügung stellt.

Die Bereitstellung dieser zentralen Informationsplattform ist Inhalt dieses Seminars.

Es wird das gesamte Spektrum eines Berichts betrachtet – von der Erstellung über die Verwaltung bis hin zur Bereitstellung. Dabei liegt der Fokus auf der Berichtserstellung mit dem Berichtsdesigner.

Nach diesem Seminar sind Sie in der Lage
- Reporting Services als zentrale Informationsstelle zu nutzen
- Dynamische Berichte zu erstellen
- Berichte und Berichtsserver zu verwalten
- Berichte im Berichtsmanager bereitzustellen
- Berichte zu abonnieren

Die Teilnehmerzahl ist auf 8 Personen begrenzt

Weitere Informationen zum Seminar gibt es unter www.berndjungbluth.de/ssrs.pdf.

Torsten Schuessler: #CU package 1 for SQL Server 2012 Service Pack 2

The Cumulative update package 1 for Microsoft SQL Server 2012 Service Pack 2 (Build 11.0.5532
) is available:

Cumulative Update 1 for SQL Server 2012 SP2

I wish you a nice day,
tosc

Torsten Schuessler

Marcel Franke: Impressions from the SQL Saturday #313

Also this year we had another great SQL Saturday here in Germany. I want to take the chance to say thank you to the organization team, to all the speakers and the volunteers for the big effort and time they spent to make this happen. From my opinion it was again a great success. We used the same location as last year, at the Campus in Augustin, and I personally like the combination of a university campus and a technology event like this. This year we also had more participants than last year and the team was able to deliver very good sessions in 5 parallel tracks (http://sqlsaturday.com/313/schedule.aspx).

I also had the chance to give some of my time to the SQL community and had a session about the different In-Memory technologies in SAP HANA, Power Pivot and SQL Server. If you are interested in my slides, I share them via Slideshare.

 

If you have any feedback or questions don’t hesitate to reach out. Finally I also want to share some impressions from the conference with you. Thank you to our photograph Dirk, who put together some more photos on OneDrive: VCSL3p

SQLSaturday313_Rheinland_449SQLSaturday313_Rheinland_267SQLSaturday313_Rheinland_416SQLSaturday313_Rheinland_423


Filed under: Conferences

Marcel Franke: A call to all Data Engineers and BI workers

In the last two years I had the chance to get my hands on some very exciting Data Analytics projects and I wanted to take the chance to recap and to reach out to Data Engineers and BI Consultants. Why?

In the area of IT we see lot’s of trends coming up every year. Some are going, some are staying, but sometimes we see also a paradigm shift. These shifts have a tremendous impact on the way we worked before and how we will work in the future, for example the rise of the Internet, the area of Business Intelligence and Data Warehouse and the whole E-Commerce shift. And now we can see new shift coming up:

The era of Big Data

The difficult thing with a paradigm shift is we need to rethink certain ideas, the way we did business before and we will do in the future. And if we don’t do it, others will do it and we will not be as successful as we have been in the past.  So let me get to that story in more detail.

Big Data vs. Data Analytics

image

Big Data is now out there for a while and people already understand that storing large amount of data is not good enough. There was a big hype about it and we are now at a point that the words “Big Data” already got a negative touch. It’s very exciting to see the big progress in new technologies like Hadoop, where customers can store nearly all their data. But in 90% of the cases it is totally useless to throw all your data into a data analysis problem. Just talking about technologies does not meet the needs of users and customers anymore.

I also don’t like to talk about Big Data, because it’s misleading, instead I’d like to talk about Data Analytics and that’s what it’s all about. So the focus is clearly on analyzing data and creating value out of it. This is also not big news, but we were told that only a specific type of people with a specific knowledge can do this: Data Scientists.

These guys are currently seen as heroes in the analytics market and everybody is looking for someone with little or no luck. So here’s my point: Analyzing data is not a new venture, in the area of Business Intelligence and Data Mining people did this all the time for years. But what has changed and where does the big shift happens?

We can clearly say that we can’t get around Data Analytics anymore. If you talk with customers and you just want to talk about Data Warehouses and BI you are missing half of the discussion. All the companies I talk to clearly think about Big Data or Data Analytics and how they can combine it with their Data Warehouse and BI solutions. But technology has become secondary in these discussions. Don’t get me wrong, Data Warehouses are still necessary and in use but the focus clearly has changed. We see new types of data that are interesting to analyze like streaming, social, logs, sensor data and there are also new ways to analyze data like pattern recognition, predictions, clustering, recommendations, etc. So the operational data that is typically stored in Data Warehouses is still necessary, but it has to be combined with the other types of data, I mentioned before. But in today’s discussions with customer, it’s all about use cases and solutions.

And in order to close the loop let me quickly come back to the Data Scientists. I agree that we need statistical and mathematical skills to solve problems like customer segmentation, next best offers and recommendations, predictions, data correlations etc. but we need much more skills to provide whole solutions to customers, so a good team mix is much more important.

New skills and approaches

With the era of Big Data and the new analytical possibilities we can also see new solution approaches. Data Analytic projects are much more iterative and evolutionary because research on your data is a big part of your work. Companies discover new use cases and sometimes they change their whole business model, because they find competitive advantages or new possibilities for revenue.

google acquisition of nest, thermostat, ProtectA good example for this are Smart Homes. We can see that the digitalization is now arriving at our homes. In the near future devices in our home are fully connected with each other and share data between each other. When I set my  weak up alarm for the next morning, an app will tell this to my heating system. My heating system then knows when I want to take a shower and need warm water or when I want to drive with my electric car.

Energy providers are highly interested in this information and in my daily behavior of energy consumption. Why?

Because when they better understand my energy consumption, they can better predict their energy sales and also how much energy is consumed at a certain time. And when they better predict the energy consumption of their customers, they can better handle their purchase of power energy at the energy exchange market.

The challenge with these new business models, and there are plenty of others, is that they are new. And for energy companies that have offered power supply in a very classical way for decades, this is a big change. So that’s why also technology providers like Google enter the market. They know how to handle the data, how to analyze it and how to use it for business models to provide additional services. Should you not accept these changes in business models, even when they take some time before they settle on the market, you wake up, when it is too late. Because applying changes need some time and companies need the experience in order to apply these changes step by step

And I think this is the most important learning in the last years. You can stick with you old business models if they work, but if an industry is changing you need to adapt. And Data Analytics happens in several industries and the most successful companies are those, that start small, get their experiences very quickly and are able to adopt the changes. There are very good examples in Germany like the Otto Group in Retail, Yello Strom in the Energy sector and also some new Startups.

As I mentioned before Data Analytic projects need to be very iterative in their approach. A lot of projects start with an idea or a use case or a feeling and we need to quickly understand if there is a business case behind it or not. In order to support those projects we need a different approach, which I call “Laboratory and Factory”.

image

The Laboratory

The Laboratory is for experiments. Here we can test all our use cases, ideas or just discover patterns in data. The important thing is, it must be cheap. We don’t want to spend much money on experiments, if we don’t know the business case behind them yet. The work can be compared to „panning for gold“. There is plenty of gold to be found, but for every gold nugget a multiple of sand needs to be panned. So from a technology perspective I would use whatever fits to the use case. In the laboratory we should be more flexible on different technologies like SQL, Hadoop, Storm, Pig, R or Python, D3 or other tools which help solve our problems.

From a data perspective we can work on a subset of the data. What we probably want to avoid is data sampling, which is often times very time consuming, so we prefer real data first. So the main goal of the laboratory is to…

image_thumb14

The Factory

After we proved our business cases in the laboratory we can than apply our data analytic models to the factory. The factory means that we operate these models on a daily base and couple them to our business processes. Here we typically use the existing enterprise platforms and we often see mixed solutions of classical data analytics platforms combined with Open Source technologies. A new requirement in the last years is that we want to apply our analytical models to the whole data history. Technologies and servers are now capable to make this possible. So our factory gives us the integration of our analytical business models to our daily business at enterprise scale, on the whole data set and probably enriched with external data.

New technologies

image_thumb16

Some month ago I had the chance to visit the European Hadoop conference in Amsterdam and it was a great chance to get another view on Big Data and Data Analytics from an Open Source perspective. It has become very obvious that Hadoop and NoSQL based technologies drive the Big Data and Analytics market. There is a whole industry behind companies like Cloudera, Hortonworks, MapR and others that push new innovations. The Stinger initiative for example was a team project of around 45 companies with 140 developers that improved the performance of the Hive technology by a factor 100 within 1 year. Imagine the power of innovation that these companies like Google, Yahoo, Facebook, Hortonworks and also Microsoft can bring to these technologies when they combine the skills. Clearly when you come from a traditional BI solution like SQL Server, Teradata, Oracle or SAP you would say that there are still some gaps in the usability and ease of use. But on the other side these technologies are built just for Big Data solutions. They offer fantastic capabilities and some of them are great technologies, also if it is sometimes harder to work with them.

And  when you see that all big players in the market like IBM, Oracle, Teradata, Microsoft and SAP have partnerships with Hadoop platform providers, then it is very clear, that there is no way around these technologies anymore. It is just a question how to combine them best. Microsoft for example has a nice offering with the Analytical platform system (APS), which is a scale-out box where you can mix Hadoop and SQL Server in a highly parallel and very high performing way.

Summary

I personally believe in the new paradigm shift of Big Data and Data Analytics. I already had  the chance to enjoy several projects in that area and I’m very happy to start on new ones in the next weeks. But that is also the reason why I wanted to write this article. In order to stay competitive we need to accept changes in the market and start to deal with them. What does that mean?

We have to keep learning new technologies, different approaches, new business models, etc. Old traditional BI projects will be also done in the future but the really interesting and challenging projects will all deal with Data Analytics. There are lots of really fascinating use cases, which due to non-disclosure agreements I can’t talk in more detail about them. But what I can say is that a little bit of ETL, SQL and building a Data Warehouse and some reports is not good enough anymore. Technologies these days can do much more and customers are starting to understand this. The demand and the expectation is increasing especially for analytical models in combination with business process optimizations. So the time is very exciting and I can encourage everybody to get started and if you don’t know where, let me know…

 

References:

http://hadoopilluminated.com/hadoop_illuminated/Hardware_Software.html

http://www.myandroiddaily.com/2014/01/why-you-should-care-about-googles-new.html

http://www.devlounge.net/friday-focus/031111-in-the-lab

http://datasciencebe.com/datalab/

http://picturethepursuitofhappiness.blogsport.de/2009/07/19/neugier/

http://blogs.microsoft.com/blog/2014/04/15/a-data-culture-for-everyone/


Filed under: Big Data, Data Analytics, Data Science

Uwe Ricken: Verwendung von Variablen statt Literalen

Im Forum eines von mir sehr geschätzten MVP-Kollegen wurde eine Frage bezüglich der Verwendung von Variablen anstelle von Literalen gestellt (hier). Das Problem war, dass die Abfrage sich deutlich verlangsamte, wenn Variablen statt Literale verwendet wurden. Warum dieses Verhalten für Microsoft SQL Server jedoch korrekt ist, soll der folgende Artikel zeigen.

Ausgangsfrage

Ich grübele über einem Select, der wesentlich langsamer wird, wenn Variablen als Parameter verwendet werden. Setze ich feste Werte als Parameter, ist der Select ca. 3mal schneller. Die Problematik bewegt sich leider nicht im 10tel-Sekunden-Bereich...

Testszenario

Für den Test wird aus der Tabelle [person].[Person] der AdventureWorks2012 Datenbank  ein paar Attribute des Datenbestandes in eine neue Tabelle [dbo].[Person] kopiert und neu indiziert.
SELECT  BusinessEntityID        AS    Id,
        PersonType,
        FirstName,
        LastName
INTO    dbo.Person
FROM    person.person;
GO
 
-- Eintragen eines dedizierten neuen PersonType
INSERT INTO dbo.Person (Id, PersonType, FirstName, LastName)
VALUES (30000, 'UR', 'Uwe', 'Ricken');
GO
 
-- Clustered Index auf Id
CREATE UNIQUE CLUSTERED INDEX ix_Person_Id ON dbo.Person (Id);
GO
 
-- Index auf PersonType
CREATE INDEX ix_Person_PersonType ON dbo.Person (PersonType);
GO
Um das Problem noch etwas zu verschärfen, wurde ein neuer Datensatz hinzugefügt, dessen [PersonType] ein Alleinstellungsmerkmal besitzt; dabei handelt es sich um meiner Einer…

Nachdem die Tabelle mit allen Indexen erstellt wurde, werden zwei Abfragen mit der gleichen Ergebnismenge ausgeführt:



-- Anzeige von IO aktivieren
SET STATISTICS IO ON;
GO
 
-- Demoabfrage 1
SELECT * FROM dbo.Person WHERE PersonType = N'UR';
GO
 
-- Demoabfrage 2
DECLARE @PersonType NCHAR(2) = N'UR'
SELECT * FROM dbo.Person WHERE PersonType = @PersonType;
GO
 
-- Anzeige von IO deaktivieren
SET STATISTICS IO OFF;
GO

Obwohl beide Abfragen – scheinbar – identisch sind, zeigt ein erster Blick auf das IO, dass Abfrage 2 deutlich mehr IO produzieren muss als Abfrage 1; beide Abfragen verwenden unterschiedliche Ausführungspläne: ExecutionPlan_01


Die prozentuale Verteilung wird durch das generierte IO untermauert:



-- Abfrage 1
Person-Tabelle. Scananzahl 1, logische Lesevorgänge 4, physische Lesevorgänge 0...
 
-- Abfrage 2:
Person-Tabelle. Scananzahl 1, logische Lesevorgänge 116, physische Lesevorgänge 0...

Wird für die erste Abfrage von einem Kostenverhältnis von 6% bei der Ausführung ausgegangen, so fallen die restlichen 94% der Ausführungskosten auf die 2. Abfrage (das sind vom Query Optimizer geschätzte Kosten!). Abfrage 1 verwendet den Index [ix_Person_PersonType] für einen INDEX SEEK während Abfrage 2 einen – deutlich teureren – INDEX SCAN unter Verwendung des Clustered Index verwendet. Das Geheimnis dieses Verhaltens liegt – wie meistens – in den Statistiken. Bevor die Statistiken näher untersucht werden, solle ein Blick auf die Eigenschaften der Ausführungsoperatoren geworfen werden.


ExecutionProperties_01 Die erste Abbildung zeigt die Eigenschaften des INDEX SEEK Operators der ersten Abfrage. Man kann erkennen, dass der Operator den Index [ix_Person_PersonType] verwendet. Um den [SEEK]-Vorgang durchführen zu können, wird das Literal N’UR von Microsoft SQL Server parametrisiert (wegen Wiederverwendbarkeit des Ausführungsplans) und der Indexschlüssel [PersonType] durchsucht. Der wichtigste Punkt für die Ursache des genannten Problems ist die Information über die [geschätzte Anzahl von Zeilen] und die [tatsächliche Anzahl von Zeilen]. Microsoft SQL Server überprüft vor Beginn der Ausführung der Abfrage zunächst die Statistiken und erhält als Information, dass der Wert “UR” im Index nur 1 Mal vorkommt. Basierend auf diesen Informationen entscheidet sich Microsoft SQL Server für einen Index Seek, da das zu erwartende IO deutlich geringer ist als bei einem Index Scan! Ausgehend von einem zu erwartenden Datensatz wird die Abfrage ausgeführt und tatsächlich wird auch nur ein Datensatz an den Client geliefert. Das Verhältnis zwischen geschätzten Datensätzen und tatsächlichen Datensätzen ist in Ordnung. Vollkommen anders jedoch sieht es bei der zweiten Abfragen aus.


 


 

ExecutionProperties_02

Obwohl die zweite Abfrage auch nur einen Datensatz liefert, geht Microsoft SQL Server von vollständig anderen Voraussetzungen bei der Ausführung der Abfrage aus. In der nebenstehenden Abbildung ist zu erkennen, dass Microsoft SQL Server nicht – wie zuvor – von einem Datensatz ausgeht sondern von 2.853,29 Datensätzen. Diese Schätzung liegt im Verhältnis zum Ergebnis vollkommen daneben und aus diesem Grund entscheidet sich der Query Optimizer von Microsoft SQL Server für einen Index Scan. Die Frage, die sich in diesem Zusammenhang stellt, ist natürlich, WARUM weichen die geschätzten Zahlen so weit voneinander ab? Im direkten Zusammenhang steht auf jeden Fall die Verwendung von Parametern statt Literale. Wird ein Literal verwendet, so kann der Query Optimizer diesen Umstand sofort in die Berechnung eines möglichen Ausführungsplans einbeziehen. Bei der Verwendung einer Variablen kann der Query Optimizer von Microsoft SQL Server den möglichen Wert dieser Variablen nicht einbeziehen – Microsoft SQL Server muss also “schätzen”, wie viele Datensätze durch die Abfrage unter Verwendung einer Variablen zurückgeliefert werden. Wie genau Microsoft SQL Server intern dabei vorgeht, zeigt die nachfolgende Erläuterung



Statistiken – der Schlüssel für optimale Abfragen

Wie bereits oben erwähnt, benötigt der Query Optimizer von Microsoft SQL Server Statistiken, um einen effizienten Ausführungsplan zu erstellen. Wie wichtig aktuelle Statistiken für Microsoft SQL Server sind, habe ich bereits im Artikel “Bedeutung von aktuellen Statistiken für Indexe” beschrieben. Für das vorliegende Problem sind die Statistiken des Index [ix_Person_PersonType] interessant und werden mit dem folgenden Befehl ausgegeben:

DBCC SHOW_STATISTICS('dbo.Person', 'ix_Person_PersonType');

DBCC_SHOW_STATISTICS_01

Die Abbildung zeigt alle Informationen zur Statistik des Index [ix_Person_PersonType]. Insgesamt werden durch die Ausführung des Befehls drei Informationsbereiche ausgegeben:

  • Statistik-Information (Header):
  • Density Vektor
  • Histogramm

Verwendung von Literalen

Wenn eine Abfrage mit einem Literal ausgeführt wird, verwendet der Query Optimizer von Microsoft SQL Server das Histogramm. Bezugnehmend auf die erste Abfrage sucht der Query Optimizer im Histogramm in [RANGE_HI_KEY] – immer sortiert - nach dem Literal “UR” und – sofern dieser Eintrag vorhanden ist – kann im Attribut [EQ_ROWS] die Anzahl der zu erwartenden Datensätze ausgelesen werden. Ist der Wert “UR” nicht zu finden, verwendet Microsoft SQL Server [AVG_RANGE_ROWS] als Wert. Aus der obigen Abbildung geht hervor, dass für den Literal “UR” 1 Datensatz zu erwarten ist – somit wendet der Query Optimizer einen INDEX-SEEK als Ausführungsoperator an.

Verwendung von Variablen

Das Verhalten des Query Optimizer ändert sich jedoch, wenn Variablen ins Spiel kommen. Da Variablen in der Kompilierungsphase nicht vom Query Optimizer verwendet werden können, muss die Berechnung anders erfolgen – durch Verwendung des DENSITY Vektors. Der DENSITY Vektor gibt die Dichte eines Wertes im Verhältnis zur Gesamtmenge aller eindeutigen Datensätze an. Die Formel für die Ermittlung dieses Wertes lautet 1 / Anzahl eindeutiger Werte. Insgesamt gibt es 7 unterschiedliche Werte im Schlüsselattribut des Index bei insgesamt 19.973 Datensätzen. 1 / 7 = 0,1428571 ! Diese “Dichte” verwendet der Query Optimizer, wenn eine – zur Laufzeit der Abfrage – unbekannte Variable verwendet wird. 19.973 * 0,1428571 = 2.853,2848583 Microsoft SQL Server geht – basierend auf der Dichte – davon aus (geschätzt), dass 2.853 Datensätze durch die Abfrage geliefert werden. Diese hohe Anzahl von Datensätzen würde – auf Grund des Key Lookup – dazu führen, dass ein deutlich höheres IO produziert würde; ein INDEX SCAN produziert ein deutlich geringeres IO!

Frage – Antwort – Fehler


Bei der Beantwortung der gestellten Frage wurden – leider – falsche Antworten gegeben, die den Sachverhalt nicht wirklich berücksichtigt haben. Warum die Antworten verkehrt sind, möchte ich kurz erläutern:


... weil es auf jeden Fall noch langsamer wäre, weil nicht auf einen gespeicherten Ausführungsplan zurückgegriffen werden kann.


Nein, das ist nicht richtig. Abfragepläne werden – generell - nur dann nicht gespeichert, wenn die Option RECOMPILE für die Abfrage verwendet wird. Ansonsten wird – bis auf ganz wenige Ausnahmen – IIMMER ein Ausführungsplan gespeichert; sonst wäre obige Auswertung nicht möglich gewesen.


Ansonsten sollten weder der Datentyp noch der Umstand, dass Parameter verwendet werden, einen spürbaren Unterschied bei der Ausführung machen.


Nein, das ist auf jeden Fall falsch wie das oben beschriebene Verfahren belegt.


Wir haben über Nacht die Ausführungspläne aktualisieren lassen, das hat am Ende das Problem gelöst.


Ganz bestimmt nicht! Oder anders ausgedrückt: Nicht das oben beschriebene Problem war die Ursache für die langsame Ausführung sondern “Parameter Sniffing”. Mehr Informationen zu Parameter Sniffing habe ich in diesem Artikel beschrieben “Tücken bei der Verwendung von sp_executeSQL”. Würde tatsächlich ein extremes Ungleichgewicht in den Indexschlüsseln vorhanden sein und die Tabelle sehr groß sein, kann das von mir beschriebene Verhalten sicherlich auf die Problembeschreibung angewendet werden.



Herzlichen Dank fürs Lesen!


Bernd Jungbluth: Seminar - Migration Access nach SQL Server

Es gibt einen neuen Termin für das Seminar Migration Access nach SQL Server:

23. September 2014 im Hotel Best Western Premier Bellevue Rheinhotel in Boppard am Rhein

In diesem Seminar liegt der Fokus auf dem Wort „nach“.
Eine Migration nach SQL Server bedeutet nicht nur die Migration der Access-Tabellen nach SQL Server, sondern vielmehr auch die Migration der Access-Abfragen und VBA-Module zu Gespeicherten Prozeduren und Sichten. Das Ergebnis ist eine zukunftssichere und schnelle Client/Server-Applikation.

Nach diesem Seminar sind Sie in der Lage
- Access-Datenbanken zum SQL Server zu portieren
- Access-Abfragen zu Sichten oder Gespeicherten Prozeduren zu migrieren
- Sichten und Gespeicherte Prozeduren in T-SQL zu programmieren
- Sichten und Gespeicherte Prozeduren in Access und VBA zu verwenden

Agenda
- Migration von Access nach SQL Server
- Analyse der Access/SQL Server-Applikation mit SQL Server Profiler
- Migration der Access-Abfragen nach zu Sichten und Gespeicherten Prozeduren
- Optimierung der Access/SQL Server-Applikation

Preis
350 Euro zzgl. MwSt., inklusive Mittagessen und Getränke

Die Teilnehmerzahl ist auf 8 Personen begrenzt.
Aktuell sind noch 7 Plätze frei.

Weitere Informationen zum Seminar gibt es unter www.berndjungbluth.de/migration.pdf.

Bernd Jungbluth: SELECT * FROM [2014 FIFA World Cup Brazil]

Glückwunsch zum 4. Stern :-)

Philipp Lenz: Einführung in PowerQuery Teil 2 von ?

thIn diesem Eintrag möchte ich das Tutorial in der Einführung in PowerQuery weiterführen. Hier geht es um die Gruppierungsfunktion und das Verbinden zweier Tabellen. Hier werden wir eine Excel Datei auslesen mit 2 Tabellenblättern – eins hält die Bestellungen für das Jahr 2014 und das zweite Blatt die Informationen zu den Kunden. Ziel ist die Auswertung der Umsätze pro Kunde und pro Monat. Normalerweise würde ich das in PowerPivot lösen, aber für eine einfache Aufbereitung der Daten und deren Darstellung in diesem Kontext bietet PowerQuery eine etwas leichtere Methode – da hier gleich die Daten zum Schluss gruppiert zur Verfügung stehen und lediglich dargestellt werden müssen. Vielleicht aber auch Geschmackssache …

pq_1Als erstes wechseln wir auf das Ribbon PowerQuery und laden Daten aus einer Datei, bzw. gleich Excel.

pq_2Im nächsten Schritt befindet sich im Navigator die jeweilige Tabellen aus dem Excel Sheet. Einerseits sehen wir hier die Tabellenblätter aber auch die Tabellen die sich auf den Blättern befinden. Da sich hier jeweils eine Tabelle auf jedem Blatt befindet, wähle ich die Tabellenblätter aus. Hier setze ich gleich die Haken bei beiden, wähle aus, dass die Daten in das Datenmodell (PowerPivot) geladen werden.

Weiterhin wähle ich die erste Tabelle Bestellungen aus und klicke auf Bearbeiten.

 

 

 

 

 

pq_3

Nun muss in beiden Tabellen jeweils die erste Zeile noch als Tabellenüberschrift gekennzeichnet werden. Nachdem dies bei den Bestellungen durchgeführt wurde, klicke ich auf Anwenden und schließen und wähle die Tabelle Kunden im Navigator aus und Bearbeite diese genauso:

pq_3_4Nachdem die Kundentabelle ebenfalls die Überschriften aus der ersten Zeile entnimmt, schließe ich ebenfalls das Fenster über Anwenden und schließen und klicke im Navigator auf Laden damit beide Excel Tabellen in PowerQuery verfügbar sind.

Nun müssen beide Tabellen miteinander verbunden werden …

Dazu wähle ich aus dem Navigator die Kunden Tabelle aus und klicke auf Abfragen zusammenführen:

pq_4

 

 

Nun sind die Tabellen verbunden – technisch ist das wie ein Left Join zu sehen. Über den Haken “nur Übereinstimmende Zeilen einbeziehen” würde das einem Inner Join entsprechen.

pq_5Nun können die Spalten aus der rechten Tabellen hinzugefügt werden.

pq_6

Da ich hier lediglich die Bestellungen aus dem Jahr 2014 vor mir habe und das Ziel eine Auswertung pro Monat ist, transformiere ich die Spalte Bestelldatum über einen Rechtsklick in Monate um. Danach steht das Feld als Monatsnummer zur Verfügung.

Die Transformation kann nachträglich immer noch wieder zurückgestellt werden. An sich handelt es sich nur um eine Formatierung.

 

 

Im nächsten Schritt brauchen wir eine Gesamtsumme. In den Daten steht derzeit nur die Summe der Einzelposition und die Menge zur Verfügung. Daher fügen wir hier nun eine Benutzerdefinierte Spalte ein mit folgender Formel:pq_7

 

pq_8Nun markiere ich noch die nicht mehr benötigten Spalten und entferne diese:

 

pq_9Nun markiere ich die Spalten Name und Monat und klicke auf Gruppieren Nach – nun erfolgt die Gruppierung, bzw. Summierung auf den Gesamtpreis.

pq_10

Nach dem Klick auf Anwenden und Schließen befindet sich im PowerPivot Datenmodell die gruppierte und aufbereitete Tabelle. Diese kann ich nun in Excel darstellen….

 

Robert Panther: Bericht: SQL Saturday in Sankt Augustin

Am 28.06. veranstaltete die PASS zum dritten Mal einen SQL Saturday in Deutschland. Dabei wurde zum zweiten Mal die Hochschule Bonn-Rhein-Sieg in Sankt Augustin als Veranstaltungsort gewählt. Die gute Erreichbarkeit mit dem Auto sowie (am Wochenende) ausreichenden Parkplatzmöglichkeiten sprechen für den Veranstaltungsort. Lediglich die etwas unbequemen Hochschul-Sitzbänke hinterließen einen suboptimalen Eindruck, der durch die qualitativ hochwertigen Vorträge aber mehr als wettgemacht wurde. So fanden sich dann auch ca. 250 Teilnehmer ein, die sich die 30 Vorträge (verteilt auf 5 parallele Tracks zu den Bereichen BI, DBA, Development, Mixed und Open) anhörten. Unter den Speakern waren viele international renommierte Experten, bekannte Buchautoren und MVPs vertreten, darunter Scott Klein, Andreas Wolter, Dr. Holger Schwichtenberg, Dejan Serka, Chris Testa-O’Neill, Matija Lah, Uwe Ricken, Christoph Muthmann, Oliver Engels, Konstantin Klein (um nur einige zu nennen). Die Vorträge deckten verschiedene Themenbereiche und Niveaus ab, so dass für jeden sicherlich etwas Interessantes dabei war. Zwischen den Vorträgen gab es Gelegenheit sich bei den verschiedenen Sponsoren der Veranstaltung über deren Produkte aus dem SQL Server Umfeld zu informieren. Zum Abschluss der Veranstaltung gab es noch diverse Verlosungen der Sponsoren, so dass sich viele Teilnehmer über diverse Sachpreise (vom Buch bis hin zu Hardware) freuen konnten. Doch bereits am Vortag der Veranstaltung trafen sich bereits einige SQL-Enthusiasten an der Hochschule um sich beim Big Data Hackathon intensiv mit Microsoft BigData-Technologien wie HDInsight und PowerBI (PowerQuery, Power View, Power Map und andere) auseinanderzusetzen.

Insgesamt war dieser SQL Saturday eine runde Veranstaltung, die jedem zu empfehlen ist. Schließlich gibt es nicht oft die Möglichkeit quasi zum Nulltarif an so viele interessante Informationen zu kommen. Auf der Website zur Veranstaltung kann man sich zu einigen Vorträgen die PowerPoint-Slides ansehen: http://www.sqlsaturday.com/313/eventhome.aspx

Beim SQL Saturday handelt es sich um eine Veranstaltungsreihe, die von der Professional Association for SQL Server (kurz PASS), weltweit durchgeführt wird. Das Besondere an dieser Veranstaltung ist, dass keinerlei Teilnahmegebühr erhoben wird, die Veranstaltung also komplett durch Sponsoren finanziert wird. Dafür, dass die Teilnehmer einen Samstag Freizeit opfern, bekommen diese hochkarätige Vorträge zu allen möglichen Themen rund um SQL Server von ausgewiesenen Experten zu hören (darunter viele MVPs).


Sascha Lorenz: SQL Server Usergroup Emsland in Lingen – PowerShell 101 für SQL Admins

Am 24.07.14 werde ich die neue SQL Server Usergroup in Lingen im Emsland mit einem Vortrag unterstützen. Die Gruppe wird von William Durkin moderiert.

Start ist um 17:30 Uhr bei der IT-Emsland, Kaiserstraße 10B, 49809 Lingen (Ems). Dort soll es auch Parkplätze geben. Im Anschluss an das Treffen ist noch ein wenig Socializing in einem Lokal geplant.

Mein Vortrag wird “PowerShell 101 für Administratoren” sein, welchen ich auch bereits auf dem SQLSaturday #313 gezeigt hatte. Da ich mehr Zeit habe, werde ich auch ein wenig mehr ins Detail gehen können. ;-)

Und als kleine Besonderheit ist das wohl mein erster Vortrag, welchen ich als SQL Server MVP halten werde. Mal schauen, wie das so ist. Ich freue mich auf Lingen!

Rick Kutschera: Ascending Key in SQL 2014

Don’t know if you are all aware of that, but just to make sure…

SQL 2014 introduced a new cardinality estimator that gets automatically activated once you switch the compatibility level to 120. In general this is a very cool thing, and in the very most cases it brings better results than the old one. There is one scenario though where you might face problems with it: The cardinality estimator was never very good with ascending keys. (e.g. Identity columns.) The old one was bad, the new one is exactly as bad… The problem is… For the old one there was a workaround with Traceflags 2389 and 2390. Those DO NOT work with the new cardinality estimator anymore, they are just ignored. So if you are using those TFs be aware of that change.

Christoph Muthmann: Mainstream Support Endet

Vielleicht ist es nicht allen bewusst, aber heute endet der Mainstream Support für einige SQL Server Versionen,

Full story »

Martin Hey: Mit dem Service Broker auf Datenänderungen reagieren

Im SQL Server gibt es seit längerem den SQL Server Service Broker. Mit dessen Hilfe kann man live auf Datenänderungen reagieren. Beispiel gefällig?

Ich habe eine sehr einfache Bibliotheksdatenbank. Enthalten ist nur eine Tabelle (dbo.Books) mit den Spalten ID, Author und Title. Und ich habe eine WPF-Anwendung in der in der MainView per Entitiy Framework die Bücher geladen und an die Liste gebunden werden.

<Window x:Class="MyApp.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="MainWindow" Height="350" Width="525">
    <Grid>
        <ListBox ItemsSource="{Binding BookList}" >
            <ListBox.ItemTemplate>
                <DataTemplate>
                    <TextBlock Text="{Binding Title}"/>
                </DataTemplate>
            </ListBox.ItemTemplate>
        </ListBox>
    </Grid>
</Window>

public class MainViewModel
{
    public MainViewModel()
    {
        ReloadData();
    }

    private void ReloadData()
    {
        using (var entitites = new LibraryEntities())
        {
            bookList.Clear();
            foreach (var book in entitites.Books)
            {
                bookList.Add(book);   
            }
        }
    }

    private readonly ObservableCollection bookList = new ObservableCollection();

    public ObservableCollection BookList
    {
        get
        {
            return bookList;
        }
    }
}

So weit erst einmal kein Hexenwerk. Der spannende Teil kommt jetzt.

Seit ADO.NET 2.0 gibt es die Klasse SqlDependency, mit der Änderungen an den Daten überwacht werden können. Diese kann für diesen Zweck auch hier verwendet werden.

SqlDependency verfügt über 2 statische Methoden Start und Stop und wie der Name schon vermuten lässt, kann man damit das Tracking starten und auch wieder beenden. In meinem Beispiel sind diese im Konstrukor und im Dispose des ViewModels. Der restliche Code wird in der Reload-Methode angefügt...
public class MainViewModel : IDisposable
{
    private string connectionString;

    public MainViewModel()
    {
        connectionString = new LibraryEntities().Database.Connection.ConnectionString;
        dispatcher = Dispatcher.CurrentDispatcher;
        SqlDependency.Start(connectionString);
        ReloadData();
    }

    private void ReloadData()
    {
        using (var entitites = new LibraryEntities())
        {
            bookList.Clear();
            foreach (var book in entitites.Books)
            {
                bookList.Add(book);   
            }
        }

        using (var connection = new SqlConnection(connectionString))
        {
            connection.Open();
            using (var command = new SqlCommand("SELECT Title, Author FROM dbo.Books", connection))
            {
                command.Notification = null;
                var dependency = new SqlDependency(command);
                dependency.OnChange += OnDependencyChange;
                command.ExecuteReader(CommandBehavior.CloseConnection);
            }
        }
    }

    private void OnDependencyChange(object s, SqlNotificationEventArgs e)
    {
        ((SqlDependency)s).OnChange -= OnDependencyChange;

        if (e.Type == SqlNotificationType.Change)
        {
            dispatcher.Invoke(this.ReloadData);
                
        }
    }

    private readonly ObservableCollection<Books> bookList = new ObservableCollection<Books>();

    private Dispatcher dispatcher;

    public ObservableCollection<Books> BookList
    {
        get
        {
            return bookList;
        }
    }

    public void Dispose()
    {
        SqlDependency.Stop(connectionString);
    }
}

... und hier beginnt es etwas schmutzig zu werden.

SqlDependency basiert auf den Möglichkeiten von ADO.NET 2.0. Damit wird hier eine SqlConnection benötigt und ein SqlCommand, das mit einem DataReader auch ausgeführt werden muss. Und es gibt noch ein paar weitere Punkte zu beachten:
  1. Auf der Datenbank muss der Broker aktiviert sein.
  2. Der Benutzer benötigt ausreichende Berechtigungen (SUBSCRIBE QUERY NOTIFICATIONS)
  3. Der Command bei der Initialisierung der SqlDependency muss bestimmten Voraussetzungen entsprechen (also z.B. nicht SELECT * FROM ....)

Alle diese Einschränkungen kann man ausführlich nochmal auf CodeProject nachlesen.

Während der Ausführung ist es dann wichtig zu wissen, dass der Event ein One-Shot ist - heißt er wird nur bei der ersten Änderung ausgelöst. Deshalb muss dann wenn die Daten neu geladen werden auch der Event wieder registriert werden.

Wenn man das alles berücksichtigt, dann kann man damit aber recht coole Sachen machen. Und man ist nicht auf die WPF beschränkt. Mit der Hilfe von OWIN und SignalR auch weitreichender über Datenänderungen informieren.

Christoph Muthmann: Noch mehr SQL Server MVPs in Deutschland

Ich freue mich über zwei neue SQL Server MVPs und einen wiederernannten!

Full story »

Sascha Lorenz: SQL Server MVP (Microsoft Most Valuable Professional)

Es ist tatsächlich passiert. So sieht sie also aus, die Mail von Microsoft, welche einem zum MVP macht.

image

Diese Auszeichnung ist eine ganz besondere Ehre für mich. Vielen Dank an Alle die mich vorgeschlagen und in das Programm “geschubst” haben! ;-)

Seit nun fast genau 10 Jahren organisiere ich Usergroup Treffen (Im September das 100. Treffen in Hamburg), spreche auf Veranstaltungen und erstellen Online-Inhalte wie Blogposts und Webcasts. Meinen neuen Status als MVP und die damit verbundenen Möglichkeiten möchte ich nutzen, um der SQL Server Community noch mehr zur Seite zu stehen mit meinen Ideen und Erfahrungen aus dem SQL Server Alltag.

Hier ein wenig Erläuterung direkt von Microsoft zum MVP Award.

Christoph Muthmann: Eindrücke vom SQLSaturday#313

Heute nur kurz ein paar Fotos, die alle von Dirk Hondong stammen. Vielen Dank an Dirk für die tolle Bildberichterstattung!

Full story »

Robert Panther: Neues Buch zur Optimierung von SQL-Abfragen

Bei entwickler.press ist gerade mein neues Buch – oder genauer Büchlein – zum Thema Optimierung von SQL-Abfragen erschienen. Das Buch erklärt in kompakter Form am Beispiel von Microsoft SQL Server, wie man performante SQL-Abfragen formuliert. Dabei wird auch auf die verschiedenen Möglichkeiten hingewiesen, die verschiedene Versionen des Produkts (bis hin zu SQL Server 2014) bieten. Viele Optimierungsansätze sind jedoch auch auf SQL-basierte Datenbank-Management-Systeme anderer Hersteller anwendbar. Auch wenn der Text sich primär an Anwendungs- und Datenbankentwickler richtet, dürfte der Inhalt auch für Administratoren interessant sein, zumal die Praxisbeispiele auch diesen Teil mit abdecken.
Das Buch ist bewusst kompakt gehalten und bildet eine auf SQL-Optimierung konzentrierte (aber auch aktualisierte und um neue Inhalte ergänzte) Teilausgabe meines – ebenfalls bei entwickler.press erschienenen – SQL Performance-Ratgebers.

SQL-Abfragen optimieren

Robert Panther
SQL-Abfragen optimieren
Was Entwickler über Performance wissen müssen
entwickler.press
176 Seiten (Softcover)
ISBN: 978-3-86802-123-3
Preis: € 12,90 inkl. MwSt.

Alternativ auch als eBook erhältlich:

PDF-ISBN: 978-3-86802-310-7
PDF-Preis: 9,99 €

EPUB-ISBN: 978-3-86802-650-4
EPUB-Preis: 9,99 €

Weitere Infos:


Sascha Lorenz: SQL Server PowerShell Vortrag vom SQLSaturday #313

Hier noch mal alle Folien zu meinem Einführungsvortrag PowerShell für SQL Server Administratoren.

 
 

Sascha Lorenz: SQLSaturday #313 – Rheinland – Und, wie war's dieses Mal so?

Wie die Zeit vergeht. Letztes Wochenende war schon wieder ein SQLSaturday in Deutschland. Wir sind bei #313 angekommen, auch bekannt als #sqlsatrheinland.

Eigentlich war Vieles wie beim letzten SQLSaturday (#230) an der Fachhochschule Bonn-Rhein-Sieg. Super Veranstaltung, alles gut organisiert durch viele freiwillige Helfer.

Das Essen. Es muss erwähnt werden, dass ein SQLSaturday kostenlos für die Teilnehmer ist und nur durch die Sponsoren finanziert wird. Vielen Dank an diese! Wie letztes Jahr auf hohem Uni Niveau.

Meine Session verlief sehr gut und ich hoffe, ich konnte einen guten Einstieg in Windows PowerShell für SQL Server geben. Es hat großen Spaß gemacht! Die Folien habe ich bereits hochgeladen. Folien für PowerShell 101 für SQL Server Admins

Geschenke. Am Ende gab es dann wieder einen Verlosungsmarathon, welchen wir ein wenig optimiert haben. Wir werden besser und schneller! Und viel Spaß mit der Xbox One und Playstation. ;-)

Dieses Jahr gab es am Freitag keine Precons, sondern einen Big Data Hackathon. Dieser wurde genau wie der SQLSaturday sehr gut angenommen.

Vielen Dank an die Organisatoren und die vielen vielen Freiwilligen vor Ort!
Ich freue mich schon auf den nächsten SQLSaturday in Deutschland!

image

Impression vom Big Data Hackathon.

imageimage

Big Data und Cloud Experten Scott Klein und Sascha Dittmann haben den Freitag moderiert.

image

Natürlich gab es auch wieder ein internationales Speaker Dinner ! Wusstet Ihr, dass es Bönnsch gibt?

image

Und dieses Selfie von Niko Neugebauer wird wohl in die Geschichte des SQL Servers eingehen!

Philipp Lenz: Einführung in PowerQuery, Teil 1 von ?

thIn diesem Blog Eintrag möchte ich eine kurze Einführung/Tutorial in das Thema PowerQuery geben. Das ganze wird eine Serie, Teil 1 von ? – ganz in der Manier des Report Viewers. Schauen wir mal wieviele es werden.

In diesem Beitrag schauen wir uns den Import von Daten aus dem Web an und wie die Daten verarbeitet, transformiert und in das Modell geladen werden können. Weiterhin werden 2 Tabellen mit einander verbunden und als eine Liste später in Excel dargestellt. Soviel zum Ziel…

2014-06-28_21-19-21Starten wir auf einer Webseite. Dort liegt eine fiktive Preisliste die außerhalb der eigenen Umgebung liegt. D.h. diese Liste steht weder auf internen SQL Servern o.ä. zur Verfügung und liegt auch nicht unter der eigenen Kontrolle. Dennoch soll diese Preisliste in Excel bzw. PowerPivot verarbeitet werden. Um an solche Daten zu kommen, bietet sich das kostenlose Excel 2013 Addin “PowerQuery” an.

2014-06-28_21-20-19In Excel wechseln wir auf den Ribbon Power Query und laden Daten aus dem Web. Dort wird die URL der Webseite eingegeben.

2014-06-28_21-21-12Nach dem Bestätigen werden uns 2 Tabellen angeboten. Auf der Webseite war schon zu sehen das die erste Tabelle die Preisliste darstellt. Wenn man sich nicht sicher ist, fährt man mit der Maus über die Einträge und bekommt dort schon eine Vorschau.

Mit einem Klick auf Bearbeiten wechselt man in ein eigenes PowerQuery Fenster um die Verarbeitungsoptionen festzulegen.

2014-06-28_21-24-03In dem Bereich “Angewendete Schritte” wird eine Historie geführt. Das erste nach dem Laden ist immer das setzen, dass die erste Zeile die Überschriften der Zellen beinhaltet. Jegliche Schritte können hier manuell gelöscht werden. Da die Spalte Rabatt leider die Daten mit Punkten anstatt einem Komma in der Fließkomma Zahl trennt, muss dies beim Import geändert werden, da ansonsten dies in Excel als Tausender Trennzeichen interpretiert wird. Weiterhin muss das Prozentzeichen entfernt werden. Beim Preis wurde schon automatisch das Euro Zeichen entfernt. Über den Bereich Werte Ersetzen werden diese Änderungen vorgenommen.

2014-06-28_21-24-572014-06-28_21-25-29Weiterhin wird die Spalte noch als Datentyp Zahl definiert. Der ETL Prozess ist somit eigentlich abgeschlossen. Über den Bereich “Einstellungen laden” können nun die Daten nach Excel und/oder PowerPivot (via in Datenmodell laden) übergeben werden.

2014-06-28_21-32-59Nun haben wir aber noch eine zweite Preisliste in einem anderen Format die hier dieser Liste angefügt werden soll. Ein Feature was in PowerPivot schonmal nicht möglich wäre, da wir so nur zwei Tabellen bilden könnten. Diese Preisliste unterscheidet sich in den Spalten. Die Rabatt Spalten fehlen völlig und die Beschreibung wie auch die Preisspalte sind auch noch anders benannt. Dennoch importieren wir einfach über PowerQuery die Daten:

2014-06-28_21-34-212014-06-28_21-35-14

 

 

 

 

 

 

 

Hier benennen wir erstmal die Spalten so wie in der vorherigen Tabelle indem man mit einem Doppelklick auf die Überschriften der Spalten klickt und die vorherigen Werte überschreibt.

2014-06-28_21-37-30Nun fügen wir einfach die beiden fehlenden Spalten noch der Tabelle hinzu damit die beiden Tabellen das gleiche Format/Modell haben. Als Standardwert tragen wir hier einfach eine 0 ein.

2014-06-28_21-40-37Nun nutzen wir das Feature “Abfrage anfügen”. Hier wählen wir die vorherige in PowerQuery verarbeitete Tabelle aus und bestätigen das Fenster mit OK.

Nun sind beide Tabellen in einer zusammengeführt. Über den Aktualisieren Knopf werden auch beide Tabellen aktualisiert, d.h. aus zwei Schritten wird einer.

2014-06-28_21-41-41Wenn nun die Daten nach PowerPivot oder Excel übergeben werden, liegen die Daten in einer Tabelle.

Fazit:
Mächtiges Tool was teils komplexe ETL Prozesse in den Self Service BI Bereich bringt und Anwendern die Möglichkeit bietet, Internet Daten schnell und einfach ohne IT zu verarbeiten.

 

 

 

 

 

Philipp Lenz: PowerBI: Automatisches Aktualisieren von PowerPivot Mappen aus onPremise Umgebungen

PowerBIErgänzend zu den Unterlagen von Microsoft zum Thema Power BI wie man einen Gateway und eine Datenquelle in einer on Premise Umgebung einrichtet, will ich das hier nochmal etwas deutlicher und bebildert beschreiben, da es hier doch einige Stolpersteine gibt …

Kurz vor ab  – wofür ist das ganze Thema gut? Mit Power BI können PowerPivot Arbeitsmappen mit oder ohne PowerView auf einen Office 365 SharePoint Online Server gelegt werden. Bei dem Thema bekommen immer die meisten Angst (“oh je, da sind ja alle meine Daten in der Cloud und was sagt der Datenschützer dazu?”) – ich persönlich halte i.d.R. nur die Daten in den Arbeitsmappen die ich für die Ermittlung der Kennzahlen und dessen Visualisierung benötige – mehr nicht! D.h. darin befindet sich nicht der Name meines Kunden oder dessen Detail Daten, denn sowas brauche ich eher selten in einem Balkendiagramm bei der Visualisierung einer Kennzahl auf einem Dashboard. Sollte ich dennoch solche Daten brauchen, bspw. bei einem Kundenblatt, dann verlasse ich mich auf die geschlossene Auftragsdatenverarbeitungserklärung (kurz ADV) mit Microsoft und das die Daten in Europa liegen und ich dies mit meinem Kunden auch so vereinbart und besprochen haben, aber das soll hier kein Vortrag zum Thema Datenschutz und Datensicherheit in der Cloud werden.

Nun denn, wenn meine Excel Sheets nun in der Cloud liegen, müssen ja auch irgendwie die Daten via der Datenquellen aktualisiert werden. D.h. die Datenquellen liegen in einer lokalen Installation innerhalb meiner Infrastruktur und die Aktualisierung soll möglichst automatisch und außerhalb der Lastzeiten liegen.

Um dies zu realisieren, installiere ich einen Gateway Dienst von Office 365 der auf einem Server innerhalb meiner Infrastruktur ausgeführt wird. Dieser nimmt die Aktualisierung vor indem dieser die Daten aus meiner SQL Server Datenquelle extrahiert und zum PowerPivot Excel Sheet in Office 365 transportiert. Dieser Weg ist m.E. auch recht sympathisch, da die Verbindung aus meiner Umgebung ausgehend und besser kontrollierbar ist. Wir der Gateway Dienst installiert wird, wird recht einfach und gut in diesem Dokument beschrieben. Sobald das Gateway online ist, sieht man dies auf der Power BI Admin Center Webseite:
28-06-_2014_15-21-30Auf dieser Übersichtswebseite sieht man jegliches eingerichtete Gateway. D.h. es können auch mehrere Gateways installiert und betrieben werden um so die Arbeitsmappen mit verschiedenen Quellen zu versorgen. Stand jetzt, funktioniert die automatische Aktualisierung von PowerPivot Mappen nur mit SQL Server Datenquellen. Über den lokalen Gateway Manager (“Microsoft-Datenverbindungsgateway-Konfigurations-Manager”) erhält man auch die nötigen Informationen zum verwendeten Endpunkt in Bezug auf das Protokoll und den Port der selbstverständlich in der Firewall (sofern ausgehende Verbindungen geblockt werden) eingetragen werden muss.

Sobald das Gateway läuft, sollte man zuerst die PowerPivot Mappe sich anschauen und die darin enthaltene Verbindung überprüfen:

28-06-_2014_15-29-34Innerhalb der PowerPivot Mappe wechselt man auf die vorhandenen Verbindungen, dort auf bearbeiten und auf Erweitert. Hier erhält man alle Informationen die in der Datenquelle in Office 365 eingetragen werden müssen (Hinweis, auch wenn fast selbstverständlich, aus aktuellem Anlass: Auf die Servernamen . oder localhost sollte verzichtet werden, wenn der Gateway Server nicht gleich der SQL Server ist …).

Nun trägt man genau diese Informationen in der neuen Datenquelle im PowerBI Admin Center ein:

28-06-_2014_15-35-08Hier sollte man unbedingt darauf achten, dass der Benutzer die entsprechende Berechtigungen erhalten hat der konfiguriert wurde um die Daten zu lesen aber auch das der gleiche Verbindungsanbieter gewählt wurde, der auch in der PowerPivot Mappe angegeben wurde. Sollten irgendwelche Daten abweichen, war meine Erfahrung das die Aktualisierung einfach nicht funktioniert hat – irgendwie auch logisch :-)

Nun kann man die PowerPivot Mappe in die PowerBI Gallerie legen und auch hier die automatische Aktualisierung konfigurieren. Durch die Warn-Mechanismen wird man auch auf fehlerhafte Aktualisierungen aufmerksam gemacht. Sollte eine Aktualisierung nach ca. 10 mal nicht funktionieren, wird automatisch die Aktualisierung deaktiviert, bzw. der Zeitplan.

Klasse finde ich bei Power BI dann auch die Unterstützung der Power BI QA, also das man dem Server fragen stellt und dieser die anhand des Datenmodells beantwortet. Wenn man dies das erstemal verwendet, merkt man wie schlecht häufig die Felder und Tabellen wie auch Measures benannt sind, so das eine Frage dann lautet Anzahl der Anzahl nach Beschreibung … Aber eben auch die Features der Power BI App im Windows Store die kostenlos dazu zählt ist klasse, so bekommen die Adressaten die Power View Berichte der Excel Arbeitsmappen als Touch App auf die Endgeräte. Lediglich der Preis von ca. 32 € pro Nutzer und Monat ist etwas abschreckend, aber schauen wir mal wie sich das entwickelt. Am besten einfach mal selber mit einer Evaluation ausprobieren.

 

Sascha Dittmann: MapReduce Entwurfsmuster - Numerische Aggregation (Standardabweichung 2/2)

MapReduce Entwurfsmuster - DurchschnittswerteIm heutigen Teil meiner kleinen Serie der MapReduce Entwurfsmustern für Microsoft HDInsight dreht es sich erneut um die Berechnung der Standardabweichung und des Medians.
Diesmal werde ich den vorhandenen Algorithmus dahingehend erweitern, dass die Nutzung eines Combiners ermöglicht wird...

Im vergangene Teil dieser Serie hatte ich bereits eine vereinfachtes Beispiel zur Berechnung der Standardabweichung und des Medians vorgestellt.

Das Problem mit dem vorgestellten Algorithmus ist allerdings das er keinen Combiner verwenden kann und dadurch - bei größeren Datenmengen - recht resourcenhungrig wird.

 

Beispiel "Standardabweichung und Median (mit Combiner)"

In der Mapper-Funktion hat sich im Vergleich zum vergangene Teil nichts geändert.
Diese extrahiert weiterhin das CreationData- und Text-Attribut und gibt die Stunde und Zeichenlänge als Key/Value-Pair zurück:

public class MedianStdDevMapper : MapperBase
{
  public override void Map(string inputLine, MapperContext context)
  {
    var parsed = XmlUtils.ParseXml(inputLine);

    if (parsed == null 
        || !parsed.ContainsKey("CreationDate") 
        || !parsed.ContainsKey("Text"))
    {
      context.CoreContext.IncrementCounter(
        "Median / Std. Dev. Mapper", "Invalid Rows", 1);
      return;
    }

    DateTime creationDate;
    if (!DateTime.TryParse(parsed["CreationDate"], out creationDate))
    {
      context.CoreContext.IncrementCounter(
        "Median / Std. Dev. Mapper", "Invalid Creation Dates", 1);
      return;
    }

    var text = parsed["Text"];

    context.EmitKeyValue(
      creationDate.Hour.ToString(CultureInfo.InvariantCulture),
      text.Length.ToString(CultureInfo.InvariantCulture));
  }
}

 

Um die Datenmenge zu verkleinern, aggregiert die Combiner-Funktion die erhaltenen Values vor.
Gleiche Textlängen werden hierbei mit ihrer jeweiligen Anzahl als neue Values zurückgegeben.

public class MedianStdDevCombiner 
  : JsonOutReducerCombinerBase<MedianStdDevData>
{
  public override void Reduce(string key, 
    IEnumerable<string> values, 
    JsonReducerCombinerContext<MedianStdDevData> context)
  {
    var query = values
      .Select(int.Parse)
      .GroupBy(v => v)
      .Select(grp => new MedianStdDevData
      {
        Value = grp.Key,
        Count = grp.Count(),
      });

    foreach (var value in query)
      context.EmitKeyValue(key, value);
  }
}

 

Bei der Combiner-Funktion nutze ich erneut eine POCO-Klasse, sowie eine JSON-Basisklasse, um mir die Datenübertragung zu vereinfachen.

public class MedianStdDevData 
{ 
  public int Value { get; set; } 
  public int Count { get; set; } 
}

 

Bei der Reducer-Funktion müssen die komprimierten Values wieder "ausgepackt" werden, um anschließend den Median, die Varianz, sowie die Standardabweichung berechnen zu können.

public class MedianStdDevReducer 
  : JsonInReducerCombinerBase<MedianStdDevData>
{
  public override void Reduce(string key, 
    IEnumerable<MedianStdDevData> values, 
    ReducerCombinerContext context)
  {
    float sum = 0;
    long totalComments = 0;
        
    var commentLengthCounts = new Dictionary<int, long>();
    foreach (var data in values)
    {
      totalComments += data.Count;
      sum += data.Value * data.Count;
      if (!commentLengthCounts.ContainsKey(data.Value))
        commentLengthCounts.Add(data.Value, data.Count);
      else
        commentLengthCounts[data.Value] += data.Count;
    }

    // calculate median
    double median = 0;
    var medianIndex = totalComments / 2;
    long previousComments = 0;
    var prevKey = 0;
    foreach (var entry in commentLengthCounts.OrderBy(e => e.Key))
    {
      if (previousComments <= medianIndex 
          && medianIndex < previousComments + entry.Value)
      {
        if (totalComments % 2 == 0 && previousComments == medianIndex)
          median = (entry.Key + prevKey) / 2.0f;
        else
          median = entry.Key;
        break;
      }
      previousComments += entry.Value;
      prevKey = entry.Key;
    }

    // calculate standard deviation
    var avg = sum / totalComments;
    var sumOfSquares = commentLengthCounts
      .Sum(entry => Math.Pow(entry.Key - avg, 2) * entry.Value);
    var stdDev = Math.Sqrt(sumOfSquares / (totalComments - 1));

    context.EmitKeyValue(
      key,
      String.Format("{0}\t{1}", median, stdDev));
  }
}

 


Weitere Informationen Weitere Informationen:

Christoph Muthmann: Berechtigungen auf Serverebene

Wenn man eine Datenbank auf einen anderen Server überträgt, werden die darin enthaltenen Berechtigungen mitgegeben. Für die Übertragung der Logins (mit Passwort) gibt es verschiedene Möglichkeiten. Was ist aber mit den Berechtigungen auf Serverebene?

Full story »

Uwe Ricken: Sperrverhalten von Shared Locks…

Auf Grund einer Anfrage des von mir sehr geschätzten Kollegen Johannes Curio (e | w), die sich um Sperren von Objekten in einem HEAP drehte, habe ich mich etwas intensiver mit dem Sperrverhalten von Microsoft SQL Server beschäftigt, da die grundsätzliche Frage war, ob Microsoft SQL Server in einem HEAP jede Datenseite / jeden Datensatz nach dem Scannen sofort wieder freigibt. Die Antwort – wie meistens bei Microsoft SQL Server – … “It depends”. Dieser Artikel beschreibt die unterschiedlichen Sperrverhalten bei SELECT-Statements unter Berücksichtigung der verschiedenen ISO-Isolationsstufen in einem HEAP und in einem Clustered Index.

Testumgebung

Alle Tests verwenden einen HEAP, der mit 1.000 Datensätzen gefüllt wird. Zwei Fragen sollen mit den nachfolgenden Tests beantwortet werden:
  • Sperrt Microsoft SQL Server bei einem SELECT in einem HEAP jeden Datensatz?
  • Wird nach dem Lesen einer Ressource unmittelbar eine Freigabe der Ressource ausgelöst?
-- Erstellen der Demotabelle
CREATE TABLE dbo.Customer
(
    Id    INT        NOT NULL  IDENTITY(1,1),
    Name  CHAR(100)  NOT NULL,
    Ort   CHAR(100)  NOT NULL
);
GO
 
-- Füllen der Demotabelle mit 1.000 Datensätzen
DECLARE @i INT = 1;
WHILE @i <= 10000
BEGIN
    INSERT INTO dbo.Customer (Name, Ort)
    VALUES ('Kunde ' + CAST(@i AS VARCHAR(10)), 'Frankfurt am Main');
 
    SET @i += 1;
END
GO
 
-- Aktualisierung von 10 Datensätzen für hohe Selektivität
UPDATE dbo.Customer
SET    Ort = 'Erzhausen'
WHERE  Id % 1000 = 0;
GO

Die Tabelle hat 1.000 Datensätze und besitzt keinen Index. Für die Tests mit unterschiedlichen ISO-Isolationsstufen reicht dieses einfache Modell aus.

Hinweis(e)

Der Fokus dieses Artikels liegt in dem Sperrverhalten bei SELECT-Statements unter Berücksichtigung verschiedener ISO-Isolationsstufen; viele andere – aber ebenso wichtige – Details können nur bedingt in dieser Tiefe beschrieben werden, da sie sonst den Rahmen dieses Artikels sprengen würden; es werden jedoch weiterführende Links zu den entsprechenden Themen / Objekten genannt oder aber die Fachausdrücke unmittelbar mit Links versehen! Ein wichtiger Link sei bereits vorab genannt – die unterschiedlichen Sperren, die von Microsoft SQL Server verwendet werden können können unter “Kompatibilität von Sperren” genauer studiert werden. Dieser Artikel beleuchtet nicht die Sperr-Möglichkeiten auf Statement-Level (ROWLOCK / TABLOCK, …) sondern ausschließlich das automatische Sperrverhalten von Microsoft SQL Server unter den verschiedenen ISO-Isolationsstufen.

Protokollierung mit Microsoft SQL Server Profiler

Ich höre bereits die Aufschreie der Anhänger von “Extended Events” aber für die benötigte Protokollierung ist Microsoft SQL Server Profiler vollkommen ausreichend und er bietet mir verbesserte lesbare Ergebnisse (für diesen Blockeintrag).

Warum eine dedizierte Protokollierung?

Das Problem von Shared Locks – bei einigen ISO-Isolationsstufen – besteht darin, dass man sie nicht “sichtbar” machen kann, indem man eine dedizierte Transaktion beginnt, ein SELECT ausführt und diese Transaktion geöffnet lässt. Eine Kontrolle der gesetzten Sperren ist daher sehr schwierig und nur während der Ausführung des Befehls in einer SQL Server Profiler-Sitzung oder mit Extended Events zu kontrollieren.

Konfiguration der Microsoft SQL Server Profiler Sitzung


SQL_PROFILER_SETTINGS_01

Während der Ausführung der SQL-Abfragen wird aufgezeichnet, wann die Ausführung des Befehls beginnt [SQL:StmtStarting] und wann die Ausführung beendet wird [SQL:StmtCompleted]. Gleichwohl muss aufgezeichnet werden, wann eine Objektsperre gesetzt wird [Lock:Acquired] und wann die zuvor gesetzte Sperre wieder aufgehoben wird [Lock:Released]. Die zu jeder Aktion aufzuzeichnenden Informationen erstrecken sich vom Sperrmodus [Mode], der auf das Objekt [ObjectID] gesetzt wird als auch auf die Ressource, die in [TextData] angezeigt wird. Der Sperrtype wird in der Spalte [Type] angezeigt. Um unnötige Aktionen aufzuzeichnen, empfiehlt es sich, auf [SPID] einen Filter zu setzen, um ausschließlich Aktionen der Verbindungen zu filtern, in der die nachfolgenden Abfragen ausgeführt werden. Wer mehr über die Konfigurationsmöglichkeiten des Microsoft SQL Server Profilers wissen möchte, wird hier fündig.

Alternative Ausgabe mittels TRACEFLAGS


Die Ausgabe von Sperren (ähnlich wie in der Aufzeichnung mit dem Profiler oder Extended Events) kann man auch unmittelbar in SQL Server Management Studio ausgeben lassen; natürlich sind es auch hier wieder Traceflags, die solche Dinge ermöglichen, wie der folgende Code zeigt:


DBCC TRACEON(-1, 3604,1200) WITH NO_INFOMSGS;
TF 3604 leitet Ergebnisse nicht in das Fehlerprotokoll des Microsoft SQL Server sondern in die Ausgabe von SQL Server Management Studio und TF 1200 aktiviert die Ausgabe von Sperrinformationen. Bitte darauf achten, dass TF 1200 nicht dokumentiert ist und somit auch nicht in einem Produktivumfeld eingesetzt werden sollte. Weiterhin gilt es zu beachten, dass – je nach Anzahl der Sperren – eine sehr große Datenmenge an den Client zurückgeliefert werden kann! Grundsätzlich wird die Ausführung einer Abfragen bei aktiviertem TF deutlich beeinflusst! Ein weiterer – nicht zu unterschätzender – Punkt, der ganz besonders bei Produktionssystemen beachtet werden sollte; es handelt sich um einen GLOBALEN Traceflag. Die Aktivierung gilt also nicht nur für die aktuelle Session sondern für alle Sessions, die auf dem Microsoft SQL Server ausgeführt werden.

Testabfragen

Für die Tests werden zwei Abfragetypen verwendet, die wiederkehrend unter einer jeweils anderen ISO-Isolationsstufe ausgeführt werden. Dabei handelt es sich jeweils um Abfragen auf die ID als auch auf das Attribut [Ort] in der Tabelle [dbo].[Customer]. Mit der Abfrage auf die [ID] soll eine Auswahl mit hoher Selektivität untersucht werden, während eine Abfrage auf das Attribut [Ort] mit einer sehr hohen Datenmenge (99%) arbeitet.
Allen verwendeten Abfragen ist gemein, dass sie vor der Ausführung die zu überprüfende Isolationsstufe explizit für die Session setzen. Weitere Details zum Setzen von manuellen Isolationsstufen finden sich hier.

Abfrage mit hoher Selektivität

Grundsätzlich stellt sich in einem Heap nicht die Frage nach “Selektivität”, da immer ein Table Scan durchgeführt werden muss (schließlich kann nicht gezielt über einen Index gesucht werden). Aber es gibt im Rahmen von Sperren interessante Merkmale, die bei entsprechender Selektivität auffallend sind. Ein Abfrage auf eine explizite ID ist hoch selektiv, da die ID – unabhängig davon, ob es sich um einen Heap handelt – immer nur einen Datensatz zurückliefert. Die CAST-Funktion wird verwendet, um sicherzustellen, dass keine implizite Konvertierung (wie hier beschrieben) durchgeführt wird.



SELECT * FROM dbo.Customer WHERE Id = CAST(10 AS int);

GO

Abfrage mit niedriger Selektivität

Abfragen mit niedriger Selektivität können sehr große Datenmengen liefern, wenn die Auswahl auf einen Wert fällt, der sehr häufig vorkommt. In der Testumgebung wird im [Ort] sehr häufig “Frankfurt am Main” verwendet (90%). Abfragen auf das Attribut [Ort] mit der Einschränkung “Frankfurt am Main” werden immer 9.990 Datensätze (99%) liefern; nur 1 % der Datensätze hat einen anderen Wert (“Erzhausen”) in diesem Attribut.



SELECT * FROM dbo.Customer WHERE Ort = 'Frankfurt am Main';

GO

Isolationsstufe “READ COMMITTED”

Die Isolationsstufe “READ COMMITTED” ist die Standardeinstellung für Datenbanken in Microsoft SQL Server. READ COMMITTED bedeutet, dass ausschließlich Datensätze gelesen werden, die VOR dem Lesevorgang mittels COMMIT in der Datenbank gespeichert worden sind. Durch dieses Verfahren werden “Dirty Reads” vermieden. Im Microsoft SQL Server Profiler werden bei Ausführung der hoch selektiven Abfrage (es wird 1 Datensatz ausgegeben!) folgende Sperrverhalten aufgezeichnet (Auszug):

SQL_PROFILER_RESULTS_01
Das Ergebnis zeigt den Beginn der Aufzeichnung mit dem Absetzen des eigentlichen SQL Befehls. Anschließend wird ein “Intent Shared Lock” auf die Tabelle selbst gesetzt. Ein IS-Lock wird von Microsoft SQL Server IMMER gesetzt, um eine – mögliche – Lock-Eskalation durchführen zu können. Mit einer IS-Sperre signalisiert Microsoft SQL Server, dass diese Objekte nicht durch andere konkurrierende /inkompatible Sperren blockiert werden können.
Die nachfolgenden 16 Zeilen beantworten bereits die ursprüngliche Frage. “Gibt Microsoft SQL Server nach dem Scannen einer Ressource die Sperre wieder auf?”. Ja – in der Isolationsstufe “READ COMMITTED” werden gesetzte Shared Locks unmittelbar nach dem Scan der Ressource wieder freigegeben, damit andere Operationen die Datenseiten verwenden können. Auch die zweite Frage, die sich unmittelbar an die erste anschließt: “Führt Microsoft SQL Server Zeilensperren durch?” kann mit diesem einfachen Test belegt werden. Im Isolationsmodus “READ COMMITTED” werden KEINE Datensätze gesperrt! Alle Sperren werden auf Ebene einer Datenseite durchgeführt. Sobald Microsoft SQL Server die Datenseite nach dem Kriterium durchsucht hat, wird die  nächste Datenseite durchsucht. Zuvor wird die mit einem Shared Lock versehene Datenseite wieder freigegeben.
Die Sperre auf einer Datenseite – statt einer Datenzeile – macht in einem HEAP Sinn, da kein Index verwendet werden kann. Sofern Microsoft SQL Server nicht gezielt durch Indexes die geforderten Daten anfordern kann (SEEK), muss immer die vollständige Datenseite nach passenden Datensätzen durchsucht werden (SCAN). Durch das Sperren ganzer Datenseiten werden die Ressourcen (RAM / CPU) des Microsoft SQL Server geschont. Eine Zeilensperre ist aber auch in einer indexierten Tabelle nicht erforderlich, da keine Änderungen an den Datenzeilen durchgeführt werden müssen. Nur in den restriktiveren ISO-Isolationsstufen müssen u. U. Zeilensperren verwendet werden (wie die nachfolgenden Beispiele zeigen werden).
SQL_PROFILER_RESULTS_02

Schemastabilität während der Ausführung von Abfragen

Nachdem alle Datenseiten eingelesen wurden, wird eine SCH-S Sperre zur Sicherung der Schemastabilität für die Tabelle selbst gesetzt. Diese Sperre ist für Microsoft SQL Server relevant, damit Objekte während der Ausführung der Abfrage keinen Strukturänderungen unterzogen werden können. Aus diesem Grund wird VOR der Ausführung der Abfrage eine Sperre zur Schemastabilität gesetzt! Diese besondere Sperre wird ausschließlich bei HEAPS angewendet.

Isolationsstufe “READ UNCOMMITTED”

Wie bereits zu Beginn erwähnt, verhält sich Microsoft SQL Server bei der Verwendung von Sperren unter der Verwendung von verschiedenen Isolationsstufen unterschiedlich. Die Isolationsstufe “READ UNCOMMITTED” ist in der Regel nicht zu empfehlen, da sie sogenannte “Dirty Reads” zulässt. Dirty Reads bedeuten, dass ein SQL Befehl auch Daten von Datenseiten liest, die zwar schon geändert aber noch nicht bestätigt wurden (Die Daten wurden innerhalb einer noch nicht abgeschlossenen Transaktion geändert). Wie verhält es sich nun mit den Objektsperren, wenn ein SELECT-Befehl für einen HEAP abgesetzt wird. Die folgende Abbildung zeigt die gesetzten Sperren bei Verwendung der zweiten Abfrage mit der ISO-Isolationsstufe “READ UNCOMMITTED”.
SQL_PROFILER_RESULTS_03
Interessant ist bei diesem Ergebnis, dass – und das gilt ausschließlich für HEAPS – ein Shared Lock auf den “Index” selbst gesetzt wird (12 – HOBT). “HOBT” steht für HeapOrBTree und die Tabelle in unserem Beispiel ist eine Tabelle ohne Indexe (HEAP). Die generelle “SCH-S” Sperre muss gesetzt werden, um Modifikationen an der Tabelle selbst zu verhindern. Dieses Verhalten wird sehr gut in den BOL beschrieben: “ Alle Abfragen, auch solche mit READUNCOMMITTED- und NOLOCK-Hinweisen, aktivieren bei der Kompilierung und Ausführung Sperren des Typs Sch-S (Schemastabilität).
Die [BULK_OPERATION]-S-Sperre auf den “Index” wird ausschließlich auf HEAPS angewendet und verhindert das Lesen von unformatierten – neuen – Datenseiten, die während des Lesevorgangs durch neue Datensätze/geänderte Datensatze (FORWARDED RECORDS) generiert werden können. Grundsätzlich unterliegt die Belegung von Speicher durch neue Datensätze in einem HEAP anderen Regeln als in einem Clustered Index (das genaue Verfahren habe ich detailliert in einem TECHNET-Artikel hier beschrieben (englisch)). Immer wieder liest man im Internet, dass READ UNCOMMITTED / NOLOCK keine Sperren verwendet. Das obige Beispiel demonstriert eindeutig, dass es sich dabei um eine Falschaussage handelt!

Isolationsstufe “REPEATABLE READ”

Die Isolationsstufe “REPEATABLE READ” gehört zu den restriktiveren Isolationsstufen. “REPEATABLE READ” definiert, dass Anweisungen keine Daten lesen können, die geändert wurden, für die jedoch noch kein Commit von anderen Transaktionen ausgeführt wurde (wie READ COMMITTED) jedoch können darüber hinaus von der aktuellen Transaktion gelesene Daten erst nach Abschluss der aktuellen Transaktion von anderen Transaktionen geändert werden. Um die Ergebnisse besser erklären zu können, wird mit dem nachfolgenden Skript zunächst die exakte Position des Datensatzes mit der Id = 10 ermittelt:

SELECT sys.fn_physlocformatter(%%physloc%%) AS Position, * FROM dbo.Customer WHERE Id = CAST(10 AS int);

PHYSICAL_LOCATION_01
Im konkrete Beispiel befindet sich der Datensatz mit der Id = 10 auf der Datenseite 163 in Slot = 9. Diese Position wird für die Untersuchung der von Microsoft SQL Server gesetzten Sperren in den engeren Fokus rücken.
SQL_PROFILER_RESULTS_04
Die Auswertung der Aufzeichnung mit dem Microsoft SQL Server Profiler zeigt, dass zunächst eine IS-Sperre auf die Tabelle selbst gesetzt wird. Anschließend wird sofort eine IS-Sperre auf die erste Datenseite (163) gesetzt. Das nachfolgende Verhalten unterscheidet sich erheblich von den zuvor beschriebenen Isolationsstufen. Tatsächlich wird JEDE Datenzeile zunächst mit einer S-Sperre versehen, um sie nach der Prüfung sofort wieder freizugeben. Dieses Verhalten gilt jedoch nicht für die Datenzeile, die sich auf der Datenseite 163 in Slot = 9 befindet! Die obige Abbildung zeigt, dass ein Shared Lock auf die Datenzeile (RID) angewendet wird, der aber nach der Prüfung nicht wieder freigegeben wird.
Die nächste Abbildung der Ergebnisse des Microsoft SQL Server Profilers zeigen, wann eine Freigabe der Datenzeile erfolgt – nachdem die Ausführung der Abfrage abgeschlossen und die Transaktion beendet ist!
SQL_PROFILER_RESULTS_05
Dieses Ergebnis ist absolut schlüssig, wenn man die Vorgehensweise der Sperren bei REPEATABLE READ genauer kennt. Der besondere Unterschied von REPEATABLE READ zu den bisher beschriebenen Isolationsstufen besteht darin, dass bei Microsoft SQL Server in dieser Isolationsstufe sichergestellt, dass ein Datensatz, der innerhalb einer Transaktion gelesen wird, auch bei einem erneuten Lesen innerhalb der gleichen Transaktion identische Daten besitzen muss. Würde Microsoft SQL Server nach dem Lesen des Datensatzes mit der ID = 10 den Datensatz wieder freigeben, wäre folgende Situation möglich:


  • Transaktion 1 setzt eine Sperre auf den Datensatz ID = 10, liest den Datensatz und hebt die Sperre wieder auf
  • Transaktion 2 bearbeitet nach der Freigabe den Datensatz mit der ID = 10 und ändert z. B. den Ort
  • Transaktion 1 setzt erneut eine Sperre auf den Datensatz ID = 10 und liest den Datensatz erneut ein.
    Diesmal sind jedoch nicht mehr die ursprünglichen Werte des ersten Lesevorgangs vorhanden sondern durch Transaktion 2 geänderten Daten.
Damit solche Änderungen nicht vorkommen können, können Datensätze während der gesamten Transaktion gesperrt werden, die zuvor beschriebenen Isolationsstufen können diese Abgrenzung nicht leisten!

Isolationsstufe “SERIALIZABLE”

Die Isolationsstufe “SERIALIZABLE” ist die restriktivste ISO-Isolationsstufe, die mit Microsoft SQL Server angewendet werden kann. In der ISO-Isolationsstufe “SERIALIZABLE” werden Bereichssperren in den Schlüsselwertbereichen eingerichtet. Dadurch wird verhindert, dass andere Transaktionen Zeilen aktualisieren oder einfügen, die den von der aktuellen Transaktion ausgeführten Anweisungen entsprechen würden. Wie restriktiv die Sperren gesetzt werden, sollen die nachfolgenden Abbildungen und Erläuterungen demonstrieren.
Für die Demonstration wird erneut eine Abfrage auf ID = 10 ausgeführt, die lediglich einen Datensatz zurückliefern wird.
SQL_PROFILER_RESULTS_06
Immer mit dem Hintergedanken, dass es sich bei der Tabelle [dbo].[Customer] um einen HEAP handelt, wird das Ergebnis schnell einleuchten. Obwohl nur ein Datensatz benötigt wird, muss ein vollständiger Table Scan durchgeführt werden. Es müssen also – wie schon zuvor geschehen – ALLE Datensatze durchsucht werden. Es wird die Tabelle als vollständiger Wertebereich durchsucht! Basierend auf der Tatsache, dass in einem HEAP nicht explizite Schlüsselwerte gesperrt werden können (Ausnahme REPEATABLE READ), wird die vollständige Tabelle gesperrt. Während einer Transaktion in der ISO-Isolationsstufe SERIALIZABLE können in einen HEAP keine weiteren Datensätze eingetragen werden!

Verhalten von Shared Locks in indizierten Tabellen

Das Sperrverhalten von SELECT-Statements ändert sich beim Zugriff auf Indexe, sofern ein INDEX SEEK angewendet werden kann. In diesem Fall werden – je nach Selektivität – nur die Datenseiten / Datenzeilen gesperrt, die durch den INDEX-SEEK gezielt verwendet werden können. Die Ergebnisse können sehr leicht selbst überprüft werden. Zunächst wird die Tabelle mit einem Clustered Index auf [ID] versehen. Zusätzlich wird ein Index für den Ort angelegt.

CREATE UNIQUE CLUSTERED INDEX ix_Customer_Id ON dbo.Customer (Id);

CREATE INDEX ix_Customer_Ort ON dbo.Customer (Ort) INCLUDE (Name);
Die nachfolgenden SQL-Statements mit ihren jeweiligen Aufzeichnungen im Profiler zeigen, wie sich das Sperrverhalten ändert. Die Ausführungen werden aber nur kurz angerissen und bei Besonderheiten vertieft.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT * FROM dbo.Customer WHERE ID = CAST(10 AS INT) ;

GO

SQL_PROFILER_RESULTS_07
Einziger Unterschied zum Sperrverhalten in einem HEAP ist die ausschließliche Sperre der Datenseite, in der sich der betreffende Datensatz befindet.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT * FROM dbo.Customer WHERE Ort = 'Erzhausen';

GO

SQL_PROFILER_RESULTS_08
Da es sich nicht mehr um einen HEAP handelt, ist ausschließlich eine SCH-S Sperre erforderlich, um Änderungen an den strukturellen Daten (Metadaten) der Tabelle zu verhindern (Schemastabilität).

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SELECT * FROM dbo.Customer WHERE Id = CAST(10 AS INT);

GO

SQL_PROFILER_RESULTS_09
Bei “REPEATABLE READ” muss sichergestellt sein, dass der Datensatz, den Microsoft SQL Server einliest auch in weiteren Lesevorgängen innerhalb der gleichen Transaktion unverändert ist. Aus diesem Grund wird eine Zeilensperre durchgeführt. Der Wert in [TextData] entspricht dem KeyHashValues des Datensatzes auf der Datenseite.

DBCC TRACEON (3604);

DBCC PAGE ('demo_db', 1, 1480, 3) WITH TABLERESULTS;

Mit dem obigen Befehl kann der Inhalt der Betroffenen Datenseite (1480) sichtbar gemacht werden. Für die bessere Lesbarkeit wird die Ausgabe als Tabelle forciert.

DBCC_PAGE_01
Die letzte Abfrage weicht etwas ab, um eine Bereichssperre zu demonstrieren, die nur in der Isolationsstufe “SERIALIZABLE READ” auftreten kann.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT * FROM dbo.Customer WHERE Id BETWEEN 10 AND 20;

GO

SQL_PROFILER_RESULTS_10
Eine Bereichssperren muss von Microsoft SQL Server gesetzt werden, um zu verhindern, dass zwischen den gesperrten Datensätzen weitere Datensätze eingefügt werden können. Würde z. B. in einer anderen Transaktion versucht werden, einen neuen Datensatz mit einer ID zwischen 10 und 20 einzutragen, wird die Transaktion so lange gesperrt, bis die von Microsoft SQL Server in der aktuellen Transaktion gehaltenen Bereichssperren wieder freigegeben werden.

Zusammenfassung

Erst einmal herzlichen Dank an Johannes Curio für die interessante Frage; zum einen hat sie mir Material für einen neuen Blogeintrag geliefert und zum anderen – wie meistens – musste ich mich wieder etwas intensiver mit Themen auseinandersetzen, die weit über die normale Verwendung hinausgehen, aber auf jeden Fall wieder viele Dinge in einem – einleuchtenden – neuen Licht zeigen.

Herzlichen Dank fürs Lesen!

Sascha Dittmann: MapReduce Entwurfsmuster - Numerische Aggregation (Standardabweichung 1/2)

MapReduce Entwurfsmuster - DurchschnittswerteIn diesem Teil meiner kleinen Serie der MapReduce Entwurfsmustern für Microsoft HDInsight erweitere ich die Gruppe der Numerische Aggregation mit einem vereinfachten Beispiel zur Berechnung der Standardabweichung und des Medians...

Nachdem ich im ersten Teil und zweiten Teil dieser Serie relative einfache MapReduce Algorithmen vorgestellt habe, möchte ich mit diesem um dem kommenden Teil den Schwierigkeitsgrad leicht erhöhen.

Die Standardabweichung (ein Maß für die Streuung der Werte) wird durch die Quadratwurzel der Varianz der untersuchten Werte berechnet.

Die dafür benötigte Varianz der Werte wird aus der Summe der quadrierten Abweichungen vom Durchschnittswert berechnet, welche noch durch die Anzahl der Werte - 1 geteilt wird.

 

Beispiel "Standardabweichung und Median (ohne Combiner)"

Bei diesem Beispiel sollen die Standardabweichung und der Median für die Kommentarlänge pro Stunde errechnet werden.

Die hierzu verwendete Mapper-Funktion ist nahezu identisch mit der aus dem vergangenen Artikel dieser Serie:

public class MedianStdDevMapper : MapperBase
{
  public override void Map(string inputLine, MapperContext context)
  {
    var parsed = XmlUtils.ParseXml(inputLine);

    if (parsed == null 
        || !parsed.ContainsKey("CreationDate") 
        || !parsed.ContainsKey("Text"))
    {
      context.CoreContext.IncrementCounter(
        "Median / Std. Dev. Mapper", "Invalid Rows", 1);
      return;
    }

    DateTime creationDate;
    if (!DateTime.TryParse(parsed["CreationDate"], out creationDate))
    {
      context.CoreContext.IncrementCounter(
        "Median / Std. Dev. Mapper", "Invalid Creation Dates", 1);
      return;
    }

    var text = parsed["Text"];

    context.EmitKeyValue(
      creationDate.Hour.ToString(CultureInfo.InvariantCulture),
      text.Length.ToString(CultureInfo.InvariantCulture));
  }
}

 

Eine Combiner-Funktion kann bei diesem vereinfachen Algorithmus nicht verwendet werden.
Im kommenden Teil stelle ich allerdings eine komplexere Variante vor, die einen Combiner beinhaltet. 

Die Reducer-Funktion berechnet anschließend den Median (Mittlerer Wert der sortierten Liste), die Varianz der Werte (s.o.), sowie die Standardabweichung (Quadratwurzel der Varianz).

Anschließend liefert der Reducer erneut die Ausgabewerte - zur späteren Weiterverarbeitung - im Tab-getrennten Format zurück.

public class MedianStdDevReducer : ReducerCombinerBase
{
  public override void Reduce(string key, 
    IEnumerable values, 
    ReducerCombinerContext context)
  {
    float sum = 0;
    int count = 0;

    var commentLengths = new List();

    foreach (var value in values.Select(float.Parse))
    {
      commentLengths.Add(value);
      sum += value;
      count++;
    }

    commentLengths.Sort((x, y) => x.CompareTo(y));

    // calculate median
    double median;
    if (count % 2 == 0)
    {
      // if commentLengths is an even value, average middle two elements
      median = (commentLengths[Convert.ToInt32(count / 2 - 1)]
        + commentLengths[Convert.ToInt32(count / 2)]) / 2.0f;
    } else {
      // else, set median to middle value
      median = commentLengths[Convert.ToInt32(count / 2)];
    }

    // calculate standard deviation
    var avg = sum / count;
    var sumOfSquares = commentLengths
      .Sum(commentLength => Math.Pow(commentLength - avg, 2));
    var stdDev = Math.Sqrt(sumOfSquares / (count - 1));

    context.EmitKeyValue(
      key,
      String.Format("{0}\t{1}", median, stdDev));
  }
}

 


Weitere Informationen Weitere Informationen:

Sascha Dittmann: MapReduce Entwurfsmuster - Numerische Aggregation (Average)

MapReduce Entwurfsmuster - DurchschnittswerteIm zweiten Teil meiner kleinen Serie der MapReduce Entwurfsmustern für Microsoft HDInsight möchte ich ein weiteres Beispiel für eine Numerische Aggregation vorstellen.
Diesmal geht es um die Berechnung von Durchschnittswerten...

Im ersten Teil dieser Serie hatte ich ein Beispiel für einen Min/Max/Count MapReduce-Algorithmus vorgestellt.

Diesmal möchte ich die Sammlung um ein Beispiel für eine Durchschnittswertberechnung erweitern.

 

Beispiel "Average"

Bei diesem Beispiel soll die durchschnittliche Kommentarlänge pro Stunde errechnet werden.

Hierzu extrahiert die Mapper-Funktion die Werte der CreationDate- und Text-Attribute der XML-Quelle und gibt den Stunden-Wert, sowie die Textlängem als Key/Value-Paare zurück:

public class AverageMapper 
  : JsonOutMapperBase<CountAverageData>
{
  public override void Map(string inputLine, 
    JsonMapperContext<CountAverageData> context)
  {
    var parsed = XmlUtils.ParseXml(inputLine);

    if (parsed == null 
        || !parsed.ContainsKey("CreationDate") 
        || !parsed.ContainsKey("Text"))
    {
      context.CoreContext.IncrementCounter(
        "Average Mapper", "Invalid Rows", 1);
      return;
    }

    DateTime creationDate;
    if (!DateTime.TryParse(parsed["CreationDate"], out creationDate))
    {
      context.CoreContext.IncrementCounter(
        "Average Mapper", "Invalid Creation Dates", 1);
      return;
    }

    var text = parsed["Text"];

    context.EmitKeyValue(
      creationDate.Hour.ToString(CultureInfo.InvariantCulture),
      new CountAverageData
      {
        Average = text.Length,
        Count = 1,
      });
  }
}

 

Die Combiner- und Reducer-Funktionen aggregieren anschließend die entsprechenden Werte.

Damit die Werte durch die Combiner-Funktion nicht verfälscht werden, muss die Summe mittels der Anzahl der Datensätze rekonstruiert werden.

public class AverageCombiner 
  : JsonInOutReducerCombinerBase<CountAverageData, CountAverageData>
{
  public override void Reduce(string key, 
    IEnumerable<CountAverageData> values, 
    JsonReducerCombinerContext<CountAverageData> context)
  {
    float sum = 0;
    float count = 0;

    foreach (var value in values)
    {
      sum += value.Count * value.Average;
      count += value.Count;
    }

    context.EmitKeyValue(key, new CountAverageData
    {
      Average = sum / count,
      Count = count,
    });
  }
}

 

Der Reducer liefert erneut die Ausgabewerte - zur späteren Weiterverarbeitung - im Tab-getrennten Format zurück.

public class AverageReducer
  : JsonInReducerCombinerBase<CountAverageData>
{
  public override void Reduce(string key, 
    IEnumerable<CountAverageData> values, 
    ReducerCombinerContext context)
  {
    float sum = 0;
    float count = 0;

    foreach (var value in values)
    {
      sum += value.Count * value.Average;
      count += value.Count;
    }

    context.EmitKeyValue(key, new CountAverageData
    {
      Average = sum / count,
      Count = count,
    }.ToString());
  }
}

 

public class CountAverageData
{
  public float Average { get; set; }
  public float Count { get; set; }

  public override string ToString()
  {
    return String.Format("{0}\t{1}", Average, Count);
  }
}

 


Weitere Informationen Weitere Informationen:

Andreas Wolter: SQL Server Database Ownership: survey results & recommendations

 SQL Server Datenbankbesitz: Umfrageergebnisse und Empfehlungen

 

(en)
You may remember the survey on database ownership which I launched several months ago.

In the following, I am now presenting the results and giving my official recommendation for a best practice for security in terms of database ownership.

(de)
Ihr erinnert Euch vielleicht an die Umfrage zu Datenbankbesitz, die ich vor einigen Monaten gestartet habe.
Hier präsentiere ich nun die Ergebnisse und gebe meine offiziellen Empfehlungen für Sicherheits-Best Practice hinsichtlich Datenbankbesitz.

First the results.

I received data from 58 different servers and 905 databases altogether.
That’s not bad, and sufficient for my purpose of giving you, my readers, the opportunity to find out how others configure their servers.

Many thanks to all those who submitted!

You may still share results but I can’t promise how soon I can include them. (Here is the survey plus the script for collection)
So now to the details. I put the most interesting data in charts.
The most obvious issue is that of the external owner’s account, which is most often and not very surprisingly sa:

Zuerst einmal die Ergebnisse.

Ich habe insgesamt von 58 verschiedenen Servern und 905 Datenbanken Daten erhalten.
Das ist nicht schlecht. Und es ist ausreichend für meine Zwecke – Euch, meinen Lesern, die Gelegenheit zu gegeben herauszufinden, wie andere ihre Server konfigurieren.

Vielen Dank an alle, die ihre Daten eingereicht haben!

Ihr könnt eure Ergebnisse immer noch mit mir teilen, aber ich kann euch nicht versprechen, wie bald ich sie mit einschließen kann. (Hier sind die Umfrage sowie das Skript für die Datenerfassung.)

Nun zu den Details. Ich habe die interessantesten Daten in Diagramme gesetzt.
Die offensichtlichste Frage ist die des Kontos des externen Besitzers, das am häufigsten und nicht überraschenderweise sa ist:

  SQL_Server_External_Database_Owner_pct

 

57% of all databases belong to sa himself. Actually, this is better than expected. But let’s dive further – what’s the server role behind the remaining 42%?

57% aller Datenbanken gehören sa selbst. Dies ist sogar besser als erwartet. Aber schauen wir mal genau hin – was ist die Server-Rolle hinter den verbleibenden 42%?

 SQL_Server_Role_Membership_of_Database_Owner_pct

 

Ok, that changes the picture quite a bit. Almost 80% of all Database owners are sysadmin. So that is by no means any better than sa.
Then some other accounts follow, which means those have low privileges (“excellent”), and then comes dbcreator, securityadmin, that are later followed by some other high privileged server roles, though with much less power.

So in other words: only 7% of all those databases have been looked at with security in mind by only using low privileged accounts as owners.

If you are interested in the plain numbers, here they go:

Ok, das verändert das Bild schon entscheidend. Fast 80% aller Datenbankenbesitzer sind sysadmin. Das ist also keineswegs besser als sa.
Es folgen einige andere Konten, das bedeutet, dass diese niedrige Privilegien (“hervorragend”) haben, und dann folgen dbcreator, securityadmin, auf die später einige andere hochprivilegierten Serverrollen folgen, doch mit weitaus weniger Privilegien.

Mit anderen Worten: nur 7% aller dieser Datenbanken wurden im Hinblick auf Sicherheit betrachtet, indem von Besitzern nur niedrigprivilegierte Konten verwendet wurden.

Wenn euch die genauen Zahlen interessieren, hier sind sie:

 SQL_Server_Role_Membership_of_Database_Owner_num

I did include some of the security-wise critical database- & server configurations:

  1. Is the database set to be “Trustworthy”?
  2. Is the database set to have “Database chaining on”?
  3. Is the Server set to have “cross database chaining on”?

Those are actually the even more important results.

Since the system databases need to have a different setting by default, I am excluding them, making it a total of 847 User databases.
Of which 30 have the trustworthy bit set to on, and 35 have the database chaining.
What you can’t see in this graph, but what I can tell from the raw data, is that those 30 “trustworthy” databases all are owned by a sysadmin.
And THIS now is the biggest security-hole in this area!
Here a graph on that:

Ich habe einige der sicherheitstechnisch kritischen Datenbank- und Serverkonfigurationen mit eingeschlossen:

  1. Ist die Datenbank auf „Trustworthy“ eingestellt?
  2. Ist in der Datenbank „Datenbank-Verkettung an“ eingestellt?
  3. Ist im Server „cross database chaining on“ eingestellt?

Diese sind eigentlich die wichtigeren Ergebnisse.

Da die Systemdatenbanken standardmäßig eine andere Einstellung haben müssen, schließe ich sie in meiner Bewertung aus, so dass ich auf insgesamt 847 Nutzer-Datenbanken komme.
30 von ihnen haben das Trustworth-Bit eingestellt, und 35 haben die Datenbanken-Verkettung eingeschaltet.
Was ihr in dieser Grafik nicht sehen könnt, aber was ich aus den Rohdaten erkennen kann, ist, dass diese 30 „vertrauenswürdigen“ Datenbanken alle im Besitz von einem sysadmin sind.
Und DAS ist das größte Sicherheitsloch in diesem Bereich!
Hier ein Diagramm dazu:

 

SQL_Server_Critical_Database_Settings 

In the interest of time I will focus this post on recommendation rather than explaining all the risks involved. At the end though I will provide some links for further reading.

Aus Zeitgründen werde ich diesen Eintrag auf Empfehlungen beschränken, als alle Risiken zu erklären. Am Ende werde ich jedoch einige Links für weiterführende Lektüre angeben.

Possibilities

So what are the general variations of database ownership?
Let me start with the most common and actually WORST possibilities (Yes, I mean it exactly as I say ;-) ):

  1. SA-Account
  2. Some other SQL-Account with sysadmin privileges
  3. Windows Login with sysadmin privileges

A first improvement(? – really?):

      4. Any of the above with Status = Disabled

 And then:

     5.   A ”shared” account without any special server role or permissions (aka “1 Account per Server”)

     6.   1 Account per Database

     7.    1 Account per Application

     8.   1 Account per Group of databases

 + all of them not only Disabled but with a Denied Connect-Permission

Möglichkeiten

Was sind also die allgemeinen Variationen von Datenbanken-Besitztum?

Fangen wir mit den häufigsten und eigentlich SCHLECHTESTEN Möglichkeiten an (Ja, das meine ich genau so, wie es hier steht ;-) ):

 

  1. SA-Konto
  2. Irgendein anderes SQL-Konto mit sysadmin-Privilegien
  3. Windows Login mit sysadmin-Privilegien

 Eine erste Verbesserung(? – wirklich?):

     4.   Alle der oben angegebenen mit Status = Deaktiviert

 Und dann:

     5.     Ein „geteiltes“ Konto ohne eine spezielle Serverrolle oder Rechte (Alias „1 Konto pro Server“)

     6.   1 Konto pro Datenbank

     7.   1 Konto pro Anwendung

     8.   1 Konto pro Datenbank-Gruppe

 + alle davon nicht nur Deaktiviert sondern mit einer verweigerten Verbindungs-Berechtigung 

My Recommendation:

Depending on your environment: Any of 5, 6, 7 or 8:

Create a specific Login without any extra permissions + Deny Connect.

The most simple approach and yet better than sa is: one database owner per server.
Example for (5):

  • Database1 owned by DBOwner
  • Database2 owned by DBOwner
  • Database3 owned by DBOwner

 Simple and self-explanatory.

The other extreme and most secure is: per database.
Example for (6):

  • Database1 owned by DBOwner_Database1
  • Database2 owned by DBOwner_Database2
  • Database3 owned by DBOwner_Database3
  • Database4 owned by  DBOwner_Database4

 Some applications use a number of different databases. For them it’s perfectly fine to use the same database owner account. So create an account per application.

Example for (7):

  • App1Database1 owned by DBOwner_App1
  • App1Database2 owned by DBOwner_App1
  • App2Database1 owned by DBOwner_App2
  • App2Database owned by  DBOwner_App2

 Another approach is kind of a compromise between 1 Database-Owner Account per Server and One per database: Define the level of security needed per database. Then create a dedicated account for the most critical Databases. And for the others use a shared owner/account, possibly divided in 2 or more groups.

Example for (8):

  • CriticalDatabase1 owned by DBOwner_Level1Dedicated1
  • CriticalDatabase2 owned by DBOwner_ Level1Dedicated2
  • Level2Database1 owned by DBOwner_Level2
  • Level2Database2 owned by DBOwner_Level2

 I hope my samples give you an idea. :-)

So why this effort?
Let me put it this way: ”Why not sa?”.
First: If you think about it, it actually makes little sense that the highest privileged account in SQL Server is being recommended by so many, even professionals + in Whitepapers (!) – when security is the focus. It is really wrong, as wrong as it could possibly get.
I mean, as you can see, there are other options out there.
The top reason why SA keeps getting recommended is administration itself: It eases the setup for failover and regular database restores, since SA is always available at any server and hence a broken database owner can be avoided with almost no extra work.
But that’s “only” from a perspective of maintenance.
With regard to security it is totally on contrary to the Principle of least privilege.

It may not matter a lot, if everything else is tightened, but that’s hardly a thing to rely on especially in bigger environments where things change and many people have access and permissions to.
Especially in the context of the trustworthy-setting for a database, this completely opens the system for privilege escalation attacks from inside. It is then a piece of cake to gain system level permissions once you are for example in the db_owner database group – like many applications are, if they are not sysadmin already.
- Remember: the owner of a database cannot be denied anything inside and with his database. So he can change structure, create backups, break log-backup-chain and also drop it completely.

And since the attack starts from inside, it really doesn’t matter whether the sa/sysadmin account is disabled as you may now realize.

Having a dedicated account with zero special permissions as database owner prevents database principals from gaining system level permissions as a sysadmin has, even in the case of the database being trustworthy.
And trustworthy is one of the dirty little shortcuts for developers implementing CLR code inside the database and avoiding the hassle of having to use certificates under certain conditions. The same is often done for code that needs to get server-level data from inside the database.

Meine Empfehlung:

Abhängig von eurer Umgebung: eine von 5, 6, 7 oder 8:

Ein spezifisches Login errichten ohne extra  Rechte + Deny Connect.

Die einfachste Herangehensweise und doch besser als sa ist: eine Datenbank pro Server.

Beispiel für (5):

  • Datenbank1 im Besitz von DBOwner
  • Datenbank2 im Besitz von DBOwner
  • Datenbank3 im Besitz von DBOwner

 Einfach und selbsterklärend.

 Das andere Extrem und dabei die sicherste ist: pro Datenbank.

Beispiel für (6):

  • Datenbank1 in Besitz von DBOwner_Database1
  • Datenbank2 in Besitz von DBOwner_Database2
  • Datenbank3 in Besitz von DBOwner_Database3
  • Datenbank4 in Besitz von DBOwner_Database4

Einige Anwendungen verwenden eine Reihe von unterschiedlichen Datenbanken. Für sie ist es völlig ausreichend, das gleiche Datenbankbesitzerkonto zu verwenden. Erstellt also ein Konto pro Anwendung.

Beispiel für (8):

  • App1Database1 in Besitz von DBOwner_App1
  • App1Database2 in Besitz von DBOwner_App1
  • App2Database1 in Besitz von DBOwner_App2
  • App2Database in Besitz von DBOwner_App2

Eine andere Herangehensweise ist eine Art Kompromiss zwischen 1 Datenbankenbesitzerkonto pro Server und einem pro Datenbank: Definiere das Sicherheitslevel, das je Datenbank gebraucht wird. Dann erstelle ein spezielles Konto für die kritischsten Datenbanken. Und für die anderen Besitzer einen gemeinsamen Besitzer-/Konto verwenden, möglicherweise in 2 oder mehr Gruppen geteilt.

Beispiel für (7):

  •  CriticalDatabase1 in Besitz von DBOwner_Level1Dedicated1
  • CriticalDatabase2 in Besitz von DBOwner_ Level1Dedicated2
  • Level2Database1 in Besitz von DBOwner_Level2
  • Level2Database2 in Besitz von DBOwner_Level2

 Ich hoffe, meine Beispiele geben euch eine Vorstellung. :-)

Aber warum diese Mühe?
Lasst es mich so ausdrücken: “Warum nicht sa?”
Zuallererst: Denkt man darüber nach, ergibt es eigentlich wenig Sinn, dass das höchstprivilegierte Konto beim SQL Server von so vielen empfohlen wird, selbst von Profis + in Whitepapers (!) – wenn Sicherheit im Fokus steht. Es ist wirklich falsch, so falsch wie es nur irgend sein kann.

Schließlich gibt es da draußen, wie ihr sehen könnt, noch andere Optionen.

Die Grund Nr. 1, warum SA immer wieder empfohlen wird, ist die Administration selbst: Es erleichtert das Einrichten für Failover und regelmäßige Datenbankenwiederherstellungen, da SA immer auf jedem Server verfügbar ist und damit ein kaputter Datenbankbesitzer mit wenig zusätzlichem Aufwand verhindert werden kann.
Aber das ist „nur“ aus Sicht der Wartung.
Was die Sicherheit angeht, steht es völlig im Gegensatz zum Prinzip des geringsten Privilegs.

Es mag nicht viel ausmachen, wenn alles andere straff sitzt, aber darauf sollte man sich nicht verlassen, besonders in größeren Umgebungen, wo sich Dinge ändern und viele Leute Zugriff und Befugnisse haben.

Besonders im Kontext der Trustworthy-Einstellung für eine Datenbank öffnet dies das System komplett für privilege escalation-Angriffe von innen. Dann ist es ein Kinderspiel, Systemlevel-Befugnisse zu erlangen, wenn man einmal z.B. in der db_owner Datenbankengruppe ist – wie es viele Anwendungen sind, wenn sie nicht bereits sysadmin sind.

Denkt dran: dem Datenbankenbesitzer kann weder innen noch mit seiner Datenbank etwas verweigert werden. Er kann also die Struktur verändern, Backups erstellen, Log-Backup-Chain brechen und sie auch komplett löschen.

Und da der Angriff von innen anfängt, ist es wirklich egal, ob das sa/sysadmin Konto deaktiviert ist, wie ihr jetzt realisiert haben werdet.
Ein spezielles Konto mit Null speziellen Befugnissen als Datenbankbesitzer zu haben hindert Datenbank-Prinzipale daran, System-Level-Befugnisse zu erlangen, wie sie ein sysadmin hat, selbst in dem Fall, dass die Datenbank vertrauenswürdig ist.
Und „trustworthy“ ist eine der unsauberen kleinen Abkürzungen für Entwickler, die CLR-Code im Innern der Datenbank ausführen und sich dabei die Umstände sparen, unter bestimmten Bedingungen Zertifikate benutzen zu müssen. Dasselbe wird oft für Code gemacht, der Server-Level-Daten aus dem Innern der Datenbank erreichen muss.

Call for actions:

Check your databases. You can find my script here: Security-Check-Script & Survey: SQL Server Security - Database-Owners, critical Permissions and role membership
Now when you start with securing your databases from database-ownership standpoint, you have to make sure that the very account does exist at any sever where this database gets restored/failed over. Usually you will have a technique in place already to synchronize your server-level principals to your other servers. So this is just one or several more of them.

Also make sure you fully understand your environment and possibly application needs before you just change the owner of your databases. You can start by reading through the links at the bottom.

Vote for an improvement in SQL Server:
I have created a suggestion as Connect Item which tackles this problem. My idea is having Microsoft include a special “DBOwner” Account at server level by default, which not only pre-exists and has not permissions, but also never compares to another. I think this would make it much easier to get rid of the habit of “sa” everywhere by also making it simple to maintain.
Please vote here: Providing a special Server principal for Database Ownership

Handlungsaufruf:

Überprüft eure Datenbanken. Ihr könnt meinen Skript hier finden: Sicherheitsprüfungs-Script & Umfrage: SQL Server Datenbankbesitzer, kritische Rechte und Rollenmitgliedschaft

Wenn ihr jetzt anfangt, eure Datenbanken aus der Perspektive von Datenbanken-Besitz zu sichern, müsst ihr dabei sicherstellen, dass dasselbe Konto auf jedem Server existiert, wo diese Datenbank wiederhergestellt/failed over wird. Normalerweise werdet ihr bereits eine Technik haben, wie ihr eure Server-Level-Prinzipale mit euren anderen Servern synchronisiert. Das sind also nur eine oder einige mehr davon.

Stellt außerdem sicher, dass ihr eure Umgebung und möglicherweise Anwendungsbedürfnisse vollständig versteht, bevor ihr den Besitzer eurer Datenbanken einfach ändert. Ihr könnt damit anfangen, indem ihr euch unten aufgelisteten Links durchlest.

Abstimmen für eine Verbesserung im SQL Server:
Ich habe einen Vorschlag als Connect Item erstellt, der dieses Problem behandelt. Meine Vorstellung ist es, Microsoft dazu zu bringen, standardmäßig ein spezielles „DBOwner“ Konto auf Server-Level auszuliefern, das nicht nur bereits immer vorab existiert und keine Rechte hat, sondern auch nie mit anderen vergleichbar ist. Ich denke, dass dies es viel einfacher machen würde, die allgegenwärtige Gewohnheit des „sa“ loszuwerden und es gleichzeitig auch einfach Wartbar machen würde.
Bitte hier Eure Stimme abgeben: Providing a special Server principal for Database Ownership

I hope this was helpful.

If you have any questions feel free to comment.
Let me finish up with some links for further readings:

Ich hoffe, das war hilfreich.

Wenn ihr noch Fragen habt, kommentiert gern.
Zum Abschluss einige Links für weiterführende Lektüre:

 

Highly recommended reading:

Dringend empfohlen:

Giving Permissions through Stored Procedures
Ownership Chaining, Certificates and the Problematic EXECUTE AS from Erland Sommarskog

More on Disabling and Deny Connect:

Mehr zu…

DISABLE and DENY LOGIN, DENY USER & Effect on Impersonation and Permissions

More on trustworthy:

 

The TRUSTWORHY bit database property in SQL Server 2005

TRUSTWORTHY Database Property

Extending Database Impersonation by Using EXECUTE AS

Discussions:

Database/Object Ownership Misalignment

database ownership - sa disabled

 

Happy Securing

 

Andreas

Sascha Dittmann: MapReduce Entwurfsmuster - Numerische Aggregation (Min/Max/Count)

MapReduce Entwurfsmuster - Einfache AggregationIn den letzten Monaten ist es leider etwas ruhig auf meinem Blog geworden, was zum größten Teil an meinem neuen Freizeitprojekt - den Azure Management Apps - lag.
Um diese Stille zu durchbrechen, möchte ich eine kleine Serie mit MapReduce Entwurfsmustern für Microsoft HDInsight starten...

Beginnen möchte ich mit einem sehr einfachen Entwurfsmustern aus dem Bereich "Numerische Aggregation".

Hierbei werden die Datensätze nach einem bestimmtem Feld gruppiert und Aggregate von einem oder mehreren anderen Feldern gebildet.
Beispiele für diese Aggregate sind Summen, Standardabweichungen, das Zählen von Datensätzen, Mindest-, Maximal-, Median- oder Durchschnittswerte.

 

Voraussetzungen

Das hier vorgestellt Entwurfsmuster kann genutzt werden, wenn folgende Voraussetzungen erfüllt sind:

  • Es handelt sich um numerische Daten
  • Die Daten können nach einem bestimmten Feld gruppiert werden

 

Musterstruktur

  • Mapper
    Die Mapper-Funktion gibt bei diesem Entwurfsmuster als Key das Feld zurück, nachdem gruppiert werden soll, und als Value eine Liste der zu aggregierenden Felder.
  • Combiner
    Die Combiner-Funktion stellt hier eine ideale Möglichkeit bereit die gesammelten Werte vorzuaggregieren. Somit wird der Datentransfer zwischen den Clusterknoten minimiert.
  • Partitioner
    Bei besonders riesigen, unausgewogenen Datenmengen können Numerische Aggregationen durch den Einsatz eines benutzerdefinierten Partitioners profitieren.
    Dieser muss dann die Daten möglichst gleichmäßig auf die Reducer verteilen.
    Da diese Art von Datenquellen allerdings recht selten vorkommen, ist in den meisten Fällen der damit erreichte Zeitvorteil sehr gering.
  • Reducer
    Die Reducer-Funktion führt dann die eigentlichen Aggregationen durch und gibt als Key das Feld nachdem gruppiert wurde, und als Value die Liste der aggregierten Felder zurück.

 

Mögliche Einsatzszenarien

  • Wörter Zählen
    Wörter Zählen – das "Hello World"-Beispiel von MapReduce - ist eine Numerische Aggregation.
    Hier splittet die Mapper-Funktion die vorhandenen Texte in ihre einzelnen Wörter auf und gibt jedes Wort als Key und eine "1" als Value zurück.
    Die Combiner- und Reducer-Funktion summiert diese Values anschließend auf.
  • Anzahl von Datensätzen 
    Ein oft genutztes Verfahren, um ein besseres Verständnis für die Datenmengen in einem bestimmten Intervall zu erhalten, wie beispielsweise Wöchentlich, Täglich, Stündlich, etc..
  • Min. / Max. / Anzahl
    Mit dem zusätzlichen Mindest- und Maximalwert können auch dynamische Intervalle dargestellt werden, wie beispielsweise das Datum des erster und letzten Forumbeitrags eines Benutzers und die Gesamtzahl der Beiträge in diesem Zeitfenster.
  • Durchschnitt / Medianwert / Standardabweichung
    Die Implementierung dieser drei Werteberechnungen ist nicht ganz so trivial wie bei den vorangegangenen Beispielen.
    Der Einsatz einer Combiner-Funktion ist zwar möglich, macht allerdings der Algorithmus komplexer.
    Dazu mehr in den kommenden Blog Posts. 

 

Hinweise zu den Beispielen dieser Artikelserie

Wenn immer möglich nutze ich Visual Studio und C# für die Beispiele dieser Artikelserie.
In besonderen Fällen, wie beispielsweise bei der Erstellung von benutzerdefinierten Partitionern oder der Nutzung von besonderen Hadoop-Eigenschaften, werde ich auf Java zurückgreifen.

Als Datenbasis verwende ich einen Auszug von Stack Overflow der hier heruntergeladen werden kann.
Diese Daten stehen unter der Creative Commons BY-SA 3.0 Lizenz.

Zur Vereinfachung habe ich die Algorithmen zusammen mit der jeweiligen Job-Konfiguration in gemeinsame Konsolenprojekte gepackt.
In der Praxis sollten die Algorithmen in separate Klassenbibliotheken eingebettet werden.

Um Boilerplate-Code für die Nutzung des Hadoop-Streaming-APIs zu vermeiden, nutze ich das NuGet Package Microsoft .NET Map Reduce API For Hadoop.

Den Source Code werde ich auf hier auf GitHub veröffentlichen.

 

Beispiel "Min / Max / Count"

Das erste Code-Beispiele berechnet die Anzahl der Kommentare pro Benutzer in einem dynamischen Zeitintervall.

Hierzu extrahiert die Mapper-Funktion die Werte für UserId und CreationDate aus der XML-Quelle und gibt diese als Key/Value-Paare zurück:

public class MinMaxCountMapper : JsonOutMapperBase<MinMaxCountData>
{
  public override void Map(string inputLine, JsonMapperContext<MinMaxCountData> context)
  {
    var parsed = XmlUtils.ParseXml(inputLine);

    if (parsed == null 
        || !parsed.ContainsKey("CreationDate") 
        || !parsed.ContainsKey("UserId"))
    {
      context.CoreContext.IncrementCounter(
        "Min Max Count Mapper", "Invalid Rows", 1);
      return;
    }

    DateTime creationDate;
    if (!DateTime.TryParse(parsed["CreationDate"], out creationDate))
    {
      context.CoreContext.IncrementCounter(
        "Min Max Count Mapper", "Invalid Creation Dates", 1);
      return;
    }

    context.EmitKeyValue(parsed["UserId"], new MinMaxCountData
    {
      Min = creationDate,
      Max = creationDate,
      Count = 1,
    });
  }
}

 

Um einen eleganten Weg aufzuzeigen, wie man mit dem NuGet Package Microsoft .NET Map Reduce API For Hadoop auch komplexe Datenstrukturen übertragen kann, habe ich statt der üblichen MapperBase-Klasse diesmal von der JsonOutMapperBase-Klasse abgeleitet.

Diese bietet eine automatische Serialisierung ins JSON-Format an (Üblich wäre eine einfache Wertetrennung mittels Tabs).

Die hierbei verwendete Datenklasse sieht dabei wie folgt aus:

public class MinMaxCountData
{
  public DateTime Min { get; set; }
  public DateTime Max { get; set; }
  public long Count { get; set; }

  public override string ToString()
  {
    return String.Format("{0}\t{1}\t{2}", Min, Max, Count);
  }
}

 

Die Combiner- und Reducer-Funktionen aggregieren anschließend die entsprechenden Werte.

public class MinMaxCountCombiner :
  JsonInOutReducerCombinerBase<MinMaxCountData, MinMaxCountData>
{
  public override void Reduce(string key, 
    IEnumerable<MinMaxCountData> values, 
    JsonReducerCombinerContext<MinMaxCountData> context)
  {
    var data = values.ToList();

    context.EmitKeyValue(key, new MinMaxCountData
    {
      Min = data.Min(v => v.Min),
      Max = data.Max(v => v.Min),
      Count = data.Sum(v => v.Count),
    });
  }
}

 

Um die Daten später weiterverarbeiten zu können, gebe ich die Werte beim Reducer wieder im üblichen Tab-getrennten Format zurück.

public class MinMaxCountReducer : 
  JsonInReducerCombinerBase<MinMaxCountData>
{
  public override void Reduce(string key, 
    IEnumerable<MinMaxCountData> values, 
    ReducerCombinerContext context)
  {
    var data = values.ToList();

    context.EmitKeyValue(key, new MinMaxCountData
    {
      Min = data.Min(v => v.Min),
      Max = data.Max(v => v.Min),
      Count = data.Sum(v => v.Count),
    }.ToString());
  }
}

 

Ausführen im Hadoop Emulator

Um jetzt den Code im lokalen Hadoop Emulator auszuführen, kann zum Einen die im NuGet-Package mitgelieferte MRRunner.exe genutzt oder zum Anderen eine Konsolenapplikation erstellt werden:

public class MinMaxCountJob 
  : HadoopJob<MinMaxCountMapper, MinMaxCountCombiner, MinMaxCountReducer>
{
  public override HadoopJobConfiguration Configure(ExecutorContext context)
  {
    return new HadoopJobConfiguration
    {
      InputPath = "/samples/comments",
      OutputFolder = "output/MinMaxCount"
    };
  }
}

static void Main(string[] args)
{
  var hadoop = Hadoop.Connect();
  hadoop.MapReduceJob.ExecuteJob<MinMaxCountJob>();
}

 


Weitere Informationen Weitere Informationen:

Christoph Muthmann: SQL Server AlwaysOn Availability Groups Supported between Microsoft Azure Regions

Friday Microsoft announced SQL Server AlwaysOn Availability Groups Supported between Microsoft Azure Regions. This is based on Azure’s new support for VNET-to-VNET connectivity.

Full story »

Philipp Lenz: Automatisiert Adressen in Geo-Daten umwandeln und die Entfernung zu einem Standort berechnen

WeltkugelImmer wieder habe ich das Problem, dass ich haufenweise Adressen habe und diese bspw. in einem Report (Reporting Services) verarbeiten muss. D.h. ich brauche Geo-Daten und will diese natürlich nicht Datensatz für Datensatz in Längen- und Breitengrad übersetzen. In diesem Artikel will ich kurz beschreiben, wie eine CSV Datei mit Adressen automatisch mit den Google Webdiensten in Längen- und Breitengrad übersetzt wird und diese Daten dann im SQL Server wiederverwendet werden können.

Beispiel: Eine CSV Datei mit verschiedenen Imbiss Buden aus Koblenz (via den Gelben Seiten abgefragt):

2014-06-21_15-17-21

Via PHP wurde nun die Datei zeilenweise ausgelesen und die Adresse wurde an Google gesendet:2014-06-21_15-20-19

Die Ausgabe des Scripts ergibt folgendes:2014-06-21_15-22-20

Diese Daten können nun super per SSIS in die Datenbank eingelesen werden – natürlich kann man auch gleich die Daten in den SQL Server, bzw. die Datenbank schreiben. Die Tabelle dazu:
2014-06-21_15-24-48

Und nun brauchen wir noch eine Tabelle wo wir unseren eigenen Standort reinschreiben:
2014-06-21_15-25-29

Nun haben wir eine Tabelle in der die Adressen sind und auch eine Tabelle wo unser Standort enthalten ist. Über je ein Update Statement lassen wir gleich das GEO-Feld in den beiden Tabellen füllen (aus Längen- und Breitengrad) und über die STDistance Funktion können wir dann die jeweilige Entfernung zwischen dem Standort und der jeweiligen Adresse finden – so finden wir den nächsten Imbiss :-)

UPDATE dbo.Orte SET geo = GEOGRAPHY::STPointFromText(‘POINT(‘ + CONVERT(VARCHAR, laengengrad) + ‘ ‘ + CONVERT(VARCHAR, breitengrad) + ‘)’,4326)

UPDATE meinStandort SET geo = GEOGRAPHY::STPointFromText(‘POINT(‘ + CONVERT(VARCHAR, laengengrad) + ‘ ‘ + CONVERT(VARCHAR, breitengrad) + ‘)’,4326)

SELECT name, orte.adresse, orte.geo.STDistance(ms.geo) As [Entfernung In Meter] FROM dbo.Orte CROSS JOIN meinStandort ms

Sascha Lorenz: SQL Server PowerShell ? Lohnt es sich überhaupt damit zu beschäftigen ?

Diese Frage wurde mir in den letzten Tagen häufig gestellt, als es sich herumgesprochen hat, dass ich auf dem SQLSaturday #313 einen Crashkurs PowerShell für SQL Server Administratoren halte.

Und warum gerade ich? In den letzten Jahren bin ich ja eher nicht durch das Thema Administration in der Öffentlichkeit aufgefallen. Bin doch eher der BI Entwickler, welcher durch konsequente Nutzung der Möglichkeiten vieles im BI Projekt als auch im späteren Betrieb automatisiert.

Da PowerShell vereinfacht gesagt eine Shell ist, mit welcher das .NET Framework sehr komfortabel genutzt werden kann, bin ich wieder voll im Spiel und gefühlt ist meine SQLSat Session quasi die Fortsetzung vieler meiner Community-Projekte und Vorträge in denen ich mit .NET Bibliotheken gearbeitet habe.

Nur warum PowerShell für den SQL Server? “Wir” haben doch T-SQL und eine Menge an Komfortfunktionen rund um den Betrieb eines SQL Servers.

Da schaut es bei den Kollegen für Exchange Server oder SharePoint Farmen schon ganz anders aus. Die können sich mittlerweile ohne PowerShell gefühlt keinen Meter bewegen. Viele Funktionen sind, so wurde mir zugetragen, auch NUR noch über PowerShell erreichbar. Daher ist in diesen Communities das Thema auch wesentlich populärer als bei “uns”.

Also, wozu braucht nun ein SQL Administrator PowerShell?

Brauchen zurzeit noch nicht zwingend, dennoch ist es schon äußerst nützlich und zwar immer dann, wenn etwas automatisiert werden soll, was außerhalb des SQL Servers liegt. Oder wenn einfach mal der SQL Server Dienst selbst an der Reihe ist.

Neben meinem Community Leben beschäftige ich mich sehr intensiv mit Administration. Meine Kollegen bei der PSG verantworten große und komplexe SQL Server Umgebungen, welche zum Teil auch global und rund um die Uhr genutzt werden. Da ist das Thema Standardisierung und Automatisierung sehr wichtig, um einen konstant robusten Betrieb zu ermöglichen. Und der Betrieb betrifft häufig auch das Betriebssystem, Storage-Systeme, AD-Gruppen, Virtualisierung etc. pp. Vieles ließe sich sehr wahrscheinlich mit ein wenig Kreativität nativ in T-SQL lösen (und ich hätte einen Wahnsinnsspaß mir das auszudenken), nur hat sich um die SQL Server Welt herum im Microsoft Eco-System PowerShell als das Allzweckwerkzeug für viele Aufgabenstellungen entwickelt.

Und dann, dann ist da noch die Cloud. Ja, die Cloud. Und so richtig sinnvoll werden Szenarien in der Cloud erst, wenn der Grad an Automatisierung sehr hoch ist. Ich weiß, dass bei dem Thema noch viele Mitglieder aus der Community zusammenzucken und ein leises “das wird nie bis zu mir kommen…” von sich geben. Dennoch stelle ich in der Zusammenarbeit mit Microsoft fest, dass immer mehr Kunden die Einsatzmöglichkeiten von Azure prüfen oder gar schon am implementieren sind. Und auch hier ist eine Automatisierung der Provisionierung von Cloud Diensten am einfachsten mit PowerShell zu erreichen.

PowerShell soll kein Ersatz für das administrative T-SQL sein und vieles lässt sich auch sehr gut wenn nicht sogar einfacher damit abbilden. Dennoch ist PowerShell eine gute Ergänzung für die tägliche Arbeit und ermöglicht neue einfachere Lösungen.

Gebt PowerShell eine Chance, schaut Euch die grundlegenden Konzepte an und entscheidet dann.

Christoph Muthmann: Dynamisches Pivot

Nicht immer sind die Spalten fest definiert, nach denen man eine Tabelle pivotieren möchte. Falls die Spalten auch erst nach Anwendung anderer Kriterien feststehen, hilft nur noch dynamisches SQL um das passende Statement zur Laufzeit zu erzeugen.

Full story »

Marcel Franke: June is my conference month

It was quite a busy time for me in the last month, with lot’s of very interesting project and exciting customers, especially in there area of Big Data and Data Analytics. That’s why it’s a little bit quite currently on my blog. Apologizes for this, but I will keep up writing. Additionally June seems to be my conference month for this year and I will be happy if I could see and talk to some of you. So here is my current schedule:

TDWI Conference 2014, 23. – 25.06.

I will have a session together with Ralph Kemperdick from Microsoft on “Analytical Platform System (former known as PDW) – Real World Experiences”

 

SQL Saturday 313, 28.6.

I’m very happy that I have again the chance to present on the SQL Saturday in Germany. Also this year the SQL Saturday will be at the Hochschule Bonn-Rhein-Sieg. And my Session will be about “Comparing SAP HANA, Power Pivot and SQL Server – In-Memory-Technologies”.

 

Datalympics 2014, 02.07.

This is a new conference to me, where I’m very excited about and happy to give a speech. My session will be on “Analytical Powerhouse − Data Analytics based on Microsoft”.

 

I think this will be a very exciting weeks and after my vacation I will follow up on my blogs.


Filed under: Uncategorized

Christoph Muthmann: Informatik Aktuell

Heute wagen wir mal einen Blick über den Zaun in ansonsten fremde Gefilde.

Full story »

Tillmann Eitelberg: SQLSaturday

In den letzten Monaten konnten Oliver (t: @oengels) und ich mit dem SQLSaturday #280 (Wien), #267 (Lissabon) und #281 (Edinburgh) wieder an verschiedenen SQLSaturday bei unseren euopäischen PASS Freunden teilnehmen. Mit unseren Vorträgen “Siena calling! Project Siena a RBID App?”, “Power BI – The self service BI Lifecycle in the cloud” und “Web Analytics with […]

Torsten Schuessler: Microsoft SQL Server 2012 Service Pack 2

The Service Pack 2 for Microsoft SQL Server 2012 (Build 11.0.5058) is available:

Bugs that are fixed in SQL Server 2012 Service Pack 2

Download:

Microsoft® SQL Server® 2012 Service Pack 2 (SP2)

I wish you a nice day,
tosc

Torsten Schuessler

Sascha Lorenz: SQLSaturday #313 - PowerShell Crashkurs – Early Access

Wie schon gepostet, bin ich beim SQLSaturday #313 dabei.

Daher möchte ich die Gelegenheit nutzen und beim nächsten Treffen der Hamburger SQL Server Community am 12.6. bei Microsoft eine Vor-Version meines PowerShell Vortrags geben. Ich würde mich sehr freuen, wenn wieder viele von Euch dabei sind und mir Feedback geben.

Wir treffen uns am 12. Juni 2014 um 18:30 bei:

Microsoft Deutschland GmbH
Geschäftsstelle Hamburg
Gasstraße 6a
22761 Hamburg

Kostenlose Parkplätze befinden sich hinter dem Gebäude. Der Parkplatz ist über die Rampe mit dem Schild "Microsoft Kunden" erreichbar.
Nur wenige Minuten zu Fuß ist der S-Bahnhof Bahrenfeld entfernt (S1/S11).

Wir (PASS Hamburg) bitten um eine vorherige Anmeldung per Email an: slo@sqlpass.de, damit wir die Größe des benötigten Konferenzraums vorher abstimmen können. Es haben sich schon einige Teilnehmer angemeldet.

Christoph Muthmann: BIG DATA Hackathon Rheinland

Free Community Event

The Big Data Hackathon Rheinland is your opportunity to assemble a team, learn and share, make new friends, and hack for fun and a good cause.

Full story »

Uwe Ricken: Sortierungskonflikte – Auswirkungen auf Ausführungspläne

Erst im letzten Artikel “Warum korrekte Datentypen für WHERE-Klauseln wichtig sind” habe ich die Auswirkungen von erforderlichen Typenkonvertierungen auf das Ausführungsverhalten beschrieben. Kaum geschrieben kam dann auch ein “echter” Fall diese Woche, der zunächst unerklärlich war; ein Blick auf die Ausführungspläne hat dann aber sehr schnell gezeigt, dass ein falsch gelöster “Sortierungskonflikt” die Ursache für das sehr schlechte Ausführungsverhalten der Abfrage war.

Problembeschreibung

In einem Projekt, in dem ich schon ein paar Monate für die Optimierung der Workloads arbeite, werden mittels Prozeduren die Workloads in kleine – überschaubare – Teilkomponenten zerlegt, die - in temporären Tabellen abgelegt – anschließend in einer abschließenden Abfrage zu einem Ergebnis führen, das an die Anwendung geliefert wird. Eine Prozedur ist als “langsam” aufgefallen, die sich von den anderen Abfragen deutlich unterschied – der JOIN wurde nicht über einen numerischen Datentypen sondern über einen Textdatentypen durchgeführt. Die Produktionsdatenbank hatte eine von der Standardsortierung (Server) unterschiedliche Einstellung für die Sortierung.

Beschreibung der Testumgebung

Um das Verhalten nachzustellen, wird eine neue Datenbank mit einer von der Standardsortierung (auf dem Beispielserver “Latin1_General_CI_AS”) unterschiedlichen Sortierung erstellt. In dieser Datenbank wird anschließend eine Tabelle [dbo].[Customers] angelegt, die mit 1.000 Datensätzen gefüllt wird. In der Tabelle gibt es ein Attribut [Customer_Number], für das ein UNIQUE Index für eine bessere Ausführungsgeschwindigkeit erstellt wird. Um die Standardsortierung für Server und Systemdatenbank TEMPDB auszugeben, kann folgende Abfrage verwendet werden:

-- Welche Sortierung für TEMPDB
SELECT  name                        AS DatabaseName,
        collation_name              AS DatabaseCollation,
        SERVERPROPERTY('Collation') AS ServerCollation
FROM    sys.databases AS D
WHERE   D.database_id = 2;

Erstellen der Testumgebung


Zunächst wird eine neue Datenbank mit der Sortierung Latin1_General_BIN erstellt:



CREATE DATABASE demo_db
ON PRIMARY
(
    Name = 'demo_db',
    FILENAME = 'S:\MSSQL11.SQL_2012\MSSQL\DATA\\demo_db.mdf',
    SIZE = 10MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 10MB
)
LOG ON
(
    NAME = 'demo_log',
    FILENAME = 'S:\MSSQL11.SQL_2012\MSSQL\DATA\\demo_db.ldf',
    SIZE = 10MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 10MB
)
COLLATE Latin1_General_BIN;
GO

Anschließend wird in der zuvor angelegten Datenbank [demo_db] eine neue Tabelle [dbo].[Customers] angelegt:



USE demo_db;
GO
 
CREATE TABLE dbo.Customers
(
    Customer_Id      INT             NOT NULL    IDENTITY (1, 1),
    Customer_Number  CHAR(5)         NOT NULL,
    Customer_Name    VARCHAR(255)    NOT NULL,
 
    CONSTRAINT pk_Customers_Id PRIMARY KEY CLUSTERED (Customer_Id)
);
GO
 
-- Additional index on Customer_Number
CREATE UNIQUE INDEX ix_Customers_Number ON dbo.Customers (Customer_Number) INCLUDE (Customer_Name);
GO

Hinweis: Die Datenbank verwendet eine Sortierung, die Groß- und Kleinschreibung unterscheidet. Es ist also für alle weiteren Beispiele wichtig, dass die korrekte Schreibweise beachtet wird!


Nachdem die Tabelle [dbo].[Customers] erstellt ist, können die Metadaten des Objekts überprüft werden:



SELECT  OBJECT_NAME(C.object_id)   AS Table_Name,
        C.name                     AS Column_Name,
        S.name                     AS Type_Name,
        C.column_id,
        C.max_length,
        C.collation_name
FROM    sys.columns AS C INNER JOIN sys.types AS S
        ON (C.system_type_id = S.system_type_id)
WHERE   object_id = object_id('dbo.Customers', 'U');
MetaData_01

In der Abbildung ist deutlich zu erkennen, dass numerische Datentypen unabhängig von der Sortierung sind. Textdatentypen unterliegen jedoch alle der Definition einer zuvor festgelegten Sortierung. Abschließend werden 1.000 Datensätze in die Tabelle [dbo].[Customers] eingefügt, die für die späteren Tests verwendet werden.



DECLARE @i INT = 1;
WHILE @i <= 1000
BEGIN
    INSERT INTO dbo.Customers(Customer_Number, Customer_Name)
    VALUES (CAST(10000 + @i AS CHAR(10)), 'Customer: ' + CAST(@i AS VARCHAR(10)))
 
    SET @i += 1;
END

Simulation des Workloads – Verwendung einer temporären Tabelle


Wie bereits oben beschrieben, hat man – um die Abfragen in kleinere Teilschritte zu zerlegen – den Einsatz von temporären Tabellen in Erwägung gezogen. Wie in einer Benutzerdatenbank gilt bei der Erstellung von temporären Tabellen auch, dass bei Nichtangabe einer Sortierung immer die Sortierung der Datenbank gilt, in der das Objekt erstellt wird. Für den zu simulierenden Workload wird eine temporäre Tabelle ohne explizite Spezifikation einer Sortierung erstellt und das Ergebnis zeigt, dass die Sortierung von TEMPDB übernommen wurde:



CREATE TABLE #Customer_Number (Customer_Number CHAR(5) PRIMARY KEY);
GO
 
SELECT  OBJECT_NAME(C.object_id, 2)  AS Table_Name,
        C.name                       AS Column_Name,
        S.name                       AS Type_Name,
        C.column_id,
        C.max_length,
        C.collation_name
FROM    tempdb.sys.columns AS C INNER JOIN tempdb.sys.types AS S
        ON (C.system_type_id = S.system_type_id)
WHERE   object_id = object_id('tempdb..#Customer_Number', 'U');
MetaData_02

In die Tabelle werden für die anschließenden Tests ein paar Einzelwerte eingetragen.



INSERT INTO #Customer_Number (Customer_Number)
VALUES
    ('10005'),
    ('10010'),
    ('10009'),
    ('10002');
GO

Hinweis: Für alle nachfolgenden Testszenarien werden sowohl Statistiken, Ausführungsplan und Ausführungszeit ausgegeben. Dies kann generell mit folgendem SQL-Statement zu Beginn aktiviert werden:


SET STATISTICS IO, XML, TIME ON;

Test 1 – JOIN ohne COLLATE Hinweise


Im ersten Test wird ein SELECT mit einem INNER JOIN der zuvor erstellten temporären Tabelle #Customer_Number mit der Tabelle [dbo].[Customers] ausgeführt. Das Ergebnis ist ernüchternd, da der Laufzeitfehler 468 erzeugt wird:



SELECT  C.*
FROM    dbo.Customers AS C INNER JOIN #Customer_Number AS CN
        ON (C.Customer_Number = CN.Customer_Number);

Meldung 468, Ebene 16, Status 9, Zeile 3
Ein Sortierungskonflikt zwischen 'Latin1_General_CI_AS' und 'Latin1_General_BIN' im equal to-Vorgang kann nicht aufgelöst werden.


Die Fehlermeldung ist selbsterklärend – ein JOIN kann nicht durchgeführt werden, da die beiden Sortierungen nicht kompatibel zueinander sind. Dieser Fehler ist prinzipiell der Einstieg in eine Optimierung, die – ungewollt – zu erheblichen Performanceeinbußen führt. Das zeigen die nächsten Tests.


Test 2 – JOIN mit COLLATE auf [dbo].[Customers]


Wenn Spalten nicht mit einem JOIN verbunden werden können, kann für jedes Attribut explizit mittels “COLLATE Sortierung” eine Sortierung erzwungen werden. Dieser Weg wird häufig gewählt, wenn die Metadaten der Tabellen nicht geändert werden können.



SELECT  C.*
FROM    dbo.Customers AS C INNER JOIN #Customer_Number AS CN
        ON (C.Customer_Number COLLATE Latin1_General_CI_AS = CN.Customer_Number);

StatisticsParser_01


ExecutionPlan_01


Die obigen Abbildungen zeigen sowohl das IO (Herzlichen Dank an Richie Rump für seine tolle Idee zur Darstellung von IO unter http://www.statisticsparser.com) als auch den Ausführungsplan für die Abfrage.


Auffällig bei der Analyse des Ausführungsplans ist, dass kein – effizienter – Index Seek auf [dbo].[Customers] angewendet werden kann. Ursächlich für den Index Scan ist der Umstand, dass einen Typenkonvertierung für die Spalte [Customer_Number] durchgeführt werden muss. Ebenfalls auffällig ist, dass ein – teurer – Hash Join verwendet wird, obwohl nur eine kleine Menge an Daten (5 Datensätze) zurück geliefert werden. Der Query Optimizer von Microsoft SQL Server entscheidet sich für einen Hash Join, da von der “Probe Phase” alle Datensätze erwartet werden. Dieser Umstand ist der expliziten Typenkonvertierung (durch Änderung der Sortierung) geschuldet, die durch die Angabe einer COLLATE-Anweisung hinter dem Attribut erzwungen wird. Damit wird Microsoft SQL Server gezwungen, in JEDEM Datensatz aus [dbo].[Customer] für das Attribut [Customer_Number] einen Typenkonvertierung durchzuführen.


Technisch wird im Arbeitsspeicher eine Hash-Tabelle angelegt, in der alle [Customer_Number] der temporären Tabelle abgelegt werden (Build-Phase). Anschließend vergleicht Microsoft SQL Server JEDEN Eintrag in [Customer_Number] aus [dbo].[Customers] mit den Einträgen in der Hash-Tabelle und bei Übereinstimmung wird der entsprechende Datensatz ausgewählt (Probe-Phase).


Test 3 – JOIN mit COLLATE auf [#Customer]


Eine Möglichkeit, die Laufzeit für die Abfrage deutlich zu verkürzen, ist es, den Konvertierungsvorgang von der “größeren” Tabelle [dbo].[Customers] auf die temporäre Tabelle [#Customer] zu verlagern, da in dieser Tabelle lediglich 5 Datensätze vorhanden sind.



SELECT  C.*
FROM    dbo.Customers AS C INNER JOIN #Customer_Number AS CN
        ON (C.Customer_Number = CN.Customer_Number COLLATE Latin1_General_BIN);

StatisticsParser_02


ExecutionPlan_02


Die Abbildung zeigt, dass sich auf Grund der geänderten Konstellation für die Sortierung der Ausführungsplan deutlich geändert hat. Die Anzahl des IO ist zwar höher, kann aber in diesem Fall vernachlässigt werden, da der – teure – HASH JOIN durch einen NESTED LOOP ausgetauscht wurde. Durch den NESTED LOOP werden die IO für [dbo].[Customers] funktional bedingt höher sein, da für jeden Eintrag der äußeren Tabelle [#Customer_Number] der B-Tree des verwendeten Index von [dbo].[Customer] durchsucht werden muss. Vier Werte (10002, 10005, 10009, 10010) in der “äußeren” Tabelle [#Customer_Number] erzeugen jeweils 2 IO (1 IO = Root, 1 IO = Data) für den Zugriff auf die auszugebenen Daten.


Obwohl das I/O höher ist, ändert sich die Ausführungszeit für diesen Zugriff erheblich. Die nachfolgende Abbildung zeigt das Verhältnis der Ausführungen (geschätzte Werte). Ein NESTED LOOP ist eine deutlich schnellere Operation.


ExecutionPlan_03


Unabhängig von der gewählten Variante bleibt jedoch ein Punkt festzuhalten, der immer zu beachten ist: Jede Variante hindert den Query Optimizer von Microsoft SQL Server daran, Statistiken zu verwenden, die eine optimale Abfragestrategie zulassen.


Test 4: Definition der Sortierung in der temporären Tabelle


Statt – wie in den vorherigen Beispielen gezeigt – die Sortierung in der Abfrage zu bestimmen, sollte die Sortierung bereits bei der Erstellung der temporären Tabelle definiert werden. Das folgende Beispiel zeigt, welchen Einfluss diese Variante auf den Ausführungsplan hat:



DROP TABLE #Customer_Number;
GO
 
CREATE TABLE #Customer_Number (Customer_Number CHAR(5) COLLATE Latin1_General_BIN PRIMARY KEY);
GO
 
INSERT  INTO #Customer_Number
        ( Customer_Number )
VALUES  ( '10005' ),
        ( '10010' ),
        ( '10009' ),
        ( '10002' );
 
SELECT  C.*
FROM    dbo.Customers AS C INNER JOIN #Customer_Number AS CN
        ON (C.Customer_Number = CN.Customer_Number);

Die gültige Sortierung der Datenbank wird bei der Definition der temporären Tabelle angegeben. Anschließend werden erneut die Beispieldaten geladen und die Abfrage wird ausgeführt. Da nun die Sortierung von temporärer Tabelle [#Customer_Number] identisch ist mit der verwendeten Sortierung in der Tabelle [dbo].[Customers], wird keine explizite Angabe der Sortierung mehr benötigt – der Abfrageplan zeigt diese Änderung eindrucksvoll.


ExecutionPlan_04


Das I/O hat sich gegenüber Test 3 nicht verändert (warum auch, die Abfragestrategie hat sich ja nicht geändert). Dennoch ist diese Abfrage noch einmal deutlich schneller (~90%) als die Abfrage in Test 3. Die Berechnungsoperation (COMPUTE SCALAR) ist also doch nicht so “billig”, wie im Ausführungsplan gezeigt.


Zusammenfassung


Konflikte in der Sortierung lassen sich nicht vermeiden, wenn die eigene Datenbank von der Standardsortierung von Microsoft SQL Server abweicht und mit temporären Tabellen gearbeitet wird. Um diese Konflikte zu lösen, besteht die Möglichkeit, unmittelbar mit COLLATE das Verhalten einer Abfrage zu steuern. Ist man gezwungen, die Sortierung selbst zu steuern, sollten zwei Möglichkeiten immer im Fokus stehen:



  • Definition der benötigten Sortierung bereits bei der Definition der temporären Tabelle (ideal)
  • Definition der Sortierung im JOIN auf der Seite mit der kleineren Datenmenge (in der Regel die temporäre Tabelle), um das I/O möglichst gering zu halten (INDEX SCAN).

Die oben beschriebenen Konflikte in der Sortierung gelten natürlich nicht nur für JOIN Operatoren; auch bei Einschränkungen (WHERE-Klausel) gilt es, die obigen Regeln zu beachten. Deshalb IMMER den Ausführungsplan und seine Eigenschaften im Auge behalten, wenn man den Eindruck hat, dass eine Abfrage schneller ausgeführt werden könnte – eventuell liegt ja ein Konflikt in der Sortierung vor, wenn es Textvergleiche betrifft.


Herzlichen Dank fürs Lesen!

Sascha Lorenz: SQLSaturday #313 – Rheinland 2014 – Ich bin als Sprecher dabei

Es ist schon wieder Zeit für einen SQLSaturday in Deutschland. Am 28. Juni 2014 trifft sich die SQL Server Community abermals in den Räumen der Hochschule Bonn-Rhein-Sieg

Und, ich darf wiederholt als Sprecher dabei sein. Vielen Dank!

Dieses Mal werde ich aus der Praxis meiner Kollegen bei der PSG und meinen Erfahrungen als Architekt und Coach berichten. Das Thema ist “PowerShell 101 für SQL Administratoren” und hat als Inhalt wie Administratoren diese Microsoft Technologie in Enterprise Szenarien erfolgreich einsetzen können, um den Betrieb von SQL Server Farmen und BI Umgebungen deutlich zu vereinfachen.

Wir stellen immer wieder fest, dass sich bisher nur wenige SQL Server Administratoren mit PowerShell beschäftigt haben. Das kann diverse Gründe haben. Daher möchte ich die Gelegenheit nutzen und einen Überblick und Crashkurs geben. Neben dem Fokus auf die “klassische” Administration gehe ich auch auf die Möglichkeiten für Entwickler ein, wie sie den späteren Betrieb einer Lösung mit PowerShell bereits in der Planungsphase deutlich vereinfachen können. Für den geneigten BI-Berater wird natürlich auch genug dabei sein, um zumindest die Technologie in der Zukunft bewerten zu können.

Ich freue mich auf die Veranstaltung!

Uwe Ricken: Warum korrekte Datentypen für WHERE-Klauseln wichtig sind

In einer Anfrage in den Microsoft Foren (link) ging es darum, warum Microsoft SQL Server trotz einer SEEK-Operation alle Datenseiten einer Tabelle durchsucht hat. Tatsächlich kann eine SEEK-Operation die vollständige Tabelle betreffen, wenn bestimmte Voraussetzungen nicht erfüllt sind. Wie wichtig zum Beispiel die korrekte Verwendung von Datentypen bei Einschränkungen sind, zeigt der nachfolgende Artikel.

Testumgebung

Als Beispiel soll eine Kundentabelle dienen, die Mandantenfähigkeiten simulieren soll. Das bedeutet in der Umsetzung, dass zu jedem Kunden immer eine Mandanten-Id gespeichert werden muss. Beide Attribute zusammen bilden den Clustered Key in der Tabelle. Erschwerend kommt hinzu, dass als [Customer_Id] ein Textdatentyp verwendet wird.

CREATE TABLE dbo.Customers
(
    Mandant_Id      INT        NOT NULL,
    Customer_Id     CHAR(5)    NOT NULL,
    CustomerName    CHAR(255)  NOT NULL DEFAULT ('Filler'),
 
    CONSTRAINT pk_Customers PRIMARY KEY CLUSTERED
    (
        Mandant_Id,
        Customer_Id
    )
);

Das Attribut [CustomerName] dient im Beispiel lediglich dazu, ein paar Datenseiten zu erzeugen. Für dieses Beispiel ist das Attribut [Customer_Id] besonders hervor zu heben; es handelt sich nicht um einen numerischen Datentypen sondern um einen Textdatentyp.



DECLARE @i int = 10000;
WHILE @i <= 11000
BEGIN
    INSERT INTO dbo.Customers (Mandant_Id, Customer_Id)
    VALUES (1, @i);
    SET @i += 1;
END
GO
 
ALTER INDEX pk_Customers ON dbo.Customers REBUILD;
GO

Nachdem die Tabelle aufgebaut und die Daten eingefügt wurden, belegt diese Tabelle 37 Datenseiten in der Datenbank



SELECT OBJECT_NAME(p.object_id) AS object_name,
       au.type_desc,
       au.data_pages,
       au.used_pages,
       au.total_pages
FROM   sys.allocation_units AS au INNER JOIN sys.partitions AS p
       ON (au.container_id = p.partition_id)
WHERE  p.object_id = OBJECT_ID('dbo.Customers', 'U');
GO
allocated_pages

Das Ergebnis lässt sich wie folgt interpretieren:



  • Die Daten selbst werden in 35 Datenseiten gespeichert
  • Insgesamt werden 37 Datenseiten allokiert:

    • Eine Datenseite für die IAM (Index Allocation Map)
    • Eine Datenseite für den Root-Knoten (B-Tree)

  • Da Microsoft SQL Server nur die ersten 8 Datenseiten in “mixed extents” speichert werden alle weiteren Datenseiten in “uniformed Extents” (jeweils 8 Datenseiten) gespeichert. 5 Datenseiten des 5. Extent stehen also noch für Daten zur Verfügung, bevor Microsoft SQL Server ein neues Extent allokiert.

Index_Structure


Szenario #1


Da für das Attribut [Customer_Id] nur numerische Werte vorhanden sind (unabhängig davon, dass es sich um einen Textdatentyp handelt), kommt man natürlich sehr schnell in die Verlegenheit, statt eines “Textwertes” einen numerischen Wert zu verwenden, wie das folgende Beispiel zeigt:



SET STATISTICS IO ON;
GO
 
SELECT * FROM dbo.Customers WHERE  Mandant_Id = 1 AND Customer_Id = 10001;
GO
 
SET STATISTICS IO OFF
GO

Das obige Ergebnis liefert exakt einen Datensatz zurück, da der Clustered Key der Tabelle verwendet werden kann. Ein Blick auf den Ausführungsplan zeigt einen INDEX SEEK als Operator für die Suche nach den betroffenen Datensätzen.


Execution_Plan_01


Verwendet man das SQL Server Management Studio 11.0.xxx, so wird beim SELECT-Operator ein gelbes Ausrufezeichen angezeigt; ältere Versionen von SQL Server Management Studio unterstützen diesen Fehlerhinweis leider nicht. Was genau es mit diesem Fehler auf sich hat, zeigen die IO-Statistiken für die obige Operation.



Table 'Customers'. Scan count 1, logical reads 37, physical reads 0,

Das IO zeigt einen ungewöhnlichen Wert für die SEEK-Operation; besagt dieser Wert doch, dass die vollständige Tabelle durchsucht worden ist. Die SEEK-Operation ist also vom Prinzip her nichts anderes als ein SCAN! Die Ursache dafür liegt in der Verarbeitung der WHERE-Klausel. Bewegt man die Maus auf den SEEK-Operator, werden die folgenden Informationen angezeigt:


Execution_Details_01


Auf der linken Seite werden die Eigenschaften des SEEK-Operators gezeigt. Ein erster Blick gilt der Option [Geschätzte Anzahl von Zeilen]. Der gezeigte Wert zeigt deutlich, dass die Statistiken für das verwendete Index-Objekt [pk_Customers] stimmen.


Der QueryOptimizer geht von einem Datensatz aus, den die Abfrage-Einschränkung (WHERE) eingrenzt. Tatsächlich wird nur ein Datensatz geliefert, wie die weiter oben gezeigte Eigenschaft [Tatsächliche Anzahl von Zeilen] zeigt.


Besondere Beachtung gilt den eigentlichen Suchkriterien. Während die Einschränkung auf [Mandant_ID] tatsächlich einen SEEK-Operator verwenden kann, kann das Attribut [Customer_Id] – obwohl Bestandteil des Clustered Keys – nicht davon profitieren. Kann ein Prädikat nicht in einer SEEK-Operation verwendet werden, spricht man von einem “Residual Predicate”. Der Grund dafür wird bei näherem Hinsehen sofort deutlich – Microsoft SQL Server muss einen Typenkonvertierung für das Attribut selbst durchführen!


Wäre – wie im SEEK-Prädikat ersichtlich – lediglich eine Konvertierung der Einschränkung selbst erforderlich, könnte die Einschränkung für Mandant_Id ebenfalls vom SEEK-Operator profitieren; da aber der Inhalt der Spalte selbst konvertiert werden muss, muss immer der Teil der Tabelle durchsucht werden, für die bereits auf die Mandant_Id gefilterten wurde.


Im vorliegenden Beispiel gibt es nur einen Mandanten, somit muss die vollständige Tabelle durchsucht werden! Das Microsoft SQL Server tatsächlich für die Ergebnismenge [Mandant_Id] = 1 anschließend eine Filterung durchführen muss, kann mit Traceflag 9130 sichtbar gemacht werden:



SELECT * FROM dbo.Customers WHERE Mandant_Id = 1 AND Customer_Id = 10001 OPTION (QUERYTRACEON 9130);

Execution_Plan_02


Der oben gezeigte Ausführungsplan zeigt deutlich, dass zunächst eine sehr große Datenmenge (in diesem Fall 1.000 Datensätze) durch den SEEK-Operator an den [Filter]-Operator durchgereicht werden. Der [Filter]-Operator muss anschließend alle 1.000 Datensätze aus dem vorherigen Operator annehmen und durch besagte Typen-Konvertierung einzeln prüfen. Das Ergebnis ist dann 1 Datensatz!


Das oben gezeigte Phänomen kennt man auch, wenn man mit “non-sargable” Argumenten arbeitet, wie ich im Artikel “Optimierung von Datenbankmodellen – SARGable Abfragen” beschrieben habe.


Szenario #2


Wie Microsoft SQL Server die Einschränkungen verarbeitet, wenn die korrekten Datentypen verwendet werden, zeigt das nächste Beispiel. Alle Beispiele werden mit dem oben genannten Traceflag 9130 ausgeführt, um zu zeigen, dass keine dedizierte [Filter]-Operation verwendet wird.



SELECT * FROM dbo.Customers WHERE Mandant_Id = 1 AND Customer_Id = '10001' OPTION (QUERYTRACEON 9130);

Die obige Abfrage verwendet für die Einschränkung auf [Customer_Id] nun einen Textdatentypen und der Ausführungsplan zeigt die deutliche Verbesserung.

Execution_Plan_03

Die Abfrage zeigt erneut den SEEK-Operator und die Eigenschaften dieses Operators zeigt, dass nun beide Einschränkungen vollständig in einer SEEK-Operation verwendet werden können.

Execution_Details_02

Sowohl die erste Einschränkung [Mandant_Id] als auch die zweite Einschränkung auf [Customer_Id] werden als SEEK-Prädikate verwendet. Durch diese Operation verbleibt kein “Residual Prädikat”, das eine weitere Filterung bedeutet hätte.


Die Abfrage läuft nun optimal, wie auch die folgende IO-Statistik zeigt. Statt einer vollständigen Suche über die Tabelle (37 IO) kann Microsoft SQL Server den B-Tree für einen optimalen Zugriff verwenden



Table 'Customers'. Scan count 0, logical reads 2, physical reads 0, 

Da Microsoft SQL Server nun keine Konvertierung der Werte im Attribut vornehmen muss, kann effizient gesucht werden.


Zusammenfassung


Für effektive Abfragen sind nicht allein Indexe verantwortlich – für eine effektive Verwendung ist es erforderlich, dass Microsoft SQL Server bereits bei der Formulierung der Abfrage die korrekten Datentypen für die Einschränkungen erhält. Sobald Microsoft SQL Server selbst eine Typenkonvertierung durchführen muss, kann ein kostengünstiger SEEK schnell zu einem teuren SCAN werden, wenn diese Typenkonvertierung das Attribut selbst betrifft. Nur die Auswertung eines Ausführungsplans reicht unter Umständen nicht aus, eine Bewertung vorzunehmen, ob eine Abfrage effektiv ausgeführt wird; auch ein Blick auf das IO und – insbesondere – auf die Eigenschaften der Abfrageoperatoren gibt einen deutlichen Hinweis auf die Effektivität der Abfrage.


Herzlichen Dank fürs Lesen!


Links


sys.allocation_units: http://msdn.microsoft.com/de-de/library/ms189792.aspx


sys.partitions: http://msdn.microsoft.com/de-de/library/ms175012.aspx


INDEX SEEK: http://technet.microsoft.com/en-us/library/aa178398.aspx


INDEX SCAN: http://technet.microsoft.com/en-us/library/aa178393.aspx


SET STATISTICS: http://msdn.microsoft.com/de-de/library/ms184361.aspx


QUERYTRACEON: http://support.microsoft.com/kb/2801413/en-us


TF 9130: http://sqlblog.com/blogs/paul_white/archive/2012/10/15/cardinality-estimation-bug-with-lookups-in-sql-server-2008-onward.aspx


Pages and Extents: http://technet.microsoft.com/en-us/library/ms190969.aspx

Philipp Lenz: Visual Studio Online mit SSDT 2013 – BI Projekte Online verwalten

vs13onlineLange habe ich gewartet damit ich endlich Visual Studio 2013 ONLINE mit den Data Tools verwenden kann. Endlich gibt es auch wieder ein SSDT 2013 Express zum Download. Damit ist die Kompatibilität gewährleistet. In diesem Beitrag will ich kurz und knapp beschreiben, wie man das SSDT für BI Projekte und SQL Server Datenbank Projekte einrichtet und nutzt.

Der Sinn und Zweck für mich ist darin, Projekte von überall bearbeiten und verwalten zu können, ob beim Kunden, zuhause oder im Büro und das alles im Fokus der Business Intelligence- und Datenbank Projekte. Die durchaus guten Features der Quellcode Verwaltung sind bei mir weniger im Fokus und werden nicht weiter benutzt. Kurz und knapp: Ich brauche eine Online Quellcode Ablage von Projekten die mit dem SSDT erstellt werden ;-)

Folgende Schritte sind notwendig um das Ziel zu erreichen:

Als erstes brauchen wir ein Konto bei Visual Studio Online http://www.visualstudio.com/de-de/products/visual-studio-online-overview-vs.aspx Hier habe ich den Online Basic Plan gewählt – dieser ist für 5 Benutzer kostenlos

Nach dem Registrieren bei Visual Studio online brauchen wir ein Projekt mit dem wir arbeiten können:
2014-05-24_16-29-41

 

Nun muss der Download der SQL Server Data Tools (SSDT BI) für Visual Studio 2013 durchgeführt werden http://www.microsoft.com/en-us/download/details.aspx?id=42313
Nach der Installation ist die Shell mit den BI Projektvorlagen verfügbar

Nun brauchen wir noch die Shell mit den Projektvorlagen um SQL Server Datenbank Projekte erstellen zu können. Hierfür muss man ein Visual Studio Express installieren. Hier nehme ich die Web Edition: http://www.visualstudio.com/downloads/download-visual-studio-vs – das ist m.E. völlig unsinnig da es bei 2012 noch die Vorlage als eigenständiges Setup zum Download gab – so habe ich nicht nur die Data Tools installiert sondern ein VS Express Web was ich eigentlich gar nicht brauche, nur einige Referenzen und Tools die darin unbedingt integriert werden mussten …. Nun gut.

 

Nun öffnet man das Projekt über die Weboberfläche:
2014-05-24_17-57-10Hier startet nun das Visual Studio (SSDT) und man meldet sich mit dem Microsoft Konto an, so wird das Visual Studio mit der Quellcode Verwaltung von Visual Studio online verbunden. So können die Objekte ein und ausgecheckt werden und alle Funktionen die man normalerweise von einem TFS kennt, sind nun auch verfügbar – und das in einer Express Editionen und einem “TFS” der Online für “lau” verfügbar ist.

 

Nun können die Projekte in der Projektmappe erstellt werden:
2014-05-24_18-00-352014-05-24_18-02-51

 

 

 

 

 

 

 

 

 

Nun fügen wir noch ein weiteres Projekt der Mappe hinzu:
2014-05-24_18-05-42

Nun füge ich den Projekten noch jeweils ein paar Objekte hinzu wie Tabellen, Views aber auch im Reporting Projekt einen Bericht.
2014-05-24_18-14-25

 

 

 

 

 

 

 

Über einen Rechtsklick auf die Projektmappe checke ich nun die Projekte komplett ein
2014-05-24_18-15-06

Nun sind alle Projekte und Dateien in Visual Studio online eingecheckt und dort verfügbar – von dort aus kann ich entweder das Projekt wieder auschecken, weiter bearbeiten oder auch als ZIP downloaden um so eigene Versionen extern ablegen zu können oder diese in Umgebungen integrieren zu können, wo es ggf. kein Online Zugriff auf Visual Studio online gibt.

Fazit:
Mit Visual Studio online kann ich meine BI Projekte mit den Microsoft Tools online verwalten und verfügbar halten. Den Transfer mit USB Sticks, Mail etc. fällt somit flach und da ich mit den Express Versionen hier komplett auskomme, kann ich die Tools überall installieren ohne Lizenzen erwerben zu müssen. Mal schauen ob hier noch ein Haken kommt? :-)
 

Bernd Jungbluth: Seminar - SQL Server Reporting Services

Es gibt einen neuen Termin für das Seminar SQL Server Reporting Services:

18. Juni 2014 im Hotel Best Western Premier Bellevue Rheinhotel in Boppard am Rhein

Mit den SQL Server Reporting Services bietet Microsoft eine zentrale Informationsplattform für Unternehmen, die aktuelle und applikationsübergreifende Informationen in interaktiven Berichten zur Verfügung stellt.

Die Bereitstellung dieser zentralen Informationsplattform ist Inhalt dieses Seminars.

Es wird das gesamte Spektrum eines Berichts betrachtet – von der Erstellung über die Verwaltung bis hin zur Bereitstellung. Dabei liegt der Fokus auf der Berichtserstellung mit dem Berichtsdesigner.

Nach diesem Seminar sind Sie in der Lage
- Reporting Services als zentrale Informationsstelle zu nutzen
- Dynamische Berichte zu erstellen
- Berichte und Berichtsserver zu verwalten
- Berichte im Berichtsmanager bereitzustellen
- Berichte zu abonnieren

Die Teilnehmerzahl ist auf 8 Personen begrenzt

Weitere Informationen zum Seminar gibt es unter www.berndjungbluth.de/ssrs.pdf.

Torsten Schuessler: #CU package 10 for SQL Server 2012 Service Pack 1

The Cumulative update package 10 for Microsoft SQL Server 2012 Service Pack 1 (Build 11.0.3431.0) is available:

Cumulative update package 10 for SQL Server 2012 SP 1 is available

And here you can get the whole information of builds that were released after SQL Server 2012 SP1.:

http://support.microsoft.com/kb/2674319/

 

I wish you a nice day,
tosc

 

Torsten Schuessler

Bernd Jungbluth: Seminar - Migration Access nach SQL Server

Es gibt einen neuen Termin für das Seminar Migration Access nach SQL Server:

26. März 2014 im Hotel Best Western Premier Bellevue Rheinhotel in Boppard am Rhein

In diesem Seminar liegt der Fokus auf dem Wort „nach“.
Eine Migration nach SQL Server bedeutet nicht nur die Migration der Access-Tabellen nach SQL Server, sondern vielmehr auch die Migration der Access-Abfragen und VBA-Module zu Gespeicherten Prozeduren und Sichten. Das Ergebnis ist eine zukunftssichere und schnelle Client/Server-Applikation.

Nach diesem Seminar sind Sie in der Lage
- Access-Abfragen zu Sichten oder Gespeicherten Prozeduren zu migrieren
- Sichten, Gespeicherte Prozeduren, Funktionen und Trigger in T-SQL zu programmieren
- Sichten, Gespeicherte Prozeduren und Funktionen in Access und in VBA zu verwenden

Agenda
- Migration von Access nach SQL Server
- Analyse der Access-Applikation mit dem SQL Server Profiler
- Migration der Access-Abfragen nach SQL Server
- Einführung in T-SQL
- Optimierung der Access/SQL Server-Applikation

Preis
350 Euro zzgl. MwSt., inklusive Mittagessen und Getränke

Die Teilnehmerzahl ist auf 8 Personen begrenzt.
Aktuell sind noch 4 Plätze frei.

Weitere Informationen zum Seminar gibt es unter www.berndjungbluth.de/migration.pdf.

Andreas Wolter: Artikel „SQL Server 2014 – Neues Fundament“ in iX Ausgabe 5/2014, Richtigstellungen zu In-Memory OLTP und ColumnStore Indexes und warum AlwaysOn nicht gleich Always On ist

In der Mai-Ausgabe der iX ist auf Seite 56 ein Artikel von mir zu finden, den ich zusammen mit den Kollegen Volker Heck (Cloud- und BI-Part) und Holger Schwichtenberg (Lektorat) geschrieben habe.

iX_Ausgabe_05_2014

 

In der finalen Version, die ich auch erst am Kiosk zu sehen bekommen habe, sind leider einige Ungenauigkeiten enthalten. Um Missverständnisse auszuschließen, möchte ich diese hier kurz geraderücken, bzw. ein korrektes Verständnis sicherstellen.

Es geht los mit der Einleitung:
1)

„…Nach zwei Jahren Entwicklungszeit stellt Microsoft die neue Version seines Datenbankservers vor...“

Richtigstellung: Ich bin mir nicht sicher, wann der Startschuss für den SQL Server 2014 war, aber ziemlich sicher war das nicht vor 2 Jahre vor dem Release-Datum (1.4.2014), wie dieser Satz impliziert. Dass die In-Memory OLTP Engine XTP bereits 2009 mit ihrem ersten Patent untermauert wurde, steht etwas später noch im Artikel. Wann genau feststand, dass es einen SQL Server 2014 geben, und der Code entsprechend gebranched wurde, ist mir nicht bekannt. Wenn ich eine Vermutung abgeben würde, wäre dies eher ca. 3 Jahre vor dem Release.

2)

„…Wichtigste Neuerung ist das Ablegen relationaler Daten im Hauptspeicher statt auf der Festplatte….“

Richtigstellung: Diejenigen, die sich bereits ein wenig mit dieser neuen Technologie auseinandergesetzt haben, wissen es natürlich: Die Daten werden sowohl in RAM als auch auf Festplatte gespeichert – es sei denn man arbeitet mit „Schema_Only“-Tabellen. Später im Artikel wird das auch noch deutlich, mag aber hier verwirren.

3)

„…Stored Procedures in Maschinensprache
… „Native Kompilierung“ ... Dafür erzeugt der Server beim ersten Ausführen aus der jeweiligen Prozedur eine DLL. Diese Bibliotheken überstehen den Neustart von Datenbank oder Server jedoch nicht, müssen also danach erneut erstellt werden…“

Das kann man leicht falsch verstehen.
Richtigstellung: Genau gesagt werden diese DLLs nach jedem Neustart von Datenbank oder Datenbankserver neu generiert. – Man muss diese DLLs oder gar die Prozeduren also nicht selber neu erstellen.

4)

„(Nativ kompilierte Prozeduren)…Solche Prozeduren … erlauben noch nicht alle T-SQL-Sprachelemente. Es fehlen beispielsweise Raiseerror und Begin Transaction, einige Funktionen sowie Query Hints.“

Auch das könnte jemanden auf eine falsche Fährte führen.
Richtigstellung: Besser ausgedrückt: „Zum Beispiel kann man bestimmte Befehl wie Raiseerror oder Begin Transaction, anstelle dessen ein „Atomic“-Block erforderlich ist nicht nutzen.“ - Der Atomic-Block startet bereits eine Transaktion, daher ist ein zusätzliches „Begin Transaction“ ohnehin fehl am Platz. - Einige Query Hints werden übrigens tatsächlich unterstützt.

5)

„(neue Parallelitätskontrolle „multi-versioned, timestamped optimistic concurrency control“)… Dazu ergänzt der Server alle Datensätze um einen bei jeder Änderung automatisch aktualisierten Zeitstempel, anhand dessen er Konflikte erkennt…“

Das kann man auch leicht falsch interpretieren und einen glauben lassen, dass immer der selbe Datensatz aktualisiert wird. Der Hintergrund von „multi-versioned, timestamped optimistic concurrency control“ ist aber gerade, das es pro Version einen neuen Datensatz gibt, was sich in ausführlichen Tests von Microsoft Research in realitätsnaheren Testreihen (mit komplexeren Transaktionen im Mix mit längeren Lesezugriffen und Hotspot-Szenarien) als effizienter als „Single-version locking“ herausgestellt hat. (Quelle: „High-Performance Concurrency Control Mechanisms for Main-Memory Databases“, Microsoft, University of Wisconsin – Madison)
- Single-Version Locking wird beispielsweise von Oracle TimesTen und IBM’s solidDB eingesetzt.
Richtigstellung:
Genauer ist also zu sagen, dass es pro Version einen Datensatz gibt, und die “Alten Versionen” durch ein End-Timestamp als solche markiert werden.

6)

„(Clustered ColumnStore Indexe)…Diese erweiterte Variante der Hauptspeicher-Index-Technik wurde für die 2013 erschienene PDW-Variante (Parallel Data Warehouse) des SQL Server 2012 entwickelt und ist dort bereits im Einsatz…“

Die Wortwahl lässt vermuten, dass diese (Columnstore) Indexe, wie auch bei In-Memory optimierten Tabellen & Indexen lediglich im Hauptspeicher liegen. Das stimmt natürlich nicht.
Richtigstellung: Besser sollte hier stehen: „Hauptspeicher-optimierte Indexe“

7)

Und last but not least leider hat sich auch in diesem Artikel ein häufiger Fehler eingeschlichen:

Die Lösung für hohe Verfügbarkeit und Notfallwiederherstellung, welche im SQL Server 2012 neu eingeführt wurde, schreibt sich natürlich „AlwaysOn“, und weder „Always On“ noch „Always-On“.
„Always On“ (mit Leerzeichen) wurde bereits in SQL Server 2005 eingesetzt, um Speicher-Hardware für SQL Server zu zertifizieren. Dazu gehört z.B.:

  • die korrekte Umsetzung der API’s, des Write-Ahead Logging (WAL) Protokolls für sowohl Transaktionsprotokolle als auch Daten- und Backup-Dateien
  • der Optionen FILE_FLAG_WRITETHROUGH und FlushFileBuffers beim Öffnen von Dateien
  • der Unterstützung von asynchronem I/O
  • Write ordering
  • Das korrekte Übermitteln der Sektor-Größen an die Windows API’s, um Sektor-Größen-Versatz und Torn Writes zu verhindern
  • Die NTFS-Fähigkeiten wie z.B. Sparse Files, File Streams, Encryption, Compression, sämtliche Sicherheitseigenschaften

Über „Always On” lässt sich z.B. hier nachlesen: www.dell.com/downloads/global/solutions/dell_pv_sql_always_on_tech_note_v_1_5.pdf

Im SQL Server 2008 wurde „Always On“ für die gesamte Palette der Hochverfügbarkeitstechniken Technologien verwendet. Dazu gehörten Database Mirroring, Log Shipping, Failover Clustering, Peer-to-Peer Replication, Backup und Restore (!), Database Snapshots, selbst Partitionierung und weiteres. (Hier nachzulesehn: High Availability – Always On Technologies) Das hat also nicht mit dem neuen Features AlwaysOn-Verfügbarkeitsgruppen/Availability Groups und AlwaysOn-Failoverclusterinstanzen zu tun.
Und Feature-Namen werden nicht einfach „eingedeutscht“, genauso wenig wie man SharePoint auseinanderschreibt – Nein, ich werde das selbst aus Demozwecken nicht tun ;-)

Soweit habe ich nun meinem Genauigkeitsempfinden genüge getan ;-)

 

Da das folgende Diagramm es leider nicht in den Artikel geschafft hat, möchte ich es hier zumindest mit meinen Lesern teilen:

 XTP_Benchmark

Das ist das Ergebnis eines Performance-Vergleiches einer schematisch so gut wie identischen „on-Disk“-Tabelle gegenüber den verschiedenen In-Memory OLTP Varianten. Der Test wurde auf Standard-Hardware durchgeführt: Intel i7-3529 (2,9Ghz), 2 Cores Hyperthreaded, 16GB RAM und SSDs. Das Ergebnis kann sich sehen lassen und entspricht Microsofts Versprechungen, das neue Hardware nicht zwingend erforderlich ist, um spürbare Performance-Gewinne durch den Einsatz der XTP-Engine zu erhalten.

Und hier sind auch nochmal die begleitenden Links zu dem Artikel:

 

Ich hoffe die genannten Punkte sind für ein besseres Verständnis nicht nur des Artikels sondern auch von SQL Server 2014 allgemein hilfreich.

Kommentare oder Nachfragen können gern hier über meinen Blog hinterlassen werden.

 

Andreas Wolter

 

PS: Leider sind in meiner Master-Class Workshop In-Memory OLTP & ColumnStore - New Storage Engines in SQL Server 2014 (XTC) keine Plätze mehr verfügbar (!). Im Sommer wird sicher die Entscheidung für eine Neuauflage im 2. Halbjahr 2014 oder doch erst wieder im 1. HJ 2015 fallen. – Im 2. HJ stehen wieder viele Konferenzen, inklusive MVP Summit, PASS Summit und PASS Camp an, so dass es da wirklich eng wird. Aussichtsreicher ist da meist eine Inhouse-Schulung auf Anfrage.

Andreas Wolter: Speaking at SQLBits 2014: PreCon on In-Memory OLTP and Security session

Sprecher auf der SQLBits 2014 zu In-Memory OLTP und Sicherheit

(DE)
Nachdem ich letztes Jahr das erste Mal als Teilnehmer auf der SQLBits-Konferenz in England war, darf ich dieses Jahr nicht nur eine Session halten, sondern sogar auch einen der begehrten „Training Days“ im PreCon-Stil!

(EN)
After having attended the SQLBits Conference in the UK last year for the first time, I became not only one of the lucky people to hold a session, but also to give one of the much sought after “Training days” in PreCon-style!

 SQLBits

 

Niko Neugebauer und ich geben am 17. Juli die ganztägige Veranstaltung „In-Memory Technologies in SQL Server 2014: CCI & XTP“.

Niko wird seine Erfahrung mit den neuen Clustered ColumnStore Indexen teilen und ich werde die neue In-Memory OLTP Engine XTP detailliert vorstellen.

Niko Neugebauer and I will be delivering the full day presentation „In-Memory Technologies in SQL Server 2014: CCI & XTP“ on July 17th.

Niko will share his experience with the new Clusterd ColumnStore Indexes and I will present the all new In-Memory Engine XTP in detail.

 

In this full-day session MVP Niko Neugebauer and MCM Andreas Wolter are going to take you onto a journey to In-Memory in SQL Server 2014 which contains two features of great impact on how databases perform and are designed: The improved Columnstore Indexes: Clustered, Updatable, they change the way BI & Datawarehouse Systems will be designed & used. On the OLTP other side, the XTP engine (Codename “Hekaton”) brings huge performance improvements for OLTP workloads.

 

- Diesen Ganztages-Vortrag haben wir bereits in ähnlicher Form auf dem Deutschen Launch Event für den SQL Server 2014 gehalten.

Das ist eine überaus große Ehre für uns, zumal dort seit Jahren einige echte Gurus ihres Fachs auftreten. So befinden wir uns dieses Jahr in der Illustren Gesellschaft von Brent Ozar, Brian Knight, Jennifer Stirrup, Dejan Sarka, Marco Russo, Adam Jorgensen und John Welch, Itzik Ben-Gan, Allan Hirt, Dave Ballantyne und David Morrison und Simon Sabin!

 

Am Freitag, den 18., halte ich dann meine bekannte Security Session: „“SQL Attack…ed” – SQL Server under attack: SQL Injection“.

Der Samstag ist übrigens „Community day“ mit kostenloser Teilnahme – die Plätze werden wie für alle Tage aber schnell weg sein. Also nicht zu lange warten.
Hier geht’s zur vollständigen Agenda.

- We presented this full day session already in similar shape at the German Launch Event for SQL Server 2014.

This is a huge honor for us, especially since some of the real Gurus of their subject have been presenting there for years. So this year we are finding ourselves in the illustrious company of Brent Ozar, Brian Knight, Jennifer Stirrup, Dejan Sarka, Marco Russo, Adam Jorgensen and John Welch, Itzik Ben-Gan, Allan Hirt, Dave Ballantyne and David Morrison and Simon Sabin!

 

On Friday the 18th, I will then give my well-known Security Session: „“SQL Attack…ed” – SQL Server under attack: SQL Injection”.

Saturday, by the way, is “Community day” with attendance free of charge– the seats are going to be taken quickly though, as for all days, so do not wait too long.
Here you can find the complete agenda.

 

Cu at SQLBits,

 

Andreas

 

Sascha Lorenz: Hamburger SQL Server Usergroup (PASS Deutschland e.V) Treffen im Mai 2014

Wie jeden Monat trifft sich die Hamburger SQL Server Community auch im Mai wieder. Wir steuern das 100. Treffen an, aber noch ist es nicht ganz so weit.

Im Mai wollen wir gemeinsam über Best Practices für Data Warehouse Lösungen mit dem SQL Server diskutieren.

Dabei wollen wir die Wahl der Werkzeuge erörtern (SSIS vs. pures T-SQL), Modellierungen ansprechen (Kimball vs. Data Vault) und viele weitere Themen (DWH: Kunst oder Handwerk) untersuchen, welche im Rahmen der Diskussion aufkommen werden. Das verspricht in Summe ein sehr interessanter Abend zu werden.

Wir treffen uns am 14. Mai 2014 um 18:30 bei:

Microsoft Deutschland GmbH
Geschäftsstelle Hamburg
Gasstraße 6a
22761 Hamburg

Kostenlose Parkplätze befinden sich hinter dem Gebäude. Der Parkplatz ist über die Rampe mit dem Schild "Microsoft Kunden" erreichbar.
Nur wenige Minuten zu Fuß ist der S-Bahnhof Bahrenfeld entfernt (S1/S11).

Wir bitten um eine vorherige Anmeldung per Email an: slo@sqlpass.de, damit wir die Größe des benötigten Konferenzraums vorher abstimmen können. Es haben sich schon einige Teilnehmer angemeldet.

Philipp Lenz: Reporting Services: Weitergabe von “Multi Value”-Parametern

Immer wieder kommt es vor, dass man Parameter von einem Bericht an den anderen weitergeben möchte. Bei einem Parameter der lediglich einen Wert aufnimmt, ist das ziemlich simpel. Was aber, wenn der Parameter mehrere Werte (Multi Value Parameter) aufnehmen kann und auch alle diese Werte an einen weiteren Bericht weitergegeben werden sollen?

Hier die Lösung:

Ausgangslage ist ein Bericht, der 4 verschiedene Werte als Multi Value Parameter aufnehmen kann und diese im Bericht darstellt.

Dieser Parameter nutzt ein Dataset für die verfügbaren Werte:
2014-05-02_20-42-39

 

2014-05-02_20-42-55
Ergebnis:
2014-05-02_20-47-32

 

 

 

 

 

Die Ausgabe ist folgendermaßen realisiert.2014-05-02_20-47-51

Der Text hier wird verlinkt auf einen weiteren Bericht der das gleiche Dataset nutzt. Hier wird erwartet, dass der übergebene Parameter die Werte vor ausfüllt und aufnimmt, die vom aufrufenden Bericht übergeben werden. Also das hier in diesem Beispiel die Werte für Nord, Süd, Ost und West übergeben werden.

Das Textfeld für die Ausgabe wird nun verlinkt auf den anderen Bericht:
2014-05-02_20-52-00

Als Ausdruck für den Parameter ist folgender Code notwendig:

2014-05-02_20-52-52Nun werden die entsprechenden Werte aus dem Multi Value Parameter an den Unterbericht weitergegeben. An dem Unterbericht muss nicht geändert werden, lediglich muss er den Parameter aufnehmen und auch als Multi Value Parameter definiert werden.

Andreas Wolter: New Permissions in SQL Server 2014: IMPERSONATE ANY LOGIN, SELECT ALL USER SECURABLES, CONNECT ANY DATABASE and the old CONTROL SERVER

(DE)
SQL Server 2014 bringt insgesamt 5 neue Berechtigungen. Zwei von diesen sind auf Datenbank-Ebene und nur in der Windows Azure SQL Database Edition verfügbar – nicht im „Box-Produkt“.
(Danke an Erland Sommarskog für die Bestätigung und Hinweis auf die recht versteckte Notiz in der Dokumentation: GRANT Database Permissions)
Die neuen Berechtigungen sind wie folgt:

(EN)
SQL Server 2014 brings altogether 5 new permissions. Two of those are on database level and only available in the Windows Azure SQL Database Edition – not in the box-version (Thanks Erland Sommarskog for confirming this and pointing me to the quite hidden note in the documentation: GRANT Database Permissions)
The new permissions are as follows:

 

Class Desc.

Permission Name

Type

Parent Covering Permission Name

DATABASE

ALTER ANY DATABASE EVENT SESSION

AADS

ALTER ANY EVENT SESSION

DATABASE

KILL DATABASE CONNECTION

KIDC

ALTER ANY CONNECTION

SERVER

CONNECT ANY DATABASE

CADB

 

SERVER

IMPERSONATE ANY LOGIN

IAL

 

SERVER

SELECT ALL USER SECURABLES

SUS

 

 

Und wofür und wie können wir diese neuen Berechtigungen auf Server Ebene verwenden?

 

IMPERSONATE ANY LOGIN

 

Erinnert Ihr Euch an das Problem mit CONTROL SERVER?

Das größte Problem war, das dieses Recht auch die Impersonifizierung eines jeden Kontos, inklusive der Privilegien Erweiterung zum sysadmin erlaubte.

Die Details und auch andere Probleme mit CONTROL SERVER habe ich hier umfassend dokumentiert:

CONTROL SERVER vs. sysadmin/sa: permissions, system procedures, DBCC, automatic schema creation and privilege escalation - caveats

 

SQL Server 2014 gibt uns mit der Einführung der IMPERSONATE ANY LOGIN-Berechtigung Munition, dieses Problem anzugehen.

-           Diese Berechtigung erlaubt es, jeden Login und User zum impersonieren(!).

 

Wenn wir dieses mit einem DENY gegenüber dem Principal mit CONTROL SERVER Recht verwenden, verhindert es diesen, irgendeinen Login direkt zu impersonifizieren. (Warum sage ich “direkt”? –  Das sehen wir ein Stück weiter unten.)
Also sehen wir uns an, wie man einen Login mit CONTROL SERVER an einer Pivilegienerweiterung hindert, mithilfe der neuen Berechtigung

So, what for and how can we use those permissions on Server level?

 

IMPERSONATE ANY LOGIN

 

Do you remember the problem with CONTROL SERVER?
The biggest flaw of this permission was, that this permission also allowed Impersonation of any account, including privilege elevation to any sysadmin.
I have documented this and other problems with CONTROL SERVER in detail here:

CONTROL SERVER vs. sysadmin/sa: permissions, system procedures, DBCC, automatic schema creation and privilege escalation - caveats

 

Now in SQL Server 2014, by introducing the permission IMPERSONATE ANY LOGIN, gives us ammunition to tackle this problem.

-           This Permission permits to impersonate any Login and User(!).

 

If we DENY this to the Principal with CONTROL SERVER permission, it prevents him from impersonating any Login directly. (Why do I say “directly”? – We’ll see a bit further down.)

So let’s see how to prevent a Login with CONTROL SERVER from elevating privileges by impersonating another login with help of the new permission:

 

USE [master]

GO

 

CREATE LOGIN DBA_TheDude WITH PASSWORD=N'www', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

 

CREATE SERVER ROLE [Role_DBA]

 

ALTER SERVER ROLE [Role_DBA]

ADD MEMBER DBA_TheDude

 

GRANT CONTROL SERVER TO [Role_DBA]

GO

DENY IMPERSONATE ANY LOGIN TO [Role_DBA]

GO

 

CREATE DATABASE ControlServer_Schema_Demo

GO

 

-- ====================

-- === Test

 

EXECUTE AS LOGIN = 'DBA_TheDude'

 

-- Attempt impersonation:

EXECUTE AS LOGIN = 'sa';

-->

Msg 15406, Level 16, State 1, Line 9

Cannot execute as the server principal because the principal "sa" does not exist, this type of principal cannot be impersonated, or you do not have permission.

//

Die Ausführung als Serverprinzipal ist nicht möglich, weil der Prinzipal 'sa' nicht vorhanden ist, für diesen Typ von Prinzipal kein Identitätswechsel möglich ist, oder Sie nicht die erforderliche Berechtigung haben.

 

 

USE ControlServer_Schema_Demo

 

EXECUTE AS USER = 'dbo';

 

-->

Msg 15517, Level 16, State 1, Line 15

Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.

//

Die Ausführung als Datenbankprinzipal ist nicht möglich, weil der Prinzipal 'dbo' nicht vorhanden ist, für diesen Typ von Prinzipal kein Identitätswechsel möglich ist, oder Sie nicht die erforderliche Berechtigung haben.

 

Hurra!(?)

Privilege-Escalation-Risiko:

Wirklich? Immer noch?
Natürlich.

Wir laufen immer noch unter dem Kontext DBA_TheDude:

Hooray!(?)

Privilege-Escalation-risc:

Really? Still?
Of course.

 

Still we are running under the context of DBA_TheDude:

 

 

USE master;

 

CREATE LOGIN UtilizeMe WITH PASSWORD=N'www', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

 

GRANT CONTROL SERVER TO UtilizeMe

GO

 

Wir können den Login “UtilizeMe” nicht impersonifizieren, aber wir können und einfach mit seinem Passwort anmelden!

-           Nebenbei ein weiterer Grund, SQL Authentifizierung nicht zu verwenden, da er ansonsten die Credentials eines validen Windows-Login’s finden müsste – viel schwieriger, als einfach seinen eigenen Backdoor-account anzulegen.

We cannot Impersonate the “UtilizeMe” Login, but we can just Log On using his password!

-           Another reason to not use SQL authentication by the way, as he would then need to find a valid Windows-Login’s Credentials – much harder to just creating his own backdoor-account.

 

 SQL_Server_2014_Logon_Screen_SQL_Authentication

Um also unseren Administrator wirklich daran zu hindern, seine Privilegien zum Sysadmin zu erweitern, müssen wir auch mit DENY ALTER ANY LOGIN  und ALTER ANY SERVER ROLE arbeiten.

So in order to further prevent our Administrator from elevating privileges to sysadmin, we also need to work with DENY ALTER ANY LOGIN  and ALTER ANY SERVER ROLE.

 

Und kann DANN CONTROL SERVER endlich sicher verwendet werden?

NEIN!

 

Tatsächlich gibt es noch ein paar andere Dinge, die man tun kann, um die Berechtigungen von einem CONTROL SERVER-berechtigten Konto zu erweitern. Etwas trickreicher vielleicht, aber ein Angreifer mit einem guten Wissen über SQL Server (ich spreche also nicht von „Raketenwissenschaft“), wird in der Lage sein soetwas durchzuführen.

Mir ist bewusst, dass das “Separation of Duties in SQL Server 2014”-Whitepaper (Enthalten im Microsoft® SQL Server® 2014 Product Guide) die Kombination von GRANT CONTROL SERVER + DENY IMPERSONATE ANY tatschlich als Best Practice listet, aber dennoch…

 

Also, empfehle ich die Verwendung in irgendeiner Weise?
Das ist für mich persönlich eine harte Frage, da ich gerne viel weniger Leute sehen würde, die sa/sysadmin für tägliche Aufgaben verwenden/vergeben.

Leider ist es jedoch weit davon entfernt, perfekt zu sein, und in Sicherhit-belangen, alles, was nicht lupenrein ist, ist ein Risiko.
Aber ich sehe es durchaus als ersten Schritt, um Leute davon abzuhalten, von Anfang an die höchsten Berechtigungen zu verwenden, da viele einfach nicht die Zeit und Kenntnisse haben werden, dort auszubrechen.
Ich empfehle es in Kombination mit soliden Überwachung und Alarmen.

Wer das also anstelle von sa/sysadmin verwendet, verdient dennoch Applaus, da es zeigt, dass man sich kümmert und es wagt, Berechtigungen einzuschränken.

Can we THEN finally use CONTROL SERVER completely safely?

 

NO!

 

In fact there are a few other things one can do to elevate permissions from a CONTROL SERVER-permitted account. More tricky in a way, but an attacker with some good knowledge about SQL Server (note, I am not saying “rocket-scientist”) will be able to do that.

I am aware that the “Separation of Duties in SQL Server 2014”-Whitepaper (Contained in the Microsoft® SQL Server® 2014 Product Guide) does in fact list the combination of GRANT CONTROL SERVER + DENY IMPERSONATE ANY LOGIN as a best practice, but yet…

 

So do I recommend using it in any way?

That is a hard question for me personally, as I would like to see much less people using/granting sa/sysadmin for daily tasks, and this permission had the potential to make an end to it.

Unfortunately it is far from perfect, and in security-terms, anything not flawless, is a risk.

But in terms of getting people away from using the highest privileges from the very beginning, I do see it as a step, since many may just not have the time and skills to break out of it.

I do recommend using it in combination with some solid Auditing and alerts in place.
So anyone using this instead sa/sysasdmin still gets applause, as it shows you care and dare to limit permissions.

 

SELECT ALL USER SECURABLES


Diese Berechtigung kann verwendet werden, um einen hochgradig berechtigten Principal, der z.B. Troubleshooting/Analysen des Servers durchführt daran zu hindern, Nutzer-Daten auszulesen. – Vergesst nicht, auch EXECUTE in alle Nutzerdatenbanken zu verbieten, ansonsten kann derjenige immer noch alle gespeicherten Prozeduren (sofern vorhanden) ausführen, um an die Daten zu gelangen.
Auch das ist nicht Bombenfest, wie wir bereits von CONTROL SERVER und seinen Einschränkungen wissen.

Was sicherer ist, ist die Verwendung für eine Art Auditor, der ALLE Daten lesen (aber nicht ändern) können soll – ohne den Aufwand, in sämtlichen Nutzerdatenbanken Benutzer und Rechte zu vergeben.

SELECT ALL USER SECURABLES


This permission can be used for preventing a highly privileged Principal that may be troubleshooting/analyzing the server from reading any user data. - Do not forget to also deny EXECUTE in all User databases though, otherwise he can just execute the stored procedures (if any exist) to get to the data.
Also this is not bullet-proof as we already know from CONTROL SERVER and it’s restrictions.

What’s more safe, is the use for an Auditor that needs to read ALL data, but not change it - without the effort of creating users and permissions in all user databases.

 

CONNECT ANY DATABASE

 

Diese Berechtigung kann gut für Logins verwendet werden, die sich im Wesentlichen mit jeder Datenbank verbinden können and zum Beispiel Code Reviews durchführen sollen – indem man diese mit der VIEW ANY DEFINITION Berechtigung kombiniert.
Das ist in meinen Augen tatsächlich sehr gut verwendbar für viele Szenarien.

CONNECT ANY DATABASE

 

This permission can be used quite well for having logins that can basically connect to any database and for example do code reviews - by combining it with the VIEW ANY DEFINITION permission.
I do think this is actually of quite some use for many scenarios.

 

Happy “Server controlling”,

 

Andreas

Don't contact us via this (fleischfalle@alphasierrapapa.com) email address.