Voice of the DBA

Tag: Database Weekly

  • Local Database Issues

    There was a report that Spotify is writing lots of junk data to users’ local drives. Some people have noted GB a day or writes, even when they aren’t using Spotify for listening to music. Some debugging by users show that a local database (SQLite) is getting a number of maintenance calls to the database that are being repeated over and over. This appears to be a bug, and one that is hopefully being fixed. I know it hasn’t hit me, so perhaps there are more factors in play than just using Spotify.

    This does bring to mind a few things to me that we should be careful of as software and data professionals. The first is that while bugs will creep into our software, being able to deliver fixes is important. I’ve seen updates made to Spotify in a smooth fashion, one that doesn’t seem to require much effort from me. While I don’t usually get to decide when these updates will apply, and they can be annoying if I reboot and attempt to use the application, this isn’t a critical piece of software for me. For some of us, though, our users can be impacted if we don’t allow them the chance to choose when updates occur, or at least, when the final time is when the old version might no longer be supported (or stop working).

    The bigger issue, to me, is that the local database is being used in a way that users don’t expect. There’s likely a bug here, causing lots of additional activity, but what’s a normal level of activity? What would our users expect? Do they even know that we’re keeping data locally on their system? In many cases they might not care, or even assume we are, but we should disclose this to our users. Security concerns over data leakage or loss may be an issue in some environments, and it’s important that we ensure our users are aware, or at least they can find, information about the data our software may collect, store, and use.

    Data becomes more important all the time, and we are constantly finding new issues surrounding its capture, storage, security, and use. The way we even think about data is somewhat immature. Data ownership, protection, lifetime, and more are concerns. The problem is that many of us would struggle to articulate how exactly we want to handle the data that is related to our lives. In fact, we may have vastly different ideas on how different types of data are handled. Perhaps we don’t care about our musical listening habits, but we care deeply about someone learning about our reading history.

    If you develop software that uses a database, and many of you do, it would be nice to document (maybe self document with code) the ways in which data is stored and managed. However, if you’re systems are like mine, there might not even be any policies on how data is managed and handled over time. That’s something I hope changes in my lifetime and we start to treat data like a very valuable asset that can impact our lives.

    Steve Jones

  • Intelligence from Data

    There is an incredible amount of data in the world, and all that data is changing the way industries work. That’s the opening to a keynote talk from Jim McHugh at the O’Reilly Artificial Intelligence conference. The talk is short, 12 minutes, and interesting to listen to as Mr. McHugh looks at autonomous cars and healthcare, talking about the impact of artificial intelligence on advancing these industries. There are examples showing how data and AI systems are already being used to change the way the transportation and medical fields can work.

    Whether you want to see more robot help in our world or not, I suspect some level of this is coming, and it’s being driven by data. We have more and more data, and as companies have success in analyzing this data with various types of AI and machine learning systems, there is pressure for other companies to join the trend and build their own systems. We certainly see that with the push from Microsoft that emphasizes the R Services in SQL Server. At the recent Data Science Summit, there was a demo in the keynote (around 17:00) of over 1 million classification queries per second running inside SQL Server. You can even try this yourself on SQL Server 2016 Developer Edition (for free).

    I’m sure that a few of you will start to get more complex analysis projects inside of your organization. Maybe you’ll help develop some sort of prototype, or maybe you’ll just be responsible for helping get the data to the data scientists. I’m also sure that some of you won’t be thrilled with the results. After all, throwing a bunch of data at a few algorithms and expecting some rapid development isn’t likely to work great.

    At least not the first time.

    One of the thing I’ve seen from many people as I study data science, machine learning, and related topics is that this isn’t a simple process. Building a useful and successful machine learning system requires experimentation, and really, ongoing experimentation, as you examine, clean, discard, and make decisions on your data. In fact, the data preparation might be the most difficult and time consuming part of the process. That’s great, since many of us are the people that will work with the data, but it’s bad in that our management might not want to have the patience to experiment, evaluate, and re-tune their systems, much less wait for data to be well prepared.

    I do have high hopes for many complex problems to be assisted with machine learning and artificial intelligence in the future. I’m glad that companies are experimenting, and I think it’s great that so many data professionals are getting excited by the possibilities. Remember that this field is hard, and requires lots of work. Keep learning and growing your skills, and above all, remember that the scoring against your data is more likely to be closer to a baseball game than a bowling match. A 30% success rate might be amazing and those perfect games are likely very close to impossible.

    Steve Jones

     

  • Analyzing Data in Real Time

    A few years ago I was listening to a presentation from Microsoft where they touted a customer that had real time analytics with Analysis Services. Since my exposure had been that cubes needed to be processed, a resource intensive process, I was wondering what this meant. Imagine my surprise to hear that the cube was being updated within 5 seconds of the changes in the OLTP system. Talk about real time.

    Of course, the definition of real time isn’t really well known. We linked a piece in this week’s newsletter that looks at a survey of companies and their investment in real time, streaming, data movement pipelines. The definitions from companies about real time range from sub-second to 24 hours. I’m sure I think that 24 hours is real time, but if you’re used to processing cubes weekly, maybe that feels real time. I tend to see real time on the order of minutes, maybe a few tens of minutes. Once we get to hours, that feels more like batch processing, albeit fairly rapid batch work.

    I am sure there are systems that need real time analysis, especially in some areas where decisions must be made quickly. As we move to more and more machine learning algorithms and automated intelligence, there will certainly be more call for real-time data movement. Yet another opportunity for data professionals, especially those that work with ETL pipelines. There will be tough problems, not only with moving data, but tracking lineage, recovering from issues, even weeding out bad data quickly.

    However, that’s an opportunity for you, not me. I bet there are a few data professionals out there that feel the way I do. There are great challenges in solving real time problems, and building systems that can handle high volumes of data. The thing is, I value my sleep. I value not working all-nighters, I value a balance in my life. While I find the problems fascinating and the money involved tempting, I prefer to work on more pedestrian systems.

    I’m glad there are people that want to work on very difficult problems, and I wish you all the best in taking advantage of these opportunities. I hope you’re well paid, and you have a great time building these impressive systems. I also hope to read about some of the amazing things you do, so please, share the knowledge where you can. This is a great, exciting time to work with data, and it’s one that I continue to enjoy every day.

    Steve Jones

     

  • Why We Need Backups First

    One of the things I show when I talk about TDE is that the backup of the certificate is very important. When you create a certificate, SQL Server warns you right away that a backup of the certificate is needed. Following Murphy’s Law, if you continue on with enabling TDE, right after the encryption is completed, your master database will become corrupt and you won’t be able to get to your data. It just takes a few seconds, so back up your certificates.
    I was reminded of this while reading a short piece on some police body camera footage being lost. I don’t want to focus on the police actions or cameras or anything but this statement: ” A backup system had been purchased but hadn’t been set up. “
    That’s a fairly egregious breach of duty for some IT personnel in this case. The system was installed in 2009, and this event apparently occurred during a software upgrade in 2012. While I understand a backup system might not get configured immediately, I think 3 years stretches the limit of an acceptable delay. However, even if the backup system were not in place at the time of the software upgrade, wouldn’t a backup before upgrade be required? Isn’t that a standard practice?
    There have been other reports of similar issues, which is disconcerting. It appears the use of technology for archival of data isn’t well developed, or properly understood by the IT staff in charge of the process. Of course, this happens in plenty of enterprises and other organizations where IT staff seem to think that backups are important in and of themselves. In case you’re confused, they are not. Restores are the important things to have, which means you must verify your backup process allows the data to be retrieved.
    Many of us have scripted methods that practice this regularly, refreshing other environments, restoring databases for DBCC, or some other task. Few of us actually practice this ourselves across all databases on a regular basis, ensuring our skills for tail log backups, NORECOVERY and STANDBY restores are fresh and ready for emergencies. However, we should not take this task lightly. It is important that we are able to recover data in almost all situations.
    If we can’t do that, then we are truly disregarding our duty as the custodians of data.
    Steve Jones