Voice of the DBA

Category: Editorial

  • Hadoop and SQL Server

    There has been a lot of media attention to Hadoop in the last few years. In fact, Microsoft has spent a lot of resources to build the HDInsight version of the platform and integrate it into SQL Server. I’ve read quite a bit about how to setup and query with Hadoop, but haven’t used it for a real project. In fact, it seems relatively few people seem to be finding it to be a replacement for, or better solution than, SQL Server. We published a great introduction to Hadoop written by David Poole awhile back, and recently I ran across another nice writeup from someone I think is a very talented SQL Server professional.

    Michelle Ufford (@sqlfool | b) wrote a piece asking if Hadoop is better than SQL Server. Michelle notes that Haddop is a different platform, and it’s a great way to consume lots of data. In fact, she has a graph from EMC talking about the data explosion and how we still at the low end of the exponential growth curve of data production. It’s a sobering thought and I tend to agree with Michelle and EMC on the growth of data.

    I had hoped Microsoft would do more with Filestream and Filetable to help meet the challenges of large volumes of data, but it seems that very little has been done with those features in the last version of SQL Server. I have little hope that additional investment will come in the future. Instead, it seems Microsoft is leaning towards using Hadoop as one way to process and consume large volumes of data.

    I wrote about Hadoop in 2009 when it was a young project, and I suspected it would enhance and work with, rather than supplant, the RDBMS. There are certainly other technologies out there to help with this, but if you are working with large volumes of data that exceed what a single instance of SQL Server can handle (at a reasonable cost), you might think about learning a bit about Hadoop. It might not solve your issues, but if it can, it would be good to know something about it.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Selling Automation to Ops

    The DevOps movement isn’t new in some companies. It’s the same coordination and teamwork that has existed for a long time between the development and operations staffs. Developers take advantage of the skills in Operations to get standardized environments for their work, and let the Ops people manage (and track) changes. Operations people talk to developers about the challenges and issues faced in production, and the let the developers build applications that can easily be deployed. The sharing of information ensures each group knows what the other faces, and the regular contact builds bonds and respect between employees. Neither wants to let the other down or make someone else’s job any harder than it needs to be.

    However that’s not the case in many companies where developers view Operational staff as complainers that slow the process down. Operations staff see developers as wild and irresponsible, tossing code into production that they don’t need to support and haven’t tested. Both of these views are correct in that each side sees a reality in the process that makes their job more difficult.

    Ultimately I believe it’s up to developers to change things. Those of us that build the software need to respect the problems that instability causes and learn to help ensure that our changes can be deployed smoothly. The development side of an organization has more skill in tracking changes in version control, in managing the movement of those changes among environments, and in programming systems. We should be working to help push that knowledge through to Operations personnel that become responsible for our applications.

    That means we need to build scripts and tools to migrate our changes and give them to Operations. I’d recommend that we learn how to automate the configuration of our development systems, as well as script our changes. Most modern platforms allow us to programmatically make changes, so let’s do that. Then let’s take a few hours and show Operations people how to use these scripts, and let them setup and change our development environments. It will be slow at first, but they’ll learn to make changes faster, but also bring stability to every environment from development to QA to production, and can ensure we have the same configurations everywhere. We’ll have one less thing to manage, and our changes will get deployed faster, but also more consistently.

    Ultimately we all want the same thing. Better software delivered to customers faster. We want to Ship Safe, and Ship Often.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Data Lakes

    I heard a new phrase this week: the data lake. It comes from a Radar Podcast episode where Edd Dumbill talks about data as an asset that should be exposed everywhere in an organization. There’s a blog post on the subject as well.

    The idea is somewhat centered on Hadoop, but it could apply elsewhere. Data comes into an organization and then tends to seek and get stored with other data in a large lake. Applications are just a way of accessing the data in the lake, but all the data really lives in a large Hadoop lake of information. In some sense, this isn’t far away from the “single view of the truth” that I’ve seen plenty of organizations attempt. In a relational world this means all data moves from OLTP systems to a large data warehouse, and is then moved to smaller data marts (really warehouse subsets) and is accessed from there.

    It’s a good idea in theory, but in the practice of trying to move data around with any velocity between users, with all the copying, cleaning, transforming, and more going on doesn’t work. OLTP systems are needed because there are transactional actions that must be completed quickly and accurately. Moving this data to other systems becomes harder as data volumes and the sheer number of clients (whether users or other systems) increase. The idea that we can keep all of our processes working quickly enough that users won’t get frustrated is likely a dream. The more a value exists in a set of data, the more users will access it. The more accesses, the slower it often becomes, which starts a cycle of smaller subsets of data and applications that subsist on those small data puddles.

    Excel is probably the most common example of a data puddle that exists in your organization. A set of data, perhaps out of date, but useful enough to make decisions based on. Infinitely flexible and convenient enough that updates, changes, and more often spawn more and more puddles where the information never gets transferred back to the large lake of a database, whether that’s an RDBMS, Hadoop clusters, or something else.

    I think the idea of a large data lake is great, but in a practical sense, much of an organization’s data will never live in the lake. If it does, it will most likely be data that’s been superceeded by information in a puddle somewhere on an employee’s laptop, tablet, or personal cloud.

    Steve Jones

  • Multiple Backup Files

    I’ve been writing a little PowerShell lately that will back up databases, move files, and restore them. I’m often testing things, and having scripts to quickly and easily move files is very handy. After one of my posts recently, a reader asked if I’d considered multiple files and how to handle them in scripts. I confessed I hadn’t, mostly because I haven’t had to deal with them.

    In my career, I’ve tended to work with small to medium sized databases. I’ve had young children for a large portion of my SQL Server career and had no desire to babysit multi-hour (or multi-day) restores when things break. I know some people have been through those situations, and good for them. I just know I’ve had enough issues with the low-GB sized database, and haven’t been interested in supporting TB sized systems.

    However that likely wouldn’t be the case in the future. More and more companies are collecting and storing data that reaches into the TB, even in small companies. The rapid advances in sensors, development tools, and cheap storage means that many people are dealing with hundreds of GB in at least one of their databases. That means for a reasonable RTO, making quick backups, and maintaining good performance, multiple backup files are becoming a necessity.

    Is that really the case? Data volumes are exploding, but you many of you using this feature? I wanted to see how many people have implemented, or at least thought of striped backups. The poll this week is:

    Do you have any databases that benefit from backup to multiple files?

    I’ve consulted with clients that accidentally produced striped backups and then lost one of the files. That’s never a good situation, and it’s bad news to have to give as a consultant. However, I’m sure many of you have consciously implemented striped backups because they can perform better than single file backups and make for quicker restores. Others of you may suspect (or have tested) striped backups will help your systems but haven’t gotten around to setting things up.

    Let us know if this is a common feature you use, or is it still something esoteric that you have no need for. And if you have put striped backups in place, have you tested a striped restore? I certainly hope so. Let us know either way.

    Steve Jones

    The Voice of the DBA Podcast

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