Wednesday, July 01, 2020

Presenting on Ethics in Modern Data at Houston HASSUG/SQL BI Group July 2020 Meeting

Looking forward to presenting with my wife Christine Assaf our talk on Ethics in Modern Data at the Houston Area SQL Group and BI Group's virtual meeting on Tuesday July 14, 11:30amCT.

This talk is a review of ethical considerations, many ripped from headlines, into analysis and collection for modern data, including the impact on society. This session will explore a variety of considerations that modern data scientists and data practitioners must account for when gathering and presenting data, including topics on bias, construct analysis, and machine learning.

See you then, free to join from anywhere!

Thursday, May 28, 2020

SOLVED! Untrusted SAS certs after SQL startup

If you've tried doing Backup to URL with SQL Server using a Shared Access Signature (SAS) certificate and received this error:

Error: 18204, Severity: 16, State: 1.BackupDiskFile::CreateMedia: Backup device '' failed to create. Operating system error 50(The request is not supported.).Cannot open backup device ''. Operating system error 50(The request is not supported.). [SQLSTATE 42000] (Error 3201) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013). NOTE: The step was retried the requested number of times (1) without succeeding. The step failed. 

You may have received the same error I encountered.

This error popped up only after startup of SQL Server. To resolve the problem, we'd recreate the SAS key, using the same cert in the same script, and the backups would start working again. This affected all types of SQL database backups.

The issue was not our syntax (note the options, the syntax here is standard, but you do need to provide MAXTRANSFERSIZE = 4194304 and BLOCKSIZE = 65536 to backup to Azure blob storage.)
TO URL = ''

The issue was not the SAS key itself, which we tried creating via Storage Explorer, with PowerShell, with various expiration dates, with shared policies, etc.

Rather, the issue was with our SAS-based cert. Here was our syntax:
WITH IDENTITY='Shared Access Signature'
, SECRET='sv=2019-02-02&xxxxx';
Again, the syntax was correct in the sense that backups would start working again right away. But after a reboot, the cert would not longer be available to SQL. 

On the Azure Storage side, the logged failure was:

AuthenticationErrorDetail:Signed expiry time [Fri, 01 May 2020 13:30:52 GMT] has to be after signed start time [Tue, 12 May 2020 22:30:57 GMT]
Why would the exact same SAS key expire after reboot, but then start working again when it was dropped/created? And why wouldn't ALTER'ing the key work?

Our ticket with Microsoft moved through the storage team, the SQL team, and eventually the SQL engine engineering team, who did a complete stack dump to catch the error.

The issue turned out to be related to the hyphen in our credential creation above, note I included "server-name" because our server names have hyphens in them. Turns out, this is unnecessary and caused SQL to choke on trusting the certificate after startup. Here's the explanation from Microsoft support after analysis of the debugger dump:
When you create the credential, the data as we expected is stored in cache and is also persisted to disk but since the data is available in cache, we directly access with full URL name and it works. But after the SQL Server service is restarted and since the credential wont be available in cache, we try to query it from metadata and the way we do it there seems to be an issue on how we fragment the URL and we incorrectly look for container with foldername causing the issue. This is also why Alter wont work and only DROP\CREATE works since it’s in cache again.
We changed the credential to omit the server-specific folder name in the credential. Note that we had originally included the full path to the backup folder because I had read a little too much into this portion of the documentation: THE CREDENTIAL NAME argument requires that the name match the container path." 

This credential creation worked, and kept working after SQL startup.
, SECRET='sv=2019-02-02&xxxxx';

Note the credential name ends with the container path but doesn't include the server name-specific subfolder, and still does not end with a '/'. 

The support escalation engineer marked the ticket as a bug and filed a defect with the engineering team, so we may have a solution in place for this soon. The end.

Oh, also, when we were first starting to use Backup to URL, we got this on some of our older instances of SQL Server:
"Backup/Restore to URL device error: Error while decoding the storage key."
Problem was trying to use an SAS key on SQL Server prior to 2016. Not supported! Gotta use the old syntax and a certificate for the  storage account identity and access key. 

Friday, May 01, 2020

