SQLSaturday #206 - Madison 2013 4/6/2013 12:00:00 AM (GMT-06:00) Central Time (US and Canada) SQLSaturday is a training event for SQL Server professionals and those wanting to learn about SQL Server. #sqlsat206 Madison Area Technical College - Truax Campus 3550 Anderson St Madison WI 53704 http://www.sqlsaturday.com/images/sqlsaturday_gb_icon.png http://www.sqlsaturday.com/images/sqlsaturday_gb_image.png Conference Information general True 1 default Maps maps False 2 default Schedule of Events schedule True 3 default Sponsors List sponsorslist True 4 default Twitter twitter True 5 default Todo List todo False 6 default My Schedule myschedule True 7 default Feedback feedback False 8 default Key Events keyevents False 9 default Share share True 10 default 2914 Tushaus Computer Services Gold Sponsor http://www.tushaus.com http://www.tushaus.com/Images/TushausLogo.png 60 170 3011 Rubix Consulting, LLC Gold Sponsor http://www.rubixconsulting.net http://rubixconsulting.net/Portals/0/Rubix_Images/rubix_rgb_image_on_top_no_line_with_url_3.gif 60 170 3149 Tegile Systems Gold Sponsor http://www.tegile.com http://www.sqlsaturday.com/files/4bea2224-5b2e-4510-83a8-7f11e6685b15.jpg 60 170 2897 Confio Software Silver Sponsor http://www.confio.com http://www.confio.com/images/assets/confio_logo.jpg 60 170 3056 Trek Bicycle Corporation Trek http://www.trekbikes.com http://www.trekbikes.com/media/images/branding/trek_brandmark_vertical_on_dark.png 40 100 3212 Pluralsight Swag http://pluralsight.com http://s.pluralsight.com/mn/img/logo/pluralsight-fullcolor-175x39-v1.png 40 100 3227 Red Gate Swag http://red-gate.com http://www.red-gate.com/assets/images/common/logo.png 40 100 3253 xsql Software Swag http://www.xsql.com/ http://www.xsql.com/images/xSQLSoftware.png 40 100 12502 SQLSaturday 206 Room 350 Room 350 4/6/2013 12:00:00 PM 4/6/2013 12:45:00 PM 12502 SQLSaturday 206 Room 321 Room 321 4/6/2013 12:00:00 PM 4/6/2013 12:45:00 PM 12502 SQLSaturday 206 Room 322 Room 322 4/6/2013 12:00:00 PM 4/6/2013 12:45:00 PM 12502 SQLSaturday 206 Room 142A Room 142A 4/6/2013 12:00:00 PM 4/6/2013 12:45:00 PM 12502 SQLSaturday 206 Room 142B Room 142B 4/6/2013 12:00:00 PM 4/6/2013 12:45:00 PM 12502 SQLSaturday 206 Room 384AB Room 384AB 4/6/2013 12:00:00 PM 4/6/2013 12:45:00 PM 12502 SQLSaturday 206 Room 380 Room 380 4/6/2013 12:00:00 PM 4/6/2013 12:45:00 PM 12503 SQLSaturday 206 Room 380 Room 380 4/6/2013 7:30:00 AM 4/6/2013 9:15:00 AM 12503 SQLSaturday 206 Room 384AB Room 384AB 4/6/2013 7:30:00 AM 4/6/2013 9:15:00 AM 12503 SQLSaturday 206 Room 142B Room 142B 4/6/2013 7:30:00 AM 4/6/2013 9:15:00 AM 12503 SQLSaturday 206 Room 142A Room 142A 4/6/2013 7:30:00 AM 4/6/2013 9:15:00 AM 12503 SQLSaturday 206 Room 322 Room 322 4/6/2013 7:30:00 AM 4/6/2013 9:15:00 AM 12503 SQLSaturday 206 Room 321 Room 321 4/6/2013 7:30:00 AM 4/6/2013 9:15:00 AM 12503 SQLSaturday 206 Room 350 Room 350 4/6/2013 7:30:00 AM 4/6/2013 9:15:00 AM 12504 SQLSaturday 206 Room 350 Room 350 4/6/2013 5:30:00 PM 4/6/2013 6:00:00 PM 12504 SQLSaturday 206 Room 321 Room 321 4/6/2013 5:30:00 PM 4/6/2013 6:00:00 PM 12504 SQLSaturday 206 Room 322 Room 322 4/6/2013 5:30:00 PM 4/6/2013 6:00:00 PM 12504 SQLSaturday 206 Room 142A Room 142A 4/6/2013 5:30:00 PM 4/6/2013 6:00:00 PM 12504 SQLSaturday 206 Room 142B Room 142B 4/6/2013 5:30:00 PM 4/6/2013 6:00:00 PM 12504 SQLSaturday 206 Room 384AB Room 384AB 4/6/2013 5:30:00 PM 4/6/2013 6:00:00 PM 12504 SQLSaturday 206 Room 380 Room 380 4/6/2013 5:30:00 PM 4/6/2013 6:00:00 PM 12540 Jeff Prom Room 380 Room 380 Does your company have similar data in multiple locations? Not really sure which set of data is accurate? With MDS, Microsoft gives you the ability to have one accurate source of non-transactional data to use with your applications or send to other databases and ETL processes. Leverage data stewards to help manage the data repository to ensure it is up-to-date and accurate. In this session we will cover MDS from start to finish including installation, creating models/entities, working with data, using the Excel add-in, security, hierarchies, views, versions, and business rules. After this session, you should have a good understanding of what MDS does, how to use it, and how to get started with your own MDS project. 4/6/2013 1:00:00 PM 4/6/2013 2:15:00 PM 12569 Janis Griffin Room 321 Room 321 SQL Server Extended Events can be used to gather a wide range of performance information, and in troubleshooting in SQL Server. Extended Events use less system resources and provide better information than SQL Profiler and they allow for correlation of SQL Server event data along side of OS or Application event data. This presentation will explore what Extended Events are, how to set them up and use them in several case studies. 4/6/2013 10:45:00 AM 4/6/2013 12:00:00 PM 12573 Gill Rowley Room 142B Room 142B Are you an accidental DBA trying to find your way through your new role? Are you an experienced DBA moving to a new position, team, or major application? This presentation is for you! In this presentation, we will review best practices for what to do when you're a brand new DBA. You will learn what to look for when you walk into a new environment, as well as pitfalls to avoid. 4/6/2013 10:45:00 AM 4/6/2013 12:00:00 PM 12574 Merrill Aldrich Room 142B Room 142B If you face managing over a hundred, or perhaps thousands, of databases, automation, standards and a 'policy-based' mindset are critical. In this session we'll do a walk-through of a rules-based backup and index maintenance solution suitable for zillions of smaller databases, and talk at a high level about features and techniques for multi-server management including monitoring, performance data collection, jobs automation, PowerShell and PBM. 4/6/2013 2:30:00 PM 4/6/2013 3:45:00 PM 12576 Dave DuVarney Room 322 Room 322 Many business intelligence teams work within traditional software development lifecycles. These cycles follow a very waterfall based approach. Gather all your requirements, do a design, do some development, etc. Several months later, hopefully there is something for the business to get their hands on. Analytical requirements are significantly more fluid and require a more agile development approach. In this session we will look at how to setup an Agile BI team. It will cover roles and meeting cadence in an Agile team, tools used to track user stories and real world examples of how larger analytical needs are broken into small iterations. 4/6/2013 4:00:00 PM 4/6/2013 5:15:00 PM 12590 Bob Pusateri Room 350 Room 350 Backups: You can't live without them but want them to complete as quickly as possible. The faster they're done, the sooner your server's CPU and IO resources can get back to their normal job of servicing queries. Queries aren't the only part of SQL Server you can tune for maximum performance - backups fall under this category as well. This session will discuss how backups work and how you can capitalize on this knowledge to ensure they complete as quickly as possible. Cutting your backup (and restore) times by 50% or more is not out of the question when using these methods. 4/6/2013 4:00:00 PM 4/6/2013 5:15:00 PM 12645 Kalen Delaney Room 321 Room 321 There is a common misconception that SIMPLE Recovery means no logging, and this is a very dangerous myth to propagate. SQL Server does log database changes in SIMPLE Recovery, but some (not all) operations are minimally logged. In this presentation, I'll tell you what minimal logging means in conjunction with each of the three different recovery models. In addition, I’ll describe the benefits and dangers of the SIMPLE Recovery Model. 4/6/2013 9:15:00 AM 4/6/2013 10:30:00 AM 12657 ERIC SELJE Room 322 Room 322 How can you be certain the 'little change' you're about to make to that stored procedure isn't going to take down your whole company? Real developers use a technique called 'Unit Testing' to minimize that risk, a concept that's also available to you. With unit testing you can quick ascertain whether the the changes to functions, stored procedures, and even other objects in your database had a negative unforeseen impact. In this session we'll walk through the basics of what unit testing is, how to set up a simple Unit Testing project in SSMS, how we can tell whether something has negatively changed in our functions or procedures. We'll also enumerate any 3rd party tools available to help us with Unit Testing our databases. 4/6/2013 2:30:00 PM 4/6/2013 3:45:00 PM 12719 Jim Dorame Room 384AB Room 384AB With PowerShell and SQLPSX your DBA life can be a lot simpler. SMO is powerful, but can be difficult to use. Enter SQLPSX. Chad Miller put together a set of PowerShell functions that streamline the use of SMO, easing management of a multi-server environment. Come learn when and how the addition of PowerShell and SQLPSX can benefit you. 4/6/2013 4:00:00 PM 4/6/2013 5:15:00 PM 12729 Jennifer Salvo Room 380 Room 380 Prior to the release of SQL Server 2012, Business Intelligence Developers had a single option when creating cubes in Analysis Services, the Multidimensional Model. Now, Microsoft has added the Tabular Model to Analysis Services which opens up many new and exciting possibilities. In this session, we'll discuss the features and benefits of the Tabular Model and draw comparisons to the Multidimensional Model. We'll also review scenarios when implementing a Tabular cube may be a better choice than Multidimensional. The presentation will conclude with a demo of basic Tabular cube functionality. 4/6/2013 2:30:00 PM 4/6/2013 3:45:00 PM 12730 Argenis Fernandez Room 321 Room 321 Follow the life of an INSERT statement from the moment it is issued by the Client, through reception, parsing and execution by the SQL Server engine, until the operation is completed and a message is sent back. This is an Internals session where we will touch on all the different components that comprise the SQL Server Database Engine involved in handling a single INSERT statement. Watch structures on memory and disk get updated, and geek out on details! 4/6/2013 4:00:00 PM 4/6/2013 5:15:00 PM 12738 Leonard Murphy Room 380 Room 380 Power View is a visual data exploration tool that comes built into Excel 2013. With it you can build charts that seamlessly interact with each other and quickly map your data using Bing maps. In this session, you will learn where Power View shines and where it doesn't so you can maximize the value you get out of it in a minimum amount of time. 4/6/2013 4:00:00 PM 4/6/2013 5:15:00 PM 12866 Josh Fennessy Room 380 Room 380 With the release of SQL 2012, Analysis Services (SSAS) has seen some major changes. An entire new method of modelling analytic data has emerged and it's called Tabular Mode Analysis Services. In addition to that, a new semantic layer has been added to the tool, the Business Intelligence Semantic Model (BISM), which will help to bring the two modes -- Multidimensional and Tabular -- into one level playing arena. In this session, attendees will gain understanding of what Multidimensional and Tabular modelling IS, as well as how the BISM layer helps to translate user requests to ensure no confusion is made. Attendees will also learn how to BUILD a Tabular model, and will help design a solution live during the presentation. 4/6/2013 9:15:00 AM 4/6/2013 10:30:00 AM 12910 derek dai Room 384AB Room 384AB This session will introduce the new xVelocity Memory-Optimized Columnstore Index feature of SQL 2012. This presentation will describe what a column store index is, how this feature is used to store data by columns instead of by rows and compare the advantages and disadvantages between the two storage methods. Live demos will explore how to create columnstore indexes, when to create them, best practices for using them and typical data warehouse scenarios that can leverage the columnstore index to drastically speed up data warehouse queries, in many cases by a factor of 10 to 100, so you are equipped to start implementing this feature into your database environment. 4/6/2013 10:45:00 AM 4/6/2013 12:00:00 PM 12950 Frank Gill Room 142B Room 142B Without the transaction log, SQL Server would have no way of maintaining a consistent and durable view of its data. It would be pandaemonium!!! Think of the Stay-Puft Marshmallow Man! Thankfully the transaction log is there and we can all breathe easier. In this session, you will learn how SQL Server uses the transaction log to maintain the durability and consistency of its data, along with methods for reading the transaction log and identifying active transactions. 4/6/2013 9:15:00 AM 4/6/2013 10:30:00 AM 13044 Tim Ford Room 142A Room 142A I've been telecommuting in some capacity or another since before I was a DBA. For 13 years I've been able to be a productive, responsive, integral and valuable asset to my company without maintaining a physical presence within the confines of a corporate office. Let's have a conversation and discuss what has (and has not) worked for me over the years. Let's discuss the traits of a good telecommuter as well as those attributes that may not make this the right arrangement for everyone. We will even discuss what my Managers have done over the years to not only acommodate telecommuting, but to embrace and succeed from having staff that work remotely! This session is aimed at staff and management alike. 4/6/2013 9:15:00 AM 4/6/2013 10:30:00 AM 13045 Tim Ford Room 350 Room 350 Confronted with the highest IO of any Windows server in our environment one intrepid DBA was able to reduce IO, space consumption, and blocking to new lows. This is a case study, based upon actual events where the names of the innocent as well as the guilty are changed to protect the DBA. Topics covered: DMVs, online operations, Filegroups, Page Compression, and disk formatting best practices. 4/6/2013 1:00:00 PM 4/6/2013 2:15:00 PM 13070 Farouq Abukhamireh Room 380 Room 380 An introduction to Business (BI) Intelligence, describing common BI scenarios, current trends in BI, and the typical roles that are involved in creating a BI solution. It also introduces the Microsoft BI platform and describes the roles Microsoft SQL Server 2012 and Microsoft SharePoint 2010 play in Microsoft BI solutions. 4/6/2013 10:45:00 AM 4/6/2013 12:00:00 PM 13142 Neil Hambly Room 384AB Room 384AB We all know that ‘Indexing’ is KING when it comes to achieving high levels of performance in SQL Server. When Indexing also combines 2 of the Enterprise features: Partitioning Compression, we can often see substantial gains. Learn how to identify those objects that benefit greatly from being Partitioned or Compressed, OR combining both of these features to even greater effect. Using Demos to illustrate the performance gains with real-world examples, Take away advanced scripts for use in your own environments. 4/6/2013 1:00:00 PM 4/6/2013 2:15:00 PM 13161 Jason Strate Room 321 Room 321 Execution plans are stored after execution in the plan cache. This metadata about how queries are executed can provide insight into how your SQL Server environment is functioning. By using XQuery to browse and search the plan cache you can find potential performance issues and opportunities to tune your queries. This information can be used to help reduce issues related to parallelism, shift queries from using scans to using seek operations, or discover exactly which queries are using what indexes. All of this and more is readily available through the plan cache. In this session we will explore the plan cache and start you on the road to discovery. 4/6/2013 1:00:00 PM 4/6/2013 2:15:00 PM 13216 Ken Stuber Room 142B Room 142B In 2007, Microsoft introduced Data Dude for Visual Studio 2005 to help bring the processes, controls and tools for code management to Database Development that Application developers had been enjoying for ages. It had its problems, but it mostly worked and was a welcome step in the right direction. Many versions later we have SQL Server Data Tools for Visual Studio 2012 (and 2010). A more mature product that finally brings database development up to par with traditional application development and has many features data geeks, like ourselves, will truly love. This talk will provide an overview of SSDT’s features and show you how to get started using it to save time, implement better code controls and be a better DBA or database developer. 4/6/2013 4:00:00 PM 4/6/2013 5:15:00 PM 13299 Michael DeFehr Room 350 Room 350 Deadlocks can be very tricky, unpredictable and generally nasty. Is all hope lost? This demo-intensive session will explore several types of deadlocks and give you some insight into how to track them down and prevent them. We wil start with a quick review of locking and blocking concepts, then take a deep-dive into deadlock demos. 4/6/2013 9:15:00 AM 4/6/2013 10:30:00 AM 13331 Michael Steineke Room 350 Room 350 Enhancements in Windows Server 2012 that give you more fexability and higher uptime with SQL Server 2012. Including Quorum models, SQL over SMB, Large SQL in HyperV. 4/6/2013 10:45:00 AM 4/6/2013 12:00:00 PM 13348 Ted Krueger Room 142A Room 142A With SQL Server 2012 released and going strong, we’re all wanting the latest and greatest, high performing, SQL Engine sitting on our servers. How we do that is another question. How do you get a weekend of downtime? Do you like convincing the business you need it? I don’t and that is why we’re going to look at methods to upgrade or migrate SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2 up to SQL Server 2012 with as little downtime as possible - minutes. In this session, we’ll see how mirroring, log shipping and even replication, can make us look like DBA Rock stars at upgrade time. 4/6/2013 10:45:00 AM 4/6/2013 12:00:00 PM 13350 Mike Donnelly Room 322 Room 322 With the release of SQL Server 2012 one of the biggest changes for SSIS Administration was the addition of the Integration Services Catalog and using parameters. The Catalog is the storage and logging location for SSIS 2012. Parameters are the new configurations in SSIS 2012 and once you start using them you will never go back. Package Variables, Configuration Strings, Package properties can all be configured using Parameters. You will learn how to create the catalog, use parameters to deploy packages through different environments and use the built in reporting to monitor packages as they run. 4/6/2013 10:45:00 AM 4/6/2013 12:00:00 PM 13358 Eddie Wuerch Room 142A Room 142A Have you ever thought about speaking at a user group or SQL Saturday, but weren't quite sure how to get started? If so, this session is for you! Technical speaking is loads of fun, great for your career, and you get to hang out with some terrific people. This session will provide the tools necessary to get your first session off the ground, and prep you to be a great speaker. Attend this session and you'll be able to: * Identify your topic and build a killer abstract for it, * Design and build the presentation, and * Prepare and deliver your first session 4/6/2013 4:00:00 PM 4/6/2013 5:15:00 PM 13480 Kyle Neier Room 350 Room 350 Just like your grandma tested spaghetti to tell if it was done, we'll be throwing several of the new T-SQL enhancements against the wall to see what's good and what falls off. We'll silence the lion's RaiseRror by throwing some meaty exceptions. We'll bring together the old lovers 'Coalesce' and '+' for one last jaunt before they are banished by the Concat function. We'll even open the window on some of the new aggregate and analytical functions as well as some other t-sql enhancements introduced in SQL 2012. 4/6/2013 2:30:00 PM 4/6/2013 3:45:00 PM 13485 Zach Mattson Room 384AB Room 384AB Why are we here? I have no idea, but I do know Powershell can probably help make it easier. This session will be entirely demos hitting on a variety of different areas in the SQL Server stack. Using plenty of SQL Server Management Objects(SMO), we will cover a variety of tasks for the data professional on both sides (OLTP / OLAP) of the house including working with SSAS, SQL Server , writing tests and automation. The theoretical will be ignored as well as any common decency towards mass produced beer. A Powerpoint free presentation. 4/6/2013 9:15:00 AM 4/6/2013 10:30:00 AM 13495 Paul Timmerman Room 384AB Room 384AB Until 2005, SQL Server was a very pessimistic RDBMS. However with the introduction of the Read Committed Snapshot database option and the new Snapshot Isolation level, that is no longer the case. After a high level look at all SQL Server isolation levels, we’ll spend the majority of this session discussing the implementation of optimistic concurrency control in SQL Server. We’ll look at the benefits and drawbacks of using an optimistic isolation level and discuss use cases where it may or may not be appropriate. Come learn how to put one of the more powerful, but least understood, aspects of SQL Server to work for you! 4/6/2013 2:30:00 PM 4/6/2013 3:45:00 PM 13504 Ed Leighton-Dick Room 142A Room 142A SQL Saturday is a great place to learn more about SQL Server. But did you know that it's just the tip of the iceberg? There are a number of places you can learn about SQL Server and just as many ways to share what you've learned with others. Most don't even require a lot of time or money. The SQL Server community is vibrant and growing, and it needs your voice. Come to this session to find out how you can get involved! 4/6/2013 2:30:00 PM 4/6/2013 3:45:00 PM 13520 Allen White Room 321 Room 321 Windows Server 2008 introduced Server Core, the operating system without the Graphical User Interface. SQL Server 2012 is the first version of SQL Server to support Server Core. To gain the maximum efficiency from your servers you want to make use of Server Core and with PowerShell you can effectively manage SQL Server in that environment. This session will demonstrate how to manage SQL Server 2012 on Windows Server Core and provide scripts to help you perform the common tasks you need to perform on that platform. 4/6/2013 2:30:00 PM 4/6/2013 3:45:00 PM 13523 Wendy Pastrick Room 142B Room 142B SQL Server has so many options for data redundancy, how do you know when you should use replication, or log shipping or AlwaysOn, etc? We will use real world examples where data redundancy is needed and discuss which is the appropriate technology and why. This is more than just a high availability vs disaster recovery discussion. If you have ever been confused or overwhelmed by all of the options, they will be broken down and made clear in this session. 4/6/2013 1:00:00 PM 4/6/2013 2:15:00 PM 13541 Russ Loski Room 322 Room 322 Microsoft has provided some great tools for creating and editing SSIS packages. First Business Intelligence Development Studio (BIDS) then SQL Server Data Tools (SSDT). However, when creating an SSIS package you frequently repeat the same steps over and over. BIML is an XML markup language designed for representing the structure of an SSIS package. BIML Script takes that structure and generates an SSIS package. In this session we will use BIDS Helper to create a simple SSIS package using BIML. We will handle a couple SSIS scenarios with BIML and BIML Script. 4/6/2013 1:00:00 PM 4/6/2013 2:15:00 PM 13565 Brian Davis Room 322 Room 322 How many times have you started a new SSIS project and added the same components that you’ve added to almost every other package you’ve created? Components to handle logging and errors or even a standard workflow for loading data files. In this session I’ll show you how you can create standard templates that already contain any and all of the common components you desire. We’ll also look at how to implement and use these templates so that the next time you create a SSIS package you can spend your time on the work you actually need to do instead of implementing common components over and over again. 4/6/2013 9:15:00 AM 4/6/2013 10:30:00 AM 13702 Jes Borland Room 142A Room 142A Ted Krueger and Jes will be presenting this session. Consulting is a mystical career path that is often questioned: should I do it, can I do it, and why should I do it? Come join us for a discussion of the pros and cons to consulting with an emphasis on SQL Server and Microsoft Technologies. We'll field questions with real world experiences. Once you leave this open floor session, you'll be able to make better choices in your own professional development and how consulting may or may not effect it. 4/6/2013 1:00:00 PM 4/6/2013 2:15:00 PM