On Tuesday we had a look around our customer's data centers here in Austria - hidden away in the labryinthine bowels of a very large building in Vienna. Typical data centre with a halon fire extinguishing system but exceptional in its neatness and organization. The star of the show was their new HP Superdome - 32 dual-core Itaniums with 1/2 a terabyte of memory. Very nice - providing a lot of headroom for their workload to grow (currently at four hundred thousand SQL statements per *second* - spelled out to show there's no accidentally added zeros).

One of the systems they showed us is responsible for doing backups. They stripe the backups across 12 devices using multiple network cards and can manage to backup 2 terabytes in two hours after tweaking the BLOCKSIZE, BUFFERCOUNT, and MAXTRANSFERSIZE! Now comes the cool(er) part - with backup compression on SQL Server 2008 they've benchmarked backing up 2 terabytes in 36 minutes! That's a pretty awesome number and makes for some excellent disaster recovery times.

(Details printed with permission)

During early SQL Server 2008 development, the Storage Engine team introduced additional minimally-logged functionality. And then it was removed again for RTM. Now it seems that they've bowed under customer pressure and have made the functionality available again (see Sunil's announcement here).

Using trace flag 610 in the RTM build, you enable the potential for minimal-logging when:

• Bulk loading into an empty clustered index, with no nonclustered indexes
• Bulk loading into a non-empty heap, with no nonclustered indexes

Sunil's previous blog post here gives more info on the required syntax - very useful!

It's been almost exactly a week since the last post - an unusually long time for me. Kimberly and I were teaching the first week of the inaugural Microsoft Certifed Masters SQL course last week in Redmond (a little intense at 8 hours of *teaching* per 10-hour day - so no energy for blogging afterwards). Now we're in Vienna, where we're on-site with one of our favorite customers, then we head to Barcelona next week for TechEd EMEA, back to Vegas for SQL Connections the week after, and finally back to Seattle for PASS the following week. Phew! Look for a photo-stuffed Where In The World Are Paul and Kimberly post from Vienna next week.

I'll try to post some interesting stuff that comes up while we're at the conferences - always a good crop of questions.

In this quick post I want to touch on something that's becoming more important to explain as more and more people start managing systems who haven't got a lot of DBA experience (what I like to call involuntary DBAs). How do you plan a backup strategy? As you'll see if you look at my Backup/Restore category, I've got lots of info about specific types of backups but nothing on putting a plan together in the first place.

It's very simple to decide on using the SIMPLE recovery model and regular database backups - the backup schedule is easy. But, what happens when you come to recovering from a disaster? Which backups to you need to restore and how long does it take you? If you take weekly full backups, say, then you stand the chance of losing a lot of data if the disaster occurs just before your next full backup. So, if you switch to the FULL recovery model and add log backups, you can recover right up to the point of the crash.

But again, what backups do you have to restore and how long does it take you? Are you able to restore within the maximum allowable downtime for your business? If you have a 300-GB database, and the downtime allowance is 15 minutes (as with one DBA I've known), the answer is no. Do you need to move to a partitioned schema that makes use of multiple filegroups so that you can use partial database availability to bring your application online faster with only the critical filegroups?

The key point when planning a backup strategy is not to think about what backups you want to take - think about what restores you have to be able to perform, then work backwards from that.

Cheers

The sparse columns feature in SQL Server 2008 is generating lots of interest from people looking to deploy extensible schemas. I've seen a few questions from people that are confused by some of the info in Books Online, particularly about adding and removing sparse columns from a table.

There's a section in BOL that states that sparse columns are added and removed from existing tables by creating a new copy of each row *on the same page* and then deleting the old row, and that this can fail when the row size is around 4009 bytes (1/2 the max row size when sparse columns exist). I've been trying to repro this behavior as it seemed a nonsensical design to have used (and the design was done after I'd left Microsoft so I've never seen the underlying code) - and I couldn't. I finally got around to discussing this with the dev team last week and had it confirmed that Books Online is indeed incorrect - there is no such issue with sparse columns. I've been told that BOL will be corrected.

One other issue that's come up is whether sparse columns work with row overflow (i.e. rows greater than the size of a page, where one or more variable-length columns are pushed into off-row storage). The answer is yes, it works just the same as when the column isn't sparse.

Hope this helps some of you.

One of our customers (and friends!) sent us this last week - a stark reminder why hardware redundancy is a good idea!

The controller card in one of his servers literally blew up at 7.15am Tuesday morning. Although they didn't have redundant hardware, they were back online by 9am - pretty good going. Funnily enough they've been considering clustering  for a while now...

(Posted with permission.)

Very exciting! The SQL Server 2008 Internals book we're working on with Kalen is now available to pre-order on Amazon, ahead of the planned publication date of February 18^th next year.

I'm neck-deep in the Consistency Checking Internals chapter right now - its shaping up to be about 100 pages on its own!

There's no description yet on Amazon but at least you can pre-order - see here.

Thanks for your patience while our blog engine back-end was updated. You should find that the response time is much better now. You'll notice that all the blog post URLs have changed - we have a conversion layer in place so all prior blog posts will be seamlessly accessible through their new and old URLs. There are a few kinks being ironed out still - the Recent Posts pane on the left will be replaced with an On This Page pane, so that Category-based browsing (which I really need for classes!) works again.

If you see anything that looks broken, please let me know.

Thanks!