Training Class Recordings (Pre-Cons & Post-Cons)

Pre-Con Title

An Enterprise-Friendly Framework For Implementing Power BI Projects (What Comes After The 5 Minutes To Wow For An Enterprise)

Track(s)

Business Intelligence & Advanced Analytics

Tags

#powerbi #analysisservices #enterprise #datamodeling

Level

Intermediate

Trainer/Speaker

Dr. Greg Low

Abstract

Power BI was introduced with a motto of ""5 minutes to Wow"", and that has been very successful. Users get started with the tools quickly and are ususally very impressed.

Once they get past the initial Wow though, enterprises are often left wondering how they can implement a Microsoft BI stack using Power BI, at least in a way that is enterprise-friendly.

In this min-course, Greg will take you through the techniques that he and his company have been using very successfully to implement these projects.

Modules/Topics

First Half Day

1. The End Game – What are we Trying to Achieve?
2. Implementing the DataModel Schema
3. Implementing the Analytics Schema
4. Using DevOps for Project Management and Deployment

Second Half Day

1. Staging, Loading, Transforming, and Processing Data
2. Implementing ELT and Processing
3. Implementing the Tabular Data Model
4. Using Advanced Tabular Model Techniques

Attendee Pre-requisite (if any)

Basic understanding of databases, data modeling, Power BI

Additional Comments from Instructor/Speaker

No special instructions/comments from the Speaker

Pre-Con Title

Build Your Own End To End SQL Server Deployment Pipeline (For Under $1 A Day)

Track(s)

Data Administration

Tags

#DevOps #Deployment

Level

Intermediate

Trainer/Speaker

Alex Yates

Abstract

Automated deployments enable teams to deliver value faster, more reliably and for less money. If your database deployments are still slow, manual and error prone, and your business wants to remain competitive, building an automated database deployment pipeline should be a high priority.

However, this can be difficult because it requires a working knowledge of various technologies, as well as an understanding about how to glue them all together. For example, folks need to understand database technologies, scripting languages, source control tools and CI/CD architectures, as well as all the necessary networking. To create a truly successful deployment pipeline it often requires collaboration between multiple specialists with a generalist to guide them.

This session is both for the aspiring generalists who wants to understand the big picture and for the specialist who wants to learn to better collaborate with their colleagues. We will focus on the high-level outcomes, without going too deep on any specific technology. Therefore, this session should be accessible to anyone, regardless of speciality or experience.

Attendees will create their own end to end deployment pipelines for SQL Server using GitHub, Octopus Deploy and AWS. While attendees tool choices and hosting providers might vary, this session will focus on universal concepts and should be valuable to folks even if they use different tools, such as Jenkins, Azure DevOps or Azure. Folks will be using their own software and licences with 100% free / trial software/services and will be able to host their infrastucture using their own AWS account, on free tier eligible VMs. The cost of running the infrastructure will be no more than a few cents per hour.

By the end of this class, all attendees will understand how to create an end to end database deployment pipeline, and they will take away their own hosted pipeline which they'll be able to spin up and tear down whenever they like.

Modules/Topics

Day 1:

• Cloning a Git repo in GitHub
• Introducing Octopus Deploy, and configuring the hand-off from GitHub Actions

Day 2:

• Exploring database source code and understanding how it gets deployed
• Configuring Octopus Projects to deploy database updates
• Further extensions (various options, depending on time available.)

Attendee Pre-requisite (if any)

(Required)

Create a free account in advance for the following services:

• AWS
• GitHub
• Octopus Deploy (Cloud edition)

(Optional)

• If you would like to run the code locally (optional) install the following on your dev machine:
• SQL Server Developer Edition (Either a full instance or using Docker is fine)
• (If using SSDT) Visual Studio (Community edition is fine)
• (If using Redgate) Redgate SQL Change Automation PowerShell cmdlets
• PowerShell
• Your prefered IDE for working with SQL Server (SSMS, Azure Data Studio etc)
• Git

Additional Comments from Instructor/Speaker

No special instructions/comments from the Speaker

Pre-Con Title

SQL Server: Advanced Training for Azure VM Deployments

Track(s)

Data Administration

Tags

#SQLServer #SQL2019 #Azure #IaaS #Migration #BestPractices #Sizing #Performance #Optimization #Licensing #HighAvailability #AlwaysOn #Clustering #Windows #Linux #Virtualization #AzureVM #SQLVM

Level

Intermediate

Trainer/Speaker

David Pless , Pam Lahoud , Amit Khandelwal , Tejas Shah, Aditya Badramraju

Abstract

SQL Server: Advanced Skilling for Azure VM Deployments

Many customers are looking to migrate their SQL Server instances and SQL based applications to the cloud to take advantage of scalability, flexibility, advanced high availability and disaster recovery options, optimized licensing scenarios, access to Azure monitoring & Azure advisory technologies, and other marketplace tools & capabilities in the Azure fabric.

The decision has been made to bring your solution to the cloud, but then the challenge many customers experience is knowing how to properly size their environments, understanding the best strategy for availability, and how to start their migration journey.

In this workshop we will address all of these key challenges and the best practices on migrating your SQL Server workloads to Azure virtual machines.

We will cover migration strategies starting with assessing the state of your current environment. We will then target our evaluation results to a destination platform in Azure. We will learn about proper virtual machine sizing and the VM classes, proper tuning for Windows and Linux platforms, and how to take best advantage of licensing practices in Azure.

We will then focus on high availability and disaster recovery options, best practices, monitoring and management. We will address modern HADR approaches for SQL Server including Always On Availability Groups, Failover Cluster Instances (FCI), Log Shipping, and supported replication models. We will also cover virtual machine optimization best practices and deployment considerations for hybrid configurations.

This workshop will be an engaging, real-world examination of modernizing your most critical SQL based platforms.

