Check out our first wave of general session content and get a taste of the high caliber learning available at PASS Summit 2017. Keep checking back for many more in-depth data-focused sessions to come.
Continuous Delivery allows developers and DBAs alike to streamline and automate the process of software delivery. SQL Server Data Tools (SSDT) allows the integration of Application Life Cycle Management practices to managing database deployments.
Using SSDT we can achieve automation from code push to delivery of consistent published artifacts (or packages) when integrated with Continuous Delivery.
The DEMO will showcase these processes and will include creating a consistent database model using SSDT, feature branching in source control and include running automated Continuous Integration build tests using SQL Server on Linux. Using Visual Studio Team Services for Continuous Delivery will achieve automated reliable deploys.
This session will also show how to utilize Infrastructure as Code using PowerShell, combined with Azure Resource Manager, to automate the deployment of a scalable SQL Server solution that is a hybrid database solution (on-premises and Azure SQL database).
General Session (75 minutes)
Add to Favorite
In this class, we will walk through 5 not so obvious ways to bypass SQL Server security and how to close these loopholes. You will be surprised with how familiar some of these scenarios sound, and inevitably, some of you will need to make a quick phone call after this session to close some of these loopholes now. This session will frame scenarios from the mindset of a business user interacting with a DBA and show you how a casual conversation about getting permissions to one item can lead to a world of hurt and security issues.
The Azure Data Lake is one of the newest additions to the Microsoft Azure Cloud Platform, bringing together cheap storage and massive parallel processing in two quick-to-setup and, relatively, easy-to-use technologies. In the session we will dive into the Azure Data lake.
First of all exploring:
Azure Data Lake Store: How is data stored? What are the best practices?;
Azure Data Lake Analytics: How does the query optimiser work? What are the best practices?
Second, a practical demonstration of: Tuning U-SQL: Partitioning and distribution: Job Execution in Visual Studio
One of the new features announced for SQL Server 2017 is Adaptive Query Plans, query plans that can change after the query starts executing.
This session will show why this is a radical departure from the way that things have worked until now and how it can improve the performance of some query forms. We’ll look at the places where adaptive query plans work and compare the performance of queries using adaptive query plans to see just what kind of improvement it can make.
The options for achieving and increasing the reliability of SQL Server databases and instances as well as storing the associated data have changed in the past few years. Not only are the features and combinations for deploying SQL Server instances and databases greater than in the past, but as we move to a software defined world, achieving availability and performance is both easier and more difficult at the same time.
This full day pre-conference session will cover the latest SQL Server availability and storage solutions including how to plan, deploy, and administer them. Topics covered will include:
Pre-Conference Session (Full Day)
Understanding how to reduce the attack surface area of applications and SQL Server environments is imperative in today's world of constant system attacks from inside and outside threats. Learn about the methodology to increase security related development practices, backed by real world examples. Including securely accessing a database, properly encrypting data, using SSL/TLS and certificates throughout the system, guarding against common front-end attacks like SQL Injection (SQLi) and Cross Site Scripting (XSS), etc. This session will include both T-SQL and .Net code to give you an overview of how everything works together.
This full day session is designed to be a practical revelation of Data Science concepts tailored for data professionals. Attendees will learn the processes data scientist employ in problem solving to understand how they can be implemented in a data environment.
The methods used to create a data science solution are broken down into understandable sections, to make the concepts readily grasped and provide a structure to implement them in real-world environments. By first understanding the process, then building on that by learning methods for algorithm selection, solutions can be created and applied using Python, Azure ML, R or Power BI. As these tools and languages are not well known in the data community, each will be introduced to provide a basic understanding of each, as no prior data science knowledge is expected by session attendees.
Deploying data science solutions is a topic nearly as important as creating the solution itself as improperly deployed the solution cannot be successful. This course will also cover how to optimally configure a SQL Server 2016 or 2017 environment to run and monitor data science solutions in R and Python on SQL Server without adversely impacting other SQL Server operations. Learn how R and Python solutions can be deployed and what can be done to improve the solution performance. Visualization techniques in the solution will be explored so that attendees will know their options and the best tool for the job, which may be Power BI or SQL Server itself.
With the introduction of SQL Server 2017, there are an increasing number of different tools, including, R, Python, Azure ML, and Power BI, which can be used to implement data science solutions. This session explores how to determine the best tool for a given situation by revealing the strengths and weakness of each technology to provide attendees with the knowledge they will need for that evaluation in their unique environment.
In this daylong session, we’ll review all the various infrastructure components that make up the Microsoft Azure platform. When it comes to moving SQL Server systems into the Azure platform having a solid understanding of the Azure infrastructure will make migrations successful and making support solutions much easier.
Designing your Azure infrastructure properly from the beginning is extremely important. An improperly designed and configured infrastructure will provide performance problems, manageability problems, and can be difficult to resolve without downtime.
As Azure scales around the world many more companies, no matter where they are located, will be begin moving services from on-premises data centers into the Azure Cloud, and a solid foundation is key to successful migrations.
There's a ton of buzz around Data Science and Machine Learning. What is it? What can we do with it? Do I have to learn a new language or tool? In this presentation, we'll walk through an example of how to use Azure Machine Learning to predict income based on demographic data. During this presentation, we'll see how easy it is to (1) Set up an Azure Machine Learning workspace, (2) Clean up our data for modeling, (3) Train and Evaluate a predictive model and (4) Publish this model as an easily consumable web service. This will be a highly interactive demonstration, so bring your data hats and get ready to fall in love with the best data science tool on the market today!
You know about the cloud but you’re not there yet. Is it hard? Is it easy? How do you get started? Come to this session and see for yourself. We’ll start with nothing and end up with a deployed Azure SQL Database. We’ll even run a quick PowerBI report and enable Geo-Redundant Disaster Recovery with a few clicks.
The goal is to take the mystery out, to show the capabilities and get you thinking about what going to the cloud could look like and what it can do for you and your company. I believe the future belongs to those who have this knowledge and know where to apply it.
This will be nearly PowerPoint free and we’ll log into my Azure Portal and build out an environment from scratch and learn as we go. We’ll migrate data from an “on-premises” database into our SQL DB and we’ll query it. You’ll leave with an understanding of the capabilities, some resource links outlining what we did and hopefully some curiosity to see what else is up there in the cloud as you start exploring.
Cloud services are now changing the dynamics of organizations and one of the new services that is being widely recognized nowadays is Database as a Service (DBaaS). The focus of this session is to understanding how Microsoft DBaaS provides a flexible, scalable and on-demand platform that also delivers self-service and easy management; so you can decide whether it is suitable for your organization. I will demonstrate how to tune database performance and how it works with the Stretch Database feature from SQL Server 2016 . This session is for Developers, Database Administrators and Decision Makers.
ETL best practices for Azure SQL DW are different from classic Data Warehouse processes. In this session I will explain what the important differences are and why. Many common ETL patterns work poorly Azure DW. But if you use patterns suitable for MPP / Column Store based environments, like Azure DW, it can be easy to outperform your old SQL Server based Data Warehouse. In this session we will look at the most common Data Warehouse loading patterns and explain why they will or won't be suitable for Azure DW. Alternative patterns will be introduced for all examples.
For a long time database professionals have been asking Microsoft to make Always on Availability Groups (AG) available in Standard Edition. With SQL Server 2016 SP1, Microsoft responded with Basic Availability Groups, a limited – but very functional – version of Always on Availability Groups.
Basic Availability Groups enable Data Professionals to provide enterprise class Hight Availability solutions using SQL Server Standard Edition, which allows organizations to save money by providing High Availability (HA) for applications that require HA without paying for SQL Server Enterprise Edition features they do not need.
In this session, we will be covering:
Your database is running fine month after month with no problems. Suddenly someone reports that their query won’t run. They get an error stating “SQL Server detected a logical consistency-based I/O error“, or something even scarier. Do you know what to do now? We will walk through 3 corrupt databases exploring ways to go about finding and fixing the corruption. More importantly we will explore how to prevent further data loss at the time corruption occurs. Learn what things you can do to protect yourself when corruption strikes. Learn how to avoid making things worse, and how to protect your data if things do get worse. You will leave with a checklist of steps to use when you encounter corruption. By the end of this session you will be ready to take on corruption, one database at a time.
Regardless of whether your data warehousing (DW)/business intelligence (BI) team is dedicated to Scrum, is a fan of Kanban, or is focused on XP, “Agile” in any form calls for small increments of potentially deployable results – which means that clear requirements and QA are essential. As we develop these small increments, we need to not only test the new development, but also regression test what we’ve already built. Our test suite grows larger with each iteration, and reactive, manual testing quickly becomes infeasible. Agile requires that we specify small, testable requirements and automate our tests so that regression testing doesn’t become a development bottleneck.
Behavior-Driven Design (BDD) and test automation are key practices that allow data warehouse and business intelligence teams to be more successful in their agile journeys. While there are lots of requirements and test practices, and automation tools, out there for software development teams to leverage, very few are targeted to data-related development and testing. Learn about essential agile test foundations, BDD, and data-focused test automation technologies to help data warehousing and business intelligence teams get a leg up on these important agile practices
This session will cover:
Half-Day Session (2.5 hours)
Not every workload can benefit from In-Memory tables. Memory Optimized Tables are not a magic bullet that will improve performance for all kinds of transactional workloads. Therefore, it is very critical that you benchmark & test in-memory performance for your SQL deployments before you decide to migrate disk-based tables to memory-optimized tables. In this session, you will learn:
a. Baselining current performance
b. How to identify the right candidates for In-Memory
c. Generate sample production data
d. Create simulated production workload
e. Test & benchmark In-Memory performance with simulations
f. Compare In-Memory performance with the baseline
You will also learn about a verity of tools and techniques that can be used in your proof-of-concept.
Once you have successfully configured Availability Groups, what comes next? In this session, we will go beyond setup and look at how to monitor your Availability Groups. We will define and cover important metrics and alerts you need to manage a database in an Availability Group.
You will walk away from this session with tools you need to monitor your environment and know how to respond to alerts.
This session is tightly coupled to Visual Studio Online (VSO) as the central component to orchestrate the path to an automated deployment process. The tool itself is so much more than just a code repository, and once we have unlocked the power of VSO, you will quickly realize the sky is the limit.
Focus in this session will mainly be delivered on processes around SSAS, SSIS and SSRS and we will also briefly touch on Continuous Delivery for the underlying Data Warehouse.
Come join this session, if you are ready to take the first few steps towards automation of your deployment processes and want to know how Microsoft tools can help you.
By now, many of you have used Biml to generate staging solutions. Some of you have built metadata stores to automate dimensional or data vault warehouses. Now you’re ready to take your Biml solution into production.
Before you release it into the wild, you will need to extend the default auditing and logging behavior of SSIS to capture meaningful audit and debug information. Of course, you can invest tremendous time and effort to build your own, but why not just reuse our free open source framework with provided BimlCatalog database, custom components, documentation, and extensive Biml samples?
This framework is robust and able to restart from the last failure skipping already loaded packages. As an example, if one of your 50 parallelized table load packages fails, the following execution will skip the packages that succeeded and resume at the next package.
In this session, you will learn how to integrate the framework into your solution and learn how to include:
Microsoft’s Cognitive Services are basically the best thing since sliced bread, especially for anybody working with data. Artificial intelligence just got packaged and made available for the masses to download. In this short talk, I’ll take you on a whirl wind tour of how to use these massively powerful libraries directly in Azure Data Lake with that offspring of T-SQL and C# ... U-SQL. How do you get hold of the DLL’s and how can you wire them up for yourself?... Everything will be revealed as well as the chance to see what the machines make of the audience!
Lightning Talk (10 minutes)
Every application needs to access data in some manner. In .NET applications, Entity Framework is the intersection between database and developer tools. Today Entity Framework is used in tens of millions of .NET applications and is only growing in usage with Entity Framework Core. While Entity Framework provides great benefits out-of-the-box, mastering the framework can be critical to developing advanced enterprise applications that take advantage of many of the more powerful features available in your relational database. In this pre-conference workshop, we will spend an entire day immersed in both Entity Framework and Entity Framework Core. We will look at best practices, tips, tricks and examples that will help you implement Entity Framework in your organization in the most useful, performant and empowering manner possible.
We will introduce Entity Framework and how it is used today in .NET applications.
Scenarios covered include:
Have you ever wanted to monitor the performance of a SQL Server Analysis Services (SSAS) server but did not want to create a tool from scratch and your company did not allow you to purchase one of the few commercial tools available?
Join me in this session as I show you how to use Windows Perfmon, SQL Server Extended Events, SSAS Tabular and Power BI Desktop to build a performance monitoring tool for a SSAS server.
We will start by reviewing some of the SSAS performance metrics you can collect. Then we will learn how to use Windows Perfmon and SQL Server Extended Events to collect performance counters and query execution information from a server and store it in a SQL Server database. Next we will use SSAS to build a Tabular model from the information collected. Finally, we will use Power BI Desktop to present this information.
Using the power of OLTP and data transformation in SQL 2016 and advanced analytics in Microsoft R Server, various industries that really push the boundary of processing higher number of transaction per second (tps) for different use cases. In this talk, we will walk through the use case of predicting loan charge off (loan default) rate, architecture configuration that enable this use case, and rich visual dashboard that allow customer to do what-if analysis. Attend this session to find out how SQL + R allows you to build an "intelligent data warehouse".
MS Tiger Session (75 minutes)
Microsoft has finally released SQL Server on Linux. Most thought it would never happen, and most don't understand the use case for deploying SQL Server on Linux. Already there are a number of misconceptions in the industry. This session will examine how SQL Server works on Linux, its feature set and discuss how it works differently. It will discuss any restrictions and limitations that exist, so that you can make a more informed choice as to whether SQL Server on Linux is right for you. This session will help you and your management decide whether SQL Server on Linux is right for you tactically and strategically.
C++ was widely denigrated as a "hopelessly complex" language with "way too many moving parts", and in truth, it was a language made up of three dominant paradigms: procedural, object-oriented, and meta-programmatic. C#, by contrast, has five dominant paradigms: procedural, object-oriented, meta-programmatic, functional and now dynamic. (Visual Basic doesn't fare much better on this score.) If we're to use these new multi-paradigm languages successfully, we'd better have a good idea of what the paradigms are, what a language paradigm is, and what the different paradigms are in the languages we know and love.
Python is the one of the most popular programming languages used today and one of the most useful tools in the data scientist's tool belt especially for machine learning. Python is integrated into the Microsoft stack in tools like Azure ML and now SQL Server 2017.
The session will be an introduction to the Python language including:
1.) Installing and Configuring Python
2.) Access and Manipulate Data
3.) Install and Manage Packages
4.) Create and Use Objects/Variables
5.) Control Flows and Functions
6.) Managing the Python installation in MSSQL
Attend this session to learn how to use Python to take your data analytics to the next level. We will use Python, SQL Server and the Anaconda distribution of Python to learn the basics of Python and how it is integrated in the Microsoft stack! We will walk through a simple deployment of a machine learning model to see how it all works together and learns some basic data science fundamentals.
No prior statistics knowledge needed.
Learn practical Columnstore Indexes tips and tricks such as which type to use, how and when to use Disk-Based vs. In-Memory or Clustered vs. Nonclustered, how to load data into Columnstore in the most efficient way, and how to get the best performance possible out of the Batch Execution Mode.
With a nod to each of the available SQL Server versions (2012, 2014, 2016, 2017 and the Azure SQLDB), this full day session focuses on practical solutions and applications of Columnstore Indexes and the Batch Execution Mode. We also review the limitations of both and learn how to solve some of those limitations.
Covering all available relational engines supporting Columnstore Indexes (including parts of the SQL DataWarehouse), this pre-conference will give you insight on why and when to use Columnstore Indexes, and when to take a step back and use a different type of technology.
Since November 2016 (and more specifically since Service Pack 1 for SQL Server 2016), Columnstore Indexes have been available for every Edition of SQL Server (including Express and Local editions). Join this workshop and become a part of the columnar revolution that is positively affecting database platforms around the world.
MS SQL Server is one of the most popular workloads in the world and is so much more than just the on-premises database engine for Windows datacenter. We now also have Azure SQL DB a robust and scalable cloud-hosted database, and SQL Server on Linux which eliminates boundaries between Windows and Linux datacenters. We want to show you the most exciting and compelling ways to get all these scenarios monitored using SCOM, OMS, SQL Best Practice Analyzer, and other tools.
You will learn how to:
Cosmos is Azure's NoSQL Database as a Service, born in the cloud and designed take advantage of the flexibility, elasticity and global reach of cloud computing.
Cosmos offers different access APIs, consistency models and performance management models that are different from a classic relational database like SQL Server. However, the same principles that you already apply as a SQL Dba are as important as ever to build a Cosmos application that performs well, is cost efficient and resilient to failures.
In this session we will look at how Cosmos is different from a relational database, it's major features as a NoSQL cloud product and how you can apply your SQL Server experience to perform successful Cosmos deployments.
R is the first choice for data scientists for a good reason: besides accessing and transforming data and applying statistical methods and models to it, it has a wide variety of possibilities to visualize data. As visual perception of data is the key to understanding data, this capability is crucial. This session will give you a broad overview over available packages and diagram types you can build with them on the one hand, and a deep dive into common visualizations and their possibilities on the other hand. Impress yourself and your peers with stunning visualizations which will give you insights into data you could not achieve with other tools of Microsoft’s BI stack.