For those of you interested, I have recorded an interview with The Thirsty Developer (http://www.thirstydeveloper.com). The interview is based around a user group presentation that I give about the new features of SQL Server 2008 for developers.
I do need to clarify one statement in the interview. We were talking about spatial data and support for the Z dimension. We talk about SQL Server's support for Z, and I want to clarify that SQL Server will allow you to store the Z dimension with points but it will not use Z in calculations.
Thank you to Larry Clarkin and Dave Bost for inviting me to participate in their project.
Chuck
With SQL Server 2008, Microsoft introduced native backup compression. Prior to SQL Server 2008, the only way to get real-time backup compression was to buy a third-party tool. There were – and still are – several third-party tools on the market. I have used a few of them, and they each have their pros and cons. But, this post is not to debate the merits of the different tools – it is to discuss the native functionality that ships with SQL Server 2008.
If you’ve ever used an off-the-shelf compression utility (such as WinZip) on a SQL Server native backup, you’ve probably noticed that they usually compress down quite well. I’ve seen certain backups compress down 75% or more. The problem with this method is that you need a lot of disk to make it happen. If you have a 10GB database, a full backup will take 10GB of disk space. If that backup achieves 75% compression, if will end up taking up 2.5GB of disk space. The problem is that you really need 12.5GB of disk to get to a 2.5GB compressed backup (10GB for the initial backup then an additional 2.5GB for the compression). Now, increase the size of your database by 10 or 100 times. Do you really have an extra 1TB of disk lying around so that you can end up with a 250GB backup? I certainly don’t.
This is where SQL Server’s native backup compression can really help. The compression happens as the data is being written to disk, not after it is there. So, if you have a 10GB database that will achieve 75% compression, you only need 2.5GB of disk to perform the backup – not 10GB like you would without compression.
From my experience, the native backup compression performs quite well, although your mileage may vary. You will have additional processor overhead above and beyond the overhead required for a standard backup, but in my testing the overhead was comparable to what I was getting with several of the third-party tools.
I don’t want it to sound like I’m against the third-party tools. I still use a third-party backup utility where it makes sense. Remember that the native backup compression won’t fit all situations – most of the third-party tools include encryption and other features that are not included in the native solution – and there isn’t a nice management console like what you get with the third party tools. But, if you can live without these added features, you might be able to save some money by using the native backups.
The best advice that I can give you is to give it a try and gather metrics to compare your third-party tool to the native compressed backups. Look at the backup time, restore time, processor utilization and final compressed size. If your experiences are similar to mine, I am guessing that you will be quite impressed.
Chuck
Those of you who know me know that I am a big supporter of the Professional Association for SQL Server (PASS). I have been involved with this organization since 2000 - when I spoke at their North American conference in San Francisco. Since then I have held a variety of volunteer positions including author, editor, presenter and - for a few years - member of the Board of Directors.
I can't express enough how important I believe this organization is to the SQL Server community. The annual conference is a hotbed of SQL Server education. In my opinion, you won't find any better source for high-end, high-quality SQL Server speakers and presentations. In reality, PASS is much more than the annual conference - but I'm here to talk about the conference.
Each year PASS requests presentation abstracts from members of the SQL Server community. It has been like that all along (that's how I got my start with PASS). The non-Microsoft speaking slots at the conference are filled by a committee of volunteers who review and evaluate the submissions that are received. This is a daunting task, and I truly appreciate the people who do it.
I would like to encourage anyone who has ever thought - or even if you have never thought - about presenting at a conference to hit www.sqlpass.org and submit an abstract. If you've got something that others might be interested in hearing about, write it up. Speaking at an event like this is not as hard as you might think - it's actually quite fun.
You can submit up to four abstracts. Now go forth and submit!
Chuck