We look forward to seeing you there!

SQL Server 2019: Advanced Skilling for Azure IaaS Deployments

Module Overview:

Module 1 – SQL Server 2019 Overview

• Topic 1: Features and Capabilities
• Topic 2: Opportunities in Azure

Module 2 – SQL Server Migration strategies for IaaS

• Topic 1: Assessing the source environment
• Topic 2: Migrating data and SQL objects to the cloud

Module 3: SQL Server best practices for common workloads on IaaS (90 min.)

• Topic 1: Sizing and VM types
• Topic 2: Performance Guidelines
• Topic 3: Resource Provider Benefits
• Topic 4: Demystifying licensing in SQL Server 2019

Module 4: Modern HADR Approaches for SQL in IaaS (90 min.)

• Topic 1: Always On Availability Groups
• Topic 2: Fail over Cluster Instances (FCI) in Azure
• Topic 3: Log Shipping
• Topic 4: Supported Replication Models
• Topic 5: HADR hybrid configurations
• Topic 6: Azure HADR options

Module 5: SQL Server on IaaS – (Monitoring and Management)

• Topic 1: Managing SQL Server on IaaS both for Windows and Linux
• Topic 2: Comparing on-prem virtualization to Azure

Module 6: IaaS/PaaS hybrid deployments for scalability

• Topic 1: Application scenarios
• Topic 2: Business Intelligence scenarios

Attendee Pre-requisite (if any)

Pre-requisite: There are no pre-requisites though familiarity with SQL Server 2016+ and virtualization is helpful.

Additional Comments from Instructor/Speaker

No special instructions/comments from the Speaker

Pre-Con Title

The Azure SQL Workshop

Track(s)

Data Administration

Tags

#AzureSQL #Azure #SQLServer #MeatAndPotatoes #Security #Performance #Availability

Level

Intermediate

Trainer/Speaker

Bob-Ward,Anna-hoffman

Abstract

In this one day, hands-on, scenario-driven workshop, members of the SQL engineering team will help you translate your existing SQL Server expertise to Azure SQL including Azure SQL Database and Azure SQL managed instance. At the end of the day, you should have a foundational knowledge of what to use when, as well as how to configure, monitor, and troubleshoot the “meat and potatoes” of SQL Server in Azure: security, performance, and availability. You will learn about not only the “what”, but also the “how” and the “why”. You’ll also walk away with resources and tools so you can go as deep as your scenario requires.

After the workshop, you should be able to:

• Determine which Azure SQL deployment option (virtual machines, single databases, serverless databases, elastic pools of databases, hyperscale databases, and managed instances) and service tier should be used for scenarios that meet the needs of your application.
• Deploy, configure, monitor, and troubleshoot security, performance, and availability scenarios in Azure SQL using both familiar and new tools and techniques.
• Harness the “power of the cloud” including automation and intelligent capabilities of Azure SQL.
• Translate your existing SQL Server expertise to Azure SQL expertise.

Modules/Topics

• Introduction to Azure SQL
• Deploy and Configure
• Security
• Performance
• Availability
• Putting it all together

Attendee Pre-requisite (if any)

The Instructor/Trainer has not specified any pre-requisites.

Additional Comments from Instructor/Speaker

No special instructions/comments from the Speaker

Pre-Con Title

Self-Service AI with Power BI Desktop

Track(s)

Business Intelligence & Advanced Analytics

Tags

#PowerBI #AI #ML #PowerQA #DAX #R #Python #PowerQuery #CognitiveServices #AzureML

Level

Advanced

Trainer/Speaker

Markus Ehrenmueller-Jensen

Abstract

Power BI Desktop is Microsoft’s free tool for self-service BI. It’s updated every single month with exciting new features and Gartner declared it since 2019 to THE leading BI Tool (which led Tableau and Qlik behind). In this full day workshop, we take a look on Power BI Desktop’s possibilities concerning Artificial Intelligence. The functionalities are reaching from simple context menus, to e. g. get an explanation for a peak in a value over time, to ingesting a self-deployed Azure Machine Learning web service.

The agenda contains all the listed features, which we will discuss and try out on concrete examples:

• Q & A
• Quick Insights
• What-if Parameter
• Analytic Line
• Data Profiling
• Usage Monitoring and Auditing
• R & Python Integration
• Smart Custom Visuals
• Key Influencer Visual
• Column by Example
• Decision Tree
• Cognitive Services
• Azure Machine Learning

The exercises contain both, no-code solutions and complex scripts in DAX, R and M. Knowledge in those languages are helpful but not necessary.

Don’t forget to bring you Laptop!

Modules/Topics

1. Q & A
2. Quick Insights
3. What-if Parameter
4. Analytic Line
5. Data Profiling
6. Usage Monitoring and Auditing
7. R & Python Integration
8. Smart Custom Visuals
9. Key Influencer Visual
10. Column by Example
11. Decision Tree
12. Cognitive Services
13. Azure Machine Learning

Attendee Pre-requisite (if any)

basic knowledge in Power BI (creating a visual, formatting a visual, loading data in Power Query)

basic knowledge in DAX, M, R & Python is advantageous

Free user for Power BI Service (no pro-account necessary)

• Azure Subscription to create Cognitive Services
• Free account of Azure Machine Learning Studio (classic)

Additional Comments from Instructor/Speaker

No special instructions/comments from the Speaker

Pre-Con Title

The SQL Server DBA’s Guide To Docker Containers

Track(s)

Architecture

Tags

#SQLServer #Docker #Containers #DevOps #Linux

Level

Intermediate

Trainer/Speaker

Edwin M Sarmiento

Abstract

Your role as a SQL Server DBA is evolving and you are now responsible for automating deployment, scaling and management of containerized applications – not just SQL Server databases. You don’t have a clue where and how to start. You are confused about this whole DevOps buzzword being thrown around and you are wasting a lot of time figuring things out on your own. What’s worse, you end up being a stressed, overworked engineer who wished you could focus on the “cool stuff”, but are instead stuck in firefighting mode while juggling the additional workload.

