GroupBy – Free SQL Server Training

GroupBy.org is free technical training by the community, for the community. Volunteer speakers submit abstracts at GroupBy.org and you – the attendee – vote to pick the sessions. Want to present your own session or just vote on others? Join us at GroupBy.org.

One of the most exciting new features of SQL Server 2016 is the integration of the R statistical programming language into the platform with the brand-new SQL Server R Services. In this introductory session we’ll briefly introduce the R language and then look at how to install and administer SQL Server R Services. We’ll start to demonstrate the power of R by showing you how to create and run simple R scripts from within T-SQL. We’ll then discuss some of the powerful open source packages that are available to aid your data analyses. Finally, we’ll go through some examples of how to use R to integrate data analytical methods in real-world situations, and wow your customers with the power of statistics!

T-SQL can help solve many problems you are faced with at your job. Each problem can have many solutions, but not every solution is simple to write, understand and maintain, and not every solution is good in terms of performance. In this session, we will look at real-world problems and solve them using T-SQL. We won’t settle for just A solution, but show elegant, simple solutions that will yield optimal performance.

In this session, I will talk about advantages of Query Store, features, configuration and query optimization using Query Store on SQL Server 2017. The session will also cover how one can monitor query performance, how Query Store gathers data and which are the best practices for using Query Store. During the session, there will be various demos.

Once data leaves your SQL Server, do you know what happens, or is the world of networking a black box to you? Would you like to know how data is packaged up and transmitted to other systems, and what to do when things go wrong? Are you tired of being frustrated with the network team?

In this session, we introduce how data moves between systems on networks, then look at TCP/IP internals. We’ll discuss real-world scenarios showing you how your network’s performance impacts the performance of your SQL Server and even your recovery objectives.

Based on the successful 500 Level talk at PASS 2016, this session will provide an internals view of how In-Memory OLTP works for SQL Server 2016 and 2017. Because this session is Advanced, it is intended for those that understand the basic fundamentals of In-Memory OLTP but want to learn how the “Hekaton” engine works behind the scenes to deliver the amazing 30x performance improvements seen for customers moving to In-Memory OLTP. This session will include a look behind the scenes at threads, data and index design, transactions and concurrency, logging, storage, and natively compiled procedures.

I’m a big believer in visuals and demos so you will see plenty of that as I describe how the Hekaton engine is truly lock-free and latch-free. And of course since this is an advanced talk, the Windows Debugger will definitely make an appearance during the session.

And since this will be an advanced level session, of course, the demonstration of the looking at the Hekaton engine with the debugger is a must.

Are you faced with complaints from users, poor performing code from developers, and regular requests to build reports? Do you uncover installation and configuration issues on your SQL Server instances? Have you ever thought that in dire times avoiding Worst Practices could be a good starting point? If the answer is “yes”, then this session is for you: together we will discover how not to torture a SQL Server instance and we will see how to avoid making choices that turn out to be not so smart in the long run.

You are probably thinking: “Hey, wait, what about Best Practices?”. Sometimes Best Practices are not enough, especially for beginners, and it is not always clear what happens if we fail to follow them. Worst Practices can show the mistakes to avoid. I have made lots of mistakes throughout my career: come and learn from my mistakes!

Microsoft has introduced native capability to the database engine around masking sensitive data and restricting access at the row level. Allowing us new options for building more secure Data Platform solutions. However, it is important to understand the differences from our previous options in order to be able to gain the most benefit from these new technologies.

Join me as we look in more detail at how we can engineer these features into our Data Platform solutions. Starting with identifying the problems they are trying to solve, through their core architecture and on to potential design patterns for their use. As with any security technology solution, there are a number of ways to use these features. However, as with all security features, there are gaps in coverage, using them effectively in a layered approach is vital.

After this session, you will be in a position to start looking at whether your systems can benefit from these features. Along with how you can potentially start building functionality into your applications.

On this session, we follow the movement of data through batch and speed layers via Azure Data Lake Store & Analytics, Data Factory, SQL Datawarehouse and Streaming Analytics, before looking briefly at Azure Analysis Services with PowerBI. This is a largely theory-based session to prime you for the future.

It just works – performance and scale in SQL Server 2016 database engine and what is being added to in-market versions. This session will showcase several improvements in SQL Server, focusing on the latest enhancements that address some of the most common customer pain points the Database Engine, involving tempdb, new CE, memory management, T-SQL constructs as well as diagnostics for troubleshooting query plans, memory grants, and backup/restore. Understand these changes in performance and scale, and the new and improved diagnostics for faster troubleshooting and mitigation.

