Voice of the DBA

Tag: administration

  • Ad Hoc Logs

    A long time ago I started working in a team of people as a general systems administrator. I worked in a team of six people managing a large, 1000+ node network with a number of servers. In my first exposure to SQL Server, we had a very unstable application that forced us to provide 24×7 support. With a couple of contractors, we had to ensure someone was on-site all the time, often working with Microsoft and our corporate developers to stabilize the applications. We were constantly trying new techniques to fix our application, and with staff stretched thin, we struggled to understand what might have happened in the previous 12 hours when we reported for work. At the time, I suggested leaving a text document on each server’s desktop, updated with a note for each change.

    That worked well and I brought that technique with me to future positions. In another job, we constantly remotely connected to systems, and having a standard file on the desktop was helpful. As we became more security conscious, and stopped using shared logins, we moved our logging to Exchange public folders. Every action taken by an admin needed to be cut and pasted into a new post. That wasn’t a perfect system, but we built habits over time and we had an audit trail that helped us in understanding the changes we’ve made and assisted in troubleshooting.

    Today I’m curious. I want to ask the question about the data corrections, those quick changes, those fixes that get production working. Do you log everything? Is there some system in place to ensure you know what’s happening?

    I’ve been wondering about this and thinking hard as the date for GDPR enforcement approaches. One of the items that I’d glean from the law’s text is that any change to correct data, any quick fix made, needs to have an audit trail. We need to prove that we know who, when, and why this change occurred. This is especially important if a data subject requested some correction. You’ll need to prove you actually performed the action.

    I’ve never worked anywhere that some admin (including myself) completely avoided connecting to a production machine and making some change. Sometimes we’ve had great auditing, often not, but ad hoc fixes and changes, especially in the heat of an issue, are a fact of life. I’ve learned to deal with it and try to build lightweight habits to help me capture those changes.

    Let me know today. How bulletproof is your auditing?

    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.

  • More SQL Server Updates

    This was a busy week for updates. I finished adding new builds to the build lists for 2017, 2016, and 2014, the first time I’ve done all three platforms on the same day. To be fair, two updates were on the 19th and one on the 20th, but that’s pretty close to the same day. In the past I’ve had updates for different versions during different months.

    That will not be the case in the future. SQL Server 2017 is on the CU model where we get monthly updates to the product. No more service packs for this version. If you hadn’t heard that, be ready for the new servicing model that consists of just the monthly updates. I wasn’t sure if I liked this at first, but I do appreciate that we don’t get confused with trying to determine which CU1 is needed as there were different patches for RTM, SP1, SP2, etc. Now I’ve gotten used to expecting an update each month and applying them after a few days. So far, Microsoft has mostly been sticking to monthly releases, though the December release got delayed into January. With holidays, that’s expected.

    This week also saw the update of SSMS to v 17.6. You can freely use this version and download the full install or just the update package if you are running 17.x. We are seeing updates on a fairly regular basis to the tool, every couple of months. I’m hoping we continue to see more fixes and improvements aside from new functionality as SSMS has been neglected for too long. I don’t know how stable this version is, but 17.4 was on my machines for a long time without an ill effects, and I think this seems fine for now.

    Updates have always been a part of software, but often they are fairly rare, with interruptions occurring just a few times a year. That’s changing. Redgate releases software every week, with almost every product updating once or twice a month. For someone that uses relatively few pieces of software, all of a sudden it seems like I have a regular set of tools to update across my SQL Server development platform between SQL Server, SSMS, VS, Redgate tools and more.

    That’s good, as I get fixes and increased functionality, but across a set of tools, I might find myself updating something every day. While the software often updates itself if I click OK, I have to make a decision and the process is disruptive and time consuming, especially when I run into required updates, which happens more than I’d like. I like the idea of DevOps, but one of the unintended consequences is that if everyone does it, there are a lot of changes and updates for users to deal with.

    I don’t have a solution, but I do find myself delaying updates more and more, often moving to only take every 3 or 4 updates. Maybe if everyone had an easy way to apply patches with PoSh across systems, or smoother built-in mechanisms I’d feel differently, but for now I probably click “skip” more often than “apply”.

    Steve Jones

    The Voice of the DBA Podcast

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

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