Voice of the DBA

Category: Editorial

  • The Right Connection

    Travis-CI had some staffers connect to the wrong database and truncate production tables. Needless to say this caused an outage and disrupted their business. Hopefully they didn’t lose too many customers, but they certainly did not help their reputation. I’m sure there are more than a few customers trying to decide if they continue to trust the company or move their Continuous Integration (CI) processes to another platform.

    I’ve done this before. Not shut down a company, but I have actually truncated a production table by mistake. Well, not TRUNCATE, I mean, who runs that. But I have run a DELETE without a WHERE clause and killed a lookup table in a production database. Fortunately I had a copy of the table elsewhere and could rebuild it in minutes. Only a few customers had their work interrupted and only for a portion of our system. The point is that I’ve been a very good DBA, with a lot of success and experience, and I still make mistakes.

    Often this type of mistakes comes about because we get busy, and we keep connections open to different systems. When we might be developing code against a schema that is close to production, it’s easy to forget which database we’re working on. Someone calls with a problem or we fight a fire, and we run some code. We fix the issue, stress bleeds away and we go back to work, but forget to switch connections or tabs. Then we run some code that would be fine in development, but causes issues in production.

    SSMS has colors for a connection. SQL Prompt has tab coloring by system and database, as do some other products., which can help, but it isn’t perfect. One thing I’ve found with colors is that if I use them constantly, my mind starts to filter out the color. I don’t always realize the outline of the tab is a different color. This is especially true if I have the need to switch back and forth between both production and non-production systems. I’ve tried running two instances of SSMS, which helps, but at times I’ll forget which one I’m working with and make a connection to a production server from a non-production instance of SSMS.

    Ultimately, we need to be careful. I know one friend that has no access to production and must hop through an RDP session and connect to a production database. However, if you run your RDP session in full screen, how often would you forget that you’re in the SSMS on the hop system and not in SSMS on your local machine.

    I don’t know if there’s a good solution. Many of the convenience features that make life easier, like reconnecting tabs when I restart SSMS are great, however, they can compromise security and safety. I don’t know if there is a good solution, but I’d certainly like more checks against ad hoc issues occurring in production systems. Maybe some sort of lock against certain instances that prevents destructive execution on certain instances or databases without some confirmation. I love SQL Prompt preventing me from running code without WHERE clauses, but that isn’t always enough. At least not for me.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Good Security Needs Layers

    How many of you have wanted to know who started or stopped a SQL instance? Probably a few of you, as disruption to the service can affect customers. Most of us are concerned with the changes made inside SQL Server to objects and data, and that’s what the auditing features inside SQL Server are watching. The problem is that the database platform is dependent on the host OS, and as such, some actions take place at that level. Auditing inside SQL Server isn’t setup to capture this information.

    Should you care? Well, restarts, or the stopping or a service are one way that a malicious actor could alter files, change the error log without you realizing it, or even copy files to other systems. All these actions might be outside of any auditing or event tracing you’ve set up. Good security needs multiple layers because the system you need to protect is often dependent on some other part of our infrastructure.

    Databases depend on the host OS and perhaps directory services. Your OS may depend on a hypervisor, and certainly needs patching, so it depends on human administrators. Many of our systems depend on networking and firewall configurations. There are other layers, but the more that we can ensure each layer is secure, the better off we are. Certainly our systems always depend on humans not giving away credentials or installing malware, but that is often something many of us can’t control.

    I ran across an article that explains how to use auditing at the Windows level to track this down, and ensure that there aren’t more unexplained restarts. You can implement this, but if you don’t have Windows administrative privileges, you’ll need to get help from someone that does. Likely a couple of you have been glad that there isn’t a great way to audit this from the OS, as you were the one performing a restart without permission. If that’s your MO, I expect you might not want to pass this piece along to your security staff or auditors. If that’s the way you work, though, I would advise you to change your habits.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Machine Learning Challenges

    If you know someone well, you may think that you can predict what they’ll do in a situation. Those that know me might think that if I go to New York, I’ll make time to run in Central Park. That’s a good bet, and the last 7 or 8 times I’ve gone there to speak, that’s what I’ve done. However, that’s a very small data set, and you might not want to attach a high level of confidence to that prediction. There are certainly times that I’ve gone to New York and not run in Central Park.

    Machine Learning (ML) is a field that tries to make predictions about some data, based on previous known actions or results. By inferring relationships between lots of data and known actions, the ML model can guess what might happen with future data inputs. Lots of companies and fields are experimenting with ML techniques, with some of them working quite well. Others aren’t faring as well, and there is a lot of research underway to better understand the entire field. One possible explanation is emergence, which is discussed in this piece.

    If it’s raining, I likely won’t run. If it’s 20F, but dry, I will. Those that know me would inherently consider the type of weather as a new factor that has a high level of predictability for me. Time in the city, location, whether my family is with me, these are all other factors that would come into play. With enough data, and enough factors, you could likely come up with a model that predicted the likelihood of me running in Central Park when I visit New York City. It might not be perfect, but it would be good if you had enough data.

    However, that’s a very simple item, and few decision points. Would that work for a larger data set? I bet it would. If we were able to collect data from lots of 5k events, we could perhaps predict how many people would register show up. There are certainly more factors at play, and since we can’t control things like weather, the prediction might not be that valuable. Though if we could change the amount of resources the day of the event, maybe that would help reduce costs or make the event more successful.

    I think ML is a valuable tool that can help with quite a few problems, but in many cases, we just don’t know enough about the complex factors, such as emergence, that might impact us. I know our SQL Saturday organizers would love a model that helped them decide how much food or drinks to order for an event. My view is that there are likely too many unusual factors, but I wonder if we could start a data gathering project to come up with a model. Perhaps we should gather marketing metrics, registration metrics, and more, with the intention to predict the number of no-shows. Even if it wasn’t useful, it might be a fun project for people in this industry.

    Steve Jones

    The Voice of the DBA Podcast

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

  • In Depth Learning at the PASS Summit

    This past week the pre-conference sessions for the 2018 PASS Summit were released. There’s a good list of topics and speakers, returning and new presenters, that you can choose from. If you’re planning on attending the Summit, I would review the options and if any of these are relevant to your position, I’d bundle the cost of a session into your request for Summit funding. I’ve seen plenty of people ask for the registration fee without a pre-con and then try to negotiate additional funds later. Do yourself a favor and ask for everything at once.

    Are these sessions worth the time and money? Most are, and a single day packed with information is often easier to digest than a full week. However, you’ll still have days of random learning slots during the regular conference, so plan on spending a day learning and then shifting into a different mode for the rest of the conference. There is a lot of information presented in an all day session, and you’re likely to miss some of it. Most presenters provide a good set of materials to go with the class, but you should plan on taking lots of notes and then spending a few hours here and there across the next month reviewing and practicing what you’ve learned.

    I’ve often found that a pre-conference session is the best way for me to dive deep into a topic that I haven’t had the time to investigate on my own. Time is a precious resource and finding time in my busy work and family life is hard, especially for a full week class. I’d still like to get to a week long sqlSkills class, but I can’t seem to find the time. Maybe after next year when I have no more children at home and fewer commitments.

    I used to take a computer to classes, but the last few years I’ve moved back to paper and pen. I find that sketching out notes by hand is quicker and easier, I ignore typos, and somehow the act of writing cements things in my mind more. I’d recommend you try both, though certainly having your phone around to take a picture of something is incredibly handy at times. I’ve tried OneNote and a digital pen on a laptop, but again, it’s not as quick or smooth as pen on paper. The one thing I did like is that I could add pictures to my notes.

    A conference is a large investment, whether you make it or your employer does. Adding in a day of training on a topic is often a relatively small incremental increase. It’s worth the time and cost, if you’re already going to be gone for multiple days. I’m not sure if I’ll get to a pre-con at the Summit this year, but I’m hoping to get a day of watching and learning on a single topic in at one of the events I attend this year.

    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.