My Sessions

I offer a variety of sessions focusing primarily on SQL Server technologies.

I offer onsite presentations to User Groups and at conferences around the country!

I am able to offer any of the listed sessions or can work with you to develop a session to cover what you need.

Abstracts

NEW - Using Modular Scripts to Perform SQL Compliance Audits in Seconds

Level 200 – 60 – 75 min

Summary:

Review common audit requirements, why they matter and get tools to let you quickly collect audit data from your SQL Server environment.

Abstract:

Security audits such as PCI, STIG, or HIPAA, are important and can be complicated. Depending on the requirements it can take considerable time to gather the information needed.

In this session, we will go over some scripts that are used for compliance audits that make it easy to collect the information required. Even when there are multiple SQL Servers, these scripts and processes can quickly and easily identify where to focus efforts to ensure compliance. Through heavy demonstration and discussion, you will leave with a better equipped to understand and obtain compliance within your SQL Server environment.

PreReqs: Basic knowledge of T-SQL and PowerShell

Goal 1: Learn DOD/NIST recommended practices for SQL Server

Goal 2: Review the use of a modular script process to easily customize data collection to your needs and/or requirements.

Goal 3: Be able to quickly check the security state of your SQL Servers, no matter how many you have.

Addtl Notes/Methodology:

This is not a marketing sessions. After having released the sp_WOXComplaint script I have been asked to show how it works and that is what this session is. The script and others referenced and provided are completely free to download and use at your own risk.

NEW - The Everyday (Budget Poor) DBA’s Hoarding (Stretch) Database

Level 300 – 60 – 75 min

Summary:

When Microsoft announced Stretch Databases we all loved the idea. Then the pricing came out and we all suffered broken hearts. Rather than just giving up on the prospect what would happen if we were to combine various SQL Server features and try to “stretch” databases to the cloud ourselves?

Abstract:

When Microsoft announced Stretch Databases we all loved the idea. Then the pricing came out and we all suffered broken hearts. Rather than just giving up on the prospect – maybe we could combine various SQL Server features and try to “stretch” databases to the cloud ourselves?
By using files and filegroups and partitioning along with a locally mounted Azure (or AWS) fileshare you can build your own stretch database without necessarily breaking the bank (YMMV).
This session with go through the steps used to build a quasi-stretch database to store old stale data in cheap cloud storage yet still have it available and simple to access when needed.

PreReqs: Knowledge of SQL Server filegroups and basic cloud storage

Goal 1: Roll your own “Stretch” Database

Goal 2: Find a way to reduce your local storage footprint while maintaining old data

Goal 3: Learn how different features of SQL Server can be combined to get results you desire

Addtl Notes/Methodology:

This session consists of a discussion around an overview powerpoint slide deck and demonstration of the techniques combined to create the stretch effect. This session is demo-heavy and goes through some more advanced options in SQL Server along with methods used to ‘trick’ the OS and SQL Server into thinking the cloud storage is local.

COMING SOON - Build Your First SQL Server Container Lab in Docker

Level 200 – 60 – 75 min

Summary: Containers are the direction things are heading, so lets review what they are along with building a container for your SQL Server installations.

Abstract: A few years back virtualization changed the way to work with SQL and infrastructure. Now the next chapter is opening and it’s all about containers. Containers can make the DBA’s job of creating and deploying servers much easier freeing them up for other tasks such as performance tuning and security.

In this session, we will go over the concepts of containers, their general capabilities, and then build, connect and configure a SQL Server in a container for use as a private lab.

PreReqs: Some knowledge of virtualization, SQL, and OS installation.

Goal 1: Understand what containers are and how they can be used by you or your company

Goal 2: Build a container with SQL Server for practice purposes

Goal 3: Experience the speed in which containers can work and learn how to help control costs by working with them.

Addtl Notes/Methodology:

This session consists of a discussion with a powerpoint slide deck for reference and live demonstrations of the process and configuration of a SQL Server in Docker container. It assumes Docker is available on the system already and will Microsoft’s provided container configuration to build a mini SQL Lab within the container environment.

COMING SOON - Build a White/Blacklist Bouncer. Who Gets into Your SQL Club?

Level 300 – 60 – 75 min

Summary: Need to block a certain IP from accessing your DB? Perhaps you need to allow only certain IPs to connect? Find out what you can do when you can’t wait for network admins.

Abstract: Are too many systems hitting your SQL Servers? Do you know who they all are? Are you under attack?

When it comes to this type of problem the consensus is to get with your network and domain admins to adequately configure access options.

What happens when you can’t wait or don’t even have folks in that role to get the configuration right?

A blacklist is a list of what cannot access something you are trying to protect. (Like the TSA no-fly list. If you are on the list, you are not getting anywhere fast.)

A whitelist is just the opposite. It is a list of what is permitted to access something. If whatever is attempting access is not on the list, it isn’t getting in.

In this session, we will go through the process to create and test your own SQL based whitelists and blacklists and also take a look at how whitelisting is being used in SQL 2017 to control what CLR objects can be trusted to run in your environment.

PreReqs: knowledge of how to create triggers

Goal 1: Discover the process to create a blacklist of IPs and prevent access to your SQL Server instance using logon triggers

Goal 2: Discover the process to create a whitelist of IPs and allow access to your SQL Server instance using logon triggers.

Goal 3: Review the new clr assemblies whitelist feature to grant you more granular control over the clr assemblies that can be run on your SQL Server.

Addtl Notes/Methodology:

TBD

Would You Just Load Already?! Maximizing Your SSIS Data Load

Summary: Learn a simple method to load data via SSIS while identifying, removing, and recording any bad records encountered allowing you to maximize the quantity and integrity of the data loaded.

Abstract:

Loading data via SSIS is great! It’s fast too! Until you hit a bad record. Then things aren’t so great. Now you probably have to waste time dumping and reloading all the data, or worst figure out where the bad record is and try a partial load.

In this session you will learn a simple method to load data via SSIS and identify, remove, and record those bad records allowing you to maximize the amount of data loaded while ensuring data integrity.

PreReqs: Basic knowledge of SSIS and data load process.

Goal 1: Learn to filter bad data from your data loads

Goal 2: Learn to maximize the speed of your load, even with bad data

Goal 3: Ensure maximum data loaded while maintaining its integrity.

Statistics and Cardinality – How they work together to find data efficiently

Summary:

This session not only discusses and shows what statistics are but how, when paired with the cardinality estimator, they have a significant impact on the performance of your SQL queries.

Abstract:

Are your statistics up to date?

Yes, it’s that dreaded phrase you hear all the time when it comes to performance tuning your SQL Server. What does it matter? Why should I care about statistics? Aren’t they automatic or something like that?

In this session we will go over why statistics matter, how they are generated, how they impact performance, and even how to maintain them. Once we have that all settled, we will take a quick look at how the cardinality estimator uses statistics to determine a ‘good enough’ plan to use. We will close out the session by taking a look at the performance impact a simple upgrade of your SQL Server to 2014 and the new cardinality estimator can have.

PreReqs: This session only requires you to have a basic knowledge of SQL Server and an interest in learning more about how the engine works behind the scenes.

Goal 1: Learn what SQL Statistics really are

Goal 2: Learn how statistics are generated

Goal 3: Understand how statistics and cardinality work together to find data efficiently

Optimizing Protected Indexes

As a SQL Server DBA I see my role having 2 main focuses. First is to protect the data and second is to ensure performance is maximized. More often than not companies forget about data security when they focus on performance tuning. In this presentation I will mention a few cases where encryption could have saved a lot of hassle. I will then demo a method that can be used to “index” commonly searched on encrypted values (ex: SSNs, Credit Card, etc) helping reduce the impact on the performance of a query.

Prevent Recovery Amnesia – Forgetting the Backups

