Me, My Database and I

Thirsty Developer Interview Posted

Chuck — Tue, 28 Apr 2009 22:26:00 GMT

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

SQL Server 2008 Backup Compression

Chuck — Tue, 21 Apr 2009 03:00:00 GMT

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

PASS Call for Presentations - You've Got Through April 24, 2009

Chuck — Mon, 20 Apr 2009 00:59:00 GMT

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

SQL Server 2008 Upgrade Woes

Chuck — Mon, 02 Mar 2009 22:37:00 GMT

I’ve been working on upgrading an application from SQL Server 2005 to SQL Server 2008. Simple process, right? Well, I ran into a few issues along the way that I thought I would share with you.

First, make sure that your processor configuration matches your affinity mask. You might be thinking that I’m crazy right now, but it is a problem that I think more people will run into. Take the following example:

You are planning to upgrade a production system and you want to test it before you do the upgrade. Your production server has 8 processor cores and you have an affinity mask set to use cores 0, 2, 4 and 6. You take a P to V image of your production system and stand it up in a virtual environment (such as HyperV), but that virtual environment only has 4 processor cores configured.

In this scenario, the upgrade will fail. The user interface will not tell you exactly what went wrong…rather it will direct you to a log file that is extremely full of information. When you finally find the error message in the log file, you will find that it could not do the upgrade because the affinity mask could not be set.

I noticed this when one of my development servers (an 8 core Itanium) was rebooted and only had four cores available when it came back up. Once I got the other four cores online, the upgrade was able to complete.

The other issue came out of restoring databases. I currently use ToolX (name withheld because I don’t know if it is the cause) for my production backup software. When I upgraded to SQL Server 2008, I used ToolX to restore backups from one of my SQL Server 2005 environments into SQL Server 2008. The backups appeared to complete successfully, but when I ran a DBCC CHECKDB on them, some of them had consistency errors that did not exist in the original that the backups were pulled from. I had to do a repair_allow_data_loss to get the databases usable again.

I haven’t nailed down what the exact cause of the second problem is, but I will post a follow-up when I know.

PASS Community Summit Recap

Chuck — Thu, 04 Dec 2008 01:18:00 GMT

This year was my last year as a member of the leadership of the Professional Association for SQL Server. I will still be involved with the organization, but will be rolling off of the Board of Directors at the end of my 2 year term in December.

That made this event a little different for me. I've been at 9 of the 10 US PASS conferences - the only one that I missed was the first one. I've attended them in a variety of capacities - speaker, board member and even general attendee.

This year was the largest PASS conference ever. It included three amazing keynotes. My favorite was given by Dr. David DeWitt. Dr. DeWitt is a Technical Fellow with the Gray Systems Lab in Madison, WI (which is located just an hour down the road from me). The keynote contained no marketing - the entire presentation was about the theory and research that goes into building high performance scalable database systems. It was one of the best technical lectures that I have ever attended.

There were over 120 technical sessions at this conference - and I did manage to make it to a few of them. One of my goals for the conference was to meet as many Microsoft employees as I could, and I think I did quite well for myself. I also served as the moderator for a panel debate on the pros and cons of NULL. The panel consisted of 5 SQL Server MVPs, and one guy who should be one. The session was well attended and the debate got quite heated at times. Who would have thought that a debate about nothing would be so well received?

Next year's PASS conference is again going to be held in Seattle. The dates are November 3 - 6, 2009. I encourage anyone who is interested in the best SQL Server education for the money to attend.

That's it for now...got to get back to installing MOSS and PerformancePoint - but that's another blog post entirely.

Chuck

I'm an MVP!

Chuck — Wed, 02 Jul 2008 06:26:00 GMT

I found out this morning that I've been named a SQL Server MVP (Most Valuable Professional).

For those of you who don't know what the MVP program is, here's a brief overview.

Each quarter, Microsoft selects a group of people across many different technologies to become MVPs. Your selection is based on your community involvement over the past year. Community involvement can include presenting at user group meetings, posting answers to questions on newsgroups, writing articles for online magazines, etc.

My community involvement is primarily through the Professional Association for SQL Server (PASS). I currently serve on the Board of Directors and am in charge of two techincal publications - a newsletter that goes out every other week and a magazine that goes out every other month.

Thank you to everyone who has supported my efforts in the community over the years. You'll probably be seeing much more of me out there in the future.

Chuck

SQL Server 2008 RC0

Chuck — Fri, 13 Jun 2008 18:39:00 GMT