This session is specifically for you. This whole-day workshop is specifically designed for senior SQL Server database administrators and systems engineers responsible for automating deployment, scaling and management of containerized applications.

Modules/Topics

• Overview of DevOps for the SQL Server DBA
• Architecture of Container Ecosystem
• Deploying SQL Server on Docker Containers
• Managing and persisting SQL Server data in Docker containers
• Creating and deploying custom SQL Server container images

Attendee Pre-requisite (if any)

Intermediate to senior SQL Server DBA with familiarity with scripting and automation. Familiarity with the Linux operating system would be helpful.

Additional Comments from Instructor/Speaker

No special instructions/comments from the Speaker

Pre-Con Title

Getting Started with Azure Data Factory

Track(s)

Business Intelligence & Advanced Analytics

Tags

#AzureDataFactory #ADF #AzureSSIS

Level

Intermediate

Trainer/Speaker

Andy Leonard

Abstract

Azure Data Factory, or ADF, is an Azure PaaS (Platform-as-a-Service) that provides hybrid data integration at global scale. Use ADF to build fully managed ETL in the cloud – including SSIS. Join Andy Leonard – Microsoft Data Platform MVP, author, blogger, and Chief Data Engineer at Enterprise Data & Analytics – as he demonstrates Azure Data Factory in action.

In this course, you’ll learn:

1. Provision a Data Factory
2. Basic Data Movement
3. Pipeline Orchestration
4. Monitoring
5. Azure-SSIS
6. Enterprise ADF Execution Patterns
7. Introduction to Mapping Data Flows

Modules/Topics

• Introduction
• Provision a Data Factory
• Basic Data Movement
• Pipeline Orchestration
• Monitoring
• Azure-SSIS
• Enterprise ADF Execution Patterns
• Introduction to Mapping Data Flows

Attendee Pre-requisite (if any)

The Instructor/Trainer has not specified any pre-requisites.

Additional Comments from Instructor/Speaker

No special instructions/comments from the Speaker

Pre-Con Title

Practical Data Engineering With Azure Synapse Analytics

Track(s)

Business Intelligence & Advanced Analytics

Tags

#Synapse #Analytics #BigData #DataWarehousing

Level

Intermediate

Trainer/Speaker

Warner Chaves

Abstract

What started as Azure SQL Data Warehouse, has now evolved into an all-encompassing analytics development service. Microsoft took the power of the MPP database engine of SQL DW and added more powerful capabilities, full big data integration through Spark, an integrated authoring experience and more.

Whether you are an administrator or a developer, Synapse now provides tools that any data professional can use to engineer their own data solution. To enable all these new capabilities, Microsoft has introduced new concepts like SQL and Big Data pools, Azure Data Lake Gen 2 integrated catalogs and more. All these new features and concepts can be easily understood and adopted by current data professionals to bring their careers into the warehousing and analytics space.

In this demo-heavy class we will go over the main concepts behind Synapse, go through all the major features and the new workspace experience.The class will leverage all the in-field experience of the trainer from best practices to lessons learned and pitfalls on how to build your data platform with Synapse Analytics.

Modules/Topics

• Synapse Fundamentals
• Synapse Workspace
• Sql Engines
• Spark Engine
• Import and Export Data
• Building Data Flows
• Visualizing Data
• Workspace Security and Monitoring

Attendee Pre-requisite (if any)

Basic knowledge of databases and basic experience with Business Intelligence systems and tools

Additional Comments from Instructor/Speaker

No special instructions/comments from the Speaker

Pre-Con Title

Power BI – Data Visualization

Track(s)

Business Intelligence & Advanced Analytics

Tags

#Power BI #Data Visualization #Visualization

Level

Intermediate

Trainer/Speaker

Yana Berkovich

Abstract

This 8 hours class will cover the Power BI report layouts and structure the agile process to creating Power BI data visualizations. It will enable attendees to understand the art behind visualizations, implications behind choosing the right charts, the impact of color, shape and size, and finally the use of Power BI custom visuals. The course includes various design patterns of dashboards and reports and best practices to authoring great reports and dashboards for the business users, some accessibility best practices will be covered as well.

Modules/Topics

• Introduction
• What is Story Telling with Data
• Science Behind Data Visualization
• Process Behind Data Visualization
• Dashboards and Reports
• Chart Selection
• LABS 1 & 2
• Custom Visuals
• Polishing & Formatting
• Report Authoring – Best Practices and Accessibility
• Dashboard Design

Attendee Pre-requisite (if any)

Download the latest Power BI version:

https://go.microsoft.com/fwlink/?linkid=2135345

or from the download center https://go.microsoft.com/fwlink/?linkid=2135519

Download the image collection and the dataset to be able to complete the labs form:

https://aka.ms/PBI-adv-Vis

Additional Comments from Instructor/Speaker

No special instructions/comments from the Speaker

Pre-Con Title

Power BI Desktop Data Modeling End-To-End

Track(s)

Business Intelligence & Advanced Analytics

Tags

#powerbi #dataanalytics #datamodeling #powerbidesktop

Level

Intermediate

Trainer/Speaker

Peter Myers

Abstract

In this presentation you will learn how to develop Power BI Desktop data models. The session will describe data modeling fundamentals together with a development methodology covering how to create model tables with Power Query, how to create and configure appropriate model relationships, how to improve and enrich the data model design with usability features and calculations, and how to enforce row-level security (RLS) by defining and testing roles.

This session introduces foundational theory and builds upon it by describing best practices and tips.

This session will appeal to those new to Power BI Desktop data modeling, and also for those already developing data models (this session will be ideal for filing in any gaps in your knowledge).

