SQLSaturday #447 Dallas Precons

Submissions for precons are open August 1, 2015 until August 14, 2015. Please send email to precons@ntssug.com with your bio and abstract and which track it belongs (DBA or BI or Developer), if you would like to submit. Then, the community will vote on the submissions between August 15th and 21st, 2015.  Click here for more details on submitting and voting.

NTSSUG Members can send their vote to precons@ntssug.com with precon title as your subject between August 15th and August 21st. 

Submitted Sessions:

1. Index Magic: Rowstore, Columnstore  and Nostore BY Kalen Delaney

2. Creating a High Availability and Disaster Recovery Plan BY Ryan Adams

3. TSQL:  From Basics to Advanced BY Michael C. Byrd

4. Implementing the Modern Data Warehouse BY Ginger Grant

5. Fast Track your Business Intelligence Projects with Biml BY David Stein

6. Building Better SSIS Packages BY Tim Mitchell

7. Murder They Wrote BY Wayne Sheffield

8. A Masters Passport to Extended Events BY Jason Brimhall

9. 10 things I Wish I know when changed skill sets from MS Access to SQL Server BY Stewart Rogers

10. To the next level: mastering SQL Server performance BY Daniel Janik

Title: Index Magic: Rowstore, Columnstore  and Nostore

Speaker: Kalen Delaney

Indexes allow SQL Server to access your data in the most efficient manner. In fact, indexes are the only tuning technique that can improve query performance by orders of magnitude, so that queries that might have taken hours to run can be reduced to sub-second execution time. Understanding exactly how your indexes are structured and stored internally can give you a deeper understanding of what indexes will be useful for your queries.  SQL Server 2014 provides three very different kinds of index structures, useful for different purposes in different kinds of applications. Knowing how indexes are useful, and how each type is stored and managed can allow us to generalize some best practice recommendations.  In this seminar we’ll look at the following topics:

B-Tree Index Structures

Clustered vs nonclustered

Unique vs non-unique

Included columns

B-Tree maintenance: splits, fragmentation and defragmenting

Best practices

Columnstore Index Storage

        Nonclustered columnstore

        Clustered updateable columnstore

        Management of columnstore indexes

        Best practices

In-memory Index Structures

        Memory-optimized tables

        Storage and management of indexes for memory-optimized tables

Best practices

Compare and contrast

How to choose

Kalen Delaney has been working with SQL Server since 1987, and provides advanced SQL Server training to clients worldwide. She has been a SQL Server MVP since 1993 and has been writing about SQL Server almost as long. Kalen has spoken at dozens of technical conferences, include almost every PASS conference in the US since the organization’s founding in 1999. Kalen is the author or co-author of many books on SQL Server, including In-memory OLTP: Inside the SQL Server 2014 Hekaton Engine from RedGate, and SQL Server 2012 Internals, from Microsoft Press.   She is one of the main editors for SQL Server Central’s SQL Server Stairways Series, http://www.sqlservercentral.com/stairway. Kalen blogs at www.sqlblog.com and her personal website and training schedule can be found at www.SQLServerInternals.com.

Title:   Creating a High Availability and Disaster Recovery Plan

Speaker: Ryan Adams

Abstract:  You have been tasked to configure a high availability and/or disaster recovery plan to ensure your company's systems are always up and running while able to withstand a disaster.  SQL Server offers several options, but how do you choose the right one to meet your business requirements?  I've been faced with this same problem.  I'll help you figure out how to choose the right technology.  We will start off with a solid foundation in backups and demonstrate how to configure them for initializing the various HA/DR technologies while also cutting your backup and restore time in half.  You will see how you can keep an offsite copy of your database in sync with your production system with mirroring.

You've read that several of the HA/DR solutions require Windows Failover Clustering, but you don't know anything about managing a complicated Windows cluster.  I'll show you how to setup and configure a Windows cluster and we'll build on that with SQL Server AlwaysOn Failover Clustering Instances that provide the 99.999% of up-time your management demands.  Lastly we'll dive into how to setup AlwaysOn Availability Groups to solve both HA and DR in one solution.  We will see how all these technologies work together by discussing a case study and developing a comprehensive solution just like you have to do when you return to the office.  Here's what you will learn:

Planning a backup strategy to avoid a data disaster

Cutting your backup and restore time in half when initializing DBs for Mirroring or Availability Groups

Configuring mirroring to keep production in real-time sync with an offsite database copy

Learning configuration tips to increase throughput

Configuring a Windows Failover Cluster to meet that five 9s business requirement

Discovering how to properly configure a Windows Cluster quorum to support the AlwaysOn feature set

Implementing a SQL AlwaysOn Failover Cluster Instance

Creating a SQL AlwaysOn Availability Group to solve both your HA and DR requirements in one solution

Learning Objectives:

