Thursday, May 30, 2019

See you at SQLSaturday Dallas!

Both my wife and I will be speaking this weekend at SQLSaturday Dallas, back-to-back in room 1.102, and both on topics perfect for entry level IT pros, job seekers, and students. We're honored to be on a SQLSaturday schedule filled with some of the biggest names in the industry, so I hope to see you there! Looking forward to seeing so many #SQLFamily there again.

I'll be presenting on SQL Security Principals and Permissions 101, a ground-level introduction to SQL Server authentication, security principals and even some security principles. We'll go through some code demos about how stored procedures assist the DBA by abstracting the permissions necessary for execution, and how you could design a database security model using role-based, least-permissions pattern. I think everyone can learn something from this session, from DBA's to developers, and how better to secure their databases.

See you at UT Dallas this Saturday, June 1!

Wednesday, May 29, 2019

Untrusted FK's demo + ADS notebooks

Back in 2012 at SQLSaturday Pensacola, I did a lunch-time lightning talk on indexing, which was supposed to have a big finale on how FK's could silently help with performance, and it didn't work. Didn't show the performance boost. It was because, thanks to poor scripting by SSMS, FK's are enabled but not trusted. This blog post serves as a long-belated knowledge share of a problem I didn't figure out live, as well as a successful recreation of that big finale.

Fast forward to a couple weeks ago at the May 2019 Baton Rouge SQL Server User Group, and local fellow SQL pro John Wells gave a presentation to a mixed audience of dev's and DBA's on performance tips, and one of them was to check up on untrusted FK's. Thought occurred to me to create some lab's to demonstrate not only how to untrusted FK's get accidentally created by well-intentioned dev's and DBA's, but how to fix, and also a demonstration on how they can be silent performance drags. So with all respect to John, Chris Bell who John mentioned in his presentation, and many others who have spoken and written well on the topic already, below is a lab demonstrating the issue, resolution, and performance impact.

To further one more goal, I've built the lab into a tsql script but also a new Azure Data Studio SQL notebook. Either offer a step-by-step introduction and demonstration. New to SQL Notebooks? No worries, they were released generally in March 2019 as a new feature of Azure Data Studio (which can be launched from SSMS 18+), which itself was made GA in September 2018.

A SQL Notebook is another way to combine text instructions with step-by-step, modular executed SQL statements in a single file. Notebooks are a popular form of mixed media, combining text, proofs, queries, and their live interactive output. They are already quite popular tools for data scientists to package methodology, analysis and live output. SQL Notebooks include kernels for querying SQL, PySpark, Spark, Python with multiple languages, and now in preview for SQL 2019 CTP 3.0, working with big data clusters. SQL Notebooks could also be used by DBA's and developers as rich, well-documented runbooks for complex exercises deployment or disaster recovery exercises.

SQL Notebooks are easy to learn and use, consider a SQL Notebook for your next detailed query breakdown or summary data analysis. Maybe you'll learn two things at once!

Github links to the SparkhoundSQL toolbox for the Untrusted FK's lab: .sql .ipynb

PS Check it out, Github even parses the notebook JSON correctly to display a (non-interactive) view of the notebook outside of ADS with the saved resultsets. Neat!

UPDATE: Refined my terminology a little bit to favor "SQL Notebooks" not "ADS Notebooks"