Modules/Topic

• Power BI Desktop data modeling
• “Getting Data” with Power Query
• Creating data model relationships
• Enhancing data model design with usability features
• Introduction to DAX for defining data model calculations
• Security data models with row-level security

Attendee Pre-requisite (if any)

The Instructor/Trainer has not specified any pre-requisites.

Additional Comments from Instructor/Speaker

No special instructions/comments from the Speaker

Pre-Con Title

Kubernetes Zero To Hero - Installation, Configuration, And Application Deployment

Track(s)

Architecture

Tags

Kubernetes Linux SQL Server Containers

Level

Intermediate

Trainer/Speaker

Anthony Nocentino

Abstract

Modern application deployment needs to be fast and consistent to keep up with business objectives and Kubernetes is quickly becoming the standard for deploying container-based applications, fast. In this day-long session, we will start container fundamentals and then get into Kubernetes with an architectural overview of and how it manages application state. Then you will learn how to build a cluster. With our cluster up and running, you will learn how to interact with our cluster, common administrative tasks, then wrap up with how to deploy applications and SQL Server. At the end of the session, you will know how to set up a Kubernetes cluster, manage a cluster, deploy applications and databases, and how to keep everything up and running.

Modules/Topics

• Container Fundamentals
• Introduce Kubernetes Cluster Components
• Introduce Kubernetes API Objects and Controllers
• Installing Kubernetes
• Interacting with your cluster
• Storing persistent data in Kubernetes
• Deploying Applications in Kubernetes
• Deploying SQL Server in Kubernetes

Attendee Pre-requisite (if any)

All examples will be executed at the command line, so proficiency at a command line is required. Platform dependent (Linux/Windows,Cloud CLIs) configurations and commands will be introduced and discussed in the workshop.

Additional Comments from Instructor/Speaker

No special instructions/comments from the Speaker

Pre-Con Title

Deep Dive SQL Server Migration To Azure SQL

Track(s)

Data Administration

Tags

#AzureSQL #Migration #DMS

Level

Expert

Trainer/Speaker

Raj Pochiraju & Mukesh Kumar

Abstract

In this class, you learn the phases in Azure SQL Migrations, the migration driving factors, migration strategies and challenges. We go very deep into various Microsoft Migration tools and services, take you through the migration journey, discovery, assessment, and migration phases.

Not only you get a very deep dive into Microsoft tools, but also get exposure to various custom IP or tools that Microsoft engineering team developed to unblock very large and complex migrations. Throughout the class you get to listen to our enterprise customer migration stories, their problem statements, desired Azure state, speciifc migration strategies adopted and post migration experience.

Modules/Topics

1. Migration drivers
2. Migration fear factors or challenges
3. Migration phases
4. Migration strategie
5. Microsoft Migration tools in action
6. Microsoft custom migration utilities
7. Minimum 3 Customer success store

Attendee Pre-requisite (if any)

Understanding any database technologies, SQL Server is preferred

Understanding of basic cloud concepts

Students doing Azure migrations or contribute to future Azure migration.

Additional Comments from Instructor/Speaker

No special instructions/comments from the Speaker

Pre-Con Title

Analyze And Optimize Your SQL Server

Track(s)

Architecture

Tags

#SQLServer #Configuration #Installation

Level

Intermediate

Trainer/Speaker

Uwe Ricken

Abstract

The day-to-day business of a DBA as well as consultants who are on the move in terms of ""Microsoft SQL Server"" include careful analysis of SQL Server systems when the business or the customer reports performance problems. In the ""Analysis of SQL Server"" workshop, a specific example is used to test a Microsoft SQL Server for various problem cases that lead to bottlenecks if they are not set correctly.

In the workshop, the various problem analyzes are demonstrated by examples with explanations of the possible effects. At the same time, a solution plan is drawn up in which the participant can work through the correct settings in the operating system to the configuration settings in Microsoft SQL Server. Finally, solutions are shown if the performance within a single database is assessed as insufficient. All possible performance bottlenecks are simulated and evaluated on an installed Microsoft SQL Server using scripts. The following analysis steps are covered during the workshop:

• Check and evaluate settings in the operating system
• Analysis of the databases for configuration, usage and system utilization
• Configuration and analysis of TEMPDB
• Waits and Latches: What is Microsoft SQL Server waiting for and what influence do the various waiting processes have on the performance of the applications.
• Index maintenance - how are indexes used, which indexes are missing, how is the physical condition of indexes to be assessed.
• At the end of the evaluations, a template is presented in which the results of the investigation.

• Understanding the process route of Microsoft SQL Server
• Important settings for the OS
• Important settings for SQL Server
• Analyse the database configuration
• Analyze Wait Stats
• Best practice configuration
• Reporting of found issues

Attendee Pre-requisite (if any)

The Instructor/Trainer has not specified any pre-requisites.

Additional Comments from Instructor/Speaker

No special instructions/comments from the Speaker

Pre-Con Title

Data Science Algorithms

Track(s)

Data Science (AI/ML)

Tags

#datascience #machinelearning #datamining #statistics

Level

Expert

Trainer/Speaker

Dejan Sarka

Abstract

Advanced data analysis techniques are gaining popularity. With modern statistics / data mining / machine learning engines, products and packages, data science has become a black box. It is possible to use data science without knowing how it works. However, not knowing how the algorithms work might lead to many problems, including using the wrong algorithm for a task, misinterpretation of the results, and more. This seminar explains how the most popular data mining algorithms work, when to use which algorithm, and advantages and drawbacks of each algorithm as well. Demonstrations and labs show the algorithms usage in T-SQL, R, and Python languages.