Learn how to design and implement a solid backup strategy.

Understand how to configure Windows Clustering and quorum design to support SQL Server HA and DR technologies.

How to install AlwaysOn Failover Clustering and AlwaysOn Availability Groups along with best practices.

Session Prerequisites:

Basic understanding of networking

Knowledge about different types of database storage

BIO:   Ryan Adams is a SQL Server MVP and has worked for Verizon for 17 years. His primary focus is the SQL Server Engine, high availability, and disaster recovery. Previously he was a Senior Active Directory Architect and designed the company's worldwide Active Directory infrastructure.  He serves on the Board of Directors for the North Texas SQL Server User Group and is President of the PASS Performance Virtual Chapter. He is also a PASS Regional Mentor and holds the following certifications: MCP MCSA MCSE MCDBA MCTS MCITP.

Title: TSQL:  From Basics to Advanced

Speaker: Michael C. Byrd

TSQL: From Basics to Advanced

This course will concentrate on the TSQL SELECT statement; its syntax, usage, and performance tuning. It will take the attendee from a basic level of understanding to being able to write, understand, and performance tune complex queries.

Classroom sessions will be about one-fourth lecture and three-fourths demos. All materials will be available for download. 

The course material is derived from Itzak Ben-Gan’s book: Microsoft SQL Server 2012 T-SQL Fundamentals (about $30 from Amazon). Although not required, the attendee is advised to purchase and read the book prior to taking the course.

While the course will primarily concentrate on the SELECT statement, the other data modification statements (UPDATE, DELETE, INSERT) will also be covered.

1. Completely understand TSQL Select statement syntax and usage.

2. Able to analyze and script all table joins (inner and outer)

3. Able to analyze, script, and understand TSQL performance tuning

8:00-8:50: SELECT syntax, processing order, intro to Management Studio

9:00-9:50: Single Table Queries, Top filter, windows functions, Predicates, Operators

10:00-10:50: JOINs, Computed columns, indexes,

11:00-11:50: Subqueries, Table expressions, view performance

11:50-12:30: Lunch (may also be brown bag lunch to catch up if behind, questions)

12:30-1:30: SELECT review (all demos)

1:30-2:30: SET Operators, DML TSQL (Insert, Delete, Update)

2:40-3:40: Variables, Batches, temporary tables, stored procedures, functions

3:50-5:00: Performance tools, sample performance tuning (demos)

* Topics may blur across times depending on class attendees progression

