Nr: 686
SQLSaturday #686 - Oregon 2017
SessionID: 67722
Python in SQL Server 2017
Event Date: 28-10-2017 - Session time: 08:30:00 - Track: Analyze
Speaker: Chris Hyde
Title: Python in SQL Server 2017
Abstract:
SQL Server 2017 is right around the corner, bringing with it a host of exciting new features. Of particular interest to BI practitioners and data scientists is the integration of the Python programming language into the SQL ecosystem. This integration opens up a number of possibilities for in-database analytics and intelligent database applications, so let's get started!
In this introductory session we'll briefly introduce the Python language and then look at how to install and administer Machine Learning Services. We'll start to demonstrate the power of Python by showing you how to create and run simple scripts from within T-SQL. We'll then discuss some of the powerful open source packages that are available to aid your data analyses. Finally, we'll go through some examples of how to use Python to integrate data analytical methods in real-world situations, and wow your customers with the power of analytics!
SessionID: 67735
JSON and the Astronauts
Event Date: 28-10-2017 - Session time: 08:30:00 - Track: Build
Speaker: Peter Kral
Title: JSON and the Astronauts
Abstract:
In one small step, SQL Server 2016 has taken a giant leap...to JSON! In this session, we'll launch a probe into a JSON data file containing profiles of NASA astronauts using new JSON T-SQL functions in SQL Server. With each astronaut we meet, we'll learn to validate, read, and manipulate "unstructured" JSON data. We'll get The Right Stuff to start developing, no previous JSON experience required. Let's go where NoSQL has gone before!
Prerequisites:
You should have experience writing queries in T-SQL. No previous knowledge of JSON is needed.
SessionID: 67742
Introduction to Indexes and SARGability
Event Date: 28-10-2017 - Session time: 08:30:00 - Track: Perform
Speaker: Jared Karney
Title: Introduction to Indexes and SARGability
Abstract:
SessionID: 67760
Boost your Analytics with Machine Learning for SQL Nerds
Event Date: 28-10-2017 - Session time: 08:30:00 - Track: Innovate
Speaker: Julie Koesmarno
Title: Boost your Analytics with Machine Learning for SQL Nerds
Abstract:
Are you trying to understand what’s the fuzz around Machine Learning? Are you working with a lot of data analysis and wondered how SQL Server 2016 R Services might be able to help?
This session will provide a gentle and fun introduction to SQL Server R Services for SQL Nerds. We will walk through pragmatic examples of using R through demos, to illustrate Machine Learning in boosting your Data Analysis capabilities. The goal of this session is to provide quick tips and tricks for attendees to get started on SQL Server R Services, either for fun or for work.
SessionID: 68680
Tame Your Unruly Data With Constraints
Event Date: 28-10-2017 - Session time: 08:30:00 - Track: Design
Speaker: Rob Volk
Title: Tame Your Unruly Data With Constraints
Abstract:
SessionID: 69080
Don’t Panic: Use the DBA Cheat Sheet for Administrative Tasks
Event Date: 28-10-2017 - Session time: 08:30:00 - Track: Build
Speaker: Elizabeth Block
Title: Don’t Panic: Use the DBA Cheat Sheet for Administrative Tasks
Abstract:
Do you have trouble remembering common administrative T-SQL commands? Remember when you created a cheat sheet in school to help you study for an exam? Use that same principle with the DBA Cheat Sheet! The DBA Cheat Sheet is a document filled with some basic administrative TSQL commands you’ll need in your daily work. During the session I’ll show you a few SSMS tricks and review some of the topics in the DBA Cheat Sheet document. You’ll leave the session with a document that you can use as a reference every day.
SessionID: 69164
Ultimate Security and Sharing in Power BI
Event Date: 28-10-2017 - Session time: 08:30:00 - Track: Scale
Speaker: Reza Rad
Title: Ultimate Security and Sharing in Power BI
Abstract:
Come to this session to learn all about security and sharing in Power BI. Session starts with covering all types of sharing (basic sharing, work spaces, apps, publish to web, embedded), and their pros and cons. Session will then continue with Row Level Security topic. You will learn how to secure the content of Power BI based on roles. You will learn through live demos different types of implementing row level security, such as; Row level security through SSAS Live query, RLS in Power BI regardless of the data source, and dynamic row level security. Finally, in this session you will learn best practices and architecture advise for both Sharing and Security, and would be able to choose best security and sharing architecture for your Power BI solution requirement.
SessionID: 69188
Adopting Power BI - How to avoid "free puppy" scenario with a new Adoption Framework from Microsoft
Event Date: 28-10-2017 - Session time: 08:30:00 - Track: Evolve
Speaker: Ron Ellis Gaut
Title: Adopting Power BI - How to avoid "free puppy" scenario with a new Adoption Framework from Microsoft
Abstract:
Microsoft has recently created a Power BI Adoption Framework designed to assist customers overcome the challenges and barriers that impede success. This Framework exposes the critical elements of Power BI adoption and provides a structured approach for driving adoption of self-service BI.
Attend this session and be among the first to be exposed to this new content from Microsoft. The session provides an overview of the Adoption Framework, covering each of the four cornerstones components:
Prove Value (awareness)
• What are the essential questions?
• What are the key value drivers?
Governance (understanding)
• What are the core concerns around security and compliance?
Roll Out (enablement)
• How to manage frequent changes, licenses, training, …
Support (enablement)
• What types of support are required?
SessionID: 67730
Welcome To the 2016 Query Store!
Event Date: 28-10-2017 - Session time: 10:10:00 - Track: Perform
Speaker: Janis Griffin
Title: Welcome To the 2016 Query Store!
Abstract:
SessionID: 67755
Using Always Encrypted in Real World
Event Date: 28-10-2017 - Session time: 10:10:00 - Track: Build
Speaker: Dan de Sousa
Title: Using Always Encrypted in Real World
Abstract:
Worried about security? Worried about compliance? Worried that someone might get at your sensitive data?
In this session you will find out how using Always Encrypted, Azure regions and Azure KeyVault you can keep your data secure.
I will demonstrate the technology and some scenarios presented to us (including challenges), what was required for the implementation of Always Encrypted considering the current model and the limitations of the feature, the reasons that led us to choose the path of Azure Regions with Always Encrypted, lessons learned as well as some cool things about the feature.
SessionID: 67790
Self-Service ETL with Power Query
Event Date: 28-10-2017 - Session time: 10:10:00 - Track: Analyze
Speaker: Dan Clark
Title: Self-Service ETL with Power Query
Abstract:
SessionID: 67882
Upgrade your SQL Server like a Ninja
Event Date: 28-10-2017 - Session time: 10:10:00 - Track: Build
Speaker: Amit Banerjee
Title: Upgrade your SQL Server like a Ninja
Abstract:
SessionID: 68391
Whats new for Columnstore in SQL Server 2017
Event Date: 28-10-2017 - Session time: 10:10:00 - Track: Perform
Speaker: Niko Neugebauer
Title: Whats new for Columnstore in SQL Server 2017
Abstract:
SQL Server 2017 is about to be launched and between huge focus on the Linux, there are some important and long-awaited improvements for the Columnstore Indexes.
Do you have LOBs in Fact Tables? Do you need to rebuild your HTAP Indexes online ? There are great improvements for your Columnstore Indexes in SQL Server 2017!
The Intelligent Database concept brings the Adaptive Query Processing for the Batch Execution Mode, with the possibility of Memory Grant Feedback Batch Mode Adaptive Join, which will help to auto-tune your workloads.
Discover all the big and little Columnstore gems of the 2017 edition of the SQL Server!
SessionID: 68686
Modern Data Modeling for Azure Data Lake and Azure SQL DW
Event Date: 28-10-2017 - Session time: 10:10:00 - Track: Design
Speaker: Steve Moss
Title: Modern Data Modeling for Azure Data Lake and Azure SQL DW
Abstract:
The principles of dimensional modeling for data warehousing, especially the work of Dr. Ralph Kimball, have been enormously successful for many years. But are they still relevant to the world of data lakes and big data? And what is their role in a modern DW of a relational database, even massively parallel databases like Azure SQL DW?
This presentation will explore when the concepts of dimensional modeling are still relevant to give value to your Data Lake and Big Data solutions and then cover the reason why a modern DW on Azure SQL DW is still needed. It will conclude with some implementation details of a Modern DW on Azure SQL DW, that relate to modeling including how to maximize performance by minimizing Data Movement (Shuffle Move and PartitionMove operations).
SessionID: 69149
Why is my DBA So Grump? Real Reasons Why Some Code Practices Cause Issues.
Event Date: 28-10-2017 - Session time: 10:10:00 - Track: Build
Speaker: Rick Lowe
Title: Why is my DBA So Grump? Real Reasons Why Some Code Practices Cause Issues.
Abstract:
One of the interesting things about the SQL Server community is how often best practice advice that is widely knows and non-controversial is also widely ignored. Have most of us heard that using NOLOCK is bad? Yes, almost certainly. Does NOLOCK still get used heavily in production code? Yep, you betcha. Presumably the issue is not that we don't care whether or not code is written correctly ... we're all professionals and take pride in our work. It seems more likely that those responsible for the care and feeding of database servers haven't found the right way to explain why some of these practices matter. Come hear Rick's attempts to explain why some code patterns are problematic. Topics covered will include NOLOCK, heaps, and cursors among others.
SessionID: 69186
SQL Server Take Over: Establishing a Baseline
Event Date: 28-10-2017 - Session time: 10:10:00 - Track: Support
Speaker: Lowry Kozlowski
Title: SQL Server Take Over: Establishing a Baseline
Abstract:
SQL Server Take Over: Establishing a Baseline
Performance assessment and tuning for the beginner DBA
Have you ever inherited a group of servers with not a lot of documentation to accompany them? Do you have a lot of undocumented or new servers in your environment and you want to establish some baseline configuration setting prior to making changes? You need up to date information on the system very quickly.
The server is running slowly... or is it? Slow compared to what? Before you being digging into performance issues, there are some baseline metrics you should have established. Only after the information has been gathered, can a plan of attack be developed to help resolve issues with performance. Attendees in this session will learn how to quickly gather information, review the results and get an action plan on the next step in performance tuning.
SessionID: 67733
Make Power BI Your Own with the Power BI APIs
Event Date: 28-10-2017 - Session time: 12:50:00 - Track: Build
Speaker: Steve Wake
Title: Make Power BI Your Own with the Power BI APIs
Abstract:
Power BI makes accessing data from various sources (both on-premise and in the cloud) and building advanced visualizations for analytics easy to do. Behind the scenes Power BI is built on standard technologies and APIs that allow it to be customized in multiple ways to fit in with your environment the way you need it to. In this session we will walk though samples and demos that show how you can extend Power BI with custom visualizations, access custom data sources, embed various Power BI elements in your applications (on-premise and in the cloud), and be able to push your data into a Power BI report/dashboard. Each of these items take advantage of the Power BI APIs in different ways and we will discuss each and provide the code samples for you to take back and get a head start. This session will be showing code to access the Power BI REST API using C# and NodeJS along with some M for accessing and manipulating custom data sources (on-premises or in the cloud).
SessionID: 67757
Will my workload run faster with In-Memory OLTP?
Event Date: 28-10-2017 - Session time: 12:50:00 - Track: Perform
Speaker: Ned Otter
Title: Will my workload run faster with In-Memory OLTP?
Abstract:
There are compelling reasons to consider deploying In-Memory OLTP, vastly improved in SQL 2016, and now running on non-Enterprise editions.
What are the use cases?
What are the tradeoffs?
We will lift the veil of confusion that surrounds this blazingly fast feature. By the end of the session, you will understand how In-Memory OLTP works and whether your workload is likely to see an increase in performance.
SessionID: 67840
Event Date: 28-10-2017 - Session time: 12:50:00 - Track: Support
Speaker: Sander Stad
Abstract:
There are not enough hours in the day to get everything done as a DBA. We need to automate our repetitive tasks to free up time for the important and more fun tasks.
In this session I'll show how dbatools can help you automate and simplify your daily tasks and make your life a lot easier.
SessionID: 67843
Azure Machine Learning 101
Event Date: 28-10-2017 - Session time: 12:50:00 - Track: Analyze
Speaker: Kathi Kellenberger
Title: Azure Machine Learning 101
Abstract:
SessionID: 67915
Linux Administration for the SQL Server DBA
Event Date: 28-10-2017 - Session time: 12:50:00 - Track: Build
Speaker: David Klee
Title: Linux Administration for the SQL Server DBA
Abstract:
Times are certainly changing with Microsoft’s recent announcement to adopt the Linux operating system with the release of SQL Server 2017, and you should be prepared to support it. But, what is Linux? Why run your critical databases on an unfamiliar operating system? How do you handle the basics, such as backing up to a network share or add additional drives for data, logs, and tempdb files?
This introductory session will help seasoned SQL Server DBAs understand the basics of Linux and how it differs from Windows, all the way from basic management to performance monitoring. By the end of the session, you will be able to launch your own Linux-based SQL Server instance on a production-ready VM.
SessionID: 68384
Introduction to Cortana – Turning Data into Intelligent Action
Event Date: 28-10-2017 - Session time: 12:50:00 - Track: Design
Speaker: Melody Zacharias
Title: Introduction to Cortana – Turning Data into Intelligent Action
Abstract:
SessionID: 68762
Well, actually... How to not be THAT guy in IT
Event Date: 28-10-2017 - Session time: 12:50:00 - Track: Evolve
Speaker: Rie Irish
Title: Well, actually... How to not be THAT guy in IT
Abstract:
Unless you've been in a cave, you've seen articles lately on what it's like to be a woman in tech. In 2017, waves of blogs and news articles tell us about those extreme examples of misogyny. Only they aren't fringe, one-off stories. It's happening to women you know.
IT can be an inhospitable place for women. Some call it a "bro-grammer" culture. Compared to men, women are leaving IT at 2.5x and are interrupted 3x as often. Our credentials are called into question. We're called emotional or aggressive. We have our own job explained to us. Sometimes its subtle. Other times it's overt. There's always that one guy who ruins it for the rest of you.
It doesn't have to be that way. Most of the guys we work with are stand-up, respectable WIT allies. The problem is, you don't always notice when it happens. Learn what those subtle forms of sexism look like at work. Learn how you can stop that guy in his tracks. Learn how to not BE that guy, even when you're just trying to help.
SessionID: 69198
Event Date: 28-10-2017 - Session time: 12:50:00 - Track: Perform
Speaker: Vern Rabe
Abstract:
When the SQL Server optimizer evaluates a query to determine how best to execute it, the statistics are quite possibly the most important tool at its disposal. But SQL Server statistics objects aren’t perfect because they only contain estimated summary information. In this session, we’ll start with an overview of what the statistics objects are, how the optimizer uses them, and some general guidelines for their maintenance. Then we’ll look at some of the issues, how to find them, and how to solve them, that can arise due to their imperfection: ascending keys (the most prevalent statistics based performance killer?), correlated predicates, skewed distribution, or downright bad summary information. There’ll be many examples, and even a stored procedure to help you find ascending keys. By applying the techniques we’ll discuss, you WILL see improved query performance.
SessionID: 67724
Why candidates fail the job interview in the first minute
Event Date: 28-10-2017 - Session time: 14:30:00 - Track: Evolve
Speaker: Thomas Grohser
Title: Why candidates fail the job interview in the first minute
Abstract:
Thomas will share what he misses in resumes and how to respond to questions better.
SessionID: 67727
Hi, my name is Powershell, let's be friends! An Intro to PoSH
Event Date: 28-10-2017 - Session time: 14:30:00 - Track: Support
Speaker: Amy Herold
Title: Hi, my name is Powershell, let's be friends! An Intro to PoSH
Abstract:
Powershell wants to be your friend - your very powerful friend. In this session we will go over some Powershell basics that will help get you started as well as some known "gotchas" when working in Powershell. This is a beginner session - no Powershell experience necessary. WARNING: You may get warm-fuzzies about Powershell by attending this session.
SessionID: 68324
What is new for database developers in Visual Studio 2017
Event Date: 28-10-2017 - Session time: 14:30:00 - Track: Build
Speaker: Frank Geisler
Title: What is new for database developers in Visual Studio 2017
Abstract:
SessionID: 68329
On Transactions and Atomic Operations
Event Date: 28-10-2017 - Session time: 14:30:00 - Track: Build
Speaker: Gail Shaw
Title: On Transactions and Atomic Operations
Abstract:
"If there’s one thing that we, as SQL developers don’t do, it’s use transactions as often as we should.
Transactions are critical when multiple changes need to be made entirely or not at all, but even given that it’s rare to see transactions used at all in most production code
In this session, we’ll look at what transactions are and why we should use them. We’ll explore the effects transactions have on locking and the transaction log. We’ll investigate methods of handling errors and undoing data modifications, and we’ll see why nested transactions are a lie.
-
Understand what transactions are and what they do.
-
Learn how to use transactions in combination with error handling to roll back changes on errors
-
Learn how transactions affect logging and locking.
"
SessionID: 68489
Locks, Blocks, and Snapshots: Maximizing Database Concurrency
Event Date: 28-10-2017 - Session time: 14:30:00 - Track: Perform
Speaker: Bob Pusateri
Title: Locks, Blocks, and Snapshots: Maximizing Database Concurrency
Abstract:
SessionID: 68999
Data Wrangling for Reporting Analytics
Event Date: 28-10-2017 - Session time: 14:30:00 - Track: Analyze
Speaker: Phil Robinson
Title: Data Wrangling for Reporting Analytics
Abstract:
SessionID: 69182
Introduction to SQL Server Reporting Services
Event Date: 28-10-2017 - Session time: 14:30:00 - Track: Build
Speaker: Ted Stathakis
Title: Introduction to SQL Server Reporting Services
Abstract:
This session will cover the basics of developing, publishing and using SQL Server Reporting Services. You will learn all the steps needed to get started from connecting to the data source, creating data sets through putting the data on the page and publishing the report to the report server. See how easy SSRS is to use and how powerful it can be. The demo will be using SSRS 2016 but the concepts covered are applicable to any version of SQL Server.
The session will also include:
A tour of a wide variety of report examples including interactive graphical dashboards that you can do in SSRS, even in older versions.
A comprehensive discussion of design elements to include and avoid when creating reports.
Report printing and export considerations for optimal output depending on rendering format.
You will leave this session able to return to the office and present meaningful data to your end users with minimal time and effort with easy to use and powerful BI tool that you already own.
SessionID: 69215
Understanding SQL Database-as-a-Service in Microsoft Azure Stack
Event Date: 28-10-2017 - Session time: 14:30:00 - Track: Evolve
Speaker: Michael Wells
Title: Understanding SQL Database-as-a-Service in Microsoft Azure Stack
Abstract:
SessionID: 67743
SQL Server Containers and Clones
Event Date: 28-10-2017 - Session time: 15:55:00 - Track: Innovate
Speaker: Paul Stanton
Title: SQL Server Containers and Clones
Abstract:
“Containers and Clones for SQL Server Development and Test”
Microsoft is emphasizing the use of containers for development and test on SQL Server 2017, and new solutions from Red Gate and Windocks add support for containers and SQL Server database database cloning, for the full range of the SQL Server family. We’ll explore use of Docker based tooling for delivery of container environments, and their pros and cons. We’ll also look at SQL Server cloning, and how clones enable Dev and Test teams to work on large database environments for containers as well as existing SQL Server instances.
Containers and Clones are particularly well suited for Public and Hybrid Cloud, as it’s infrastructure agnostic. Container images are ultimately portable, and the built-in database cloning is especially useful on public clouds.
SessionID: 67909
Upcoming Improvements to SQL Server Always On Availability Groups
Event Date: 28-10-2017 - Session time: 15:55:00 - Track: Perform
Speaker: Sourabh Agarwal
Title: Upcoming Improvements to SQL Server Always On Availability Groups
Abstract:
In this session we will learn about the improvements in the most recent versions (SQL 2016 and above) of SQL Server that can help improve the scalability, reliability and supportability of SQL Server Always On Deployments. This session would focus on the numerous improvements being driven by the SQL Server Tiger Team to in-market versions of SQL Server. Ranging from the diagnostics and troubleshooting improvements to added feature capabilities, learn how these can be utilized improve the Always On Availability Groups deployment experiences.
SessionID: 68249
SQL Server 2017: Intelligent Diagnostics Just Built-In
Event Date: 28-10-2017 - Session time: 15:55:00 - Track: Innovate
Speaker: Parikshit Savjani
Title: SQL Server 2017: Intelligent Diagnostics Just Built-In
Abstract:
SessionID: 68422
Designing an alternative to Replication - a Real World™ Example
Event Date: 28-10-2017 - Session time: 15:55:00 - Track: Build
Speaker: Ben Thul
Title: Designing an alternative to Replication - a Real World™ Example
Abstract:
Transactional Replication gets a bad rap for being fragile and somewhat difficult to manage. Given that reputation, we decided to try to accomplish some of the same things that it does using alternative means. Let me regale you with the tale of that journey. You may come out of it with a newfound appreciation of Replication or you may find yourself asking "that's pretty neat!". Only one way to find out, though…
SessionID: 68621
SQL Server Then and Now: Changing the State of Long-held Beliefs
Event Date: 28-10-2017 - Session time: 15:55:00 - Track: Support
Speaker: Maxwell Myrick
Title: SQL Server Then and Now: Changing the State of Long-held Beliefs
Abstract:
We’ve all heard the rules for SQL Server like separate your data from your log, watch for a disk queue length over 2, and a low Page Life Expectancy always means memory pressure. All of these used to be true, but on today’s hardware and SQL Server versions – they are dangerously outdated. In this session, we will take on a bunch of these old “givens” of SQL Server that everybody has heard and followed, the underlying technological reasons they were true then, how today’s technology has rendered them obsolete, and what the new rules are that you need to know. So whether you are one of the old guard looking to refresh your set of working SQL Server rules or a member of the up-and-comers looking for the facts to win over the old guard, this session is for you.
SessionID: 68642
Event Date: 28-10-2017 - Session time: 15:55:00 - Track: Perform
Speaker: Hamish Watson
Abstract:
TempDB is an integral part of SQL Server, and is often overlooked when trying to get the most out of your system. Researching the many suggested methods of tuning TempDB is a long, arduous and at times confusing process.
In this session we will go over what TempDB is used for, why it is a special and important type of database and how to tune it to enhance the performance of your overall system.
We will start with determining the best configuration of TempDB database files to use, the impact disk configuration has, which trace flags are important and what changes SQL Server 2016 will bring us. We will look at what is stored in TempDB and how both developers and DBA can optimize how TempDB is configured and used.
We will then use these best practices to optimize our instance and performance tune our queries to make the system faster.
SessionID: 69178
DAX Filters: A Quest for Clarity
Event Date: 28-10-2017 - Session time: 15:55:00 - Track: Design
Speaker: Derek Rickard
Title: DAX Filters: A Quest for Clarity
Abstract:
If you are working with SSAS Tabular, Power BI, or good ol' Power Pivot for Excel, you've likely run into a need for DAX, the formula and query language of these tools. A very common task is to augment, override, or otherwise modify a filter context. This sounds easy enough, but if you've had experience beyond simple filters, the syntax and behavior may seem confusing, or worse, counter-intuitive. The purpose of this presentation is to discuss the different types of filters, their behavior, and their interactions with other DAX formulas.