Do you suffer from Recovery Amnesia?

You have a complete strategy to backup your SQL environment; but what about the recovery?

Is that even part of your strategy?

Through this introductory session we will review the various aspects & myths of backups in SQL Server. We will pin down how developing a recovery strategy to meet business requirements is, in fact, more important than just a good backup strategy.

Hacking Exposé – Using SSL to Secure SQL Server Connections

Summary: Did you know that, by default, SQL Server does not protect connections to your data?

Learn just how much is exposed, and just how simple it is to enable connection encryption using an SSL certificate.

Abstract: You know all the ways to protect your database when it is at rest, but what about when someone connects and starts running some queries? What if they connect and don’t do anything? Just how exposed is that data?

We will assume the role of a hacker and, by using a simple technique, we will sniff packets on a network to reveal what data is being sent. You may be shocked!

We will then secure our database connections with a simple self-signed SSL certificate.

Once secured, we will resume the role of the hacker and look inside the packets to see what has changed.

*Warning – Do not try these demos at work without proper permissions as actual hacking techniques are used.

PreReqs: This session only requires you to have a basic knowledge of SQL Server configuration and an interest in protecting the data in your database.

Goal 1: Learn what is exposed when using the default, unprotected connections to SQL Server

Goal 2: Learn how to configure SQL Serve to use an SSL certificate to encrypt connections

Goal 3: Learn ways, with proper permission, to capture and check network packet to confirm data in transit is protected

Addtl Notes: This session is designed to engage beginner through experienced SQL Server users via basic configurations and tools, yet diving deep into the internals to see how SQL Server is working with the security settings

Hacking Exposé Too - Just How Transparent is TDE?

Summary: Compliance. The dreaded word. If you use, or are thinking about using, Transparent Data Encryption to be compliant with some regulation, do you know how transparent it is?

Learn how much data, if any, is exposed when you use TDE on your SQL Server.

Abstract: You have to be compliant, but can’t change source code, or SQL code. What’s your option? Transparent Data Encryption (TDE) of course!

The real question though, is just how transparent is TDE?

In the second installment of the Hacking Exposé series, we will assume the role of a hacker once more and dig into our database and steal our backups and data files. We will then try to restore the files, and even dig into the files themselves and see what might be exposed.

We then go through the steps to lock down our data using TDE.

Once again we assume the role of a hacker and try to dig through the database, steal backups and data files and see just what is available to us.

*Warning – Do not try these demos at work without proper permissions as some actual hacking techniques are used.

PreReqs: This session only requires you to have a basic knowledge of SQL Server configuration and an interest in protecting the data in your database.

Goal 1: Learn exactly what is protected by TDE

Goal 2: Learn how to configure and implement TDE

Goal 3: Learn ways, with proper permission, to see just how much might be exposed when using TDE

The Spy Who Loathed Me - An Intro to SQL Security

Summary: SQL Server features a lot of different fully integrated options to protect your data.

This demo-free session goes over a majority of the features available so you can make the best decision of which of them to combine and use to protect your data in your environment.

Abstract: You have lots of data painstakingly collected over the years.

How do you ensure that all your hard work is protected from hackers, spies and other ne’er-do-wells?

Having knowledge of the vast array of security features available in SQL Server is the first step in helping determine what actions you need to take to protect your data.

This session will quickly go through a majority of the security features in SQL Server, to help you learn what each does, and let you determine which features work best for your situation.

*Note – This session is demo-free.

PreReqs: This session only requires you to have a basic knowledge of SQL Server and an interest in protecting the data in your database.

Goal 1: Learn what security features are available in SQL Server

Goal 2: Learn how each works at a high level

Goal 3: Be able to determine which security features to combine to protect your data

Addtl Notes: This session is demo-free. It has been shown that any demo directly influences choices and actions via observational learning. Because security is a fairly sensitive topic the choice was made to relay a LOT of basic information in a condensed time rather than focus on a single topic or feature with demos.