Upcoming SQLSaturdays in Alberta, Canada

Taking a short break from the Database Fundamentals series of the last few weeks, I’d like to mention some upcoming PASS community events in the province of Alberta.

I will be presenting at SQLSaturday #594 in Edmonton on 22 April 2017 (this coming Saturday). My topic is Migrating to Azure SQL Database: Tips, Tricks and Lessons Learned.

Next weekend, I will be hosting SQLSaturday #607 in Calgary on 29 April 2017. This is the first ever SQLSaturday in the city of Calgary, and we even have a special message from our celebrity mayor, Naheed Nenshi.

If you live in or around these two cities, please come and say hi. You can also reach out to me on Twitter at @bornsql or @sqlsatcalgary.

The Database Fundamentals series will continue next week.

Data Platform MVP Award

I am pleased to announce that I was awarded the Data Platform MVP Award by Microsoft on 1 January 2017.

Nothing changes with my customers. I will continue to look after you with the same level of trust. Though, I may ask for a plate of cookies at my next meeting.

My friend Kenneth Fisher said it best, when he was awarded in October 2016:

I’ll be honest and say it still hasn’t really sunk in yet. At least in part because I don’t really feel like I’ve done anything special. Mostly I just write a blog.

Thank you to Microsoft and, more specifically, the SQL Server community for recognising my contributions. This is for you.

Note: new blog posts will come out on Wednesdays from now on.

A look back, and a look forward

Time flies. My father used to wear a t-shirt that claimed, “When you’re over the hill, you pick up speed.”

I’m turning 40 in a few days. I still feel like a teenager in many respects.

SQL Server, released in 1989, is 27 years old now. It’s about the same age I was when a career change became necessary. I became a lecturer, briefly, then a high school teacher, and a tutor on the side, sharing my knowledge, all the while learning how people learn.

Combined with my so-called “train the trainer” learning a decade prior in college and the help desk support job from around the same time, it allowed me to unlock ways to explain technology to people. In so doing, I realised that I had a real arrogance about technology. Whereas I wanted people to bend to its ways, I realised that it was technology that had to adapt. Now when I build products, the aim is to be as simple as possible.

When I went back into the tech world after teaching (for money, as it turns out—it’s always about the money), I tried to keep this new perspective. We need to review our thinking and assumptions more often than we do.

SQL Server, and Azure’s Platform as a Service, have come a long way in a relatively short amount of time. Things we had to worry about as little as four years ago are being made much easier by the newest versions. Self-tuning databases, the Query Store, In-Memory OLTP, and recent changes to SQL Server 2016’s Service Pack 1 are all things Microsoft has learned and borrowed from competitors and customers alike, to produce probably the best relational database engine currently on the market.

Microsoft challenged their own assumptions. The market required it. Five years ago it was unheard of, the thought of Azure hosting Linux and Windows equally, with NoSQLs and Red Hats and Oracles and SAP HANAs, oh my! We even have Linux on SQL Server now. I still don’t know what it’s for, but it’s really very clever how they did it.

(Full disclosure: I didn’t know what cameras on phones were for, either.)

I’m a technology Luddite. I literally break technology more often than I care to admit, though admittedly not because I think it’s taking my job. I call myself a living edge case and a human beta tester. It’s usually accidental, but if something is going to break, it’ll break around me.

As a result, while I tend to have the latest shiny computers and electronics, I write my important notes in a Moleskine notebook I carry with me. It was a gift from my spouse and has Smaug on the cover. It has no lines on the pages, because sometimes I need to sketch things in meetings. I prefer a resilient hard copy any day of the week, and flipping back the pages of the book has a certain feeling that technology can’t touch.

When SQL Server 2000 was released, Microsoft’s marketing claimed that the database server could look after itself. Of course this turned out to be false, and I was still working with 2000-era databases until quite recently. Marketing played nicely into my bank account, thank you.

However, the trend towards not having to manage databases is continuing apace. Azure SQL Database takes much of the administrative burden away so that most people who need a database can get one easily, without concerning themselves about maintenance tasks and how the data drive was formatted. Technology is getting better. It is adapting better to people.

Looking back on 2016, I recognize my only fondness for past eras of computing is borne by nostalgia. SQL Server 2016 is the best version of the stand-alone product. Azure SQL Database keeps getting new features so frequently that it obsoletes my blog posts, sometimes only days later.

I am looking forward to the new challenges this brings. I feel overwhelmed sometimes, but I know I’m not alone. There will always be performance issues, and database corruption, and customers not taking or testing their backups. I’ll find a gap there, and as for all the other stuff, I’ll keep learning.