Algorithms explained include Naïve Bayes, Decision Trees, Neural Networks, Logistic Regression, Perceptron Model, Linear Regression, Regression Trees, Ordinal Regression, Poisson Regression, Principal Component Analysis, Support Vector Machines, Hierarchical Clustering, K-Means Clustering, Expectation-Maximization Clustering, Association Rules, Sequence Clustering, Auto-Regressive Trees with Cross-Prediction (ARTXP), Auto-Regressive Integrated Moving Average (ARIMA), and Time Series.

The seminar also includes the explanation of the introductory statistics, including descriptive statistics, correlations and linear associations. Even the information theory is touched briefly. All of these methods are useful for gathering understanding of the data used for later analysis and advanced data profiling. Mining unstructured data, specifically texts, is covered in the course as well.

Modules/Topics

• Introduction to data mining and / or machine learning
• Descriptive statistics for data overview
• Classification, prediction and estimation algorithms
• Forecasting, unsupervised algorithms, and text mining

Attendee Pre-requisite (if any)

The Instructor/Trainer has not specified any pre-requisites.

Additional Comments from Instructor/Speaker

No special instructions/comments from the Speaker

Pre-Con Title

Building Enterprise-Grade Data Pipelines Using Synapse Pipelines in Azure Synapse Analytics

Track(s)

Development

Tags

#AzureData #AzureDataFactory #Dataflows #DataPipelines #DataEngineer #Code-free

Level

Intermediate

Trainer/Speaker

Abhishek Narain

Abstract

Azure Synapse is an integrated analytics service that accelerates time to insight across data warehouses and big data analytics systems. At its core, Azure Synapse brings together the best of SQL technologies used in enterprise data warehousing, Spark technologies used for big data, and Pipelines for data integration and ETL/ELT. Synapse has a web-based Studio that provides a single place for management, monitoring, coding, and security.

In this training, we will deep-dive into Synapse Pipelines, which focusses on data engineers targeting to integrate and transform big data workloads, batch-based, on Azure using Synapse Analytics.

Modules/Topics

1. Synapse pipelines overview
2. Extract data using 90+ data connectors
3. Transform and Load data using code-free data flows
4. ETL patterns and practices – metadata-driven workflows, event-driven data processing pipelines, CI/CD, security, pipeline monitoring and alerting

Attendee Pre-requisite (if any)

Basic knowledge around data engineering processes.

Additional Comments from Instructor/Speaker

No special instructions/comments from the Speaker

Pre-Con Title

PowerShell And SQL Server With DBATools

Track(s)

Data Administration

Tags

#PowerShell #dbatools #sqlserver

Level

Basic

Trainer/Speaker

Rob Sewell

Abstract

The SQL Server Collaborative organisation have created the free, open source PowerShell module dbatools lead by Chrissy LeMaire and with over 100 contributors to extend PowerShell support for SQL Server even further

Join Cloud and Data Center MVP and dbatools evangelist Rob Sewell to learn how to you can make use of both of these modules to simplify and automate your DBA needs.

You will be shown how you can accomplish many routine DBA tasks easily and simply with one line of PowerShell code

