SQLSaturday #567 - Slovenia 2016
SQL Server 2016 - is it worth the upgrade?
Event Date: 10-12-2016 - Session time: 09:00:00 - Track: DBA/DEV
Speaker: William Durkin
Title: SQL Server 2016 - is it worth the upgrade?
Microsoft has shortened their release life-cycle recently, causing many companies to skip versions when there were no compelling features in them. So how does SQL Server 2016 look? This session will give you a heads-up on the newest features and improvements that are newly available in SQL Server 2016. At then end of the hour, you will have identified which new features you can't live without and which you can happily wait until SQL Server vNext for.
Demistifying the errorlog
Event Date: 10-12-2016 - Session time: 09:00:00 - Track: DBA
Speaker: Stephane Haby
Title: Demistifying the errorlog
As a DBA, the Error log is an essential daily tool. Learning and understanding its content are not the last part of the job, we also have to manage it to obtain a better interpretation.
In this session, we will discuss about this famous and essential feedback tool, too often skipped from maintenance plans.
Introduction to U-SQL and Data Lake
Event Date: 10-12-2016 - Session time: 09:00:00 - Track: BI
Speaker: Alex Whittles
Title: Introduction to U-SQL and Data Lake
U-SQL is the new kid on the SQL language block. In this session we'll look at the what, where, why and how of the language. Is it a query language? Is it a data transformation language? Will it replace T-SQL? Should you learn it? Where is it? What is Data Lake? We'll discuss all this and more.
SQL Server 2016 Availability Group Enhancements
Event Date: 10-12-2016 - Session time: 10:15:00 - Track: DBA
Speaker: Karol Papaj
Title: SQL Server 2016 Availability Group Enhancements
Availability Group was first introduced in SQL Server 2012 with many feature limitations. In SQL2016, a lot of this feature limitations was removed besides performance improvement. In this session, you will see a lot of demos of SQL Server 2016 Availability Group improvements.
Advanced BIML - for the advanced ETL developer
Event Date: 10-12-2016 - Session time: 10:15:00 - Track: BI/DEV
Speaker: Regis Baccaro
Title: Advanced BIML - for the advanced ETL developer
Are Temporal Tables The Better CDC?
Event Date: 10-12-2016 - Session time: 10:15:00 - Track: DBA/DEV
Speaker: Uwe Ricken
Title: Are Temporal Tables The Better CDC?
YEAH - SQL Server 2016 introduces the first time support for system-versioned temporal tables as a database feature that brings built-in support for providing information about data stored in the table at any point in time. "Temporal Tables" was a feature many developers had on their wish list for Microsoft SQL Server.
Before "Temporal Tables" Change Data Capture (CDC) has been used for the "storage of temporal data". Although the core concepts of CDC didn't had the focus on Temporal Tables it was a really good tool for its implementation.
See in this demo-heavy session both techniques will all there pros and cons. Make your personal opinion what technique is the right one for you and which one will make you crazy!
Execution Plans End-to-End in SQL Server
Event Date: 10-12-2016 - Session time: 10:15:00 - Track: DEV
Speaker: Sergio Govoni
Title: Execution Plans End-to-End in SQL Server
This session examines in depth the Execution Plans on SQL Server. To go beyond the basics such as how to reading an Execution Plan or detecting Index Scans or Lookups, you must acquire a deeper understanding of how the Query Optimizer builds the optimal Execution Plan and how you can influence the choice of a particular index or a particular join operator, up to force a particular Execution Plan without changing the query text. Finally, we will explore the Plan Cache memory area where SQL Server stores Execution Plans.
Compelling visuals in Power BI
Event Date: 10-12-2016 - Session time: 10:15:00 - Track: BI
Speaker: Prathy Kamasani
Title: Compelling visuals in Power BI
Power BI quickly gained enormous popularity in the in the Data Analytics world. PowerBI assists us to import, analyse and publish data instantly. We can quickly create stunning data visualisations with built in visualisations or the visualisations available from the gallery.
In this demo-focused session, we look at how we can create stunning dashboards using power BI and publish them to the cloud. We also look how to embed these reports into your blog or website.
SQL Server Myths and Misconceptions
Event Date: 10-12-2016 - Session time: 11:30:00 - Track: DBA/DEV
Speaker: Miroslav Dimitrov
Title: SQL Server Myths and Misconceptions
SQL Server is full of misunderstood features and misnomers that could affect how it is operating and respectively our applications. If you do not want to be surprised in a bad way by some intended, but not widely known SQL functionalities, join me in this session where we will, in an entertaining way, examine some of the most common behaviors that you probably have not expected.
The Good, the Bad and the Ugly - Always Encrypted in action
Event Date: 10-12-2016 - Session time: 11:30:00 - Track: DEV
Speaker: Janos Berke
Title: The Good, the Bad and the Ugly - Always Encrypted in action
As SQL Server 2016 is coming soon, it is important to see how new features are working. This session is about Always Encrypted feature and covers the followings:
- What encryption types are in SQL Server?
- A business scenario: handling sensitive data w/o giving access to DBA team.
- Always Encrypted basics
- Creating table with encryption by using AE
- Migrating data from regular table to AE table
- Decrypting data from AE table
- AE under the hood
- Performance impact
In the name of the resources: Aggregating data
Event Date: 10-12-2016 - Session time: 11:30:00 - Track: DEV
Speaker: Dejan Dular
Title: In the name of the resources: Aggregating data
The queries that are used for reports are usually the least efficient because you are dealing with huge amounts of data from tables sometimes joined many, many times. In this session we will go through good and bad examples of aggregating the data from your relational database. You will see tips on how to get good use of the indexes, how to lower the number of actual table queries and other tricks that will make your queries run faster.
Step into the Internet of Things with Azure IoT, Stream Analytics and PowerBI
Event Date: 10-12-2016 - Session time: 11:30:00 - Track: BI
Speaker: Johan Ludvig Brattås
Title: Step into the Internet of Things with Azure IoT, Stream Analytics and PowerBI
As IOT climbs up the Peak of inflated expectations on the hype cycle, let's take a closer look at Microsoft's approach to the Internet of Things. They have even given us two reference architecture samples to play with. This session will introduce you to Azure's IoT offerings, how to do analysis on your data with Stream Analytics and then present this in a dashboard with PowerBI. We will use demo data, as well as take a look at real world examples.
How I use PowerShell to automate the installation upgrade of DBA scripts
Event Date: 10-12-2016 - Session time: 13:30:00 - Track: DBA
Speaker: Rob Sewell
Title: How I use PowerShell to automate the installation upgrade of DBA scripts
Every DBA will have a collection of scripts that they want to have on all of their instances.
Maintenance scripts, Troubleshooting scripts, post-installation scripts - with a dozen instances these may be easy to manage manually. But when your number of instances increases, you still want that consistency and when you need to deploy an upgrade to the scripts, you want to test it prior to releasing to production
In this session I will show how I tackle this for hundreds or thousands of instances using my DBA database and PowerShell
Session Details not available
Event Date: 10-12-2016 - Session time: 13:30:00 - Track: BI/DEV
Speaker: n/a n/a
Title: Session Details not available
Session Details not available
Event Date: 10-12-2016 - Session time: 13:30:00 - Track: BI
Speaker: Andrea Martorana Tusa
In SQL Server 2016 Microsoft added support for mobile reports to Reporting Services.
They also released the Mobile Report Publisher for authoring and creation of mobile reports. This application is based on Datazen technology, a company that Microsoft acquired in 2015.
As Microsoft stated, using only one mobile BI app users will access dashboards and reports on mobile devices.
Now developers must choose a tool for developing BI optimized for mobile devices: SSRS, Mobile Report Publisher (former Datazen) or Power BI? The question is: which one to choose? What are the key factor for supporting the correct decision?
We will provide a clear and understandable overview of each product, highlighting strengths and weaknesses.
Based on some real examples we will set out a comparison matrix to find the best way to meet your needs with the right mobile reporting tool.
Giving Permissions through Stored Procedures
Event Date: 10-12-2016 - Session time: 13:30:00 - Track: DEV
Speaker: Erland Sommarskog
Title: Giving Permissions through Stored Procedures
You probably know that with a stored procedure you can permit users to access or update data even if they have no direct permissions on the table. But you may have noticed that this does not work when the SP uses dynamic SQL.
In this session you will learn why it is so and techniques how you can use to overcome this. You will also learn that the same techniques can be to permit users to perform specific actions they need to do without granting them general high-level permissions on database or server level by packaging these actions into a stored procedure. You will also learn why should should be careful with setting a database TRUSTWORTHY.
The session should be of interest both to DBAs and application developers that need to consider advanced permissions in their stored procedures.
Benchmarking like a Pro
Event Date: 10-12-2016 - Session time: 13:30:00 - Track: DBA/DEV
Speaker: Gianluca Sartori
Title: Benchmarking like a Pro
Exciting times ahead! You bought a license for SQL Server 2016 and you are going to upgrade to the new shiny version of SQL Server on a beefy new machine!
Fantastic! Except that you have no idea how your application will work on the new version. There’s a new cardinality estimator in 2016: how will it affect performance? The new features in In-Memory OLTP and Columnstore Indexes look really promising, but how will your workload take advantage of these features?
The best way to know for sure is to conduct a benchmark and compare it to your current system.
In this demo-intensive session you will discover how to capture a meaningful workload in production and how to replay it against your test system. You will also learn which performance metrics to capture and compare, and which tools can help you in the task.
Deadlocks. Everything you ever wanted to ask but were too shy.
Event Date: 10-12-2016 - Session time: 14:45:00 - Track: DEV
Speaker: Denis Reznik
Title: Deadlocks. Everything you ever wanted to ask but were too shy.
It's not a secret that a deadlock - it's not very good. Definition of deadlock is very simple and quite clear: This is an exceptional situation when two concurrent queries request same resources but in a different order. Classic deadlock can occur when two concurrent transactions modifying data from the two tables in a different order. Unfortunately, in a real life deadlocks often are be more complex and unobvious. One of the rules, which I always keep in mind, sounds: "You can not design a database, in which the occurrence of deadlock is impossible". And we should deal with them. The algorithm is simple: catch, analyze, fix. In practice, the process can be challenging and can require different types of analysis.
In this session, we will learn some basics about Locks and Transaction Isolation Levels and then analyse and solve as many deadlocks as we can in 75 min.
Introducing the SQL Server 2016 Query Store
Event Date: 10-12-2016 - Session time: 14:45:00 - Track: DBA/DEV
Speaker: Enrico van de Laar
Title: Introducing the SQL Server 2016 Query Store
With the release of the public preview versions of SQL Server 2016 we were finally able to play with, in my opinion, one of the most exciting new features in SQL Server 2016, the Query Store! The Query Store serves as a flight recorder for your query workload and provides valuable insights into the performance of your queries. It doesn’t stop there however, using the performance metrics the Query Store records, we can decide which Execution Plan SQL Server should use when executing a specific query. If those two features aren’t enough, the Query Store provides all this information inside easy-to-use reports and Dynamic Management Views (DMVs) removing a great deal of the complexity of query performance analysis.
During this session we will take a thorough look at the Query Store, it’s architecture, the build-in reporting, DMVs and the performance impact of enabling the Query Store. No matter if you are a DBA or developer, the Query Store has information that can help you!
Scripting tabular models
Event Date: 10-12-2016 - Session time: 14:45:00 - Track: BI
Speaker: Bent Pedersen
Title: Scripting tabular models
Introducing the new scripting language for tabular models. Before SQL Server 2016 tabular models was wrapped into a multidimensional constructs. TMSL is the new native language for tabular which is build on JSON - this makes it easy to understand, modify and deploy.
During this session I will go through and explain some examples on generating a SSAS tabular model by using the new TSML to demonstrate how powerfull this feature is and how you can use it to save time and effort.mduring this session I will spend some time explaining a real world example and how to make an implementation with MDS and business users.
Event Date: 10-12-2016 - Session time: 14:45:00 - Track: DBA
Speaker: André Kamman
There are many community scripts out there that DBAs can use without having to write them yourself. Like Ola Hallengrens scripts, or Brent Ozars or sp_WhoIsActive by Adam Machanic. But how about Powershell? Turns out, this world is still a bit unknown to the DBA but there is a fast growing community with extremely useful scripts for day to day SQL Server management. In this session we will take a look into my favourite scripts and we will also write a bit of Powershell ourselves.
Real-Time Analytics: OLAP OLTP in the mix!
Event Date: 10-12-2016 - Session time: 14:45:00 - Track: BI/DEV
Speaker: Stijn Wynants
Title: Real-Time Analytics: OLAP OLTP in the mix!
In this session we will talk about the Real-Time analytics possibilities with SQL Server 2016. This will be a deep-dive on how this works, and will give you an explanation on the new Columnstore features. We will also talk about the difference between hot warm data.
Boosting DWH-Performance with SQL Server 2016
Event Date: 10-12-2016 - Session time: 16:00:00 - Track: BI/DEV
Speaker: Markus Ehrenmueller-Jensen
Title: Boosting DWH-Performance with SQL Server 2016
SQL Server 2016 brings the topic "in-memory" to a new level. Not that previous versions did not hold as much as data as possible inside of memory through caching, but now we enter a new era: in-memory row stores, in-memory column stores and compiled stored procedures are different from what we have seen so far. In this session, you will see how you can leverage those new features and how you can come by existing limitations. Come back to office with the knowledge how to boost the performance of your ETL, queries and reports into the new era.
How to recover deleted data with no backups?
Event Date: 10-12-2016 - Session time: 16:00:00 - Track: DBA
Speaker: Vedran Kesegic
Title: How to recover deleted data with no backups?
From time to time on the forums emerge people in really tough situation: data is deleted, database is in SIMPLE recovery model, and as a cherry on top - there is no backup! It might be boss's private database that nobody has put into backup scheme yet, or some new database that was missing from the backup scheme.
It is nearly impossible to recover the lost data from that situation. But I will show you at least three methods. After this presentation, you will be prepared to solve such a tough case. It is really important to know two simple steps you must do really fast, and after that you can relax and extract the data with peace of mind and without time pressure.
SQL Security in the Cloud
Event Date: 10-12-2016 - Session time: 16:00:00 - Track: DBA/DEV
Speaker: Tobias Koprowski
Title: SQL Security in the Cloud
Our data should be secure. And our environment too. What we can do for maximize security in a hybrid environment, where SQL Server exist in two forms: premise and cloud. How organize our job, how control our data if we use Windows Azure SQL Database - The Cloud Database. Physical security, policy based management, auditing, encryption, federation, access and authorization. All of those subjects will be covered on my session.
From MDS to SSRS - a short walkthrough
Event Date: 10-12-2016 - Session time: 16:00:00 - Track: BI
Speaker: Gabi Münster
Title: From MDS to SSRS - a short walkthrough
MDS makes the administration and maintenance of your master data quite easy and comfortable. But how is the effect on your business interfaces? This session will present demos of how the changes to your master data will effect your cube and reports - and how quick they can be available to your business users.
Performance tips for faster SQL queries
Event Date: 10-12-2016 - Session time: 16:00:00 - Track: DEV
Speaker: Emanuele Zanchettin
Title: Performance tips for faster SQL queries
When you need to extract data from the database you are writing, more or less complex, T-SQL code. Often simplistic and procedural approach reflects what you have in your mind, however this could have a negatively impact about performance because the database engine might think otherwise. Fortunately T-SQL, as a declarative language, allows us to ask the "what" and delegate to the engine the "how". Everything works best as long as you respect a few simple rules and you may use special constructs. In this session, with few slides and a lot of real-case scenarios, you can see the advantages of writing the query for high performance, even when they are written by that "someone else" called ORM.