Voice of the DBA

Category: Editorial

  • Keep It All

    I love this quote, though I’m not sure it’s accurate. From The Future of Data Storage, the piece states: “What’s the most expensive thing you can do with a piece of data? Throw it out.”

    That’s from a storage vendor, and obviously they’d prefer that you keep all your data, which means more storage and backup space needed. Certainly I do think that losing data that’s valuable can be expensive, but I also think that we often keep around older data that we don’t use, or won’t use, which is expensive. Not for individual pieces, but in aggregate, it becomes expensive. This is especially true if you move to the service area where you pay for what you use, as opposed to investing in a large chunk of storage that has a fixed cost.

    I didn’t really think a lot of the piece, though it did get me thinking about backups. I’ve run backups for my entire career, and in 99 point some number of nines cases, I haven’t ever used the backup file again. These were insurance against potential problems. Even in places where I restored the backup to verify the process worked, I often just discarded the backup file at some point.

    Early in my career, we had tape rotation systems to reuse the media a certain number of times, while also ensuring that we had off site copies and specific points in time saved. Today there are plenty of backups systems that perform deduplication and complex disassembly or re-assembly of files from blocks to use space more efficiently. That doesn’t always work well for database restores, especially when time is of the essence.

    As vendors look to add more intelligent, or at least more efficient, processing to backup systems, I wonder if they really think about databases and how we use files. I hope so, and I’d like something that was optimized for database restores. I don’t mind combining the duplicate parts of files into some index, but I need to have the latest files available for quick restores. What about backing up a database to a file and keeping this file online and ready. Then, after the next backup, move the previous one to an area that dedups it, maybe takes it offline, etc. That way I have the best of both worlds. I rarely go back further than the latest full backup for a restore, so keep this ready.

    Of course, we need to consider log backups, which really need to be kept online and intact if they have been made since the last full backup. Keeping track of that is a pain, but it’s something software could easily handle. Once we’ve made a new full backup, you can mark older log backups for deduplication. Though, if you’re building this into a system, perhaps performing a restore of the full backup files automatically should be included as well.

    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.

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