SQL PASS Detroit

January 2019

DBAtools Powershell
Ever wanted automate restoring databases? Disaster recovery testing? Database instanace migrations? Using [DBATools](https://dbatools.io) and [DBAChecks](https://dbachecks.io) PowerShell modules, I will demo using these open sources tools taking a bare bones SQL installation, configure it using industry standard best practices and migrate all the users, advanced settings, databases and more. After the migration is complete, we will use dbachecks to validate the server is setup as expected.

DBAtools is a PowerShell module boasting 400+ commands to help you deploy, administer and migrate SQL server instances. DBACheck is a framework using Pester and Dbatools to validate your SQL server environment. Both of these tools are open source and free for you to use.

Expect less than 10 slides, back and forth discussions and lots of DEMOS! Additional demos include automating restore testing with DBCC checkdb, keeping user accounts in sync across instances and (time permitting) using Desired State Configuration to install and configure SQL server.

Speaker Bio: Scott Miller
Scott has worked in the IT industry since 1998 holding many roles from Network/Security admin to OS/DBA team lead. Currently he works as a platform engineer on the infrastructure team for Domino's Pizza at the World Resource Center in Ann Arbor. Scott, along with his team, manage application deployments and 3rd level support mostly using PowerShell to configure, administer and gather information on thousands of point of sale servers running SQL on Hyper-V throughout the US.

Prior to Dominos, Scott lead an OS/DBA team providing SaaS services as well as on-prem managed and DBA services for ERP software. This is where he started his passion for PowerShell which he used to automate redundant processes as well as his desire to teach others to automate the world!

February 2019

Table Design Considerations
This presentation will discuss the many features we can use to design tables in SQL Server. I will discuss some design trade-offs and performance considerations. Data types, constraints, indexes, compression, partitions, memory mapped tables and temporal tables will be discussed.

Speaker Bio: Tom Groszko
Tom has been working with database design since 1972 (IBM mainframe) and with SQL Server since 2000. His work has primarily been with design and much less with administration. He's currently an independent consultant.

Powerpoint Presentation and Code (.zip 773 KB)

March 2019

The Basics of Power BI
This presentation will provide a basic introduction to Microsoft's Power BI offering including an introduction to the Power BI Desktop application and the Power BI service, and, how to create and share business intelligence dashboard solutions with Power BI. Along with demonstrations of Power BI relevant terminology will be introduced as well as ten great resources that will assist someone learning Power BI and developing Power BI solutions.

Speaker Bio: George Squillace
George has been in the I.T. industry for over thirty years and has been a professional trainer for twenty five years, a Microsoft Certified Trainer since 1997. He has worked with and certified in every version of SQL Server since SQL Server 2000 along with a number of other industry certifications. He is very enthusiastic about Microsoft SQL Server and Power BI.

Powerpoint Presentation and Code (.zip 2.5 MB)

April 2019

Replacing Profiler with Extended Events
Extended Events was released over 10 years ago, with SQL Server 2008, and DBAs and developers alike *still* prefer Profiler. However, if you want to troubleshoot anything related to new features, you have to use Extended Events. If you've tried XE and struggled, or if you've been thinking about it but just aren't sure where to begin, then come to this session. Using your existing knowledge and experience, we bridge the gap between Profiler and Extended Events with demos, starting with the Profiler UI you know and love, and ending with an understanding of how to leverage functionality in the Extended Events UI for data analysis. By the end of this session, you’ll know how to use Extended Events in place of Profiler to continue the tasks you've been doing for years, and you'll have the foundation to better understand how you can leverage Extended Events to troubleshoot other issues in ways not previously possible.

Speaker Bio: Erin Stellato
Erin Stellato is a SQL Server MVP and lives in Cleveland, OH. She has over 15 years of technology experience and has worked with SQL Server since 2003. Her areas of interest include statistics, performance tuning, Extended Events, and helping accidental/involuntary DBAs figure out how SQL Server works. Erin is an active member of the SQL Server community, has volunteered for PASS at the local and national level, and is a regular speaker at conferences like SQLintersection and the PASS Summit. Outside of time with family and friends, she enjoys running, spinning, movies, and all things chocolate.

PDF Presentation and Code (.zip 450 KB)

May 2019

SQL Microsoft Machine Learning Services
SQL Server 2019 Machine Learning Services is more than just R, Java and Python language support for SQL Server. SQL Machine Learning Services provides analytics for any size of data, runs in SQL Server, or alongside Windows, Linux, HDInsight Spark or Hadoop clusters, is simple to administer and allows you to integrate and deploy simply. In this session, learn architecture and deployment options, stand-alone and in-database, along with some real-world use-cases. Learn how to leverage Machine Learning Services with new TSQL language support, training and deploying models, scaling beyond memory with RevoScaleR to generating millisecond predictions in-database. With any size data environment, SQL Machine Learning Services can provide analytics in both an operationally sound and easy to administer paradigm. A beginner level understanding of Machine Learning and process is highly recommended but not required. Beyond SQL Machine Learning Services, an overview of all Microsoft Machine Learning ecosystems will be covered, but specific toolset knowledge or in-depth language background (R, Java or Python) are not part of this session.

Speaker Bio: Andrew Loree
Andrew Loree is a Data Insights Consultant at Microsoft and has over nineteen years of data and database experience with roles in architecture, design, development and implementation of enterprise-scale SQL Server data solutions, supporting industries from legal e-discovery to manufacturing. Andrew holds fourteen Microsoft certifications, has a Bachelor of Science in Computer Science from Wright State University and continues his life-long learning journey working to finish his Masters of Science from the same alma mater.

Powerpoint Presentation and Code (.zip 14 MB)

June 2019

DBA: What's Most Important
There are so many things for a DBA to focus on that it can be overwhelming, especially for a new DBA. With all of the different responsibilities that can go with the job, the new or accidental DBA can find themselves trying to plan out so many "top priorities" at once that nothing gets done. This presentation is an overview of five key topics for any DBA, regardless of industry.

Speaker Bio: Ed Wagner
Ed's a Senior DBA and SQL mentor at Ipsos RDA with 26 years experience as a developer and DBA. He's the president of the the SQL PASS in Detroit (SPID) chapter and a frequent poster and guest author on sqlservercentral.com. Ed has spoken at several local PASS chapters and SQL Saturdays. He's passionate about high-performance and "black arts" SQL and actually enjoys it.

Powerpoint Presentation and Code (.zip 2.8 MB)

July 2019

Understanding SQL Server Backup and Restore
Everyone knows that it's critical to have backups but it's amazing how few people make sure those backups can be restored properly! Too many times to count, Tim has been brought in by organizations to help recover failed systems where administrators simply didn’t know how to properly restore SQL Server backups when they have to. In this session, Tim will explain the various types of backups that can be taken and demonstrate how to restore each of them. Apart from covering the common full, differential and log backups, Tim will also cover partial, filegroup, and COPY_ONLY backups, backing up to and restoring from a URL, backup encryption, and how to perform a hack attach for the ultimate tail log restore. There's something here for everyone to learn!

Speaker Bio: Tim Radney
Tim is a SQL Server MVP. He has presented at PASS, SQLintersection, SQL Saturdays, user groups and numerous webinars. In addition Tim runs the Columbus GA SQL Users Group, is a PASS Regional Mentor and was named a PASS Outstanding Volunteer in 2012. He’s married with three children and has a passion for electronics. He also farms chickens (for eggs), dwarf Nigerian goats, and tilapias in his spare time.

Powerpoint Presentation and Code (.zip 832 KB)

August 2019

Hacking SQL Server...A Peek Into the Dark Side
The best defense is a good offense. Learn how to practice hacking without going to jail or getting fired. In this presentation we'll be demonstrating how to exploit weak SQL servers with actual tools of the penetration testing trade. You will learn why the SQL Service is a popular target on your network and how to defend against basic attacks.

Speaker Bio: Dustin Prescott
Dustin has worked for a large insurance company since graduating from Western Michigan University in 2006. Dustin has certifications from Microsoft, EMC, VMWare and Cisco. He currently wears three hats as a SQL/Storage/Virtualization administrator but enjoys a large array of other infrastructure and automation topics. He blogs about these topics at http://nujakcities.wordpress.com.

September 2019

Black Arts Index Maintenance #3 – Defragmented by Default
Microsoft made many wonderful changes to SQL Server with the release of SQL Server 2005, especially when it comes to querying LOBs (MAX, XML, and other large data-types)..

They also made a not-so-well advertised change that seriously affects the way that LOBs are handled and stored by default. Most people don’t even know this change occurred and, for those that do, most are unaware of the ramifications and rather extreme collateral damage this seemingly innocuous change has caused.

1. Non-Lob (which is most of our queries) Clustered Index Queries run twice as slow and require two orders of magnitude more memory to do so.
2. Rampant “bad” page splits, which can lead to serious blocking.
3. Permanent and perpetual fragmentation of Clustered Indexes.
4. Increased data storage requirements.
5. Increased and totally unnecessary Index Maintenance.
6. Increased log file activity, which also affects query performance, backup storage requirements, and increased backup and restore times. Fixing any one of those problems would be a big help.

In this session, SQL Server MVP Veteran Jeff Moden shows us how and why the change causes all of these problems and then demonstrates how two simple changes to our tables fix it all. He also demonstrates how the same techniques can be used to make some non-LOB tables Defragmented by Default. .

Speaker Bio: Jeff Moden
Speaker Bio: With more than 48,000 posts and 3 dozen mostly 5 star articles, Jeff Moden is a strong contributor on SQLServerCentral.com where he coined the term “RBAR” (Row By Agonizing Row) and helped make the "Tally Table" a household name. Jeff has more than 2 decades of experience with SQL Server and is mostly self-trained in what he calls the “Black Arts” of T-SQL. He’s known worldwide for his informative articles, high performance T-SQL coding methods, and methods of mentoring. His dedication to helping others earned him the MS SQL Server MVP award for nine years and the RedGate Exceptional DBA Award in 2011. His mantra is "Performance is in the code". 

Powerpoint Presentation and Code (.zip 5.4 MB)

October 2019

Tuning Indexes to Avoid Blocking
You're getting complaints and alerts about blocking and deadlocking. You've tried slathering NOLOCK all over the queries, and that's not enough. You might be able to change the code, but you'd really rather make a few indexing tweaks to make it all disappear. Can it work?

Brent Ozar will start with the StackOverflow.dbo.Users table from his free online How to Think Like the Engine class, and run updates against this table to see why SELECT queries won't make progress. He'll show you how to fix it by aiming for his 5/5 rule.

Speaker Bio: Brent Ozar
Brent Ozar loves to make SQL Server faster and more reliable. He created sp_Blitz and the SQL Server First Responder Kit, and he loves sharing knowledge at BrentOzar.com.

Back to Top