• Kimball SDC
• Trace File Source
• XMLify
• File Watcher
• Dynamic Data Flow
• Rank Transform
• Normaliser
• Twitter
• Compression

XMLify

Jamie find this useful when parsing error output from other SSIS steps, which often have several result sets with different columns.  XMLify dumps all of the data into a single XML file for easier error storage.

It’s currently only available for SQL 2005 on Codeplex, but Jamie’s updated it for 2008.  He expects to upload it to Codeplex shortly.

Kimball SDC

SSIS includes a Slowly Changing Dimensions wizard, but Jamie says it has extremely poor performance due to the amount of lookups it does, plus you can’t modify your work.  You have to reuse the wizard.  One workaround is the KimballSCD tool on Codeplex, which handles Type 1 and Type 2 dimensions.  These dimensions track the history of our data – for example, as our customers change from Single to Married to Divorced, we may want to capture those changes so we can view their status over time.

The KimballSCD component takes your incoming stream of data and dumps out separate streams with new records, changed records, deleted records, invalid input, and so forth.  It has a staggering array of configuration choices on how to handle errors.

Twitter Task

Built by Andy Leonard and Jessica Moss, this SSIS component can fetch and retrieve tweets.  Of course, when he went to demo Twitter.com, the page took forever to load.  Ah, the curse of using Twitter and WiFi networks for demos!

He demoed how to send tweets, then how to receive them and pipe the results through the Term Extraction task.  It’s basically data mining for text – it finds the most popular phrases in the stream of tweets.  Jamie asked if attendees found it interesting, and they definitely did.  A couple of attendees wanted to know when Twitter search integration would be included.  Doh!

Normaliser

This component takes a flat source like a list of orders that includes both header and detail info in the same row, and then normalizes it.  You have to pass in the data in the right sort number, because it uses those sorts to determine duplicates.  You pick which fields determine a header versus a detail record using checkboxes.

Rank Transform

The ranking T-SQL functions in SQL 2005 will take a set of data and add a rank column, and this SSIS component does the same thing.  Jamie says it’s still a little buggy, but he’ll work out the bugs in the next few days.  (Honesty is the best policy!)  The bugs are problems with the UI – checkboxes don’t show up as checked when they’re supposed to be.  The data under the covers works though.

It has nice options for rank, dense rank, row number, and row number by partition.  Rank and dense rank are two different ways to handle ties.

Compression Task

Pass in a file – but only one file at a time – and this task will gzip it.  It works for SQL 2005, and there’s a newer version coming on Codeplex to work in SQL Server 2008’s SSIS.  There’s an identical task to unzip.  I can see this as being useful for passing big XML files over networks.

File Watcher

This task watches a directory and waits for files to arrive.  When a file is created, it goes on to the next task in the package.  The File Watcher passes the name of the newly created file to the next task as well, so you can process that file.

Trace Reader

This takes a SQL Server trace file as a source, and then splits it out into three data outputs: short queries that took under 300ms, queries that took 300-1,000ms, and queries that took over a second.  Useful for DBAs who want to automate performance tuning on a large scale.

Dynamic Data Flow

The only non-free tool in the demo, Dynamic Data Flow is by a company called CozyRoc.  You can use one destination in SSIS, but populate different tables based on the contents of your data.

I bailed out just before the last task was shown because I had to get to my own session.

End of the Line

Jamie did an awesome job of demoing a lot of stuff in a short period of time without being overwhelming.  I can really appreciate how much work he had to put into these demos – every single component needed its own set of data.  Fantastic work.

Simon Sabin onstage at SQLBits

Simon likened it to getting walking directions.  If you ask for directions from Big Ben to the Millennium Wheel, your best option is walking.  But if you decide to go from Big Ben to the Eiffel Tower,walking is no longer the best option – because the statistics of the distance you’re trying to cover is suddenly different.  SQL Server’s statistics about the quantity and distribution of the data in your database help it determine how to build the best directions.

Scaling up to higher numbers of users also changes how queries perform.  If one query returns in 250 milliseconds, that doesn’t mean that 100 of those queries running simultaneously may not all finish in 250 milliseconds each.  He likened it to a grocery checkout line – the more people you put in line, the more the folks at the end of the line are going to have to wait.

