Friday, October 23, 2015
Full-day training offered the day before Oregon SQL Saturday
8:00 AM until 4:30 PM
University of Phoenix; 13221 Sw 68th Pkwy, Tigard, OR 97223
Planning Highly Available SQL Server Deployments in a Cloudy, Virtualized World
Allan Hirt & Max Myrick
T-SQL For Performance and Accuracy
Vern Rabe
Building Real BI Solutions with Power BI – hands-on workshop
Paul Turley
Planning Highly Available SQL Server Deployments in a Cloudy, Virtualized World
Allan Hirt & Max Myrick
DBAs and IT admins have been deploying SQL Server on physical hardware for years. We are moving into a world where most installations are mainly virtualized and companies are considering the public cloud as an alternative to on premise deployments whether they are physical or virtual. Virtualization and either hybrid or public cloud solutions add interesting new options for making SQL Server highly available beyond what is included in SQL Server itself. However, DBAs are often at a disadvantage since understanding the nuances and features of Hyper-V, VMware, or the public cloud has traditionally been a skillset left to others. DBAs should also have a working knowledge of these platforms to plan and deploy highly resilient SQL Server deployments in this new world order.
This preconference will help DBAs and IT admins understand how to plan and deploy highly available SQL Server architectures whether using Hyper-V, VMware, or the cloud. You will learn about important topics such as:
- Evaluating whether a physical, virtualized, hybrid, or public cloud solution is best for your needs
- Understanding when and how to utilize features like Live Migration or vMotion with traditional SQL Server availability options like clustered instances (FCIs) and availability groups (AGs) to create an optimal, highly resilient design for HA and DR
- Planning FCI and AG deployments in a virtual or cloud architecture, including hybrid architectures that span on premise and the public cloud
- Learning about new technologies in storage and networking that can improve SQL Server availability as well as performance
- Tips, tricks, and other best practices based on real world deployments
- Sign up today to provide yourself with a knowledge to help guide your company’s database transition from the physical world we all know today to a virtual one that meets your mission critical demands.
Module One – Physical, Virtual, and Cloud – Oh My!
With virtualization and public cloud supplanting physical deployments of SQL Server, everyone needs to know:
• A brief history and comparison of the popular players in the game such as Hyper-V and VMware and what that means in relation to SQL Server deployments
• The benefits of virtualization and the public cloud for SQL Server
• Survival tactics to transition from a physical to a software defined world
• The ways in which your job change and what will stay the same
Module Two – SQL Server High Availability Techniques – Past, Present, and the Cloudy Future
The traditional methods of making SQL Server highly available are still in play with virtualization and the public cloud, but there are some changes and also new availability options to consider. You will learn:
• Considerations for deploying FCIs and AGs in non-physical environments
• How to leverage virtualization-based availability options such as Live Migration and vMotion
• How to bridge the gap between what DBAs need and what non-SQL Server administrators want to do (platform versus SQL Server features)
• How to choose the best availability option for your architecture
Module Three – The Future Is Now
There are new technologies that can transform the way we deploy SQL Server not only for availability, but performance as well. Understanding and leveraging these new options can make you look like a hero. Topics will include:
• The software defined data center
• Remote Direct Memory Access (RDMA) and InfiniBand networking
• Scale Out File Server and SMB Direct
Module Four – SQL Server Availability Architectures
Solutions are more than just a feature like AGs or vMotion. This module will show how you design solutions by presenting traditional availability architectures and how they look in a virtual, public cloud, or hybrid deployment.
• How physical architectures compare to virtualized and public cloud deployments
• Implementation considerations for each architecture presented
• Lessons learned from SQLHA’s experience designing and deploying real mission critical solutions
Module Five – Top Keys to Success
The day will wrap up with the top tips, tricks, and best practices that you will need to do to ensure that you are successful in your availability architectures.
Register for this session
Bio
SQLHA founder, Cluster MVP, and SQL Server mission critical expert Allan Hirt is a consultant, published author, speaker, and trainer who has been using Microsoft SQL Server in various guises since 1992 since interning at Sybase in college. Based in the Boston, MA area, he travels all over the world to work with and train clients. Allan is currently working on the book Mission Critical SQL Server.
Max Myrick recently joined SQLHA after 15 years at Microsoft which included 5 years on SQL Server development where he ran the SQL Customer Lab and then the Sustained Engineering team. He was an initial member of Microsoft’s Mission Critical team where he worked directly with some of the largest SQL Server applications worldwide for the past 8 years. Max was a regular speaker at TechEd and TechReady as well as the creator of several new service offerings including the Monthly Hotfix Report.
T-SQL For Performance And Accuracy
Vern Rabe
It seems like there’s always a query or a stored procedure that takes way too long to execute. Don’t be quick to blame it on a missing index, or out of date statistics, or insufficient memory. Often a slow performing query can be drastically improved with a rewrite. This preconference will teach you how to write T-SQL with two goals in mind: Getting the results you expect (accuracy), and quickly (performance).
In this preconference we will start with some SQL Server internals, where you will learn how the optimizer determines the execution plan, which join algorithms should be used in which situations, and some of the right and wrong choices that the optimizer can make. We’ll discuss how statistics are key to the optimizer’s choices, how the plan cache is used (for good or bad), how to minimize blocking, and how the ubiquitous transaction log is a performance factor is just about everything.
After the internals you will learn how to use various tools to identify poorly performing queries and the reasons for their poor performance. We’ll dig deep into the execution plan, identifying some details of how the optimizer chose to execute a query. We’ll learn about tell-tale warnings, estimated vs. actual plans, and parallel execution threads. We’ll also learn where the graphical execution plan can mislead you.
Next we’ll get into specific of T-SQL queries, where we’ll discuss many of the query writing mistakes that can lead the optimizer down a bad path, and most importantly, how to rewrite those queries to give the optimizer the appropriate information to make good decisions. All while making sure the results are exactly what they should be.
Some of the topics covered:
- Graphical Execution Plans
- Statistics
- Cardinality estimator
- Plan cache
- Parallelism
- SARGABLE
- Implicit conversion
- OUTER APPLY
- UDFs
- NOLOCK
All attendees will receive a USB flash drive with a copy of the PowerPoint presentation and all of the demonstration SQL scripts. Although not necessary for the class, if you bring a laptop configured with a USB port, SQL Server 2014, AdventureWorks2014, and AdventureWorksDW2014, you can execute the scripts along with the presentation.
Bio
Vern Rabe is an independent SQL Server consultant and contract trainer in Portland, OR. He has attained MCSE, MCITP (both Administration and Development), and MCT certifications, among others. Vern has been working with databases for 23 years, and has worked with SQL Server since version 4.21a. He provides broad technical SQL Server skills gained from the mixture of academic and practical experiences acquired from his classroom instructing and varied consulting contracts.
Building Real Business Intelligence Solutions with Power BI Desktop – Hands-on Workshop
Paul Turley
This interactive workshop will teach attendees how to use Business Intelligence tools (formerly called Power Query, Power Pivot and Power View) to create a complete solution delivering dashboards, self-service and mobile BI. We will create a solution using the new Power BI Desktop designer. Mastering these tools will enable solutions to be used on the desktop by Data Analysts, for group collaboration in on-premises SharePoint, using the Excel Power* add-ins and in the Microsoft Cloud with Office 365 and the new Power BI cloud service. Attendees should have a basic knowledge of relational database concepts.
Computer Requirements
Bring your laptop. All attendees should install the free Power BI Desktop (available at PowerBi.com) and setup a free subscription at PowerBi.com before they attend. Note that during setup, you will be prompted to use a work email address rather than a free, web-based email service (like GMail, HotMail or Yahoo! Mail). Please setup your subscription before the workshop.
At the conclusion, attendees will know how to use Power Query to combine and transform data from different sources; Power Pivot to model data for reporting and discovery, and write calculations, KPIs and complex metrics. They will learn to use Power View (now Power BI reports) to create interactive visualizations and dashboards. Finally, attendees will learn to deliver a collaborative BI solution using the Microsoft Power BI hosted solution enabling natural language queries with Power BI Q&A, scheduled data refresh and queries using live, on-premises data.
BI Solution Components
Begin with a brief and high-level introduction to the components of traditional BI solutions to help attendees appreciate the usual scale and cost. Discuss the options to scale these items for small and large projects. Discuss the trade-offs for each and the value of centralized solutions, data quality and master data management. Discuss the challenges of designing, building and maintaining large-scale BI solutions.
Topics covered
• Universal Principles of Data Behavior
• Universal Data Problems
• Universal Visualization Principles
• Interactive Visuals
• Project Types and Scale
• Roles and Tasks
• Self-Service BI Tool Choices & Criteria
• Power BI Scenarios & Options
• Introducing the Power BI Dashboard Designer
• Managing Data Transformations with Power Query
• Data Modelling with Power Pivot
• Advanced Power Pivot Properties
• Introducing DAX Calculations
• Visualizing and Exploring with Power View
• Publishing to the Cloud
• Power BI Components in Excel 2013/2016
Bio
Paul Turley (Blog | LinkedIn | Twitter) is a Mentor with SolidQ and a Microsoft SQL Server MVP. He consults, writes, speaks, teaches & blogs about business intelligence and reporting solutions. He works with companies around the world to visualize and deliver critical information to make informed business decisions. He is a Director of the Oregon SQL PASS chapter & user group, the lead author of Professional SQL Server 2012 Reporting Services and 13 other titles from Wrox, Packt & Microsoft Press.
