GroupBy – Free SQL Server Training

TSQL was voted best programming language in 2013, let's explore why!

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.

You've heard that In-Memory OLTP is fast, but how do you know if your workload can take advantage of this awesome feature?

We will explore In-Memory OLTP workload evaluation, use cases, architecture, capacity planning, data migration, hot/cold data, potential impact on disk-based workloads, and more.

By the end of the session, you will have an understanding of how In-Memory OLTP works, and whether your workload is likely to see an increase in performance.

Get full notes, resources, and more information here: https://groupby.org/conference-session-abstracts/in-memory-oltp-survival-guide/

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.

Attendees will be taken through the following:

• Defining what containers are (benefits and limitations)
• Configuring Windows Server 2016 to run containers
• Installing the docker engine
• Pulling/Pushing SQL images from/to the docker repository
• Running SQL Server containers
• Committing new SQL Server images
• Exploring 3rd party options to run containers on previous versions of Windows Server (real world example)

How do you know your database change won't affect something you haven't thought of?

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 tools I use to make this possible with SQL Server are:

• Redgate's ReadyRoll plugin for Visual Studio to build migration script
• GitLab for source control and project management
• tSQLt for unit tests
• SQL Cover for unit test coverage reports

By the end of this session, I hope you will see how a CI approach to database development can remove the unknowns from deploying database changes.

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.