A great example for parameter sniffing troubles is when the same query is run in both a stored procedure, and standalone.  It may perform poorly inside the stored proc, but when the DBA tries to troubleshoot it, it performs fine.  The two queries are using different execution plans.  Simon demoed how to compare the two plans, and how to find out why the bad plan isn’t optimal.

One way to get better plans is to get better statistics, and you have to do better than SQL Server’s default statistics.  SQL creates its own statistics for new tables, but they don’t work terribly well.  If you’re using table variables, those don’t get statistics at all, but on the plus side, they won’t cause recompiles when the data distributions change.  Temp tables, on the other hand, get statistics, but they incur recompiles when SQL Server sees that the temp table contents have changed dramatically and need statistics updates.

He demoed how SQL Server will create different query plans based on the exact text of the query.  If one query includes comments and another doesn’t, they can end up with different query plans and different performance.

In order to catch these problems, you have to monitor and baseline your application, then identify changes in reads, writes, and CPU time.  You don’t want to monitor duration, because you won’t catch things until it’s too late.

Physical vs Virtual Memory

Physical memory means the fast, volatile storage of the chips themselves.  We’re finding 64GB is very common these days.  If every app tried to access this memory directly, we’d run into performance problems quickly.  That’s why virtual address space came about.  On 32-bit OSs, the default settings leave 2GB for the kernel and 2GB for applications.  On 64-bit OSs, that breaks out to up to 8TB of kernel memory and 8TB of application (user mode) space.

All applications just work with virtual address space first – they don’t know whether they’re working with physical memory or the page file.  The allocation of memory to either chips or the page file is the job of the Virtual Memory Manager.

What Uses SQL’s Virtual Memory

The Buffer Pool is SQL’s main memory consumer, and that’s managed by the min and max memory settings in SQL Server.

Another consumer is VirtualAlloc – which just means anything that doesn’t use the buffer pool.  The heap manager, thread stacks, and multi-page allocations (things that need more than 1 page to store data).  For example, execution plans are normally quite small, but they can exceed one page, and we need to store them in memory.  Linked servers and extended stored procs also use VirtualAlloc to grab memory.

The buffer pool will never be bigger than physical memory.  If you’ve got 32GB of memory and a 64GB page file, your buffer pool won’t be bigger than 32GB.  On an x86 box, this can be problematic since you won’t have more than 2GB of user mode space by default.  Because there’s not much memory, SQL Server has to reserve some memory before the buffer pool grabs hold of the rest, and that’s called memtoleave.  It’s calculated with MemReserved + (NumThreads * StackSize).  By default, MemReserved is 256mb.  NumThreads is the max number of worker threads configured.  StackSize is .5mb on x86, 2mb on x64.  Christian had an excellent visual demo showing how the memory gets used up on different CPUs and memory amounts – I can’t begin to convey that here, but hey, that’s where his great memory chapter in the book comes in.

Christian demoed the RMLUtils to stress test SQL Server and affect the memtoleave space.  He asked how many attendees were using wait stats for performance analysis, and the number was about the same as my presentation’s attendees – around 10%.  Folks just aren’t using wait stats yet.  But if you turn around and ask how many of those 10% use it as their primary troubleshooting tool, it’s usually 100%.  Folks who use them, love them, as I’ve blogged before about wait stats.

SQL Server’s Memory Model

Memory nodes are the lowest level allocator, and you can view info about them in sys.dm_os_memory_nodes.

Memory clerks are the next level, and they’re used to access nodes.  When something in SQL wants memory, they ask the clerks, and the clerks allocate nodes.  There’s a general MEMORYCLERK_SQLGENERAL, and then heavy memory users get their own clerks, like MEMORY_CLERK_SQLBUFFERPOOL and MEMORYCLERK_SQLQUERYPLAN. You can check what they’re using with the DMV sys.dm_os_memory_clerks.

He talked about the different kinds of caches, and you can query them in sys.dm_os_memory_cache_counters.  He touched on how the plan cache has hard-coded maximum sizes based on your memory size – you don’t want plans running you out of buffer cache.  When you throw in the buffer pool and query memory too (for joins/hashes), it’s easy to see how SQL Server really needs all the memory it can possibly get.

