sqlsathistory

Nr: 576

SQLSaturday #576 - Salt Lake City 2016

Start Time (24h)	Speaker	Track	Title
09:00:00	Ben Miller	Enterprise Database Administration Deployment	Inside the SQLPS PowerShell Module
09:00:00	Brandon Preece	Enterprise Database Administration Deployment	Introduction to the SQL Server Query Store
10:10:00	Dale Cunningham	Enterprise Database Administration Deployment	Creating an Always On Distributed Availability Group
10:10:00	Lars Rasmussen	Enterprise Database Administration Deployment	Alerts: Knowing Is Half the Battle!
10:10:00	Randy Knight	Application Database Development	Locks, Blocks, and Deadlocks, Oh My!
10:10:00	Steve Seeley	BI Information Delivery	Power to the people
11:20:00	Scott Cameron	BI Platform Architecture, Development Administration	Introduction to Azure SQL Data Warehouse
11:20:00	J May	Strategy and Architecture	SQL Server 2016 Always On Availability Groups Perf Internals
13:20:00	David Dye	BI Platform Architecture, Development Administration	Advanced SSIS Techniques
13:20:00	Greg McMurray	Advanced Analysis Techniques	MMM: Making the Most of the M Language - Real World Power Query for Excel and Power BI
13:20:00	Jason Brimhall	Enterprise Database Administration Deployment	Extended Events - Better than Profiler
14:30:00	David Dye	BI Platform Architecture, Development Administration	Dealing With Errors in SSIS
14:30:00	Chris Hyde	Analytics and Visualization	SQL Server R Services in SQL 2016
14:30:00	Tracy Boggiano	Strategy and Architecture	Disaster Recovery Where to Begin
14:30:00	TJay Belt	Enterprise Database Administration Deployment	PowerBI, from a DBA
15:40:00	George Walkey	Enterprise Database Administration Deployment	Using Powershell to Document SQL Server
15:40:00	Tracy Boggiano	Enterprise Database Administration Deployment	Query Store Primer
15:40:00	Randy Knight	Application Database Development	But it worked great in Dev! Perfomance for Devs
15:40:00	Jason Brimhall	Enterprise Database Administration Deployment	Bare Naked Skinny on SQL Server
15:40:00	Robert Every	Enterprise Database Administration Deployment	Database Restore Testing...Automated

SessionID: 54185

Inside the SQLPS PowerShell Module

Back to calendar

Event Date: 19-11-2016 - Session time: 09:00:00 - Track: Enterprise Database Administration Deployment

Speaker: Ben Miller

Title: Inside the SQLPS PowerShell Module

Abstract:

SQLPS has been evolving since 2012 and the SQL Provider/cmdlets have been around since 2008. Have you taken advantage of the PowerShell infrastructure that is on your servers? This session takes you behind the scenes of the SQLPS module that is installed with the tools. We will cover how to get it without the tools, how to use it once you have it and why you will want to use it for simple tasks or complex ones. When you learn how to navigate SQL Server and all its services using Paths, you won't go back. Join me for a fun filled hour and learn how to keep SQLPS from becoming frustrating and a black box. You will be glad you did. Let's continue the journey to becoming a PowerShell DBA.

SessionID: 56473

Introduction to the SQL Server Query Store

Back to calendar

Event Date: 19-11-2016 - Session time: 09:00:00 - Track: Enterprise Database Administration Deployment

Speaker: Brandon Preece

Title: Introduction to the SQL Server Query Store

Abstract:

Don't you wish there was a recording of everything running on your SQL Server instance tracking performance data? Well in SQL Server 2016, Microsoft has introduced a new feature called the Query Store. This session will go over how the query store works, how you configure it for your workloads and how to use the information to track performance data. When you leave this session, you will know how to get started using the Query Store.

SessionID: 53640

Creating an Always On Distributed Availability Group

Back to calendar

Event Date: 19-11-2016 - Session time: 10:10:00 - Track: Enterprise Database Administration Deployment

Speaker: Dale Cunningham

Title: Creating an Always On Distributed Availability Group

Abstract:

Always On has developed and matured into an effective HA/DR solution with SQL 2016. Most enterprise DBAs have built a cluster and installed Always On Availability Groups (AOAG), this session will take the Always On technology one step further and discuss the new features in SQL 2016 that will allow a DBA to have live, read only database replicas in a geographically diverse location, in a completely different domain cluster and as a member of a completely separate Availability Group (AG). This session will go through the steps of identifying the pre-requisites, limitations and installation steps to setup a Distributed Availability Group from start to finish! Come and watch how to do it!

SessionID: 54190

Alerts: Knowing Is Half the Battle!

Back to calendar

Event Date: 19-11-2016 - Session time: 10:10:00 - Track: Enterprise Database Administration Deployment

Speaker: Lars Rasmussen

Title: Alerts: Knowing Is Half the Battle!

Abstract:

Learn how to quickly configure alerts for several instances using tools that ship with SQL Server. Increasing visibility of the errors occurring in your servers can expose the cobras that have been hiding there all along. Scripts will be made available to attendees.

SessionID: 54440

Locks, Blocks, and Deadlocks, Oh My!

Back to calendar

Event Date: 19-11-2016 - Session time: 10:10:00 - Track: Application Database Development

Speaker: Randy Knight

Title: Locks, Blocks, and Deadlocks, Oh My!

Abstract:

"I've got a locking problem", "I need to get rid of blocking," and "deadlocks are normal behavior" are all things heard way too many times.

In this session targeted at the accidental or new DBA, we will walk through each of these items, what they are, what their causes are, which of them are normal behavior and which are not, and some common causes of deadlocks and excessive blocking.

SessionID: 54846

Power to the people

Back to calendar

Event Date: 19-11-2016 - Session time: 10:10:00 - Track: BI Information Delivery

Speaker: Steve Seeley

Title: Power to the people

Abstract:

Over the years Microsoft has released many power products. We are all familiar with PowerPoint and PowerShell but how familiar are you with PowerQuery, Power QA, PowerView and Power BI? In this session we will take a closer look at the new tools Microsoft has released to enable true self service BI

SessionID: 53901

Introduction to Azure SQL Data Warehouse

Back to calendar

Event Date: 19-11-2016 - Session time: 11:20:00 - Track: BI Platform Architecture, Development Administration

Speaker: Scott Cameron

Title: Introduction to Azure SQL Data Warehouse

Abstract:

This session will introduce you to Microsoft Azure SQL Data Warehouse and show you how it compares to other technology options. You will learn about the benefits and idiosyncracies of working with SQL DW's massively parallel processing architecture. Then you will get to see a demo of provisioning, loading, and querying SQL DW.

SessionID: 56199

SQL Server 2016 Always On Availability Groups Perf Internals

Back to calendar

Event Date: 19-11-2016 - Session time: 11:20:00 - Track: Strategy and Architecture

Speaker: J May

Title: SQL Server 2016 Always On Availability Groups Perf Internals

Abstract:

Always On Availability Groups (AGs) were introduced in SQL Server 2012. AGs significantly expanded our HA/DR options. However, in both SQL 2012 and SQL 2014 two limitations were extant in the code—both Network Transport and Log Redo significantly compromised performance. In SQL Server 2016, these limitations have been significantly improved.

Our work shows enhancements of 7x – 10x, allowing over 1/2GB/s (4Gb/s) across the wire. These improvements expand solutions options for Tier 1 OLTP and Data Warehouse workloads, Tier 1 and Tier 2 consolidation, and more. This presentation explains the improvements, characterizes the new solutions, and shows commodity servers and modern non-shared in-server flash storage showing the profound contrast between SQL Server 2014 vs. 2016. Doing so moves the performance bottleneck from the SQL Server codebase to the CPU where it belongs.

SessionID: 53518

Advanced SSIS Techniques

Back to calendar

Event Date: 19-11-2016 - Session time: 13:20:00 - Track: BI Platform Architecture, Development Administration

Speaker: David Dye

Title: Advanced SSIS Techniques

Abstract:

As an ETL developer using SQL Server Integration Services have you ever wondered how to:

Import all .csv files from a folder
Replace dynamic SQL using variables in SSIS
How to configure the data flow buffer
Create custom logging solutions
Use a script component as a data flow transformation This session focuses on advanced techniques that are used in SSIS and is meant for the experienced SSIS developer. We will focus on tasks and techniques that are native to SSIS 2005-2014 as well as some of the more advanced and vague properties.
Attendees must have a good working knowledge of SSIS as well as familiarity with VB.NET or C# as some time will be taken looking at extending SSIS with script tasks and components. Upon completion of this workshop attendees will be able to:
Import/export multiple sources/destinations
Use an execute SQL task with variables to replace dynamic SQL within a package
Dynamically handle errors within any part of a package