While not necessary, trainees are encouraged to bring laptops with SQL Server 2014 or 2012 Developer Edition (available from Amazon.com/Pricewatch.com for about $50) installed. (Actually 2008R2 Developer Edition will work. This course will not cover or touch on SQL Agent, SSIS, SSAS, or Reporting Services.

Mike Byrd Bio:

Email: mbyrd@nctv.com

Twitter: @ByrdNest_TX

Web Site: www.TSQLMentoring.com

LinkedIn profile: https://www.linkedin.com/profile/preview?locale=en_US&trk=prof-0-sb-preview-primary-button

Summary: Former rocket scientist and USAF Fighter Pilot -- now a SQL Server geek. Past opportunities include free-lance technical writer for PC Magazine, Senior Software Manager for government sponsored munitions effectiveness committee, and IT Director for Texas Windstorm Insurance Association (9 years), Current in SQL Server 2014, 2012, 2008R2, and 2005. Presented many SQL Saturday presentations at Austin, Baton Rouge, Denver, Dallas, Houston, Oklahoma City, South Florida and Gothenburg, Sweden. Currently consulting for several Austin based software companies and free-lance writing a series of performance articles for SolarWinds (Confio) (http://logicalread.solarwinds.com/?s=Byrd). Also working up new data architecture and implementation plan for Daughters of Republic of Texas system. Ideal job: sproc and query performance tuning or TSQL instruction.

Specialties: SQL Server performance tuning; database (OLTP & OLAP) architecture; trouble-shooting; teaching TSQL (Basic through Advanced).

Instruction: Have presented group TSQL classes for Newgistics, Austin, TX; Conexis, Irving, TX; and Mentor (Johnson & Johnson), Irving, TX, and individualized mentoring on a one-on-one basis.

Title: Implementing the Modern Data Warehouse

Speaker: Ginger Grant



Implementing the Modern Data Warehouse

The data stored in today's modern data warehouse is expected to do more than ever before. Users are looking for more than facts and measures, but the ability to empower users with data and to build analytics solutions that predict future outcomes. We'll examine the problems and solutions facing the modern data warehouse, and look at the internals to see what components are going to be needed to help provide this insight.

We'll look at how to balance data loading and computing processes with the needs for the platform. Looking at how to work with both structured and unstructured data and when to continue to leverage relational databases versus file-base solutions, such as Hadoop. From on-premise to cloud solutions, this session will examine how to select the right fit for the right solution. We’ll also discuss how to augment these solutions with Big Data and Cloud technologies, including HD Insight, Machine Learning and Data Factory and adding common analytic data sources such as social media. By the end of the day, you will understand what is possible in the modern data warehouse and how to architect solutions fitting your company.

Session Agenda

· Components of a Modern Data Warehouse

· The future of data acquisition, storage and processing

· New tools and concepts in Data integration

· Data Analysis features of a Modern Data Warehouse

· Visualization and Self-Service Analysis

· Analytics tools for providing answers to data questions for the business

· Modern Data Warehousing in your business, real world application

Bio - Ginger Grant

Ginger Grant has worked with the Microsoft BI stack for a many years for a number of different industries including transportation, education, insurance and healthcare, and along the way became certified as an MCTS in SQL Server 2008 and MSCA for SQL Server 2012.  As a consultant for Pragmatic Works, I enjoy applying current technology to provide data solutions for clients.  When not working I can be found speaking at number of events including SQL Saturdays, users groups, code camps and webinars, which led to being a finalist in the 2014 Red Gate Tribal Awards for New Community Voice.

To read what recent technical things interest me most recently, as well as my speaking history, check out my blog at desertislesql.com or reach out on Twitter @desertislesql









Title: Fast Track your Business Intelligence Projects with Biml



Speaker: David Stein

Fast Track your Business Intelligence Projects with Biml - Precon

You’re a business intelligence developer or manager trying to provide quality data in a timely, cost-effective manner. Perhaps you’ve considered Agile methodology, but found that the details of ETL, the plumbing if you will, makes iterative development all but impossible. In just one day, you’ll learn how to leverage Biml Business Intelligence Markup Language (Biml) to super charge your ETL efforts, alleviating the pain and tedium of data warehouse development. You will Learn:

  • How Biml makes Agile data warehouse development a reality.

  • Understand the basic syntax and rules of Biml, the important role of meta data, and how to choose the right scripting language for you.

  • Using BimlScript to dynamically create tables, views, and the SSIS packages to load them.

  • How to change your development mindset and spot re-usable patterns which are ideal for using Biml.

    As an added bonus, you’ll leave the precon with several production quality BimlScript frameworks and code samples to use in your own environments. None of these samples require special software or third party tools. These samples will enable you to:

  • Dynamically create and load an Operational Data Store with historical tracking.

  • Dynamically create and load an Enterprise Data Warehouse using Agile Methodology.

    But wait, theres more! To help you learn BimlScript faster and easier, Varigence has agreed to provide a free two month subscription to Mist Mist/BimlStudio to all attendees. This is a $500 value!

    The entire seminar is geared toward getting you up to speed on BimlScript so you can stop wasting your time repeating the same old patterns. Come see why Biml is changing the way we build data warehouses.

     

    Bio: 

    Data Warehouse Architect and Independent Consultant - has more than a decade of experience working in SQL Server Business Intelligence. He’s designed multi-terabyte, multi-tenant Data Warehouses from scratch as well as saved projects in serious jeopardy.

Title: Building Better SSIS Packages

Speaker: Tim Mitchell

Building Better SSIS Packages

Building packages in SQL Server Integration Services is easy. Building good packages takes a bit more effort. SQL Server Integration Services is deceptively easy to use. With a few hours of instruction or self-study, most data professionals can throw together an SSIS package. However, creating a process that simply runs successfully isn’t the endgame; enterprise-class ETL demands a higher standard. Specifically, well-designed packages will be:

· Reliable

· Resilient

· Reusable

· Maintainable

· Well performing

There’s nothing magical about building rock-solid SSIS packages, but it does take some discipline, experience, and a library of best practices. That is exactly the aim of this course: to demonstrate a set of proven practices that help frame the development of enterprise-ready SSIS packages.

In this full-day presentation, we will walk through each of these five facets of well-built packages, discussing and then demonstrating ways of applying these practices to design better SSIS packages. Among the topics for the day:

· Checking for problems even when the package executes successfully

· Building effective tests for your packages

· Error handling and prevention

· Being kind to your fellow developers by building  clear and easy-to-maintain packages

· Leave a trail of evidence: package logging done properly

· Avoiding performance bottlenecks: tips for making packages run faster

· Using the right tool for the job by integrating non-SSIS tools when appropriate

This course is designed for the beginner- to intermediate-level SSIS developer.

 

Title: Murder They Wrote

Speaker: Wayne Sheffield

Murder They Wrote

The Target Audience and what we cover

If you are a DBA, a database developer, or an application developer that connects to a SQL Server back-end database, then this session is for you. If you are experiencing performance issues, then this session is most definitely for you. If you’re looking for a fun way to spend a day and to get some #sqllearning, then this session is for you. This presentation is geared around decisions that have been made in the development life cycle and the effects that those decisions have on SQL Server, based upon the things that we have seen done to the instances that we have managed (and, in my case, some of the things that I did back when I was a developer and didn’t know better). With no topic being off limits, we also cover critical issues that just aren’t that great to do – and that still happen way too often. And demos? Oh yeah! You know that I like to show things instead of just telling them, so in that spirit, this is a demo-heavy presentation. We don’t just tell you that something is bad and something else is good… we’ll show it to you. We might even blow something up!

So, come on out for a day of learning, fun, and SQL camaraderie. I look forward to spending a day chatting with the attendees, and in helping them to overcome some of their issues.

The Official Session Abstract

Join Microsoft Certified Master Wayne Sheffield as he examines numerous crazy implementations he has seen over the years, and how these implementations can be murder on SQL Server. No topic is off limits as they cover the effects of these crazy implementations from performance to security, and how the “Default Blame Acceptors” (DBAs) can use alternatives to keep the developers, DBAs, bosses and even the end-users happy.

Course Objectives

1.     Recognize practices that are performance pitfalls

2.     Learn how to Remedy the performance pitfalls

3.     Recognize practices that are security pitfalls

4.     Learn how to Remedy the security pitfalls

5.     Demos, demos and even more demos – (and the scripts to demonstrate both the pitfalls and their remedies will be provided to attendees!)

6.     Have fun and discuss

7.     We might blow up a database. 

 

There will be a nice mix of real world examples (and some contrived ones), all of which demonstrate a good and useful point.

 

Wayne Sheffield, a Microsoft Certified Master in SQL Server, started working with xBase databases in the late 80′s. With over 25 years in IT, he has worked with SQL Server (since 6.5 in the late 90′s) in various dev/admin roles, with an emphasis in performance tuning. He is the author of several articles at www.sqlservercentral.com, a co-author of SQL Server T-SQL Recipes, and enjoys sharing his knowledge by presenting at SQL PASS events and blogging at http://blog.waynesheffield.com/wayne.

 

Title: A Masters Passport to Extended Events

Speaker: Jason Brimhall

As is commonly the case, all good things come to an end.  And now is as good a time as any for the use of SQL Trace and Profiler to come to an end.  Let’s face it, Trace was a good tool and had some wonderful uses.  Profiler for that matter was a good tool and was useful at times.

It is time to let those old tools retire gracefully and move into the world of XE.  This full day workshop will provide you the means to let Profiler and Trace be retired from your toolset as you discover all that XE has to offer.

This full day session on Extended Events will help prepare you to put this tool to immediate use as you walk back to your daily duties.  This workshop will teach you about Extended Events starting with the basics and moving through how to create XE sessions that will get the right data for you, while doing so with minimal impact.

You will be exposed to advanced troubleshooting techniques as I work through complex issues that are made easier through the use of XE.  Take advantage of this opportunity to dive into the world of Extended Events and learn how you can make best use of this tool in your SQL 2008+ environment.

Course Objectives

1.     Build a knowledge base for Extended Events

2.     Become familiar with the tools for Extended Events

3.     Become familiar with uses for Extended Events

4.     Get acquainted with troubleshooting scenarios for Extended Events

5.     Begin to put Extended Events to practical use

6.     Return to work with enough information to eradicate Profiler from the environment 

 

Jason Brimhall has 10+ yrs experience and has worked with SQL Server from 6.5 through SQL 2012. He has experience in performance tuning, high transaction environments, as well as large environments. Jason also has 18 years experience in IT working with the hardware, OS, network and even the plunger (ask him sometime about that). He is currently a Consultant and a Microsoft Certified Master(MCM).

 

Title: 10 things I Wish I know when changed skill sets from MS Access to SQL Server

Speaker: Stewart Rogers

Beginner … 10 things I wish I know when changed skill sets from MS Access to SQL server. This talk is an introductory talk for the new users to SQL coming from MS Access world.

 

Title: To the next level: mastering SQL Server performance

Speaker: Daniel Janik

To the next level: mastering SQL Server performance

Abstract:

This full-day course is a deep dive into monitoring and troubleshooting query performance in SQL Server 2008 and beyond, including SQL Server 2016! The focus of this course is to give developers and DBAs the tools necessary to quickly resolve performance problems plaguing the instance. Finding root cause and getting all team members on the same page is an art that requires skills in all aspects of the Microsoft stack. This session is intended to help you understand each aspect of the SQL Server instance and dig deep down into focused, level 500 detail to quickly resolve performance problems and get each team member on your page.

Bio: Daniel Janik has been supporting SQL Server for 18 years. Six of those years were at Microsoft Corporation supporting SQL Server as a Senior Premier Field Engineer (PFE) where he supported over 287 different clients with both reactive and proactive database needs. Daniel has presented at many community events and SQL Saturdays.