Christian tied it all together by showing a slick DMV query that breaks out cache space usage by database.  I glanced through his blog but I couldn’t find it – I’ll hit him up for that later.

Best Practice: Lock Pages in Memory

Locking pages in memory makes sure SQL Server’s memory is not paged out to disk.  Otherwise, Windows may push it out to disk – often with bad drivers.  The first thing to do when running into SQL’s memory getting swapped out is to check for updated drivers, then tweak down SQL’s max memory setting, then use AWE/lock pages in memory, then consider upgrading to Windows 2008.  Even if you have bad drivers, Win 2008 won’t be as aggressive with trimming SQL Server’s address space.  Memory allocated using the AWE mechanisms can’t be trimmed.  Should you lock pages in memory on 64-bit systems?  Yes, because it locks pages in the working set.  Only the data cache is locked, though – other memory like the plan cache can still be trimmed.

You can lock pages in memory on Standard Edition, but you need to get on the right patch levels – see this knowledge base article for details.

Christian asked how many DBAs set the max server memory, and I was surprised that the vast majority of DBAs haven’t.  I’m a big cheerleader for setting that.  Glenn Berry recently wrote a set of guidelines for max server memory.  Christian recommended leaving 2GB for the OS, plus the memory for the worker threads we calculated earlier, plus 1GB for the multi-page allocations/linked servers/etc, plus 1-3GB for other applications (if necessary.)  This is a worst-case-scenario guideline because we just want to make sure our server isn’t paging to disk.

Bottom line – I learned a lot from this session, and this one alone was worth the price of admission!

Application and Multi-Server Management

To illustrate the problem with server sprawl, Jasper gave some quick stats about a big enterprise.  Microsoft IT has around 5,000 SQL Server instances with 100,000 databases, averaging CPU utilization under 10%.

To help solve this problem, SQL 2008 R2 introduced Data-Tier Applications (DAC packs) as a new unit of deployment for T-SQL apps.  They contain developer intent as policies – for example, the developers may want high availability, 2 CPUs and 2 gigs of memory.  Of course, this can present problems down the road – developers will always want more resources.  Jasper demoed the SQL Server Control Point.

SQL Server Connection Director & Connection Plans

Right now, SQL Server connection strings are tied to the server name and database name.  It’s not easy for DBAs to move heavily utilized databases to a more powerful server because we have to touch every client.  The problem is made worse because over time, we need to continuously consolidate databases together as older databases seem to stay on old SQL Server versions.  Take SQL Server 2000 – many of us are still managing a handful of 7 or 2000 servers because a handful of apps just won’t work on newer versions.  We need to combine these old things onto the minimum number of servers possible, but database moves are painful.

There’s help coming in the long term, but not in SQL Server 2008 R2.  The CTPs of SQL Server and Visual Studio for next year’s release contain a feature called the Connection Director, but Microsoft has already said Connection Director won’t be included in the RTM builds.  Bummer!

The new declarative connection plans will let us specify connection strings that lives in Active Directory or on a file share as shown in Books Online.  We can specify a cache refresh interval much like DNS time-to-live parameters – we would put a long cache refresh interval in the plan normally, and then turn that time down shorter when we plan to do maintenance.  Jasper demoed doing exactly this, changing the plan while running an app that regularly connects to SQL.

Me likey!  Can’t wait for that to be introduced.

Donald Farmer onstage at SQLbits

IT professionals still break into the same 3 roles that existed a century ago:

• Acquiring and storing data
• Validating the data quality
• Managing calculations on the data

Our roles, though, conflict with those of analysts – power users who want to do this work themselves.

Letting People Build Something Cool

Analysts do their slicing and dicing in our “approved” tools, but when they bang up against the ceilings of our capabilities, what do they do?  Universally, they export to Excel, and then start doing crazy things in this radically familiar environment.

To build the future of BI, Microsoft is delivering PowerPivot as an optional add-in for Excel 2010.  Excel 2010 installs side-by-side with older versions, so users can install it without violating corporate policies on using older Excels.  Farmer demoed PowerPivot with a >100 million row table using his laptop, sorting & dicing it in real time.  This demo still hasn’t gotten old for me yet – I love seeing that.  Since PowerPivot is a column-oriented data storage mechanism, it performs really well in memory.  It still takes a long time to get 100 million rows into PowerPivot (over the network, for example) but once it’s there, it flies.

