Voice of the DBA

Tag: administration

  • 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.

  • Lowering the Noise

    Over my career, I’ve had the chance to work in a variety of environments of all sizes. I’ve managed systems that powered all sorts of applications, with a variety of requirements. In many cases, I’ve had certain databases that needed constant monitoring and care from me to perform as needed. I’ve also had other systems that the business used, but weren’t necessarily critical. Those systems often had a lower priority for my attention if multiple problems occurred at the same time.

    I’ve been meeting regularly with the SQL Monitor team as they try to enhance and tune the next version of their product. As the product has grown and evolved, and SQL Server advances, there are different types of counters and metrics that need to be tracked. One of the major goals is to ensure that they reduce the number of alerts for DBAs and sysadmins that don’t require immediate attention or may be unnecessarily adding to someone’s workload, at least for new installations. You might customize your system to include many alerts. To do this, we have to make decisions for the default alerts and threshholds, which can be a challenge.

    That’s been a goal of mine as an administrator as well. I don’t want to get notifications or alerts of activities that are expected, such as backups. However, if backups fail, I may need to be alerted. Actually, I’m sure I need an alert, but it’s a question of whether I need to know now, at 2am, or get an alert the next morning at 9am. Often I may choose to respond differently to the QA server than I do for the production Sales instance. One might get immediate attention 24×7 while the other is a best available effort, and certainly isn’t likely to get a response on Saturday night.

    While SQL Server becomes better at adapting to changing conditions, there are definitely times when a human must get involved and decide how to solve an issue. That’s the reason that many of us are employed by others. We manage tasks and make decisions that machines can’t do for us. However, our attention and time is limited. Despite what some managers think, there are only so many hours in the day and week, and we need to make decisions about which items to focus on and handle. Perhaps even more important, unnecessary distractions can weaken our focus and cause us to make mistakes that we wouldn’t ordinarily make.

    As we manage more systems, many of which are important to our employers, a good system administrator will tune monitoring systems and ensure that they aren’t receiving or responding to unnecessary alerts. They work to minimize the interruptions and distractions from lower priority items. Maybe most importantly for long term job health, they ensure that systems don’t cause interruptions during their personal time, especially vacations. Good administrators put another human in the loop when they need a break.

    Of course, we also do ensure that low priority systems receive some attention. We can’t ignore that low disk space warning on the development instance forever.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Are You Always Up?

    I’ve had the opportunity to work for and with quite a few companies in my career. In some sense working as a database developer or database administratrator is very similar at many places. The “job’s the job,” as I’ve said in more than a few interviews, as I often ask to meet with others and better understand the environment. I’m rarely sold on the technology in use, usually more concerned with management and co-workers, though you might view employment differently.

    At the same time, it can be amazing how different the environment may be and how varied the requirements are for my application and systems. One employer was a small, but busy and growing import/export company. We stretched the limits of technology at the time, and it was a challenge to meet the desires of management. At the same time, we were primarily a weekday, roughly 9-5 shop and I had the opportunity to bring down systems at night or on weekends for maintenance.

    This was in contrast to my prior position at a nuclear power plant. There systems were expected to be up 24×7, with (grudgingly) scheduled maintenance once a quarter. That was the environment with the most stringent requirements, though I also learned over time that I preferred to have a bit more latitude in how I could architect systems or manage databases. Though that was when I had children, and I might be willing to work in a similar environment now.

    Many of you probably have varying requirements by application, but I suspect more of you are expected to keep systems up more often than in the past. It seems the world has moved to a greater dependency on database platforms, with the expectation that the data is always available.

    When you plan upgrades or maintenance, can you work with a flexible schedule? Or are changes always carefully managed? I’m sure some of you prefer one type of environment over the other, so chime in today with your preferences and restrictions. I’m sure some of you work in less busy organizations, and I’m curious if that’s the case.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Disable or Drop

    When I started working with SQL Server and Windows, it seemed that the administrative side of many actions was limited. We could add and drop many items, but that was it. Relatively few tools allowed setting limits or disabling them, which was a pain. After all, I’d gotten used to setting up accounts for vendors and contractors in Active Directory, often disabling them when they weren’t in use. I couldn’t do this in SQL Server for many objects, which was a pain and an administrative burden to reset them up when troubleshooting issues. This was pre-PowerShell and .NET when any SMO access was a project in and of itself.

    These days SQL Server has done a great job of adding in the ability to pause or disable many objects. We’ve had the ability to lock out an account for many versions, which is a great way to setup a vendor tech support account when it’s needed. This is especially important for security these days, as we may want to be sure that we prevent access by any suspect accounts. Disabling them allows us to prevent their use, but keep all their rights and permissions in the even they are valid accounts.

    We can disable indexes, which can be useful as a precursor to deleting them at some point. We can disable triggers, which is incredibly useful when you are testing or debugging actions on a table. We can disable Extended Events, audits, and more. All of these are useful actions for a developer or administrator, if you use them.

    When things go wrong, we’re often stressed and pressed for time. If there are issues with a system, many of us make snap decisions, which might fix the problem or make the situation worse. Even in those cases where we fix an issue, deleting or dropping objects might cause is extra work later. My question for you is what’s your first reaction? When you need to make a quick change to remove something, security access, an index, etc., do you drop or disable?

    I would hope that you disable, as this removes the effects but keeps the object in the system with associated meta data. Rebuilding permissions or trying to get the old trigger code is a pain (since few people use a VCS, please start doing this). It’s possible that you won’t even be able to get things reset back up in the same manner. That might be fine, but it’s not ideal as new code should be tested, and in a crisis, it likely won’t be looked at too closely. At least the previous version of the code was tested in production.

    Build the habit to disable, not drop, and I think you’ll be glad you did.

    Steve Jones

    The Voice of the DBA Podcast

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