Nr: 863
SQLSaturday #863 - Raleigh 2019
SessionID: 89398
SQL Server Basic Backups for Beginners
Event Date: 27-04-2019 - Session time: 08:30:00 - Track: Support
Speaker: Mark Gordon
Title: SQL Server Basic Backups for Beginners
Abstract:
If you just started using SQL Server and may be unsure of the need for backups or even how to do them, this is for you. It is very basic and will cover Recovery Models, Backup Types. In addition, I will demonstrate backups and a restore.
SessionID: 89743
Event Date: 27-04-2019 - Session time: 08:30:00 - Track: Evolve
Speaker: Sandra Peele
Abstract:
Imagine you have a new employee with valuable SQL experience, but from a different DBMS platform. Or imagine you’ve been assigned to a project that involves a DBMS other than SQL Server. What do you need to know? In this high-level presentation, Sandra will highlight differences between default Windows installations of Oracle, Postgres, DB2, SQLite, MySQL, and SQL Server. She’ll share her experience moving from one platform to another and what she has found to be the most important thing to keep in mind when working with a new DBMS or working with a new employee who comes from another DBMS environment.
This presentation is for anyone with a basic knowledge of SQL. It was co-produced with Stéphane Faroult, author of The Art of SQL, SQL Success and other technical books. The presentation uses techniques found in his latest book, Getting the Message Across: Using Slideware Effectively for Technical Presentations.
SessionID: 90613
Why I use cursors in SQL
Event Date: 27-04-2019 - Session time: 08:30:00 - Track: Design
Speaker: Anders Pedersen
Title: Why I use cursors in SQL
Abstract:
"Cursors are bad, never use them!"
How many times have you been told this by your senior DBAs or developers?
In this session I will explore why I use cursors to solve certain problems.
Discuss when they are appropriate, and when they are not.
SessionID: 90622
Database Corruption! Preventing Disaster to your Database.
Event Date: 27-04-2019 - Session time: 08:30:00 - Track: Support
Speaker: Jeff Mlakar
Title: Database Corruption! Preventing Disaster to your Database.
Abstract:
Every DBA should know about database corruption because it can be a resume generating event. A corrupt database can ruin your day, affect system stability, lead to unnecessary downtime, and possibly data loss.
In this presentation we will explore database corruption, learn how to diagnose, and see what we can do to fix it. We will discuss the causes, dispel some myths, and learn what questions to ask. After this, we dive into discovering the root cause and putting a plan in place to stop it in the future. Demos will illustrate diagnosing, troubleshooting, and fixing database corruption. Prepare to break a database and go through the steps to return it back to a usable state.
Beginners will benefit along with others who may one day find themselves with a corrupt database. By the end of the session, you will have the tools and understanding necessary to take corrective action and prevent your databases from corruption!
SessionID: 90694
Power BI Deployed
Event Date: 27-04-2019 - Session time: 08:30:00 - Track: Design
Speaker: Carlos L Chacon
Title: Power BI Deployed
Abstract:
SessionID: 91221
Diagnosing IO Latency
Event Date: 27-04-2019 - Session time: 08:30:00 - Track: Perform
Speaker: Collin Benkler
Title: Diagnosing IO Latency
Abstract:
How to identify when there are IO bottlenecks by walking through the Windows storage IO stack. Starting at the top with SQL Server all the way down to miniport driver, collect and analyze data using DMV queries, PerfMon, storport tracing and WPR tracing to identify and isolate IO bottlenecks.
SessionID: 89440
Utilizing Azure DevOps on a BI Project
Event Date: 27-04-2019 - Session time: 09:45:00 - Track: Build
Speaker: Liz Barrett
Title: Utilizing Azure DevOps on a BI Project
Abstract:
DevOps has historically been a term associated with software engineering, but did you know it can have a number of benefits in the BI space? Using Azure DevOps, code changes and ETL pipelines can be managed and deployed in a partially or fully automated, repeatable process. In doing so, bugs and integration issues can be detected and resolved early, while also saving time in doing manual migrations.
In this session, we will explore the intersect between Azure DevOps and BI, the tools needed to implement, and a real-life example of how it all works together.
SessionID: 90252
SQL Restore: Optimizing Your Data Recovery
Event Date: 27-04-2019 - Session time: 09:45:00 - Track: Perform
Speaker: Jamie Wick
Title: SQL Restore: Optimizing Your Data Recovery
Abstract:
How long will the restoration process take?
Can it run faster?
In this session, we’ll answer these questions by looking at the Disaster Recovery process and the options available for performance tuning database restores.
SessionID: 90585
Moving Data with Azure Data Factory
Event Date: 27-04-2019 - Session time: 09:45:00 - Track: Build
Speaker: Andy Leonard
Title: Moving Data with Azure Data Factory
Abstract:
Azure Data Factory - ADF - is a cloud data engineering solution. ADF version 2 sports a snappy web GUI (graphical user interface) and supports the SSIS Integration Runtime (IR) - or "SSIS in the Cloud."
Attend this session to learn:
- How to build a "native ADF" pipeline;
- How to lift and shift SSIS to the Azure Data Factory integration Runtime; and
- ADF Design Patterns to execute and monitor pipelines and packages.
SessionID: 90630
Dodo - Using PowerShell to Deploy TSQL to Multiple Servers
Event Date: 27-04-2019 - Session time: 09:45:00 - Track: Build
Speaker: Jeff Nowiski
Title: Dodo - Using PowerShell to Deploy TSQL to Multiple Servers
Abstract:
Do you have multiple servers in your environment?
Do you have have multiple databases that share the same schema on the same instance (sharding)?
Do you wish you had a simple way to run a TSQL script against an instance without having to use SSMS?
Do you tire of other people forgetting to deploy to one of your servers?
Do you wish you had a way to communicate when someone deployed code to an instance?
Do you enjoy ASCII art?
Are you lazy?
If you answered yes to any of the above and wish you had an easier way to "Right click file(s)" and just deploy them to instance(s), I think I might have the solution for you. This is a free and open source PowerShell based solution that you are welcome to use. It can rely on a CMS (Central Management Server) or CSV file for server list.
This doesn't solve the need for a proper release process or CI Automation but sometimes you just need to do things manually.
SessionID: 90660
Event Date: 27-04-2019 - Session time: 09:45:00 - Track: Innovate
Speaker: Shannon Lowder
Abstract:
With that schema, automatically generating your extract package is a matter of seconds! Join me while I share my interrogator project with you and start saving hours at work today!
SessionID: 90702
Automating the Data-Centric Deployment Pipeline
Event Date: 27-04-2019 - Session time: 09:45:00 - Track: Perform
Speaker: John Welch
Title: Automating the Data-Centric Deployment Pipeline
Abstract:
SessionID: 91836
SQL Clinic Lab
Event Date: 27-04-2019 - Session time: 09:45:00 - Track: Support
Speaker: Kevin Feasel
Title: SQL Clinic Lab
Abstract:
SessionID: 89421
Encrypting Data within Sql Server
Event Date: 27-04-2019 - Session time: 11:00:00 - Track: Design
Speaker: Thomas Norman
Title: Encrypting Data within Sql Server
Abstract:
In this session, we will review each option to encrypt data within SQL Server, Transparent Data Encryption, Cell Level Encryption and Always Encrypted. The session will deep dive into setting up Always Encrypted encryption and share my experiences implementing the encryption. In addition, the session will review auditing of sensitive data usage.
You will leave the session with a deeper understanding about SQL Server encryption options and how encryption can affect your performance. In addition, you will gather information on how to fully protect and review your sensitive data usage.
SessionID: 89741
Index Trek: An Introduction to Nonclustered, Clustered, and Columnstore Indexes
Event Date: 27-04-2019 - Session time: 11:00:00 - Track: Perform
Speaker: Sandra Peele
Title: Index Trek: An Introduction to Nonclustered, Clustered, and Columnstore Indexes
Abstract:
When learning something new, it’s helpful to have a foundation. Yet, indexes are often taught with only one or two sentences of explanation before CREATE INDEX. I recall a student, exasperated by the textbook definition saying, “Will someone just SHOW ME an index!” The goal of this high level presentation is to do just that. In this 60 minute mission, we’ll explore nonclustered and clustered indexes as well as the strange new world of columnstore indexes. Rather than focus on space and time requirements of indexes and index maintenance, we’ll seek out new understanding:
• Why well-designed indexes speed up queries.
• Why queries must be engineered carefully to use indexes.
• Why indexes have both benefits and drawbacks.
After the mission, you’ll be prepared to further explore the galaxy of indexes.
This presentation was co-produced with Stéphane Faroult, author of The Art of SQL and Getting the Message Across: Using Slideware Effectively for Technical Presentations.
SessionID: 90566
Migrate Your Database with Distributed AGs
Event Date: 27-04-2019 - Session time: 11:00:00 - Track: Build
Speaker: Elizabeth Noble
Title: Migrate Your Database with Distributed AGs
Abstract:
You find yourself needing to migrate to new hardware or new operating system. If you're running SQL Server 2016+, you're in luck! Distributed Availability Groups (AGs) may just be the solution you want.
In this session I will explain the purpose and usages of Distributed AGs, create a Distributed AG and the associated AGs, and perform a mock data center migration.
NOTE: This session expects that attendees are already familiar with Availability Groups.
SessionID: 90623
Environmental SQL Server Troubleshooting
Event Date: 27-04-2019 - Session time: 11:00:00 - Track: Support
Speaker: Jeff Mlakar
Title: Environmental SQL Server Troubleshooting
Abstract:
In this session we will discuss both performance pitfalls and optimizations that can be made to the system outside of code changes. We will diagnose configurations commonly misapplied and review best practices in SQL Server, Windows Server, and VMware!
SessionID: 90642
TempDB Inside Out
Event Date: 27-04-2019 - Session time: 11:00:00 - Track: Design
Speaker: Ryan Adams
Title: TempDB Inside Out
Abstract:
Either you are experiencing TempDB contention or you are not sure and want to find out. This pain is something every DBA has experienced or will experience, but it's not easy to figure out how to set it up correctly. You will learn how to identify if TempDB is having contention, what causes it to happen, where it can come from, and how to fix it. We will discuss the required trace flags and when to use them as I demo the behavior. You will walk away with the scripts and knowledge of how to configure TempDB per best practice.
SessionID: 90730
SSMS Tips and Tricks
Event Date: 27-04-2019 - Session time: 11:00:00 - Track: Perform
Speaker: Wayne Sheffield
Title: SSMS Tips and Tricks
Abstract:
Almost everyone uses SSMS to write queries and to browse around on the server - but do you use all of the features that SSMS offers to increase your productivity? Most people use few of these, and probably don't even know that many of these features even exist. Come to this session, consisting almost entirely of demos in SSMS, to see features built in to SSMS that will improve your experience and productivity. Time permitting; we'll also go into some 3rd party products that also increase your productivity in SSMS.
SessionID: 90049
CozyRoc: Dynamic Data Flows in SSIS Without Programming
Event Date: 27-04-2019 - Session time: 12:15:00 - Track: Vendor Session
Speaker: Diane Schuster
Title: CozyRoc: Dynamic Data Flows in SSIS Without Programming
Abstract:
This session is an introduction to the unique functionality offered by COZYROC to create integration packages that dynamically map columns at runtime. The standard SSIS Data Flow Task can only use static metadata which is defined when the package is initially designed. The mapping of columns is configured and remains unchanged until the package is re-opened and design changes are made, thus costing maintenance time and effort. Data Flow Task Plus extends the capabilities of the standard Microsoft Data Flow task in that it allows you to set up dynamic data flows, thus eliminating maintenance time and effort. Changes to tables and columns are automatically incorporated at run time without the need to change and redeploy the package. Columns and fields are mapped automatically at run time. Furthermore, the initial design time and effort may be greatly reduced when dealing with huge amounts of data in a large number of files and/or databases.
SessionID: 90286
Event Date: 27-04-2019 - Session time: 12:15:00 - Track: Vendor Session
Speaker: David Klee
Abstract:
This session is packed with many live examples of tips and tricks gained from years of experience for getting the most performance from your virtual SQL Servers. Tools, techniques, and processes will be demonstrated to help you measure and validate the system performance of the key components underneath your data.
SessionID: 91505
Event Date: 27-04-2019 - Session time: 12:15:00 - Track: Vendor Session
Speaker: Alec Pickup
Abstract:
Hopefully you are monitoring your SQL Servers using DIY scripts or a 3rd party tool. Active monitoring is invaluable to enable the operational DBA to identify and address issues quickly. But did you know that SentryOne offers capabilities beyond just monitoring? Join me and I'll show you how SentryOne provides environment wide visibility and aids in managing cross-server activities. I'll showcase how alerting can be tuned to be effective and not noise. And you'll discover how SentryOne can give you both operational and business value to address your application challenges.
SessionID: 91506
Running SQL Server on AWS
Event Date: 27-04-2019 - Session time: 12:15:00 - Track: Vendor Session
Speaker: Richard Waymire
Title: Running SQL Server on AWS
Abstract:
Learn how Amazon Relational Database Services (RDS) helps SQL Server DBAs focus their time where it matters most. We will cover Amazon RDS for SQL Server features and the key differences between running deployments on Amazon Elastic Cloud Compute (EC2) and Amazon RDS.
SessionID: 93083
SQL Access to SaaS/Cloud Data through PolyBase and Linked Servers
Event Date: 27-04-2019 - Session time: 12:15:00 - Track: Vendor Session
Speaker: Jerod Johnson
Title: SQL Access to SaaS/Cloud Data through PolyBase and Linked Servers
Abstract:
Data virtualization technologies enable modern organizations to operate across disparate data sets without the need to consolidate data storage through warehousing and ETL processes. Technologies like PolyBase and SQL Server Linked Servers bring this capability to SQL Server, enabling users to query external data like any other table in SQL Server.
In this talk, we will discuss the virtualization features of SQL Server 2019 PolyBase and the distributed querying enabled by SQL Linked Servers and examine how we can issue Transact-SQL queries that connect data across SQL Server and SaaS/Cloud or NoSQL data sources.
SessionID: 89626
Interviewing-Be So Good They Can’t Ignore You
Event Date: 27-04-2019 - Session time: 13:15:00 - Track: Support
Speaker: Leveat Ruse
Title: Interviewing-Be So Good They Can’t Ignore You
Abstract:
From the basics to the advanced, we will explore techniques that will ensure you leave a positive impression and help you land that role!
SessionID: 89657
Your AG might not be as “Available” as you think
Event Date: 27-04-2019 - Session time: 13:15:00 - Track: Design
Speaker: Joseph Fleming
Title: Your AG might not be as “Available” as you think
Abstract:
An availability group will keep all of your database objects synchronized, but server-level objects are often overlooked. In this session I’ll show you how to set up a reporting tool that will show you which objects are out of sync so that you can correct them, including a query that will let you compare both objects so you can see what’s different.
SessionID: 89973
Event Date: 27-04-2019 - Session time: 13:15:00 - Track: Support
Speaker: Tracy Boggiano
Abstract:
In this session, we will discuss sp_whoisactive and Query Store. Then we will be combining three open source tools – Telegraf, InfluxDB, and Grafana – into an inexpensive system that collects performance metrics you can use to troubleshoot issues and answer important questions about your SQL Server instances, including your Linux SQL Server instances. We will learn what metrics to collect, how to use the tools to collect performance metrics and then we’ll put it all together in an interactive dashboard for easy visualization.
Attendees will see how easy it is to get good performance data and visualize in an interactive way and combine with other tools to troubleshoot issue
SessionID: 90721
Extended Events - Better than Profiler
Event Date: 27-04-2019 - Session time: 13:15:00 - Track: Innovate
Speaker: Jason Brimhall
Title: Extended Events - Better than Profiler
Abstract:
SessionID: 90729
Intelligent Query Processing
Event Date: 27-04-2019 - Session time: 13:15:00 - Track: Support
Speaker: Wayne Sheffield
Title: Intelligent Query Processing
Abstract:
In this session we will explore all the Intelligent Query Processing features in SQL Server 2019. Time permitting, we will go into other changes in recent versions of SQL Server (such as execution plan changes and new Dynamic Management Objects) that will help improve your efficiency at troubleshooting and tuning SQL Server.
Coming away from this session, you will have plenty of reasons to want to upgrade to SQL Server 2019 immediately!
SessionID: 89397
Module Signing: Certificates Replace TRUSTWORTHY / EXECUTE AS / DB Chaining
Event Date: 27-04-2019 - Session time: 14:30:00 - Track: Build
Speaker: Solomon Rutzky
Title: Module Signing: Certificates Replace TRUSTWORTHY / EXECUTE AS / DB Chaining
Abstract:
We often need to grant a higher-than-ideal level of permissions to a Login / Role so that someone can: start a SQL Agent job (but you don't want them to edit anything), get data from a DMV that requires "VIEW SERVER STATE" permission (but that permission gives access to too much data), query across databases, etc. These problems are usually solved with a combination of: EXECUTE AS (i.e. Impersonation), cross-DB ownership chaining, or TRUSTWORTHY ON. Unfortunately, those options are all security risks.
Module Signing — https://ModuleSigning.info/ — is more flexible and secure, but requires a Certificate or Asymmetric Key. Those can be confusing to work with, and the security mechanism isn't intuitive. However, that confusion ends here.
Come learn how to have more secure, granular permissions that handle cross-DB tasks, Dynamic SQL, and SQLCLR. See what signing can do, how certificates and asymmetric keys work, and realize it's not as hard as you thought.
SessionID: 90187
From R to Machine Learning Services and Beyond
Event Date: 27-04-2019 - Session time: 14:30:00 - Track: Evolve
Speaker: Mark Hudson
Title: From R to Machine Learning Services and Beyond
Abstract:
R, R Studio, Microsoft R Services, Microsoft R Server, Machine Learning Server. What is R and how do you make sense of all these R options?
Join me for a quick R timeline and description followed by some use cases and live demonstrations.
Take with you a better understanding of why analysts, developers, DBAs, PMs, CIOs, and others want to know what is up with R.
SessionID: 90571
DBA's and DevOps: The Fundamentals
Event Date: 27-04-2019 - Session time: 14:30:00 - Track: Innovate
Speaker: Stuart Ainsworth
Title: DBA's and DevOps: The Fundamentals
Abstract:
DevOps is a software delivery philosophy that focuses on delivering quality, faster. The purpose of this session is to cover some of the basic principles of DevOps and how they apply to database development and administration. Everyone's environment is different, but the strategies discussed within can be translated into basic tactical gains that are easy to implement.
Topics covered include:
- a (VERY) brief history of DevOps
- why DBA's make the best DevOps engineers
- what admins can teach developers, and what developers can teach admins
SessionID: 90644
Building machine learning models in Azure ML Studio
Event Date: 27-04-2019 - Session time: 14:30:00 - Track: Innovate
Speaker: Zeydy Ortiz
Title: Building machine learning models in Azure ML Studio
Abstract:
We will also share practical tips including pitfalls to avoid when using customer data, how deployment affects the machine learning model, and how to build trust in the predictions.
SessionID: 91219
Stored Procs Rule, Ad Hoc Drools
Event Date: 27-04-2019 - Session time: 14:30:00 - Track: Design
Speaker: Dale Burnett
Title: Stored Procs Rule, Ad Hoc Drools
Abstract:
There are many ways to access and modify your SQL Server data including ADO, ORMs and ad hoc SQL embedded in application code. Successful, robust applications have been created using all of these methods. In this session I lay out 10 points for the argument that the overall best way to access your data is with the stored procedure.
SessionID: 93457
What is Power BI?
Event Date: 27-04-2019 - Session time: 14:30:00 - Track: Analyze
Speaker: Angela Henry
Title: What is Power BI?
Abstract:
SessionID: 89404
Getting started with Azure Machine Learning Services
Event Date: 27-04-2019 - Session time: 15:45:00 - Track: Innovate
Speaker: Bhakthi Liyanage
Title: Getting started with Azure Machine Learning Services
Abstract:
SessionID: 89438
Taming of the Shrew - Optimizing Power BI
Event Date: 27-04-2019 - Session time: 15:45:00 - Track: Design
Speaker: Kellyn Pot'Vin-Gorman
Title: Taming of the Shrew - Optimizing Power BI
Abstract:
This session will take the Power User through how to identify time consuming processing in Power BI and how to address it through step-by-step and proven methods used to optimize in multi-tier environments. This method removes frustration and finger-pointing and gets to the heart of the problem with a goal of correcting performance challenges. The results are satisfied end users who get the information they need, when they need it.
Take Aways:
Learn how to identify what is taking the time in a Power BI data set and report.
Understand the logging and tracing mechanisms and capabilities in the environment.
Understand when a problem is really a problem.
SessionID: 90194
SQL Graph
Event Date: 27-04-2019 - Session time: 15:45:00 - Track: Design
Speaker: Azhagappan Arunachalam
Title: SQL Graph
Abstract:
Graphs are everywhere! Microsoft has added a lot of new features to SQL Server 2017 (and 2019), and among them SQL Graph has been one - one that holds a lot of promises, though it's still in its early stages (at least with Microsoft's offering). Microsoft added SQL Graph features to their 2017 release, and has added support for MERGE (UPSERT) scenarios to ease insertions into edge tables, and Edge Constraints to apply restrictions on relationships between nodes in 2019 release (in CTP 2.0). In this session, we'll take a look at what this feature brings to the table, and how it compares with other Graph databases.
SessionID: 90612
I/O Tuning 101
Event Date: 27-04-2019 - Session time: 15:45:00 - Track: Scale
Speaker: Anders Pedersen
Title: I/O Tuning 101
Abstract:
One of the most common problems I have encountered as a DBA is the complaints from users that a particular query is slow, just one query. Many times one that used to run in acceptable time, but that over time has slowed down substantially.
In this session we will show some of the causes for this, with emphasis on I/O requests being too high for the query.
First part of the session will explain how to find these numbers, looking at a few queries with some major performance issues, including explaining why they have a problem.
The second part will focus on how to reduce I/O requests.
All of this can be done without the need for any 3rd party tools, all within the scope of what is available to SQL Server, all versions, all editions, including Azure.
SessionID: 90788
Welcome to DAX
Event Date: 27-04-2019 - Session time: 15:45:00 - Track: Build
Speaker: Brett Tomson
Title: Welcome to DAX
Abstract:
"Welcome to DAX" is an introductory session inviting you to the world of Data Analysis eXPressions (DAX), Microsoft's new native formula and query language found in Power BI, SQL Server Analysis Services (SSAS) Tabular and the Power Pivot plug-in for Excel. DAX, initially released in 2009 with the first version of Power Pivot, opened a brave, new world for analysts and developers to quickly build formulas and calculations to analyze and mold data of all shapes and sizes. DAX is designed to be simple and easy to learn while exposing the power and flexibility of Tabular modeling. But be warned, looks can be deceiving. In this session, we'll navigate the basics of DAX to understand best practices around syntax and usage. Along the way we will discover the intricacies of how row, query and filter context affect output in a DAX expression and how we can manipulate that context to get the results we need.
SessionID: 91090
From the grave: Horror stories from Microsoft SQL Support archive
Event Date: 27-04-2019 - Session time: 15:45:00 - Track: Support
Speaker: Chris Skorlinski
Title: From the grave: Horror stories from Microsoft SQL Support archive
Abstract:
Chris Skorlinski, Microsoft SQL Escalation Support Engine will share a collection of short stories, how it happened, the consequences, and most important, how to avoid your own horror story. Come along and share your own SQL Server tales of the deep and macabre.