Data Community #DataWeekender Europe 2020: Ethics in Modern Data

On Saturday May 2 Christine and I presented at the Data Community #DataWeekender Europe 2020 online conference, an all-online free virtual popup Microsoft Data Conference hosted by various professionals from around Europe. We're looking forward to presenting to the international audience of hundreds of data professionals, and are grateful for the final time slot that put us in mid-morning Saturday, Central US Time!

Our joint presentation is one of our favorites to present, Ethics in Modern Data. We'll be presenting in Teams Track 6 at 9:45AM US Central Time (-05:00).

This is an important topic that lives at the crossroads of both of our careers, my wife's career in organizational psychology and human resources, my career in data, and our joint passion for history and civil rights. It's important to understand that when dealing with bias, outcomes matter, intentions don't. While many of our examples come from the historical context of the United States, not all, and we have added addition context to an international audience.

Link to our presentation slidedeck with references is here:  Thanks to, a previous recording of our presentation is available here.

Friday, April 03, 2020

PASS Professional Development Virtual User Group: Certification Exams Inside Out

Thanks for the 90+ who joined today's PASS Professional Development Virtual Group webinar on Certification Exams Inside Out: How to Think Like a Certification Exam! This was a lot of fun. Thanks everyone for joining us! And great questions!

If you have any further questions on cert exams that we didn't get to during the webinar, reach out on Twitter or LinkedIn. And if you've become unemployed because of covid-19 and my wife Christine Assaf or I can help, let's talk. We're all in to help. Hit us up on LinkedIn.

Here's a link to the slide deck:

Sorry about all the technical foibles that our Paresh and I encountered! We weren't able to record it for some reason. Here's a recording of a past presentation of this talk if you want to share with folks who didn't see it live:

Stay safe, stay home, wash your hands. Here's some further Q&A:

Q: Does it still make sense to get one of the "old" certifications (MCSA, MCSE) as long as they're offered by Microsoft? 

A: Sure. Exams are always changing generationally and expiring. If being a SQL DBA is still a big part of your career, those older exams do not expire until January 31, 2021 and they will continue to show on your public transcript until January 2023, and the certifications still count towards partner competency attainment through January 2022, I believe. I wouldn't dedicate a lot of time and treasure in achieving them, but if you think you're close, go right ahead. Eventually you will be able to and you'll want to get the newer certifications, as always. Learning new skills in your trade has always been part of the game.

Q: How does the new Azure Database Administrator Associate certification compare to the previous MCSA and MCSE certifications?

A: This is probably a question better answered by others. Aside from further emphasis on newer and especially Azure-hosted technology, I couldn't speak to question format or selection other than what is publicly available anyway. In general, the exams have for a decade been trending towards broader sets of experience (again, an emphasis on delivering solutions as opposed to mastering features).

Q: Tell me more about beta exams.

A: Not free any more, but check the Microsoft Learning community blog for opportunities like this, where you can take a beta exam for 80% off if you're early to it. And while beta exams aren't scored immediately, if you pass the beta exam, you do get credit for passing the exam.

Q: Can you spread crunchy peanut butter with a fork?

A: Yes. That's just science.

Thursday, March 26, 2020

The Business Case for SQL Server 2019

Thanks for joining us for the The Business Case for SQL Server 2019 webinar via Spark/Ed today, an overview of new features in and around SQL Server 2019's release but also including new features to be aware of in Azure, recent SQL licensing changes, and more.

The presentation deck can be downloaded here in PDF form.

Link to the Sparkhound SQL toolbox on Github:

More about the Unlimited Virtualization with SA in Azure on Dedicated Hosts:

Contact me for an opportunity to get involved in an early adopters preview program for Azure SQL Database Edge:

Monday, March 09, 2020

"What did it take to become a MVP?"

This tweet made me pause. I'll explain.

