Here’s an overview of the event from the Dativerse call for speakers:

I’ve submitted a session, but I’ve mostly done that because I want LOTS of people to submit sessions. I can now say, “I submitted a session and I think lots of others should, too.”

Here’s why I’d love for many others to submit: I am a white, cisgender woman who is married to a man. I’m also at a point in my career where I’ve established a strong network. While we have a long way to go to reach gender equality in tech, I also recognize that I am relatively privileged due to my race, orientation, social background, and my connections. I feel it’s important for me at this time in my life to get better at helping others rise up.

I’ve also found in my life that the most exciting and productive teams I’ve worked on have had team members from a very diverse mix of backgrounds, experiences, and preferences. I strongly believe that having a more diverse, open, and welcoming community is good for everyone who is part of that community.

So I would absolutely love it if you helped spread the word far and wide about this event, and encourage others from a rainbow of backgrounds to apply. My hope is that there will be so many sessions submitted from people of diverse backgrounds that my session is not chosen, and I can support the event in another capacity.

Please share and encourage others see if Dativerse is a good fit for them at this point in their career.

I’ll be giving a 10 minute lightning talk at the upcoming Mental Health and Wellness day event on May 7, 2021, hosted by the Data Platform WIT group.

My session is “Maintaining Balance while Re-Training in a new Professional Area”. It begins at 1:10 PM GMT / 9:10 AM Eastern.

I am inspired to talk on this topic because I’ve shifted my career a few times:

• From Tech Support to Database Administrator
• Then to consultant and small business owner
• Then to Developer Advocate
• And now I’ve moved into a Product Manager role

These kinds of career shifts are common and healthy in the modern workplace. Moving into a new role often requires quite a bit of new learning at a fast pace, though, and for some of us this can be quite stressful. I’ll share the top lessons that I’ve learned to set myself up for success and protect my health when taking on a new professional challenge.

If you can’t join me live, I believe my session will be recorded and made available on YouTube afterwards if the technical stars align.

There’s many more great sessions in the lineup– more info over on the Mental Health and Wellness day event page.

Amid the global pandemic, online tech conferences have had a natural surge in popularity. I’ve attended quite a few of these in 2020 across several technical areas (database tech, DevOps, privacy, tech research). The conferences have been both large and small, both free and paid, and have been held across a few different regions and time zones.

Many things have gone well. Organizations who are used to doing in-person events have worked hard to quickly adapt to online platforms, and I’ve seen some fantastic content. This is terrific!

But there are a few critical things that haven’t adapted well in the transition to online events. I’ve seen some major problems occur consistently across all these tech conferences. This isn’t due to laziness or lack of trying — it’s simply that some patterns that work at physical events don’t work well at online events.

We are learning as we go.

With creativity we can massively improve the experience of online events — and that’s really worth doing! Please help brainstorm and share your own ideas about how to make online conferences more awesome.

Why it matters: if you care about inclusivity, improving online conferences is VERY important

My favorite thing about online conferences is that they are generally far more accessible and open than in-person events.

Travel and hotel costs for in-person events are often significant, and only those who are already in quite Senior roles typically have the benefit of these being paid by their employer. Ticket prices for in-person conferences are also generally higher, and less privileged community members similarly have a more difficult time getting these funded, often needing to pay out of their own pockets.

I also believe that low cost and free online events may create more open communities, as there is relatively low risk for someone new to the community to try out the event for a short time. Online spaces may feel safer for members of marginalized groups in terms of testing the waters to find out what a community is all about. This can result into bringing fresh ideas and many new people into the community, both in terms of those who are new to working in tech and in terms of those who specialize in other areas.

For these reasons, even if we are able to get back to in-person events quite soon, I have hopes that online tech events will continue to be a stronger presence than they were prior to the COVID-19 pandemic.

To do this, we need to solve some important problems about funding community and professional conferences, as well as improve the participant experience.

Problem 1: Sponsors are isolated because Expo Halls don’t work online

Most community and professional tech events, both free and paid, rely on sponsors to lower costs for attendees. Platforms for online events and staff to help organize and carry out the event cost real money, so this is still incredibly relevant for online events.

I’ve seen event after event attempt to replicate the ‘Vendor Expo Hall’ of an in-person tech conference. And at event after event, I have seen minimal to no engagement — I’ve yet to see or hear about ‘Vendor Booths’ working in an online format. After all, there’s no good online way to replicate the ways that events have made the Vendor Expo Hall more fun at physical events: free toys and swag, tasty food and drinks, games and magicians and entertainment which can take place right next to personal, real-world conversations.

Here are some things I’ve seen which didn’t work well. If you’re going to try them, change them up a bit to avoid known pitfalls:

• Vendor happy hours at the very end of the day – an in-person happy hour sponsored by a vendor with refreshments served works well sometimes at physical events — often at these, attendees prefer to relax with others rather than return to an empty hotel room or sit in traffic during a busy commute time. At virtual events, everyone has screen fatigue, there’s no refreshments, and only one person can talk at a time. Plus, attendees would probably rather be spending the evening with their families.
  • Idea: work more of the events suggested below into the ‘daytime’ conference schedule itself.
• Vendor lunchtime hours when you provide competing entertainment – It’s great to provide lunchtime entertainment. But if you’re not otherwise working sponsors into your event schedule, don’t ask them to compete with paid entertainment at lunch.
  • Idea: bring them into the lunch entertainment in some ways (and also work them into the event schedule in other ways).
• “Visit a vendor booth” scavenger hunts – I’ve seen some conferences do simple code scavenger hunts to try to keep the sponsorship “exhibit” area in their platform from being a dead zone. I’ve yet to see or hear about this working, unfortunately. Some attendees snag the codes and leave, many ignore it altogether.
  • Idea: make the contests more about meaningful interactions. More on this below.

Problem 2: Conference participants have a hard time making meaningful connections

I’ve been part of conferences which have worked on building engagement in quite a few ways, which is great — but none have been great at helping participants create meaningful connections with speakers, each other, and sponsors. Here’s what I’ve seen tried:

• “Chat roulette” style platform features: Several online conference platforms have decided that attendees want to experience the most stressful parts of online dating in a technical conference, and offer people the chance to be paired up with a random stranger for a few minutes on a video call. I haven’t seen this work well: attendees don’t seem tempted to join, and the platforms often seem to be error prone and not great at managing this.
  • Idea: I have some suggestions for regular small-group gatherings and interactive workshops below — and there are ways to work sponsors into this as well!
• Slack/Discord chat accompanyments: Several online conferences have offered (or attendees have created) social spaces where people can chat along with the event. While I think these can be a great back-channel for some and these can fill some needs for Q&A, for many these are overwhelming. They also become insanely busy very quickly if the conference drives a lot of people to them. So while they fill some needs, they don’t help most people feel like they made meaningful connections.
  • Idea: keep evolving this model, but don’t rely on it for meaningful engagement.
• Presenter Q&A during a ‘live replay’ of a pre-recorded session: I personally really like this model as a presenter — it’s nice to know my session is safely pre-recorded and to be present to live chat with attendees in a text box. However, for many attendees it is hard to follow both a live chat window and a pre-recorded session that goes on simultaneously! While one may get a question answered, one leaves the session feeling a bit frazzled — rather the opposite of having a meaningful connection.
  • Idea: Continue leveraging pre-recorded sessions if that helps you ensure a more consistent experience for your attendees, but allow time in your schedule for live Q&A after the session with the presenter, ideally with a webcam. They can recap chat that happened in the session, answer new questions, and reflect on how their session connects with other sessions at the conference.
  • Another lesson learned: for your speakers, it’s critical to set expectations very early (such as in the call for sessions itself) about whether pre-recording will be needed, and what due dates and expectations you have for recording quality.

Problem 3: Conference participants suffer from screen exhaustion and Painfully. Long. Days.

Most conferences have kept similar time frames and flow of sessions as they’ve moved into online events. However, the attendee experience is different: there’s no physical movement between sessions. There’s no change of scenery. And it’s often even harder for participants to disconnect fully from their work: they end up checking emails frantically during breaks, in early mornings, and in evenings.

Ideas: Mixing up “types” of sessions between more passive learning and interactive sessions will help a bit (but not do the whole job)

• Experimenting with different “tempos” for online event days is important for conference organizers.

• Experiment with more partial-day events rather than only full-day sessions. Could an online platform even allow you to have an event across more than one week?

Part of this is mindset for the organizers: resist the urge to believe that the selling point of your conference is a relentless bombardment of information across multiple tracks. Focus on maximizing the value participants will take away instead, which means helping them absorb information and have some time away from the conference screen.

Those are the three biggest problems I’ve seen.

Now for the fun part: ideas on how to make this more sustainable, more rewarding for participants, and more awesome!

Ten ideas to make online conferences more awesome

These are numbered, but not ranked.

1. More Birds-of-a-Feather Chats, and Work In Your Sponsors – One thing I’ve seen that worked very well was mixing regular time periods into the conference schedule where conference attendees were invited to join live small-group discussions led by a moderator. Multiple discussions on different topics were live at each time. These were held in a format that allowed attendees to share webcams and microphones. Moderators came prepared with a list of questions and topics to spark the discussion.

This helps attendees have time for more meaningful engagements. The difference in format between this and a more passive learning session provides a needed change of pace. And you also have the option of inviting your vendors to participate in these, particularly the topics which are related to their mission and areas of interest. (This doesn’t necessarily mean doing demos or selling products, but rather being part of the discussion.)

2. Workshops, some Sponsored – I’m not going to lie, workshops ain’t easy to do well, whether in-person or online. But these are absolutely worth experimenting on, as when they are great they are amazing for your participants. Some notes:

• Since this is hard, allow plenty of planning time, start with shorter workshops with fewer tech requirements, and do pilots first to find your groove. Build on your experience across subsequent events.
• A key to a good workshop is scoping and selecting the right topics. Don’t fall into the trap of thinking tech workshops need to be all about how an engineer performs a technical task while they follow along in a cloud environment: that’s super hard mode! Start with “softer” topics around professional development or making strategies to effect change in the modern work environment.
• I am personally a fan of the Lightning Decision Jam workshop format, which can work well online with access to resources like Mural . A great thing about this format is that it utilizes non-verbal communication effectively, and there’s a lot of folks who aren’t a fan of talking to a bunch of strangers in a video call. Find some formats you can suggest to your workshop organizers, but allow them to be creative.
• For effective workshops, you need a way to limit the number of people in the workshops, which may require pre-registration to pull off. It also helps to have some volunteers available to switch workshops and balance out the groups if you have some which are low on numbers.

3. Commercial Contests – I know, especially for paid conferences, attendees may not love the idea of seeing sponsor commercials as part of the main conference track. But what if you gamified this for your sponsors, and added a contest for your attendees related to it? This could be really fun.

• Keep the commercials short – 90 seconds to 2 minutes
• Play the commercials live and reveal them throughout the conference, have an emcee announce them and reference the contest each time
• Have multiple categories people can vote on — funniest, best production quality, most thought provoking, most educational, most charming, etc.
• Open the voting at a specific time in the conference once they’ve all been launched, have a YouTube channel where attendees can watch them again in case they missed any
• Encourage vendors to add a URL at the end of their video or to appear at the bottom of the screen the whole time for lead generation purposes

4. Prizes for Meaningful Engagement – As I mentioned above, I’ve seen simple scavenger hunts fail miserably. Just “visiting” a vendor booth in an online world doesn’t make for engagement with the vendor. Why not change the contest up? Your conference platform needs to provide some way of interacting with sponsors, whether it’s by chat room, video chat, or even email options. Ideas to hype throughout your conference to encourage engagement:

• “Best question” contest: allow each sponsor to nominate up to N (whatever number is appropriate for your scale) people who asked the best questions for a prize draw. The winner draw can be done and announced at the end of the conference.
• Sponsor trivia contest: create a trivia game where all the questions are about your sponsors and how their offerings relate to your attendees and their pain points. Source the trivia facts from your sponsors ahead of time, and if you’re doing a booth leverage things in their written materials and live demos. Have good prizes! Emphasize how valuable it is for the conference for contestants to share their email addresses and pain points with sponsors (but have a way for them to specify their contact preferences clearly, as noted below). This is a great “late in your day” session option for near the close of your event. Platforms like https://kahoot.it/ can make the trivia fun. (Note: do some test runs and allow for latency between the game and your viewers.)
• Focus groups, with prizes for attending: Have time slots for 20-30 minute focus group interviews run by your sponsors. Give a small prize (gift cards, stickers sent by mail, etc.) to attendees to who participate in at least N number of focus groups.

5. Help Vendors Learn and Create Leads – Why would vendors want to sponsor community and professional events when they can run their own online events at a pretty low cost and gather all the leads they want? The answer is that vendors want to find new potential customers and to learn about where customers are going in the marketplace.

In other words, conferences can work to help vendors with both short term and longer term goals. Successful vendors understand that building their brand over time is important. They understand that learning about potential customers, their pain points, their perspectives, their goals, is key to their survival. Ideas:

• Vendor surveys: Offer options for vendors to link attendees to relatively short (5 minute) surveys asking attendees about their pain points, interests, goals, etc. A contest for attendees who complete surveys is a potential encouragement for this one if you have interest from a good amount of vendors.
• Vendor focus groups: See the “workshop” section above for more info on this. A focus group can feel more approachable / less intrusive for an attendee, as it’s clear that the point is to help the vendor by sharing experience and that the attendee may not necessarily be ready to buy a product.
• Lead generation is still important. Don’t skip this! Do still work on building and improving the ways in which you encourage attendees to share their contact information with vendors when it’s beneficial for both parties. But add value around that wherever you can.
• Showcase free training resources from sponsors which employees can use after the event: Many vendors have free online resources which can be very valuable for attendees (whether or not they are a customer of that vendor). This is everything from YouTube Channels to whitepapers to online courses. Ask your vendor if they have these, and showcase them for your conference participants in multiple ways throughout the conference — be it on banner adds, downloadable lists of resources, short video clips on the main stage, etc.

6. Ideas to Overcome “Awkward Chat Room” Experiences – I know quite a few folks who hate awkward silences in online meetings. It makes their skin crawl. I know others who feel very shy at speaking up in an online session. Others who can’t stand turning on webcams.

And then there’s the problem of having 12 people in a video room, and everyone starts talking at once, then apologizing. There’s That Person who can’t help dominating the discussion. There’s so many ways that things can get awkward.

And also, let’s face it, video calls get super boring real fast these days. Ideas to make it more interesting:

• Provide non-verbal ways to participate: Apps like Poll Everywhere break up the monotony and allow people to give non-verbal feedback that is immediately visible to the group online (as long as the person hosting the polls/surveys are screen sharing).
  • Other apps like Mural also provide ways to do this, but have fewer controls if you are worried about spammers or rogue feedback in more open events. (Poll Everywhere has some moderation abilities on free text style questions, and you can also stick to other question types if needed.)
• Use Breakout Rooms, if you can: If you’ve got a larger group, apps like Zoom let you split folks into smaller breakout rooms for certain periods of time. This is useful for some workshop formats and focus groups.
• Plan a game or activity: If you’re doing something like vendor chats or happy hours, I think a planned activity of some sort is really helpful. Relying on the guests who show up to provide the topics of conversation is often the cause of the awkwardness.

7. Pre-Recorded Sessions, Live Q&A After – Many conference organizers like to have some or all the sessions at an online event available in a recording ahead of time. This helps them ensure that the show will go on, no matter what technical problems ensue. (See my note above about the importance of mentioning your recording requirements when you do your call for speakers.)

I understand the appeal of pre-recorded sessions at a very personal level, having had my home internet stop working completely on the first day of a three day conference recently. I was working off a series of not-great-bandwidth cellular devices until it was repaired by line crews outside my home EXACTLY as the conference ended on the last day. My sessions were 100% saved by the fact that I’d pre-recorded them.

But as I mention above, it’s a huge challenge for attendees to multi-task and learn at the same time. I’ve yet to see a platform where a presenter could pause a pre-recorded session to handle a question live. Instead, chat and Q&A flows past while the session marches forward.

Part of the solution longer term involves explaining this challenge to conference platforms, and asking them for ideas to solve it. In the meantime, adjust your session timings accordingly!

• If you want to do pre-recorded sessions in 40 minute timeslots, ask presenters to do 30 minute sessions and be available for the session and a live Q&A spot in the 10 minutes after the session.
• Allow webcam in the live period if you can.
• Encourage presenters to come prepared with a couple of things to discuss if Q&A is slow, such as how their session relates to others at the conference, how attendees can learn more, the most common misunderstandings on the topic, etc.

8. Shorter Days, Longer Events – I know event organizers are used to going flat out for a few days and then wrapping everything up. That makes sense when people need to travel to attend your event. But the game is very different with online events, and it’s time to change tempos.

In teaching online courses over the years prior to the pandemic, I learned that shorter sessions spread across more days helped attendees learn more:

• They were able to take care of the most urgent things in their workplace in the hours that they weren’t in class
• They were able to absorb things by spreading learning out over more time
• They were able to literally “sleep on things” — several reported having woken up understanding something more clearly!

This is going to be a big mindset change for organizers in terms of marketing. Conference organizers are used to advertising on being the “biggest” conference with the most concurrent sessions. But with this approach, attendees leave your event feeling burnt out and strangely lonely. Will that bring them back?

A key part of improving attendee experience in online conferences will be experimentation with both session formats and conference timing. Think about how to help your attendees have the best experiences with new scheduling patterns.

9. Plan Carefully for Multi-Timezone Events – Timezones are hard, y’all. They’re hard when planning an event, and they’re hard when attending an event.

If you are an event organizer and are evaluating an online platform, a critical thing to evaluate is how the platform presents times to people who may be watching from different timezones. How easy is it for people to understand when sessions begin and end? It needs to be super easy.

Depending on what technology you have to work with, you may not have a “perfect” option as an organizer. But do your best to try to provide whatever workarounds you can to make your attendees from different time zones feel at home. And express how important this is to the people who make virtual event platforms, please. This is a courtesy your guests will notice!

10. Be Clear — But Not Paranoid or Greedy — About Vendor Sessions – This is a funny one, because it’s more of an issue in some tech circles than in others. In both DevOps and Privacy themed conferences, it’s fairly normal for vendors to give sessions which feature how their products work. The sessions are typically clearly labeled so you know what you’re going to see. It’s not a big deal.

This is a good model for others to embrace. Offer sponsoring vendors the chance to give sessions explaining how their solutions give value. Clearly label the sessions so that attendees know what to expect and can choose accordingly.

This model actually works better in an online conference format than it does in an in-person one. Your sponsors will be highly aware of how easy it is to quietly and quickly leave an online session which is dull or generally unhelpful! There is none of the awkwardness of stepping through a row of people in a physical room to keep people there. The online format means that vendors need to bring relevance and value in these sessions, which is great for both attendees and conference organizers.

This is just the beginning

This is only ten ideas. There are many more! What we need is creativity.

I’d love to see tweets, blog posts, and LinkedIn articles about what you think will make online conferences more sustainable and better experiences.

I have two thoughts overall about how well I’m coping

Thought 1: Wow this is rough

Thought 2: I am incredibly lucky and privileged, and it’s still rough

My pandemic story started with an intercontinental move

At the beginning of March 2020, I moved with my husband and two dogs from Portland, Oregon to Cambridge, England. The virus was in the news at that point, but we had no idea how it would spread and how the world would change.

After nearly ten years of working at home, my intention in moving was that I’d begin to work from the Cambridge office for my employer, Redgate. I’d also periodically travel to visit customers in the UK and Europe.

Things didn’t work out exactly as we’d planned, of course: I worked in the Redgate office for exactly two weeks before the UK went into the first lockdown. I’ve been working from home ever since — at first from a small rental house (which we’d thought we wouldn’t spend all that much time in awake, haha) and now from a more comfortable home which we were very lucky to find and move to after the first lockdown let up.

Looking back, I was lonely as hell for the first few months

I was very happy to have my job and my husband and dogs with me, but I felt very homesick, stressed, and incredibly lonely throughout spring and summer.

But almost everyone was lonely. When I talked to friends at home and mentioned I was homesick, some mentioned that they also felt homesick– and they hadn’t gone anywhere. Everywhere, we were out of place, and worried about safety and health.

To soothe my stress and worry, I relied on habits that don’t scale well: eating sweets, making cocktails, not moving much, and not setting many goals for myself. I was grateful to have a job and that my family was OK, even if they were far away. But I didn’t want to work on myself.

6+ months into the COVID 19 pandemic, I’ve made some changes

Over time, I began feeling more and more sluggish, down, and anxious. I realized that I could work on good habits and help myself feel better, even while the pandemic continued.

In early September I began working on eating better. “Eating better” looks radically different for different folks — for me, from experience the key to eating better is to largely eliminate processed sugar from my diet. If I avoid processed sugar most of the time, I begin enjoying vegetables more and generally make better decisions about what to eat, when to eat, and how much to eat.

I also largely stopped drinking alcohol.

I know. This post is a drag. WHAT IS A PANDEMIC WITHOUT ALCOHOL?

Well, for me, it’s a pandemic where I make better daily decisions about my health. I haven’t stopped drinking entirely — I think it’s OK for me to occasionally have a glass of wine when there’s cause for celebration. But “occasionally” is once every two weeks or less often. I try to avoid alcohol altogether if I’m feeling stressed or sad.

Two weeks ago, I got an indoor cycle for the house and have started up daily spin workouts. I’m also challenging myself to do a bit of beginner yoga and to hold a plank pose once a day for however long I can manage.

What’s made the biggest difference?

Taking on the habit of exercise as a positive coping mechanism has been a massive help. I’m finding that physical exertion is helping me to be less anxious even in stressful times. I also quite like that I can set goals around my fitness, track them, and work to improve.

And I also must say that my job with Redgate and my wonderful coworkers have made a huge difference. Not only is having the security of a regular paycheck a massive blessing, but working with understanding, open, and caring people is something I’m truly grateful for. My colleagues have helped me get through this weird, strange time.

Do I have any tips?

My main tip is that it’s never too late to start working on a good habit.

I didn’t work on building good habits during the first part of this pandemic. But I eventually came to a time when I was ready and able to begin doing that. I don’t win anything by feeling bad about the first part — there’s no point in that. I may as well celebrate what I’ve been able to do, and work on motivating myself to keep going.

Could I have done this with less privilege?

This is a hard one, y’all. Not everyone is lucky enough to have a job, much less one with supportive colleagues. Not everyone has a family who are healthy and able. Not everyone has the time to obtain and cook fresh foods for dinner. Not everyone can afford an indoor cycle. Not everyone has a safe place to take walks outside. Not everyone has their health.

It’s an incredibly tough time. And if all you can do is get by each day, I certainly don’t want to imply that you are doing it wrong. You’re not. We each find our own way through this life the best that we can.

Also, I’ve shared a bit about anxiety before, and to be clear I don’t think that simply “good habits” are a cure-all for anxiety and depression in general. Right now these are what is helpful to me, but I’ve been hugely helped by medical professionals before when that was what I needed.

If you’re suffering significantly from anxiety and depression, I encourage you to please reach out for help and be open to seeking help from a doctor. Please know that you do not need to suffer alone.

In this episode, I share what “Down Tools Week” is, what I’m working on this week, and why I think it’s a terrific experience for fostering creativity, innovation, and teamwork. In closing I share some thoughts on variations of this kind of project which you might use in your own organization, whether you are a developer, DBA, or have another role.

Prefer to listen on the go? Get this episode on iTunes, play the audio file, or find Dear SQL DBA in your favorite podcast app.

Prefer to listen on the go? Get this episode on iTunes, play the audio file, or find Dear SQL DBA in your favorite podcast app.

One small localization correction: the default collation on a SQL Server install will be “the oldest available version that’s associated with each specific locale.” So if your Windows installation has a different localization than mine — I always use English (USA) — then you’ll see a different default collation than the one I discuss in the episode. The main point is still the same: it won’t be the recommended collation for *new* development.

Resources:

• Questions About SQL Server Collations You Were Too Shy to Ask, by Robert Sheldon – https://www.red-gate.com/simple-talk/sql/sql-development/questions-sql-server-collations-shy-ask
• Collation and Unicode Support – https://docs.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support

Prefer to listen on the go? Get this episode on iTunes, play the audio file, or find Dear SQL DBA in your favorite podcast app.

Note: nearly everyone has been inconsistent about this

You might think, “This is easy, just check Microsoft’s documentation to find the answer.”

That’s a great idea, except you’ll find multiple variations of “tempdb” on Microsoft.com:

• This docs page uses TempDB
• This docs page uses tempdb (lowercase even when first word in the title)
• This support page uses tempdb (But “Tempdb” when title case is needed) — most Microsoft support pages seem to follow this pattern
• Bob Dorr, one of my favorite Microsofties who writes on SQL Server, likes to use TEMPDB (all caps)
• Pam Lahoud, another of my favorite Microsoft experts on SQL Server, uses tempdb (lowercase) but TEMPDB (all caps) in her title of this post

So the good news is, if you’re writing for casual usage, there are a lot of different alternate spellings out there, and that’s OK. Most people do NOT use a space and make “Temp DB” two words, but you’ll see variations of that around the internet as well.

However, it can be nice to have a sense of the “most accepted” current spelling and capitalization of a term. This is especially useful if you’re building a tool with a GUI and you want it to look as “standard” as possible, or if you’re writing for a company website.

Here’s my take on what’s won the contentious “tempdb” spelling and capitalization wars.

My recommendation is: tempdb, all lower case, but title cased as Tempdb when required

A few years back, I believe I saw Paul Randal point out on Twitter that tempdb is a real database, and the database is spelled tempdb. This simple argument has persuaded quite a few of us who write in the SQL Server world that there’s no use for any spaces in there, and that the “db” bit shouldn’t be capitalized. (Time has passed and I can’t find the tweet, so hopefully I’m attributing this correctly.)

You will find some variation in capitalization of tempdb on the SQL Skills blog, but Paul tends to use lowercase “tempdb” even at the beginning of a bullet point.

My colleague Kathi Kellenberger is the editor of Simple Talk. On posts she edits, she uses lower case tempdb, but there are exceptions as in when tempdb is the first word in an article title. (This is a good article to look at as a style guide, in my opinion.)

I have also noticed that other frequent writers in the Microsoft Data Platform space have standardized on “tempdb” somewhat, so if you choose this you are in good company:

• Paul White, who has written more deep technical content on tempdb than anyone else I know, uses tempdb
• Erik Darling uses “tempdb” in this new post on SQL Server 2019’s in-memory features for tempdb
• Andy Mallon uses “tempdb” as he reminds you to stop shrinking your tempdb
• As mentioned above, recent Microsoft support pages have largely standardized on this style

I try to be consistent using “tempdb” on this site, and I mostly succeed at this lately. I tend to be massively inconsistent about title casing in general, but I do my best. (Truth: I didn’t even capitalize the title of this post like I usually do, I had to go back and edit it after publishing, haha.)

Thanks to Piers for the suggestion on this post

My colleague Piers is on the SQL Monitor team at Redgate. The team was chatting about how tempdb is so complicated that it’s not even obvious how to spell or capitalize it, and he said, “you know, maybe someone should write a blog post.”

And there’s always room for one more post about tempdb, right?

Learning Git can be daunting for DBAs. In this 20 minute episode, I discuss why learning a VCS is necessary for DBAs, then give three tips on scoping your project, choosing the right tools, and making sure the project is successful.

Resources:

• The cheat sheet I mention in the episode is at https://littlekendra.com/2019/11/27/my-git-cli-cheat-sheet/
• A written version of this content is over at https://www.red-gate.com/blog/the-managers-guide-to-git-training-for-database-administrators
• Here’s the full episode list of Dear SQL DBA

Prefer to listen on the go? Get this episode on iTunes, play the audio file, or find Dear SQL DBA in your favorite podcast app.

Becoming more consistent in sharing my pronouns

My pronouns are “she/her.”

I only became aware of why it’s helpful to share your pronouns in the last year. Before that, I hadn’t given much thought to it. Andy gives a great explanation of why this is helpful if this is something you are comfortable sharing — here is one quote:

For some folks, the obvious pronouns aren’t their preferred pronouns. You share your pronouns to help normalize it so that everyone feels more comfortable sharing pronouns.

Andy Mallon in Hi, I’m Andy. My pronouns are he/him.

I’ve started to edit my various biographies online to include my preferred pronouns. I plan to request that conference organizers include an optional space for pronouns in their slide deck templates as well when I speak at events. This is also an easy thing to include in my Slack profiles and other interactive tools.

Championing anti-harassment policies/codes of conduct instead of being a jerk about them

My inner teenager isn’t so fond of things like dress codes — and for some years I thought of a code of conduct as being pretty similar to a dress code: unnecessary in the modern world. I was very wrong.

I now recognize that harassment and discrimination happens frequently, and that it can easily happen in any group. Harassment and discrimination often impact those who are marginalized in a group the most. However, even those who are well known in a group may have a hard time speaking up when they are victimized or when they see a peer being victimized. Anti-harassment policies help people understand that they can and should speak up, they explain who to approach about the situation, and they also give a framework for the event handlers to follow when working through the situation. I now see that this is very useful and needed.

I also now recognize that anti-harassment policies are important for expressing community values of inclusion. It’s a good thing to talk about what we find important as a group, and to express what works for a community, and what doesn’t work.

This is even good business sense.

“The smartest thing for people who are running conventions is to recognize that in both the short and long run, it’s going to be better for your convention if everybody knows they’re going to be treated with respect,” he said. “If you don’t do that, the younger people, the people who are vital to your field, are no longer going to feel like your convention or conference is a welcome place, and they will create spaces that are more welcome to them. You don’t want to be the ones who are left behind.”

John Scalzi quoted in Why Your Meeting Needs a Harassment Policy

My commitment now is to asking if an event has an anti-harassment policy before I commit to speaking at it, and to also check and make sure that the policy is inclusive of…

everyone regardless of gender, gender identity and expression, age, sexual orientation, mental or physical ability, physical appearance, body size, race, ethnicity, religion (or lack thereof), technology choices.

Andy Mallon in What’s Important in a Code of Conduct?

If it isn’t possible for the event to commit to this inclusion, it may not be the best place for me to spend my time right then. If it is possible for the event to do this, then I should send them positive feedback and help them promote this as part of a healthy community.

These are small things, but I can do them — and the more of us who do them, the easier it gets.

A little bit about the course

What happens when you use NOLOCK hints in your code, or set your isolation level to READ UNCOMMITTED in SQL Server? In 50 minutes of online videos, you will learn what NOLOCK means, why NOLOCK can return incorrect results (and other problems), what allocation order scans are (and how to get them), and other risks and options for reading uncommitted data.

Course lessons include:

• What is NOLOCK? (3 minutes)
• The dirtiest secret: NOLOCK returns garbage data (10 minutes)
• The fast alternative to NOLOCK: indexing (5 minutes)
• Why NOLOCK is a misnomer (7 minutes)
• Bad phenomena that can occur under NOLOCK (2 minutes)
• Uses for NOLOCK: admin queries and garbage data (5 minutes)
• Allocation order scans (13 minutes)
• A common error with NOLOCK (6 minutes)

The course is free, but please donate if you find it helpful

If you find my free courses helpful, show your appreciation by donating to one of the following organizations:

• Black Girls Code – whose mission is to “introduce programming and technology to a new generation of coders, coders who will become builders of technological innovation and of their own futures.”
• The Trevor Project – whose mission is to “end suicide among gay, lesbian, bisexual, transgender, queer & questioning young people.”
• The NAACP Legal Defense Fund – whose mission is to “achieve racial justice, equality, and an inclusive society.”
• The LGBTQ Victory Fund – which “works to change the face and voice of America’s politics and achieve equality for LGBTQ Americans by increasing the number of LGBTQ officials.”

Azure DevOps doesn’t technically allow you to rename branches– but you can work around the issue by creating a new branch from master, setting the new branch as the default branch, and deleting the master branch. Here’s the Microsoft documentation on this topic.

In this video, I create a new default branch named main, then update a pipeline dependent on the default branch name.

Note: if you’re using Git in something else like GitHub, you can do this in a simpler way.

I still make fun of YAML, but I’m not scared of it anymore now that Rob Sewell showed me how to avoid having to write it myself.

In this 11 minute video, I show how to set up a YAML build pipeline in Azure DevOps. My build is configured using a Local Agent, and will be building a database using Redgate’s SQL Change Automation.

To make this easier, I’m using:

• Octopus Deploy Runbooks to organize a series of commands (they have a free tier, FYI)
• Chocolatey to install multiple SQL Server 2019 Developer Edition instances (I’ve written about choco before, it’s also free)

While I’m not at all a fan of “stacking” multiple SQL Server Instances into one Windows Installation in production, it’s fine for this testing scenario.