PowerPivot data is read-only, though – once it’s been pulled in and compressed into column-based storage, it’s pretty much stuck.  If you need to make modifications, the easiest way is to use linked tables, and modify the linked ones.  Farmer demoed how to create relationships with those tables, and how PowerPivot helps detect which relationships don’t violate integrity rules.

To get that data into PowerPivot, one easy way is to use RSS. Business users can click the RSS icon on new SSRS reports, and Excel will open the feed to create a PowerPivot document.  From there, users can easily do their own slicing & dicing.

When we look at Excel spreadsheets from analysts, often the front couple of sheets are nicely formatted reports.  The rest of the sheets are various intermediate pivot tables and aggregates hitting other sheets of source tabs.  Excel’s new formulas in PowerPivot help you get those well-formatted aggregates faster with less intermediate steps.

I Made Something Cool! Now What?

Once users have built something cool in Excel, they didn’t have a solid way to share that with multiple users.  Farmer says SharePoint 2010 is like the ultimate file share for Excel because:

• IT knows the files are backed up
• IT knows who’s building the reports
• IT knows who’s accessing the files (which means we can tell which reports we may need to take over)
• People can consume the data without actually having PowerPivot or even Excel – it just works in the web

PowerPivot spreadsheets with slicers look fantastic in SharePoint.  I can envision people building their own report portals without BI team help.  There’s still a question of where the data’s coming from and how accurate it is, but that’s the case no matter how reports are delivered anyway.

Farmer says that this new self-service BI doesn’t interfere with real BI teams – you still need real BI in order to get analytics.  The self-service BI delivers the answers to the “background noise” of analytics requests – small, quick-hit requests that never end.  Farmer says self-service BI isn’t for the big projects, and the big tools aren’t right for self-servie needs either.

To try out PowerPivot, hit:

• http://www.powerpivot.com
• http://www.powerpivotpro.com
• http://www.powerpivotgeek.com
• http://twitter.com/powerpivot
• http://twitter.com/powerpivotpro

Right.  Because I’ve been really looking for a netbook with even less capabilities than my iPhone.

I’m also submitting sessions for the upcoming SQLSaturday in Chicago next April, and you can join me!  It’ll be on April 17th, but the planning starts now, and we need your help to make the event successful.  SQLSaturdays are completely free events for the attendees, and speaking there is a great way to give back to the community.

You’ll also be polishing your presentation skills, which pays off when you’re up against tons of other speakers for coveted PASS Summit sessions.  Remember, if you get sessions approved for the Summit, you get a free registration!  Think of your SQLSaturday sessions as a down payment towards your Summit skills.

Enter your session abstract today, and as a thank-you, I’ll give each SQLSaturday Chicago speaker a personal thank-you gift at the event.  Don’t get too excited though – it won’t be a free TabletPC like those PDC attendees got (I’m not bitter.)

Update: I’ve gotten a couple of emails asking if I’d fly out to local user group meetings to speak.  I would love to, but the reality is that I have to focus on reaching the most number of people possible in relation to my travel time.  When I fly out to speak at a user group, I spend several hours in flight back & forth, plus I often have to overnight at a hotel because the user group meeting takes place after the last flight out.  All of that costs money and time, and frankly, doing that for a 10-30 person group for 1 hour is pretty painful.  I already travel too much (especially if you ask my wonderful girlfriend who already puts up with too much).  I’m completely open to doing remote presentations, but if I have to travel, I need to combine several events, or reach a lot more than 10-30 people.  Just like your company is asking you to do more with less, Quest is asking us to figure out how to reach more people with less, and usually that means virtual conferences or focusing on big (several-hundred-people) meetings.

Thursday – the entire Rome sales force is out sick. There’s a flu going around, so my staff training sessions turn into a day of me catching up on work.  Friday, two salespeople show up.  It’s an improvement over the day before, so I’ll take it, and I take revenge by having fun walking around Rome on Saturday.