• Migrate an entire instance with one line of code - databases, users, jobs, schedules, credentials ,proxies, operators (The list goes on, it's pretty much everything)
• Restore an entire instance from a backup directory
• Test your restores every day
• Simply and easily find a stored procedure, trigger, view in a database even if you don’t know which instance it is on
• Show who changed my database and what they did
• Display the last time a successful DBCC CHECKDB was run for all databases across my estate
• Make us of sp_whoisActive and Glenn Berry's diagnostic queries straight from PowerShell
• Write Pester Tests to validate your SQL Server environment
• And much much more

Come and join me for a great day of PowerShell and SQL full of demos and useful solutions that you will be able to take back to your workplace. You will improve your knowledge and skills, see how the modern DBA will work and have fun at the same time.

Who Are You?

You are a DBA, senior or junior, looking after 1 or 100,000 instances, a developer who interacts with SQL Server or an accidental DBA.

You are interested in improving your PowerShell skills for SQL Server or reducing the time you and your team spend administering SQL Server.

You have spent 1 day or 20 years working with SQL Server.

You know that you work in a field where automation is king and want to understand more of what is available.

You are interested in learning and improving your automation skill set.

What do you need?

All you need is yourself! We would recommend that you bring something to take notes with as well as a minimum.

All scripts and slides will be provided to you. If you wish to follow along with the demos you should have access to a machine with PowerShell v 5 or above with a SQL Server 2012 or above instance.

Modules/Topics

• What is dbatools?
• Finding Commands
• Using Commands
• Migrate an entire instance with one line of code - databases, users, jobs, schedules, credentials ,proxies, operators (The list goes on, it's pretty much everything)
• Restore an entire instance from a backup directory
• Test your restores every day
• Simply and easily find a stored procedure, trigger, view in a database even if you don’t know which instance it is on
• Show who changed my database and what they did
• Display the last time a successful DBCC CHECKDB was run for all databases across my estate
• Make us of sp_whoisActive and Glenn Berry's diagnostic queries straight from PowerShell
• Write Pester Tests to validate your SQL Server environment

Attendee Pre-requisite (if any)

What do you need?

All you need is yourself! We would recommend that you bring something to take notes with as well as a minimum.

All scripts and slides will be provided to you. If you wish to follow along with the demos you should have access to a machine with PowerShell v 5 or above with a SQL Server 2012 or above instance.

Additional Comments from Instructor/Speaker

No special instructions/comments from the Speaker

Pre-Con Title

SQL Server 2019 Big Data Clusters In A Day

Track(s)

Business Intelligence & Advanced Analytics

Tags

#BigDataClusters #SQLServer #Hadoop #Kubernetes

Level

Intermediate

Trainer/Speaker

Ben Weissman

Abstract

With SQL Server 2019, Microsoft introduced a game changer for Big Data and Analytics Workloads: Big Data Clusters

This feature set combines solutions for Data Virtualization, Data Mart Scale Out using traditional SQL Server tables, non relational and file based data as well as Machine Learning and AI.

In this session we are going to explore the capabilities of the exciting new feature. Ben Weissman will walk you through the different aspects and use cases of Big Data Clusters, demo them and eventually show you how to deploy and push data to your own Big Data Cluster so you can start using them right away!

Modules/Topics

Modules/Topics (required)

• What's a BDC
• What do I need to build a BDC
• How do I deploy a BDC
• How do I work with a BDCz
• How do I manage a BDC

Attendee Pre-requisite (if any)

T-SQL or python for data analysis

Additional Comments from Instructor/Speaker

No special instructions/comments from the Speaker

Pre-Con Title

The Beginner’s Guide To Advanced Performance Tuning

Track(s)

Development

Tags

#PerformanceTuning #Index #Query #Plan #T-SQL

Level

Basic

Trainer/Speaker

Erik Darling

Abstract

You’re new to SQL Server, and your job more and more is to fix performance problems, but you don’t know where to start.

You’ve been looking at queries, and query plans, and puzzling over indexes for a year or two, but it’s still not making a lot of sense.

Beyond that, you’re not even sure how to measure if your changes are working or even the right thing to do.

In this full day performance tuning extravaganza, you’ll learn about all the most common anti-patterns in T-SQL querying and indexing, and how to spot them using execution plans. You’ll also leave knowing why they cause the problems that they do, and how you can solve them quickly and painlessly.

If you want to gain the knowledge and confidence to tune queries so they’ll never be slow again, this is the training you need.

Modules/Topics

In this course we'll be talking about how to read query plans to spot problems, query writing anti-patterns, what makes a good index, how parameterization works, and how to make sure your queries are reliably fast.

Attendee Pre-requisite (if any)

1-2 years working with SQL Server, knows how to write basic queries, the difference between clustered and nonclustered indexes, and how to find query plans.

Additional Comments from Instructor/Speaker

No special instructions/comments from the Speaker

Pre-Con Title

Applying Forensic Accounting Techniques To Data Analysis In Python and SQL

Track(s)

Data Science (AI/ML)

Tags

#Python #DataScience #ForensicAccounting #SQL #Jupyter #numpy #scikit-learn #EnsembleModeling

Level

Advanced

Trainer/Speaker

Kevin Feasel

Abstract

Forensic accountants and fraud examiners use a range of techniques to uncover fraudulent journal entries and illegal activities. As data professionals, most of us will never unravel a Bernie Madoff scheme, but we can apply these same techniques in our own environments to learn more about our data. This video will use a combination of SQL Server and the Python programming language to apply these fraud detection techniques and gain a better understanding of your data.

You will learn a variety of techniques to learn enough about your data to draw interesting inferences, starting with the use of basic analytical techniques, including regression analysis. From there, you will learn how to use cohort analysis to find outliers between groups, leading you on a data-driven approach to forensic investigation. Finally, you will review numeric techniques around data set validity, including rules around the distributions of the first and last digits in data sets.

Modules/Topics

• Set up the envrionment, including all materials for learners to participate in certain labs. Introduce the problem space and explore the data set. Learners will know the problem we are trying to solve and have an understanding of the kinds of data at our disposal.
• Perform summary, growth, and gap analyses. Learners will understand how to perform aggregate analyses of static data, as well as flow analyses of time-series data. Learners will additionally understand the importance of gap analysis for forensic accounting and the risks of using identity columns for accounting-critical sequences.
• Perform a regression analysis. Learners will understand how to perform regression using techniques such as Ordinary Least Squares and Ridge regression. Learners will also understand the concept of collinearity and how it can harm our understanding of a regression result. Learners will additionally understand how to build ensemble regression models using tools like gradient boosting and random forests.
• Perform cohort and time series analyses. Learners will understand how to slice data and track changes across relevant features, including time.
• Perform numeric analysis of fact data. Learners will understand the relevance and importance of first-digit and last-digit analysis of data sets for fraud detection purposes.

Attendee Pre-requisite (if any)

Attendees should be familiar with SQL Server and the T-SQL programming language. Familiarity with Python and Jupyter Notebooks is also helpful, though not required.

Additional Comments from Instructor/Speaker

No special instructions/comments from the Speaker

Pre-Con Title

Data-Loading And Transformation Strategies For Modern Cloud-Based Data Warehouses

Track(s)

Business Intelligence & Advanced Analytics

Tags

#Azure #Datawarehousing #ETL #Cloud #Architecture #MicrosoftBotFramework

Level

Advanced

Trainer/Speaker

Benjamin Kettner

Abstract

Most of us know ETL jobs and Datawarehouse designs. When working within the Microsoft Data Plattform on premises, using SSIS is a no-brainer. But what if you build your DWH in the cloud? What if dependencies rapidly change? For example if your sources are no longer Databases and Flat Files but REST APIs? How can you incorporate all this into a cloud-borne DWH project? You will need to rethink the patterns you used to work with on premises to adapt for the cloud. Also, with the multitude of available cloud services, sometimes it is hard to decide on the right tool for your task.

I will develop a modern ETL architecture and talk about the different services, you can apply in order to get your data into your analytical system. You will learn, what tools you an use for which task, how to utilize Azure Functions, Data Factory, Logic Apps and Web Activities and where to make your tasks asynchronous and how to decouple your processes in order to have a stable environment for your cloud-borne DWH.

Modules/Topics

1. Using ADF, ADF Basics
2. Azure Functions
3. Asynchronicity
4. Connectiong with APIs
5. Resiliency, hardening your solution

Attendee Pre-requisite (if any)

Basic Azure know-how, know-how of ETL processes.

Additional Comments from Instructor/Speaker

No special instructions/comments from the Speaker

Pre-Con Title

Microsoft Conversational AI Master Class

Track(s)

Development

Tags

#Bots #ChatBots #AzureAI #ConversationalAI #CongnitiveServices #PowerVirtualAgents #MicrosoftBotFramework

Level

Intermediate

Trainer/Speaker

Prashant G Bhoyar

Abstract

Microsoft's CEO Satya Nadella has said: "Human Language is the new UI layer, bots are like new application". As more and more bots are getting popular in homes and enterprises, the demand for custom bots is increasing at rapid space.

In this full-day workshop, we will cover the following topics in detail.

• Microsoft Bot Framework
• Power Virtual Agents
• Q and A Maker
• Bot Composer Tool
• Adaptive Cards
• Rolling out custom enterprise-grade bots
• Voice-Based Bots
• How to debug Bots
• Authentication and Authorization
• Bots Development Best Practices

At the end of the workshop, attendees will have a working knowledge of the Conversational AI in Microsoft ecosystem and how to use it to implement the enterprise-grade bots (ChatBots as well as Voice-Based Bots)

Access to SharePoint online site, Microsoft Team and Azure will be provided to create the Bots. The target audience of this hands-on workshop is Developers and Power Users and IT Pros who want to get into the development.

You will learn:

• How to create enterprise-grade Bots using Bot Framework and Cognitive Services
• How to create enterprise-grade Bots using Power Virtual Agents
• Bots Development Best Practices

Modules/Topics

1. Microsoft Bot Framework
2. Power Virtual Agents
3. Q and A Maker
4. Bot Composer Tool
5. Adaptive Cards
6. Rolling out custom enterprise-grade bots
7. Voice-Based Bots
8. How to debug Bots
9. Authentication and Authorization
10. Bots Development Best Practices

Attendee Pre-requisite (if any)

The Instructor/Trainer has not specified any pre-requisites.

Additional Comments from Instructor/Speaker

No special instructions/comments from the Speaker

Pre-Con Title

A To Z Azure Cosmos DB

Track(s)

Development

Tags

#NoSQL #CosmosDB #Azure #Cloud #Database

Level

Intermediate

Trainer/Speaker

Hasan Savran

Abstract

Azure Cosmos DB is battle tested non-relational database service by Microsoft. It is used by many mission critical applications, including Xbox, Office 365, Azure and Skype.

This workshop will help you take a more in-depth look at Azure Cosmos DB's architecture, features and tools. We will start with an overview of Azure Cosmos DB's infrastructure and architecture, then explore its main features and benefits. Additionally, we will use Cosmos DB Emulator for development purposes. In addition, we will look at the Data Migration tool to import and transform data as well as cover stored procedures, available SDKs, and Rest API of Cosmos DB. For the closing of this session we will configure Change Feed functionality using Azure  Functions.

Modules/Topics

• History, Infrastructure and Architecture of Cosmos DB
• What does Multi-Model mean?
• Understanding Consistency Levels
• Why Partitioning is crucial?
• Managing Indexes in Cosmos DB.
• Cosmos DB Constraints
• Pricing
• Using Cosmos DB Emulator
• Querying Cosmos DB with SQL API
• Setting up TTL
• Data Type Problems of JSON
• Creating Stored Procedures
• How to Import&Transform Data
• Available SDKs and Rest API
• Spatial Data in Cosmos DB
• Configuring Change Feed
• Using Vs Code extension with Cosmos DB
• Connecting to Cosmos DB From SQL Server 2019

Attendee Pre-requisite (if any)

The Instructor/Trainer has not specified any pre-requisites.

Additional Comments from Instructor/Speaker

No special instructions/comments from the Speaker

Pre-Con Title

SQL Server Performance Tuning – Fast Track

Track(s)

Data Administration

Tags

#Performance #SQLServer #Tuning #Troubleshooting #Query Tuning

Level

Intermediate

Trainer/Speaker

Amit R S Bansal

Abstract

One fine day you realize that you have to start tuning SQL Server for better performance. The need might have arisen from a recent performance issue, or the barrage of support tickets that are filling your inbox, or maybe your boss assigned this to you after realizing that SQL Server performance troubleshooting is not his cup of tea. Irrespective of the reason, the challenge is upon you to identify, diagnose and fix a variety of performance issue that is plaguing your SQL Server environment.

Performance troubleshooting can be categorized as proactive, real-time & reactive. If a well-defined methodology is followed during the proactive phase, you will have to fire-fight less during the real-time troubleshooting phase and you will have a lot of meaningful performance data to work within the reactive phase, which will further help you in preventing performance cases.

Join this demo-filled, full-day class, where you will learn the three approaches of SQL Server performance troubleshooting & tuning. You will learn the practical way, exactly like how you would troubleshoot in the real world with lots of code and scripts. Amongst many takeaways, here are some interesting ones:

• Develop a performance troubleshooting methodology for the real-world
• Learn how to benchmark and baseline (and also take away a framework, ready-to-use)
• Analyzing and troubleshooting common wait types
• Proactive, reactive and real-time CPU troubleshooting
• Troubleshooting IO performance bottlenecks
• Tracking SQL Server memory consumption and fixing OOM errors
• Dealing with Tempdb madness
• Top 6 query tuning techniques that will solve 75% of performance issues (real-world query tuning)
• Top 3 indexing strategies that will fix 60% indexing woes
• Get rid of deadlocks (yes, you hear it right)
• SQL Server 2019 performance enhancements
• In total, there will be five modules comprising of more than 55 demos, all from the real-world. Yes, a lot of content and the class will be fast-paced. The pre-con will focus on recent versions of SQL Server. All demos will be shown on SQL Server 2019. You will walk away with all the demo scripts, most of them being production-ready. You will also get PDF version of the content.

Modules/Topics

Module 1: SQL Server Wait Types & Statistics

1. Query Lifecycle
2. Connections -> Sessions -> Requests -> Tasks -> Workers -> Threads
3. Common Wait Types in SQL Server, identification & diagnosis

Module 2: The Holy Trinity – CPU, Memory, IO

1. Identifying, troubleshooting & tuning workload patterns that cause excessive CPU
2. Identifying, troubleshooting & tuning workload patterns that cause excessive Memory Usage
3. Identifying, troubleshooting & tuning workload patterns that cause excessive IO

Module 3: Query Execution & Query Plan Analysis

1. Statistics & Cardinality Estimation
2. Reading & Analysing Execution Plans
3. Important Iterators

Module 4: Query Tuning

1. Rewriting Transact-SQL
2. Optimizing Stored Procedures
3. Solving Parameter Sniffing Issues
4. Parametrization Best Practices
5. Tips with Procedural Code

Module 5: Index Tuning

1. Demystifying Common Myths with Indexes
2. Indexing Strategies
3. Index Fragmentation
4. Real-World Index Tuning Examples

Module 6: Dealing with Deadlocks

1. SQL Server Concurrency, Isolation Levels, Locking Granularity & Hierarchy
2. Common Deadlock Types
3. Deadlock Best Practices
4. Practical Deadlock Troubleshooting & Diagnosis

Attendee Pre-requisite (if any)

Pre-requisite: You need to have at least two years of genuine SQL Server experience to gain maximum from this pre-con.

Additional Comments from Instructor/Speaker

No special instructions/comments from the Speaker

Note

A better version of this class is available as a video course from SQLMaestros. 15 hours of professionally recorded HD content. NEW YEAR SALE: Book Performance Tuning Video Course & get 100% off on T-SQL Querying & T-SQL Programming Video Courses. Discount Code: HAPPY2021. Learn More.

Pre-Con Title

Machine Learning For Developers

Track(s)

Data Science (AI/ML)

Tags

#MachineLearning #DataScience #AzureML #ML.NET #MLOps

Level

Advanced

Trainer/Speaker

Anupama Natarajan

Abstract

If you are a developer and new to Machine Learning and would like to learn ML from Fundamentals to Advanced, then this session is for you. This course will start with an introduction to Machine Learning and walk you through the Machine Learning process. You will get to learn the different types of ML Algorithms and how to use the right algorithm for the right problem with some real-world scenario examples. Then we will delve into the creating Machine Learning models using Azure Machine Learning Service and finally we will wrap up the session with some ML best practices you must consider.

Modules/Topics

Introduction to Machine Learning

• What is Machine Learning?
• Machine Learning Challenges
• Benefits of Machine Learning
• How to determine Machine Learning Use Cases in your organisation?

Machine Learning Fundamentals

• Machine Learning Process
• Machine Learning Algorithms and Patterns
• Machine Learning Life Cycle
• How to choose the right algorithm for the right problem?

Advanced Machine Learning

• Introduction to Azure ML Studio
• Machine Learning Models using Python Notebooks
• Create ML models using drag-and-drop tool
• ML models using Automated Machine Learning
• Machine Learning models using VS Code
• Introduction to ML.NET

Machine Learning Best Practices

•  Models Optimization
• Enterprise Security and Governance
• Responsible ML (Privacy, Fairness and Confidentiality)
• MLOps and Monitoring

Attendee Pre-requisite (if any)

Prior knowledge with Azure Portal, C# or Python.

Additional Comments from Instructor/Speaker

No special instructions/comments from the Speaker

Pre-Con Title

Maximum ROI - How To Optimise Your Azure SQL Database Platform And Applications

Track(s)

Data Administration

Tags

#azure #sql #sqldatabase #performance

Level

Advanced

Trainer/Speaker

Martin Cairney

Abstract

When you move from SQL Server on-premises to Azure SQL Family of services you will find that there are different performance issues that you need to deal with – things like choosing the correct version such as Hyperscale, Managed Instance, Serverless or SQL Database are all important starting points, but once you have that decision then how can you tweak more performance out of your applications?

With the move to the cloud, factors such as increased latency and restricted resources based on your choice of service and tier come in to focus and you have to consider each of these to maximise your ROI for the choice of platform. You could simply continue to scale up to the next performance level but that involves additional cost and soon your savings from the move from on-premises are wiped out.

To get your database humming along at its peak performance you need to start with your configuration – have you chosen the right tier, is it configured for maximum performance, are you connecting optimally before looking at how to optimise your resource usage and then considering techniques to minimise consumption of your precious DTUs.

In this workshop, we will work through the many layers we can tweak to improve our Azure SQL Database performance with plenty of demos to let you see the impact.

Modules/Topics

• Comparison of the different service offerings in the Azure SQL Family and which one to choose for your workload
• Optimising the configuration for maximum performance
• Improving latency on connections
• Identify and remove bad application access patterns
• Optimisation memory usage with limited resources
• Identify and mitigate IO constraints
• Patterns for loading data
• Using Read Scale-Out to maximise read-only query performance
• Monitoring methods and tools

Additional Comments from Instructor/Speaker

No special instructions/comments from the Speaker

Pre-Con Title

DAX in a day

Track(s)

Business Intelligence & Advanced Analytics

Tags

#DAX #PowerBI

Level

Basic

Trainer/Speaker

Philip Seamark

Abstract

An introduction to the DAX language following a hands-on course with instruction from a member of the Microsoft Power BI team.

Modules/Topics

• Describe Power BI Desktop Models
• Write DAX formulas for Power BI Desktop models
• Add calculated tables and columns to Power BI Desktop models
• Add measures to Power BI Desktop Models
• Use DAX iterator functions in Power BI Desktop models
• Modify DAX filter context in Power BI Desktop models
• User DAX time intelligence functions in Power BI Desktop models

Attendee Pre-requisite (if any)

A recent version of Power BI Desktop installed

Additional Comments from Instructor/Speaker

No special instructions/comments from the Speaker