SessionID: 54341

MMM: Making the Most of the M Language - Real World Power Query for Excel and Power BI

Back to calendar

Event Date: 19-11-2016 - Session time: 13:20:00 - Track: Advanced Analysis Techniques

Speaker: Greg McMurray

Title: MMM: Making the Most of the M Language - Real World Power Query for Excel and Power BI

Abstract:

Learn how to leverage the diverse abilities of Power Query to acquire and transform data in this example driven session. Our journey will begin with CSV and we will work our way through HTML tables, JSON data, stop to examine REST and OData endpoints, and end with the spectacular view of connecting to SQL Server 2016. Each of these data types can present a unique opportunity to combine elements of the M language in Power Query to transform and massage the data before finally presenting to the visualization engine for display. Come learn to use the tricks of expanding records and nested tables to flatten out your data model and be better prepared to tackle any size or shape of data that comes your way.

SessionID: 54445

Extended Events - Better than Profiler

Back to calendar

Event Date: 19-11-2016 - Session time: 13:20:00 - Track: Enterprise Database Administration Deployment

Speaker: Jason Brimhall

Title: Extended Events - Better than Profiler

Abstract:

Extended Events were introduced in SQL Server 2008. With SQL 2014, we have seen a significant upgrade to this feature. Join me for a little adventure into the realm of extended events. We will discuss how to use extended events to aid in performance tuning and in day to day administration. We will also explore some background and the architecture of extended events. This is a fast paced session and will be a lot of fun. Attendees will be able to take back several sample XE sessions to help troubleshoot different issues and get to more intimately know their data/environment.

SessionID: 53520

Dealing With Errors in SSIS

Back to calendar

Event Date: 19-11-2016 - Session time: 14:30:00 - Track: BI Platform Architecture, Development Administration

Speaker: David Dye

Title: Dealing With Errors in SSIS

Abstract:

Errors can occur anywhere within an SSIS package and the cause and error message can be somewhat cryptic. This session focuses on identifying error causes and handling them dynamically and proactively. The means of dealing with errors is defined by where the error occurs and we will cover error handling in control flow, data flow, and script tasks in great detail.

You will learn: •To identify the different error handling methods in control flow, data flow, and script tasks •Be able to utilize event handlers to proactively handle errors •Be able to redirect data error and proactively handle data flow errors •Be able to identify error descriptions and log error details

SessionID: 53975

SQL Server R Services in SQL 2016

Back to calendar

Event Date: 19-11-2016 - Session time: 14:30:00 - Track: Analytics and Visualization

Speaker: Chris Hyde

Title: SQL Server R Services in SQL 2016

Abstract:

One of the most exciting new features of SQL Server 2016 is the integration of the R statistical programming language into the platform with the brand-new SQL Server R Services. In this introductory session we'll briefly introduce the R language and then look at how to install and administer SQL Server R Services. We'll start to demonstrate the power of R by showing you how to create and run simple R 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 R to integrate data analytical methods in real-world situations, and wow your customers with the power of statistics!

SessionID: 54143

Disaster Recovery Where to Begin

Back to calendar

Event Date: 19-11-2016 - Session time: 14:30:00 - Track: Strategy and Architecture

Speaker: Tracy Boggiano

Title: Disaster Recovery Where to Begin

Abstract:

So you have a good backup strategy in place, but now you have to think about a disaster recovery. Where do you start and what are your options? SQL Server offers several options, but then how do you choose the right one to meet your business requirements? In this session, we will go over some of the common DR options including log shipping, replication, mirroring, clustering, and AlwaysOn. Then we will talk about how to choose the best one to meet your business needs.

SessionID: 56475

PowerBI, from a DBA

Back to calendar

Event Date: 19-11-2016 - Session time: 14:30:00 - Track: Enterprise Database Administration Deployment

Speaker: TJay Belt

Title: PowerBI, from a DBA

Abstract:

Microsoft’s PowerBI is the new hotness. In this session, you will get your hands dirty and dive into PowerBI and create an actual report, with actual data. You will get to learn from examples from what a DBA has done to implement PowerBI in his shop and make his data accessible.