Sunday – British Airways loses one of my bags. Only one of my two bags made it to Stockholm.  Being a DBA on a two-week trip, I’d planned ahead and packed two bags with equally divided gear.  Redundancy, redundancy, redundancy.  I didn’t pack more than I needed, mind you, I just striped my clothing, as it were.  I didn’t stress out, but it didn’t make things easier.

Sunday – I arrive in Stockholm in the rain. I can’t find a taxi, so I take the train into town.  From the train station, I try to catch a cab to my hotel, only to be told by the cabbies that it’s just around the corner, and they won’t take me – I have to walk instead.  I walk two blocks in the rain, try to check in, and it’s not the right Radisson.  I walk back to the cab stand, point angrily at the map, and we come to an agreement.

Monday lunchtime – our Stockholm lunch & learn seminar draws 0 attendees. The salespeople frantically call customers to find out what’s going on, and turns out they all either had production emergencies or they just didn’t want to leave the office.  I spend a couple of hours training the sales staff instead.

Monday evening – my beloved Macbook Pro won’t boot.  It turns on, and then displays a no-smoking style circle with a slash through it.  I’m not worried about my data, because I’ve got a second internal hard drive with Time Machine, but I can’t read that drive without another Mac.  Since nobody’s got them in the Copenhagen team, I briefly consider buying a second Macbook when I get to the London airport, but European prices for Apple gear are outrageous.  I’ve got a two-week-old backup of all my decks on a USB flash drive with me, but I’d tweaked them for SQLbits.  Oh, and yeah, I’m supposed to present Tuesday morning in Copenhagen.

Tuesday morning – I present with a borrowed laptop using my USB backups.  It goes smashingly – lots of attendees, lots of good questions, and lots of thumbs-up.  Whew.  One success.  I’d love to walk around through Copenhagen snapping pictures, but I have a tight timeline now if I’m gonna get my decks updated for SQLBits on Friday.

Bork Bork

Tuesday afternoon – I buy a Swedish netbook, a Lenovo S10-2 with Windows XP, because it’s the only sub-$1,000 one I can find in stock after visiting three computer stores, and I’m running out of time.  Upon powering it on, I discover that it’s not the multi-language version of XP – it’s just all Chef, all the time.  I can almost deal with it, but even the built-in Office is Swedish, and I keep running into slide problems with it.  I work until midnight getting it reinstalled with an English build of Windows and Office using an MSDN account, a USB key, a paper clip and two socks.

Wednesday morning – I’m scheduled to leave Copenhagen at 8am, but of course that doesn’t go right either.  British Airways tells us we have to delay boarding for 20 minutes because there’s high winds in London, and there’s no sense in us getting onto a plane that can’t leave the airport.  20 minutes later, they board us onto the plane and pull away from the jetway.  They proceed to inform us that yes, there’s still high winds in London, and no, we still can’t depart yet, but they stuffed us onto the plane anyway.  We taxi to a remote area of the airport and they shut the engines down for an hour, because they figure as long as their employees are unhappy, we should be unhappy too.  I’m quite okay with the delay, because my netbook’s got a full battery, so I continue knocking out my presentations.

Wednesday – news breaks that PDC09 attendees (the conference I really wanted to see this week) all get free tablet PC laptops with multitouch, Office 2010, and more.  I contrast that with my Swedish netbook that cost a minor fortune, and I think I just might have gotten slapped in the face with the short end of the stick.

Wednesday afternoon – I check into the Quest corporate house. We’ve got an actual house in Maidenhead, a London suburb, because so many folks breeze through here.  I really like the place, although it’s about a mile’s walk from food, and boy do I need a drink.  I walk to a pub, down a big Guinness and a burger, then get chastised when I try to pay with a credit card.  The waitress angrily informs me that nobody’s allowed to pay less than ten pounds with a credit card, so she rounds the bill up.  She then proceeds to hand me the 3+ pounds in cash, something an American would never do.  I happily hand it back to her and tell her to keep it, and it immediately brightens her day.  Hopefully she won’t be as cranky on my next visit.  I go back to the Quest house and throw a load of laundry into the washer/dryer, something really handy when you’re on the road for two weeks.  Ideally, I’d be in a hotel with dry cleaning and restaurants that do room charges, but I don’t mind sucking it up now and then.