In this session, we will look at what makes SQL such a fascinating language. We will do this by working through a few real-life development problems and common design challenges based on real questions asked on Ask.SQLServerCentral.com.

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.

We can be better at our jobs if we have a good grasp of basic statistics.

It doesn’t matter if you’re a DBA looking to understand query plan performance, a data warehouse person needing to come up with ETL load time estimates, or an analyst needing to report figures to managers. Statistics can help you all.

If only maths classes hadn’t been so darn boring!

Instead of going all mathsy, we’ll be doing some real-time data capture and taking an intuitive and visual approach through summary statistics right up to understanding how to produce simple predictive models.

By the end of the session, you’ll understand concepts like sampling, error, regression, and outliers – important day-to-day stuff and a great base upon which to build. By the end of the session, you’ll wonder how people could have it made seem so hard for so many years.

For years, you've heard that you're supposed to reorganize your indexes to make SQL Server go faster. It sounds like it makes sense - keep things in order, right? But you keep doing it, and SQL Server isn't getting any faster. You've even heard that setting fill factor will help prevent fragmentation, and you're doing that too - but your indexes still keep getting fragmented every day, and users aren't happy with performance.

This advice made a lot of sense at the turn of the century, but today, things are different - and we're not just talking solid state drives. In just the first 15 minutes, you'll have a series of ah-ha moments when you realize that your daily index maintenance jobs might just be making the problem worse instead of better. Then, you'll learn what you need to do instead.

Imagine your Data Warehouse is growing exponentially to multi-terabytes and you have been tasked to make analytics queries even faster while keeping the cost of storage low. Industry leading SQL Server 2016 columnstore technology can help you solve these by reducing the storage footprint by 10x (average) and speed up analytics queries up to 100x. SQL Server Tiger team has leveraged columnstore technology with many Tier-1 workloads to achieve these goals. This session will cover three different types of workload. For each workload, we will cover the application architecture, challenges and how SQL Tiger team used columnstore index technology to address them.

I was required to prove that I had successfully installed and configured a backup solution across a large estate. I had a number of success criteria that had to be met. Checking all of these by hand (eye) would have been error prone, so I wrote a test to do this for me and an easy for management to read HTML report using PowerShell and Pester.

The session has come from that situation and is about enabling you to provide an easy to read output to quickly and repeatedly show that infrastructure is as expected for a set of checks, also known as Operational Validation using Pester. There are many use cases for this type of solution; DR testing, installation, first line checks, presentation setups

What is Pester?

Pester is a Unit Testing framework for PowerShell which can be used for testing your code but also as shown in this session for validating your infrastructure. This is an excellent post by Adam Bertram to introduce Pester It is included with PowerShell on modern Operating Systems and free to download from the PowerShell Gallery or GitHub if not included.

After this session, you will have a basic understanding of how Pester works and the capability to examine your checklists and create your own validation tests and provide some reporting for management.

Database objects can have many dependent objects in the database and in the application. Keeping track of these dependencies can be difficult, especially if the database is used by multiple applications or services.

The first step is to have a project for the database and get it into source control.

Source control is the single source of truth that all deployments should be kept in sync with. Source controlling the database also acts as the foundation for automation of:

Builds

Tests

Coverage Reports

This automation is key to the Continuous Integration methodology. After every commit, builds and tests will run in the background and only alert if there is a problem. Builds test the deployment of the change and tests check that everything affected still works. Coverage reports indicate any gaps in the testing suite.

The war between DBAs and developers has been raging since the dawn of relational databases. One reason for disagreement comes from developers who want to store their data in JSON because it is fast, standard, and flexible. DBAs cringe when they hear of long text strings being stored in their SQL databases; they cry with concern, “No data validation? No schema binding?”. Is there any hope for these two warring factions to see eye-to-eye?

This session will explore the new JSON functionality introduced in SQL Server 2016. We will use T-SQL examples to learn how these functions can be used to parse, create, and modify JSON data. More importantly, we will discuss how to optimize performance when using these functions.

By the end of this session DBAs and developers will know how to efficiently work with JSON in SQL Server 2016. It will also usher in an era of peace between DBAs and developers…

… at least until someone brings up the topics of cursors, NOLOCKs, or Entity Framework.

Get full session notes, resources and more here: https://groupby.org/2017/03/dbas-vs-developers-json-in-sql-server-2016/