(skip if you're not interested):

On Sunday March 1, I got an email awarding my Microsoft Data Platform MVP status for 2020-2021. The first person I told was my wife and some close friends we were hanging out with. The first person I thanked was Melody Zacharias, my good friend, co-author, and the MVP who nominated me in 2019.

Fast forward past the coronavirus pushing the MVP Summit to virtual-only, past my supportive wife being my supportive wife and supportive Randolph being supportive Randolph. @KennyB7322 replied with the above question, and I was stopped in my tracks. I didn't know how to reply because to be frank, I had thought MVP was something that was out of reach for me.

Years ago I'd made my peace with not being able to attain MVP status, despite being nominated several times over the years via the various processes that existed at the time. I had periodically provided my MVP-qualified activities list in all via the various ways that one self-applied over the past decade. I even got an unexpected MVP email rejection while I was leaving Seattle after PASS Summit 2018, an ill-timed punch in the gut after what was otherwise a really active and positive week of community interaction.

I'd made peace with my level of activity and involvement and location and travel not being enough, and I'd moved on. I still organized SQLSaturday Baton Rouge, helped run the SQL User Group, raised up and megaphoned the tech community how I could, spoke to other SQLSat events regionally and user groups groups when I traveled, though I never invested heavily in travel. I volunteered for local STEM initiatives and nonprofits and folded them into the overlapping audience that attended SQLSaturday Baton Rouge. I enjoyed being a regional mentor for PASS and sharing at PASS organizer meetings at Summit for the past 3 years. I was very fortunate to be on the international author team of a couple Microsoft Press books, a circle for which I have my old friend Patrick Leblanc to thank for roping me into initially. I'm not suggesting these are common everyperson activities, I'm only suggesting that I was doing them despite having long since acquiesced MVP recognition. When Melody nominated me last year, I begrudgingly updated my activity via the new MVP status site, and months later, I got the email.

What did it take?

When reviewing the last decade of my career, and all my active and passive efforts towards MVP, I replied as honestly as I could.
I feel this answer was a bit of an easy escape for me, but I didn't have a better answer.

I talked it over with other MVPs and Melody Zacharias was most clear and concise to what I was trying to capture with this blog post:
"The biggest issue people have is trying to guess what Microsoft is looking for and in that forgetting to be themselves. MVP is not something you try to attain, that it is something the community and Microsoft use to recognize your commitment and contributions to community."
So without knowing or having any specific guidance on becoming an MVP, here are a few pieces of advice:

1. Understand that I don't know - I'm not sure if any MVPs do - what exactly was the special sauce that got them awarded or renewed, other than the accumulation of individual activities that enhance the community in a positive way. Microsoft has a qualitative process they discuss here and here, and they are - from what I gather - pretty dialed into the community. For the Data Platform MVP process, they are clearly dialed into PASS, among other circles. If you have questions about this process, a Community Program Manager (CPM) from Microsoft will explain this better than I, their contacts are listed here.

2. How many DISTINCT talks are you giving at user groups and SQLSaturdays and other tech conferences? In a conference call Q&A that Microsoft hosted for nominated MVP candidates in December 2019, one bit of feedback that caught my attention was that they didn't want to see the same presentation over and over again in your activity list. (They specifically gave an example of an MVP with a rotation of 7 different talks in the field.) I've had mainstay presentations on DMVs and also Security for a decade that I've presented dozens of times... so it was initially scary to hear that. But upon review, I have mixed in other topics, including current events topics like "What's New in SQL 20nn?" and non-technical topics, and also different educational settings. For example recently my wife and I have started jointly presenting a session on Ethics in Modern Data, which has been a great experience itself.  

3. What can you do to uplift others in the tech community? I was encouraged by other MVPs after I was nominated to provide much more on my MVP activity list than just PASS events, including: volunteering and organizing events and groups, nontechnical blog posts about community activities, articles or other activities published or picked up by other websites, collaborations with other technical and STEM groups and events, hackathons, volunteering, mentorship. (Note that paid stuff you do via your job is probably not supposed to be part of your MVP activity list.) I did not go so far as to add each of my blog posts to the activities list, only the most substantial and relevant. And certainly, I have not provided a complete list of all things you could put on your activity list.

4. On that note, my wife had a great idea during her job search in the completely unrelated field of I/O Psychology, to add a chronology of speaking or other public engagements to her blog. It was a long list once she started adding training she'd led, community workshops, and events she'd facilitated for various organizations, in addition to groups and conferences. So I did the same on my blog. I don't believe this helped me because the timing doesn't work out, but it couldn't hurt your online presence. Before you are nominated for MVP, this might provide a nice historical list of your qualified activity.

I hope this blog is helpful and constructive, without over-promising. If you have anything to add, please do, and link to it the comments below. Happy to discuss. 

Sunday, March 08, 2020

Thanks for joining us at SQLSaturday Baton Rouge 2020 Business Intelligence edition

Thanks for joining us at the first annual SQLSaturday Baton Rouge Business Intelligence yesterday, put on by the Baton Rouge Analytics and Intelligence Network (#GOBRAIN!), an official chapter of PASS. Congrats to Balaji, Thomas, John, Katie, Andy and the rest of the organizers and volunteers on a successful event.

It was great to see so many new and familiar business intelligence faces in the Speaker Room in the morning, and spending the day in the brand new Taylor Hall at LSU, which back in my day was called CEBA, and hosted the first few SQLSaturday Baton Rouge events starting in 2009.

I had the pleasure of facilitating a panel on Careers in Business Intelligence for a full room of students and jobseekers after lunch, with a fantastic panel featuring:

  • Ganesh Lohani
  • Chris Hyde
  • Sekou Tyler
  • Leslie Andrews
  • Anandi Subramanian
  • with guest appearance from Amy Herold. (lol)

Following the panel, Christine and I presented our talk on Ethics in Modern Data. This is an important topic that lives at the crossroads of both of our careers: my wife's career in organizational psychology, my career in data, and our joint passion for history and civil rights.

Eager for feedback on both sessions, please use the online feedback form if you haven't already.

Thanks again for joining us in Baton Rouge, where it was a pleasant break for me to be a simple, humble speaker and volunteer instead of a lead organizer at a SQLSat event in Baton Rouge. :) From all appearances, the veteran team handled their first event well, even with the expected problems like locked rooms, no-show sponsors, room changes, and sharing our space with an army of high schoolers thanks to mixed schedules from our host facility. I didn't see any problems or hear any complaints, and despite being very busy, the volunteer team did well!

Monday, March 02, 2020

SQLSaturday Baton Rouge 2020 Business Intelligence edition this Saturday!

The first of a double whammy of SQLSaturday Baton Rouge events over the next 5 months, SQLSatBatonRougeBI is this Saturday, March 7! The organizers of this event, from the Baton Rouge Analytics and Intelligence Network, have assembled a dynamite speaker schedule of business intelligence voices from around the country.

Christine and I will be speaking at 3pm on Ethics in Data. This is an important topic that lives at the crossroads of both of our careers: my wife's career in organizational psychology, my career in data, and our joint passion for history and civil rights. The effort of researching, paring down, and rehearsing our presentation together as a couple has been an exciting new challenge for us.

It's important to understand that when dealing with bias, outcomes matter, intentions don't. In this presentation we review historical bias, sources of bias, transparency in bias, patterns and anti-patterns, and disparate impact. We have loads of real-world examples and case studies pulled from journals and headlines alike. This has been a fairly interactive presentation in the past and we look forward to bringing this topic to the audience in our hometown.

I'll also be facilitating a Careers in Business Intelligence panel in the same room at 1:45pm, with a selection of speakers from the conference sharing their career progression and wisdom and experience. All students, educators, and job seekers are encouraged to join.

See you there!

Friday, February 21, 2020

ACM Lecture at SELU

Had the pleasure of presenting to Dr. Ghassan Alkadi and a full house at the Southeastern La. University ACM group. Big thanks to the bright student organizers for inviting me, organizing, ordering not enough pizza, ordering more pizza, and generally being awesome for the evening.

A link to my complete presentation is here and the toolbox referenced, where you can get those scripts to show off to your friends at parties, is here. If you have any questions, please reach out.

Thursday, February 13, 2020

What's New in SQL 2019? at the Santa Fe SQL Server User Group

Hi Santa Fe! My first remote presentation for the Santa Fe SQL Server UG was on "What's New in SQL Server 2019?" Thanks to my friend Adrian Mee for organizing and reaching out so that we can line up this meeting, happy to participate in another group in the US South Central region. Thanks to the great crowd in Santa Fe that joined us for lunch and to learn more about SQL Server 2019!

The slidedeck is available for download here, with many links inside. Any referenced "toolbox" labs are here.

Saturday, February 08, 2020

Ethics in Modern Data presentation at SQLSaturday Austin BI Edition 2020

Excited to launch another new presentation, the first co-presented with my wife, on Ethics in Modern Data. We'll explore the ethical considerations around historical bias, ethics in analysis and data collection, and disparate impact, with tons of well-documented case studies and examples.

This is an important topic that lives at the crossroads of both of our careers, my wife's career in organizational psychology and human resources, my career in data, and our joint passion for history and civil rights. The effort of researching, paring down, and rehearsing our presentation together as a couple has been an exciting first for us. It's important to understand that when dealing with bias, outcomes matter, intentions don't.

Thanks to the many of you who chimed in during the presentation, including with further reading and book recommendations for us all!

If you'd review any of the topics or case studies we covered, our slides and citations is available for download here.

Thanks to, a recording of our presentation is available here.

My wife also presented at Austin SQLSaturday BI 2020 on "Mastering your Resume & Interview: Tips to Get Hired" Saturday afternoon.

Friday, January 24, 2020

ANSI_WARNINGS = OFF can break update statements with some SQL features

Working on a SQL 2017 database where an external, non-Microsoft application has direct access to read/write data via the ODBC Driver 17 for SQL Server.

They suddenly began to experience a regression, UPDATE statements were failing with the following message snippet:

 esu_sql_execute (SQLExecute) : [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]
UPDATE failed because the following SET options have incorrect settings:
'ANSI_WARNINGS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications
and/or XML data type methods and/or spatial index operations.

esu_sql_execute (SQLExecute) : [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared.

Right away, I figured that something in the database from the above list of options was incompatible with the legacy ANSI_WARNINGS = OFF setting the external developers were using in their code. And I was resigned quickly to the fact that something in SQL Server would have to change, as this project was nearing a time-sensitive milestone.

Though ANSI_WARNINGS = OFF is not recommended, changing it in an existing application is problematic. Development should begin with ANSI_WARNINGS = ON and ANSI_DEFAULTS = ON, but if it doesn't, changing these settings requires a LOT of regression testing, especially around the behavior of NULL values in aggregate functions, divide-by-zero and artithmetic error handling, and how trailing blanks are handled. So yeah, it gets messy and time-consuming.

As the error message above had said, I quickly looked through the database for the SQL features listed in the error message (above). All of these features of SQL Server have incompatibilities with the legacy ANSI_WARNINGS = OFF setting. 

Sure enough, we had added a very beneficial filtered index recently to help with some lookups on a table where only a minority of the records had a status we were interested in. 

The filter was removed, performance gain lost, the UPDATE statements started working again.

Wednesday, January 22, 2020

Our SQL Server 2019 Administration Inside Out Fun Author Survey

SQL Server 2019 Administration Inside Out
Honored again to be leading the talented SQL Server 2019 Administration Inside Out author team, and share some fun insights from the team as the book is in its final edits.

Technical book writing is such a mixed bag of emotions - long hours writing, existential doubt and validation, progress in seeing chapters move through editing stages, Sisyphean edits, wrangling metadata and chapter status, pride in the finished product, and finally some post-traumatic stress as the next major release of SQL Server approaches. It's been a pleasure to be the ersatz leader of the team, even though I'm not the first name on the book, a lot of work from everyone on the cover goes into producing the content, including our two beloved technical editors Meagan Longoria and Louis Davidson.

So a "fun" look back at our time on the book, spent mostly in Q4'19, seems appropriate as we near publishing.

A little bit on the cover order. We broke up the "shares" of workload on the book by chapter. Authors who revised/added to existing chapters received one share. For net new chapters (for example, our entirely new final chapter on Big Data and Machine Learning features of SQL Server 2019) an author received three shares. Combine all those shares together, and we came up with a cover order, descending. It doesn't necessarily translate to chapter count or effort, and I can attest personally that with all the new changes necessary for SQL Server 2019, existing chapters' revisions were considerably more work than we thought! Exciting that the book has a lot of new value even for existing chapters, and something to note for next time as we plan workloads...

Similar to our fun author survey from the 2017 bookwe the author and tech editor team put answers to questions. This was a nice break while the chapters were progressing through their final edits. The 2019 edition of this book series should be on shelves in Q1'20 and is available for pre-order now everywhere books are sold.

As for this book's fun author survey, I found it interesting how half this team writes with music and half the team must not, how some of us write/edit in odd nooks and some in their home office enclaves, how some of us know how to spend free time and some of us have forgotten how, and how all of us love the new scalar function inlining feature of SQL Server 2019...

1. What music if any did you listen to while writing or editing?

What's New in SQL 2019? at the Data Architecture Virtual User Group and Q&A followup

Thanks to all 180+ of you who joined the Data Architecture Virtual User Group for a hour all about SQL Server 2019! We reviewed everyone's favorite new features of SQL Server 2019. There were lots of questions, we got off onto tangents and use cases for SSRS, licensing, Hybrid Buffer Manager, Accelerated Database Recovery, and memory-optimized TempDB metadata. I answered some more questions below. 

Kudos to Kenny Neal for organizing!
Slidedeck here: 

Q&A Followup:

1. The Database Experimentation Assistant uses Distributed Replay to do AB testing for the same workload on different hardware/platforms. Yes, as long as you can connect the application to the SQL Server instance using Windows Auth, you can do AB testing between on-prem or Azure VM instances of SQL Server and Azure SQL Database or Azure SQL Managed Instance, and only between SQL Server on Windows and SQL Server on Linux. More info:

2. More info on the announcement that you can get SQL Server for free with Software Assurance if it's an Availability Group replica in Azure VM's:

3. Yep, you can put the SSISDB in an Availability Group. You have been able to since 2012, it has gotten easier/better recently.

4. Would "OPTIMIZE_FOR_SEQUENTIAL_KEY" help with BULK INSERTS? Parallel bulk inserts maybe. The sequential key hotspot PAGELATCH_EX issue is not I/O related (because it's  PAGELATCH_EX, not PAGEIOLATCH_EX), it's memory page related, and it's related to multiple parallel inserts, not just a lot of inserts. More info here:

5. Parallel Data Warehouse (now inside of the Analytics Platform System appliance) is in a different lifecycle than SQL Server... I'm afraid I don't follow it and I don't know which of these features is getting over into the PDW.

6. There was a question I think about about Resource Governor and Hybrid Buffer Pool or PMEM (Persistent Memory such as Intel Optane DC). I wasn't sure there was a connection at the time. Still pretty sure there isn't. The memory governed by the Resource Governor is not buffer pool memory but query execution grant memory.

7. Accelerated Database Recovery is a SQL Server 2019 feature - it doesn't depend on database compatibility level! Though you do store the ADR version store in the user database (in a filegroup of your choosing), it works with any database compatibility level. More about ADR here: and also in our book, out soon!

8. Are there any drawbacks to enabling the memory-optimized tempdb metadata option? There are some fringe limitations in memory-optimized TempDB metadata that might be an issue for some, but probably not for most. Damir Matešić joined my webinar today and followed up with some more info on the limitations here:

If you have any additional questions, feel free to leave a comment here on this blog post or hit me up on twitter @william_A_dba.

Tuesday, January 14, 2020

Practical Personal Cybersecurity at Activate Conference Lightning Night

Photo: Shaniya W. of The Futures Fund.
Thanks to the organizers and many attendees of tonight's Activate Conference Lightning Night at LSU, the first of many series of short-format talks on technology put on by the creatives behind the annual Activate Conference in Baton Rouge.

It was great to see so many diverse faces from the Baton Rouge tech scene, including a big presence (and a teenage speaker!) from the Futures Fund!

You can download my slidedeck on Practical Personal Cybersecurity here on my Github.

Thanks again to everyone to was there, and a special thanks to Lynsey Gwin and Quinton Jason Jr for organizing! Please fill out their post-event survey here.