But I came across a little puzzle when trying to get this to work: when I tried to use choco install or choco upgrade for my second instance, it saw that SQL Server 2019 Developer Edition was already installed and did nothing. There is a –force option which can be used to install additional instances, but the SQL Server Installer will throw an error if you use –force and pass it information for an instance which is already installed. I needed to add a little extra PowerShell to make my script re-runnable and simple move on gracefully if an instance is already installed.

Here is the code pattern I finally landed on for each instance installation in my runbook:

$inst=(get-itemproperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances

if ($inst.Contains('SQL1')) {'SQL1 installed, no action taken'} 
else {
choco install sql-server-2019 -Y --force --params="'/SQLSYSADMINACCOUNTS:domainname\username /SECURITYMODE:SQL /SAPWD:MyNotVerySecurePassword /IgnorePendingReboot /INSTANCENAME:SQL1 /INSTANCEDIR:c:\MSSQL\SQL1'"
}

Thanks to this StackOverflow answer for helping me along.

I’m looking forward to sharing more of my progress on this project as I go!

Thanks to the support of Redgate, I’ve launched a new course which teaches you the basics of TSQL. The course is totally free, no logins required — we don’t even ask for the email address.

Check out the course on Redgate University

The course is here:

https://www.red-gate.com/hub/university/courses/t-sql/tsql-for-beginners

Each week has an embedded video for the course, along with a link to the syllabus and scripts. The videos also have a timeline in case you wish to jump to a particular part of the discussion.

Want to join live?

I’m publishing a new module each week, and we have five weeks to go. There’s a calendar reminder in the course if you want to join me live on Wednesdays at 3 pm BST / 11 AM EST, or you can catch the videos each week and follow along.

Want to learn Query Tuning, Reporting Services, or PowerBI?

Redgate’s Community Circle initiative has other free courses on these topics. Check out the free course list here.

In this post…

• Why do we need query tuning?
• Who needs to do query tuning? Who doesn’t?
• What skills are involved in query tuning?
• Popular built-in query tuning tools and how they work together
• Tough problems
• Is testing in production required?
• Automated query tuning: history and evolution
• Common mistakes and pitfalls in query tuning

Why do we need query tuning?

We’ve seen incredible improvements over the last 15 years:

• Storage speed has massively increased due to advances in both storage technology and network bandwidth, CPUs have become much faster, and prices for memory have dropped dramatically.
• Database optimizers are constantly improving and finding ways to make queries more adaptable and to conquer areas of poor optimization.

Yet there are still professionals who make a good living tuning queries, and training others to tune queries. This process involves finding specific slow queries that are key to the performance of an application and making strategic changes, whether in the code, the database structures, the instance configuration, or something else, to ensure that these specific queries consistently execute with a given speed or to a required standard of performance.

Why?

• Data sizes are also dramatically increasing
• Customer expectations about performance / speed of applications have also risen
• Customers expect to not have to wait for results now – they want to see current status right away

Who needs to do query tuning? Who doesn’t?

Query tuning is done by:

• Database administrators
• Developers who specialize in performance or in databases specifically

Full stack developers don’t generally do query tuning unless they have a specific interest or work experience. This is a specialization rather than a “quick learning” task, so most full-stack developers simply don’t have time, and they need to engage a more specialized person to help. Teams who don’t have a readily available specialist may periodically bring in consultants to help with this.

There are also many database administrators who manage databases where only “basic” availability and performance are required. These databases are used by cost-conscious organizations who don’t need every database to be tuned like a race-car: most of their databases are used by internal users who are used to moderate performance.

What skills are involved in query tuning?

I am not a wizard at TSQL, but I still became pretty good at query tuning. A very unscientific estimate of what skills make someone good at query tuning:

• 65% – the time, interest, and resources (including a network of people to ask) to build an extensive map of “performance trivia” – this is patterns that don’t optimize well, edge conditions where performance goes bad, understanding of trace flags and configuration on TSQL.
• 20% – interest and ability to learn how the database engine optimizes and processes queries using indexes and other resources, and what impacts concurrency as multiple queries run against a live database at the same time
• 15% – understanding of the TSQL language and different ways of rewriting a query to produce a given result set

Popular built-in query tuning tools and how they work together

Execution plans

These are a “map” of how the query is run behind the scenes.

• “Estimated” execution plans show the choices the optimizer has made to run the query, including how many rows it estimates will flow through different parts of the plan
• “Actual” execution plans are estimated plans updated with runtime statistics for things like how many rows flowed through the plan. If the plan is “adaptive” it will contain some information about which options were chosen.

Wait statistics

• Was the query slow because it took a long time to read a lot of data? Or because it couldn’t get a lock resource? Wait statistics help sort this out.
• Some wait statistics are included in actual execution plans as of SQL Server 2016 SP1+, but not all waits are tracked per query due to overhead

Query Store

SQL Server 2016+, all editions

• This feature tracks execution plans and aggregate runtime metrics (duration, cpu usage) along with aggregate wait stats
• This also has the ability to “freeze” plans
• Query Store information restores with the database itself, so it can be shared between environments if desired.

Dynamic management views and performance counters

• These help understand overall instance bottlenecks during the slow performance
• Example: overall wait statistics for the instance and metrics about storage latency during the time the queries performed poorly can help explain if the query really needs to be tuned
  • Query tuning frequently needs to do callbacks to “workload tuning”

SQL Trace and Extended Events Traces

• These are tricky to use for query tuning because it’s easy to slow down your workload and cause performance problems when tracing:
  • Execution plans (filtering doesn’t help in this case, the plans are all examined / collected and the filter is applied too late)
  • Wait statistics (filtering can help here, but the data collected is so massive that you have to be very careful – and sorting through and querying the collected data is also quite cumbersome

Tough problems

Parameter Sniffing

• If a query is “sometimes fast, sometimes slow”, this may be the cause
• For parameterized queries, SQL Server “sniffs” the values supplied for the parameters on first execution. This plan  is reused when other values are provided on subsequent execution unless something happens to cause recompilation.
• An example…
  • The CustomerOrderDetails procedure is initially compiled for @CompanyId= 1001, a tiny customer with one order. A plan expecting a very small amount of orders is generated, which allocates very little memory for sorts and joins.
  • The CustomerOrders procedure is then run for @CompanyId= 128, our biggest customer with a million rows of details about their orders. As the query runs all the estimates are too small, there are inefficient one-by-one reads and memory spills are happening all over the place
  • Someone restarts the SQL Server because “it’s slow”. This causes queries to all compile freshly. CustomerOrderDetails runs for the first time with @CompanyId = 128 and compiles a plan suited to many rows, and runs fast. Nobody understands what happened.
    • In this case the “slow plan” would not longer be in the DMVs. It would be stored in Query Store if that was in use.

Contention

It’s difficult to predict how queries will interact with one another in a live workload

• Shared resources:
  • Query workspace memory – a certain amount of memory needs to be allocated for sorts/ joins/ moving data around in a query. Lots of queries executing at once which need significant workspace memory can cause a problem with this.
    • Sometimes the queries are estimating they need far more of this memory than they need and it will need to be tuned – people are unlikely to realize this outside of a live workload
  • The number of queries doing modification and the approach to locking is difficult to predict outside of a live workload
    • Changes in query plans can cause blocking when it wasn’t present before
• Changes in server resources – even improvements – can cause blocking when it wasn’t present before
  • Example: moving to a new server with more memory and faster CPUs saw an increase in lock waits, because storage waits went down and query execution sped up.

Is testing in production required?

Often, yes. One of the examples of this is parallelism.

Tuning the level of parallelism for a workload, and for specific queries in that workload tends to be quite hardware specific, and you need a live environment.

Workload “replays” are available within SQL Server’s toolkit but they are:

• Time consuming to set up
• Only replays – you can’t “amp up” the activity meaningfully (deleting the same rows 10 times isn’t the same as deleting different rows 10 times)

Automated query tuning: history and evolution

Automated Plan Correction

SQL Server 2017, Enterprise Edition

• Built on top of Query Store
• Detects queries that are sometimes fast and sometimes slow
• Can recommend changes only if desired
• Can freeze plans, test if it helps, and react accordingly
  • Freezing is intended as a temporary fix – it’s recommended that a user evaluate the query for tuning as a longer term fix
• Very good feature for helping identify parameter sniffing

Intelligent Query Processing

• IQP features have been releasing over the last few versions of SQL Server
• Several of these features address common query tuning issues in SQL Server
• Examples:
  • Scalar function inlining: Scalar functions have historically been very poorly optimized in SQL Server, causing slow row-by-row performance
    • This feature “inlines” the functions into the larger execution plans and rewrites the plan accordingly
  • Adaptive joins: Adaptive logic is built into plans in some cases so that not only a “single path” for execution is available in the plan
    • This currently applies to limited scenarios
  • Batch mode execution:
    • Optimizes how SQL Server handles scans of data by processing multiple values at once
    • This originally was available only for queries which reference columnstore indexes.
    • Batch mode for rowstore indexes introduced in SQL Server 2019

Common mistakes and pitfalls in query tuning

Lack of connection between DBAs and Development teams

Over-reliance on traces

Lack of knowledge of execution plans in the team

Overuse of hints

Lack of understanding of SQL Server isolation levels and “optimistic” options

Why do this?

Bad code is really fun — at least, when you write it on purpose. And other people’s bad code is even more fun.

But seriously, I’ve needed to write a lot of bad code over the years when creating demos and reproducing various scenarios. It uses your brain in an unusual way, so it’s a fun thing, but it also teaches you a lot!

How to play

The game starts at 5pm BST / Noon Eastern / 9 AM Pacific today. I’ll be livestreaming the contest here, but you can play whether or not you join the stream. Here’s how it works:

• I will post a challenge on Twitter to write your best worst code that either does a specific thing in TSQL or produces a specific result set when run against a SQL Server
  • I will use the tag #BestWorstCode
  • I will push a “challenge file” describing the challenge to https://github.com/LitKnd/BestWorstCode/
• There will be a 10 minute time limit for each challenge.
  • You can use whatever resources you want to put your solution together, but you must submit your solution within 10 minutes to compete. The solution needs to be in a set of short scripts (it can’t require cloning a whole repo or restoring a database — the most beautiful awful code is succinct)
• Tweet back a link to your BEST WORST sample code using the tag #BestWorstCode.
  • You can save your code anywhere that works, as long as a link works and I can copy it to a clipboard and test it. One easy way to do this is to create a free GitHub account and create a Gist for each sample. But blog posts are fine too, or whatever you like using that is easy to link to.

We’ll do TWO challenges, so the contest will probably take 45 minutes altogether, with time for chatting through different submissions.

You can join for one or both of the challenges. If this is fun, we can do it again sometime soon, too!

What do I need to have installed to compete?

You need three free things:

• A SQL Server (don’t use a production instance, LOL, Developer Edition is free)
• A copy of the pubs sample database (script to create it is here, this is a Microsoft script shared under the MIT License)
• A dream

And truthfully, the dream is optional.

Do I have to play to join the livestream?

Nope. Join the livestream if you wanna chat while you play along, or just chat. You can also play without joining the livestream. It’s all good.

Is there a prize?

You will win the admiration of two corgis and a sense of superiority that will last you all weekend long.

Let’s write some awful code together!

We can make something terrible and beautiful at the same time.

When I first began working with databases, I was lucky to land a job at a little start-up which had solid development and operations processes: all our code, including database code, was in version control. We had a strong database architect who instilled good coding practices into the way we managed database code as well: code was expected to be reusable whenever possible.

For example, there was one set of code which managed error handling and logging for all our databases. It lived in one repository in version control, and was deployed into many databases. This code resided in its own schema, in this case the “EVT” schema. Similarly, there were other bits of “reusable” database code for managing configuration, transferring data between instances, documenting objects and procedures, and other common tasks.

There are huge benefits to establishing reusable pieces of code for your databases

The top five benefits I can think of, are…

• It becomes easy to instill good practices, such as quality error handling, across different teams, without each team needing to learn in-depth about the topic. They simply need to learn how to call the reusable code.
• It is easy to test if core good practices have been implemented, by checking for the existence of the reusable code and whether it is working as part of deployment processes
• It is far more efficient to version and maintain code commonly used across databases by storing it in one repository and creating tests for that code itself
• Those doing production operations gain a consistent way to quickly gather information from databases in production across every environment. For example, 15 years later I still remember that I would run the EVT.LogList procedure to check for error output in databases when diagnosing an issue
• This vastly simplifies writing triage documentation for your databases

But how do you implement this with modern tooling?

Back in the day, our company wrote our own tooling to help us with this, because there simply weren’t any vendors who provided this functionality. Writing and maintaining that tooling required a significant investment on our part.

These days, there are lots more tooling options available (and now I work for a vendor in this area, Redgate Software). However, it can be puzzling to determine how to implement reusable code in modern database tooling, especially “state-based” tooling such as Microsoft SSDT or Redgate SQL Source Control.

That’s because when you do a state-based deployment, tooling of this type compares the object definitions which you have stored in source control with the target database, and generates commands to make the target database look exactly like the objects you have in source control.

This becomes tricky if you are only deploying part of a database, especially if there are any dependencies which exist between the part of the database you are deploying and the rest of the database. If you do nothing, the state-based solution will assume that you want to make the target database look exactly like what is in version control and only like what is in version control, so it will script DROP commands for anything else.

Deployment filters simplify managing reusable database code

How you solve this problem depends on your tooling. Since I work for Redgate, I naturally invest my time in working with our tooling the most — so from here on out I’ll be talking about deploying SQL Source Control projects with SQL Change Automation, which has features which help with this problem.

SQL Change Automation has graphic extensions/plugins as well as PowerShell cmdlets. I’ll refer to the PowerShell cmdlets when describing how to do this, but know that the very same functionality is available in the plugins:

• If your reusable code is in a repository all by itself, you can use the -IgnoreAdditional option to deploy everything from your build package and simply ignore (aka don’t drop) additional objects in the target database
• If your reusable code is in a shared repository (perhaps with other reusable code, perhaps with something else), and you want to build the whole thing but only deploy part of the code (say, a schema), this can be done by specifying a filter file saved from SQL Compare using -FilterPath when you create the release artifact.

In other words, no matter how you are versioning the code you want to make reusable, there is a path towards being able to safely deploy this code in an automated fashion to target databases as needed.

Reusable code at work

There are other interesting patterns which deployment filter files enable. For example, Chris Unwin and I recently worked on a case where we were asked how to make schema based deployments to a large number of production databases as efficient as possible. This can be done with a variety of orchestrators — in this example the use case is to have Octopus Deploy manage the release orchestration.

We’ve just published a 46 minute YouTube video, “Reusable Schema Deployments with SQL Source Control and Octopus Deploy” which gives you a view of this at work. (To be clear, Chris 100% did all the hard work here and I’m riding his coattails!) If you’d like to check out individual parts of this video, a table of content with links is at the bottom of this post. https://www.youtube.com/watch?v=j-X1ePOL1xY

• 00:40 Overview of the problem we are solving: finding the most efficient way to deploy individual schemas (which are each stored in version control once) to a large number of production databases (which each may contain a mix of some or all of the schemas)
• 3:00 A discussion of state and migrations approaches, and why we are discussing a state-based solution with SQL Source Control for this problem
• 5:10 An overview of the two parts of the solution: Release Artifacts are reusable, and deployment filters effectively “ignore” anything that is excluded by the filter (it will not script “drop” commands for them)
• 8:05 An overview of the solution workflow
• 10:32 Demo begins! An overview of the databases used for the prototype environment proving this out
• 14:00 An overview of the build pipeline in Azure DevOps Services which builds SQL Source Control and hooks things up with Octopus Deploy — and a brief excursion where Chris de-mystifies YAML and shows Kendra that dealing with it in Azure DevOps is MUCH easier than she thought
• 22:00 A view of how the prototype environment works in Octopus Deploy (including a view of the lovely aquatic dark theme in Octopus now)
• 24:00 A view of the procedural deployment steps in Octopus Deploy using Redgate SQL Change Automation and an explanation of how steps can be configured to run against certain environments
• 28:00 A view of the filters created for schema deployment, and a demo of how to create them in SQL Compare
• 33:15 A view of how managing “environments” as groups of databases makes it easy to perform deployments of a schema to many databases with a minimum of steps in Octopus Deploy
• 35:02 Time to show it at work! Chris kicks off a deployment
• 36:25 A review of the Release Artifacts created, and the changes and code therein
• 40:39 Summary /recap of the benefits of this approach

Good people write bad database code for many reasons:

• Bad code patterns often look simple and elegant
• Smart people re-use code patterns, so when a bad pattern gets into your code, it tends to spread
• Most people, even smart people, aren’t great at remembering loads of gotchas, especially if they need to work in multiple languages or in multiple areas of specialization

This is why I’m so excited about Microsoft’s new Intelligent Query Processing features for SQL Server: they are upping their game and working to make your existing code run better.

But, with all new things, you may need to be careful.

If you’re curious to know how this can help your code — and where it may not work well — sign up to join us for Redgate Streamed on Friday, April 3. At 4 PM GMT / 10 AM CDT, Kathi Kellenberger will give you an introduction to Intelligent Query Processing and show you examples of where it helps bad code, and where it doesn’t.

This is a free, virtual event. If you aren’t able to make it live, go ahead and register and you’ll automatically get access to the recordings for free.

If you are able to make it live, we look forward to your comments and questions! Hope to see you there.

We’ve got a session coming up in the free Redgate Streamed virtual conference on, “How database DevOps levels up remote work.” When thinking about that session I began thinking about other ways to improve remote work.

While I worked remotely for nearly ten years until recently and have established practices which work well for me , moving to another country and the general chaos and uncertainty right now as the world tries to cope with a global pandemic have increased my feelings of worry and loneliness.

I have found that certain folks in my Twitter feed help me feel connected, combat my worry, and also add some good old fashioned nerdiness to my day. They level up my remote work day!

If you could use a few more connections in your life and would like to get started on Twitter, I highly recommend following these friendly folks who love working with data, and who bring thoughtfulness and happiness into the community:

Cathrine Wilhelmsen, aka @cathrinew

Cathrine loves teaching and learning, and her tweets always teach me something, give me something to think about, or improve my day in some way.

Cathrine’s bio: “SQL/Data geek, Microsoft Data Platform MVP, speaker, blogger and chronic volunteer. Taller on Twitter! Loves sci-fi, chocolate, coffee, and cats. She/Her.”

Mala Mahadevan, aka @sqlmal

Mala constantly learns and shares. She is one of the kindest people I know as well as an honest person, and her tweets remind me that one can confront things and be kind at the same time.

Mala’s bio: ” SQL Server Enthusiast, PASS Regional Mentor-NE region, here to learn and follow all things SQL. My views do not reflect views of my employer. She/Her”

Drew Furgiuele, aka @Pittfurg

Drew loves dogs, databases, and people who are doing their best.

Drew’s bio: “KE8SQL. Former Data Platform MVP turned Microsoft PFE. Co-host of http://doingtheirbest.com podcast. Tweets and opinions my own. The toughest guy in Letterkenny”

Jess Pomfret, aka @jpomfret

Jess is stellar at automation. Her twitter feed also always makes me feel like doing something active, even if it’s just a quick set of exercises in my home office (and as a lazy person I really need this kind of inspiration)

Jess’ bio: “She/Her. SQL Server, PowerShell, Crossfit, Proper Football”

Andy Mallon, aka @AMtwo

Andy provides a lot of dog photos and asks essential questions in polls, like “What’s the appropriate thing to say when you toot?” These are the things that keep me sane. He’s really good at managing databases too and he probably even posts about that sometimes.

Andy’s bio: ” Boston Terror. Database Artist. Dog Dad. Carbatarian. I am a four letter word. Vaccinated. he/him. #Food #Politics #SQL #Activism #TeamOxfordComma“

Steve Jones, aka @way0utwest

Steve lives on a ranch, so he’s got horse photos, ranch photos, and mountain views. He’s also got a really grounded-but-positive perspective on things. He’s thoughtful. He has good ideas. (Proof: he not only founded SQLServerCentral, he also co-founded SQLSaturday events back in the day!)

Steve’s bio is truly minimalist. It is: “Editor, http://SQLServerCentral.com“

Buck Woody, aka @BuckWoodyMSFT

Buck is more than a person. He’s more of an experience. Follow Buck for learning resources, tutorials on loads of things across the data platform and analytics, terrible puns that you’ll still laugh at, and also a lot of kindness.

Buck’s bio is empty.

There are loads of awesome people in the community, and this isn’t an exhaustive list — not by far!

These are simply the people I thought of first today! If I didn’t list you, it doesn’t mean I don’t love ya

I’m giving a session on index tuning at the upcoming Redgate Streamed free online conference. The conference will be held April 1-3 2020, register here and join us!

I love talking about index tuning and I know this area well, so I’m excited to put together this new session. I thought it would be fun to share my process of outlining and creating the session, leading up to the event.

The session title and abstract I wrote this week is…

Index Tuning in an Agile World

Software developers increasingly work across databases and applications, and there are fewer and fewer dedicated database developers each year. This produces a new opportunity for database administrators and developers who are interested in performance: the role of the incremental index tuner. In this session you will learn a framework for developing and tuning indexes in SQL Server, and how to apply this framework in an Agile development context.

Who is my audience? What is my goal?

Whenever I’m writing or rehearsing a session, I remind myself to think about who my ideal audience is. In this case it’s fairly broad: it’s developers or DBAs who care about performance of their code, but they aren’t yet experts at index tuning. The interesting thing right now is that often the people in this audience don’t have all of the information they need to do index tuning by themselves: from the DBA side, they typically have access to the production system but often don’t have access to the codebase, or the design of the queries. From the developer side, they often have the opposite situation: they can get to the code and change it if needed, but they don’t have regular access to production.

My goal in this session is to get all of these people started by showing an example of index tuning, and proposing a workflow in which a performance problem can be identified, a change to indexes can be suggested and reviewed, and the change can be agreed upon and deployed through the environments needed to reach production.

My initial outline

I like to begin putting together sessions with an outline of how I think the session will flow. This outline may change entirely over the course of writing the session, but it feels right to start with it. Here’s what I’m thinking we can cover in an hour:

Intro: the problem space

• Disconnected teams: developers lack production access, DBAs lack access to code and ability to change it
• Both groups often are unfamiliar with index tuning

Learning by example – the goal workflow

• Show an example query with erratic performance
• Demo: How can the problem be discovered by a DBA with production access?
• Demo: How can the problem be discovered by a developer without production access?
• Example: testing different indexes to improve performance in a development environment
• Example: using work items and a pull request workflow to propose index changes
• Gotchas: where can this go wrong?

How to improve your workflow

• What if your database code isn’t in version control yet? How do you get started?
• What if other folks don’t want to change / want to keep things they way they are?
• What about designing indexes from the ground up instead of tuning an existing system?
• References / links for learning Git, branching / merging strategies
• References for index tuning basics and further learning

I’ll be working on this session more next week, so stay tuned to see how it comes together.

I’m grateful that I get to work with others in the community in this topsy-turvy time, and that I have you as a reader. Stay safe, take good care of yourself and your families, and I hope that you are healthy and able to join us o n April 1-3 2020, for Redgate Streamed.

In this 9 minute livestream, I give you a rundown of the event schedule and tells you why I’m excited about each session.

For every registration that attends Redgate streamed, live or on-demand, we’ll donate $1 to the World Health Organization’s COVID-19 response fund (in addition to a separate donation we’ll be making. Even if you can’t join us live, be sure to register and you’ll receive the recordings to watch on-demand, plus you’ll help us raise our gift level!

I’m introducing a series of “learner’s guides” — overviews of a given topic, chock full of links and references. For this first post, the information is based on what I learned when I was part of the team at Brent Ozar Unlimited who put together the original First Responder Kit and built a related consulting practice using those tools.

In this post…

• What is performance triage, and when do we do it?
• Who does performance triage?
• “DMVs” revolutionized the way performance triage is done
• Sources for information in performance triage
• When is performance triage difficult?
• SQL Server Performance Triage Checklist
  • A frequent check: What’s happening now?
  • Phase 1 – look for big gotchas
  • Phase 2 – Narrow down: is it the infrastructure, is it the workload, or is it one query?
• Common mistakes and pitfalls
• How is this evolving, and what does the future look like?

What is performance triage, and when do we do it?

Performance triage is done when a production database is suspected to be the cause of slowness or of loss of availability. Many times when perf triage is done it’s unclear what the source of the problem is, and the goal is to either identify what is happening in the database or instance to cause this problem, or to rule out the database as much as possible.

In other words, most applications aren’t written with enough built in observability to clearly tell if the problem is in the application layer, the database layer, the network or somewhere else. But the database is often an early suspect in an investigation.

Who does performance triage?

Database administrators (DBAs) have been the main people to do performance triage over the last 20 years. This is largely due to separation of duties: DBAs have typically been responsible for the performance and availability of production databases, while access has been limited to these environments for developers. Often DBAs are part of an IT group which is managed as a separate business unit from that of software development. DBAs also often are responsible for databases developed externally from the company, such as vendor databases.

In recent years, responsibility for production environments has been shifting, at least when it comes to in-house development. As organizations work to be able to deliver new features to customers more quickly in competitive environments, movements such as Agile development, DevOps, and Infrastructure as Code have been shifting roles: it is more common than ever for developers to need to maintain responsibility for their code throughout the entire deployment process, and for Database Administrators to be asked to participate in the development process earlier to help with quality design and testing.

Monitoring tooling also better enables communication to both of these teams, and automation is used to help manage permissions for different environments, allowing access to be granted quickly, but only when it is needed and approve.

“DMVs” revolutionized the way performance triage is done

Prior to the release of SQL Server 2005, “Profiling” using SQL Trace was the most widely used methodology, most often run by the SQL Server Profiler tool.

SQL Server 2005 changed methodologies dramatically with the introduction of Dynamic Management Views and functions, which are commonly referred to as “DMVs”. DMVs hold a rich amount of information regarding the present and recent aggregate performance of a SQL Server instance and its databases. The major benefits of DMVs include:

• Much lighter overhead than tracing
• Data on everything from internal performance of the SQL Server to which user queries have been run and how often

However, many DMVs in SQL Server are cleared when different events occur, such as an instance restarting, a database going offline, a query recompiling, a configuration changing, or memory pressure. For this reason, monitoring tools are very popular and are used to harvest information from DMVs (as well as the sources listed below), store them in a separate repository, and use them to provide historic performance information.

Sources for information in performance triage

In addition to dynamic management views, monitoring tools and folks performing manual triage often reference:

• Operating system error logs. Windows is most commonly used to run SQL Server in production environments, so this is usually the Windows Event log, both System and Application logs
• Windows Cluster Manager logs if Failover Clusters and / or Availability Groups are in use
• SQL Server Error Log – SQL Server maintains its own log of errors
• Virtualization metrics (if applicable)
• Performance counters – SQL Server performance counters can be accessed via Perfmon or queried via SQL Server in sys.dm_os_performance_counters
  • There is some overlap between performance counters and DMVs – occasionally perf counters are either easier to use or hold information not available or easily attainable from DMVs
  • OS performance counters are available via Perfmon and are often relevant as well
• SQL Trace (either Server Side SQL Trace or live SQL Trace via SQL Profiler) and Extended Events (XE) traces – these have more impact on the SQL Server and it’s easier to cause a performance problem with them, but they can be useful when handled with care
• Query execution plans and related query runtime metrics – execution plans may be harvested via DMVs, Query Store, or a trace (warning: these are expensive to collect in traces, even using Extended Events)

When is performance triage difficult?

Performance triage is most difficult when monitoring of the SQL Server either isn’t in place, or monitoring is only being done by a high level “platform” style monitoring system such as System Center Operations Manager (SCOM). This is because a significant amount of critical information about performance is cleared when failovers happen, restarts occur, configuration is changed, or memory pressure occurs.

While “platform” tools sometimes do have specialized management packs to attempt to gather some deeper information, in practice most teams find it difficult to harvest and use this information. For this reason, it’s quite common for teams to use both a platform level tool for high level monitoring across many types of databases, applications, and services, as well as a specialized SQL Server monitoring tool for monitoring databases and instances, both on-prem and in the cloud.

While it is possible to do performance triage in a reactive model and begin gathering information after the problem happens, in this mode you are left waiting for the problem to happen again before you can begin to diagnose it.

If you’d like to see an example of SQL Server specialized monitoring running against a live system, Redgate’s SQL Monitor has an online demo which runs against environments including the live SQL Server Central databases as well as cloud PAAS workloads. (Disclaimer: I work for Redgate.)

SQL Server Performance Triage Checklist

A frequent check: What’s happening now?

Whenever I begin triage, I often start with checking: what queries are running RIGHT NOW? No matter what monitoring system or tooling is in place I like doing this check with the free utility sp_WhoIsActive. I like this because sp_WhoIsActive is a stored procedure you may run against the instance, and like many operations people I like to see information in multiple tools — so a monitoring tool can help confirm what I see in sp_WhoIsActive and vice-versa.

Throughout triage I typically stop and run sp_WhoIsActive multiple times to confirm that the instance is responding quickly and that I don’t see signs that something is going wrong. After all, you never want to make a problem worse by triaging it, and you don’t want to be oblivious to what is going on while looking at logs of past behavior.

Phase 1 – look for big gotchas

When triaging performance I generally want to start big picture and focus on the following sources:

• Windows Error Log – How many times did I forget to check this at the beginning, only to realize there was an obvious error which was either the source of the problem or a major immediate clue? At least 10 times. Do a quick pass on this early on!
• Key virtualization performance counters or checks – Using a hypervisor? Check (or ask the managing team to check) if the balloon driver kicked in when you had the problem, the VM was migrated between hosts, or something else unusual occurred.
• SQL Server Error Log – Have there been stack dumps? Did the instance restart? Are there messages about super slow storage latency? Again, this is always worth a quick pass early on as it can save you loads of time.
• Do we have a ridiculous lack of resources? – Spending extensive time troubleshooting performance on a 1 vCPU instance or one with 4GB of memory is going to cost way more operationally than is worth it.
• I like to use the free sp_Blitz script from Brent Ozar Unlimited (note: I’m biased, I worked there!) to quickly check for the following. Just be sure to get used to the script so you don’t get lost in the details and can find the high level info quickly when you run it. (Again, I like to cross-check this information in a monitoring tool — I’m all about triangulation and multiple tools, though.)
  • Bad settings which can harm performance — super low max memory, auto-close or auto-shrink on databases
  • Signs of “poison wait stats” which can kill performance (I think I may be the person who coined that term! It me!)
  • Signs of super-slow storage from storage wait stats DMVs like sys.dm_io_virtual_file_stats – this DMV stores an average since startup, so don’t jump to conclusions too fast, but sometimes this holds the answer
  • Database backups / RPO and RTO – Although database backups / recovery models are unlikely to be the cause of your performance or availability problem, please do take the time to glance at these: because one problem is that often backups get cancelled when performance is bad in order to lighten load on the system. This can leave you exposed to MAJOR RISKS. You don’t want to be tweaking configuration and skipping backups. Well, you just don’t want to be skipping backups.
  • Major missing indexes – SQL Server records when it thinks a nonclustered disk-based rowstore index will be useful to speed up a query in its DMVs and execution plans. Don’t assume that something in this list is the reason poor performance happens, but possibly this will inform your list of “suspects”

Phase 2 – Narrow down: is it the infrastructure, is it the workload, is it one query?

If I haven’t found a clear direction to go in so far and instead I’ve just ruled out the big gotchas, now I start narrowing in. Hopefully I have either a specific time period that I’m looking at where I know the problem occurred, OR I know it’s an ongoing problem which I can observe now. When it comes to anything that isn’t constant, having a specialized monitoring tool is critical.

If I don’t have a specialized SQL Server Monitoring tool, I need to start sampling and capturing information without trying to put too much pressure on the system. This can be done, but you can’t do everything and once and you have to be careful — so you must do a dance of watching what is running, sampling information, analyzing it, and then moving on to more samples. Data I sample or review in a monitoring tool includes things like:

• SQL Server wait statistics for the impacted time – sys.dm_os_wait_stats and sys.dm_os_waiting_tasks
  • These are an invaluable tool to finding out why SQL Server is slow, but it’s quite an art to use them. You need to know what to ignore, which waits are available in different versions of SQL Server and what they mean, and which waits are more problematic than others.
  • It’s incredibly useful to have a baseline of wait statistics, either collected manually or by your monitoring system. Erin Stellato gives a background on wait stats and explains why here.
• Storage waits / samples of sys.dm_io_virtual_file_stats and / or performance counter information for Physical Disk (Avg Disk sec/read and write, Disk Reads/sec and Writes/sec) for the impacted time
  • How much are/were we using storage when there is a problem?
  • How much storage latency do we have when there was a problem? (What latency is acceptable? As Paul Randal says, everyone has their own idea of what that is — here is his take.)
• Blocking and/or deadlock reports, if available
  • If you see lock waits in the wait statistics, blocking may have been part of the issue
  • If you have a monitoring tool, it will help you analyze the chain of blocking and view deadlock graphs to decode which queries are involved
• Query durations and runtimes in the problem period, along with query execution plans
  • Both monitoring tool repositories and Query Store are rich sources of information for this
  • If you don’t have either of those, you can reference sys.dm_exec_query_stats and related DMVs, but will need to play a guessing game about which of your suspects were running in the problem period, what their duration was then, and what execution plans they had

Common mistakes and pitfalls

The biggest mistake that I’ve seen with this is that folks rely too much on tracing. Not only can tracing slow down the SQL Server, but it slows down your triage process:

• You have to wait for the problem to happen again
• Traces generate a lot of data which are time consuming to sort through and analyze
• You have to be cautious about the amount of things you collect to avoid causing a whole new performance problem, so it’s not uncommon to find that you weren’t tracing the right things to troubleshoot the issue after it occurs, and to need to start all over again

My most common personal mistake is that I’ve forgotten to check for errors in the OS and SQL Server Logs early, and missed clear indications of the source of the problem, wasting loads of time. This is easy to do in a stressful situation even when you have practice, so I recommend having a checklist or runbook for your team to use when triaging.

Another common issue is that not all SQL Server performance counters are useful, and some have persistent myths and misinformation to be aware of.

I’ve found that one of the hardest problems to identify is parameter sniffing. It’s good to be familiar with this in advance if you need to troubleshoot performance in SQL Server. A great resource on this is Erland Sommarskog’s Slow in the Application, Fast in SSMS.

How is this evolving, and what does the future look like?

One of the most exciting pieces of tooling introduced into SQL Server regarding performance tuning recently is Automatic Plan Correction. This requires the use of Query Store and it helps identify when queries are sometimes fast and sometimes slow. In other words it can help with identifying that tricky problem of parameter sniffing.

• You may use this in an advisory mode, or let it test freezing different plans for you in production
• Note that “corrections” are intended as temporary fixes: for long term performance, queries that are “corrected” likely need either query tuning and/or index tuning

Automatic Index Tuning is available in Azure PAAS editions. I’m not crazy about trusting SQL Server’s missing index suggestions — I look at them but take them as a suggestion to look at the related queries directly and do my own analysis, as they’re often oversimplified. ( I also do NOT trust ye olde Database Tuning Advisor.) But automatic index tuning is an interesting feature and it involves an element of testing. I haven’t personally looked into this much, but I’m curious about it, and I can see it working well on the right workloads, probably workloads with relatively small data sizes. Automatic index tuning does present interesting challenges in terms of how teams want to manage schema in source control and what indexes they want to deploy to a new workload if using a single-tenant-per-database architecture.

APIs for Customization from Monitoring tools – “Did that deployment/maintenance/configuration change we did yesterday cause a problem? When exactly did it happen, anyway?” Figuring that out has always been a toughie. One of the cool things that SQL Monitor has done is to add an API for annotations so that teams can track significant events on the graph of performance metrics. This can be automated to happen right away when an event occurs, or it can be used by team members to note times of interest, perhaps when triaging performance and noting times when a problem occurred. As DBAs merge more into platform teams and/or combine with development teams, the use of APIs to both manage alerts and the ability customize how alerts are delivered will only increase, along with other custom features enabling teams to customize their responses to incidents. (Again, I work for Redgate, so I’m biased on my choice of tooling and examples.)

Years ago, I worked on a fabulous team of eight database administrators

We supported more than a hundred developers who worked in an agile fashion. When I first joined the DBA team, we had a shared on-call rotation, but each DBA specialized in a certain area of the environment and regularly met with the development teams working on that area. DBA specializations rarely shifted.

Over time, this created significant stressors

Covering on-call for some areas became quite difficult in times of rapid change. It became impossible for someone to be out of the office for an extended period without delaying releases. When we got to the point that we had to decide whether or not to escalate a critical issue to someone who was attending a funeral, it was clearly time for a change.

To gain flexibility, the DBA team decided to regularly rotate positions

Not everyone was thrilled. We each felt attached to the environment we supported: like a garden we had faithfully tended for years, it was hard to let it go! This made the transition a bit tough, especially at first.

Over time, we found that regularly rotating specializations had many benefits for our group. The most wonderful improvement was that it made it possible for team members to fully disconnect while they were off work, as other team members not only had deep technical familiarity with an area, but also our developers were used to partnering with more of us and everyone could adapt more easily.

Fast forward to today: now I work at Redgate

When I joined Redgate I was intrigued to learn that our Product Development teams run a self-selective re-teaming process across the entire division. Re-teaming just took place at Redgate in January 2020 for the second year in a row. Because it is at a larger scale than eight people, Redgate’s re-teaming model gives people the option to have a say on whether it is time for a change or not (rather than only enforcing rotation).

Self-selecting reteaming is used at Redgate for multiple reasons, which I’ve shamelessly stolen from an internal post written by Redgate’s own Chris Smith to share with you. Reteaming….

• Realigns team size and purpose to reflect the larger organizational strategy for the year
• Gives engineers autonomy based on the principle that people will be most engaged and motivated if they have agency regarding what they work on
• Spreads knowledge, best practices, and innovation throughout teams, as people bring techniques, skills, and insights along with them from their prior work
• Enables personal development
• Reflects the recommendations of thought leaders, who champion this collaborative approach as an increasingly common characteristic of high-performing software development organizations.

Re-teaming encourages good documentation habits

This week I realized something obvious: if you’re a developer who has been working on a productivity tool like SQL Prompt and you move to working on a tool related to version control and automation, there are certain things you wouldn’t necessarily know about your customers. For example, most customers in financial services and healthcare can’t or won’t use SQL Authentication. There are many things like this.

However, this isn’t a blocker to re-teaming. Instead, realizing this helped me understand that I can help improve our customer profiles to make these types of requirements and preferences more clear to everyone.

Reteaming helps surface “tribal knowledge” and help make it clearer for everyone.

Although change can be tough, I believe that we’ll increasingly find reteaming practices popping up in engineering teams everywhere

And in my experience, it’s a good thing.

If you’d like to learn more about Redgate’s use of self-selecting reteaming, check out the “Dynamic Reteaming (with Heidi Helfand)” episode of the Ingeniously Simple Podcast.

ALTER VIEW can be applied to indexed views; however, ALTER VIEW unconditionally drops all indexes on the view.

ALTER VIEW documentation

When I first heard about this behavior, it sounded like a bug to me: why should an alteration like adding a column to a view remove all the indexes?

A colleague of mine at Redgate wondered: is the behavior the same with the new CREATE OR ALTER syntax as it is with just plain ALTER? (Spoiler: I tested and it is the same: CREATE OR ALTER also drops all indexes on the view.)

One note: if you are using Redgate tooling to commit your database objects to version control, never fear: it handles this for you and will automatically put the indexes back on your indexed views if you alter them! This is true both for SQL Source Control and SQL Change Automation. Just be aware that behind the scenes the indexes must effectively be recreated to implement this change, so if large tables are involved in the view definition, deployment time will be impacted.

This behavior makes some sense when I think about how indexed views are implemented

When you create an index on a view (we’re talking views using classic disk-based tables here), it materializes the view — in other words, it stores the data as if it is a table. That data is stored in rows on 8KB pages. These pages can fill up. So if you add a column to a view, just like adding a column which requires a size-of-data operation to a table, the database engine needs to rewrite those pages to add the new column to each and every row (depending on datatypes and nullability, etc etc).

Also, it’s quite possible that when altering an indexed view, the alteration makes the view no longer adhere to the many rules required for indexed views.

After thinking about this, I can understand why if you allow ALTER VIEW, the simplest way to implement that against an indexed view is to make it the user’s problem to ensure that indexes can be re-created after altering a view.

However, I personally wonder if ALTER VIEW should even work for indexed views

This is one of those little niche behaviors that strikes me as problematic. From a user perspective, I’d personally rather that ALTER VIEW simply failed for indexed views and notified me that to change an indexed view, I need to drop the indexes, alter the view, then recreate the indexes which I need. Although that’s more work to me as a user, it helps me understand that this is not necessarily a cheap or low risk operation, and it reduces the risk of me accidentally deploying code that un-indexes a view which might be critical to performance.

However, this ship has sailed: the behavior is documented and established and unlikely to change in the SQL Server Engine anytime soon.

Want to see a demo?

Check out this fantastic video by Erik Darling where he discusses and demos this behavior.

• Sales Engineer – Cambridge, UK
• Pre-Sales Engineer – Pasadena, CA
• Sales Engineer – Austin, TX

As a DevOps Advocate, the Sales Engineering team is one of my favorite groups to work with at Redgate. These folks get to learn all about database DevOps, and they get to show clients the possibilities for how they can better develop and deliver database changes, as well as how to better protect their data in the software development process.

But the Sales Engineers aren’t only intelligent. Our Sales Engineering team is a group of kind, smart people who are just plain fun to spend time with. Whenever I’m on a call or a webinar with one of the SEs, we work as a team and we have a great time figuring out how to solve problems. Joining our Sales Engineering team means joining one of the most respected and well-known teams inside Redgate.

One note about sales at Redgate

Redgate practices ethical selling. Depending on your life experience, the words “ethical selling” may look a bit like an oxymoron. However, I’ve been working with our sales teams at Redgate for more than a year and I can attest that we really do work our hardest to make sure that we only recommend solutions for the client which fit the team’s needs and which will help the client succeed. There’s no “vapor ware” in this job, only helping people find a solution which empowers them to work more effectively as a team, be more productive, and deliver value for their organization more rapidly.

Don’t know DevOps?

If you’re knowledgeable about databases and are interested in developing expertise in Database DevOps (but aren’t there yet), please feel free and empowered to apply! While it’s terrific to find an applicant who is well versed in it all, this is one of those “unicorn” jobs in my experience where strong experience in a few of the needed areas is what is needed, as there’s not exactly a huge pool of people who tick all the boxes.

If you’re interested in the job, please use one of the links above to apply

If you’d like to specify me as the person who referred you on the application form, that’d be lovely — and full disclosure, I might get a bonus if you get hired. If you don’t feel like listing me, that’s OK too, we’d still love to hear from you.

In the past week or so, the Microsoft Data Platform community has begun having a discussion about inclusivity, both on Twitter and across community blog posts.

This conversation began when a member of the community shared their story about being repeatedly mis-gendered and additionally feeling excluded, unwelcome, and hurt at a series of community events.

Historically the legacy SQL Server community has had quite visible support for Women in Technology, but has not shifted broadly/overall to a public focus on larger Diversity and Inclusion issues. There have been efforts to be more inclusive to people of color and members of the LGBTQ community, but these efforts have have not been nearly as visible or “popular” within the community as the efforts to support WIT.

I don’t write this in an accusatory way, only to describe the state of things as I see them. I have not worked to change this, and as a community member I share in the responsibility for the problem I see.

This past week I have seen more care and concern from community members over this issue than I expected

I’ve seen many comments from people saying that they want to learn to be more inclusive, and I’ve also seen folks asking for advice on how to speak and act in ways that will help make everyone feel welcome.

This pattern of asking how to do better is what makes me the happiest, because I do need help to learn to be inclusive by default, and to recognize when I am making a mistake without realizing it. I see that I’m not alone in recognizing this and I appreciate others who are making it a known pattern to ask for help in this way.

Here is an example I thought about this week from my own life

Years ago I began a job as a contractor (V-dash, holla!) at Microsoft. At the time there was a bit of an “outsider” feel to being a contractor, but I didn’t mind, I got to work on cool stuff, and I liked my fellow contractors just as well as I liked the full time employees.

One office over from where I sat was another contractor who happened to be transgender. We didn’t work on the same team, so we were never in the same meetings, but we passed each other in the hallway plenty. I never said “hi.” I never went out of my way to smile. I never said, “hey, do you want to grab lunch?” Looking back, there was plenty of evidence that my coworker felt unwelcome, isolated, and alone, and by doing nothing I simply added to that problem.

What I should have done was to be friendly and welcoming to my colleague, even though we weren’t on the same team. I should have introduced myself. I should have smiled in the hall, even if I’m always super awkward smiling at people I don’t know (insecurity, hi, it me). I should have asked if they liked what they were working on– like I did of plenty of other folks who were on other teams.

If I felt like I was a bit of an “outsider” because I was a contractor, I can hardly imagine how my colleague felt.

I did not realize this in any way until this week.

I’ve learned a lot this week

I can’t go back and redo my past behavior, but I can do better now and in the future. I can ask others to help me recognize when I can improve. I can listen. And I think we as a community can do better as well. I am regretful that someone was hurt, but I am glad that we are having this conversation. Growth is usually not comfortable, but it is important.

When implementing any kind of automation for database deployments, it’s important to implement safeguards for your production environment. This is needed even in the best conditions when team members collaborate well and there is a high level of trust, for a very simple reason: accidents happen easily!

As a person who has accidentally destroyed quite a lot of things VERY RAPIDLY with automation, I am a fan of adding protection around production environments to lower risk for everyone, including myself.

On the other hand, it’s also desirable to empower team members to be able to quickly build and experiment with automation in other environments. It’s best to not lock down more than you need to.

In this post I’ll give a quick overview of three control points which help protect your production databases when working with Azure DevOps Server/Services (or legacy Team Foundation Services). The controls I’m discussing here apply to SQL Server instances where you are deploying changes with self-hosted agents.

There’s more than one way to do these things, so if you’ve got thoughts about your favorite alternatives, I’d love to hear about them in the comments.

1. Control who can create new pipelines against production instances

While it’s useful to let folks create new pipelines and edit them against all sorts of development, test, or QA resources, not everyone should be able to create a pipeline, point it to an existing production database, and deploy changes to it.

One simple way to manage this is to create one or more agent pools for production SQL Server instances and use the security properties on the agent pool to limit who can create pipelines using that agent pool. For those who need to see the progress of pipelines/monitor deployments, there is a ‘Reader’ role available.

This could also be done with deployment groups for parallel execution.

2. Control what the agent can do

When you install a self-hosted agent, you are prompted to configure not only which agent pool it uses, but also which account the agent will run under. If you wish for that agent to only be able to deploy to one SQL Server, you may choose to use an account which you have granted only permissions for that SQL Server instance.

In other words, if you are concerned about the agent being used to communicate with other SQL Server instances across the network in your production environment, you have the ability to control this by using an account which only has permissions granted against the SQL Server instance of your choice.

3. Control what can be done within an existing release pipeline

Pipelines are quite flexible and there are many things you can do with them in terms of gating and approvals. Two very powerful controls which you may choose to configure right away for pipelines with production targets are…

Configure who can run vs edit vs view a pipeline: Pipeline security can be configured to allow people to interact with an existing pipeline in many different ways – for instance some users/groups perhaps should be able to execute the pipeline but not edit the pipeline definition.

Configure approvals within the pipeline: Stages may be edited to set approvals. An example with Redgate tooling is that you might create one stage to create a release artifact for a production change, then another stage which performs the deployment of that release artifact. You might choose to add an approval step at the entry to the deployment stage.

It’s OK to start slowly

If this is a bit overwhelming, remember that you don’t have to automate everything at once, and in fact it’s preferable to have a strong foundation for automation. The first step is to identify the current pain points and constraints for your team and start from there. If your database code isn’t yet in version control, beginning there is very likely the first step. If provisioning dev, test, or pre-production environments is slow and cumbersome, working on automating provision and deployments there will help increase your velocity. But when it does come time to begin automating deployments to production environments, do take the time to examine the available safeguards and implement the right controls for your organization.

And check out the magic of the sqlhelp hash tag, I got loads of answers! So many that I’m actually selecting a few here for the purposes of keeping this post readable. First, John Morehouse , a smartie and a consultant who does a lot of great work in the cloud and on-prem helped me figure out what would be needed for clustering (which isn’t something you can do simply with this version in an Azure VM)…

I also got input from two smarties at Microsoft, Oscar Zamora and Amit Banerjee. (Amit has been my hero since forever so that’s awesome, right???)

And also Dr SQL himself, Glenn Berry chimed in — he is also a consultant and specializes in topics like high availability and performance.

Other folks responded as well. It can be so useful to get multiple perspectives on a topic, and I’m continuously amazed at how helpful folks in the Microsoft Data Platform community are when they can help out with a question.

Thanks to everyone who helped me out!

I had just set up this whole demo environment and its pipelines, so the video shows me troubleshooting and fixing an option or two in Azure DevOps which I hasn’t configured quite correctly — which makes things all the more fun for a livestream. If you want to skip watching me installing ZoomIt in the new demo environment (which I’d completely forgotten– how could I??? I can’t show things without Zoomit!) then start at around 6 minutes.

This week, I’ve been thinking about a scenario which may come into play when an organization is moving away from a monolith architecture toward a microservices architecture

One pattern which can enable this move is to at first increase the use of schemas within the existing large legacy database, and begin to document and understand dependencies between different parts of the codebase. Over time, the goal is to reduce dependencies and to begin to move portions of the database out from the large legacy database and into their own data stores. The schemas help clarify ‘ownership’ of code with minimal initial impact to performance and no need for initial data movement.

Part of starting to clarify ‘ownership’ of code in schemas is to limit who commits code to objects in a given schema

In other words, if team Fruitbats owns a schema which exists for the Fruitbats service, members of the Cheeseweasel team shouldn’t just go making changes to those objects. Instead, they need to talk to the Fruitbats team and discover what the best way is to accomplish what they need. At the same time, it’s valuable for team members to be able to see the code for all objects to support the existing legacy codebase.

I thought about a couple of different options in thinking through this, including things like using multiple projects, using a branching strategy, or figuring out some way to run checks in pre-commit hooks with Git.

Here’s what I would do

After consideration, the approach which I think is the simplest, but which has the most benefits, is to keep all objects in the database in a single project and use Pull Requests (PRs) with automatic reviewers to ensure the right changes are being made.

This is simple to set up with a combination of Azure DevOps and Redgate’s SQL Source Control tooling, because SQL Source Control uses an object’s schema name as part of the file name.

Here’s an overview of the setup:

• A branch policy protects master and requires a PR to complete a merge
• Automatic reviewers are set up
  • Establish a user or group with ownership over each schema, and set up a rule so that if any files in that schema are changed in a PR, the group or user is added to the PR. It can’t pass until they review it (or someone with permission to override makes it so).
  • Since SQL Source Control places the schema name at the beginning of file names, the filter for each schema can be as simple as something like: */fruitbats.*
• Automation can further automatically run a build of code and run tests against the code when the PR is created, which can do things like ensure that new cross-schema dependencies are not introduced

I like this approach because while it ensures that the teams owning a schema will always be in the loop on a change, it is still flexible: there may be times when it is desirable to have a single person contribute changes to multiple schemas. This allows that without the need for permissions to be reconfigured, and it even gives a place for the owners of all those schemas to have visibility and input on the change early in the process.

In other words, this was simpler than I thought

The approach here can be a simple modification to an implementation of the Azure DevOps Release Flow branching strategy I wrote about last week — it’s a way to use feature branches with automatic reviewers. While there are certainly other ways to solve this problem, this workflow fits many teams and is also quite simple to implement and maintain.

When using Git for version control, branches are a huge help when it comes to managing this flow.

But how do you use branches? It’s helpful to pick a strategy. There are many fine Git branching strategies out there, things like GitFlow and GitHub Flow and more — enough that it’s overwhelming to learn about these when you are just starting out.

The strategy that I recommend for folks who are starting out with database DevOps and Git is the Azure DevOps team Release Flow model with dedicated development databases. (Why dedicated development databases? Read more here.)

Why the Azure DevOps Release Flow model fits databases well

• Topic / feature branches provide a place for experimentation
• Pull requests provide communication, feedback, and review — a prime collaboration point for developers and DBAs for many teams
• Code can be merged into the main code line without being deployed automatically (for databases, where rollbacks are often anything but straightforward, immediate deployment isn’t always grand)
• Releasing at sprint milestones using a branch created for deployment often makes the most sense

Further, the release flow model can support both small and large teams.

Look! I drew a picture

There are absolutely exceptions where other models make sense

For instance, for those who have implemented a microservices model with robust automated testing in place, it may be more suitable to do something like a modification of the GitHub flow branching strategy.

But that’s not the case for most folks — most folks have databases which are more complex, with cross-database queries and downstream dependencies galore. For these more ordinary databases, the Azure DevOps Release Flow model is a really great place to begin.

I’ve learned a bit about Git in the last year: I’m now quite comfortable creating and managing Git Repos in Azure DevOps. I frequently do demos with SQL Change Automation and  SQL Source control with Git on Redgate’s YouTube channel, and I’ve published a Git Cheat Sheet for the Command Line Interface.

I love learning Git now: I’m very comfortable with a small set of core commands which empower me to get a lot done and I’m able to regularly add new bits of knowledge into my repertoire.

I was interested in learning Git before, and I tried and failed to get started learning more than once. Each time, part of the problem was that I tried to use graphical tooling which I thought would make the whole thing simpler, but which I found to be difficult to start using – and also prone to quickly getting me into complex situations which I didn’t understand.

A big differentiator to me finally learning Git was embracing the built-in command line interface (CLI).

While command line interfaces are often harder to learn than graphical tools, in Git’s case I feel that it’s the opposite: the CLI helps you learn and focus. Here’s why:

• You don’t need a lot: You mainly need to learn five or six simple, core operations which you will use 90% of the time – for everything else you’re generally going to use search engines and forum entries to figure it out case by case, anyway
• It helps you learn: Mis-type a command? It will list commands spelled similarly.
• Simple add-ins take away the toil:  Most of the pains I’ve had with Git have had to do with forgetting my current branch context, or not wanting to type out a whole command – add-ins like posh-git quickly solve these problems and let me have a simple-but-easy experience

There is still a case when I’m happy to depart from the command line: when it comes to merge conflicts, I’m all about having a graphic tool to help me sort out the mess. I’m a long-time fan of the editor Sublime Text, so I’ve recently been happily working with Sublime Merge (which allows you to try it out for free).

But for everything else Git, I love learning with the CLI.

To follow along with this demo yourself…

• SQL Source Control is part of Redgate’s SQL Toolbelt. It is not free, but we have a free trial. If you don’t need automated build and deployments, you can also purchase SQL Source Control individually.
• The Northwind database is shared by Microsoft under the MIT License on GitHub
• I use the Sublime Merge editor in this demo to resolve the conflict. You can try that out for free at https://www.sublimemerge.com

Take the survey at Redgate.com/DevOpsSurvey.

Along the way, I fell behind. Because these features have steadily come out over a period of time, my brain simply didn’t register them all, or possibly I missed seeing them amid other announcements.

Having realized this, I did a little inventory: here’s my rundown of what I understand to be the current state of ONLINE options. Please feel free to add notes in the comments if I’ve missed things, and I’ll update the post accordingly. And I admit, in researching this post, I didn’t spend time looking at In-Memory OLTP tables … they come up so rarely that I didn’t go there.

ALTER TABLE ALTER COLUMN ONLINE (Disk-based rowstore)

Usage: most often changing nullability or data type. This is slow against large tables, because we’ve got to go rewrite all those rows and hold locks while we do it.

• ONLINE options in Azure SQL DB, SQL Server 2016 +
• Documentation lists the limitations and impacts (search for “as applies to altering a column” to find it in the page)
• Note: there are no WAIT_AT_LOW_PRIORITY or RESUMABLE options for ALTER COLUMN ONLINE

ALTER TABLE ADD COLUMN (Disk-based rowstore)

Usage: commonly done operation in routine software development when adding a new feature. This can be extremely slow when adding a non-nullable column with a default value, read about that in the link below.

• There is an Enterprise Edition feature for adding a non-nullable column with a default value in SQL Server 2012+
• Read about it from Remus Rusanu
• Note: this does not require or allow the use of ONLINE in the syntax. This is particularly tricky because it occurs in Developer Edition and doesn’t occur in Standard Edition

By the way, if you want to nerd out on what happens behind the scenes when you add or modify various types of columns, Remus has another excellent post on that topic.

CREATE INDEX ONLINE (Disk-based rowstore)

Usage: index creation is a common operation for both new features and performance tuning.

• ONLINE options in Azure SQL DB, SQL Server 2005+ (Enterprise Edition required)
• Documentation lists the limitations
• RESUMABLE added in SQL Server 2019
• Note: there is no WAIT_AT_LOW_PRIORITY option for CREATE INDEX

DROP INDEX ONLINE (Disk-based rowstore, clustered indexes only)

Usage: I am very hesitant to recommend this. I am mentioning it here to explain why it is probably not your friend and your attempt to do something ONLINE could end up in a world of regret with a full transaction log.

• ONLINE options in Azure SQL DB, SQL Server 2008+
• From the documentation: “When a clustered index is dropped OFFLINE, only the upper levels of clustered indexes are removed; therefore, the operation is quite fast. When a clustered index is dropped ONLINE, SQL Server rebuilds the heap two times, once for step 1 and once for step 2.”
• But wait, there’s more you should know: because nonclustered rowstore indexes use the clustering key as part of their own definition, this also causes rebuilds of all nonclustered indexes on the table – this can accidentally use a TON of space.
• If you need to drop a clustered index, generally this pattern is the best bet:
  • Drop or disable all nonclustered indexes (monitor for blocking, but a fast operation otherwise )
  • Drop the clustered OFFLINE (monitor for blocking, but a fast operation otherwise)
  • Create new clustered if desired (potentially with ONLINE option)
  • Create or rebuild all nonclustered indexes (potentially with ONLINE option)

If you find a situation where you want to use this, I recommend testing very carefully with a production-like data set before unleashing it on your database.

ALTER TABLE DROP CONSTRAINT ONLINE (Disk-based rowstore)

Similar to the DROP INDEX for clustered indexes, this works with indexes created by constraints. Since Clustered Primary Keys are implemented with constraints, this basically allows you to do the clustered index drop mentioned above for a clustered PK.

CREATE COLUMNSTORE INDEX ONLINE

Usage: columnstore index creation is increasingly common, as mixed workloads / reporting off of transactional databases is in high demand and far more viable from a software and hardware standpoint than it used to be.

• ONLINE option for Nonclustered Columnstore in Azure SQL Db, SQL Server 2017+
• ONLINE option for Clustered Columnstore in Azure SQL Db, SQL Server 2019+
• Documentation
• Note: there are no WAIT_AT_LOW_PRIORITY or RESUMABLE options for CREATE COLUMNSTORE INDEX

ALTER INDEX ONLINE (Disk-based rowstore)

Usage: commonly used to defragment indexes during maintenance operations, sometimes used to adjust fill factor and other index settings

• ONLINE options for rowstore in Azure SQL DB, SQL Server 2005+ (Enterprise Edition required for ONLINE rebuilds. Note that REORGANIZE is an online operation and doesn’t require Enterprise Edition, or the ONLINE keyword, however.)
• Documenation
• ALTER index is used for maintenance and for things like changing properties such as data compression, fill factor, and index padding
• WAIT_AT_LOW_PRIORITY in SQL Server 2014+ (using it requires ONLINE=ON)
• RESUMABLE = ON in SQL Server 2017_
• Single-partition ONLINE rebuild in SQL Server 2014+ (prior to that online rebuilds for partitioned indexes is only available for all partitions)

ALTER INDEX (columnstore indexes) – online by another name

Usage: maintenance operations (some details in the text below)

• Documentation
• “Starting with SQL Server 2016 (13.x), rebuilding a columnstore index is no longer necessary in most situations since REORGANIZE physically removes deleted rows and merges rowgroups. The COMPRESS_ALL_ROW_GROUPS option forces all OPEN or CLOSED delta rowgroups into the columnstore which previously could only be done with a rebuild. REORGANIZE is online and occurs in the background so queries can continue as the operation happens.”
• In other words, for most purposes for columnstore in 2016+, use REORGANIZE when you want REBUILD ONLINE

ALTER TABLE SWITCH PARTITION – ONLINEESQUE

Usage: loading new data or removing data in “sliding window” scenarios

• WAIT_AT_LOW_PRIORITY in Azure SQL DB, SQL Server 2014+
• Documentation (search for “switch”)
• Note: this technically doesn’t have the ONLINE keyword, but the WAIT_AT_LOW_PRIORITY option makes it online-ish (or at least less offline, if that’s a thing)

The other day, I was looking back at an excellent blog post my colleague Jamie Wallis wrote about what Product Marketing Managers do at Redgate. I really like the chart he created which explains how Product Marketing Managers work with Product Managers — what each role focuses on, and where they collaborate.

I realized that my own role as an Advocate can also be hard to understand. I did a podcast episode a while back on the topic — at the time, my role was called “Evangelism” rather than “Advocacy” — but I don’t expect my colleagues to listen to a podcast to figure out what value I can provide.

Why does it matter if people know what you do?

I work in a very collaborative role, but I’m not special: all roles which work with databases are increasingly collaborative. Even if you act more as a service provider than a partner for a group, it is very beneficial for you to help important groups in your business understand the value you provide to the organization. Taking the time to do this helps build your relationships with others:

• They have a way to understand your team’s mission and strategy (rather than guessing based on assumptions or scattered interactions)
• They can see how to best make use of your time
• They have a better understanding of the value you provide to the larger organization — and that will benefit you whenever they talk about your group to others in the company. They will automatically help you connect with others and provide more value.

There’s another hidden benefit of writing this out. It may remind you that your job is a little bit cooler and more important than you realize, by helping you see the big picture.

Here’s an example for my role at Redgate

I asked Jamie if I could borrow and the diagram which he used in his post. He was happy for me to use it. One of the key partnerships that I have at work is with Redgate’s sales team, so I thought specifically of how I work with them when creating this first draft at outlining what I do, and how I can help them.

In this case, the sales team is my audience for explaining how I work, so I didn’t need to outline what they do as much as I needed to outline what I do on my own, and in collaboration with their group.

OK, I made it, how do I share it?

There’s a few way to share things like this which can help:

• If you work in a physical office, print it and post it!
• Share with new colleagues when they join your organization to help them get oriented
• Add to slide decks in regular meetings to help introduce your team (even if you don’t spend time on the slide life, it can be a useful reference in shared PDFs)

By the way, I’m not a team lead

I’m not “in charge” of much of anything but myself these days — and I’m totally OK with that. (Managing other people is hard work!) I still went to the trouble to make this diagram. I’ve shared this with my team for input. I also shared this with my manager and let her know how I’d like to use this internally with our sales teams to help them understand our role (especially new folks coming on board, but also people whom I just haven’t worked with as much yet).

Earlier in my career, I tended to see activities like this as something that a team lead or manager would do. I didn’t really see it as my business.

Now, I have changed, and the world has changed a bit, too. It’s really everyone’s business to identify opportunities like this to represent your work a little better. Whenever you find a chance to do something to help connect to your colleagues and customers more clearly, it’s worth mapping out a little time to take care of that task yourself!

Spoilers:

• The command I show in the video is simply: choco install sqltoolbelt
• Fun fact, you can also upgrade: choco upgrade sqltoolbelt
• And uninstall: choco uninstall sqltoolbelt

The rest of the 6.5 minutes is about related tools you may want to install, and why this is all needed.

Links from the video — check these out while you watch

• https://chocolatey.org/products#foss
• https://chocolatey.org/packages/SqlToolbelt

Gists with sample commands for useful installs for Microsoft Data Platform database folks:

• Aaron Nelson’s List
• Kendra’s List (forked from Aaron’s to track my own preferences)
• Rob Sewell’s List

Video explaining Chocolatey and showing how to install SQL Toolbelt (6.5 minutes)

Transcript

Hello and welcome to this live stream episode.

My name is Kendra Little and I am a DevOps Advocate at Redgate.

Today I’m going to show you something that is a little bit life-changing for me. I have been setting up a new desktop and I’ve got a lot of apps to install, including things like Redgate SQL Toolbelt.

I remembered that I heard recently from my school my teammate Grant Fritchey, who’d heard from Steve Jones (also on our team) that when you’re setting up Windows the Chocolatey package manager can save you a bunch of time. I’d used Chocolatey briefly for setting up some tooling given out by our dev team for some cool preview stuff we’re doing, but I hadn’t really thought about the fact that it could save me a ton of time setting up Windows desktop apps. And it totally can.

So I want to show you a little bit about using Chocolatey to set up SQL Toolbelt.

So far today I have– just using the command line– installed SQL Server Management Studio, Azure Data Studio, Azure Data Studio PowerShell. All of the stuff you see on here, I have just installed using the command line.

I’ve even be able been able to customize installation. For example, I like to put the Sysinternals tools — these are tools like ZoomIt, Process Explorer, and Process Manager from the Sysinternals team at Microsoft— I like to put these in a custom folder. It’s where I always put it. I can actually pass in a parameter for that!

Sample commands for Chocolatey installs for SQL Server DBAs – some lists!

So these are a lot of the commands that I like to use: https://gist.github.com/LitKnd/93d02119cb10cef6992fd1bcddbdc73a

I forked this off a really useful gist from Aaron Nelson who has his favorite things to install with Chocolatey, too.

You can check out the things that Aaron likes to install, you can check out the things that I like to install.

All I did was I create a fork from Aaron so I could customize mine.

How to install Redgate SQL Toolbelt with Chocolatey

I just uninstalled SQL Toolbelt (before we started). Chocolatey is a package manager and it’ll help you both install packages and uninstall them.

Now I’m going to change my command to: choco install sqltoolbelt

This saves you so much time installing packages– because look it’s going out, it’s finding the latest version of the Installer, grabbing it for you, and then starting to run it for you.

Part of the magic here is that you have your list that you can maintain in something like a gist or source control, so you don’t even have to remember what all of the things are that you want to install every time. Then you can simply paste them in.

Then also you don’t have to step through all of the wizards. You don’t have to download them to directory, click Next Next Next Next finish. You will notice that for some things, like SQL Toolbelt it will pop up a window (an unattended install window) for you— it’ll let you know how it’s going. It really depends on how the Installer is written as to how that happens.

Certain things may take a little bit of time– like everything else it can take some time– but this really takes all the work out of it. You can just get it going, start working through your email for the day, or maybe figure out how you’re going to configure your livestream on your new computer and just have everything set up unattended in the background.

I’m really excited for this brave new world. I never thought I would see the day where with Windows it was this easy. We can see over here that SQL Toolbelt has been installed successfully. Freya and I can now move on and get going with our day.

Setup

Download git CLI: https://git-scm.com/downloads

git clone <url>

Clone a remote repo (maybe an empty one) into your local directory. Ref.

git init

If not cloning, you might initialize a fresh repo locally. Ref.

.gitignore file

• I often show people how to use Git with Redgate’s SQL Change Automation. For that tool, you can download and unzip the .gitignore file to the folder where your .sqlproj file lives: https://documentation.red-gate.com/display/sca4/Version+control.
• Another great resource for figuring out what to ignore is http://gitignore.io/

Essentials

git status

Lists modified or added files, with status as to whether they are staged. Ref.

git add .

Stages all modified or added files. You must stage before you commit. Ref.

If you want to unstage everything: git reset  Ref.

git commit -m “this is my meaningful message”

Commits staged files. Ref.

git push

Pushes staged files to remote repo. If upstream branch doesn’t exist, git CLI prints syntax to push to upstream branch with same name as your local branch: simply copy and paste! Ref.

git pull

Fetches from remote and updates current branch. If using feature branches, remember to checkout master and pull after you merge a Pull Request on the upstream repo. Ref.

Branching

git checkout -b features/mynewfeaturename

Switches to a newly created branch (the -b tells it to create a new branch. In Azure DevOps (and some other systems), the “/” means that this branch will be logically grouped into a features “folder-like-thing” and the branch will be named mynewfeaturename. Ref.

git merge master

Merges the contents of another branch (in this case, it’s master)into your current branch. Ref.

git checkout master

Switches to existing branch named master (trunk). Ref.

git branch

Lists all local branches. Ref.

git branch -d features/mynewfeaturename

Deletes local branch features/mynewfeaturename. Only works if you are not in that branch. Ref.

Stashing

git stash -u

Put away / temporarily removes all modified files. The -u means it applies also to even recently added untracked files (not yet staged). Ref.

git stash pop

Unpack your stash. Doesn’t matter if you’re in a different branch from where you stashed. Ref.

Weekly Database DevOps Live Chats – a new experiment on YouTube

Stop by to chat! In this session I’ll be talking about what I’ve learned lately, what’s new in Redgate products, and whatever comes up with those who stop by live.

• Weds, Nov 20th – 9:30 am Pacific – YouTube Link (30 minutes)
• Weds, Nov 27th – 9:30 am Pacific – YouTube Link (30 minutes)

How to Make Your 2020 Monitoring Strategy a Success

Tomorrow, tomorrow, tomorrow! I’ll be chatting with BMW’s Tony Maddonna about monitoring in the real world. (The invite says my name is Arneh Eskandari, but I’ll be subbing in for Arneh as he’s been called away to Jury Duty.)

• Wed, Nov 20th – 8 AM Pacific / 11 AM Eastern – Register

Essential Practices for High Performing Database DevOps Teams

Learn the four essential practices and see demos of these practices in action in this free session.

• Tue, Nov 26th – 8 AM Pacific / 11 AM Eastern – Register

Why the Database is at the Heart of DevOps Success

Join me and William Durkin from Data Masterminds for insights about how to succeed at database DevOps.

• Fri, Nov 29 – 6:00 AM Pacific / 9:00 AM Eastern / 3PM CET – ScaleUp 360 online conference – Register

Managing and Automating Test Datasets for DevOps

I’ll be talking about data with Jeffrey Palermo of Clearmeasure. Join us!

• Weds, Dec 4 – 7:30 AM Pacific / 10:30 AM Eastern – Register

Steve Jones and I had a great time today talking about source control for databases and release patterns for performance and availability in Seattle. We had a great group of folks who asked terrific questions, made thoughtful comments, and interacted throughout the day.

Here are some links and resources from the day…

Suggestion for configuration option to make SQL Server Developer Edition act like Standard Edition

This has been requested by the community for many years, but now is the right time to please vote up this suggestion.

After all, Microsoft just made Transparent Data Encryption as well as a whole slew of other cool things available in Standard Edition in SQL Server 2019. Things have really changed in Microsoft, and your voice matters — voting and commenting helps!

The unnecessary evil of the shared development database

Troy Hunt‘s amazing post from 2011 is unfortunately still as relevant today as ever. Lots of helpful info on the business value of dedicated development environments is here: https://www.troyhunt.com/unnecessary-evil-of-shared-development/

Lock escalation magic numbers

5,000 is a magic number… but it’s not necessarily 5,000 rows we are talking about, because more locks count than you might think.

How to set up a self-hosted build agent for Azure DevOps

This has come up twice in the last two days, so I think a video will be in the works for this! In the meantime:

• An overview of Azure Pipelines agents: https://docs.microsoft.com/en-us/azure/devops/pipelines/agents/agents?view=azure-devops
• If you wish to build and / or deploy to a SQL Server on-prem or build with something other than LocalDB, a self-hosted Windows Agent is likely what you want to set up. It works just fine to set that up on your laptop or a VM if you simply need to learn and test and it’s fine for it to be offline when your laptop or VM is shut down: https://docs.microsoft.com/en-us/azure/devops/pipelines/agents/v2-windows?view=azure-devops
  • I haven’t tested this new Learning Module on the topic, but it looks interesting. Note: the exercise is specific to using a VM in Azure to host the agent — not on-prem.

The “aqua” deployment model

This series of posts on online deployments from Michael J Swart is soooooo good (and the cartoons can’t be beat, either): https://michaeljswart.com/2018/01/100-percent-online-deployments-blue-green-deployment/

My favorite whitepaper and a helpful update – minimal logging FTW!

Not a ton of people in the room knew about the amaaaaazing whitepaper on minimal logging in SQL Server: The Data Loading Performance Guide. This is important when you need to bulk load data fast.

Some guidance changes for SQL Server 2016+, for info on that, check out this post: https://blogs.msdn.microsoft.com/sql_server_team/sql-server-2016-minimal-logging-and-impact-of-the-batchsize-in-bulk-load-operations/

Accelerate State of DevOps Reports (DORA / Google)

Only a few folks in the rooms (whom I referred to as my fellow DevOps nerds with the UTMOST respect) knew about these free online reports which represent the findings from more than 30,000 respondents! This is the work led by Dr. Nicole Forsgren, and it is amazing. The DevOps Research and Assessment Group is now part of Google, download their work here: https://cloud.google.com/devops/

DBATools.io

Some PowerShell steps in our demos featured cmdlets from this wonderful and amazing open source community resource.

Azure Data Studio

Our demo has a narrative outlined in a Jupyter notebook from Azure Data Studio. Please don’t blame me for not using a PowerShell notebook, those were just released!

Resources to download from the day

If you are in need of the resources from the day, download them here (73MB — we use a lot of images and they only compress so much).

If you weren’t at the conference, feel free to download a copy. Please remember, though, that slides contain shorthand to help the speaker deliver a narrative, not full explanations — it’s easy to misunderstand based on slides without having been there. In other words, your mileage will vary, but perhaps you can find inspiration.

Thanks everyone for attending!

I am super lucky to have all these awesome activities lined up, but one thing is clear…

I need to be PREPARED

I will not have a lot of time to react if something goes wrong, such as laptop failure, a critical VM that won’t boot up, poor internet connection, etc. When it comes to my talks, I keep copies of my presentations in the cloud and on USB keys in case my laptop fails– that’s no big deal, as the conference provides workstations that speakers can use which are loaded with PowerPoint. However, demos are a different story.

My old way of managing this was to create screenshots of my demos

I would then add the screenshots to hidden slides in my presentation. If I couldn’t do the demos live or if something weird went wrong, I’d un-hide the slides and start showing screenshots of how it was supposed to work.

The problem: screenshotting is time-consuming. And doing a run-through where you create screenshots breaks up your flow — the constant pausing, screenshotting, and pasting is very distracting. It’s not great demo practice.

My new solution is to record a run-through of the demo, then save it as one or more animated gifs

This means that I don’t need to pause, I can simply rehearse the demo while recording my screen — and I can think about important things, like what are the most useful points to convey about the demo to my specific audience at this event. There is no need to capture sound: if I use the gifs, I’ll narrate them live and talk through what is auto-playing on screen. You can even pause a gif that is playing in PowerPoint by clicking the menu at the bottom left of the screen in a live presentation.

Here is the workflow I use:

• I use Camtasia to record my screen while I run through the demo. I have been a Camtasia for Mac user for years and find it very easy to use.
• I save the project, make sure I have the resolution set to 1080p and the demo is full-screened, and do any simple edits needed (such as small cuts of where I made a mistake that’s not relevant)
• I add a fade transition to the beginning and end of the clip to make it clear where it starts and ends (the animated gif will repeat when open on a slide in PowerPoint)
• I publish the file as a gif (I use frame rate 5) and save it in the file share with my presentation, where it automatically syncs to cloud storage
• I also add the gif file into the presentation itself on a hidden slide

For pretty short demos (less than a minute) I have even found that it’s convenient to show the animated gif and talk through it instead of doing a live demo. This is nice because it reduces switching in and out of PowerPoint when I want to show something quickly — screen switching takes time and can be visually jarring for the audience on many projectors.

One downside of using animated gifs this way

Your PowerPoint files can become quite large. This was also a downside of my screenshot approach, so I’m already used to large PowerPoints. If it is an issue for you, you could simply not add the animated gifs to the PowerPoint and store them and access them separately if needed.

All in all, I’m quite happy with my updated method of “accident proofing” my talks. Long live the animated gif!

I think about Robert when I work on a tough problem that he would have found interesting, when chatting about database nerdery on Twitter, when preparing for a conference or training that I know he would have been excited to attend. I remember him when I get a great new photo of one of my dogs, because I know he would have loved to see it.

When I remember Robert now, I remember how delighted he was to solve all sorts of puzzles, and I remember the sound of his laughter. He helps me try to remember to be kind. To be more helpful to others.

I don’t have a big takeaway or piece of insight from this post. I simply wanted to write something about our friend who isn’t with us anymore, because he continues to make an impact on my world.

Wish you were still here, friend.

It’s just ten days until PASS Summit 2019 begins in Seattle. The schedule is up and there are loads of good sessions. Here’s what I’m putting on my calendar to make sure that I don’t miss it — along with some things that I wish I could attend that I’ll be sure to catch the videos of afterwards.

Monday, Nov 4: “Redgate SQL in the City Summit Seattle” Pre-Con Day – Room 3AB

This is going to be a fantastic day — loads of DevOps learning and sharing. I’ll be talking about implementing and improving Database DevOps with some of my very favorite DevOps and SQL Server specialists: Steve Jones, Grant Fritchey, Ike Ellis, Hamish Watson, and Arneh Eskandari. The great thing about having a list of speakers like this is that you get to hear about varied real-world experiences and different takes on topics, plus there is loads of time to bounce ideas off people. There’s still time to sign up and join us!

Tuesday, Nov 5: “How to Architect Successful Database Changes: Source Control and Release Patterns for Performance and Availability” Pre-Con Day – Room 604

On Tuesday, Steve Jones and I will be talking about development patterns that enable you to frequently release database changes while maintaining high production uptime and minimizing the impact of your deployments on your target environment. This isn’t the easiest thing to do, but it’s increasingly important to understand the methods behind sneaking in database deployments to live systems without causing problems for users — we’ll share what we’ve learned over the course of our careers, as well as some insights into what we see as the future of database development.

Tuesday evening: There is a Women in Tech Happy Hour from 4-6 PM at the Tap House Grill. I’ll be late, but it’s a great event I’d like to make, even if it’s just for 15 minutes. Following this, there is a Welcome Reception in the Keynote room back at the Conference Center. I will almost certainly head back to do a little preparation for my presentation the next day and an early night of sleep, however — after speaking a lot during the day I’ll be tired and my voice and brain will need a rest. (Note: sometimes people feel bad for taking nights off networking at conferences — fear of missing out can kick in pretty easy. But if you need time to chill and recharge, do it!)

Wednesday, Nov 6: Day 1 of the main conference

08:15 AM – 09:45 AM: Keynote with Rohan Kumar. I’ll watch this remotely on PASS TV, as I am presenting a session directly afterward. If you can make it in person, everyone will be walking to the big Keynote Room, follow the crowd.

10:15 AM – 11:30 AM: I’m presenting “Why the Database is at the Heart of DevOps Success” in room 615 (it was 608, but we had a room change). I love, love, love this session and am so excited to share it.

11:30 AM – 01:30 PM: I’m going to swing by one of the DevOps tables at the Birds of a Feather lunch (Dining Hall, 4EF) for a quick chat, then head to the Redgate booth in the Exhibit Hall (4B).

1:30 PM – 2:45 PM: I cannot possibly miss Pam LaHoud on “Improving Availability in SQL Server and Azure SQL Database with Accelerated Database Recovery and Resumable Operations.” She is magic and these new features are magic, too. Room 6B.

2:50 PM – 3:30 PM: Back to the Exhibit Hall (4B) — I love hearing questions and chatting DevOps at the Redgate booth.

3:15 PM – 4:30 PM: The Exhibit Hall closes at 3:30, so I’ll probably be late for this. I’ll try to find a spare seat in Linux Fundamentals for SQL Server DBAs by Hamish Watson in Room 608.

04:45 PM – 6:00 PM: It’s SPEAKER IDOL time! Round 1. This is a really fun series of lightning talks with a fun competition mixed in. I get to be a judge! I can’t wait, I learn a ton from Speaker Idol every year. Be there in room 2AB.

6:00 PM – 8:00 PM: Have some food and drinks on the Exhibitors — and maybe talk to us as well if you like, we’re nice enough – Exhibit Hall, 4B

If my voice isn’t already starting to fail, I’ll head to Game Night at 8PM (Ballroom 6A).

Thursday, Nov 7: Day 2 of the main conference

08:15 AM – 09:45 AM: Keynote with Tarah Wheeler (Keynote Room). Imma try to attend this one in person. I need to make notes for a post-event webinar that I’m doing with Grant, Kathi, and Steve: “What we learned at PASS Summit.”

10:15 AM – 11:30 AM: “Enabling End to End Scenarios with Data Virtualization in SQL Server 2019” with James Rowland-Jones, TCC Yakima 1. It’s a hike over to the TCC building (you can do it all inside the convention center using a skybridge for convenience), but this is something I must learn more about for cross-platform scenarios. And isn’t this a crazy world: James works at Microsoft and he’s presenting on how to use ORACLE AND SQL SERVER TOGETHER! Oh brave new world!

11:30 AM – 01:15 PM: Women in Tech Lunch with LaShana Lewis (Keynote Room). I can’t remember if I signed up for this when I registered, but I’ll find out when I check into the conference. If I didn’t, I’ll attend the Birds of a Feather lunch (Dining Hall, 4EF) and catch LaShana’s talk via recording afterward. Hopefully will fit in some time at the Redgate Booth in the Exhibit Hall.

1:30 PM – 02:45 PM: SQL Server in Containers: Your Next Dev/Test/Integration/Upgrade Server. Grant Fritchey has some amazing things to show and I’m going to tweet all about it. Room 618.

2:50 PM – 3:30 PM: Back to the Exhibit Hall (4B) to meet up with friends at the Redgate booth. Exhibit hall closes at 3:30 again.

3:15 PM – 04:30 PM: I’d love to see Tobias Ternstrom talk about Amazon Aurora: Design Considerations for High Throughput Cloud-Native Relational Databases. Room 3AB.

4:45 PM – 06:00 PM: SPEAKER IDOL – ROUND 2 in Room 2AB. The action heats up in this second round with an all-new set of contestants. A great community session.

I have an early session Friday, so I’ll be heading to dinner with Friends of Redgate and going to bed early. I wish I could go to Game Night at 8PM (Ballroom 6A) as well, though.

Friday, Nov 8: Final day of the conference

8:00 AM – 09:15 AM: I’ve gotta see Batch Execution Mode on Rowstore Indexes by Niko Neugebauer in room 6B. I will have to watch the recording of Niko’s excellent talk because SPEAKER IDOL – ROUND 3 is in Room 2AB. Start your day off with a fun sampler of learning in this final heat of the first round.

9:30 AM – 10:45 AM: I’m presenting “Best Practices for Branching Database Code in Git” in room 6A. WOO HOO!

10:30 AM – 2:00 PM: Exhibit Hall (4B) – I can’t show up at 10:30 unless literally no-body is in the audience of my talk, but I’ll get there as soon as I can for the last day at the booth.

2:00 PM – 03:15 PM: SPEAKER IDOL – FINALE, Room 2AB. A winner will be crowned! Errr… actually I don’t think there’s a crown, but there IS a very cool prize.

3:30 PM – 04:45 PM: I wish I could see Continuous Intelligence… What’s This All About? by Gabi Münster in TCC Skagit 4. Unfortunately I have to jet to the airport to get home to my puppy, who I’ll be missing like crazy all week. I know, excuses excuses.

In this video, Freyja the puppy and I talk about a recent workshop which I facilitated at the IDC DevOps conference in London. We cover:

• 01:45 The workshop methodology: about the Lightning Decision Jam Session, by AJ&Smart / Jonathan Courtney. I think this is a great format, and you may want to use this yourself for a workshop with your team (on any problem-solving topic)
• 05:33 Key findings from our group about how to overcome the top obstacles to Continuous delivery for databases

A transcript of the session is below the video.

Transcript (edited a bit for clarity)

Hi, I’m Kendra Little. I’m a DevOps Advocate for Redgate

What this means is that I get to spend time talking to folks and thinking about the patterns and practices that help people successfully develop and deploy stable changes to their databases, that bring value to the customers of these organizations.

Recently I went to a conference that was really great– it was the IDC DevOps conference in London. At this conference, I really liked the format. In the morning we had a series of presentations, and in the afternoon we had workshops where we talked about different problems and potential solutions.

These workshops were collaborative

I hosted a workshop, but I didn’t get to just get to tell the whole group: here’s how it’s done. That wasn’t the point!

The problem that we were solving in my group was how to overcome the top obstacles to continuous delivery for databases.

What we zoomed in on was the fact that when you’re starting out on a journey to implement Database DevOps, you’re working across silos. You’ve got the database folks generally working in their own silo. You’ve got developers generally working in another silo, and the larger the organization the more complex it can be to establish new processes between these two groups, because they have different ways of working.

Starting out at the workshop, a lot of people at the conference were in different phases of implementing DevOps, and many were early on. So we did this exercise that not everybody was bought into at first.

This exercise is built on a format called the Lightning Decision Jam Session by AJ&Smart, and that itself is based on a great book called Sprint. It’s about design thinking.

Here’s a little bit about how the workshop goes..

You have everyone take a stack of either paper or post-it notes– having something sticky works well though. Everybody privately writes down different potential solutions to how could we make it easy to get people to take on the new processes required for database DevOps.

1. Generating solutions

Everyone generates these solutions. You focus on quantity, not quality. The idea is that everyone should just start generating ideas, because it’s like brainstorming but without the pressure of having other people listen to your ideas. You’re just recording them on sticky notes. You have a time limit for that, and we did about ten minutes of people generating their solutions.

At first some people were like, well, I don’t know what solutions I have for this because I haven’t done Database DevOps successfully!

But the truth is most people in the group had gone through IT changes and seen what worked well and what didn’t. Most people in the group had observed a lot of behavior among their co-workers and seen what’s difficult and what’s not difficult to get people to do. And also a lot of people at the table had gone through Agile transformations for their application tier, and had seen what worked and didn’t work for that. Everyone already had a lot of reference material to work from.

2. Voting

After you generate all these solutions, you come together. Each solution needs to be legible because you’re not gonna defend them, you’re not gonna explain them. What happens is the facilitator puts them up on a board — we just used a wall in the room. You spread them out on the wall and you equip everyone with either voting dots or a pen. Everyone reads silently reads all the solutions and puts dots on the one that any of them that they think would be viable.

Now, you’re not limited. You can vote for as many of these as you want, but the point is that you don’t vote for your own because you already submitted yours– you already kind of voted for your own. Everybody silently votes on these using dots. The idea is not to do a bunch of talk during this, just read them and vote on them.

3. Filtering

Then you do a filtering process. We filtered out anything that had less than three dots. These aren’t terrible ideas, necessarily, these are just things that the group doesn’t think are gonna be the most effective. That narrows down the window of what you have.

4. Effort / impact scale

Then you take down the ideas that pass the dot filter, and you arrange them by the amount of impact they have in the amount of difficulty they’ll be.

This is where having an axis drawn on a whiteboard can come in handy. We just had to imagine the axis ourselves, but ideally you’ve got a vertical access which is the amount of impact a suggestion will have on the organization / the amount it’ll help you solve the problem. The horizontal axis is how difficult it is.

Things that are very far to the left and very high up will be very high impact and not a lot of work. You can also have things that are high impact and a lot of work (top right quadrant). You can have things that are low impact and a lot of work (lower right quadrant). You have things that are low impact, but they are not a lot of work, either (lower left quardant).

We arranged all the ideas collaboratively on this scale.

This led us to identify things that are high impact but not super duper challenging to do.

The findings that the group came up with were…

1. Version control

For the top three things that help people overcome process obstacles to initiating change for continuous delivery for databases, our group found that establishing version control for database code is number one.

Version control is the foundation of so much collaboration between people that you really need to get code into version control and focus on that. This isn’t always the simplest thing in the world, but it’s really a necessarily necessary enabler for everything else.

3. Minimum viable dataset

The group also had a concept of a Minimum Viable dataset. I loved this point: in order to successfully do development work, we need a copy of a dataset that enables progress.

In some cases, if production is a 10 terabyte environment our Minimum Viable Dataset for development might be much smaller. This viable data set may need to replace sensitive data that lives in production with non sensitive data. In some environments it may need to be synthesized. But we need to have this to support this work.

3. Constant communications between teams

The third thing that the group found is this enabler to break down these silos between teams and get database DevOps going is constant communications between the teams. We need to have teams really working together to establish these new processes, and we need to have the teams listening to one another.

Enabling all of these: an executive sponsor

Additionally, in order to enable all of these things to happen, if you can identify an executive sponsor who can meet with the teams on a regular basis and emphasize how this project meets organization goals, it helps these teams to work together.

For cultural change, it’s good for teams to know they are being supported by the organization at large. That is an underpinning that can help this change go through.

This was a really fun workshop at the IDC DevOps conference. Every time I’ve done this exercise, I’ve been impressed at the insights that come from the group– which is always so much fun because at the beginning the exercise there’s so much doubt as to whether the team knows enough to overcome the challenge! But in this case, we certainly did.

Thanks for joining me for this talk. I’m Kendra little from Redgate– and this is Freyja, our local expert on Corginetes who has joined me for this quick video. Bye folks, I’ll see you again in another video soon.

Recently, I was doing a bit of work in Azure DevOps Services, preparing a demo for an upcoming webinar about Redgate’s solutions. (Shameless plug: the webinar is free.) Part of the demo does the following magic, using a branch policy and pull request automation trigger, combined with some of Redgate’s extensions:

• Builds/validates database code
• Creates a lightweight clone of the “production” database (I’m using a copy of StackOverflow, thanks Brent & the folks at Stack)
• Creates a release artifact summarizing the changes that’ll be deployed to the clone, exports it, then deploys the changes

This combination of actions is lovely — reviewers of the pull request have validation that the code builds, they know it deploys successfully, and they can even look at deployment timings. Plus, they have a real environment to review the change.

I was working on improving something simple about my demo: giving the cloned database a clear, accurate name that ties to the Pull Request Id.

Previously, I’d been using the default $(Release.ReleaseId) variable in the database name, but I decided that I would prefer to identify this with the PR number, and for the cloned database to be replaced when re-running the automation for a PR (if code in it changes, etc).

There wasn’t a default variable that does exactly what I wanted

Looking at my options in the default variables, the closest thing to what I was going for is $(Release.{alias}.SourceBranch)”. My alias for my build artifact is _StackOverflow-CI, so I referenced that as $(Release.Artifacts._StackOverflow-CI.SourceBranch).

That outputs a path like refs/pull/34/merge. For a variety of reasons, I don’t want a bunch of forward slashes running around in my database names. But no problem – looking at the documentation, I saw that I can set a custom variable easily in a PowerShell task step in my pipeline. Maybe there’s an even simpler way to do this, but that looked like a fast and easy way to replace those slashes with underscores, so I went for it.

A key piece of info about setting variables in Azure DevOps pipelines with a script

This ended up being a bit time consuming for me, because there’s one important piece of the documentation which I didn’t notice. I skipped to the sample code and missed this:

To set a variable from a script, you use the task.setvariable logging command. This does not update the environment variables, but it does make the new variable available to downstream steps within the same job.

Azure DevOps Services – Variables doc – emphasis mine

Having missed that fact, I struggled with my code for a good while, because I was trying to set the variable and then read it for validation in the same task. That didn’t work, so I thought I wasn’t actually setting the variable properly. Whoops.

Some simple sample code

Let’s say you have a PowerShell script task in your Azure DevOps release pipeline. You set it to “inline” type, and you have the following code in there:

$path="$(Release.Artifacts._StackOverflow-CI.SourceBranch)"
Write-Host "$path"
$path= $path -replace "/", "_"
Write-Host "No problem reading $path"
Write-Host "##vso[task.setvariable variable=DUCKS]$path"
Write-Host "Trying to read $env:DUCKS"

This will produce something like this:

2019-08-14T20:56:19.8412596Z refs/pull/34/merge
2019-08-14T20:56:19.8424967Z No problem reading refs_pull_34_merge
2019-08-14T20:56:19.8431236Z ##[debug]Processed: ##vso[task.setvariable variable=DUCKS]refs_pull_34_merge
2019-08-14T20:56:19.8545546Z Trying to read 

Totally looks like our $env.DUCKS variable didn’t get set.

However, if you add an additional PowerShell script task to the same job, also set to “inline type”, which contains that same final line:

Write-Host "Trying to read $env:DUCKS"

That will do what you expect and produce:

2019-08-14T20:56:21.4502122Z Trying to read refs_pull_34_merge

I guess reading is fundamental

This all makes perfect sense to me, now that I think about it. In my defense, it’s been a loooong time since I’ve thought about environment variables in Windows! I couldn’t tell you how many times I looked at that doc and missed this important detail — it was more than a few times. Hopefully this helps someone else out there who misses the same thing I did.

I’ve been working with SQL Change Automation with Git for a while in Microsoft Visual Studio. Visual Studio contains a lot of integration by default with Git and Azure DevOps, so I’ve been using its graphical tools, for the most part.

The new SQL Change Automation extension for SSMS works with the Version Control System (VCS) of your choice, but this initial release provides only “working folder” support

That means that you set up your version control outside of SSMS, then point SQL Change Automation at your working folder.

This inspired me to start learning the Git command line. I’m not a command line guru of any sort, but it turns out the Git command line is really easy to learn, and I think it’s super fun to use. In this 22 minute video, I give an overview of how to create a fresh repo in Azure DevOps and clone it to your local machine, set up a new SQL Change Automation project in SSMS for the Microsoft pubs sample database, commit changes to source control, work with branches, and stash files away when you need to.

A reference for the Git commands run with the times they are demonstrated is right below the video

00:40 Where to get the code to create the Pubs database
01:00 Creating a new repo in an Azure DevOps project
01:39 Clone the repo to your local machine with: git clone urlfromyourrepogoeshere
03:00 git status
03:30 Create a new SQL Change Automation project and baseline it in SSMS
06:00 Stage the files with: git add .
06:40 Review staged files with: git status
07:33 Commit the files with: git commit -m “commit message goes here”
08:08 Send commit to upstream repo with: git push
09:42 Create a feature branch with: git checkout -b foldername/branchname
10:25 Add a new migration script with SQL Change Automation extension in SSMS
11:36 Observe how non-committed files in working directory act in Git when changing branches
13:04 Put away / temporarily remove “dirty” files with: git stash -u
16:02 List local branches with: git branch
16:11 Switch to an existing branch with: git checkout foldername/branchname
16:17 Unpack / get back your stash with: git stash pop
16:58 Stage modified files in our feature branch with: git add .
17:03 Commit modified files in our feature branch with: git commit -m “commit message goes here”
17:33 Get detailed syntax to push feature branch for the first time to remote repo with: git push
18:07 View repo in Azure DevOps Services
18:22 View branches and create a pull request to merge our feature branch into master
19:05 Complete pull request, note I use an option that deletes the feature branch on the central repo after merge
19:45 Validate that the feature branch still exists on my client machine with: git status
20:10 Return to master branch with: git checkout master
20:22 Clean up my local feature branch with: git branch -d foldername/branchname
20:40 Pull down updated code from master with: git pull

In the 2019 edition, research continues to show that DevOps drives business value: high performers at DevOps are “twice as likely to meet or exceed their organizational goals.” While this isn’t a new finding, it’s very important that this continues to be true: why invest in improving at DevOps if it doesn’t drive business value?

While there are a ton of valuable insights in the report, in this post I will focus in on the findings which I believe are most relevant to those of us who work “close to a database.” There are three very interesting aspects of the research which hit close to home:

1. Speed and stability are not tradeoffs
2. Heavy change processes negatively impact speed and stability
3. Communities of practice are a common and successful tool to transform culture

Speed and stability enable one another

Accelerate recommends tracking four key metrics, in addition to availability. Note that half of these are related to speed, and half are related to stability:

Accelerate explains that the relationship between these metrics is often misunderstood: “Many professionals approach these metrics as representing a set of trade-offs, believing that increasing throughput will negatively impact the reliability of the software delivery process and the availability of services. For six years in a row, however, our research has consistently shown that speed and stability are outcomes that enable each other.” Accelerate: State of DevOps 2019, p17

This is a tricky thing for IT professionals who have been in the field for a while to get our head around.

Another way to put this is that, as Microsoft found, “moving fast with poor quality is as bad as moving slow.” A raw deployment count doesn’t give any indication of delivery of value. If many of those deployments are doing rework and making up for poor quality, that needs to be factored in, because the point is delivering value to customers— hence the importance of stability metrics.

The important thing for IT leaders and data professionals to notice is that the evidence has surely piled up that DevOps techniques are a tool that helps us improve stability: and when it comes to data, that’s typically one of the biggest areas where a company cares a great deal about maintaining quality and stability.

There is a clear action for leadership to take here: moving to DevOps means building a team across development and IT silos, and making the metrics above universal outcomes which the entire team moves towards. You must stop rewarding IT based on stability, and stop rewarding software development based on feature delivery: all groups are working towards the full set of metrics.

Heavy change processes negatively impact speed and stability

IT and database specialists typically act as gatekeepers for production. Traditionally, when things go wrong, heads of IT and their related specialists try to make things better by adding another layer of process to things. This has resulted in complex change approval processes, often including a Change Approval Board.

As much as we like to think that a complex process will make things better by weeding out problematic deployments, Accelerate finds that in our attempt to make things better, we’re actually making it worse:

…we investigated whether a more formal approval process was associated with lower change fail rates and we found no evidence to support this hypothesis, consistent with earlier research. We also examined whether introducing more approvals results in a slower process and the release of larger batches less frequently, with an accompanying higher impact on the production system that is likely to be associated with higher levels of risk and thus higher change fail rates. Our hypothesis was supported in the data.

Accelerate: State of DevOps 2019, pages 50-51

This doesn’t mean that you must get rid of change management. Many organizations simply cannot do that due to regulations, and Accelerate also doesn’t suggest that as the path to success.

But we do need to work towards significantly changing the way that change management and change approvals are done. This includes clarifying change processes to make it incredibly clear to everyone what needs to be done to have a change approved. This also means transforming the jobs of specialists from production gatekeepers into consultants in the software development process: both to build the most effective change process, and to act as peers to review critical changes early in development, and shape the testing strategies for those changes.

Similarly, leadership on Change Approval Boards should shift into more strategic roles, identifying areas where capabilities are needed the most, and providing guidance and resources on how to improve.

Changes like this are not going to happen overnight, particularly in larger enterprises. Perhaps this is part of why Accelerate found this year for the first time that Enterprises, defined as organizations with more than 5,000 employees, comparatively are lower performers than smaller organizations. However, enterprises can change their culture and processes. On a personal note, I am frequently surprised at the massive level of transformation that I’ve observed from my friends who work at Microsoft over the last few years, for example. In an Enterprise situation, the game is about persistence and about seizing promising opportunities to work in new ways.

Communities of practice are a common and successful tool to transform culture

One question I often get from database developers or DBAs is how they can convince developers to change their habits in one way or another. This is sometimes phrased as, “they don’t listen to me.”

Accelerate finds that if you want to change your culture, you’re probably going to need to get out of your silo. They asked respondents to share how DevOps and Agile is spread at their organizations, and that found that “Low performers tend to favor Training Centers (also known as DOJOs) and Centers of Excellence (CoE) — strategies that create more silos and isolated expertise.” Accelerate: State of DevOps 2019, p71

We already have plenty of silos and isolated expertise when it comes to databases, we certainly don’t want to create more.

Instead, tools like Communities of Practice are found to be more effective. 57% of “Elite” performers were found to use Communities of Practice, the heaviest concentration of any single strategy studied.

Communities of Practice build both knowledge and community inside organizations. For IT leadership, it’s critical to encourage specialist groups in your organization to seed and sponsor these groups, and to participate in them as active community members. For specialists such as DBAs, establishing a Community of Practice around database development is your best tool toward transitioning from being a “production gatekeeper” into becoming an internal consultant who shapes how database development is done in your organization — a key contributor and collaborator in the software development lifecycle.

Want more insights?

You can download the Accelerate: State of DevOps Report 2019 today.

Catch a recording of our webinar “What You Need to Know About the 2019 DORA Accelerate State of DevOps Report, with Jez Humble and Steve Jones”.

It can be very useful to enable Continuous Integration for multiple folders in your DevOps pipeline — say, for every branch created under releases/ or features/. But configuring this can be strangely confusing! In this video, I show how to get a custom release branch folder set in your triggers for both build and release pipelines. Then I show a quick test of the automation at work in my demo pipeline.

Spoiler: the fix is to type directly into the filter box and hit enter. It looks like that box is only for filtering existing branches, but you can use it for edits! The fix starts in the video at 1:50 if you only want to see that bit.

After almost fifteen years of heavy usage by developers and database administrators (DBAs), it might seem like Microsoft’s free tool, SQL Server Management Studio (SSMS), is about to go out of style.

After all, SSMS is no longer the cool new kid on the block: Microsoft has shown consistent effort to develop their new tool, Azure Data Studio (the artist formerly known as SQL Operations Studio), since November 2017. Azure Data Studio is built on the modern foundation of Microsoft’s VS Code, whereas SQL Server Managed Studio is related to the legacy Visual Studio Shell.

Based on this overview, it might seem like a new SQL Server DBA or developer should primarily learn Azure Data Studio, not SSMS. And it might similarly seem like vendors should focus on developing new tooling only for Azure Data Studio.

But when you look into the details of how Azure Data Studio is being developed, it becomes clear that SSMS is still just as relevant than ever:

Azure Data Studio really shines where it specializes in different functionality than SSMS provides

While both Azure Data Studio and SSMS each provide an interface to author queries and to execute them against relational database instances, I find that the user experience in Azure Data Studio is often not nearly as smooth as it is in SSMS.

Where Azure Data Studio really shows its value is in unique experiences:

• SQL Notebooks (based on Jupyter notebooks) which offer an experience of “interactive documentation” and more
• The ability for users to connect, manage, and query different database platforms, using tools like the PostgreSQL extension
• The ability for MacOS or Linux users to run Azure Data Studio natively, without installing a Windows client
• The ability to work in other languages, such as PowerShell

Azure Data Studio is being built to sometimes connect with SSMS

One Azure Data Studio feature I noticed recently is “Release of Database Administration Tool Extensions for Windows” in June 2019. Here’s the overview of the feature:

This extension launches two of the most used experiences in SQL Server Management Studio from Azure Data Studio.

Release Notes for Azure Data Studio (emphasis mine)

As a MacOS user, I was slightly disappointed that this is a Windows-only feature — it’s Windows-only because it requires that SSMS be installed, so the SSMS feature can be launched inside Azure Data Studio.

However, I understand where this is coming from: I don’t need every SSMS feature to work natively in Azure Data Studio on MacOS, and I suspect that this saved a ton of engineering work. If Microsoft plans to support and develop both tools long-term (and I believe this is evidence that they do), it makes sense to strategically create these dependencies.

SSMS is still under active development

A major update to SQL Server Management Studio, SSMS 18.0, released in April 2019. This release included many improvements and new features. New features have been regularly added in the versions since this release as well.

This pattern shows evidence to support Dinakar Nethi’s suggestion in his SSMS 18.0 release announcement that we should “think of these two tools not as separate tools doing different things, but as one integrated tool. Each tool has different experiences built into it and can be launched from the other seamlessly.”

Users still care a ton about SSMS – and therefore,  vendors care about it as well

One comment I have frequently heard over the years from developers and DBAs who work with multiple platforms is that they are impressed by the richness of tooling provided for the Microsoft Data Platform at no extra charge. SSMS provides a very rich experience and covers a vast amount of features – and we users very much enjoy it.

For this reason, vendors will continue to build new features for SSMS.

For example, at Redgate, we have just created a major new extension for SQL Change Automation in SSMS, which allows users to author changes in a migrations-first approach to development. We wish to empower teams to collaborate both across Visual Studio and other IDEs, and we recognize that SSMS continues to be the primary tool for Microsoft Data Platform DBAs and many developers – it remains incredibly valuable to offer users the ability to work in SSMS.

Where do we go from here?

SSMS remains the primary tool for SQL Server specialists. Azure Data Studio is a terrific, complementary tool, with strong use cases for cross-platform experiences and SQL Notebooks.

For new database administrators working with the Microsoft Data Platform, it continues to make sense to download and learn SSMS first – but do take a few minutes to install both tools and play around with the SQL Notebook functionality in Azure Data Studio specifically, these can be a great tool for you to document information as you learn.  This is also true for developers who need to perform some operational tasks.

For occasional users who only need to connect to existing instances and run queries, either SSMS or Azure Data Studio is a fine choice.

For odd users like me, who generally run in a MacOS or Linux environment, and who periodically do more in-depth performance tuning and database management tasks, it’s especially valuable to become fluent in both tools. I love using Azure Data Studio for its Notebook experience and running occasional queries against SQL Server running in Docker on my laptop when appropriate, and only firing up a Virtual Machine to use SSMS when I need a more in-depth experience.

Like a lot of developers and database administrators, I do a fair amount of short-term problem solving during the course of my normal work week. I get to join some Redgate sales calls, often during Proof-of-Concept exercises, and this frequently involves helping brainstorm about the best way to use our tools to solve a specific need for monitoring, database development, or automation. I also pick up customer questions from the #Redgate channel in the SQL Server Community Slack and answer forum questions for Redgate when I can.

Inevitably, when you do a regular amount of troubleshooting and brainstorming with customers, you start to notice patterns and have ideas about how to make things easier, faster, or better in some ways. Sometimes this is an idea for a new process or a new product, or it might be a big change to existing processes or products.

The challenge is getting it to happen, especially when doing so requires time from your coworkers.

I’m getting close to my one year anniversary at Redgate, which caused me to pause and think critically about the biggest patterns I’ve seen with one of my favorite Redgate products — and about some changes I would like to see to make that product even more awesome. (I’m not going to tell you what the product or the idea is in this post, by the way. The point is how I’m trying to get the idea done.) I realized that this idea is pretty important to me, and I would really like to see it happen– but it’s not a simple or easy thing. So I started to think about what I could do to try to cause some change to happen in this area.

Saying, “hey, we should do this thing!” usually isn’t enough

Occasionally, you can successfully initiate change simply by dropping the right idea into the right brains at the right time. But this is pretty rare, in my experience. Usually that “right time” described is when you can clearly and quickly show that the change will either result in a large amount of revenue immediately, or avoid a disaster which is provably imminent.

For anything else, even amazing ideas risk falling by the wayside. There are a lot of great ideas out there, but typically all our coworkers and managers already have a direction set for the next few weeks or months, if not longer.

It takes more than mentioning an idea to get it prioritized, much less actually done. So we need to put in more work to give our great idea a better chance at happening.

Think about the way your company likes to communicate

Company cultures vary quite a bit. If you work at a startup, you’re going to pitch ideas quite differently than if you work for a government agency.

At Redgate, I’ve learned that people love writing and reading. You can see this about us externally by browsing Simple Talk, our Product Learning articles, and even our product documentation. We also internally use video quite a bit — we share video for live meetings and demos, and often share recorded meetings for distributed teams and learning afterward.

For my pitch, I decided to focus mostly on writing, but to use a bit of recorded video as well. I wrote a paper about the change I was advocating, and why it should be done.

Here’s what I included in the proposal.

Give an executive summary

My proposal ended up at around 11 pages, including the table of contents. This is fine for my audience– like I said, Redgaters like writing and research. Even so, not everyone has time to read 11 pages. Even if they do have time, you should start with a pitch about why they should spend the time with your proposal.

That’s what an executive summary does. Give a short, high-level, one-paragraph overview of what the problem you’re solving is, the impact change will have on your organization, and a suggested course of action.

Explain the problem, and talk in dollar signs whenever you can

The next thing I did was detail the problem I’m solving: what thing or things could be much better? How does my idea relate to those problems?

In my experience, the more you can quantify the opportunity you see in revenue, and the more specific you can be about that, the more of a response you will get.

In my case, I explained this in three steps:

• The current experience, and why/where it could be improved
• A list of customers who asked for something related to my idea, or who could / would have benefitted from what I am proposing
• Details on how competing tools measure up in this area

This wasn’t something I did in five minutes — it took several research sessions. I dug through my email and identified some customers that way, and also asked colleagues for examples they remembered. I searched through Salesforce to identify more examples, and also to be able to quantify the size of deals which might be accelerated or created if my idea was implemented. I also did research on competing tools.

Be open to other solutions

Possibly the hardest part about this whole thing is to not get married to your idea.

Yes, you’re proposing a specific thing. But the really important bit is actually bringing focus to the problem or opportunity that “thing” addresses. There may be ways this can be approached more quickly, or ways that that address it more thoroughly — possibly those things are even more awesome.

Because of this, I didn’t spend too much time locking my proposal down to the details of the exact thing I’m recommending. Instead, I wrote more about the types of functionality that I am proposing, and what customers have told me they want to be able to do quickly and easily — along with some notes about why I believe this is all humanly possible.

What happens next?

Redgate has a quite open culture. Earlier this week, I shared my proposal out with my team and a few coworkers. Yesterday, I shared it with the stakeholders for the products involved in my idea.

Today, I got a meeting invite to talk through things with the product teams next week.

So you’ve got a meeting. What if nothing happens?

As much as I like my idea, it’s not something that’s likely to happen overnight. It’s not a small amount of work, and there’s also other awesome things in the works.

So, even if my idea gets prioritized, I don’t consider my proposal to be “done.” I plan to add more customer use cases to it over time as I come across them, and also to regularly ask other people at Redgate if they have new ideas in this problem space that would make a big difference. Since the bulk of the proposal is written, this maintenance piece is not particularly hard to do, and it will keep my proposal fresh — just right for the exact moment when the stars align and it’s the right moment for this change.

This isn’t the only way to inspire change

The approach that I described here is one that I’ve tailored to my situation and my audience. There are other ways to do this.

For some ideas, you may be able to create a working prototype or proof of concept of what you’re talking about — even potentially put it to work on a small scale. When that’s possible, you get even better customer stories and an ability to show the potential impact on dollar signs in another way. Absolutely take the option to “just get started” whenever you can.

In this 20 minute video, I give an overview of the two most popular options that Redgate customers use to manage cross-database dependencies when building SQL Server databases with Redgate’s SQL Change Automation.

In this 40 minute episode, Kendra discusses DevOps team topologies, the changing role of DBA teams in DevOps, and the opportunities available in creating communities of practice around database development.

Prefer to listen on the go? You can get this episode on iTunes, download the audio file, or find Dear SQL DBA in your favorite podcast app.

Bookmarks of topics covered

00:30 – Our question, from a DBA
7:00 – Why bother with DevOps?
9:23 – Team topologies, aka rethinking how the teams work together. This discussion includes notes on where and when DBAs review changes and talk about architecture in a DevOps way of working.
21:20 – Changing the DBA role from gatekeepers to internal consultants
32:30 – Creating a community of practice
38:38 – Recap and wrap up

Links from the video

DevOps Topologies – https://web.devopstopologies.com/

Communities of Practice – https://wenger-trayner.com/resources/what-is-a-community-of-practice

Got a question to ask Dear SQL DBA? https://sqlworkbooks.com/ask

This post is a part of #tsql2sday, a monthly community ritual where a topic is proposed by a community member and everyone is invited to join in.

This month’s topic is from Mohammad Darab, who encouraged us to: “Write your 20 year old self a letter. If you could go back in time and give yourself advice, what would it be?”

The advice I would give to my 20 year old self is the same advice I give to myself today, more than 20 years later:

You are a person who throws yourself into your work — ALL the way into your work

You tend to define yourself by your professional activities, and you expect your work to be consistently of very high quality.

One of your greatest strengths is that you are a creative person, but when you narrow your focus too much, when you make your life too much about work, and when you try to make things ‘perfect’, your creativity suffers. Life is a messy business, and to both be productive and to enjoy life, you need to get out of your “WORK ROBOT” mindset quite often.

For these reasons, it is important for you to set some boundaries on your work life, and to also put effort into tending to your life outside of work.

Tactically, this means that you need to practice:

• Regularly turning away from work messaging and tasks when you are not at work and not on-call. That email can wait, no matter what that little voice in your head suggests.
• Finding hobbies and activities outside of the workplace and showing up for them, regularly.
• Staying in communication with your friends and family, and practicing getting back in touch with people you value when you drift apart.
• Saying “no” at work in a multitude of ways, many of which don’t use the word “no” directly, but do involve you not doing everything yourself. You will feel guilty most times you do this, but that does not mean that the practice is wrong.
• Asking for help at work in a multitude of ways when tasks can’t be completed as they were estimated.
• Accepting that “good enough” is often, in fact, good enough. You will repeatedly be frustrated when you can’t make something perfect and when you make mistakes. Often, it is best to own up to the flaws in yourself and the situation and simply accept it, then move on to the next challenge where you may be able to contribute more. Look forward, girl, there’s cool stuff ahead.

On a personal level, throughout the years you will worry — you will worry A LOT

You will worry about being a nerd, and that you will never really be loved because you are too loud / too fat / too nerdy / too opinionated / not nice enough / not pretty enough / not clever enough / too ‘much’.

You will worry that not everyone likes you, and need to constantly remind yourself that it’s fine and right and normal for not everyone to like you, and that “being liked” is not your measure of success.

You will struggle to shore up your confidence, again and again.

You will frequently experience “Impostor Syndrome,” and to compensate for this you will tend to spend even more time working, even more time attempting to work on something so hard that it is beyond criticism.

There is no easy fix for over-worrying. Tending to your non-work life and helping that flourish will help: this wider perspective helps you to better find role models and sponsors, appreciate your strengths, find opportunities to help others, take criticism constructively, let non-constructive trolling roll away into the abyss of the internet, and to enjoy speaking in your own voice.

But there is no way to banish an over-worrying tendency forever. Learning to let your worries pass instead of clinging onto them like the world’s worst life raft will be a life-long project. You will constantly find new tools. You will always need to adapt.

The good news is that you do seem to love a good long project.

So get at it, friend. There’s still a lot of awesome things to do. Life passes in a flash, but while we are here, we always have the chance to appreciate it and to make it better.

The issue is that these folks are trying to configure a build for their SQL Server databases using Redgate tools, but they aren’t allowed sysadmin permissions on any SQL Server instance in their organization (even in development environments), because of a policy set by the Database Administrators in IT.

Why do DBAs deny sysadmin permissions in development?

You may find this type of policy puzzling — and for good reason. After all, if you don’t trust developers to keep their own development environment running, why would you ever trust any code they’ve written enough to deploy it to production?

In that question is a bit of an answer: this policy occurs when there is a fundamental lack of trust between development and operations.

The policy that developers can’t have sysadmin rights in development has two main justifications used by database administrators:

1. Concerns that development environments will drift so far from production standards that they will introduce risks to code quality
2. Concerns that allowing any account sysadmin rights in development will lead to application accounts using sysadmin rights in production

Denying sysadmin rights to developers locks you into manually controlling environment drift — instead of managing it the smart way

If you’ve worked in IT or in developing changes for databases for a while, you’ve probably come across development database instances that are a complete mess — there are databases with odd names which maybe haven’t been used in a long time (but take up a lot of space), jobs failing, low disk space, and settings which resemble nothing in production. This messy type of development instance isn’t completely surprising, because developers need room to experiment.

Sometimes when the DBAs get called in to help support and instance like this, they respond by locking down permissions. If developers have to ask to create a database, we won’t get unexplained databases, and so on. The idea is to prevent the drift of these shared environments.

But treating developers like children won’t improve the quality of development long term. Removing permissions in development environments instead limits the ability of developers to experiment and automate, and limits the tools you can use to improve code quality.

How DBAs should fix a drifted shared development environment: blow it up

It’s true that nobody wants to support a shared development database environment. But here’s the thing: you shouldn’t even have that shared development environment. As Troy Hunt explains in “The unnecessary evil of the shared development database,” it makes a lot more sense for each developer to have a dedicated environment to work in.

One of the things that happens when you develop in a private environment and use standardized practices for your database code is that you automatically begin to control environment drift, by limiting routes for deployment.

In this world, there is no “wild west” shared development environment. Instead you have a workflow like this:

• Each developer has a private development environment
• Each developer checks their database code into source control
• Branches in source control are combined with the private development environment to provide isolation / room to experiment for changes (in a way that will not impact other developers)
• When code is ready to be merged in, automated processes build the code — proving that the code in source compiles properly and that all dependencies are met
  • Having sysadmin rights for the process building your code is helpful for flexibility — for example, you might want your build to create a SQL Agent job if it does not exist, etc
• Automation also provides support for deploying that code to a fresh environment for review

This process emphasizes that development environments need to be able to be quickly reset / recreated whenever needed, and that doing so should not impact other developers.

This approach also forces developers to ensure that configuration changes are standardized and are handled by code as much as possible, as there is no ability for them to do manual configuration to make the build or review process succeed.

Most importantly, this approach reduces environment drift while also using automation to improve code quality.

But a key to making this work is allowing high permission right to the accounts which are carrying out the build and other automation.

What about the possibility of a “sysadmin free for all”?

The other reason this policy creeps up is the idea that developers can’t be trusted to follow the principle of least privilege, and if you give them sysadmin rights for anything they will use it for everything.

Let’s assume that’s a correct assumption for a moment. Giving developers lower privilege won’t actually fix the problem if they are always going to configure accounts to have the maximum permission allowed (“everything except sysadmin”).

This, again, is an issue of trust.

The better answer here is not to focus solely on what developer’s permissions are in the development environment. The answer is to focus on the permissions that application service accounts have, and to examine when and how code reviews can make sure that the roles used by service accounts are following the principle of least privilege.

To survive, DBAs need to stop being the TSA

We increasingly live in a world where the ability to deliver changes in software quickly, without impacting the user experience, is a core requirement to doing business.

However, we still live in a world where database administrator groups create policies that slow down development.

Worse, these policies also prevent the effective use of tools — like automation for builds and deployment of code to fresh environments for review — that improve code quality and reduce the risk of environment drift.

If you were a business owner and were investigating how to speed up time to market for your products, would you want your DBA team to be creating and enforcing policies that slow down releases and don’t improve quality?

Or would you be tempted to replace them with a different team, who would invest in using automation to improve code quality as well as improve release tempo?

Don’t make your organization choose. As DBAs we need to leave our obsession with policies behind, and focus on how to get what we truly want: high quality deployments and efficient management of our environments.

Today I was looped in on an email thread about the pros and cons of attending a specific event. One person on the thread asked if any of us had attended the event in the past, and whether or not event attendees were engaged with presenters and vendor representatives.

My immediate thought was: of course the attendees were engaged, because the event is a SQL Saturday.  I’ve never been to a SQL Saturday where the attendees weren’t engaged.

But, I realized that it’s a fair question.

This level of interaction isn’t a given for events, much less tech events

I’ve been to other tech events — from free events to very expensive ones — where attendees didn’t interact much with anyone else. Mostly people worked away on their laptops/phones/tablets quietly (maybe taking notes, maybe playing Minecraft, hard to say). The speakers seemed a little inaccessible and like they were very busy apart from when they were speaking, and they’d generally disappear right after their talk.

There are definitely a lot of people who attend SQL Saturday events who aren’t yet part of the broader SQL Server / Microsoft Data Platform community. They’re at a crossroads in their career and they’re not completely sure if this technology is for them yet, and they’re checking out this free event to see what they might learn from it. But I think the unusual thing about SQL Saturdays is that these events tend to draw these new folks into conversation with people who are already part of the community.

SQL Saturdays are very casual, and you don’t need to act like an expert

It’s traditional for speakers at SQL Saturdays to encourage questions and comments, and usually at SQL Saturdays there is no lack of attendee questions.

At some tech events, nobody ever “breaks the ice” during sessions by asking a question that’s accessible to beginners, and it can be tough to get the audience to ask questions or make comments. (Aside: if you are a speaker and would like me to ask a dumb question✣ from the audience to break the ice in your session, I am happy to do so! I am proud to provide this free service anytime I am around.) I’ve never found “too much quiet” to be a problem with SQL Saturdays, even at the events in countries where people are traditionally more reserved. I attended a lunchtime session at a SQL Saturday in Cambridge where Grant Fritchey got a room full of British people to share their experiences and pain points throughout the whole hour.

SQL Saturday organizers are also very good at packing their schedules with a variety of sessions that are quite accessible for people at different phases of their career and different experience levels, which helps everyone find content they can relate to.

A huge factor is that people in the SQL Saturday community are extremely welcoming

There’s a certain community vibe at the event that I think newcomers often feel and want to be part of. You see people who are chatting about what they’re up to, their experiences, and what they want to learn, and it’s natural to want to be a part of that.

I tend to take this community vibe, and in fact the supportiveness of the whole SQL Server community for granted, most of the time. But it’s a real thing, and it’s great to be a part of.

So, what are you waiting for?

If you haven’t been to a SQL Saturday and you’re reading this, check out SQLSaturday.com and look for SQL Saturday events in your area or your region. Even if you’re not committed to working in the Microsoft data platform. Even if you’re just a little bit curious about learning something for free on a Saturday. It’s a wonderful event, and it makes it easy for you to meet new people, to check out all sorts of new things, and to get engaged with the community of people involved in the Microsoft Data Platform.

I hope to see you at an event soon.

✣ On dumb questions: what I generally mean by a “dumb question” is one that makes the asker sound like a bit of a newbie / beginner / person who isn’t in-the-know. I have become a person who loves to ask these, because having the question asked and answered thoughtfully often changes the nature of the dynamic in the room and makes it OK for everyone else to be a bit more vulnerable. It makes it easier for everyone to worry a little bit less about whether they may sound stupid by saying something.

This isn’t an unfamiliar place for me, but it is new for me to recognize the early signs of burnout in myself before it becomes a full-fledged disaster. This time, I’m thinking about how I got here, and making an explicit plan to change course.

In the hopes of helping someone else out there, I thought some public journaling might be in order.

How do you recognize if you’re in the early stages of burnout?

I have recognized two symptoms which I identify as unusual for me. Together they indicate I’m heading towards burnout.

Symptom 1: Lately, I get frustrated and angry by small things

One warning sign of burnout is when small inconveniences start causing a disproportionately large emotional response.

For example, on a recent weekend I was traveling for work. I was in the Detroit area, about to head to London between a client visit and a conference. It was a beautiful day. I stopped by a local Starbucks and, by chance, another customer was rude to me.

Normally, if I wasn’t in the early stages of burnout, I would assume the other person was having a bad day, and I’d shrug this off. It wouldn’t be something that I’d even be likely to remember. But in this case, I felt a lot of anger toward that person. I was livid.

I quickly realized that the way I was feeling was more about me than about that random person in the Starbucks, but it took me a long time to shake off the anger.

This aspect of burnout is particularly tricky, because natural little miscommunications at work can slow you down more than normal, as you’re now having to handle not just the miscommunication, but also keep your own stress and irritability in check.

Symptom 2: Lack of excitement

I’m also just not as interested in work projects as normal. I’ve got a lot scheduled, but I often feel like I’m overwhelmed, and that all I can do is the bare minimum.

This lack of excitement contributes to:

• Less curiosity and asking fewer questions
• Taking less time to connect with my teammates and chat
• Poorer listening skills
• More hurried work / less critical thinking

Those things together mean that my work quality goes down a bit. And then that frustrates me.

What causes me to burn out?

Like I said, I’ve been here before — and I’ve been past this point, to where I simply couldn’t cope with the stress of my daily job anymore. Looking back, I can see some trends.

I have a tendency to enjoy working a little too much. And I have a few traits that I believe pay the way to burnout:

I tend to be a perfectionist, and I always want to help. I don’t like saying no, I always want to be involved when asked. I like finding a way to not only make things work, but to try to give them an interesting twist, too. I spend more hours working than I should — and by saying, “more hours than I should,” I mean…

I haven’t been giving myself enough time to recharge. For good health, I need to spend time away from a computer. I need to get exercise. I need to spend time outside. Meditating on a daily basis helps me ward off anxiety. Spending time with friends in person really helps as well. One of the things I realize now is that I haven’t been doing enough of these things lately.

I stretched myself thin and didn’t leave any room for life to happen. I signed up for a few more work projects than I should have this spring and summer. They are awesome projects, they are interesting and exciting and important. But I maxed out my schedule (plus a little), complete with loads of travel. It looked barely doable — until my dog, Mister, unexpectedly passed away. There was no room in my busy schedule for me to grieve my best doggo friend, and he wasn’t there to wag and say it’d all be great anymore. I started to feel trapped.

(( Perfectionist + overcommitment ) – self-care ) * grief = TIRE FIRE

As a career Ops person, I always want leeway. I like to plan a course where I’ve got a backup plan in my back pocket, and ideally a few viable alternatives behind that.

When you are headed towards burnout, you start feeling like there’s no leeway. No alternate plans. You have more stress than you can handle. You’ve got a seat on the struggle bus, and you’re not sure who is driving it.

So, what to do to change course?

Here’s my plan.

Step 1: Book time off

Yes, I am presently oversubscribed. But the very first thing I did when I recognized that I’m heading towards burnout was that I went into my calendar and started requesting days off wherever I could possibly make it work, or where someone else might be able to make it work in my absence.

This sounds counter-intuitive, but it’s necessary. While I can’t take a week or two weeks off right now, what I can do is:

• Make a proposal for days off
• Explain why I’m asking for those days off to my colleagues and boss, and ask for help to make it happen
• Commit to not working during that time off — no notifications, no emails, nothing

Oddly enough, I find that it’s harder to disconnect from work than normal when I’m close to burnout. It’s something about the stress– it makes it harder to put work down. But disconnecting is truly needed, and for me I think this is one of the biggest ways to avoid burnout.

Putting work on pause and having time off helps give needed perspective on life. It reduces stress, eases the tension causing those knee jerk reactions.

It will also pay itself back by helping make me more focused and efficient when I am at work.

Step 2: Spend time with humans (of the non-work variety)

The second thing I did after recognizing the symptoms of burnout was to email my friends at home and suggest getting together. I was lucky to have a good friend who had just started up a conversation about this, but I looked around at my other friendships and thought about others who I haven’t seen in a while, as well.

As an adult, it can be tough to make and maintain friendships. But the time and effort is so worth it. For me, it makes me a happier person, and that happiness extends into my work life.

Just like disconnecting from work, shifting gears and making time for friendships can be a mentally tough thing when you’re feeling burnt out. My mind tends to fixate on the problems at work, and it wants to stay there.

But I know from experience that planning a hike with a buddy is so much better for me in the long run, so a big part of my “anti-burnout” plan is making sure that I’m getting at least four hours of non-nerd-time human contact a week for the next few months. (I know, it’s just so “perfectionist” of me to set an hourly goal, right? I like specific targets.)

Step 3: Pick an anti-anxiety habit (or two)

For me, daily meditation is very helpful. I have learned in the past that this is a simple tool that is quite effective at reducing my stress and anxiety. When I start doing this on a daily basis, it has a more significant effect each day.

I’m starting this slowly at just five minutes a day of meditation. At first my whole goal is simply to re-establish this as a habit, without mentally scolding myself if I skip a day. The point is to keep starting until it becomes something that I look forward to each day and it is once again natural.

I find that when I do practice meditation, I have a more balanced view of things and I am more able to ask for help. I’m also better at thinking of alternatives for how something could work when someone has a request that I can’t fulfill due to time commitments.

Journaling is also helpful for me. I’ve started writing for this blog in a new way. I’m finding that it’s helpful in a similar way that journaling helps me think through things.

For example, I dictated the first draft of this blog post aloud while walking around a hotel room looking out the window. An app on my phone recorded the audio and uploaded it to the cloud. Another app created a transcript, which I edited for the post.

This method encourages me to be more conversational and more personal in my writing.  That’s very very helpful for me right now, because it’s a little bit more about my experiences and it’s a little bit more about getting my thoughts out in a way that is helpful and therapeutic for me. It also just makes me more excited about writing again, which is incredibly welcome.

What if I don’t have time?

The voice of burnout in your head is likely to make an objection: we don’t have time for this. That’s the whole point.

Well, here’s the thing that I’ve learned from the past: it may hurt to make time, but it’s going to hurt even more if you don’t.

It’s not easy to ask for help with your workload. You may need to negotiate to make it happen. It’s not a good feeling to say that you can’t do things which you’ve agreed to. If you’re in a culture of over-achievers, it can be quite difficult to say that you don’t want to work as much as everyone else is working.

However, the thing about burnout is that you can’t sustain it. If you don’t take action and you just keep your nose to the grindstone, chances are good that you’ll become desperate for a job change, and that you’ll either quit your job or take something, anything, for a change.

Burnout leads to bad choices.

It’s a much better choice to start doing the tough work and speak up for your own needs, before you are so burned out that you can’t. Get yourself into a more productive place before making any big decisions about the future — and things will look better from your new vantage point.

One of the cool things that I do as an Evangelist at Redgate is to periodically visit company headquarters in Cambridge. The other Evangelists and I get to meet with every software developer, product manager, and UX designer at Redgate over a series of meetings. That’s really cool. We talk about things that they’ve released lately, what they’re looking at doing in the near future, and we get to give feedback based on what we hear from the community and from folks in the sales process. We also get to share what we personally think should happen in these products now.

As you might imagine, I have a wish list for features in a variety of different Redgate products

Our products are great, and one of the things about great products is that users are always inspired to want to use them in new ways, so I never lack for ideas.

So, I have a lot of opinions about things that I think should happen, and features that I would love to have for customers. And, of course, I’d like those features right now, please.

In a recent meetings with one of the teams, they mentioned that most of their work over the next couple of sprints involves working on their continuous integration process

This sounds like a bummer, right? It’s a time period when the features on my wishlist, and Steve‘s wishlist, and Grant and Kathi‘s wish lists aren’t getting worked on.

But what the team explained was that this application has been around for a while, and a large amount of tests have accumulated. It currently takes more than 10 hours for the build and test process to run. There may be duplicate work going on in the tests, and there are probably tasks that can be made much more efficient. The long build and test time currently makes it painfully slow for the team to iterate on developing and testing new features. 

The team said that in the long run it’s worth paying down some debt and making the automated build and testing cycle more efficient, so that they can iterate on features faster in the future, instead of having to find other things to do while waiting for the CI process to complete.

This news wasn’t greeted with cheers from all the Evangelists present — but, to be fair, when we do respond to something with cheers it makes some of the teams look at us oddly, as we’re in the UK and that’s not something they see every day at work. (Hey, I bring my American enthusiasm everywhere!)

But everyone in the room agreed that speeding up the build and test cycle as much as possible is a necessary and reasonable thing to do

Like any other set of users, we want what we want (and we want it ASAP), but we respect that to make software development work well, you occasionally have to step back and pay down some technical debt.

This is also true for database development

It’s not always obvious that when doing DevOps, stability is just as important as release frequency, but that is the case, and maintaining that stability requires being diligent about tidying up one’s processes.

As database professionals, as developers and DBAs, I believe what we should see as the ideal release cycle is one in which we are free to release features every day without any manual work, and we have a software development cycle in place that ensures that the risk of our changes is minimized, that coding patterns are being used that ensure system stability, and that we have a response process in place that restores services as quickly as needed should there be a performance, availability, or functionality problem.

But that doesn’t mean that we actually release changes every single day. In order to do that effectively, we usually have to have paid down a lot of technical debt. That means stepping back periodically and working on improving our processes, rather than relentlessly focusing on shipping, shipping, shipping, shipping.

This is even more critical with legacy applications, where there is a significant amount of technical debt to pay down

Spending release cycles on making continuous integration and continuous delivery/deployment work better isn’t the part of DevOps that gets business owners and users really excited. But it’s still important to talk about, because this is a critical activity that enables us to deliver value on a regular basis — and that is what gets those folks excited.

Today I got a bit closer to a meaningful definition of automation, as it applies to the software development process. I’ve been turning this concept over in my head for a while, which is partly related to the dreaded question of licensing.

Why should licensing an automation product be related to the number of users?

A few weeks ago, I was chatting a bit in the SQL Server Community Slack Channel.✣ One community member was frustrated with running into situations with per-user licensing for monitoring and automation products.

This isn’t the first time I’ve heard grumbling about per-user licensing, of course — with any licensing model, you’re going to hear grumbling about it, that’s just how licensing goes.

But I think per-user licensing can make a lot of sense when it comes to automation products, because of the nature of automation. I work for Redgate, which does per-user licensing. I also often do demos of how our tools integrate with Microsoft’s Azure DevOps Services (formerly VSTS / or TFS-in-the-cloud), which does licensing based on user numbers.

But not everyone thinks this makes sense.

That’s because they see automation as:

• Something that one person sets up on a server, which that person may occasionally tweak; and…
• A script or orchestrated set of scripts and products that replace the work that people (maybe more people than the person who set it up) would do manually

This definition isn’t dumb or naive at all. This is classically what automation has been in IT for many years: I’ve got a problem. I create a script. The script helps save me and my team some time and I only ever look at it again if it stops working.

Based on that definition, it would seem most natural way to be charged for automation tools would be based on something like the number of times the tools are run, the number of servers/cores they are run on, etc. ✣✣

The nature of automation has changed dramatically in recent years

Like I said, I’ve been having a hard time putting a definition of what automation means now into words. Then I saw a link to this job description for a Sr. Resilience Engineering Advocate at Netflix.

There are a lot of interesting things about the job description, but one sentence that leapt off the page to me was that the team values:

Automation as a team player versus automation as a replacement for humans

Netflix Cloud and Platform Engineering SRE Team

This is a huge part of the evolving definition of automation. Automation is now:

• Something that a team configures, interacts with, and improves on a daily basis
• A script or orchestrated set of scripts and products that are an integral part of the productivity of the team

The big reason that per-user licensing makes logical sense to me when it comes to tools that are designed to be a key part of the software development life cycle is that the tools are meant to be experimented with freely. The tools will work best if they’re able to be tinkered with and adapted over time, to suit the needs of the team at that point. Licensing based on cores or CPU cycles or usage naturally reduces experimentation if it is going to drive up cost.

Also, the tools are meant to be team players: they are meant to be available to have every team member interact with them. Automation in the SDLC for database changes doesn’t mean that every time a change is committed, the change rockets toward production without a human being ever needing to think about it again. Instead, automation is a player in a process that can absolutely include rigorous review (both automated and human-powered), testing, and even approval gates when needed.

Automation looks different in different teams

One observation: team size matters. If you’re one person in a small shop and you’re setting up automation to reduce the amount of manual work that you personally have to do, this high-faultin’ definition of automation as a “team player” probably isn’t going to resonate with you. You’re much more likely to continue to see automation in the classically defined sense.

But, on the other hand, you don’t have to have a team nearly as large as Netflix to start seeing the advantages of thinking about automation differently. It just takes a few people working together collaboratively and thinking about how to more consistently and reliably deliver values to customers to start changing the way automation exists in the workplace.

✣ The SQL Server Community Slack channel is great, join up here

✣✣ I don’t mean to make this post about how much software should cost. I actually don’t think that’s too terribly related to licensing model choice at all — whatever you are charging by, whether it be people, cores, tentacles, or whatnot, you can find a way to make it cheaper or more expensive.

I got a follow-up question from my friend Chris Randvere at Redgate: he asked for more information about what the question meant? I realized that my wording had been pretty unclear. I had meant to ask the panelists what their thoughts were on release cadence when a team lacks tooling to do automated load and performance testing outside of production.

Should the lack of automated performance testing ability change the rate at which we deploy software?

In other words, if we can’t do performance and load testing, does that mean that we should or shouldn’t deploy a change to a database every weekday?

I don’t think we covered this super-well in the panel because I worded the question poorly. So I wanted to share my experience around this, and also talk about why it can be fairly common for teams to lack automated load testing ability outside of production.

Why doesn’t everyone have an environment where they can validate performance by replaying activity against an updated database before it ever gets released?

We have some built-in tools for this in SQL Server. The current version of this is called Distributed Replay. These tools are not the most lovingly tended by Microsoft in terms of updates. A frequent complaint that I’ve heard about distributed replay is that the current version of the tooling still requires you to feed it with old style profile traces done with the old SQL Trace.

You don’t necessarily have to have the Profiler app running while you do the trace, but the old-style SQL Trace results are what it takes in in other words. You can’t do a more modern Extended Events trace and feed that into the tool.

But that lack of updates isn’t the main reason why not everyone runs Distributed Replay.

Distributed replay is tricky to set up

The more complex your environment, the trickier it is to set it up. If you’ve got things like linked servers or SQL Server transactional replication creating interesting patterns in which your SQL Server communicates with other SQL Servers, that can make using Distributed Replay particularly tricky.

There are absolutely people out there who’ve configured Distributed Replay on complex systems, but they all say it wasn’t something they set up in just two hours. So one factor is the complexity.

Another factor: distributed replay is designed to replay not to amplify

When we’re doing performance or load testing, we are not always interested in: how will the system perform under the current load? A lot of times we’re interested in: how will the system perform if it’s under even more load? 150% of the load, or 200%, or more.

But with any replay tool — I’m not just dogging on distributed replay here — if we think about the nature of any database replay tool, we can’t replay the exact same commands and expect to learn how performance will be at a higher load rate.

For example, a delete command. If a delete command on the first run finds 10,000 rows to delete, that could be a fair amount of work.

If we replay that same delete command, depending on what the criteria are in the delete, it possibly will find zero rows to delete the second time, because it’s already completed. Similar things can happen with updates. We may also have constraints that mean we can’t just insert the same thing twice depending on the nature of the data.

So the way modifications work, just amping up the load in a replay isn’t the same as adding true additional load.

Now, there are other ways you can do load testing. There are third-party tools that you can buy to get around this problem of repeated modifications.

They can be expensive. But they also require a fair amount of coding, because you’ve got to put in commands that help you get to a state where you can check: Hey, let’s turn let’s turn the volume of activity up to 200%, to 300%.

So, some folks do that. but because of the cost and the effort put into it, folks only tend to do this with third party tools when it’s really worth their while, and their management is deeply invested in the idea of having load testing.

Even then, the load testing needs to be updated for some changes

For some changes, you can test them with load testing tools without any changes. For example, if I refactor a function for performance tuning, but don’t change any inputs or outputs, I could test that with an existing configuration of a load testing tool.

But what if I add a new parameter onto a stored procedure? If I don’t change the load testing, will that be appropriate, or not? Should I be running the load test with a variety of values for that?

Or what if my change involves dropping one procedure and adding in another one? A replay system would have no idea what to do, and with a load testing system I’d need to adjust what gets executed, how often, etc.

When it comes to testing database changes, load testing tools are excellent, but I’d expect some human work to be required as well.

So, most people don’t have automated performance and load testing. Should that impact how frequently we deploy changes to production? 

What we’re really looking to find with load testing in the SDLC cycle is regressions in performance. We should have other testing to catch true defects such as making sure that the right results are returned, etc.

With database changes, there is a fair amount of work we can do to make sure that things perform well without load testing. There is other due diligence that can help: we can maintain and use a staging or pre-production environment with production size datasets and the same data distribution as production, for example.

In that environment, we can testing that confirms: what indexes is the modified code using, and are they optimal for the modified code? How long are queries taking? We can make educated guesses about production performance instead of waiting until after a release to see what it’s like.

This level of manual performance testing can work extremely well, and it unlocks us to do frequent deployments, in my experience.

Without load testing, it’s best to frequently deploy small database changes

By small, I mean as small as possible. A lot of these database changes are going to be things that our customers shouldn’t notice at all. Like, hey, we add a new column to this table. We’re not actually using it, though. We’re going to use it in the future for a feature that’s coming out soon.

But we regularly trickle out the staging steps for a future change. Each of these steps is backwards compatible and deployed well ahead of the point in which we “turn on” the new feature for our customers, which is often handled via an application control called a feature flag.

This regular stream of very small changes is helpful for speedily resolving any performance issues which may occur.

If performance does change, we haven’t released a big batch of 50 changes all at once — that’s a lot of things to go through to find the culprit. Instead, we’ve released maybe 7-10 changes in the last week, and we can look at the most recent ones first, and check if they could be related to the issue.

What do you mean by “frequent”?

By “frequent”, I mean that daily releases shouldn’t be a big deal.

But remember: batch size is critical. This isn’t like a race car, this is like a steadily dripping faucet.

Complexity: sometimes performance problems don’t happen right after a change is released

There can be changes that we make where performance is fine for a while, and then something happens. Maybe it’s even three weeks after the change was released, but suddenly performance is terrible.

One particular tricky issue in SQL Server that can cause this is what’s called parameter sniffing. If we are using queries that are parameterized, a lot of the way that things perform depends on the execution plan that is cached with the first set of parameters that are passed into the query or procedure when it’s compiled, because SQL Server reuses that execution plan until something causes it to recompile.

Maybe the thing that causes the query to recompile is enough data has changed that statistics automatically update. Maybe it’s that there’s a failover in an Availability Group. Maybe the SQL Server is restarted. Maybe someone manually clears the execution plan cache.

A very wide variety of things can change, but if we happen to we have a recompile and we happen to have a compilation with a set of parameters that leads to an execution plan that doesn’t work so well, then we can suddenly run into performance issues.

But this risk of parameter sniffing doesn’t mean that we shouldn’t release that changes frequently. We’re going to have the same risk whether or not we’re releasing a big batch of changes or whether we are regularly releasing small changes.

For issues like this, I like the recent auto-tuning features in SQL Server. Essentially the automatic plan correction feature will look cases where a query is sometimes fast and sometimes slow, and attempt to identify “bad” execution plans which are periodically cached for a query.

You have to have the built-in Query Store feature enabled, and when using on-prem SQL Server this feature requires Enterprise Edition for production (Developer Edition has all the features of Enterprise for non-production environments). But if you do have a SQL Server where performance is critical, this feature is a huge deal. It gives you the option to either let it temporarily correct the plan for you, or to notify you in a DMV that it’s spotted a problem that needs tuning.

This feature allows can either work with automated load testing — you can use the feature to spot parameter sniffing problems before deployment — or it can give you early warning of performance problems before your customers start containing. It also gives you insight into how to reproduce these tricky problems outside of production.

A quick recap

Automated load testing is fantastic, if you have the time and budget to take it on for your team.

But if you don’t have it, the lack of automated load testing shouldn’t block you from frequently deploying small changes to production. By combining targeted manual performance testing into properly planned changes, you can still deploy frequent changes safely into high performance and high uptime environments successfully.

Steve and I will be discussing proven patterns to version and deploy changes successfully

Read more about this precon session, or check out the video below where I give a brief overview of what Steve and I will cover.

Will you teach me how to use Redgate tools?

Nope– not in this session.

While Steve and I both work for Redgate, we will be showing patterns and approaches that work with both vendor and custom tooling, and we’ll do demos with a variety of tools, including free tools when possible. This is absolutely not a product-specific session, and the patterns discussed have been proven in the industry by developers and DBAs using a wide variety of tooling.

If you’d like to learn more about Redgate tools, check out the Redgate SQL in the City Summit Precon that will be held on Monday, November 4. That day of training has 100% different content, so if you’d like to go all-in on DevOps, join us for both!

Reserve your spot

You can currently register for an individual pre-conference session for $499, or bundle pre-conference sessions with the conference.

Hope to see you at PASS Summit!

This morning, I received the following question from a user:

Hello Madam,

Could you please clarify SQLServer “Data Row” size:

If I run the script below on SQL Server 2012, then Slot(row) Size is 710 bytes

if I run the same script against SQL Server 2016 and above, then Slot(row) Size is 724 bytes.

They then provided a script which creates and inserts a few rows into a sample table, runs the DBCC IND command to find a list of pages for the sample table, then uses the DBCC PAGE command to examine the page.

The first thing I looked for: how many bytes is the difference? Is it 14 bytes?

I love it when someone sends me a repro script, but in this case I didn’t need to run it. The first thing I did was to look at the two numbers given for row size, and to subtract the smaller one from the larger one: 724 – 710 = 14 bytes of difference.

That bit of information alone gave me an immediate guess of what was going on.

Row versioning in SQL Server has a 14-byte row overhead

You can reduce blocking in SQL Server by enabling “optimistic” locking. There are trade-offs for this approach, however: row versions are enabled in tempdb, and 14 bytes of overhead may be required on impacted rows. (For a quick overview of this row versioning process, check out this Simple Talk post by Kalen Delaney.)

My guess is that row versioning is enabled only on the SQL Server 2016 and above instances that are being tested in this case. This could be because of any of the following

• Snapshot isolation is allowed on the database
• Read committed snapshot isolation is enabled on the database
• The database has a readable secondary in an availability group

You might wonder – why would having a readable secondary cause the row to grow?

Readable secondaries in SQL Server allow you to offload read workloads.

It’s important to minimize blocking against those readable secondaries, though: you don’t want data modifications flowing through to the readable secondary to be blocked by reporting queries that you are running against the secondary, after all. You also want reads to be as consistent as possible.

For this reason, queries that are run against readable secondaries are automatically escalated to snapshot isolation. And snapshot isolation requires row versioning — those 14 bytes of space are needed too help make it all work.

But readable secondaries are read-only, right? So how can it it write those 14 bytes? The answer is that it’s complicated. Here’s the documentation from in the “capacity planning” concerns in Microsoft’s docs:

When you configure read-access for one or more secondary replicas, the primary databases add 14 bytes of overhead on deleted, modified, or inserted data rows to store pointers to row versions on the secondary databases for disk-based tables. This 14-byte overhead is carried over to the secondary databases. As the 14-byte overhead is added to data rows, page splits might occur.

The row version data is not generated by the primary databases. Instead, the secondary databases generate the row versions. However, row versioning increases data storage in both the primary and secondary databases.

Offload read-only workload to secondary replica of an Always On availability group

To dig into the details of this more, click the link — there’s even more info on the full page.

Calling all Database Administrators, Developers, Analysts, Consultants, and Managers: Redgate has a survey open asking how you monitor your SQL Servers.

Take the survey before April 5, 2019.

Your time is valuable. The survey will take 5 – 10 minutes to complete. That’s not a ton of time, but it’s a noticeable part of your day, and there should be something in it for you. Here’s why it’s worthwhile to take the survey.

Database use patterns and monitoring trends are valuable to everyone in the community — and we’ve been missing out on this trend information!

This isn’t the only survey from Redgate — you may also have heard of the annual State of Database DevOps Report.

But this survey is different in important ways: it asks how you manage and monitor your SQL Server database environment, regardless of whether you think about DevOps at all.

Redgate shared the results of the State of SQL Server Monitoring Survey with readers last year, and will do so again in 2019. 2018 was the first time we know of that a survey of this kind had been done, and it will be especially interesting to notice what has changed in the past year.

If you fill out the 2019 State of SQL Server Monitoring survey, you’ll get an advance copy of the results by email

The results will help you understand:

What do other DBAs, Developers, and IT Professionals see as their biggest challenges over the coming year?

In last year’s report the biggest challenges were seen as:

1. Migrating to the cloud

2. How to deploy changes faster to larger environments

3. Protecting data – especially for compliance reasons

2018 State of SQL Server Monitoring Report

I’m curious to see if these priorities have changed after a year. With GDPR implementation having come to pass and more states and countries around the world passing increasing privacy regulations, I suspect that ‘protecting data’ may move up from number 3 on the list, but I won’t know until we see the data.

How much time do your peers spend examining SQL Server health and resolving issues?

This is a great question on the survey — and I think this one is absolutely worth some reflection after you take the survey (and after the results come out). Do you spend more time firefighting than you should? If so, what ideas you have to change that?

At what point to most organizations move from manual monitoring to a third party tool?

If you work for a growing company and are interested in making the case to purchase monitoring to your management, it may be useful to know information like this…

Respondents with fewer than 10 servers were twice as likely to rely on manual monitoring as to use a paid-for tool. Those with 10 or more servers were more likely to use third-party software.

2018 State of SQL Server Monitoring Report

Survey results will help you prioritize what to learn

Are you curious as to whether you should learn another database platform, such as MongoDB, Oracle, MySQL, Cosmos DB, or PostGres? The survey will show how much respondents report that they are using each platform, and whether they think the amount will increase or decrease.

Want to know whether you should invest time (and maybe ask for some budget) to explore cloud technologies like Azure Managed Instances, Azure SQL Database, or Amazon RDS? The survey will show how many of your peers are using each one.

Also, You Could Win Money

Everyone who completes the survey and provides their details at the end will be entered into a prize draw to win a $250 Amazon Voucher (or equivalent in your local currency).

In the article, I discuss:

• Why referencing the current database name creates a dependency
• What ‘deferred name resolution’ is, and why the dependency may be more noticeable in views and some functions (rather than stored procedures)
• Which activities are most likely to break if you place dependencies on the current database name

Read the full article here.

Are you interested in speaking at the Professional Association for SQL Server’s annual Summit conference? The call for speakers is now open, and you may submit up to three sessions between now and March 31, 2019.

I’m currently in the process of sketching out my ideas for what sessions that I’d like to submit, and I thought I’d share my process here.

Generating ideas: what am I interested in spending six months thinking about?

I’m a bit selfish when it comes to topic selection, and I think that’s fine: it needs to be something that I’m interested in thinking about for more than half a year.

That does NOT mean that it needs to be super-advanced rocket science content. Figuring out how to present introductory level content clearly, in an easy-to-understand way takes a lot of time. The topic simply needs to compelling enough for me to stay interested.

A filter: is the topic relevant to enough people?

When I first began speaking, I thought I needed to speak on topics that were unique to me, which other people in the community didn’t already “have covered.” This led me to somewhat esoteric concepts. There’s a big downside to that: your talks simply won’t be relevant to many people.

Now, I encourage myself (and you) to use the opposite filter: think about talks that will be be helpful to a lot of people. That means you probably won’t be the first one in the world to deliver a talk on the topic, and that’s perfectly fine: your perspective is valuable!

There is definitely a “three bears” aspect to this filter. Your talk doesn’t need to be useful to everyone at the event. But do think about your intended audience, and whether you’ll help a significant portion of the audience at the event.

Sketching out ideas and audiences

For this year’s PASS Summit talks, I currently have two topics I’m quite passionate about, which I’d love to share.

Right now I don’t have abstracts. I’ve started by creating notes on:

• Subject matter area / rough title
• What the talk would include – a problem summary (which I write as quickly as possible, just throwing out ideas) and a bulleted list of content ideas
• Who would care (audience) and why they would care

Here’s where I’m at with my two topics.

Topic 1: Source Controlling Index Code in a Changing World 

Alternate title: I prefer, “How to Standardize Index Code in a Changing World.” Not everyone may get that Standardize = Source Control, however — not sure if that’s simply how I think of it.

Problem summary: It’s critical to get database code standardized into source control to manage collaboration, store and version your organization’s intellectual property, and to track and audit what has happened in your database code. The database code for indexes, however, is increasingly difficult to standardize: new features in Azure SQL DB automate the process of tuning index schema. Single-tenant database architectures often standardize table schemas, but require customizations of indexes for performance in individual databases. And index schema often needs to “drift” in production as operations teams respond to critical performance problems. How do you adapt successfully to this chaos, yet still maintain your sanity by managing your database code in source control?

Topics to include:

• Differences in source controlling indexes with state vs migrations approaches to database code
  • Strategies for working around limitations on advanced index features with a state approach
• How to manage drift that occurs with automatic indexing in Azure SQL Database
• How to manage incidents where indexes are manually changed/drifted in production due to a critical need without going through a full release cycle 
• How to manage single-tenant environments where the indexes are customized in individual databases 

Audience / who would care:

• Developers and DBAs who have their databases in source control, but who struggle with “drift” in some areas
• Developers and DBAs interested in getting databases into source control — sometimes thinking ahead about these more advanced issues at the beginning can help you learn fast
• Architects thinking about using single-tenant designs

Topic 2: Best Practices for Branching Database Code in Git 

Problem summary: There are quite a few discussions and patterns available online for branching application code, but special considerations apply when it comes to database code — and hardly anyone has written about this! This talk helps DBAs and developers design the simplest branching strategy that meets the needs of their organization. I will share key considerations for designing a branching strategy for database code, and we will discuss multiple popular branching models along with “fit notes” describing the strengths and weaknesses of each model.

Topics to include:

• The overarching rule of “keep it simple”, but why this leads to different practices for small teams (1-2 devs checking in code) vs larger teams
• State-first vs migrations-first and explain what that means for branching – a migrations approach that doesn’t include a state component will SUCK for merging … maybe don’t use the word SUCK in all caps in the final abstract tho
• Why shared vs dedicated development databases is important when considering branching – why object locking is a necessarily evil of a shared model, so don’t do shared if you can avoid it (esp considering that Dev Edition is free for SQL Server, side-eye at Oracle)
• What feature branches are and how to tell if you need them 
• What release branches are and how to tell if you need them 
• Pull requests 
• Rebasing 
• Branching diagrams for multiple popular models, and “fit notes” for the teams using them

Audience / who would care:

• Me!!! Hahahaha, I find this topic fascinating, even if there were five people in the room I’d be thrilled (but I do think this would be quite popular with dev-minded folks, I don’t think it’s just me)
• Developers and DBAs who want to get database code into source control
• Developers and DBAs who have code in source control, and who struggle with:
  • Managing concurrent changes to objects
  • Building the codebase from scratch to a specific release / version
  • Managing drift in different environments / comparing database versions to source versions

Next steps: mulling these over

I feel pretty good about these topics so far: I think that I care enough about them to want to spend a good amount of time with them, and I think that enough people at the conference care about the problems discussed to make them worthwhile.

These two talks are a bit more focused than talks I’ve done in the past, in that they’re specific to managing code in source control. I’m strongly of the opinion that everyone should be managing their database code in source control, though, so I’m fine with the level of focus — source control should be the norm!

I am presently waiting a few days before writing and revising “real” abstracts. I’ve got some time before the deadline, and I find it’s good that I sit with a topic for a few days to see if a bright new idea occurs to me, or to see if I may want to go in a different direction.

First time speaker? Go ahead and submit!

Writing an abstract and choosing a title for a talk can be daunting. I personally find it easier to start with making notes about a potential talk like I’ve shown here, and then refining those notes. I am hoping that helps some potential speakers out there to get started.

Here’s that link to the call of speakers again — submit by March 31, 2019.

Your mission: Tell us a true story in 500 words or less about a time when you were involved in an Agile or DevOps project that went full steam ahead in speeding up delivery of application code, but didn’t modernize database development practices. Did trouble follow? Check out the prizes and give us the scoop here before March 20, 2019.

Enter today, time’s almost up!

1. Share your story using the form here
2. The story must be true – but never fear, we will anonymize all stories and the names of the winners
3. Enter as often as you’d like (but each person may only win once)

Need inspiration?

Grant’s sample entry (the short version)

“Our organization built a new application using an Object Relational Mapping (ORM) tool. The team worked without any DBAs or database developers for speed. We ended up with an incredibly slow application that nobody could report from at the end – and deployments dropped and recreated tables! We had to implement weird workarounds to make deployments safe. The project delivered late and required a lot of unexpected budget from consultants. If our teams had been able to work together on the database as part of the project, we would have saved the company a lot of time and money.” …Read Grant’s full article

My own development disaster story: The V1 Team’s Doomed Redesign

Once upon a time, I worked for a startup which purchased another startup. For the first year, the acquired company’s applications operated independently, as they were originally designed. After that point, developers from both organizations began merging together, and a team was created and assigned the task of redesigning the applications of the purchased company to be more scalable. Let’s call this the “V1 Team.”

The existing production legacy applications involved complex data processing. The idea was that the V1 Team would reproduce almost all functionality of the legacy applications in the initial release of the redone application.

The V1 Team went off and began work. As they worked through their long development cycle, features continued to be added to the production application. These were added to the workload of the V1 Team, continuously increasing the scope of their project and delaying their initial release date.

There were a lot of quite smart developers on the V1 Team, people I enjoyed working with, but I found myself stopping by their area less frequently over time – frankly, it was a depressing place to visit. The team was overworked and frustrated. They were never releasing any code to production, and there were no design meetings with anyone.

Finally, we heard that the redesigned applications were going to be ready to launch soon! This was good news, as the legacy applications were not easy to support.

The first thing I remember about the database deployments for the V1 Team’s new application is that… well, they didn’t actually deploy. The V1 Team had been stuck in a prototype environment for so long that quite a bit of work was needed to bring the codebase into a deployable state for pre-production and production domains.

The second thing I remember is looking at one example table in one of the V1 Team’s new databases, and finding that the clustered primary key was defined as being the combination of four uniqueidentifier columns (GUIDs).

I’m not the kind of DBA who sees a uniqueidentifier column as being universally bad or a sign of doom. However, keeping clustering keys relatively narrow is generally a good practice for scalability in SQL Server– and a 64 byte surrogate key is a particularly bad sign. And it was too late to make any changes: the poor V1 Team was desperate to get anything in front of a customer at this point.

It was a tricky and painful struggle for both the V1 Team and the operations teams to get that code into production and to begin supporting it. Performance wasn’t great, and customer response was lukewarm at best– after all, they’d been told the whole project was about scalability.

Nobody declared victory, least of all the customers.

Scoping and architecture discussions, I learned, are critical for any project.

Well, I love breaking technology on purpose, in a place where it’s not going to slow anyone else down. It’s a great way to learn more about how everything works and what your options are to fix the situation when things go sideways.

In this 9 minute video, I ignore SQL Source Control’s valiant attempts to keep me out of trouble, and put the database code in my local git repository into an inconsistent state. Can I fix it? (Spoiler: yes I can.)

These videos step through setting up and working with a project in one of my favorite Redgate tools: SQL Change Automation. The awesome thing about SQL Change Automation is that it gives you two things:

1. Fine-grained control to tune and order the execution of changes to your database with a migrations-first approach to source controlling your code
2. A view of the state of your database, which is crucial for code reviews, controlling environment drift, and communicating about changes between developers and database administrators

The tricky thing is that there’s a lot to learn when starting out with SQL Change Automation if you haven’t used a migrations-first approach in the past, or aren’t used to working in Visual Studio.

That’s where these videos come in: you can follow along with the demos in your own installation of Visual Studio and Redgate’s SQL Toolbelt, or just watch and learn. I take it slow and don’t assume that you’re an expert in Visual Studio.

In one hour of demos, you will learn…

• How to set up a SQL Change Automation project with Microsoft’s Northwind sample database
• What a baseline is and how to create one
• What programmable objects are, and how to edit one offline in Visual Studio
• What the offline schema model is, and how to work with it in Visual Studio
• How to use branches and SQLCompare with SQL Change Automation projects
• How to automate the production of database documentation with the SQL Change Automation PowerShell cmdlets
• How to build and deploy with SQL Change Automation PowerShell cmdlets

Why aren’t you working in SSMS in the videos?

If you prefer working in SQL Server Management Studio or Azure Data Studio, you can choose to make database changes there, then import the changes into SQL Change Automation as scripts that you tweak as desired in Visual Studio. That’s a valid workflow, and I often use it myself.

In these videos I show how to do all the work in Visual Studio — because although I’m a long time SSMS fan, it’s pretty convenient to know how to do your work in multiple tools.

Tools used in the videos

• Microsoft Visual Studio 2017
• Redgate SQL Toolbelt – Free Trial available
• VSCode – I use this for PowerShell script execution – you could use the PowerShell ISE instead, or just execute scripts from the command line

Using SQL Toolbelt Components with SQL Change Automation: Create Your Demo Environment

This is the first of four videos on using SQL Toolbelt components (SQL Prompt, SQL Compare, and SQL Doc) with SQL Change Automation Projects. Follow along to set up the demo environment used in the next three videos.
1:07 – Download the Northwind Sample Database script from Microsoft: http://bit.ly/msftnorthwind
2:15 – Create the Northwind Database and the empty NorthwindDev database from Visual Studio
4:20 – Create the SQL Change Automation project and create a baseline script
8:30 – View pending deploy of the project to the NorthwindDev database
8:53 – View location of baseline script, programmable objects, and offline schema model in Solution Explorer. Check for ‘Unsupported’ folder.
10:04 – Deploy to the NorthwindDev database

Using SQL Prompt with SQL Change Automation in Visual Studio

Learn how SQL Prompt can help you quickly improve TSQL scripts in Visual Studio with SQL Change Automation.
The PowerShell script shown is available at: http://bit.ly/2IfgmUK
In this video:
00:20 – Overview of programmable objects in SQL Change Automation
01:15 – Overview of the offline schema model in SQL Change Automation 2:10 – Demo of refactoring a stored procedure (a programmable object) begins
7:30 – Saving the “offline edit” to the programmable object triggers option to deploy to development database
9:08 – Test full build and deploy of first committed change in VS Code with PowerShell components
13:15 – Demo of editing an index on a table with SQL Server Data tools and generating a migration script begins. This demo shows the “offline schema model”
20:19 – Deploy the edited migration script
22:40 – Perform build and deploy with PowerShell cmdlets
24:00 – Review release artifact

Using SQL Compare with SQL Change Automation

Learn how you can use SQL Compare with SQL Change Automation projects in Visual Studio to compare database state at different points in your project.
00:16 – How the SQL Compare is used by SQL Change Automation under the hood
1:03 – Demo begins with a view of the Northwind SQL Change Automation Project in Visual Studio
1:50 – Demo of setting up the source and target in SQL Compare, with discussion of how this comparison uses programmable objects and the offline schema model
4:23 – View of the results of the comparison
5:06 – Demo of creating and checking out a branch in Git via Visual Studio 6:37 – Demo of refreshing SQL Compare after the branch switch in Visual Studio, and how and why the results change
7:53 – Demo of switching back to the master branch and a few of how this once again changes the comparison being performed (in a good way)

Automating SQL Doc with SQL Change Automation

Learn how to automate the production of database documentation using the SQL Change Automation PowerShell cmdlets to produce SQL Doc documentation.
The PowerShell script shown is available at: http://bit.ly/2V14Lui
In this video:
1:06 – Start of demo in VSCode, walk through of some key points of the script
3:28 – Explanation of why I am specifying a SQL Server instance for the documentation and not using LocalDB
3:45 – View of the resulting documentation in a .zip output file begins

A transcript is below the video if you’d rather read. You can also subscribe to the podcast to listen on the go.

Episode Transcript

Note: I generated the images below for this episode with buffer.com’s free tool, Pablo.

Hello and welcome to Dear SQL DBA, a podcast, YouTube show, and now a twitch stream for database developers and database administrators.

I am Kendra little from Redgate software and from SQLWorkbooks. Today I want to chat about what it is it evangelists do. My job at Redgate— my job title is that I’m an Evangelist. I’ve gotten the question from multiple folks, including people at passport control, as to what exactly is it that you do as an evangelist? Because it really really isn’t always obvious, and I think it’s changing as well. I think the role of Evangelists who’ve been around in software companies for a while is evolving.

How I first encountered an ‘Evangelist’: a man yelling into a phone

I first heard of this job title way back when I first was working at Microsoft. I think I was still a contractor there. I got a job at Microsoft as a contractor, and I was very very excited to be working at Microsoft. It was an amazing job. I worked a lot and I learned a ton– of course I was an hourly employee so working a lot was actually to my benefit. I eventually did become a full-time employee at Microsoft and kept learning more and more there as well, and it was great. But at this time, I was still a contractor.

The first encounter I had with an evangelist was… we were in these buildings where everyone was in offices. They weren’t cube farms at all. Everybody was in offices. Lots of us did share offices with other people. My office mates were always good people to work with. It was nice, actually, to share an office, because you could learn a lot. The people you were sitting with were often on your team or on a related team, so you could figure out lots of stuff about what it was like to support different products. But our offices weren’t all together for the team, so you had neighbors who worked in completely different parts of the organization.

One of the neighbors had a little nameplate that mentioned this person was an Evangelist. I don’t even know what team this person was on. It was a man, and the only things I knew about this Evangelist were that their door to their office was usually shut, and they were often yelling angrily into a telephone.

I was just like: I don’t know exactly what this job is, I don’t know if that the job is for really angry people, or if this just happens to be a really angry person– but there seems to be a lot of yelling involved. To this day I’m surprised by that. Even now, I cannot reconcile what all of this anger and yelling had to do with this person’s job. In my job as an Evangelist there is not a lot of yelling, there is not a lot of anger, and I don’t even use the phone that much. So everything is really different from that first encounter with an Evangelist. But it did teach me that different Evangelists may have very very different jobs, because that is not at all the job that I’m in in now.

Rebranding: ‘Evangelist’ –> ‘Developer Advocate’

These days a lot of people who do the job of what was formerly known as Evangelists are changing their job title, because the the word ‘Evangelist’ has religious overtones. To the many people I think it brings up the Crusades, and it brings up violence. I don’t know if that’s related to the angry person in their office screaming into the phone or not. But at Microsoft, for example, I do know that many evangelists now have the job title Developer Advocate.

I appreciate why the name might change, but I don’t know if Developer Advocate is the perfect name, either. When I think of an advocate it brings up to me someone you might get if you were in a difficult legal proceeding or if you were in a tough healthcare situation and you needed someone to advocate for you. It represents being a little bit helpless.

Maybe that’s not the right understanding of Advocates. Maybe that’s a bias on my part. But naming things is about understanding cultural bias. Is Developer Advocate a better name than Evangelist? Maybe. But it isn’t a name I’m rushing out to adopt — not yet.

I feel like I kind of haven’t come up with the perfect job title yet.

So what does an Evangelist do?

Let’s talk about what Evangelists actually do. A lot of what I do has to do with communication between my company and our customers, and our potential customers out in the wild. This is communication that goes two ways.

One of my main tasks is figuring out: what are the difficult things that people are encountering, what are the problems that people really need to solve? How do our products or different techniques related to our products help with that? That is a big part of my job: Patterns and practices that can help people be more successful.

Now, the two-way communication involves both listening to people about what’s difficult and what’s hard (which our products could help with) AND finding where our products could improve. Identifying: what are those pain points that our existing products help with, AND what might we be able to do with them that could really help fill this need?

I’m tasked with channeling communication back to our development teams and saying, hey, here’s what I’m hearing from our customers. I get asked by our development teams a ton: what are you hearing from people in the marketplace? What are you hearing from consultants? What are you hearing from potential customers? What are you hearing from people at conferences? They really want to know, what are the opportunities we have, what’s working well with people, what could we do better?

When listening to a customer, the job of an Evangelist is not only to understand, okay there’s a product that could help you out. The job of an Evangelist is also to understand patterns about how people work. If we are going to apply a technology or a product or a process, what are the things that will actually make a team successful at that? What are the ways that people interact that need to change? What are the dependencies on really making this work well?

The idea isn’t just to sell the products. In my talks, I’m going to talk about things where, yeah, our products could make it easier. But there’s always a bunch of different ways to do things. When I design talks, I’m interested in thinking about: what are the ways to make people successful? Yes, if our products fit into this, that’s amazing. And I do have a bias, because I do have an employer, right? I want to be clear about that all the time. Evangelists aren’t unbiased and neutral. But part part of the job is also to be quite open, and to not only think about the products, but to also think about how people work and how people can be successful — even if parts of that are outside of the products. Or even if our products aren’t required to make you successful.

Because the truth about the modern world and technology is you can absolutely write a bunch of code yourself to do a lot of things. Is it gonna take you a long time? Yes. But there’s always more than one way to do something. Buying products from different vendors can make your life easier, though.

So, to me, the point of being an Evangelist is really about helping people get set up to succeed. The parts I’m gonna be the best at, the parts I’m gonna look at the most are in the areas around where my company’s focus is. Because in fact, I am really passionate about that.

I took an Evangelist job at a place where I was passionate about what we are doing. I wouldn’t have taken an Evangelist job if I didn’t think these were important problems to solve, if I didn’t think they were good tools to solve the problems.

It’s really hard to say this in a nutshell, but I think an Evangelist is someone who helps people be successful with the technology from that company– and further helps set people up for making an informed decision about how they can be successful. What this ends up meaning is Evangelist may be useful to you even if you’re not a customer of that company’s products. Because when it comes to a good Evangelist, they’re giving you information that — yeah, it’s gonna really help you if you use that company’s products, but they’re also giving you really valuable information that’s going to have implications agnostic of what products you’re using.

For me, I want to deliver value and insights about DevOps however you’re doing DevOps. I want it to be valuable to you. I would love it if you would use our products, but even if you aren’t, in everything I do I want there to be takeaways that are valuable. Even if you’re not doing DevOps right now, the information can be useful if you’re keeping it in the possible things you might do. If you are doing DevOps now, I want to include concepts and patterns you can work in regardless of the products that you’re using.

My job at Redgate combines Evangelism with involvement in the sales process

An interesting thing about my job is — my job personally is not a “pure Evangelist job”. (I don’t know if there is such a thing as a pure Evangelist job.) But my job at Redgate is a mix. The job advertisement for my position said: up to 40% of your time is going to be spent working with the sales folks, as part of the sales process. But not as a salesperson, as an Evangelist.

This might sound kind of weird, because I have said hey, part of Evangelism is including things that are meaningful even if you aren’t using the products. That’s still true.

The interesting thing I think about sales — and I think this is an evolution of the sales process– is more and more companies need to tap into insights about the customer and really understand the customer’s problems. Companies are turning to different models of purchase, different subscription models. Organizations don’t want to sell you something you buy one time. They want to sell something that you renew.

Renewal is incredibly important to software companies. The points in sales is not just to sell a product once, you need your customers to use your products and to become successful with them, because you want them to continue happily using (and renewing) your product in the future. That is a huge important thing. So really understanding where the customer is coming from, what their problems are, and how to solve those problems is a very important part of the sales process.

Working with customers is the best part of my job

My absolute favorite part of my job is interacting with new and renewing customers as part of the sales process. Because when you interact with potential customers and existing customers, you learn about all these different real world scenarios. I think the value that I provide to Redgate as an Evangelist is that I’ve been a database person for twenty years. For a long time, I’ve been working with how to write code, how to optimize SQL Servers, how to optimize processes. I’m always learning more. So in terms of my experience, it helps me really understand where customers are coming from when they’re struggling with: how do we safely deliver database changes at a high rate? How do we do this without overworking our people so that they’re dragging in the next morning and aren’t even awake enough to do code review? How do we optimize this process and make happy teams that work well, that have stable systems, and we have everything humming perfectly– so we can really churn changes through?

That’s not easy, but my job as an Evangelist is to have insights into: how do we safely make these changes? Working with different customers, it resonates with so many different experiences I’ve had.

Every software development group is unique. Everyone has their own problems, and everybody has a certain amount of anti patterns that work for us. The job isn’t just, hey, there’s these best practices. It’s much more about figuring out: how do we come up with unique ways to solve these problems and get to the result we want. We’re gonna follow a lot of best practices, but probably we can’t just implement all these best practices. Because there’s always unique requirements and unique factors that mean not every best practice fits.

So a lot of what I do is listen for: what are the really important requirements, and how do we map best practices to this? How do we creatively come up with solutions to the places where the best practices don’t match? That helps us picture the future state. Then there’s the further problem of: how do we get from here to the desired future state? Often we can’t do it all in one go. Often we may have to take smaller steps to get there.

Working with customers in the sales process — I have found that it’s the secret to making Evangelism exciting for me. Because I get to hear from customers about real problems, and I get to help brainstorm creatively to come up with solutions for those– and help unstick people from getting blocked. To me that is just a ton of fun.

The culture of your sales team matters

I do think that the culture of the sales team you’re working with matters. I’ve worked with different sales teams throughout my career, and I’m not gonna name any names here — I’m not talking about now at Redgate– I have worked with sales teams who tended to over-promise. They promised vaporware. They were selling things that don’t quite exist.

Being an Evangelist in that context would be deeply uncomfortable, and probably wouldn’t work. Because a big part of being an Evangelist is being straightforward with people. Being honest and and having credibility.

I can’t sell vaporware. I’m not going to sell vaporware. We’re working in a culture where that would be a hard fit. I do think I think more and more with software, because of the importance of renewals, because of the importance of customer happiness, I don’t think those practices are gonna survive and thrive.

I think more and more sales processes are going to need to become more transparent to be successful. Otherwise, later on, people are going to be looking at those deals and they’re gonna say: why why didn’t this customer renew? Well, if we sell them something that doesn’t exist, if we sell them something that doesn’t work for them, that customer is not going to end up being super satisfied.

So I have been thriving as being part of the sales process, but if you’re if you’re looking at different Evangelists in other companies, their ability to be part of sales will depend on on the sales culture.

The future for Evangelists / Developer Advocates

I think Evangelist jobs are great. It’s definitely a specific personality type: you have to like to talk, you have to like to listen, you have to like to interact with people, as well as create presentations and things like that. But if you are a person who is interested in teaching and in learning, it can be a fantastic fit.

My job does technically sit within a marketing department. I think that’s great. In the past, I thought of marketing as being just a department that put out a good image. “We need to make things look good.” More and more, that is changing.

With the business changes that are happening inside companies — with the “digital transformations” that get talked about at the executive level — the role of marketing is about helping the company have insight into the customer.

What is the customer saying? What is the customer doing? What resonates with the customer? What excites the customer? What frustrates the customer? These insights are incredibly critical to helping a company survive both in terms of the messages you’re giving to people — because you want to help people understand when you can solve their biggest pains. But also in terms of shaping the product design. Customers can easily compare and easily switch service providers more quickly than ever using phones and computers. People have choice.

We have more and more interesting ways of delivering messages to people, too – and I think Evangelists are a really important part of that. That has to do both with reaching people through things like podcasts, through things like Twitch, through trying out new communication methods.

But also in terms of listening to the customers, I get insights from customers in Slack channels, for example the SQL Server community Slack channel. Sometimes I get them by email, I get them by messages on my website. I also get them through that contact with customers. The great thing about that [contact with customers] is that I can learn in-depth about: what are the problems that you’re facing? What are the real pains you have? What’s really important to you, and what is the best plan to get there?

Do our company’s products fit in that plan? Sometimes they will. Sometimes they won’t. The success of marketing and the success of sales is finding places where it really works, because those are going to be the customers that not only buy, but the customers that renew. The customers that tell other customers: hey we found a great way to solve these problems using those solutions.

So, have I found a short answer to what an evangelist that I could say at passport control? Well, to be honest, at passport control, I think it’s just easier to say that I am a software developer or sales engineer. We can all describe our jobs in different ways, right? My job is not a sales engineer, but a related job title is a little bit easier to explain to someone who doesn’t work in software. For better or for worse, the term ‘Evangelist’ is just very heavily loaded.

If you have ideas about the best possible job title for an Evangelist, I would love to hear it — because I still haven’t come up with that perfect term yet. I like the term Developer Advocate, but I’m still not 100% sold that it’s the best way to describe the job.

So, anyhow, this episode is for all of those have you been asking, “hey Kendra, what is it that you’ve been doing these days?” I’ve got some more episodes in the queue coming up, I’ve got some more questions to answer. Looking forward to talking to you folks again soon, and making Dear SQL DBA more regularly again. Thanks, folks!

One controversial topic in database development is how to properly store and deploy database changes.

This is generally described as choosing between two options, which are approximately as easy to understand as Greek philosophy:

• State based – A Platonic view of the database as a snapshot of a set of forms in a given state at a point in time
• Migrations – An Aristotelian view of the database defined as a series of scripts which generate change over time

Yeah, I never really liked arguing about philosophers, either.

Scratch all that. There’s a simple question that you can use to decide whether a state-based or migrations-based approach is right for a given database.

Here’s that question:

Do you want at least 95% of your database changes to deploy using auto-generated code?

There you go, that’s it.

Look back on your life-long experience with Clippy, with Siri, with every automated telephone system you’ve called in the past, including those where you ended up yelling “CANCEL MY ACCOUNT!” or “OPERATOR! PLEASE CAN I SPEAK TO AN OPERATOR?!?!” into the line because you were stuck in a loop.

Think about Alexa, who may get into conversations with your television sometimes. Think about the driving directions from Waze that have you make four left turns in a row. Think about those times when you wonder if Google Maps is sending you somewhere simply so it can pick up traffic info, or if it really thought that was a good idea.

If you think about those examples and want to use un-modified computer generated database change scripts 95% of the time, a primarily state-based approach is right for you.

That’s because a state-based approach looks at the current state of the in source control, compares it against a live database representing the desired state, and generates the shortest “directions” to get from one to the other.

If you want to give custom directions 5% of the time or more, a primarily migrations based approach is right for you.

Why 95%?

There are 50 ways to leave your lover, and there are definitely more than two ways to store and deploy your database changes. That’s because vendors have created hybrid versions between state and migration approaches that allow you to primarily use one approach, and occasionally use a bit of the other. (Disclaimer: I work for one of those vendors, here’s a whitepaper where we talk about state-first vs migration-first, and why we offer hybrids rather than absolutes.)

But these two approaches are difficult to unify, so even a “hybrid approach” has a strong side.

Do you mean migrations-first tools don’t help me write SQL?

Nope. Migrations based database deployment tools may have very nice engines to auto-generate changes for you, too.

The differences are that a migrations-first tool generally makes it much easier for humans to review change code and customize those scripts — making it easier for you to control exactly what happens, and how many steps are taken to make it happen.

It’s been a few months since I published an episode of Dear SQL DBA. I like the idea of doing future episodes with livestreaming on my Twitch channel, so I’m hoping to make this a regular thing again. The audio in this episode isn’t perfect, but I reminded myself that perfect is the enemy of good, and that sometimes it’s OK to be imperfect: people will choose if they want to listen or not. But I’ll work on getting audio quality back up in the next episode for sure.

And with that, here’s the recording:

Next week, I’m giving a free webcast for Redgate on DevOps fundamentals. DevOps is something I am a big proponent of for database administrators, developers, and company leaders. The webcast will be held on Wednesday, Jan 23 at 8 am Pacific / 11 am Eastern.

Click here to register and get a reminder to attend the live webcast, or to watch the recording after it airs.

I’ll share a lot of information in the talk. As a preview, here are three points which are often the source of misunderstandings:

Developer productivity is a top concern of C-level executives

Implementing DevOps isn’t something you do in an afternoon. Often, DevOps implementations begin from the C-suite as part of a digital transformation effort. DevOps can also be originated from small teams in the company who transform their own practices, share their success with the company, and enlist executive support to spread these practices to other teams.

Many DBAs and developers worry about the costs of implementing DevOps: not only the costs for tools, but also slow-downs in delivering new features while working on important projects that are the foundation of DevOps, such as standardizing the database code in Version Control and writing tests.

We should all be less worried about opening this conversation with company leaders, however. In September, 2018, Stripe performed a study with Harris Poll on software engineering efficiency. They got the following response from more than 1,000 C-level executives:

Slowdowns in productivity can be painful. But if they result in increasing the overall productivity of developers afterward, that slowdown pays off quickly.

The study overall found that a “lack” of developers is not the main problem: instead the problem is leveraging existing talent within companies better. DevOps directly addresses this problem.

Having a team who does DevOps “for you” doesn’t work

It’s common for companies seeking to release code changes faster to implement a “DevOps team.” While specialists can help larger teams adapt DevOps practices, inspire DevOps culture, and suggest patterns for work that get the cycle of continuous improvement started, these teams can’t do DevOps “for” other teams.

Andrew Hatch, Platform Engineering Manager at SEEK, has written about why SEEK created a DevOps team, what happened with the team, and why they ultimately decided they no longer need a DevOps Team.

In his article, Andrew Shared that the DevOps team took over the build, deployment, and operational support for the organization’s critical websites, but:

We had made rapid advances in our delivery processes but we still faced torrid nights on-call with software systems straining under the sheer volume of product being deployed to them.”

Why We Don’t Need a DevOps Team by Andrew Hatch

Doing DevOps is not only about release tempo. Other measures regarding stability are hugely important:

• Time to restore service
• Rate of failure of changes

By spreading the DevOps culture into developer and IT teams and making them autonomous units who could support their own releases, the team at SEEK were able to improve quality and maintain their higher release frequency.

Doing well at DevOps means including the database

Dr. Nicole Forsgren of DevOps Research and Assessment (now part of Google) is an internationally known researcher into DevOps practices. In the 2018 State of DevOps Report by DORA, they found that…

Teams that do well at continuous delivery store database changes as scripts in version control and manage these changes in the same way as production application changes.

2018 State of DevOps Report, DORA, Dr. Nicole Forsgren, Jez Humble, Gene Kim

Making this work well doesn’t mean getting rid of database specialists. Instead, DBAs are part of the DevOps culture:

Furthermore, when changes to the application require database changes, these teams discuss them with the people responsible for the production database and ensure the engineering team has visibility into the progress of pending database changes. When teams follow these practices, database changes don’t slow them down, or cause problems when they perform code deployments.

(Emphasis mine) 2018 State of DevOps Report, DORA, Dr. Nicole Forsgren, Jez Humble, Gene Kim

I have lots more to share!

In my upcoming webcast, we’ll talk more about what DevOps is, who is involved in DevOps, and the impact DevOps has on organizations.

Register here to join me in next week’s webcast.

I follow Cathrine, but apparently I need to follow her more closely, as I’d missed this tweet on the 14th. I had no idea these recordings were free and searchable.

These free sessions include way more than the Summits

I checked out which recordings are available, and my mind was blown — it’s not just past PASS Summit recordings (excluding the 2018 Summit, most of which are not currently free), but also user group talks, 24 Hours of PASS Recordings, everything!

How do I watch?

• Head to https://www.pass.org/Learning/Recordings/Listing.aspx
• Search for recordings by topic or speaker name
  • To find mine, simply type ‘Kendra’ in the search box on the left
• Log in when prompted, or create a free account
• Watch away

That’s about it. It’s a treasure trove of stuff.

A list of my PASS Sessions you can find on pass.org/learning

Here’s a list of my sessions on the PASS Site, along with notes of their “freshness” today:

2019

• Get Inspired: Celebrate the Careers of Three DevOps Heroes with Kendra Little
  • WIT Virtual Chapter, Jan 2019
  • Freshness

2018

• Fix My Functions: Speeding Up Scalar and Table Valued UDFs
  • Summit 2018
  • Note: this one isn’t showing as free on the ‘Learning’ site, but it was livestreamed during the Summit, so it’s available via other channels. Watch the recording on SQLWorkbooks.
  • Freshness
• How Keys & Included Columns Work by Kendra Little
  • Data Architecture Virtual Chapter, June 2018
  • Freshness
• Why is that Data Wrong? How Choosing the Wrong Isolation Level Causes Bad Results
  • 24 Hours of PASS, June 2018
  • Freshness

2017

• When Partitioning Indexes Hurts Performance (and How to Fix It)
  • PASS Summit, 2017
  • Freshness – batch mode for rowstore is coming up soon, but other than that it’s quite current
• Why Did My Clever Index Change Backfire?
  • PASS Summit, 2017
  • Freshness
• SSMS Shortcuts & Secrets – Kendra Little
  • DBA Fundamentals Virtual Chapter, Sept 2017
  • Freshness
• Why Did My Clever Index Change Backfire?
  • 24 Hours of PASS, Summit Preview, 2017
  • Freshness – note, this is a preview of the live summit session above

2016

• The Great Performance Robbery: Locking Problems and Solutions
  • PASS Summit 2016
  • Freshness
• 3 Skills Every Junior DBA Must Know
  • DBA Fundamentals Virtual Chapter, July 2016
  • Freshness

2015

• Take the SQL Server Index Quiz!
  • 24 Hours of PASS Summit Preview, 2015
  • Freshness
• How to Configure Quorum in SQL Server
  • HA/DR Virtual Group, May 2016
  • Freshness — a lot has changed in this area since 2015

2014

• World’s Worst Performance Tuning Techniques
  • PASS Summit 2014
  • Freshness
• Why Does SQL Server Keep Asking for this Index?
  • PASS Summit 2014
  • Freshness – main points are still true, but I haven’t tested the demo against higher compatibility levels to see if the same execution plans are still generated

2013

• How to Tell When Storage Is a Problem
  • PASS Summit 2013
  • Freshness is surprisingly high — I’m talking about interpreting DMVs and perf counters here, not which SAN to buy, so it’s still spot on

2012

• Index Psychiatry: Diagnose and Treat the Top 5 Disorders
  • PASS Summit 2012
  • Freshness – I show the sp_BlitzIndex procedure in this talk. That procedure has changed a lot since the talk, so it’ll look different. Concepts discussed are still true for disk based nonclustered rowstore
• SQL Server First Responder Kit
  • PASS Summit 2012
  • Freshness – medium

2011

• No More Bad Dates: Best Practices for Working With Dates and Times
  • PASS Summit 2011
  • Freshness – medium

Thanks to PASS for publishing these videos for free!

I had a lot of fun looking through my own videos, and I love having this library of free videos as a learning site for fundamental concepts.

Abstract: DevOps sounds great in theory, but in reality it’s tricky to transform your people, process, and tools.

In this interactive, poll-packed panel discussion, we will use real-world scenarios to clarify what DevOps principles are and how they help reduce manual work and improve productivity for software developers and database administrators.

We’ll step through situations that are tricky when it comes to making database changes, sharing patterns to avoid and how to approach and conquer problematic entrenched processes.

This session is designed to be accessible to both beginners and advanced practitioners: we’ll talk through definitions of DevOps terms and patterns and will encourage audience discussion.

Your panelists:

• Kendra Little – Product Evangelist, Redgate
• Hamish Watson – Alchemist, Morph iT
• William Durkin – Data Platform Architect, Data Masterminds
• Rob Sewell – Beard Twizzler

Check out more free GroupBy sessions here.

Session abstract: Whether you love or hate buzzwords, the big ones signify critical cultural changes. In this session, Kendra Little will explain what executives mean when they describe a ‘digital transformation’, why this transformation is happening across all industries, and how understanding this gives developers and database administrators an advantage in building their careers. You will learn what motivates CEOs to modify their business models in a digital transformation, and patterns and anti-patterns of companies that have attempted these transformations – with different results. You’ll leave the session with an understanding of the core ideas and philosophies behind digital transformation that will help you prioritize what to learn, guide your interactions at work, and strategize your career path

There’s a lot of information out there on data breaches. I’ve written before about one source that I trust — the Verizon Data Breach Report (DBIR).

The 2018 DBIR studied a sample of 2,216 confirmed data breaches, and of these it found that 28% involved internal actors. The DBIR uses a publicly accessible database of security incidents, and applies quality filters to data before including it in the report.

Only 28%? I heard that 75% of breaches come from “insiders”

Different studies sample different breaches, so it’s natural that there would be some variance on findings about who is behind breaches. However, I heard about a report where the variance was enough that I wanted to look into it: a presentation at the PASS Summit in 2018 cited a 2017 article which found that three quarters of data breaches came from “insiders.”

This figure seems very high to me. Every day we hear about data breaches in the news — from bagels to literally everything else. Could three quarters of these really be due to the malice or incompetence of employees?

Let’s find out where that number comes from.

Follow the article trail

I’m not going to link to the articles involved in spreading this statistic, because I think they’re clickbait. I followed a trail of:

• A somewhat legitimate looking security article which says, “nearly three-quarters of incidents are due to insider threats.” The article doesn’t define what insider threats are, but says “Not all insider threats are deliberate,” and then references a different study.
• The source referenced for the “nearly three-quarters” stat in that article is an ebook by a security tools retailer. In the undated eBook, they write…
  • “According to the most recent Clearswift Insider Threat Index (CITI) report, 74% of security breaches originate from within the extended global enterprise. ”

It looks like the original source of the “75%” number is the security company Clearswift. They used to publish their “Threat Index” as a PDF, but more recent years appear to simply be simpler press releases, such as this one for 2017.

What do we know about the methodology?

Studying data breaches is hard, for a few reasons. Not everyone wants to talk about them. Often, it’s a long time until the breach is discovered. And getting to the bottom of the breaches can be tough.

The folks at Clearswift describe their research as having…

surveyed 600 senior business decision makers and 1,200 employees across the UK, US, Germany and Australia

Clearswift 2017 threat index

It’s not clear how many of these respondents had confirmed data breaches that impacted customers, or the nature of how the causes of the breaches were assessed.

Reading the press release, here’s some clarification on the numbers for 2017:

• “Threats from an employee – inadvertent or malicious – make up 42% of incidents”
• “When looking at the extended enterprise – employees, customers, suppliers, and ex-employees – this number reaches 74%, compared to 26% of attacks from parties unknown to the organization.”

Looking at sources: do they define ‘data breach’ and ‘security incident’?

The folks who write the Verizon DBIR are very careful about defining terms at the beginning of their report — which is one of the reasons I’m such a big fan of it. Here are the definitions from the 2018 DBIR:

• Security incidents are: “a security event that compromises the integrity, confidentiality or availability of an information asset”. 
• Data breaches are: “an incident that results in the confirmed disclosure — not just potential exposure — of data to an unauthorized party.”

Clearswift uses both of these terms, but their threat indexes do not define them or differentiate against them. It often reads as if they use the terms interchangeably. That’s a big problem — and it may be that the people who are taking their surveys aren’t sure what the definitions are, either.

These numbers shouldn’t add up, should they?

Another puzzling thing about the Clearswift numbers is that they add up a bit too neatly.

If 74% of attacks originate from ‘inside’ (the extended enterprise, due to malice and accident) and 26% originate from hackers outside, then were there 0% of cases where hackers collaborated with a malicious employee? Or where hackers took advantage of a mistake?

Isn’t it natural, and even likely, that many data breaches have multiple points of origin?

Separating the research from the clickbait

When I’m reading about data breaches, I ask these questions:

• Is the source study clearly referenced and available for review?
• Does the source study define its terms clearly?
• Does the source study clearly state how many distinct confirmed data breaches were analyzed?
• Does the source study allow multiple causes for a data breach?

If all of those are a ‘yes’, it’s probably not clickbait.

In this case, I think it probably is.

This is a typical first world problem, but with a history of reporting tools ranging from temperamental Infopath forms to websites where you painstakingly click here and there for hours, there have been reasons for the complaints.

This year I decided to try out using PowerShell to add my contributions. This has been available for about a year thanks to François-Xavier Cat and a few friends, but I’d completely forgotten that it was possible until William Durkin reminded me about it on Twitter.

First time ever, I didn’t wait until the last minute to start thinking about my annual contributions. Instead, I took a little time to learn about how this works — and now I’ll keep my contributions in a spreadsheet and then upload them right before the deadline.

Here are my notes on what I found, and why I’ll still wait to upload.

Some setup is required

The documentation for setup is quite good. I used the main documentation and it went very smoothly.

One note: in the docs it mentions that your request will be reviewed and accepted by an administrator within a couple of business days. Mine appeared to be auto-approved and I didn’t have to wait at all, I was able to start using my subscription right away.

Caching sounds great, until it’s not

There are only two hard things in Computer Science: cache invalidation and naming things.

Phil Karlton

The most painful part of uploading contributions with PowerShell wasn’t getting the upload to work: it was figuring out that the cache for the MVP website doesn’t get cleared by PowerShell uploads which use the API.

This means that the contributions you see on your profile on the website are probably not the current version at all if you’re uploading via PowerShell, because that cache is way out of date.

This is true if you are:

• Looking at your contributions from a logged out state
• Editing your contributions while logged in. The website doesn’t even invalidate the cache reliably if you’re editing your contributions, I’ve found

Workaround: export all your contributions to a .csv file from the API and review them that way

I’m no scripting genius, but I came up with a simple command to export all my contributions for review:

 
Get-MVPContribution -Limit 500 | export-csv exportedcontributions.csv

This also gives you the ContributionId for each of your contributions, in case you want to delete any.

Inserts are easy, updates are hard

One of the main questions I had when starting out was whether re-running New-MVPContribution would be smart enough to identify matching existing contributions with the same date and description, and update other information, such as the ‘AnnualReach’.

Why this matters: Microsoft doesn’t publish the criteria they use to select MVPs, but if I was them, I’d pay a lot of attention to those reach numbers.

So when it comes to items like videos, I want to report the reach at the end of the review cycle, not just after I post the video.

Finding: New-MVPContribution does not find and update contributions. If you rerun an import for a .csv file with contributions, it’s very easy to create duplicates.

If you accidentally create duplicate contributions, no worries

It’s simple to get a list of all your contributions using Get-MVPContribution (see my workaround above), and then run a delete loop using the ContributionIds with code like this sample in the documentation.)

All in all, I like this way of managing contributions

My new way of doing this is…

1. Use a simple spreadsheet saved as a .csv file to track my contributions: I’ll update it once a month with a summary of the talks that I’ve done that month, and links to videos I’ve posted.
2. A couple of weeks before contributions are due, I’ll update the reach numbers for the videos in the spreadsheet. I’ll also add in entries for aggregate information where helpful — for example, I add a single line for all my blog posts with reach information, instead of a line for each post. Then I’ll do a single upload for the year from the .csv file.
3. After upload, I’ll manually edit a contribution in the GUI and see if this triggers a cache clear to validate display of the contributions (I’ve had mixed results). Setting a reminder to see how they display the next day isn’t a bad idea, either.

It’s certainly not perfect, but it’s waaaaaaay better than an old infopath form, so I’m happy to have it.

I recently did a bit of research on the source of data breaches. In this post, I’ll talk a bit about my current favorite source for breach information, and a bit of what I learned.

Verizon publishes the ‘Data Breach Investigations Report’ annually 

The 2018 edition of this free report by Verizon Enterprise Solutions is the 11th edition — they’ve had some practice. The reports are extremely well detailed, and shockingly, they’re even entertaining to read.

The reports don’t claim to discover all data breaches. After all, not all data breaches are discovered, and those that are discovered aren’t necessarily reported.

2,216 breaches, analyzed

The 2018 report covers 53,000 incidents, defined as “a security event that compromises the integrity, confidentiality or availability of an information asset”. 

It also covers 2,216 breaches, which are defined as “an incident that results in the confirmed disclosure — not just potential exposure — of data to an unauthorized party.”

These numbers (and the screenshots I’m sharing below), do NOT include  breach data involving botnets. 43,000 “successful accesses via stolen credentials” associated with botnets are handled in a special insights section of the report.

Are data breaches caused mainly by insiders or outsiders?

A colleague of mine mentioned that he’d recently seen some numbers suggesting that data breaches were mainly perpetuated by insiders to an organization — but he hadn’t been able to track down the source of those figures or substantiating data. With the number of data breaches we see these days, that’s a pretty dark view of employee-employer relationships!

Here’s what the Verizon report shows in terms of who is behind the breaches:

These figures are regarding those confirmed data breaches, not all security incidents. While 28% involve internal actors, the bulk of data breaches are coming from people outside the organization, finding their way in by using malware or social attacks, or by exploiting vulnerabilities created due to errors.

Who can a database administrator trust?

For those internal actors involved in data breaches, my first thought was, “Well, so WHO WAS IT?”

That’s answered a couple pages later. While the exact internal actors weren’t found for all of the reported data breaches, analysis was done for 277 data breaches:

As much as database administrators like to focus on denying permissions to developers for production, developers were much less likely to be involved in data breaches than system admins.

And who exactly are system admins?

Well, I’m guessing that includes… the DBAs. 

Awkward.

This is remarkable given that you don’t need production access to cause a data breach

It’s a pretty normal practice in an Enterprise to make copies of production data for use in internal environments. Copies of data are used by analysts, developers, product managers, marketing professionals, and more.

Redgate’s 2018 State of Database DevOps Report found that 67% of respondents use production data in development, test, or QA Environments, and that 58% of respondents reported that production data should be masked when in use in these environments:

There are good reasons that production data is spread around like this: performance is extremely difficult to predict using data that doesn’t have a very similar distribution and similar size to production data.

But after many years of working in IT, I know that most often this data is not modified or masked after being duplicated. These environments tend to be far less secure than production environments, and they are a very rich target for data breaches — even if it’s not the developers themselves intentionally causing the data breach.

The rise of malware and social attacks means that all environments in our company can the the source of a data breach.

Update: The survey is now closed, thanks folks!

When I began working with databases, nobody talked about DevOps. It was a few years before I heard the words ‘Agile’ and ‘Extreme Programming’, (which I still read as “EXTREEEEEEMMMMME programmin!”).

A lot has changed since then. But a lot hasn’t changed as well. 

Please help us track the history of how we work with databases by taking the Redgate State of Database DevOps survey today. I believe it’s helpful to our whole community to participate in this survey, and this post explains a few reasons why. 

The survey…

• Is hosted by SurveyMonkey, no login is required
• Takes around 10 minutes
• Allows you to remain anonymous OR provide contact info to get a chance at winning a $250 Amazon gift card
• Is for everyone who works with databases, regardless of whether you use DevOps or are a Redgate customer
• Closes Friday, November 30th

The survey creates a history of how we manage database changes

I was recently listening to a podcast on CIOs and the Future of IT. The hosts discussed that executives need to be very forward-thinking, and focus on where the company is going more than where the company is.

One side effect of this: executives often think that their organization is ahead of where they actually are, in terms of changing processes and adapting to business initiatives.

This makes a lot of sense to me. When I learned to ride a motorcycle, the instructors emphasized that looking forward to where you are going is important when it comes to executing turns correctly. You need to have a wide field of vision and see obstacles, but if you stare at obstacles too much you are more likely to hit them. 

However, we need a feedback loop! We need all sorts of people who work with databases to discuss what actually happens. This allows Redgate to document and share what’s really happening right now in our technical patterns and practices. You can help us do that by answering the survey (and things like this are why we ask about your job title in the questions).

Responding helps us identify and share with you information to help you drive change

Ever had an idea which would make a big difference for your organization, but you weren’t sure how to make it catch on?

This might be an idea about DevOps, or it might be about data science, cloud adoption, general automation– any project big enough that it’s not simply something you can implement yourself.

It’s difficult as a DBA or developer to know how to approach these ideas. Do you need executive support? Is there another way you can help move it forward? The State of Database DevOps survey asks questions about how people have moved projects forward to help map out how change is instigated and sustained in companies.

This helps Redgate discover and share with you how you can get projects moving (and you can apply this to projects other than DevOps).

Responding provides the community with information on how database development and administration jobs are changing

I hear from a lot of folks who are concerned about the future. There’s so much to learn, and it’s increasingly difficult to prioritize between learning more about the tasks you work on today, and learning more about the tools which you may use tomorrow. 

The results from the State of Database DevOps help show the community: how are roles changing? What’s still the same? This will help you answer those tough questions about what to focus on.

Take the survey today!

Please share the survey with your colleagues (shortlink: redgate.com/DBDSurvey). And remember, the survey is only open until November 30th, so complete the survey today.

There was a time when I saw PowerPoint as a necessary evil – a way of conveying ideas that I wasn’t crazy about, but which worked. These days, my perspective has changed quite a bit.

I’ve become a PowerPoint fan for a couple of reasons. First, I’m more proficient with PowerPoint, so I can put together presentations quickly and my slides carry greater impact.  Also, the PowerPoint team has added features that I believe help me deliver presentations more effectively.

This describes one of those features: the new ‘Zoom’ functionality.

‘Zoom’ is about dynamic presentations (rather than ‘zooming in’)

I think it’s incredibly important to give your audience context for where you are in a presentation — especially in a talk longer than 5 minutes. I like to give folks an outline of the narrative at the beginning of the talk in the agenda. While I’m going through the material, I like to return to that agenda and show where we are in the talk.

This has more than one benefit: first and foremost, it helps keep your audience from being too restless, because they understand more how much you’re going to cover. But additionally, I find that it helps solidify learning for your audience by repeatedly contextualizing details within the larger narrative structure. In other words, revisiting an agenda effectively can help prevent “not seeing the forest for the trees.”

For years, I’ve built slides manually that do this — but as you add and remove sections from your talk, it becomes a real pain to update. The ‘Zoom’ feature makes it much easier to build a dynamic agenda, and it’s more powerful than manually created slides can be, too.

It’s easier to show this than it is to describe it, so here’s a 1.5 minute video showing what it looks like

Sorry, this isn’t available in every installation of PowerPoint

As of the time of this writing, Zoom is available in PowerPoint for Office 365, PowerPoint for Office 365 for Mac, and PowerPoint 2019. The documentation from Microsoft is here.

There has never been a better time to start a project to champion data privacy

In a recent Harris poll sponsored by the payment company Stripe, over 1,000 C-level executives were asked to rate which factors they feel are most threatening to their business.

The #1 item that executives rated as “somewhat” or “very” threatening to the success of their business is security / data breaches. The #2 rated threat to these businesses? Increased regulation. 

Data masking mitigates these top threats

One of my very first jobs when I began in IT was to build out environments for developer and test teams at a software development company. At first, we didn’t mask any of this data. After problems arose, we began masking only a single field. That was a huge mistake.

In this upcoming free webcast, I’ll share the following in just 30 minutes:

• Why data professionals have a history of neglecting privacy (just like I did in that first job)
• The three industry drivers for data masking
• Why regulations have become a top risk to C-level executives
• Key elements of static data masking tools to consider when evaluating products
• Pains that people run into when implementing data masking (and how Redgate tools address those pains)
• An overview of the data masking process
• Next steps to take in making a case for a data masking project at your organization

Now is the time to become a privacy champion

Whether or not you are an IT leader, a developer, or a DBA, right now you have a huge opportunity to make a difference for your organization,  your customers, and your own career by championing privacy.

Yep, your own career! Those top two C-level concerns mentioned at the beginning of this post means that initiating projects like data masking and seeing them through from proof-of-concept to implementation is a big resume-building career move. That’s true whether you choose a Redgate solution, or use the information in this webinar to go a different route. 

Join me in this upcoming webinar to learn the initial steps that you can make to instigate changes in your company — and to help mitigate the top risks that are keeping C-level executives up at night.

DevOps is the way of the future – but not all executives understand that yet

DevOps practices are powerful tools to build productive, engaged teams who constantly learn and regularly deliver value to their customers.

High DevOps performers report greater workplace satisfaction than their peers. But implementing DevOps isn’t a matter of being great at writing code: to become a high DevOps performer, you need the powerful catalyst of executive support.

Communicating the value of DevOps to company leaders requires changing your perspective

In this webinar, I discuss the value of DevOps from the perspectives of CEOs, CIOs/CTOs, and Managers. We explore how the role of CIOs and CTOs are undergoing a major transformation, and how DevOps aligns with that transformation.

You will discover a fresh take on how to discuss DevOps with company leaders, and an understanding of how to explain the value of DevOps to people in these roles.

My session was live-streamed, and the video, scripts, and slides are below.

Want to watch more Summit sessions for free? Check out PASS TV here.

Video (1 hour 10 minutes)

Note: if you’re just here to see the SQL Server 2019 (CTP2.1+) scalar UDF inlining, that starts at 54 minutes into the video. 

Scripts

Slides

Below the slides is an outline of the content, generated by SlideShare.

1. Speeding Up Scalar and Table Valued UDFs Kendra Little, Redgate Fix My Functions

2. Kendra Little Evangelist at Redgate Founder, SQL Workbooks Microsoft MVP, MCM Kendra.Little@red-gate.com @Kendra_Little

3. This talk Level: 200 Audience: DBAs & Devs Goals: Speed up functions

4. “It is a known fact amongst practitioners that UDFs are “evil” when it comes to performance considerations [35, 28].”

5. Agenda

6. Why we use functions Modular meerkat

7. Why functions? Modularity Code reuse Simplify queries

8. Function types Scalar: returns a single value Multi-statement TVF: returns table Inline TVF: returns table

9. CREATE FUNCTION [schema].[function_name] (@parameter_name AS INT) RETURNS INT –WITH SCHEMABINDING, … AS BEGIN RETURN END GO Scalar function syntax

10. CREATE FUNCTION [schema].[function_name] (@parameter_name AS INT) RETURNS @return_variable TABLE (/* table type definition */) –WITH SCHEMABINDING, … AS BEGIN RETURN END GO Multi-statement TVF syntax

11. CREATE FUNCTION [schema].[function_name] (@parameter_name AS INT) RETURNS TABLE –WITH SCHEMABINDING, … AS RETURN ( ) GO Inline TVF syntax

12. SELECT TOP (10) qp.dbid, qp.query_plan, cp.size_in_bytes / 1024. / 1024. AS size_in_mb FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp ORDER BY size_in_mb DESC; CROSS/OUTER APPLY and TVFs

13. Demo: why so slow?

14. sp_WhoIsActive – free procedure from Adam Machanic: WhoIsActive.com

15. sp_WhoIsActive – free procedure from Adam Machanic: WhoIsActive.com

16. Lightweight Statistics Profiling SQL Server 2014 SP2 through 2017 • Trace Flag 7412 • Install KB 4078596 (2016 & 2017 only) SQL Server 2016 SP1+ MUCH lower overhead SQL Server 2019 no trace flag needed https://blogs.msdn.microsoft.com/sql_server_team/query-progress- anytime-anywhere

17. sp_WhoIsActive Free procedure Written by @AdamMachanic WhoIsActive.com

18. Find what functions hide Curious corg

19. Get tuning data for functions Estimated & actual execution plans Finding function calls in operators UdfCpuTime and UdfElapsedTime in actual execution plans

20. Demo: spy on function internals

21. Estimated plans help! Scalar functions and multi-statement TVFs: estimated plan shows the function logic • Does not appear in an actual execution plan • Plans for the calling query and the function are stored in sys.dm_exec_query_stats, but you must find them individually

22. UDFs and parallelism TSQL scalar UDFs – serial plan Multi-statement TVFs – serial zone Computed column with TSQL UDF – parallelism eradicator, BEWARE

23. MSTVFs and row estimates SQL Server 2005 – 2012 SQL Server 2014 – 2016 SQL Server 2017+ 100 1 ?

24. Interleaved execution Part of adaptive query processing, all Editions Introduced in SQL Server 2017… • MSTVFS only • Read only queries • Cannot be on the inside of an APPLY • Compatibility level 140+

25. Interleaved execution (continued) Diagram by Joe Sack @JoeSackMSFT https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/04/19/i ntroducing-interleaved-execution-for-multi-statement-table-valued- functions/

26. Scalar UDF Inlining Clever crow

27. https://aka.ms/iqp

28. Why are scalar UDFs slow? Executed row by agonizing row Scalar operators not ‘costed’ No cross-statement optimization No parallelism https://docs.microsoft.com/en-us/sql/relational-databases/user-defined- functions/scalar-udf-inlining

29. Automatic inlining Rewrite scalar UDF Substitute rewrite into calling query Then optimize https://docs.microsoft.com/en-us/sql/relational-databases/user-defined- functions/scalar-udf-inlining

30. Demo: wizardry

31. Controlling behavior Database compatibility level 150 CREATE FUNCTION … WITH INLINE = OFF USE HINT (‘DISABLE_TSQL_SCALAR_UDF_INLINING’) https://docs.microsoft.com/en-us/sql/relational-databases/user-defined- functions/scalar-udf-inlining

32. No scalar UDF inlining if it… Uses GETDATE() Uses table variables or TVPs Is in computed column Is in a check constraint https://docs.microsoft.com/en-us/sql/relational-databases/user-defined- functions/scalar-udf-inlining

33. Takeaways

34. Tips for tuning functions Scalar UDFs and Multi-Statement TVFs inhibit parallelism Use SCHEMABINDING if your function doesn’t do data access Use inline TVFs (single statement) or persist data when possible

35. The future of scalar UDFs 2019 inlining is VERY compelling Edition has not been announced Releasing with “high coverage”

36. References & links Lightweight query profiling reference – Pedro Lopes https://blogs.msdn.microsoft.com/sql_server_team/query- progress-anytime-anywhere/ SQL Server Functions, the basics – Jeremiah Peschka https://www.red-gate.com/simple-talk/sql/t-sql- programming/sql-server-functions-the-basics/ Froid: Optimization of Imperative Programs in a Relational Database – Karthik Ramachandra et al http://www.vldb.org/pvldb/vol11/p432-ramachandra.pdf

37. References & links continued Interleaved execution for multi-statement TVFs – Joe Sack https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/04/19/introd ucing-interleaved-execution-for-multi-statement-table-valued-functions/ Parallelism inhibitors – Paul White http://sqlblog.com/blogs/paul_white/archive/2011/12/23/forcing -a-parallel-query-execution-plan.aspx

38. Thank You Where to find me… @Kendra_Little Kendra.Little@Red-Gate.com

It’s the second main-session day of the Summit for the Professional Association of SQL Server. I had a great breakfast this morning with Brent Ozar, along with Nathan and James from my first timer group. If you attend Summit for the first time in the future, this is a great program and you should join up.

I’m jazzed to follow the keynote this morning.

Like yesterday, I’m going to capture the highlights into a slide deck and publish them below. Let’s do this!

Highlights… and lowlights

So… I didn’t love it today. I have some comments in the slides as to why.

As a presenter, and as someone who can influence event organizers, I have a lot to reflect on after today about how we can improve at inspiring people across cultures and genders in an international community. That’s the positive takeaway I have.

Good morning from Seattle, at the Summit for the Professional Association of SQL Server.

I’m lucky enough to be sitting at the blogger table this morning, watching the keynote of announcements.

I’m following along with the keynote and making notes on slides — and I’ll share those slides with you below.

Don’t feel like reading or rewatching the presentation?

Join me on Tuesday, Nov 20th, with Grant Fritchey and Steve Jones in a webcast to sum up everything we learned at PASS Summit!

Keynote summary in slides

Text from the slides

PASSION award winner: Michael Johnson

Rohan Kumar

• Hybrid cloud is the true enabler for digital transformation
• AI is helping MS customers
  • Understand their customers and better meet their needs
• • Improve their operations
• Critical to build training model on data that spans the hybrid estate

• CTP 2.1 has been released
  • Monthly releasesUsing Azure to get feedback on a constant basisRequest for engagement from you

Conor Cunningham and Bob Ward

• Demo: Removing page latch waits in tempdb
• Behind the scenes, it’s using Hekaton in system tables in tempdb to speed this up
• Not yet in preview
• Will be in SQL Server 2019

Asad and Nellie

• Demonstrating Azure Data Studio and Data Clusters
• Use Python and Notebooks
• Can query HDFS using native features in SQL Server engine to read that data
  
• Access multiple data sources through SQL Server

More from Rohan Kumar

• Azure Database Migration Service
• Near zero downtime
• Migrate at scale
• Optimize IT Infrastructure
• Azure SQL Database – 5 million active at any give time, a pedabyte of telemetery data every day
• Microsoft is using machine learning against the data
• Managed Instances
• Big push to get customers here
• General Availability of Business Critical SKU starting December 1
• Azure SQL Database Hyperscale
• 4 TB limit on initial implementation
• Have been working on rearchitecting
• Scales out storage over various nodes
• Has abilities for fast point in time restore
• Scale storage and compute independently
• Accelerated DB Recovery & Machine Learning Services
• Azure SQL Database
• Goal is to make sure that no matter what happens, recovery happens in “constant time”
• Machine Learning workloads in Azure SQL Database – enables migration for these features

Lindsey Allen

• We don’t have fancy machines
• We have 2 socket machines
• Most customers don’t like figuring out sharding with partitioning keys – whatever key you choose is wrong
• This is going to happen automatically behind the scenes
• Now they take snapshots and restore from those – not a size of data operation
• Accelerated Database Recovery
• How long will it take to restore?
• Painful for customers
• Painful in Azure as well
• Can aggressively truncate the log – even in full recovery mode
• https://docs.microsoft.com/en-us/azure/sql-database/sql-database-accelerated-database-recovery

Deborah Chen

• Multi-Master Replication in CosmosDB
• Azure Cosmos DB
• Apps can read from any region and write to them as well
• Drawing app
• Data being replicated to Japan in almost real time
• Go to instance in Japan and drawn, and it app

John Macintyre

• SQL Data Warehouse competing
• 30% less expensive than Amazon Redshift
• Fastest cloud DW based on benchmarks
• Demo with 6 trillion rows of data
• TPC-H Benchmark running
• Processing over a trillion rows a second
• Demo: prioritizing workloads in SQL Data Warehouse
• Demonstration of queries being queued, waiting for system resources on a busy system
• Connect to a dashboard using a special service account
• That work gets prioritized and jumps the queue
• Feature name: workload management

Ariel Pisetzky of Taboola

• Azure Data Explorer
• Used across Microsoft internally for several years to explore logs and do analysis
• Ingest unstructured and semi-structured data
• Not a lot of prep work to do quick queries
• Demo: exploring data
• Can query and show results in graphs or tables
• Using the tool to report on the customer experience
• “What is the 5% of the slowest recommendations we provide?”
• • Identify spikes where they are above 800 ms
• • Using queries to zoom in and identify who is impacted

Patrick LeBlanc

• Dataflows
• Point Power BI to any data lake store
• It will figure out connectivity and transformations for the scenario
• Big customer request
• “Self-service for big data” stored in the data lake
• SSRS Reports in Power BI
• Reporting Services – things went quiet for a long time
• BUT NOW WE HAVE MORE
• Paginated reports in Power BI Service
  

Deepsha Menghani

• Demo: Shell health & safety portal
• Predictive alert
• Azure database notebook
• Need to have data to feed and train – and you can’t just burn down a gas station – she checked
• Can do a keyword centric search for images to feed into the model
• Image classification – databricks lets you build on existing models
• Live demo: image detection
• Hold a cigarette in front of a camera
• Image identified and detected
• Notification suggesting stopping a pump
  

And we’re done! Goodbye from Rohan

What if you could assess the performance level of your team by asking one simple question? In his recent webinar with Redgate, Gene Kim (@realgenekim) suggests that you can.

In this 30 minute session, I discuss three insights from the recent ‘Gene Kim joins Redgate to discuss The 2018 Accelerate State of DevOps Report’ webinar (recording). I also dig into why these insights make a compelling argument for modifying your organization’s change control process.

Key Insights from Gene Kim that I discuss in the session

1. The question, “To what degree do we fear doing deployments?” on a scale of 1 to 7, is a simple way to measure whether or not your company is a high performer
2. Although database operations has been a high-spend area for a long time, it has been comparatively impoverished in most organizations when it comes to tooling and automation, as compared to other areas of Software Development
3. Becoming a high performer at DevOps includes completely rethinking how change control is managed for your IT organization

I’m excited to have a session accepted to GroupBy, a free online conference targeting the Microsoft data platform community. The conference is sponsored by Brent Ozar Unlimited, and sessions are chosen by community votes. 

My session will be given on Fri, Dec 21, along with five other terrific looking sessions. You should  register for GroupBy here.

About my session

My session is called “What ‘Digital Transformation’ means, and how you can use it to advance your career (without being a robot).” Here’s the abstract:

Whether you love or hate buzzwords, the big ones signify critical cultural changes. In this session, Kendra Little will explain what executives mean when they describe a ‘digital transformation’, why this transformation is happening across all industries, and how understanding this gives developers and database administrators an advantage in building their careers.

You will learn what motivates CEOs to modify their business models in a digital transformation, and patterns and anti-patterns of companies that have attempted these transformations – with different results.

You’ll leave the session with an understanding of the core ideas and philosophies behind digital transformation that will help you prioritize what to learn, guide your interactions at work, and strategize your career path.

Group By session abstract

I wasn’t sure that this session would get votes

I’m particularly excited that the community of voters saw value in this topic because I haven’t seen any talks on this topic before in the data platform community. 

While we hear the phrase ‘digital transformation’ in conference keynotes frequently these days, I think many technologists don’t think much about what it means. Or worse, assume it doesn’t mean anything. 

‘Digital transformation’ is meaningful, and is absolutely worth understanding

For the last several months, I have been researching what worries and inspires CEOs and CIOs. I was surprised to find a large body of studies and predictions of massive trends in “digital transformation” across all industries.

What I once thought was “just a buzzword” is a shorthand that executives use to represent major changes in business models. It’s incredibly useful for practitioners, team leads, and managers to understand what the goals of a digital transformation are and how success is measured. This understanding helps you communicate better with executives, align your team’s work with business strategies, and ask for the right resources and headcount to execute on critical tasks for your organization.

I hope you join me in December at GroupBy!

I was lucky to get an opportunity to present a session to some college students at Seminole State College in Florida last week. I was in town for SQL Saturday Orlando– a fantastic event on its own. The organizers run a simultaneous seminar for students at the college studying software development and IT topics, and it’s an opportunity where they can hear from professionals in IT professions. I gave a session on interviewing best practices.

The students were a terrific audience, and I really enjoyed speaking. The slides I shared that day are here:

Prefer to listen on the go? Subscribe on iTunes, listen on Google Play, or plug this URL into your favorite podcasting app: http://dearsqldba.libsyn.com/rss#

If you enjoy Dear SQL DBA, reviews  on iTunes help me out a ton.

Slides from the presentation

Spoiler: Visual Studio suggested updating Microsoft’s SQL Server Data Tools, which failed for me and wasted a lot of time. Updating Visual Studio did fix my issue.

The basics on my setup

When I installed Visual Studio 2017, I selected the ‘Data storage and processing’ workload, which contains SQL Server Data tools as well as some of the Redgate tools:

I used the default checkboxes on the right there, installing everything except F# desktop language support.

First, I created a new project

In the ‘SQL Change Automation’ window, I clicked ‘Create Project’, selected ‘SQL Change Automation Project’ and gave it a name, and click OK. This launched the wizard to set up the project.

I clicked ‘Get Started’, and proceeded to the step to configure my connections.

At this point, I was prompted to specify my development database and deployment target. I chose two databases on my local SQL Server 2017 instance.

I clicked ‘Next’, and that’s where things got wacky.

Unknown SQL Server Platform – The target platform for this project is not supported by the installed SQL Server tools.

When I proceeded past this point and had SQL Change Automation create my baseline, it stopped pretty quickly and I saw a message…

When I clicked on the blue link, I was taken to a download for the most recent version of Microsoft’s SQL Server Data Tools.

After I carefully read the page and made sure I didn’t have Visual Studio extensions installed that would cause a problem, I attempted to update the SQL Server Data Tools and found that installation ran for 7-10 minutes, then failed with an “incorrect function” error.

I restarted Windows. I tried updating again. Same failure.

What fixed this for me: updating Visual Studio

I went into the ‘Help’ menu in Visual Studio to capture the version numbers of my extensions (that’s under Help -> About Microsoft Visual Studio), and that’s when I realized: I should probably update Visual Studio before I wasted any more time chasing this error.

After I opened “Check for Updates,” I saw that there was a bunch of stuff that Visual Studio wanted to fix. I told it to apply those changes (it really wasn’t clear what the changes were, I just let it run), and then….

Everything worked after that

Yep, everything was fine. I still had the exact same version of SQL Server Data Tools as before (15.1.61808.07020, for the record), but Visual Studio was now happy to use it, for whatever reason.

I was able to complete my baseline and get on with my day.

I should really know better…

Microsoft has changed a lot in the last few years, but one truth that remains eternal is “always check for patches before you start to use a fresh install.”

Why? Well, let’s ask Bing who Kendra Little is….

To clarify,  I am not married, I have dogs instead of children, and I prefer romances about strong women who bring SQL Servers to their knees.

I also don’t think it’s possible to drink too much coffee.

Most of those pictures are of me, but a careful reader will note that the biography at the top references a domain name that, sadly, dear reader, I do not own. Someone out there named Kendra Little gets sweet royalties from her contemporary romance novels, but they don’t come to this address.

I helped Malathi Mahadevan write a different kind of book

‘Data Professionals at Work’ is a new book by Malathi Mahadevan. Mala went out and interviewed loads of data professionals, and talked with us about topics like:

• How to stand out as a data professional
• How to focus on the right things and evolve your career in a fast-changing world
• How to understand current trends and best practices

Mala interviewed an amazing group of people, including 
Mindy Curnutt, Julie Smith, Kenneth Fisher, Andy Leonard, Jes Borland, Kevin Feasel, Ginger Grant, Vicky Harp, Kendra Little (THIS Kendra, right here), Jason Brimhall, Tim Costello, Andy Mallon, Steph Locke, Jonathan Stewart, Joseph Sack, John Q. Martin, John Morehouse, Kathi Kellenberger, Argenis Fernandez, Kirsten Benzel, Tracy Boggiano, Dave Walden, Matt Gordon, Jimmy May, Drew Furgiuele, Marlon Ribunal, and Joseph Fleming.

Here is a twitter list with all those awesome folks: https://twitter.com/Kendra_Little/lists/data-pros-at-work/members

Here’s where you can buy the book

Data Professionals at work is way better than a bodice-ripper and is currently available for pre-order at Amazon.com in softcover.

You can also compare prices on the Apress site here.

I can’t promise that my chapter is steamy, but I hope that you enjoy it.

I’ve submitted a session proposal to the free, online GroupBy conference. Voting will open for sessions soon, but before then, you can help me out by reading my abstract. Is it a session you’d like to see?

Got any constructive criticism that you think would make the session or abstract better? Let me know in the comments over on the abstract for “What ‘Digital Transformation’ Means, and How You Can Use It to Advance Your Career (Without Being a Robot)“

I’ve got FOUR sessions coming up in October.  These sessions will each be 30 minutes.

Redgate Sessions

Wed, Oct 3, 9AM PT / 12PM ET: 
How to Lead DBAs to Embrace DevOps, Rather than Fear Change

Tues Oct 30, 9AM PT / 12PM ET:
The Single Question to Predict Success for DevOps, and What It Means for Change Control

Dear SQL DBA Sessions

Tues Oct 2, 9AM PT / 12PM ET:
DevOps Terms and Why they Matter for Database Specialists

Tues Oct 23, 9AM PT/ 12PM ET:
Book Review, “Accelerate: The Science of Lean Software and DevOps”

It’s TSQL Tuesday today! If you’re not familiar, TSQL Tuesday is a monthly blogging event where a Microsoft Data Platform community member chooses a writing prompt. This is a great thing if you’re interested in starting a technical blog and wonder, “What would I write about?” It’s also great if, like me, you occasionally want a little outside inspiration to shake things up.

This month’s topic is from Steve Jones: we’re invited to write about an experience that we’ve had with triggers.

My first encounter with triggers was overwhelmingly positive

When I first edged my way into a Junior DBA-ish role, I worked with a complex application with many large databases. Customers loaded and configured data into a (mostly) OLTP database, and then data was replicated to multiple other systems — in part to publish data to an adserving platform, in part to transform the data for reporting.

Triggers were used extensively in these downstream systems to detect changes. It went like this:

• Transactional replication publications were set up on the OLTP  (ish) database.
• Transactional replication subscriptions were set up on each downstream server. A dedicated database was used for replication articles on each instance.
• After replication was initialized, DML triggers were created on each article in the subscriber database. For each modification, the trigger would insert a narrow row into a “delta” table related to that article.
• The “delta” tables were in their own schema, and contained row identifiers, timestamp columns, and custom indexes for processing. This enabled batches to be efficiently pulled from these tables for processing into the related system.
• Cleanup processes periodically pulled processed rows out of the delta tables in the background (and indexes were designed to prevent the cleanup process from fighting with data processing jobs and inserts)

This design worked very well, and was a very positive introduction to me for triggers– they worked exactly as they’d been designed, and were easy to understand and support. Making schema changes to the tables involved was rarely a problem, and we had established processes for modifying articles and re-initializing replication which worked very well.

Why didn’t you use Change Tracking?

The primary reason that triggers were chosen over Change Tracking was that Change Tracking didn’t exist — these tools were written and tuned well before that feature was released. 

Looking back, even if we had Change Tracking as an option, we benefited from the granular control we had over this custom system and the elegant simplicity of the implementation. The Change Tracking feature in SQL Server has remained somewhat of an ‘edge feature’, and people using it have had to deal with a variety of issues over time – a need to customize the indexes on the change tables, statistics estimate problems with change functions, and problems with cleanup. (I wrote about these a while back over here.)

I don’t mean to say that Change Tracking is bad, just that it’s not necessarily as ‘easy’ a solution as you might assume from reading the documentation. You can’t simply set it up super fast and skip load testing, or assume that cleanup is going to work like a charm for every scenario — you need to really think about scaling it… kinda like you’d have to think about scaling it if you were writing your own custom application to handle this type of processing with … TRIGGERS! There are pros and cons to each approach.

And even today I wonder… what if we’d been able to get batch mode processing on those delta tables with a little careful custom coding? Hmm…..

I still kinda love triggers

A first impression goes along way, you know?

Sure, people can do terrible things with triggers. But when they’re used well, they can be simple, reliable, and incredibly useful.

In this 25 minute keynote, I discuss the challenges and opportunities facing you as a data professional in this ever-changing landscape. You’ll also hear recommendations about which skills are needed to help you succeed in your role and how you can in turn support your IT team and business’ needs.

Are you interested in in leadership topics in Database DevOps, or do you know someone who is? Get admission to Redgate’s SQL in the City Summits in NYC (Oct 12) or Chicago (Oct 26) with code KendraLittle.  Get 50% off admission to London (Oct 18) with code KendraL.

SQL in the City Streamed is coming up this week! This is an awesome FREE online event, and it’s all about learning new things and getting inspired.

Join us live, chat, and ask questions

This event is streamed on YouTube. While the session is going, you can rewind up to three hours or so. But it’s the most fun to watch live whenever you can, and ask questions. 

I’ll be presenting at the beginning and end of the event. When I’m not on screen, I’ll be tweeting with the #sqlinthecity tag. I’ll also be chatting in Slack about the current presentation, collecting your questions, and giving a little bit of the inside scoop on the event.

I’d love to have you join me.

Not set up in Slack yet? No problem

You can join the  SQL Community Slack for free anytime. Join here: just request an invite and get set up. Once you’re in, here’s a link to the #redgate channel where we’ll be chatting.

Calendar reminders for each session

SQL in the City Streamed – September 5, 2018
Register here for the whole day. The invites below do not register you, they simply put reminders on your calendar for the scheduled start times. Do check in on the day to see if we’re running on schedule as well – because life happens

• 7AM ET / 12PM GMT – Introduction and Keynote: Adapt and Thrive – Annabel Bradford & Kendra Little
• 7:35AM ET / 12:35PM GMT – Yes, You Should Monitor Azure SQL Database – Grant Fritchey
• 8:25AM ET / 1:25PM GMT – Wise Up and Get on the Redgate Hub – Chris Kerswell and Chris Unwin
• 8:45AM ET / 1:45PM GMT – Azure Machine Learning 101 – Kathi Kellenberger
• 9:30AM ET / 2:30PM GMT – Strategies for Solving Compliance Challenges for the Technology Industry – Steve Jones and Chris Unwin
• 10AM ET / 3PM GMT – Automating Database Deployments with the Microsoft Stack –  Rob Richardson
• 11:05AM ET / 4:05PM GMT – Panel Discussion Q&A: Skillsets for a Successful Career – Steve Jones, Grant Fritchey, Kathi Kellenberger, Rob Richardson
• 11:50AM ET / 4:50PM GMT – The Latest from Redgate’s Product Teams – Redgate Product Teams
• 12:10PM ET / 5:10PM GMT – Keynote Redux: Adapt and Thrive – Kendra Little (a live repeat of the keynote for those joining from later time zones)
  

Listen on the go

Subscribe on iTunes, listen on Google Play, or plug this URL into your favorite podcasting app: http://dearsqldba.libsyn.com/rss#

Video

Slides from the talk

Online (free!)

• SQL in the City Streamed – Sept 5, 7AM-12:40PM ET – free – Register
  

In Person

• SQL in the City – New York – Oct 12 – contact your Redgate rep – Register
• SQL in the City – London – Oct 18 – GBP £150 until Aug 31, then £200 – Register
• SQL in the City – Chicago – Oct 26 – contact your Redgate rep – Register

What will you learn?

There’s a pretty sweet schedule for each event, featuring folks like Grant Fritchey, Steve Jones, Kathi Kellenberger, and Bob Ward. 

I’ll be giving a keynote at the streamed edition of SQL in the City called, ‘Adapt and Thrive’:

Do you know where your career is going? Do you know what skills you need to be successful in the current climate? This keynote will discuss the challenges, as well as the opportunities facing you as a data professional in this ever-changing landscape. You’ll also hear recommendations about which skills are needed to help you succeed in your role and how you can in turn support your IT team and business’ needs.

There’s tons of technical content on the streamed event as well, including sessions on Azure SQL Database, Machine Learning, Deployment Automation, and more.

The live events have even more content – check out the sample schedule for London here.

Tweet along with the live stream

I plan to tweet along from the live event — join in the conversation on twitter as you watch with #sqlinthecity.