Voice of the DBA

Author: way0utwest

  • Template Configuration

    One of my goals this year is to really spend some time learning more about Extended Events (XE). I’m somewhat embarassed that almost a decade after their introduction in SQL Server 2008, I have a fairly rudimentary grasp of the system. I’ve watched talks from Jonathan Kehayias and Erin Stellato and others. I recognize the value in a lightweight system, but for some reason I haven’t done much with XE.

    As part of my learning, I’m going back to the basics with videos, articles, and documentation. In one section, I ran across the templates and how they can be used to greatly simplify the creation of new sessions. I’m a bit fan of templates, and I love the ones I use for T-SQL in SQL Prompt. I know templates are incredibly useful in plenty of other areas.

    XE is more complex system. The verbiage, the complexity of filtering, and the variety of targets make this a very flexible system, but also one that can overwhelm people. When do you use the histogram target v the ring buffer? What events make the most sense to solve or diagnose your system?

    Today I wonder if some of you out there can share some knowledge. Are there built in templates that you find useful for certain situations? Have you created your own that speed up your analysis of an issue? If so, it would be great if you could share some code and explain why you find certain session settings useful. If you find some templates to be problematic, perhaps share that.

    As I’m learning, I think that it’s likely I’ll have my own set of events and settings that I lean on heavily. Since I can save these on an instance, and not just a workstation, that’s a huge improvement over trace. These sessions can be shared with other DBAs in my organization, which is helpful and handy. Maybe one of you will give me a new template that I can add to my toolbox.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.7MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • Enabling Guest in a Database–#SQLNewBlogger

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    The guest account exists in all your databases. This is installed by default, and guest is used to map a login that doesn’t otherwise have access to a database.

    Sound scary?

    It should. This would be bad if any login could connect to any database, potentially reading data using the guest account. Fortunately Microsoft has done two things. First, guest is disabled in all user databases. This is because it’s disabled in model, which is our template.

    2018-02-08 08_51_24-SQLQuery8.sql - (local)_SQL2016.AdventureWorks2014 (PLATO_Steve (74))_ - Microso

    Second, guest is typically assigned no rights. It’s a member of the public role, which also has no rights by default.

    Enabling Guest

    If you want to allow anonymous access for logins through the guest account, it’s easy. Be wary and careful of doing this and be sure you understand what rights have been granted to public if you do this. In general, I’d expect auditors and any compliance/security officers to be against this, but you should check.

    The user exists already, and just needs the CONNECT permission to get enabled. You can do this with this code:

    GRANT CONNECT TO guest

    If you want to remove permission, use

    REVOKE CONNECT FROM guest

    That’s it. Remember, by default this user can’t access any objects. I would recommend you not grant rights to guest, but use roles. Either one of the built in ones, or better yet, create your own role and choose limited permissions.

    SQLNewBlogger

    One of the ways you can showcase your knowledge, show you’re learning, and show you’re motivated to enhance your career is blogging. This post is an example of what you could write, in your own words, about something you’ve learned.

    This one took my about 5 minutes after I’d spent a little time getting guest enabled for a test project.

  • Muddle Through

    I used to work in the restaurant business. I got started after my first year at college, landing a job waiting tables in a new hotel. It was eye opening for me to see just how stressful and difficult that job can be. However, I wanted to earn money and jumped at opportunities. When we needed room service waiters to pick up shifts and elevators didn’t work, I carried trays up staircases, taking advantage of the opportunity. When a bartender didn’t show up, I volunteered to take the lunch shift. I had no idea where things were in the bar, I was 19 and didn’t know how to make drinks, and I’d make less money, but it was an opportunity that I knew would pay off at college.

    It did, and at the next three jobs, I wound up me getting hired, starting work, and having either no one to train me that day or a very busy shift where I was mostly on my own to survive. I had to muddle through and learn on the fly. The ability to do that, without panicking  being overwhelmed, or giving up has served me in quite a few positions since then.

    When I started working for various companies as a developer or DBA, I found myself in similar situations. Problems would arise, often the day or week I started, and I’d have to solve them. Usually with DBA positions,  I was the only one there, so I couldn’t depend on anyone else. It was  a good thing as I often found that sysadmins or developers were not managing or configuring databases in an efficient way. As I gained experience, I could make more and more of a difference earlier on at each organization.

    Kevin Feasel wrote a bit about his experiences with Lucerne (near the bottom), muddling through the need to write queries. I’ve seen similar stories from other friends working with SSIS, SSRS, Redis, Azure, and more. They don’t know a lot, but they dig in and learn, making mistakes, but getting tasks done for their employer.

    The ability to work through adversity, have some confidence, learn quickly, and be effective are valuable skills. Those data professionals that can do so often find more opportunities, challenges, they grow their skills, and get more compensation. Those that find reasons to avoid learning, that lack confidence in their ability to find a way to solve a problem, or are unwilling to tackle challenges often stagnate a bit. I’d like to think there are more of the former than latter in this business, but I constantly seem to find people that just look to repeat the same work they’ve done over and over for a long time.

    It can be mentally difficult to start a project using technology with which you have little familiarity. It can be disconcerting to have someone ask you a question that you can’t answer because you’ve barely begun to learn. That ability to muddle through, to keep learning, accepting that you don’t have answers but can find them, knowing that some of your answers will be wrong and you’ll need to backtrack. That ability to muddle through will serve you well.

    I’d urge you to develop it.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 4.0MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • Join me for Masking, Privacy, Protection, and Clones at SQL in the City Feb 28

    Registration is open for SQL in the City 2018. Our first event is Feb 28 and I’ll be heading back to the UK for the event. This time Grant and Kathi will be there and we’ll be joined by the amazing Ike Ellis. This is an event to watch.

    I’ll be covering some GDPR stuff about compliance, with good information that all DBAs and system administrators should know. This is a prove you’re doing what you have decided to do session.

    In the afternoon I’ll also cover some enhancements to SQL Clone in our Privacy bundle that helps you mask data from production and build your dev/test environments in a way that can protect your sensitive data.

    Some Redgate products will be used to show how to accomplish the same tasks, but all of us will be talking about core concepts and ideas that you need to know. We’d like you to consider our software if it provides you with value for the cost, but either way, you will learn about things that we all think are important.

    Register today and I’ll see you in a few weeks.