If you would like to chat about this some more, feel free to contact me. I’ve taken a break on Twitter, but you can find me there at @bornsql , and I respond to direct messages.

SQL Saturday in Calgary!

I am very happy (and terrified) to announce that Calgary will host its very first SQL Saturday, #607, on 29 April 2017.

Noel Tan and I are organising it (though Noel is doing all the hard work), and my company is sponsoring the main prize, a Microsoft Surface Pro 4.

I would like to invite all my readers to attend this event, and if you are in the general vicinity of Alberta and can take the week off to also attend Edmonton’s own SQL Saturday a week prior to ours, on 22 April 2017.

It’s a comfortable drive from Edmonton to Calgary, and you can take in many sights, sounds and tastes over that week. Both Calgary and Edmonton have international airports only 30 minutes from their respective city centres.

Speakers are welcome to apply. We already have interest from Canada, USA, and Australia. You can speak on any number of topics, including Azure SQL Database, SQL Server on Linux, Best Practices, SQL Server 2016, and so on.

To register to attend, speak, or sponsor the event, visit the official Calgary SQL Saturday website (http://www.sqlsaturday.com/607/) by visiting and clicking on the Register Now button. We will be Event #607.

To stay abreast of what’s happening in the lead up to this event, you can also watch the #sqlsatcalgary and #sqlsat607 hashtags on Twitter.

If you would like to know more, find me on Twitter as well, at @bornsql .

Updated Max Server Memory Script

Earlier this year I released a free T-SQL script that will calculate the correct amount of RAM you should allocate to SQL Server, assuming it is a standalone instance.

After attending the PASS Summit in Seattle in October, I visited the SQL Server Tiger team’s GitHub repository and discovered something similar, but not quite the same, in the Maintenance Solution folder.

I have taken the best ideas from their Database Server Options script and merged them into my Max Server Memory Calculator script.

New Features

The SQL Server thread stack is now taken into account. This value depends on the CPU architecture (32-bit, or x64 / IA64) and the maximum worker threads configured for the SQL Server instance.

On my 64-bit laptop with 16GB RAM, the new recommended amount for Max Server Memory has dropped from 11,264 MB to 10,112 MB (1,125 MB of RAM is now reserved for the thread stack).

Improvements

By default, the generated script will enable show advanced options before trying to set the max server memory (MB) value.

The @ProductVersion parameter uses a new method to calculate the major SQL Server version.  Previously it was a hack based on the string returned by the @@VERSION function, but now it uses the @@MICROSOFTVERSION function.

This code is also from the Tiger team’s repository, and I’m sharing it here because I think it’s pretty clever how it works.

-- Get SQL Server Major Version
SELECT CONVERT(INT, (@@MICROSOFTVERSION / 0x1000000) & 0xFF);

I have also added a note on the Max Server Memory Matrix page to note that the script now accounts for the thread stack.

I hope you enjoy this new version of the script. If you have any comments or suggestions, please contact me on Twitter at @bornsql .

Interview Questions for a SQL Server DBA

When I’m interviewing a SQL Server DBA, I have three questions that I always ask.

My favourite interview question is this:

“What is the difference between a clustered and a non-clustered index?”

My second favourite question is this:

“What is the right disaster recovery strategy for my company?”

The first question is one of basic understanding. Depending on their work experience, the candidate may have an inkling about why such a distinction matters, and this lets me know how much I have to teach. What it does not do is disqualify them. I am not averse to teaching, which is why I write on this website in the first place.

The second question should be answered with “it depends”, followed by the candidate asking me questions about my company so that they can figure out a way to begin answering it. If the candidate blurts out an answer without asking questions, well, their résumé goes in the garbage. This is the disqualification question.

I’m mostly self-taught, with formal training to fill in the gaps much later in my career, so I know the difference between “I know this thing, but not the textbook definition”, and “I read this somewhere on Google and have never used it”.

If the candidate uses words that are blatantly wrong but have the right idea (“the blob is sorted on the disk, but the non-blob one is a copy of the blob, but smaller, because you can only have one blob, and it’s shaped like a Christmas tree”), then that’s a pass for me. SQL Server doesn’t care how you describe an index to an interviewer, as long as the syntax is correct, and that’s what Books Online is for.

Heck, I might even say “if you can’t explain it with words, draw it for me on the board”. I love pictures. I’ve drawn many lopsided databases in my time. That’s what the dry-erase board is for: working through a problem that exists in the real world. It’s not there to make someone regurgitate a textbook definition. Again, that’s what Books Online is for.

Here’s a practical example from my school days, as a student and as a teacher: the notorious open-book exam.

In this scenario, the exam taker is allowed to refer freely to their textbook throughout the exam, and the invigilator is there to make sure no one is distracting their fellow students.

The open book exam relies on the exam takers having a working knowledge of their subject. In the case of English literature, for example, it helps to have read the book. Summarised study guides only get you so far — if I’m asking why the love between Catherine and Heathcliff can be described as “demonic”, providing examples from the text, you would have to know where to look.

Even so, the open book exam is unfair for those people who haven’t read the book but have seen the movie ten times and can quote their favourite lines from it. Or if they prefer the audio book, because they struggle to read words on a page, especially under stressful situations. Or their parents read the story to them many times in their youth. Or English isn’t their first language. Or their second language.

If you have haven’t read the book, you will run out of time to answer the questions, because you’ll be frantically reading the textbook for the first time, trying to understand why anyone wants to know what a linked list is for (a very typical interview question for software developers). Meanwhile, you’ve used linked lists before in that job you had writing code for your cousin’s bicycle store, but you didn’t realise that was their actual name.

If I’m asking you, with only a few years of experience administering an Access database for your uncle’s grocery store, to create an index in SQL Server, without using the graphical interface of Management Studio, the first place you’re going to look is Books Online.

As my friend Gail Shaw (b | t) once said many years ago, when you are working on a SQL Server database, you should have two windows open, and one of them is Books Online. Why guess arcane syntax when you can simply look it up?

Which brings me to my third favourite question:

“What was the last mistake you made, and how did you recover from it?”

I’ll go first.

This morning I was working on a stored procedure for a new system. The code called for an upsert-style stored procedure (INSERT and UPDATE in one block of code, to check for the existence of a row of data in order to update it, or insert it if it doesn’t exist). My UPDATE statement was missing a WHERE clause.

Fortunately, the tools that I have at my disposal managed to catch this before the code was ever run. My point is, even with decades of experience, and being able to recite all of the keywords in an UPDATE statement, even the most battle-tested person forgets a WHERE clause once in a while and updates or deletes the entire table.

This is why I love my second favourite interview question, because I get to ask the candidate this bonus question:

“Does the disaster recovery strategy you came up with in question 2 cater for the scenario you just described?”

After all, backups matter only if you can restore them promptly and accurately.

Final Thought

This discussion has made a resurgence thanks to my friend Janie Clayton, who has posited (in my interpretation anyway) that technical interviews are designed to exclude certain people from applying for a position, for arbitrary reasons (university degree, culture, gender, language, etc.), for the sake of some puritanical ideal.

My take is obvious: If you have working knowledge that you can demonstrate, and some experience, you can learn the rest on the job. Every system is different, with different goals guiding a decision. It takes time to become accustomed to a new system, and even the smartest person in the room will have to ask questions.

The question is not “what have you memorised?”, but “what can you learn?”.

By all means, if you’re applying for a specialised role (for instance, performance tuning), then you need to have specialised domain knowledge. For everything else*, there’s Books Online.

* MasterCard did not pay for this post.

What to do at PASS Summit 2016

Next week, while blog posts are scheduled as expected, I will be attending my third PASS Summit.

Summit 2014

In my first year, I attended every single event I could.

The Monday night started with Steve Jones’ regular Networking Dinner. When I arrived, there were already over a hundred people there, all of whom I didn’t recognise. Until I saw Ed. Ed Watson and I had met in Tampa, FL, at a SQLskills Immersion Event in 2012 and stayed in touch on Twitter. I met new people that night through Ed and reconnected with the folks I’d met at the five Immersion Events I’d attended in 2012 and 2013. The Summit hadn’t even started, and I was already seeing its benefits.

On Tuesday was the First-Timers’ Orientation Meeting & Speed Networking event, where I met some folks with whom I reconnected repeatedly during the week. There may be thousands of attendees every year, but humans recognise familiar faces in a crowd, and we didn’t need to feel overwhelmed.

Later on Tuesday night was Denny Cherry’s long-running SQL Karaoke evening, where I met Argenis Fernandez for the first time in the flesh, and sang a duet with Ed Watson (Sonny & Cher’s I Got You Babe).

There were many vendor-sponsored networking events as well, where we received free food and beverages. I attended several of those.

I also attended both keynotes (hint: the first keynote on the Wednesday is marketing, so you can sleep in – but take note, Brent Ozar thinks it might be worthwhile attending this in 2016. The second one, on the Thursday, is worth attending), and I can say that Rimma Nehme is amazing.

I attended a lot of sessions. Many of them were incredible. I had my brain melted by Bob Ward’s talk, “Inside SQL Server I/O”. If you get a chance to view this online, do so.

I hung out a lot with another friend, Larry Toothman, whom I’d met in Tampa at the same SQLskills Immersion Event where I’d met Ed. Sadly, Larry died in 2015, but the SQL Server community was incredible with showing support to his husband, and keeping Larry’s memory alive by sharing stories and handing out ribbons.

The main takeaway I had was that I had to return in 2015. The networking aspect alone made it worthwhile. The learning was the cherry on top.

Summit 2015

In 2015, I took a calmer approach. While I still attended a lot of technical sessions, I went to two pre-cons (pre-conference events, which are run separately and cost extra). One was PASS-sponsored, and I got to learn a lot from Aaron Bertrand in his “50 Things All SQL Server Developers Need to Know” full day session.

My other pre-con was unpaid, and unsanctioned by PASS. I attended the Brent Ozar Unlimited “FreeCon” event, with 49 of my closest friends, to learn the things you need to know to market yourself better. That’s the reason I now write at least one blog post a week and have made a bigger effort to sell my skills as a consultant.

I sang another duet with Ed, at the new SQL Karaoke venue (Summer Nights from Grease) and had an impromptu group of ladies helping me sing Sandy’s part.

While I did attend both keynotes again, I decided I would skip the next year’s Day 1 Keynote, because it’s pure marketing. In the second keynote, Rimma Nehme was back again, incredible as ever, with David DeWitt to assist her.

I also did not attend every session I’d planned, because the rumours were true: I was gaining more knowledge by networking with people. I met some new people, including Ewald Cress, a guy I went to school with in a rural town in the middle of nowhere.

Bob Ward melted my brain again, but this time his talk was easier for me to understand. I sat next to Gail Shaw, a fellow South African I know from the old country, and we agreed later that the 2014 talk was a lot meltier.

I met Steve Stedman in the flesh, after taking part in a Database Corruption Challenge he had run earlier in the year. As it turned out, Steve and I did some business together in 2016 as a direct result of this networking. It works, folks.

On Friday evening, David Klee invited a group of us to try again at karting. In 2014, due to bad planning with taxis, we missed out, but ended up crashing a birthday party of my favourite Australian, Rob Farley. Rob and I got to know each other during Steve Stedman’s corruption challenge too.

2015 was more successful at karting too, because I won the final race with David coming in a very close second place.

Summit 2016?

More networking. More karaoke. Because I’ll only be arriving on Tuesday this year, I will miss some of the unsanctioned festivities, but rest assured I’ll catch up with everyone again. I also plan to have my brain melted by Bob Ward. Alas, his talk is the same time as Gail Shaw’s, so I won’t be able to heckle her.

Advice for newcomers

If this is your first year, definitely do the orientation first, and check out the sessions that interest you.

On the other hand, don’t forget to introduce yourself to new people and talk to them about SQL Server. If you are on Twitter, and follow any of us in the SQL family on Twitter, come and say hi. (Heck, even Grant Fritchey, Executive Vice President of PASS, agrees.)

I’ll be wearing my trademark black EAT SLEEP RAVE REPEAT CREATE READ UPDATE DELETE sweater (don’t worry: I have three of them, and they get washed). Since I have terrible concentration, eyesight, hearing, etc., I’m usually sitting right at the front of each session I attend, so you can’t miss me.

Speaking of Twitter, come find me on @bornsql and let me know if you’ll be at Summit 2016. Come to Denny’s karaoke evening.

The golden rule applies to Summit, as it does in life. If you’re respectful to fellow attendees, the favour is returned, and you’ll become lifelong friends with lots of folks, who will be happy to help you out in a bind.

Speaking at SQLSaturday 507 this weekend in Edmonton

On Saturday 9 April 2016, four days from today, I will be speaking at the first ever SQLSaturday in the province of Alberta.

Edmonton is the provincial capital, so it makes sense to have it there, though it’s a bit of a drive to get there from Calgary, where I live.

Fortunately, Red Deer is about halfway between the two cities, and I have a place where I can eat and buy some Rooibos tea in Gasoline Alley.

My talk will be similar to the one I gave last year at the Calgary SQL PASS User Group, The SQL Server Performance Starter Kit, which was well received by the attendees.

If you’re in the area, please come and say hello. It’ll be great fun, and you might even learn something.

You can find out more on the event’s home page: SQLSaturday #507.

Professionalism doesn’t mean a collared shirt and tie

Working from home, consulting with companies all over the world, has changed how I interact with customers. The last time I was physically on site was seven months ago.

We deal almost exclusively with each other via conference call and video using Skype, LogMeIn or GoToMeeting, juggling webcams, headphones, microphones, email, text messages, phone calls, instant messaging, and so on and so forth …

Scott Hanselman wrote on Twitter recently about spending more than 20 minutes of a one-hour meeting getting microphones working for all meeting attendees, and this is in 2016!

Being professional means treating your customers and colleagues with the respect you think you deserve in return.

Put another way, if you treat other people with contempt, you can’t expect to be taken seriously.

Missing meetings, not having your equipment set up correctly, not wearing camera-friendly clothing (or any clothing at all!), having an inappropriate backdrop, or having an inappropriate desktop background if you’re sharing your screen, all amount to contempt.

Take the time to set up your work space correctly by keeping the webcam-visible area behind you friendly to anyone watching you on video.

Learn how to use your webcam or microphone or headphones correctly. If you have to share your computer screen, make sure you have turned off notifications. Even better, try to keep to one virtual desktop away from email, web browsers and social media.

Do you use a Mac? Did you know that there’s a way for you to set up your microphone to send clear and crisp audio through Skype or other tools? It’s called Loopback.

All that money you’re saving on gas? Buy a decent condenser microphone, over-ear headphones, and a high-definition webcam. Don’t rely on your laptop’s built-in speakers. You know what microphone feedback sounds like, and wearing headphones is a great way to avoid it.

Don’t pick your nose. Don’t get too close to the camera. Someone might have you on a giant television screen with lots of people in the room. Because you’re not physically in the room, perception is everything. Even I make some of these mistakes, which means I’m also guilty of behaving in an unprofessional manner.

This post is not only to let you know how to behave, but to remind me how I should behave. We’re in this together.

The SQL Server Family

(This is a non-technical post, and is part of the #SQLNewBlogger Challenge.)

This year was my second PASS Summit, and my favourite one so far. Many of the data professionals in attendance with whom I spoke, feel the same way.

Aside from some spectacular keynote moments, not to mention the sessions I attended, I was struck on several occasions with how much like a family the SQL Server community is, and how awesome it is.

I would like to take you on a journey to the years of 1989 and 1990.

I was in my final year at Craighall Primary School in 1989, a school which had introduced me to Atari Logo and my love for computers beyond the CreatiVision console system my father had bought us in the early 1980s.

I would be going to Queen’s College, an all-boys boarding school in Queenstown from 1990, in the Eastern Cape. It was an overnight train ride away from my family, where I would have to embark on a very different chapter of my life, make new friends, and “become a man” (thanks, dad).

Fast forward to 1990, and a fish out of water called Randolph. I only lasted 18 months.

There were highlights, though. I made lifelong friends with several boys there. And every so often while I was in Grade 8, we would be treated to a piano performance in our weekly assembly, by a Grade 12 boy called Ewald Cress. I remember how extraordinarily talented he was.

piano_hands

Despite some months of music lessons, my own musical aspirations never took off until 2014, when I joined the Calgary Men’s Chorus in order to sing with them and other choirs at Carnegie Hall in New York.

Yes, it’s as amazing as you think it would be.

But until this week, I had forgotten about Ewald Cress.

This week, I was attending a session at the PASS Summit by Glenn Berry, presenting his SQL Server Diagnostic Queries (which I use all the time with my clients).

During the break, I caught up with Lonny Niederstadt, one of the many incredible people from the community I’ve met on Twitter. We had our usual chit-chat that you’d expect from this sort of meeting, and then, like a cliché, he asked if I knew another South African he knew.

I do this too: I hear someone is from somewhere, and the pattern-matching algorithm in my head goes ping! I’ll ask, “Oh, you’re from Madison, Wisconsin? Do you know Janie Clayton?”

Anyway, he asked me if I knew a guy from the SQL Server community in the UK, who had also emigrated from South Africa. Statistically speaking, the chances of this are tiny. While the SQL Server community in South Africa might be relatively small, I only knew some of them from Johannesburg, who I’d met at the User Group meetings over five years ago.

I rolled my eyes internally, and then Lonnie showed me Ewald Cress‘s Twitter profile.

This was turning out to be a week of crazy coincidences.

I told Lonnie that, not only did I know Ewald’s name, but I went to school with him. In a rural town in 1990, where our paths crossed for a very short time.

Just over an hour later, I met up and chatted with Ewald for more than two hours, discovering many common interests.

I made a lot of new friends this week, and I think my favourite one was a person I’ve known for 25 years.

Thank you, PASS. Thank you, Lonnie. Thank you, Ewald.