Wednesday evening – I can’t pay with a credit card, but I can’t find cash anywhere. I’m somewhat hungry, but not enough to eat ten pounds worth of food.  Knowing now about the credit card minimums, I ask around at a couple of nearby restaurants, and none will take credit cards for less than ten or twenty pounds.  They point me to a cash machine half a mile away, but upon my arrival, it’s out of commission.  The shop owner doesn’t know of another nearby one.  I try ordering pizza, but the systems wig out when my delivery address doesn’t match my billing address, plus the billing address is in America.  I give in and just get a pizza to eat in the restaurant, then take the leftovers home for breakfast.  My status as a foodie is diminished.

Wednesday 11pm – I get a surprise visitor. I’m working on a presentation alone, in the dark, when I hear someone fumbling at the front door.  Thankfully, I’m not working in my undies, because in walks another Questie who’s just as surprised as I am that he’s not alone.  We end up talking into the wee hours of the night about IT, but man, that could have been ugly.  He’s thankful for my leftover pizza.

Thursday morning – I check my clothes in the “dryer” and they’re soaking wet. The dryer’s drain is plugged, so my clothes just rolled around in a pool of water for an hour.  I hang my clothes out on a line, knowing full well they won’t dry before Iain comes to pick me up in a couple of hours.  I’ll be dry cleaning those the instant I get to the hotel for SQLbits.

Travel isn’t all unicorns, rainbows, and meals at WD50.  Part of me really wants to extend my trip by one day to attend the ComputerWeekly blog awards in London next week, where I’m in the running for Best Individual IT Male (hint: vote today! and often!), but the rest of me can’t wait to get back home to the States.  I love Europe, but I’d rather live here, not travel through it.  Traveling anywhere, not just Europe, is a royal pain when things don’t go as planned.

If you see me presenting at SQLbits this weekend and I seem even more relaxed than normal, it’s because at this point, I’m at one with the chaos.  Bring it on.  If my projector bursts into flames and my audience consists of hecklers wearing “I Love Microsoft Access” shirts, I’m still going to be grinning.

How to Lose Weight

Get your pencils ready, because I’m about to give you the ultimate weight loss tip.  It’s going to sound almost too good to be true because it’s so darned easy, and here’s the craziest part – it can actually save you money!  That’s right – this is the tip that the exercise equipment industry, the personal trainer industry, and the vitamin industry are desperately trying to keep under wraps.  You ready?  Here it comes.  Don’t blink – you might miss it.

Eat less.  A lot less.

I know – it sounds ridiculous, but that wild technique helped me drop 40 pounds in under a year.  I didn’t exercise one bit, either.

I know what you’re thinking – you’re thinking, “But Brent, how can I possibly lose weight without spending money?  Don’t I have to spend a fortune on the FlabBlaster 3000 just like Chuck Norris tells me to?”  Far be it from me to disagree with Chuck – very far – but…

When The Regular Thighmaster Isn't Working

Buying Hardware Doesn’t Fix Bad Practices

No matter how much you spend on exercise equipment or systems management, you and your servers aren’t going to get healthier when the stuff sits on the shelf.

Money can’t buy you health.

It can pay for experts to come in and fix you when you’re sick, but it can’t keep you healthy – that part is up to you.  The very first step to getting healthier, and this goes for both your servers and your thighs, is to change your habits.  Elbow grease has amazing results when applied liberally.

Start Testing Your Restores. Now.

If you don’t have a spare server lying around, use somebody’s spare desktop.  We all have ancient machines sitting in closets from our last upgrade or that employee who just got fired because he couldn’t restore a dropped table.  (Get it? That’s a hint.)  Get that machine, and throw in a 1.5 terabyte drive for about $100.  Yes, use your own money if the company won’t pay, because this is an investment in your career.  If you’ve got several machines lying around, consider combining their memory if possible, but don’t sweat it – this is only your training wheels system.

Install the OS again from scratch, and put SQL Server Developer Edition on there or the 180-day evaluation version of SQL Server 2005 or 2008.  Don’t overcomplicate your life by trying to get every best practice ideally perfect – even if everything’s installed on a 1.5tb C drive, this system will still work for the basics.

