Voice of the DBA

Tag: sql server

  • Risky Backups

    This editorial was originally published on Jan 13, 2009. It is being re-run as Steve is away at the SQL Intersection conference.

    The other day I was working in the forums at SQLServerCentral and saw a question about someone doing backups and having issues. While myself and others were trying to help, I recommended that this person not run multiple backups to one file. It’s something I’ve done for years and I’ve recommended, but the person followed up with a question about why I think it’s important.

    My first answer was that it’s an unnecessary risk. If something happens to the file and you’ve stored multiple backups in this file, then you lose all the backups. I’ve seen this happen with older versions of SQL Server and after that time I made it a point to never stick multiple backups in one file. To me it’s creating a single point of failure for no reason.

    But as I wrote this, I started to question myself a little. I know that some people like having the same filename every day as it makes restores simpler, especially if they’ve scripted things.  I know that file systems and hardware is often redundant and files are generally safer in the short term than they were in the past, so is my advice outdated?

    I still don’t think so because in my mind there’s a great risk of issues here and that outweighs the convenience. Not to mention that some simple  scripting can make restoring to something like QA easy enough for anyone to run.  However I’m curious what the rest of you think about storing multiple backups in one file.

    Steve Jones

    The Voice of the DBA Podcasts

    Everyday Jones

    The podcast feeds are now available atsqlservercentral.mevio.com to get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted, and you can get feeds from there.

    Overall RSS Feed:  or now on iTunes! 

    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.

  • SQL Server Knockoffs

    This editorial was originally published on Mar 17, 2009. It is being re-run as Steve is away at the SQL Intersection conference.

    I thought this was an interesting post from John Dvorak on the Microsoft business model. I’m not sure it’s the end of Microsoft, after all, many corporations would not go with non-standard software since it might cause compatibility issues, and the cost of the software might be noise compared to all the questions, complaints, and potential retraining of employees.

    But that could change. And I think that Microsoft is in a bit of a downward spiral. One of their main products, Office, is essentially done. What more can you do to those applications that people need? Even Outlook hasn’t really added much value for a few versions. Most of us view these types of products as commodities, and a few more features don’t really add a lot for us.

    Even the OS is starting to disappear a bit for many people. We live our lives in applications, on the web, or with applets, like Twirl or TweetDeck, that really interact with the Twitter service. Twitter is really what matters, not the OS or even the application I am using.

    So what about the relational database? Are we heading down the road of the RDBMS becoming a commodity? Right now SQL Server, Oracle, and DB2 all constantly strive for a good amount of feature match between them. Each comes up with something new in each release, but how many of these new features are compelling? Already I think a lot of people are perfectly happy with SQL Server 2000, and the only reason to move is because of support.

    What if we get to the point where a MySQL or PostgreSQL type knock-off that runs T-SQL comes along and implements 80% of what SQL Server 2005 has. Would people switch to that engine? I don’t think people would completely convert their environments, but what about the 20% of your servers that are barely used, or are used for homegrown applications? If I sold you a “SQL Server knock-off” for $500, would you buy it instead of the $1895 for SQL Server Standard? What if it wasn’t priced by the CPU?

    I think SQL Server is the best RDBMS around right now, from a feature standpoint, price, performance, and support. Sure it has bugs, sure it has issues, but overall it’s a great database at a great price. It has ETL features, reporting, and most importantly, an extremely well performing database engine. However I can see someone trying to enter the market as a competitor in this space, just like the “netbooks”, hyperspace and theThinkOffice suite are trying to do to general business applications.

    After all, it’s easier to “copy” an application than innovate, and I think at some point the list of copied applications will go beyond Word and Excel.

    Steve Jones

    The Voice of the DBA Podcasts

    Everyday Jones

    The podcast feeds are available atsqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there.

    You can also follow Steve Jones on Twitter:

    Overall RSS Feed:  or now on iTunes! 

    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.

  • 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

  • It Still Happens – Full Transaction Log

    Probably the most often asked question on SSC is about full transaction logs, and often full disks. It happened again recently. I don’t have an exact count of threads, but rough searches show this to have 70,000+ results, when most other searches I run show many, many fewer results in the forums.

    I talked about this recently in my backup talk on the SQL in the City tour stops. Back up your logs.