Voice of the DBA

Category: Editorial

  • Hire the Right People

    I’ve seen this list of why projects fail going around the Internet for awhile (recently from Brent Ozar Unlimited).  There are a number of items in here, and if you substitute some other field for the data scientist part, you might have seen some of these issues as well. One of the items that really struck me was number 5, which is titled: You shouldn’t have hired scientists. The other part of the slide is that ETL needs data engineers (whatever those are) and reporting needs BI analysts.

    I with that we had good ideas and definitions of what different jobs are supposed to do in a company, and what skills are helpful. The more I look at job descriptions and responsibilities, the more I find that DBA, analyst, data xxx-anything are just amorphous concepts that vary dramatically from organization to organization. In fact, since the title that one has often determines pay scales, I think that we start to covet titles and work towards getting a title, regardless of the work we do. This isn’t helpful for anyone, least of all HR departments that try to match salaries to value and pay for particular jobs.

    There isn’t a great solution here. Our industry is young and changing more rapidly than any other in history. New jobs are created out of thin air, to meet the changing face of software development and system administration. Build master, data scientist, blockchain engineer, and a few others didn’t exist when I started working in technology. Whether those jobs are needed in an organization might not relate to whether or not anyone has those titles, or if they perform the tasks we might expect of those positions.

    I think that many of us are expected to be able to perform any task tangentially related to our position. Or we should be able to learn it quickly. DBAs should understand replication as easily as they might Availability Groups and configuring Extended Event sessions. A developer should be able to write C# as well as T-SQL. Both should be able to use SSIS to import and export data. While I’m sure many of us could do those tasks at a rudimentary level, are we really competent and capable at each of those tasks? Maybe enough for our organizations, maybe not.

    When we embark on projects, there may be needs to accomplish tasks outside of the core competency of our staff. That’s fine, and since many people in technology are willing (and excited) to learn new skills, this situation may be perfectly acceptable for our project. When there are core skills critical to the project, such as a deep understanding of large scale ETL processes, we are probably better off hiring people that have those skills or investing heavily in just-in-time training for existing staff. Hoping that the average DBA or C# developer can just “pick up some tips” is a recipe for project failure.

    Steve Jones

  • Monitor All the Changes

    Can you monitor every configuration change and setting on all your SQL Server instances? Can you get an alert every time code changes, or even if an option for an object changes, such as the changing of an EXECUTE AS or the rebuild options for an index? Do you want to know about every security change (new logins, grants, revokes, etc.). Can you keep up with every alteration of a SQL Agent job?

    You certainly can, but across any busy enterprise, all these alerts might result in a constant stream of items to review each day. In fact, in some companies, the volume might be high enough that this becomes a full-time job for someone. I’m not sure many of us think that it’s worth an employee’s time to actually review every change.

    Even if you decided it’s worth reviewing every change, is it possible to do a good job actually doing so? Too many alerts usually result in an individual starting to treat all of them as though they are the same priority. It becomes hard to differentiate what’s important to review and what’s not over time if you are always looking at a stream of changes across disparate systems. This is one reason why I never want to get success message, but even failure or change messages can be problematic when the volume is high.

    What can you do in a situation like this? Certainly there are alerts that are critical and need to be addressed right away, aren’t there? There are, but they are probably few. Making the decision about which items are important enough to review daily can be hard. I’ve typically only wanted critical alerts for backup tasks (after some retry) and privileged security alterations (add/change/remove sysadmin/securityadmin/processadmin/serveradmin). Those are items I need to take action on. Most other items, such as failed index rebuilds, job schedule changes, configuration alterations, I just want to capture and log.

    In one of the SQLskills newsletters recently, Paul wrote about an issue where replication settings had changed. The distributor had gone from 72 hours to 72 days, resulting in memory pressure for the workload. While I know this is a problem, is this the type of alert you’d define as critical? I’m not sure I would. In fact, this is the type of alert I’d want logged as a set of changes on this system so that I could review it when it seems that the system is not running as smoothly as it otherwise might be. In fact, this is the type of problem I’d hope I’d catch through performance monitoring, which might pro-actively catch performance degradation that would lead a DBA to review changes and metrics before a user reported the situation.

    Ultimately I want monitoring systems to help me find issues, and only notify me when I might need to take some action. To do this, the system might need to capture everything, but I want most of the items filtered until such time as the information might actually help me solve an issue. This takes some time to setup and tune as you discover holes in your monitoring, or you find that too much data is being passed on. Don’t be more afraid to add more data to capture, but be ruthless about removing extraneous notifications from the system. This is a case when too much information sent to the administrator is as bad as too little.

    Steve Jones

  • Wow. Just Wow

    Wow. Just Wow.

    Yes, I meant to use a capital letter there. The Red Cross’ Blood Service in Australia had database backups on a website that anyone could access. That means that anyone could download the backup, which contained PII (Personally Identifiable Information) about donors. This was a mysql dump file, which is shockingly easy to restore, so anyone could have read the file inside of an hour.

    To be fair here, this wasn’t the Red Cross’ fault. A partner had put the file on a website, perhaps for developers or an analyst to download, but the web server had directory browsing available and was serving data on a public IP. That’s three major faults, and perhaps a fourth since the backup file wasn’t protected at all.

    How many of us have innocently taken a backup, put it in a location for someone else to download inside our company, and not thought anything of the action? If you have done this, would you know if the file were accessed and downloaded by the wrong person? In most cases we wouldn’t because the correct person might download the file as well and we wouldn’t think anything had ever gone wrong.

    That’s a problem with data. We don’t get necessarily notified if someone makes a copy. We certainly have no way of knowing if the backup files we create are ever restored by an unauthorized user, much less just copied. We often just assume that data at rest is just at rest and isn’t ever mishandled without our knowing about it.

    This is a good reason why we should be practicing security by default in most every case. All our backups should be protected at the very least with passwords. Even if everyone in the organization knows the password, some random person that might steal a laptop or browse an unsecured web server won’t know it. The password also shouldn’t be easily guessed, and if possible, the data should be encrypted. Always.

    What’s more, I would hope that we would use secure ways of moving information around with our fellow employees and partners. I know Dropbox is a very convenient and tempting mechanism to use, but is it really appropriate? It might be good enough if you’ve protected the data in other ways, but I’d be very, very wary of using any public service that doesn’t allow for some sort of security that can be tied to individual, authenticated users.

    No matter how you choose to protect and transfer database backups, I’d also be sure that you don’t leave the information there indefinitely. Have expiration times where files are removed. All encryption and protection can be cracked, given enough time and resources, so don’t leave your files there forever.

    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.

  • A Joke Come True

    Yesterday was the opening of Microsoft Connect 2016. If you didn’t have the chance to watch the opening keynote, I’d recommend you do so. Mostly because one of my jokes, or perhaps a dream, has come true. SQL Server runs on Linux, and you now run it yourself. I know Microsoft has talked about this for most of 2016, and they have used it in demos, but there haven’t been public bits available.

    I’ve been lucky. In fact, I’ve been running SQL Server on Ubuntu since April of this year as part of a private program. I’ve been testing various Redgate Software tools as well as my demo code from presentations and so far everything has run. This includes the tSQLt framework and my tests that make use of SQLCLR. This includes my AlwaysEncrypted demos. I was very impressed that these features just worked, as though this instance were any other SQL Server that I had installed.

    What’s more, the installation and updating of SQL Server on Ubuntu, using apt-get, is far, far smoother than the installation on Windows. To be fair, this is a default installation, and I haven’t tried to set up all the various options and settings that are available on Windows. The various additional subsystems (SSIS, SSAS, SSRS, etc) aren’t available as well, but still, it’s a very smooth process. As I’ve updated various release candidates across the last few months, I run two commands: “apt-get update”, and “apt-get install mssql-server”.

    I don’t know if this is a good business decision for Microsoft. Time will tell, but I can’t help but think that the addition of another platform on which SQL Server can run is good for the product. More people will consider SQL Server as their database platform, with all of the powerful features and capabilities that brings to a database driven application. I suspect this will mean that many developers working in non-Microsoft environments with Linux, Java, PHP, and more will begin to consider SQL Server as an alternative to PostgreSQL and MySQL, in addition to Oracle and DB2. Certainly there is still a cost to using SQL Server, but it’s an incredibly powerful platform, one that now has a more consistent programming surface since almost all features are now available in Standard as well as Enterprise with SQL Server 2016 SP1. Getting RLS, Columnstore Indexes, In-Memory OLTP tables and more in all additions is a major win, and another of the pet peeves I’ve wanted changed for years.

    I am very interested to see how people view these changes, and if they will impact you? Do you want to run SQL Server on Linux? Since In-Memory OLTP and other features are now on Standard, are you interested in upgrading to SQL Server 2016 now? Perhaps you’re a little more excited about Microsoft and SQL Server with all of the new development changes announced yesterday? Let me know today.

    Steve Jones