Start by testing your restores once per week.  The first few times you do it, don’t try to script the whole thing out – just use SQL Server Management Studio and point/click your way through it.  Remember, high bang, low buck/effort: we want this whole thing to take less than two hours per week of your time, max. The restores aren’t going to be fast, but the point is to even figure out if we can do them, period.  After a couple of weeks, you’ll start scripting your work as you find more and more things that aren’t included in your test system – logins, DTS packages, jobs, whatever.  Document what you’re doing along the way, because every time you find something else that has to be done to make the server work, that’s one less lesson you’d need to learn under the gun.

One Month Later: Add the Apps

After you’re comfortable restoring the database, try to configure your application.  Install IIS, DLLs, code, whatever else you might need to get the app to run.  If you don’t manage the app, ask the app guys to take another old desktop and try to do their part to set up a restore testbed for themselves.  If they don’t want to, that’s okay – but now you’re starting to build up some cover for your rear end.

Some things might not work in your environment.  For example, if you’re using the evil xp_cmdshell, your developers may have hard-coded paths and files into their code.  The faster you find things like this, the faster you can get them fixed before disaster strikes.  When disaster strikes, these problems won’t be seen as developer mistakes – you’ll get blamed, because you can’t make the server work the way it used to.  99% of your problems won’t stem from hardware that you can buy with a check, though – they’ll stem from practices.  Stop waiting for the company to buy you a Thighmaster, and start doing pushups.  It’s better than nothing, and when disaster strikes, the last thing you want to have is nothing.

When there’s enough basic plumbing in place that you think everything works, format the box and start over.  Use your documentation and try to repeat the whole process.  The first several times you do this, you’re going to continue to find more errors and gotchas.

When you think your documentation is complete, format the box and hand the documentation to your junior person or your manager.  Say, “I’ve got a set of steps to follow when disaster strikes, and I want you to test them for me, because if I’m not around then you’ll be the one doing it.”  They will be shocked, but down the road they’ll appreciate your due diligence.

This kind of disciplined effort is why experienced DBAs walk around with an air of confidence.  The best DBAs aren’t worried about what happens when disaster strikes, because they’ve already practiced it again and again and again.  When I was a DBA, I liked to say that disaster struck every week for me – it just struck in my lab.

My Book Cover Beats Your Book Cover

When WROX decided to dump the author photos on the book covers, I was bummed out.  One of the big selling points of a book is to have your name on it, to point to it in the bookstore and say, “I made that.”  Having the authors on the cover was even cooler, especially in a platform like SQL Server where the personalities are so identifiable.  The new book covers don’t seem to have anything to do with the content, and they don’t have any personality.  They scream “cheap stock photo.”

But when I found out ours had a race car on the front, I cheered.  No, cars don’t have anything to do with internals or troubleshooting – but it’s a car, and it’s going fast!  Woohoo!  I can’t wait to say, “Look for my book in the SQL Server section.  It’s the one with a race car on the front.”  Shazam!

Here’s a postcard Wiley/WROX is using to promote the book at SQLBits next weekend:

Promotional Postcard

I’ve read the other authors’ chapters, and I can tell you with complete authority that I am by far the dumbest guy involved with this book – not just the brilliant authors on the cover, either, but also including two other folks involved, Cindy Gross and Jonathan Kehayias.  I am completely riding on their coattails.

Before I did this, I glanced at books and just figured that any author on the cover knew everything that was in the book.  For example, when I saw my buddy Jean-Claude Armand on the cover of Professional SQL Server 2005 Administration, I just assumed he was a genius who knew everything in that book cover to cover.  While JC is indeed a genius, don’t assume that I knew everything in our book’s chapters – on the contrary, I’ve learned a *lot* in every single chapter I’ve had the privilege to read.  Christian Bolton’s chapter on memory alone was worth the entire book, because the Quest LiteSpeed support folks are always asking me questions about MemToLeave and my answers suck.  Now, my answers suck much less.

I’m starting to see what authors mean when they say the writing process doesn’t seem as bad when it starts to fade into the rearview mirror.  Now that the book’s coming closer to press time, I’m feeling more and more psyched about it.  My junk still hurts, but there’s a very small part of my brain that’s starting to think about how to make it easier next time.

The book’s scheduled to be available in late December or early January, and you can preorder it now from Amazon for around $38.