For those of you who haven't heard, RC0 of SQL Server 2008 has been released.

If you have played with any of the CTP releases of SQL Server 2008, you'll notice that there are a few differences in the installer - but not enough to cause you to relearn anything.

I loaded it up on my dev box this week, and I am quite impressed with what I am seeing so far. There's a nice new Activity Monitor that is similar to the default view of the Vista Reliability and Performance Monitor - and it even works against SQL Server 2005 servers.

Over the next couple of months, I'll be writing some fairly detailed blog posts on SQL Server 2008 - each of them based on a different new feature. Keep checking back - I hope to profile at least one new feature every other week.

Chuck

TechEd Developer - Day 3

Chuck — Fri, 06 Jun 2008 20:14:00 GMT

Another day, more booth duty. I spent a good part of my day working with the VSTS Database Edition team. Great group of people - and they have a great product. It is nice talking to people who have no idea how the product can help them. When they see what it can do, they normally want to get it in place quick.

I also caught a session on a new in-memory distributed caching technology from Microsoft codenamed "Velocity". It is quite impressive. It can actually be an in-memory store for your IIS Session State - and it allows you to move from machine to machine in a web farm without losing your session!

More to come on Day 4!

Chuck

TechEd Developer - Day 2

Chuck — Thu, 05 Jun 2008 20:35:00 GMT

I spent a bunch of time today in the SQL Server TLC area down on the show floor. I did, however, manage to get away for a great session on Extended Events presented by Bob Beauchemin - who I had dinner with the night before at a great Brazilian steakhouse. It's amazing all that you can do with the extended events architecture.

More to come tomorrow.

TechEd Developer - Day 1

Chuck — Tue, 03 Jun 2008 15:10:00 GMT

I just got out of the keynote for TechEd Developer. Bill Gates actually opened up for a Q&A session from the floor. Quite impressive.

It was announced that Silverlight 2 Beta 2 will be released by the end of the week, and that you will be able to get a Go-Live license. I know some of my co-workers will be happy about that.

I'm getting ready to go do my first shift in the TLC area. I'll be working the SQL Server area (look for the green). Today I'll be at the Storage demo station. The rest of the week I'll be splitting my time between Storage and Team Edition for Database Professionals. If you're here, stop by and say hi!

That's all for now. I'll have more to report later.

Chuck

TechEd Developer

Chuck — Fri, 30 May 2008 21:15:00 GMT

If you're going to be at TechEd in Orlando next week, stop by and say Hi! I'll be in the Microsoft TLC area over lunch Tuesday through Friday. I'll be helping out the VSTS folks. Hope to see you there.

PASS Call for Presentations - Deadline Extended Again!

Chuck — Tue, 01 Apr 2008 16:39:00 GMT

Due to technical difficulties, the deadline for submitting abstracts for the 2008 PASS Community Summit has been extended through Monday, April 7, 2008.

If you are interested in submitting an abstract, please do so at http://calltospeakers.sqlpass.org/.

Chuck

PASS Call for Presentations - Deadline Extended!

Chuck — Fri, 28 Mar 2008 16:55:00 GMT

The deadline for submitting abstracts for the 2008 PASS Community Summit has been extended through Monday, March 31, 2008.

If you were considering submitting an abstract but thought you ran out of time, you now have a few extra days. Let's get those abstracts drawn up while watching basketball this weekend!

Chuck

PASS Call for Presentations

Chuck — Thu, 27 Mar 2008 18:59:00 GMT

The call for presentations for the 2008 PASS Community Summit closes on Friday, March 28, 2008. If you are interested in submitting an abstract, go to http://calltospeakers.sqlpass.org/.

The PASS Community Summit is a great opportunity to start your career as a conference speaker or to add to your presentation list if you already present at conferences. Please consider submitting an abstract or 6.

Chuck

Upcoming Presentation

Chuck — Thu, 27 Mar 2008 14:30:00 GMT

If anyone is interested, I will be presenting at the Wisconsin SQL Server User Group meeting in June. The meeting date is Tuesday, June 10, 2008, and it will be held at the Microsoft office in Waukesha, WI. The meeting starts at 4:30 PM, and usually is wrapped up by 6:30 PM (to allow people to make it to the local .NET user group meeting at 7:00 PM).

I'll be presenting on new features in SQL Server 2008. This will be a follow-up to the session in April by James L.

If there are any topics that you would like to see covered, please add them as comments to this post.

See you in June!

Chuck