Voice of the DBA

Tag: databases

  • Common Data Challenges

    Working with data has proven to be a challenge for most of my career. It’s been fun, and certainly fulfilling, but there are constant challenges involved. Let’s take away the hardware and admin challenges of keeping systems running, backed up, and performing well. I’m thinking today of the struggles of just data.

    There is a short article that talks about three common data issues, and these are some of the same ones I’ve struggled with for most of my career. Are these challenges any different than they were 20 or 30 years ago? I’m not sure, and I was working with databases and software nearly 30 years ago.

    ETL is a constant challenge, even today with tools like SSIS and Biml that make it much easier to build flows that migrate data from one database to another. ETL is such a challenge that many people make a very comfortable living helping organizations meet their every changing needs to move and prepare information for end users.

    The other challenges noted in the article are getting a complete data picture because of missing data and not trusting or believing in data. The latter hasn’t been as much of a problem for me.  I might describe it differently as more often we aren’t sure what weight to place on certain data. The world is messy, and often we collect data that we think might be valuable only to realize later that it doesn’t mean what we thought or our our hypothesis was incorrect in the first place.

    I think the challenges are part of what makes this work interesting. Our employers and clients might view the effort and time involved as frustrating, and I wish I had solutions to make our process quicker and smoother. Actually, I do think that advances like SSIS have made things quicker, but the world has grown more complex. We deal with more data from more systems, in the still chaotic, messy formats of the world.

    What are your challenges with data today? Are they getting better, worse, or still the same as they always were?

    Steve Jones

    The Voice of the DBA Podcast

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

  • The Age of Multiple Databases

    I can still remember learning to store data in files, reading one record at a time, testing for end of lines and end of files. It was slow and cumbersome, but for a teenager, it was amazing. We built applications that could help run Dungeon and Dragons games, and the ability to store information about our games was amazing. I even adapted some of these routines to store data about various chemistry experiments in school, making my report writing (and data fudging) must easier.

    In time I stumbled upon the basic database structure of dBase, thinking this was vastly superior. I could control the structure of each table, but have the power of quick access and indexes. Of course, I learned to make sure I opened all the indexes with the data file, otherwise I might “lose” some data over time.

    Those first database structures felt great, and even superior to SQL Server when it was just one person. Initial queries were much quicker under dBase type applications (Clipper, FoxPro, etc) than under SQL Server, though that quickly changed as the number of users grew. For the last two decades, I’ve been entranced and thrilled with the power of relational systems. They solve many problems well, and I find them fairly easy to develop against.

    Not everyone feels the same, and there have been many different types of data stores built in the last decade, each designed to solve different types of problems. I ran across a post about the different specialty databases available in AWS from Werner Vogels, Amazon’s CTO. The piece is interesting as he notes that one type of database can’t meet all the needs of modern applications, at least, it can’t meet the needs of very large scale systems like Amazon’s eCommerce site. Instead, there are situations and problems better solved by specialty systems.

    Amazon built a key-value, extremely fast database to back a system that mostly needed those types of simple lookups. Over the years, in response to their needs or those of their customers, they’ve added other types of databases in their AWS cloud. Mr. Vogels lists six databases in AWS, including relational stores. He talks a bit about where each can be used. He also cites some customers that have had success in different applications with different data stores.

    Microsoft has done similar things in Azure and their data platform. Most of us know SQL Server as the relational engine, but we have an in-memory component and with 2017, we also get graph capabilities, albeit rudimentary ones. In Azure, they have CosmosDB, which include a number of different types of data stores (key-value, document, graph, etc.). I know Google has multiple databases available as well.

    As we build larger systems that reach out to more people, I wonder sometimes if we ought to be architecting to use multiple data stores from the beginning, choosing the type that works best for the problem and not choosing to just use one database for all parts of an application. I already have some customers that supplement their SQL Server with Redis, Elastisearch, AWS or Azure queues, cloud storage buckets/containers, and more.

    I’m a practical person, who likes to pick the solution that works well for the problem I need to solve. I have no issue with using multiple technologies, and as my career as advanced, I think it’s easier than ever to do so. If you haven’t considered other data stores, perhaps you want to learn a bit about them and see if they could help improve your system. Keeping an open mind and considering alternatives is good, and I recommend it. What I don’t think you should do is choose another technology because you like it or are interested in learning more about it. Pick the solution that works best.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Virtual Data

    Yesterday I republished an editorial from 2014 for the holiday. The topic was production subsets of data, which has been something that many data professionals have struggled with for years. Many of us have built scripts to delete, change, obfuscate, or alter production restores as a way of providing useful, but manageable development database sets. Or maybe it’s just some of us. I’m sure more than a few of us have given up on this task and just restored production databases in entirety to test and development systems.

    I changed over my career to become a fan of additively building a known dataset rather than deleting extra data. I advocate adding rows from production (properly masked/obfuscated) and maintaining this set over time as requirements change. However, this isn’t without it’s own administrative headaches. I think it’s easier, but this does require commitment from everyone to keep going over time. It’s certainly better than each developer adding their own 10 rows of data to a table for testing.

    A year ago, Redgate released SQL Clone, designed to solve some of these issues. Once an image is created, new databases for test and development and be provisioned in seconds. I found this to be an amazing product that really changes how I develop against databases, though it does require me to stop getting caught up trying to undo changes or manage a single database. Instead, I need to ensure I am saving code to version control and then build the habit to drop and rebuild a baseline database.

    As we’ve worked on SQL Clone, I’ve found that there are lots of companies that offer similar ways of virtualizing your data, giving you access to large, production scale systems in seconds. Data masking, obfuscation, and more are features, with some vendors requiring specific hardware. Others, like Red Gate, have software add-ons (Data Masker).  All of these products cost money, which can be an issue for many organizations, but I’m glad that this technology is growing and advancing. With GDPR and other draft legislation, many of us need to take better care of our data and build more secure architectures.

    Containers are another interesting way to virtualiza data, though they don’t solve the scale issues. If you can work with a smaller data set, and maintain that, then containers might provide a fantastic way for you to learn to build, teardown, and rebuild databases in seconds.

    The world of databases hasn’t changed a lot in some ways across my career, but in others, I’m amazed. Data virtualization is one of these areas, and if you haven’t trialed the technology, maybe you should give it a whirl this year.

    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.

  • Looking Back at Build–CosmosDB

    Part of a series looking back at Build 2017, going over the 20+ pages of notes I took.

    One of the big announcements at Build 2017 was the release of CosmosDB. This is a rebranding of DocumentDB, the document store in Azure, with some additional capabilities. You can also choose to store as

    • Column-family (columnar)
    • Key-Value
    • Graph

    The service is interesting in that it supports the MondoDB and Gremlin protocols for querying, which should allow some people to consider Azure instead of those platforms. The announcement was interesting from the data perspective. Microsoft focused a decent amount of time on this platform, and certainly people at the conference were very interested in the offering. Quite a few developers were thinking this would replace SQL Server, since it has some nice capabilities.

    One of the big ones is that CosmosDB offers < 10ms latency for reads and < 15ms for writes. They scale up to millions of transactions/sec, and also grow to PBs in size. There are some good guarantees for the database.

    They call it infinite, but that’s a marketing term. There’s a limit, and there certainly may be a limit you’re willing to store in the service. Jet.com was the featured customer and they talked about how much data they used and how they can scale the platform. I’m sure they went with DocumentDB and moved over, but they have their story in the keynote, if you want to watch. They get over 100 trillion, yes trillion, CosmosDB transactions/day. No matter what you do, a trillion of anything is a lot.

    Perhaps the more interesting thing is that Azure is offering five different consistency models in CosmosDB. They are

    • Strong
    • Bound Staleness
    • Session
    • Consistent Prefix
    • Eventual

    I don’t know how well these are supported in other platforms, but I like to see that developers have a choice. I’m not sure how many will understand the trade-offs and implications, and how many will get burned by choosing one over the others, but I’m glad the choice exists.

    They are also saying a money back guarantee. What that means, or how you get money back, is going to be something to see. I haven’t always been thrilled with the disclosure in billing for new parts of Azure services, but perhaps there is going to be some way to request credit, though I would hope that any telemetry that shows issues results in some credit.

    The other good thing is that the SLA is guaranteed across multiple dimensions: latency, throughput, availability, and consistency.

    Rimme Nehme, who demo’d the product, had an interesting quote. It was something like “developers can concentrate on the app, not the database.” Forgive me if I’ve slightly misquoted as I’m going from notes. While I cringe a bit as a data person, I do know that most developers don’t want to really work deep in the database, especially for many simple apps. If I were building some simple mobile app, I’d seriously look at CosmosDB, and I plan to learn a bit about it.