Voice of the DBA

Tag: databases

  • Data Preservation

    Maintaining data across time isn’t something many of us think about. We work with data in the here and now, and in the database world, we typically only need to recover or restore data from a short window. Like most of you, I would usually plan on recovering data that’s only a few days old. Being forced to restore a database from two weeks on any of my systems would make me cringe. It certainly would be embarrassing for me personally if it were my fault I couldn’t restore to a point in time that was more recent than that.

    In planning to recover our systems, we typically know the versions of software we have to recover from, and we can easily re-download copies of SQL Server or the patches we need. Most of us are dealing with SQL Server 2000 or later, which is good since those are the only versions still documented on MSDN. If you need SQL Server 7.0 or SQL Server v6.5 documentation, I hope you have copies.  The same goes for the media. You can still download SQL Server v6.5, and SP5, but if you needed SP3, it isn’t easily available. I ran into that situation about 10 years ago, and we had to make a special request through our TAP manager to get someone in Redmond to dig up a copy.

    In some ways it might not be important to worry about long term storage. Most of us will end up transferring our data to newer systems (and formats) over time. As we upgrade SQL Server, our databases move along to newer formats, or we abandon them because they are no longer needed. That’s fine for some data, but not all.

    Long term archival and storage is a challenge, as you can see in this short look at how old films are maintained. It just touches the edges of what’s being done, and doesn’t address costs. Plenty of old films have been lost forever, and perhaps that doesn’t matter, but it does concern me. I have thousands, maybe tens of thousands of digital images. While I love the ease with which I can share them with family, and make extra copies, I am worried that perhaps the lack of a physical copy means my great-grandchildren will struggle to find evidence of my generation if there is a catastrophe or storage formats change.

    This is one area of our industry in which we have a lot of maturing to do, and I hope that we can come up with some new ideas for maintaining our data for the long term, across not months or years, but decades or centuries.

    Steve Jones

    Video and Audio versions

    Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo atwww.everydayjones.com.

    Follow Steve Jones on Twitter to find links and database related items and announcements.
    Steve Jones Windows Media Video ( 18.9MB) feed

    MP4 iPod Video ( 22.0MB) feed

    MP3 Audio ( 4.5MB) feed

    Feeds are available at iTunes and Mevio

    To submit an article, rant or editorial,
    log in to the Contribution Center

  • It Starts with Version Control

    As a software developer, I learned about the value of version control over time. My first forays into development were as a child, where I made a single backup of my code on a separate floppy disk in the event I had an issue. This was my one-man-wolf-pack version control system, and it served me well, although it wasn’t efficient. In a few of my early development positions, we zipped up all our code each night to a separate folder, giving us the pentagram-version-control system. This worked as long as we didn’t need to go back over a week, but it still wasn’t very efficient. I think that because we worked on fairly small projects and could hold most of the code in our minds, we muddled through issues.

    Later I was introduced to a real version control system (VCS), Visual SourceSafe. Some of you cringe, but that system worked well. We could see the differences in code, and over time I learned to cut loose with my development, just trying new things, knowing that I could easily revert code backwards without much effort. Since then I’ve worked a bit with Subversion and Git, both of which seem to be very capable, mature systems. I still plan on getting some work done with Mercurial and TFS at some point, just to compare the features and functions.

    I started source controlling my databases after reading an article by Bill Wunder. This was over a decade ago, and it seemed to make sense to me. All of the things I had learned about VCS worked with databases as well, and I learned that having a database under VCS provided me with some stability in development, including the ability to roll back to previous versions of code when we found bugs. Trust me, checking out a previous version of a stored procedure is much, much less stressful than restoring an old backup just to look at stored procedure code.

    These days I see more and more people putting their databases under version control, which is comforting. It makes me think that we, as data professionals, are maturing in our industry. However there are still lots of people out there that don’t use a VCS for their database (DDL) code. Some don’t even use a VCS for their application code.

    There’s no excuse these days. Subversion and Git are free and open source. Download them, spend a few days, and get control of your code. All of the ways in which you can improve the efficiency and quality of your software start with more knowledge, and a bit of version control on which to apply your craft.

    Steve Jones

    Video and Audio versions

    Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

    Follow Steve Jones on Twitter to find links and database related items and announcements.
    Steve Jones Windows Media Video ( 23.4MB) feed

    MP4 iPod Video ( 27.2MB) feed

    MP3 Audio ( 5.7MB) feed

    Feeds are available at iTunes and Mevio

    To submit an article, rant or editorial,
    log in to the Contribution Center

  • Database Queries and Scalability

    I ran across this post with a very provocative title:  (thanks Brent Ozar Unlimited). It’s an interesting read from a developer, talking about how so many website designs aren’t built for scalable because they rely too heavily on a set of database queries place on every page. If you expect a database query to retrieve or store a user id, or other data on every page, then you are limited to the scalability of your database back end. There’s no doubt there, but the author has ideas that can help you overcome these issues.

    One of the problems is that systems read and write account information (or registrations) to the database directly. That’s definitely a scale issue, and the author suggests generating the client information on the web server and storing it in a more robust and scalable backend. That makes sense, but it’s with the caveat that ” As long as you eventually write the data to the db later on, you’re good to go.” Good point, but now you need a replication process of some sort (or messaging) that gets the data to your database. This ignores some of the synchronization issues across multiple servers as well, but it’s not a bad idea.

    The idea of avoiding joins, by using a cached set of data to iterate through and pull data from other tables may work in some cases, but I’m not sure this is the most efficient way of grabbing data from a database, ensuring it’s the latest data, and not impacting concurrency. This technique seems to invalidate the power of an RDBMS by not using joins to effectively, and efficiently, retrieve data. I suspect replicated copies of slowly changing data is a better way of scaling your system than avoiding joins.

    Overall I don’t love the article, and depending on which platforms and technologies you’re using, it might be better, or worse, advice. I would say that building better systems involves considering alternative techniques, allowing for failures, and using new techniques like messaging, caching, asynchronous processes and more. As we try to improve the quality of our software, we should be talking about, writing about, and trying out new techniques, learning what can work better in our environments.

    Steve Jones

    Video and Audio versions

    Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

    Follow Steve Jones on Twitter to find links and database related items and announcements.
    Steve Jones Windows Media Video ( 22.3MB) feed

    MP4 iPod Video ( 25.7MB) feed

    MP3 Audio ( 5.2MB) feed

    Feeds are available at iTunes and Mevio

    To submit an article, rant or editorial,
    log in to the Contribution Center

  • Getting Started with CI and Databases

    It’s fairly easy. At least, I think it is if a salesperson can do it.

    Alex Yates, salesman extraordinaire at Red Gate Software, spent a lunch hour recently setting up a CI system so he could better understand how hard, or easy, it can be for customers. He wrote about his experiences using the Jenkins CI server.

    Check it out. If Alex can do it, I bet most of you developers and DBAs can as well.