SQLSaturday #539 - Sioux Falls 2016
My Top 10 Favorite Replication Tricks
Event Date: 23-07-2016 - Session time: 08:20:00 - Track: Enterprise Database Administration Deployment
Speaker: Marcus Hopfinger
Title: My Top 10 Favorite Replication Tricks
Replication can be intimidating and a bit confusing. I will show you my ten favorite tricks for working with replication that will show you how you can replicate from multiple sources to multiple destinations, chose the appropriate method of replication and, if desired, put the data in a different schema with a different collation at the destination than it was at the source. I will also cover various methods of managing your publications, agent properties and distribution schedules. This presentation assumes a basic knowledge of configuring distribution and creating a basic publication and subscription.
Mastering queries into XML data.
Event Date: 23-07-2016 - Session time: 08:20:00 - Track: Application Database Development
Speaker: Rick Bielawski
Title: Mastering queries into XML data.
If you've never queried an XML column you definitely want to attend. While targeted to beginners this isn't a "Just do this" session. After this session I'd like you to truly understand even subtle nuances of basic query syntax. Seasoned veterans stand a fair chance of learning something new.
Build on-the-fly reporting with Dynamic SQL
Event Date: 23-07-2016 - Session time: 08:20:00 - Track: BI Platform Architecture, Development Administration
Speaker: Dorothy Ling
Title: Build on-the-fly reporting with Dynamic SQL
Using Dynamic SQL, grouping windowing techniques, and cursors, one can dynamically build a report with specifications that are stored in tables. In this session, we will look at each of the techniques used in building an employee report card that is completely driven by formula and scoring calculations stored in the database. We will also look at how everything is put together to create a truly dynamic report that can be used, created and maintained by different teams.
Getting Started with Extended Events
Event Date: 23-07-2016 - Session time: 09:45:00 - Track: Enterprise Database Administration Deployment
Speaker: Andy Galbraith
Title: Getting Started with Extended Events
Few subjects in Microsoft SQL Server inspire the same amount of Fear, Uncertainty, and Doubt (FUD) as Extended Events. Many DBA's continue to use Profiler and SQL Trace even though they have been deprecated for years. Why is this?
Extended Events started out in SQL Server 2008 with no user interface and only a few voices in the community documenting the features as they found them. Since then it has blossomed into a full feature of SQL Server and an amazingly low-impact replacement for Profiler and Trace.
Come learn how to get started - the basics of sessions, events, actions, targets, packages, and more. We will look at some base scenarios where Extended Events can be very useful as well as considering a few gotchas along the way. You may never go back to Profiler again!
SSIS Performance Tuning with Biml
Event Date: 23-07-2016 - Session time: 09:45:00 - Track: BI Platform Architecture, Development Administration
Speaker: David Stein
Title: SSIS Performance Tuning with Biml
You may heard that Biml can automate ETL loads, but what if your data warehouse is several terabytes with a tight load window. In this demo focused presentation, we’ll discuss how to use BimlScript to dynamically create multiple SSIS packages for a data warehouse ETL load, each following a different best practice design pattern. The included framework then automatically executes each package repeatedly, records the results for analysis, and determine which package version should be used in production.
The only way to be sure that your SSIS packages are performing optimally is to test them against multiple patterns. Make your life easier and let BimlScript do the work for you.
Tips and Tricks for Performance Troubleshooting
Event Date: 23-07-2016 - Session time: 09:45:00 - Track: Application Database Development
Speaker: Phillip Podhradsky
Title: Tips and Tricks for Performance Troubleshooting
“The application is too slow”...”reports are taking too long to load”… “my queries are running slow”. These are complaints we have all heard from our end users, and as we know, it is always the database that is the issue. In this session we will talk about different things to look for when assessing performance of queries. We will examine execution plans at a high level to see where problems may lie and look at some few different “gotchas” that can affect query performance. We will talk about indexes and statistics – what are they, what to index, and how they can help query performance. Finally, we will cover a few ways to help performance while using encryption.
Implementing security inside SQL Server
Event Date: 23-07-2016 - Session time: 11:05:00 - Track: Enterprise Database Administration Deployment
Speaker: Rick Bielawski
Title: Implementing security inside SQL Server
Having a good understanding of what security features are available and what configurations are considered best practice can be crucial to your company's data security. Learn to view security differently and how key builtin security features can be leveraged to make your systems both more maintainable and more secure. This is very much a HOW TO session, both in terms of how to decide what feature combinations work best for your environment and how to implement those features.
SQL Server Foreign Keys – De-mystifying the Rest of the Story
Event Date: 23-07-2016 - Session time: 11:05:00 - Track: Application Database Development
Speaker: Mike Byrd
Title: SQL Server Foreign Keys – De-mystifying the Rest of the Story
Foreign key constraints are the ultimate Catch-22. They are necessary to ensure the relationship integrity between a parent table and its child tables. However, they may also have a significant downside effect on insert, update, and delete performance. In this session we will describe the concept of Foreign Key constraints and how they maintain data integrity when properly maintained. You will learn their syntax for creating, deleting, disabling, and re-enabling them. You will also learn the possible side effects of disabling and re-enabling Foreign Keys for data modification. We will examine the two most important Foreign Key attributes: is_not_trusted and is_disabled and their effect on query performance. All of these characteristics will be demonstrated with many script examples. Attendee should have some knowledge of Management Studio and the SELECT statement.
Agile Data Warehouse and ETL Design with Biml
Event Date: 23-07-2016 - Session time: 13:15:00 - Track: BI Platform Architecture, Development Administration
Speaker: David Stein
Title: Agile Data Warehouse and ETL Design with Biml
One approach to make SQL deployments repeatable, scalable semi-automated
Event Date: 23-07-2016 - Session time: 13:15:00 - Track: Application Database Development
Speaker: Matthew Dobson
Title: One approach to make SQL deployments repeatable, scalable semi-automated
SQL deployments case study using Powershell, SQL, Subversion Reporting Services to create a repeatable process able to scale for numerous developers DBAs (30+) developing simultaneously for multiple releases across multiple development test environments.
Availability Groups: Monitoring and Alerting
Event Date: 23-07-2016 - Session time: 13:15:00 - Track: Enterprise Database Administration Deployment
Speaker: Shawn Meyers
Title: Availability Groups: Monitoring and Alerting
So you have your availability groups configured and everything reports green. How do you measure SLA compliance? Do you know when replication is delayed? Did you know it failed over last night?
This session will answer all these questions and more, and will show you what should you be looking at to ensure no surprises...
We will look at the DMVs, extended event sessions, perfmon counters, SQL agent jobs and policies to show what to monitor in your AG and how to setup alerts so you know when to take action.
Building Stronger Relationships Between Us... "Data People" and Them... "End Users"
Event Date: 23-07-2016 - Session time: 14:35:00 - Track: Professional Development
Speaker: Joshua Warne
Title: Building Stronger Relationships Between Us... "Data People" and Them... "End Users"
The role of data and IT professionals is evolving from simply responding to requests about business to one of helping shape the business strategy. The integration of these professionals into the strategy process is a transformation that presents communication challenges and requires more visibility by people typically known to be introverts. In this session we will discuss some of the barriers causing relationship pains as well as some techniques and strategies to begin building stronger relationships with the managers and end users so data can thrive and customer satisfaction can proliferate.
Event Date: 23-07-2016 - Session time: 14:35:00 - Track: Enterprise Database Administration Deployment
Speaker: Cecil Spivey
Everybody loves a free lunch. Come to this session to learn about all the SQL Server freebees
MSSQL is coming to Linux - Are You Ready?
Event Date: 23-07-2016 - Session time: 14:35:00 - Track: Application Database Development
Speaker: Nem Schlecht
Title: MSSQL is coming to Linux - Are You Ready?
MSSQL is coming to Linux next year. In this session, I will show you how to access MSSQL from UNIX Operating Systems (Linux, Mac OSX, Cygwin, and others) today. If you're considering possibly using MSSQL on Linux and want to get started on migrating your application today, this is the session for you. Yes, you can use MSSQL as your database today for your UNIX application. I'll also show you how you easily migrate existing MySQL or SQLite databases, popular in the UNIX world, over to MSSQL using Linked Servers and the openquery() call.
Tackling Technical Blogging
Event Date: 23-07-2016 - Session time: 15:55:00 - Track: Professional Development
Speaker: Mike Hays
Title: Tackling Technical Blogging
Ever thought of writing a technical blog? This session will cover the Ws (who, what, when, where, why, how) of getting a technical blog started. Answer the question of how it can be done for free. Discuss why pay for your own domain name. Explain how to be on the first page of a search engine. Learn how to determine the time commitment. And finally determine topics to write about.
Get Your Optimizer to Give up All Its Secrets
Event Date: 23-07-2016 - Session time: 15:55:00 - Track: Enterprise Database Administration Deployment
Speaker: Brian Hansen
Title: Get Your Optimizer to Give up All Its Secrets
SS2014/2016 In-Memory Tables: Formula 1 Performance Unleashed?
Event Date: 23-07-2016 - Session time: 15:55:00 - Track: Application Database Development
Speaker: Mike Byrd
Title: SS2014/2016 In-Memory Tables: Formula 1 Performance Unleashed?
Query performance is what it is all about. SQL Server 2014 now has the new in-memory functionality that takes a completely different approach to data access and manipulation. Google “In-Memory Tables” and you’ll get a myriad of results from white papers, blogs, webinars, etc. This session will consolidate the relevant information from all these sources so the production DBA/Developer will be able to understand, create, use, maintain, and trouble-shoot in-memory table functionality. This session will also examine several demo scripts showing performance gains using the in-memory tables over equivalent disk based tables.
This session is all about when and how to apply in-memory tables in a production environment.
After this session you will
- Understand what in-memory tables are and how to create and maintain them including their limitations
- Be able to identify performance gains over equivalent disk back tables
- Comprehend the new data monitoring needed for in-memory table