As a DBA, we often need to collect and display data in a friendly and interactive fashion. We will play with some PowerShell to collect data from several systems, store this data in an Azure DB in the Cloud, then point PowerBI at this data to display and interact with said data. You will learn a little about PowerShell, Azure SQL DB, PowerBI, and how to make these work for you.

You will be given permissions to an Azure DB, and get connected to this source. You will also need to have PowerBI desktop downloaded and installed. We will be using this to create the report during the session. So please come prepared to interact with the internet, with a cloud-based database you will be provided, and PowerBI Desk

SessionID: 53435

Using Powershell to Document SQL Server

Back to calendar

Event Date: 19-11-2016 - Session time: 15:40:00 - Track: Enterprise Database Administration Deployment

Speaker: George Walkey

Title: Using Powershell to Document SQL Server

Abstract:

Most DBAs use multiple native and third party tools to fully document SQL Server instances yet still dont get all the objects. Even using Visual Studio and Database Projects, not all objects are exported because they live at the server level, above the database. Using Powershell, we will script out an organized set of files that can be checked into any Source Control system and or used for Disaster Recovery purposes. Objects exported include: SQL Agent Jobs/Alerts/Schedules, NT Service Credentials, SSIS-MSDB, SSIS-Catalog, SSAS Cubes, SSRS Reports, Logins, Server Configs, Linked Servers, DB Mail Operators/Accounts, Server Triggers and much more

SessionID: 54144

Query Store Primer

Back to calendar

Event Date: 19-11-2016 - Session time: 15:40:00 - Track: Enterprise Database Administration Deployment

Speaker: Tracy Boggiano

Title: Query Store Primer

Abstract:

This session will take a look at the new Query Store feature in SQL Server 2016. Query Store tracks changes in execution plans, allowing you to easily view performance differences and revert to older plans with a few clicks of the mouse. We will take a hands on look at how to use Query Store to troubleshoot problems as well as some of the new DMVs you can use to find issues as they arise.

SessionID: 54439

But it worked great in Dev! Perfomance for Devs

Back to calendar

Event Date: 19-11-2016 - Session time: 15:40:00 - Track: Application Database Development

Speaker: Randy Knight

Title: But it worked great in Dev! Perfomance for Devs

Abstract:

If you've ever found yourself stating the above, this session is for you. For many developers, writing T-SQL that works is not the challenge. But too often, functional T-SQL is not the same as good T-SQL. In this session, we will examine why "SQL that works" is not good enough. Understanding indexes, exectuion plans, sargability, and more are all critical to writing good T-SQL. We will also examine several real-world examples of T-SQL that "worked great in dev" but caused major issues when it hit production.

SessionID: 54444

Bare Naked Skinny on SQL Server

Back to calendar

Event Date: 19-11-2016 - Session time: 15:40:00 - Track: Enterprise Database Administration Deployment

Speaker: Jason Brimhall

Title: Bare Naked Skinny on SQL Server

Abstract:

Ever feel like there are just too many layers with SQL Server? You peel off a layer and behold there is yet another layer. SQL Server is rather complex. The complexities can be rather frustrating from time to time.

Join this NSFW (not safe for whining) session as we strip SQL down to the bare bones. I will help you undress the complexities of a few of the basic SQL Server concepts. These concepts will include locks, principals, query directives and deadlocks.

In this session I will disrobe misunderstandings related to the aforementioned topics. I will show the different types of principals in SQL Server. I will also show various scenarios involving locks and deadlocks along with some of their effects with and without query directives. The goal is to better understand these topics and to hopefully stop using and perpetuating common mis-uses of terminology and common misconceptions about the technology.

SessionID: 54472

Database Restore Testing...Automated

Back to calendar

Event Date: 19-11-2016 - Session time: 15:40:00 - Track: Enterprise Database Administration Deployment

Speaker: Robert Every

Title: Database Restore Testing...Automated

Abstract:

How do we know the backup files of our databases are free from corruption? Why would we care? The hardware storing the backup files is reliable…right? What could possible go wrong? Right? The only way to know our database backup files are in good order is to validate them. How do we do that? One way is to restore the databases from those backup files and check the integrity of the restored databases. This session will show some benefits of database restore testing and walk through the code of a SQL Agent job that automates it.

This site is open source. Improve this page.