SQL Server releases every 2 years. SSMS releases every month. Power BI releases every week. It’s impossible to “keep up” with technology, and it’s foolish to try. Instead, you should realize you have a limited amount of TrainingBucks: time, energy and focus. And then you have to decide how to make the most of those TrainingBucks.

First, we’ll talk about how exposure and mastery are different goals with different paths. We’ll cover how exposure does not equal mastery. Second, we’ll talk about strategies for adding more learning with the time you already have available. Third, we’ll cover what types of training lead to deeper, stronger learning. Finally, we’ll talk about having a theme in your learning. We’ll talk about how learning the wrong things is wasting your time.

During the presentation, I will use an interactive Power BI report to visualize how using these strategies will lead to more growth for less effort. This report will be publically available, so the viewers can follow along as they watch.

The goal of this talk is to give you an analytical framework and multiple dimensions to look at, instead of the simplistic “more is better”. More is not better, and if you try to drink straight from the firehose, you’ll drown. By the end of this talk, you’ll have a roadmap for how to focus your training and grow your career.

Are you new to SQL Server and not sure where to begin with T-SQL? Does it feel like a foreign language? The objective of this session is to go over simple T-SQL Statements and to show you how to build on them. We will focus on simple select statements and translating English into T-SQL. Once you have the Rosetta stone, you will find that T-SQL becomes like second nature. We will also focus on adding a simple where clause, order by and join. We will also create a simple backup script and learn about the built-in intellisense in SQL Server Management Studio.

This isn’t the dark ages anymore. You’ve learned that you need to put your database in source control and you’re competent with source control systems like TFS or Git. You’ve also learned how to express your database in script form using a tool like SSDT, DbUp or Redgate.

However, you still haven’t written as many automated tests as you know you should and you aren’t convinced you’re going about it the right way. You haven’t looked at the build functionality in VSTS yet or gotten to grips with build servers like TeamCity or Jenkins, and even if you have you aren’t sure how the process should work for SQL Server builds and tests.

In this session, I’ll explain how to use tSQLt to build a suite of automated tests to give you confidence in the quality of your code. Then I’ll talk through various ways to automate your database builds and run those tests on some schedule or trigger. I’ll also discuss the pros and cons of various different approaches so that you can understand which approach would suit your teams and projects.

Are you accountable for databases or database servers? Do you fix problems only to see them pop up again months or years later in slightly different scenarios? Have you been called at 2am and asked to “health check” hundreds of servers for an issue that nobody in the world has ever documented before and wished there was an easy mode to do that or even avoid the problem in the first place?

If so then Operational Validation can help you. In this session you’re going to get:

A mindset on how to handle your day to day duties in a way that keeps you efficient, honest to yourself and to others, while covering your posterior. Free tools to fix things and make sure they stay fixed over time. Really free. As in you couldn’t pay for them even if you wanted to. If you work with one instance you’re going to learn how to manage five hundred instances. And if you work with five hundred instances you’re going to learn how to have fun doing it.

You’re going to learn:

Jenkins, a cool web application you can install on your workstation to provide orchestration and reports. How to integrate PowerShell with Jenkins and multi-thread efficiently in the background with PoshRSJob using ready-to-go templates so you don’t need to worry about how it works. Then we’re going to write some tests. They’re not going to be beautiful tests. We’re going to be testing some of the scummiest, most crapulent edge cases SQL Server has to offer. Please bring tissues because there will be tears. If you’ve ever said, “I don’t know what to test”, I’ll make you regret it. I’ll also discuss a few other PowerShell modules like CimSession and DbData and why you might want to use them for reliable and scalable tests. By the end of the session, you should be bouncing off the wall and reconsidering your life. I want you to start screaming about Operational Validation from the rooftops, and have the tools and know-how to quickly start on your own.

How easy is it to hack a SQL Server? In this session, we'll see examples on how to exploit SQL Server, modify data and take control, while at the same time not leaving a trace.

We'll start by gaining access to a SQL Server (using some "creative" ways of making man-in-the-middle attacks), escalating privileges and tampering with data at the TDS protocol level (e.g. changing your income level and reverting without a trace after payment), hacking DDM, and more.

Most importantly, we'll also cover recommendations on how to avoid these attacks, and take a look at the pros and cons of new security features in SQL Server 2016.

This is a demo-driven session, suited for DBAs, developers and security consultants.

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 couple 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 with your own trial. Platform as a Service isn’t the answer to every problem, but after you see how simple it is to get started, maybe you’ll get some ideas of where it is.