5 worst days in a DBA's life

Episode 1 Black Day at I/O Gulch

Share on facebook Share on twitter Share on email Share on print More Sharing Services 62

by Tony Davis Not long ago, a crack team of SQL Server experts was flamed for a crime against database normalization they didn't commit. These men promptly escaped from the taunts of academic relational theorists. Today, they survive as website editors and software evangelists. If you have a problem, if no one else can help, and if you can find them, maybe you can hire the DBA Team.

Scene 1: A bad morning starts with phone calls

There were ripples in my coffee cup. My hand was shaking. Why? Because I could hear the phone ringing from the end of the corridor as I arrived for work. Ten minutes later, there was enough of a pause to take a tepid sip. The users were revolting. Application slow. Application stalled. Report running slow. "Is the server down?" they asked. Had I, Joe DeeBeeAy, changed something on the server without telling them? No, the server was not down, and no, as far as I knew, nothing had changed, though sometimes it felt like I was the last to know about any changes. In among it all was that strange call from HQ telling me the CTO was on his way over to find out what was going on.
The CTO visiting me was a rare and serious event. I had to act fast, but could my brick of a laptop get any slower? Task Manager said CPU was higher than normal. PerfMon had reported high disk I/O latency. This was crazy. What did it all add up to, and what was causing it? High disk I/O latency…is it a SAN problem?
On cue, the CTO stormed in, apparently looking for a fight.
"What the heck is going on in here today? Are you running maintenance jobs outside the window again?"
"No, I swear. I think it's a SAN problem, but…"
He sighed wearily. "Is there a DBA who doesn't always say that? Maybe you accidentally dropped an index?" He swatted away my plea of innocence. "So what are you doing to fix it?"
I thought quickly. "I'll run a Profiler trace…"
"Are you nuts? One minute you're telling me about high disk I/O, next you want to add to it. Are you trying to bring the whole system to its knees?"
Sweating, wrong-footed, I stood blinking at the CTO. His take-no-prisoners attitude was well known, but this was bullish even for him, and since when did he take to wearing a baseball cap?
Suddenly, the CTO broke into a broad grin. He removed his glasses. Took off his cap. Ripped off prosthetic skin and fake moustache.
Stunned, silent, I stood, blinking dumbly at none other than the renowned SQL Server expert, Jonathan Kehayias.
"Jonathan? What the…?"
"I worry about you, Joe. You may fold under questioning. I hear you're in the frame for heinous crimes against a database server."
"I'm innocent. Minor point, I know."
"I think I can help you prove it, but not alone. We have to get the DBA Team back together."
"The DBA Team? I've heard about them, a crack team of SQL Server administrators forced into hiding after being framed for a crime against database normalization they didn't commit. I assumed that was a myth"
"No, they are real all right and we need their help. But first we need to find them…."

Scene 2: Grant becomes scary once more

We parked my sedan near a forest.
"Serious heat is coming your way from HQ," explained Jonathan, as we strode into the trees. "The developers are whispering in the ear of Eric 'Bloodaxe' Pedersen, one of the business managers. He's telling anyone that will listen that your relational database is the only thing still hindering the progress of the new agile system. It sounds like you're the fall guy."
Out of breath, I trailed Jonathan into a forest glade. Birds were singing, scouts were running around happily, tying knots, putting up tents and lighting fires. A voice boomed out above the general hubbub.
"Hey scouts, great progress; we should have the moose cooked before sundown!"
"Grant," shouted Jonathan "Long time. We've got a DBA here needs your help pretty bad…"
I blinked in surprise. "This…this is Grant Fritchey…the…Scary DBA?"
Grant bore down on us. "No, no! I've put that behind me now. I'm teaching leadership skills to scouts, and I have at last found peace and harmony. I'm no longer the Scary DBA people once knew."
"But this is a real emergency, Grant!" said Jonathan. “The developers are saying there is nothing wrong with the application and that it's the database at fault!"
"No! Peace and harmony!" he shouted, clutching his temples and beginning to color up, "Don't…make… me…angry!"
"…did I mention that they're using the ORM to fix the object-relational impedance mismatch?"
Grant seemed to grow visibly taller, angrier. His fists clenched. "What? It's…it's….clobberin' time!!!"
Birds scattered. The scouts looked up in amazement as we rushed off, back to the car.

Scene 3: Steve hits the trail

30 minutes later, the old sedan was bumping down a dirt track towards an old ranch building. Dust blew as we pulled up outside. A grizzled cowboy in chaps finished tying up the reins of his horse and eyed us nonchalantly. "Jonathan, Grant…long time…what brings you and your friend all the way out west?"
"Hey Steve, this is Joe DeeBeeAy. He needs our help. We need to get the DBA Team back together."
Steve fiddled with the horse's reins. "I don't do that stuff anymore, Jonathan, you know that. I've met me a pretty girl, settled down, got me a farm, kids, dogs, horses….alpacas, sheep…"
"Our main database is dog slow." I pleaded, "I don't know what's causing the problems, but every database call is taking ages. I need your help."
"… pigs, cats, Prius." Steve continued, distracted, before suddenly staring me straight in the eye. "Hey, stranger, that database might be your problem but it's not mine. I've put my past behind me. I've been falsely accused of a database crime for the last time."
"Other DBAs are suffering the same fate, Steve," said Jonathan. “Joe is innocent, but being blamed for the slow database. Bloodaxe Pedersen is telling the CIO how much money they could save by replacing the relational database with a NoSQL database, and Joe with a new coffee machine."
Steve spat in the dust, wheeled around and strode into the ranch. We sat silent, defeated.
Suddenly, he emerged through the billowing dust, sporting a Hawaiian shirt of shocking garishness.
"Maybe a DBA's gotta do what a DBA's gotta do. Lead the way, pardners!"

Scene 4: The hunt begins

Back in my cubicle, we crowded around my laptop. Jonathan got straight down to business, "So, Joe, what have you got for us?"
I told him what I knew. Physical Disk\Avg. Disk Reads/sec and Physical Disk\Avg. Disk Writes/sec were showing latency values over 50ms. I thought it could be a SAN issue.

"What else do you know? Have you checked the wait statistics?"

Part of the SQL DBA Bundle

Monitor wait stats without lifting a finger, with the SQL DBA Bundle.

Grant paced the room, a frown like thunder. "What else do you know? Have you checked the wait statistics?" He eyed Steve meaningfully. "Run the sys.dm_os_wait_stats query."
The laptop spewed the results reluctantly onto the screen.
"See here, pardners, that's PAGEIOLATCH_SH waits, sitting pretty at the head of the line. That's delays in obtaining a latch for a buffer page because of delays getting the page from disk…"
"I told you it smelled like a disk problem!" I cried triumphantly, already heading for the exit.
"Not so fast youngster! Maybe it's the disk that's at fault and maybe it's not…"
Sure, and maybe it was an albino gator in the basement, but right now, I only had a few hours before Bloodaxe Pedersen started taking the air-pressure out of my career, and I was off for a fistfight with some SAN admins. Dammit, how long had I been telling them that we needed to move this server over to SSDs?

Interlude: Grant Fritchey on wait stats

Scene 5: A setback

20 minutes later, I was back, flopped sulkily into my chair, holding a bloody handkerchief to my nose.
"I see the SAN admins agreed with your diagnosis," smiled Steve.
"Thath not thunny. They say there's nothing wrong with the performance of the SAN. They've run tests, the IOPS is right on spec. They asked if I'd tried adding an index. I lost my cool. Big mishtake, but that index barb always cuts deep."
"Joe, pardner, sometimes you need to learn the hard way. PAGEIOLATCH_SH waits may be top of the list, but we need to know the 'normal' wait profile for this server. Don't suppose you have baselines for the wait statistics?"
I looked back at him, as blank as a starting pistol in a shoot-out. "No, I figured not," he said, shaking his head.
"Steve's right, and even if disk I/O is the bottleneck - meaning the disk subsystem can't return pages fast enough, so sessions are waiting for latches - that doesn't mean disk I/O is the problem."
I stood blinking dumbly at Jonathan. This was becoming a habit today.
Grant was looking more menacing by the second. "Jonathan's right. Think more laterally, Joe. I've seen those PAGEIOLATCH waits on 100 servers. Maybe on 10 of them the problem is the disk. The other 90 it's something else. Sometimes, it's a memory problem. If the buffer pool is experiencing memory pressure, you'll see cache churn and a lot of disk I/O. Most times, though, it's a rogue application, a query gone bad, doing huge range scans where seeks would do the job."
"Back in Colorado we sing it like this 'Oh, give me a home where the range scans all roam, then the disk I/O metrics will play…'," crooned Steve.
I slapped my forehead, "You know that CRM application the devs are so proud of? They modify it so often I've stopped trying to keep up, but last week's deployment sounded different. I heard talk around the water cooler about finally matching their object persistence layer to a more flexible database structure. I think I even heard terms like 'EAV' and 'fact table'."
Grant's temples seemed to pulsate visibly…"Object persistence layer! No! Don't…make…me…angry…"
Even Jonathan flinched at the EAV part. "Grant, calm down! Joe, you're bleeding all over your expenses form! Steve, grab the laptop! It's time to get us some inspiration…"

Scene 6: Coffee and doughnuts

We sat huddled round the canteen table, clutching mugs of coffee. Jonathan munched ruminatively on a jam doughnut. "I've heard about this CRM team. Young, very agile, up and coming. They have some…challenging…ideas about how to write data applications, but they're the new golden boys with HQ. We'll need some cast-iron proof before we start lobbing grenades in their direction."
"Well then, we better arm ourselves properly before we go picking any more fights," said Steve. “These problems are happening now, right? So let's look at sys.dm_os_waiting_tasks. This query will show us all the currently blocked and blocking sessions and the associated statements…"

					
SELECT  blocking.session_id AS blocking_session_id ,
	blocked.session_id AS blocked_session_id ,
	waitstats.wait_type AS blocking_resource ,
	waitstats.wait_duration_ms ,
	waitstats.resource_description ,
	blocked_cache.text AS blocked_text ,
	blocking_cache.text AS blocking_text
FROM    sys.dm_exec_connections AS blocking
	INNER JOIN sys.dm_exec_requests blocked
		ON blocking.session_id = blocked.blocking_session_id
	CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle)
						blocked_cache
	CROSS APPLY sys.dm_exec_sql_text(blocking.most_recent_sql_handle)
						blocking_cache
	INNER JOIN sys.dm_os_waiting_tasks waitstats
		ON waitstats.session_id = blocked.session_id
					
				

"We can follow the blocking chain. That'll show us which request is at the head and is causing all the locking waits."
I winced, "That's one nasty looking request that's causing the blocking."
Steve pointed at the screen, "The resource_description tells us the ID of the page that is the source of blocking contention so we can find out which table it belongs to." Quick as a flash, he was bashing out a DBCC PAGE query.
"That's a table in the CRM database!" I cried, jumping to my feet. "We've got 'em!"
"It's clobbering time!"

"We better arm ourselves properly before we go picking any more fights"

Part of the SQL DBA Bundle

Know exactly which queries are using the most resources, with the SQL DBA Bundle.

"Whoah, people, we still haven't proved that this is what's causing the disk I/O bottleneck!" Steve shouted, “We've got to be sure. If the app is as wacky as you fear, it could be a heck of a tuning effort…and that means a lot of time and money. You think Bloodaxe Pedersen will take this lying down?"
"Eric Pedersen doesn't take much lying down."
Who said that?!
We looked up to find ourselves face to face with the man himself.

Scene 7: Canteen showdown

Eric towered over the crowded table, chuckling quietly. "Well, well, how many DBAs does it take to change an index? More even than I realized. No wonder you're such a drain on our IT budget…"
"Even you must be tired of that index jibe by now, Eric," I growled “But you won't be laughing when you see what we found out about your beloved new CRM app…"
"Whenever I need a laugh, Joe, I just remember your outdated ideas about what kind of database the modern business needs. Dave here has told me all about how, yet again, it's a roadblock to their new CRM development work."
Developer Dave emerged from behind Bloodaxe, smiling, "Sure is! All we really need is a simple data repository to serialize our object data."
I looked nervously over at Grant, wanting to avoid further bloodshed, but fortunately he was distracted, staring intently at some execution plans on my laptop.
"…anyway, it's all in the latest report to HQ," Dave continued, oblivious to the danger. “Enjoy your little party while you can, but when they read about how you let us down, I'm guessing they'll be mad. We're just off to tell them about it now. And don't forget - a good NoSQL data repository don't need no administrator."
With that, Developer Dave picked up a doughnut from the plate, crushed it and stalked away, jam dripping through his fingers like blood.
"Geez, are all developers such drama queens?"
"Takes one to spot one, Grant," said Jonathan.
"Hey! Don't…make…me…. ANGRY!"
"Pardners, did you not hear what Bloodaxe said?" bellowed Steve “They're heading to HQ now! We've got to beat them to it!"

Scene 8: The chase

We dashed out into the car park and crowded into my sedan.
"But we haven't even finished the analysis," I cried, breathless. "We don't have proof."
"Listen, pardners, name any DBA task…denying table access to developers, killing developers' connections…I've done it at high speed on the back of a horse. A bit of data analysis in the back of a sedan will be a piece of cake."
We pulled onto the highway, tires smoking. I glanced in the rearview mirror. A Jeep was on our tail.
In the back seat, Jonathan tapped away frantically. "The last piece we need to examine is the virtual file statistics. It shows us the disk read and write patterns, how the I/O load is distributed across files and databases on the SQL Server instances. It'll help us nail the files and objects that are eating all our I/O."
I swung a sudden left, tires screeching, mounted the sidewalk and sent a pile of old cardboard boxes scattering.
"WHOAH!" screamed Grant, "Will you look at the size of the I/O stalls on this object!"
Jonathan scanned through the results, "It's associated with…the CRM database! It all correlates! We have the proof we need!"
Grant grabbed the laptop and frantically bashed away at the keyboard. "OK then, just one more thing we need to do."
Jonathan looked nervously through the rear window. "Step on it, Joe! Eric and Dave are right behind us!"
"My foot's on the floor! We're almost there!" I cried.
"It's no good, they're going to overtake!"
We screeched into HQ car park, neck-and-neck with their Jeep.
"All done," shouted Grant. "It's clobbering time!"
With that, he threw the laptop out into the Jeep's path. The Jeep hit it, flew into the air and barrel-rolled, before crashing to the ground upside down and exploding.
We jumped out of our car, looking back in disbelief as Eric and Dave crawled out of the wreckage, dazed but otherwise completely unscathed. We turned and rushed into the building just ahead of them.

Scene 9: HQ

We burst into the office, shouting at each other and gesticulating wildly.
"Phil!" Eric called out, spotting the CIO in the corner. He looked remarkably unfazed. White hair, wild beard, quietly nursing a cup of tea. He had the look of a wise old bird who'd been in the IT trade a good while, and was no longer shocked easily.
"So sorry about all this fuss," said Eric. "But I knew you'd want to discuss our latest report on the CRM project as soon as possible. The application is functioning perfectly, but our antiquated relational database model simply can't adapt to our changing business requirements. We're losing revenue hand over fist as a result. And the best our DBA can do," he gestured toward me, dismissively, "is request an expensive SAN upgrade. Again."
Phil raised a questioning eyebrow in my direction.
"It's true, I did, but I was wrong about that."
The CIO refilled his cup, serenely, and signaled for me to proceed.
"With the help of Jonathan and the DBA Team, I've learned a lot this morning about wait statistics, and about how massive range scans from the new CRM app are bringing the IO subsystem to its knees. All the data is on my laptop, right here...Ah." I made a futile grab for the laptop. Gone, of course, leaving only the hernia from lugging it about.

"A good DBA always backs up his data. You know that, Dave."

Part of the SQL DBA Bundle

Quickly and securely copy your backups to the cloud, with the SQL DBA Bundle.

"And what data would that be then?" cried Dave, triumphant.
Grant looked scary. "A good DBA always backs up his data. You know that, Dave. Even during a life-threatening, high-speed car chase. I uploaded it all to the Windows Azure cloud, via Cerebrata, just before I…err…accidentally dropped the laptop into the path of your Jeep."
"And it proves that the CRM application is the culprit. Not the disks and not the database," I smiled.
"Sure, we could probably find an index or two that would help, but the problems run deeper than that," explained Grant. "For a start, I noticed from the execution plans that we have implicit conversion problems that are forcing table scans for every query the CRM app makes against a VARCHAR column, due to the ORM parameterizing it as NVARCHAR."
Suddenly, Phil spoke up.
"It seems to me that we have a lot of reasons for the DBA and development teams to start working together more closely. Eric, Dave, I'm sure you can sit down with Joe, explain the business domain fully, what it means to your applications in terms of the data model. Joe, you've learned a lot recently, and I'm sure, with a bit of help from the DBA Team, you can find a way of doing what they want to do without cramping their style too much."
Grant smiled sweetly. "Of course! They can even use that ORM too, but I'd say you'd need a bigger server for that."
As we left the office, I was surprised see how warmly Phil shook Jonathan's hand. The final piece of this puzzle seemed to slip into place. Had our CIO hired Jonathan, and with him the DBA Team, setting this crazy day in motion?
Seeing my searching look, Phil confessed, "Jonathan and I met many years ago. I knew he could help with this sort of problem. Besides, it was about time the DBA Team got back together. Who knows when we might need them again…?"
I shook my head, smiling.
"Oh, and don't forget to order yourself a new laptop, Joe," said Phil, as he closed the door. "Plus maybe a monitoring tool to help with future analysis. Collecting and maintaining all that baseline data manually is a big job for a solo DBA."
Jonathan put his arm round me and grinned.
"I love it when a plan comes together!"

Episode 2 The Girl with the Backup Tattoo

Share on facebook Share on twitter Share on email Share on print More Sharing Services 25

by Phil Factor Not long ago, a crack team of SQL Server experts was flamed for a crime against database normalization they didn't commit. These men promptly escaped from the taunts of academic relational theorists. Today, they survive as website editors and software evangelists. If you have a problem, if no one else can help, and if you can find them, maybe you can hire the DBA Team.

---

"Our rivals are going to be sweating this weekend." Bruno laughed mirthlessly from the shade of the canopy of the café and had a sip of the wine.
"It's sure hot, boss."
"It's not the heat that will make them sweat, but the nasty surprise I have planned, Gabriel." Bruno put down his drink and glanced across the square at the headquarters of Acme Logistics. "Yeah, I don't think they'll survive this. I'm going to own the only Logistics company in town."

Scene 1: Piña Colada in the server room

"Piña Colada in the disk drives?" Manuel, the CIO of Acme Logistics, stared out of the server room window across the town square, shaking his head in disbelief. How could a DBA do such a thing? It was breaking a taboo.
Developer João looked up while scraping together the broken bottles on the floor. "He was working late on Friday, as normal, but then he seemed to flip, destroyed the server and vanished."
Manuel surveyed the desolation around him. On the whiteboard was scrawled the words ‘Maria is a witch!' The server had been pulled out of the 19in. rack and lay in bits on the floor. When love dies, Manuel mused, it turns to anger, a terrible anger that lashes out in all directions. Still…it didn't quite make sense.
"Well, we've got the rest of the weekend to clear up the server room, and put the database back together in time to begin trading on Monday."
"Can we start by cleaning the whiteboard?" muttered Maria, one of the developers, pointing at Raul's parting message.
"Raul is in serious trouble, don't worry about that, but without him we're going to have a struggle to put things back together in the time we have. Where is he?"
"I phoned his home but his mother says he's gone abroad. Don't worry, we can restore the database. We have the backups," said João, reassuringly, but his confidence didn't ring true.

Scene 2: Running to the wrong base

Two hours later, a new server sat in the rack, flashing red lights. It felt like progress but the developers looked wretched.
Nervous foot shuffling and coughing greeted Manuel's forced, optimistic "So…how's it going?"
"It's hard to know. We tried a restore but there seems to be something wrong with the latest full backup," sighed Maria "The restore failed with a pretty scary error message…"

"The restore failed with a pretty scary error message…"

Part of the SQL DBA Bundle

Verify your backups automatically with theSQL DBA Bundle.

Manuel bowed his head. "We need a good DBA, in a hurry."
"With the problems facing us, I think we need the whole DBA Team" smirked João "You know…if you have a problem…if no one else can help…and if you can find them…maybe you can hire… "
Manuel waved his hand dismissively "The DBA Team are a myth…and anyway, I think we could do without adding silly disguises and exploding jeeps to our list of woes."
"No, no, they are real all right, but forced into hiding accused of a crime against database normalization they didn't commit!"
"Stop being a drama queen, João," interjected Maria, briskly. "A more sensible idea would be to get a good consultant like Robyn Page to help us sort this out. I happen to be in contact with her mentor, Phil Factor…"
"They're not real either!" Manuel blurted, in exasperation "Phil Factor is clearly a made-up name and Robyn Page…well, no-one that glamorous could really be in IT…"
"Just because of how she looks you assume she can't also be a talented DBA?" Maria fumed, glaring around the room menacingly. "No wonder half the human race avoids going into a profession stuffed with post-pubertal, unreconstructed misogynists with personal-hygiene problems!"
"Err quite. Yes, yes, slip of the tongue, I meant… oh never mind. I'll contact Phil right away and see if he can set up an urgent visit from Robyn…"

Scene 3: I spy

Bruno put down his wine glass and languidly answered his phone.
"Raul….how are our friends coping over at Acme?"
"Floundering like kippers, boss, as we predicted…"
Bruno laughed heartily. Hacking into the security IP camera that Raul himself installed in the server room was a masterstroke. Now they had a live video feed into their misery.
"…. but they are talking about getting in help from some SQL Server dame called Robyn Page…"
Bruno stopped laughing, and chewed his cigar menacingly. "Doesn't sound likely, Raul, but we ain't going to let it happen anyway."
With that Bruno leapt to his feet and made his way to his jeep, "Gabriel, keep an eye out for anyone approaching Acme's IT Building."

Scene 4: Baby sitting

Robyn picked up the phone, expertly burping the baby balanced on her knee.
"Uncle Phil, good to hear from you. A job, you say? Wow, yes, I'd love to be back on the TV!" cried Robyn, excited.
"Oh, you mean a DBA job…you know, Phil, those were good times we had running that server room, but years of stuffing baby food into gaping mouths takes its toll. I've forgotten all that I once knew about databases".
"You'll soon brush up. Remember what we used to say…'If it's not in Stanek's Pocket Guide, it ain't worth knowing'?" said Phil, encouragingly. "And I tell you what…we'll wire you up and I'll give you backup from a café over the road….I'll be a reassuring voice in your ear when the going gets rough."
Robyn sighed…."How much will they pay? Is that per hour? Oh, per day…"
There was a crash in the other room followed by a wail and then heated screaming. Robyn sighed again, more wearily, "I've got to go, Phil. I fear my daughter's 'My Little Pony' dressage unit has collapsed. Look….I'll do the job, but only if Steve and Grant are there with me, plus you over the wire."
"Deal. Looks like it's another job for the DBA Team…."
"Oh, and you'll also need to arrange a baby sitter…" said Robyn, before hanging up.

Scene 5: Reluctant reunion

"Grant, have I got a job for you! DBA gone crazy, server in bits…"
"Man, what sort of DBA destroys his own server…it makes me…SO…ANGRY…" Grant clutched his temples.
"And this time someone wants to actually pay us!" Phil interrupted hurriedly, "Well, actually they want to pay Robyn."
"What for? Do they want an actress or a professional DBA? What's wrong with my looks?"
"You'll get a cut. These guys don't know about the DBA Team. Robyn needs help. She's OK with the basics but a bit rusty with the DBCC commands, if you get my drift. You and Steve will be there to provide expertise when she gets stuck. I'll be in contact with Robyn over a wire. Easy."
"So you want me to stand next to her while she sorts the problem out? I'm a DBA, not a ventriloquist, and she's no dummy."
"Did I mention that it sounds like the crazy DBA messed with the latest backups…"

"It sounds like the crazy DBA messed with the latest backups"

Part of the SQL DBA Bundle

Recover from onsite disaster with SQL Backup.

There was an awful silence.
"His own backups….!" Grant clutched his forehead. "Don't make me angry! Aw hell! It's clobbering time!!"

---@---

"Steve? How's life on the ranch?
"Phil, pardner. A call from you means trouble, so quit the small talk and spell it out!"
"Steve, you've gotta believe this, someone wants to actually pay us for our database skills."
Steve Jones dropped the hayfork in surprise. The horse rolled its eyes in sudden panic and trotted a couple of times around the field, neighing. "That'll be the day."
"I'm exaggerating slightly. They want to pay Robyn but she won't go unless you and Grant are there as backup."
"There is no such a thing as backup, only a restore."
"She just needs someone to help her when the purple wobblies start to wobble . Will you do it? A simple trip, it'll be over in a couple of hours."
Steve chewed on a straw for what seemed like forever before saying, "Now you understand. Anything goes wrong, anything at all…your fault, my fault, nobody's fault…it don't matter…I'm gonna sing you extracts from my SSC Christmas editorials for all eternity."

Scene 6: If in doubt, Kanban

Robyn and Phil gazed nervously out of their taxi as they pulled up outside the Acme building. Robyn had her phone to her ear and was giving elaborate instructions to her baby sitter on warming the milk. With a sigh, she snapped the phone shut. "I don't think I can remember any SQL Server at all. Give me a test question, Phil."
'How do you restore a database?'
"Well, a good Napoleon Brandy, of course. Certainly not rum and pineapple juice…Seriously, Uncle Phil, I don't think I know my SQL from my Celko right now."

"How do you restore a database?"

Part of the SQL DBA Bundle

Handle all your backups and restores in one place with the SQL DBA Bundle.

Phil laughed nervously. "Grant will be here soon. And don't forget, I'll be in the Café on the other side of the square at the end of the phone."
Robyn sighed. "If things go wrong, I suppose we can always get them to do the Kanban."
"Well, the developers might be game but I don't think Grant will put on stockings, link hands, and kick his legs in the air for anybody, plus the kilt…."
Robyn managed a smile, "Don't fall asleep over your wine. I'm going to need your help in there." With that, she got out of the taxi, and walked up the steps into Acme's IT office.

---@---

Across the square was the glint of a pair of binoculars. Gabriel stared intently at the distant figure and swore.
"Hey boss. A classy dame just arrived in a taxi. I'm all tooled up and going in."

Scene 7: More to a database than meets the eye

Maria ushered Robyn into the server room, glowing with smug pride. They surveyed the dead server. By rights, there should have been a chalk-mark around it.
"OK", shouted Robyn, decisively, clapping her hands and bringing the group to attention. "I assume you've got a cold standby and at least attempted a bare metal restore of the database and environment?"
Blank looks.
"What I mean is, Raul should have set up a spare server with SQL Server installed and configured just like production, where you can attempt a restore."
"Ah, yes," cried João, relieved, "there is a problem with the latest full backup but we got the one before that from the archive. It's out of date, of course, but we wanted to prove we could get everything running on the…err…cold standby"
"And it worked….?"
"Well, put it this way, if anyone ever starts an archive of interesting but blood-curdling error messages, they'd do well to give us a call. The restore worked and we have access to the database but the application won't run at all."
"Did you restore the entire server environment before attempting the database restore? This will also bring back any other applications and data which may have resided on the server at the time of failure."
"You mean that there should be a Windows backup of the server as well as a SQL Server backup?"
Robyn sighed, "Yes, there are often quite a few things stored on the SQL Server instance, or the server itself, that are required for a database to work correctly in the production environment…I assume you've got a backup of the system databases?"
There was a faint bleat from the developers. Robyn gulped. No Windows backup, no system database backups, broken full database backup…It was going to be a long day.
At that moment the door burst open. There stood Gabriel with a Kalashnikov under his arm, looking as if he meant business. "Don't move!" he shouted, as he stormed in, slamming the door behind him. "You really cooked your duck this time," he snarled, leaning menacingly against the door. "Hands in the air where I can see them!"
João's hands shot into the air - his caiprinha went flying.
Gabriel glanced scornfully at the lime wedges rolling under the server, before turning to Robyn. "OK, Miss Robyn, step right forward slowly, Mr Bruno wants …."
Crash. "Hey guys, sorry I'm late. Just show me the DBA who…" Grant stormed in, waving balled fists. He stopped dead in his track as Gabriel slumped dramatically to the floor, from behind the door, and his gun skittered across the floor.
"Sorry, didn't see him there…a Kalashnikov?! Oh boy, your users sure take database down-time seriously."
"Just in time, Grant!" cried Robyn, as the developers immobilized Gabriel with the vogon nerve-grip, and dragged him downstairs, "the most recent production database backup won't restore…"
"OS backup? System database backups? I see….well someone at least show me the latest production backup," cried Grant, throwing his hand up in despair.
He attempted a restore, confronted with a screen of red. He tried again, this time just RESTORE HEADERONLY…."Why do you suppose the Backup Description field says "Maria is a witch?"
"You think Raul tampered with the database full backup??…Of all the lowdown, dirty…"
"Maria," Grant cut her off, "call your offsite backup people immediately and tell them we need the latest backups here within the hour."

"We need the latest backups here within the hour!"

Part of the SQL DBA Bundle

Recover data whenever you need with cloud storage from the SQL DBA Bundle.

"I already did. They said we only pay for 48 hours delivery and they couldn't possibly do it faster than 36."
"OK then, well…we'll just have to go down there and get them ourselves".
Grant and Maria hurried out.
"In the meantime," said Robyn, addressing the devs, "we've got to be able to find backups of master and msdb, somewhere, so we can restore the agent tasks and alerts. Really? No?"
"I think we have the scheduled jobs in source control though…" offered João.
Robyn took a deep breath. "Well, that's something at least. Let's get scripting…."

---@---

"Hey Bruno, more bad news. They are going after the offsite backup with a mean looking guy called Grant Fritchey in tow."
Bruno smiled and stubbed out his cigar. "Well let's hope they don't run into any trouble along the way…"

Scene 8: Break out the backups

Grant and Maria sped out to the backup facility in his beat-up pickup truck, crossing the old river valley. Maria looked nervous.
"You know, Grant, there's a chance they may not be so keen to hand over those backup tapes."
"Why not? OK, so you only paid the 48 hour turnaround and we need them a bit quicker. Big deal."
"Well, it's more that we may not have paid at all for a few months…"
Grant looked at her slowly. "We'll find a way. Nobody keeps this DBA from his backups."
"But this is a high security facility. There will be guards, we'll need clearance. Even if we somehow get past them, we'll need authentication, pass codes to get in the building…"
Maria fell silent as Grant pulled up outside and parked in a slot marked "Reserved for C.S.O." They got out.
"OK, so no guards at the gate for some reason, but there is no way we're just going to walk into here." Maria fell silent again, as they noticed a rusty old metal door standing ajar. A little further down were two guards, backs to them, chatting over a cigarette. Two minutes of rummaging and they had their backups. Grant peered around the door.
"Coast clear?"
"Put it this way…"

Scene 9: "Ruuuuuunnnnnn…!!!!"

The pickup tore down the highway, tires smoking
In a side-turning, Bruno stood up in his jeep, looking through binoculars. "That's them. Pedal to the metal and after them." He grabbed his machine gun. Their jeep shot off in a cloud of dust.
Maria glanced back, frantic, "We're being pursued. If we can just get back across the valley bridge, I know how we can lose them…but… Hell, they're gaining on us"
They swerved onto the bridge, the jeep right on their tail.
"I don't think we're going to make it," screamed Maria.
They were almost across the bridge when out of nowhere a figure in a Stetson and a really terrible shirt loomed right in front of them. He was riding a handsome steed…
"It's Steve!" cried Grant, the pickup swerving and missing them by inches. The horse reared, the jeep careered wildly to the right, smashed through bridge barrier, flew briefly through the air before crashing into the valley slope.
Staggering from the sedan, wincing at each loud bang, they saw the jeep begin its rolling, crashing descent.
"Howdy, pardners!" Steve lifted his hat "I rode in up the valley this morning."
"Steve…Why did you do that!?"
"What? I had no choice…the Prius is at the garage…"
A massive explosion sent everyone to their knees. Peering down from the bridge, they saw the mangled, smoking carcass of the jeep on the valley floor, and two distant figures crawling from the wreckage, disoriented and dishevelled but otherwise completely unharmed.

Scene 10: We have a database, people!

"Grant, you have the backups!" cried Robyn as they entered.
Grant nodded with a steely determination in his eyes, and immediately got to work transferring them for restore to the standby server.
"Howdy, Steve. What took you so long? We've already managed to come up with a complete build script for the agent jobs and alerts."
"Yep, Grant and Maria brought me up to speed. We've still got a lot to think about while Grant test restores the latest backups…security credentials, the possibility of orphaned users and broken ownership chains, connections, server configuration, database properties…"
Robyn intervened. "Whoa there cowboy, let's get all this down" She started to write on the whiteboard, before checking herself and grabbing a pack of post-its, building a KanBan board to begin to layout the tasks that need to be completed.
"Let's start at the beginning with Security." She wrote the heading on the board. We need to recreate any Windows logins that need to exist on the server. She slapped the Windows logins post-it on the 'Tasks' area.
"How do we know which ones we need?" asked João.
Steve piped up, "A database user maps to a server login, which is stored in 'master'. Once Grant restores the database, we use the sys.database_principals and sys.server_principals catalogs to check them. The SID maps from the user to the login. It looks as if your application only uses Windows logins, so all we have to do is create those logins on the server."
"Also, check for orphaned users," continued Steve. Robyn duly slapped up the post-it. "The SID and password for every database user must match its associated Windows login. If you find any, synchronize them with ALTER USER…WITH LOGIN."
Maria suddenly rushed up to Steve. "I can hear a strange echoey voice in the server room. It sounds a bit like Raoul, the DBA, but it sounds as if he's inside a biscuit tin."
"Hmm. Bandits, security guards, now ghosts. This ain't no ordinary database consultancy job."
In the server room, a voice was issuing from a security camera at the top of the server rack. "Hiss….crackle….Hello boss? The reception is terrible…Eh? What are you doing down there!?
Maria ripped the camera off its mounting. "Silly guy didn't switch the microphone off."
Back in the main office, Robyn was still in full swing "Create all the SQL Server logins on the server that you know about as well, just in case they're used by a process." Up went the post-it.
Steve broke in again. "We also need to check database ownership to make sure that the database owner hasn't been orphaned and that it is related to a login on the new server. You'd need to use ALTER AUTHORIZATION ON DATABASE:: to fix this if necessary."
"OK, what next"
"Connection strings," suggested Tom, one of the quieter devs. "We need to check the application build to make sure that every connection between the application and the database addresses the new server, which will probably mean wading through lots of connection strings and ODBC configurations within the application."
"Good", cried Robyn, slapping up the note and sensing the developers were finally warming to the task. "Also, test the connection to make sure that it isn't being prevented by firewalls or DNS entries." Slap.
Next, up went server configuration settings. "By default" said Steve "when you install a SQL Server instance, some features that the database might need are disabled, so we need to check that."
"Hey, I seem to recall Raoul scripted out the production settings with a PowerShell script. I think we've got those in source control, and probably the database property settings too."

"I think we've got those in source control"

Part of the SQL DBA Bundle

Deploy directly from source control or backups, with the SQL DBA Bundle.

In a similar fashion, up went notes for database mail, collation settings, service broker, maintenance plan checks, and more, until the board look liked a healthy tree decked in fluorescent colors.
"Excellent!" shouted Grant, punching the air "We've restored the latest full backup and the full log chain. The disk housing the log file avoided the Piña Colada so we've lost almost no data. We have a database, people!"
"OK, we're all set. There are five of you," said Robyn, "so let's allocate the top five tasks, and when you're finished, just pick the next unallocated, and put the post-it for the job you've done in 'Done'.
Soon, a flurry of hands were pulling off post-its and, after a while, slapping them in the 'Done' area.
Steve looked on in amazement. When all the work was done, Robyn clapped her hands like a schoolteacher and said, "Right, let's run the new version of the application and make a note of every error message there is. Then just work through them."
There was a hush as they went through the build process for the application, and restarted the server. It seemed like an hour but wasn't. There were no errors.
Grant, Robyn, Steve, Maria, and all the developers linked hands and began kicking their legs in the air, in something approaching synchronization.

Scene 11: Not just a pretty face

Phil nursed his class of wine. Steve, Grant, and Robyn strolled over. "Phil, that was great. You overdid it a bit though, didn't you? I thought that the developers would suspect that you were prompting Robyn."
"Overdo it? I dialled Robyn's phone and all I got was some silver-tongued dame telling me I was first in the meeting and then she kept playing calming music at me."
"Robyn? Was that Lady Gaga or Phil you were listening to?"
"What can I say? One day I had this Gonzo headache, and before it was over I could speak and read SQL Server."

---

You'll need fewer heroics and less luck with the SQL DBA Bundle

The SQL DBA Bundle includes SQL Backup Pro, the world's most widely-used third party tool for SQL Server backups.
SQL Backup Pro 7 lets you schedule backups and restores and take compressed, encrypted backups. It can also test your backup files after they're created by including CHECKSUM and RESTORE VERIFYONLY.
Try it today as part of the SQL DBA Bundle, which contains SQL Backup Pro and six other tools that help you keep your data safe:

• Create up to 95% compressed backups and schedule regular restores, including full DBCC integrity checks.
• Protect your data from unauthorized access with 256-bit AES encryption.
• Monitor your servers in real time so you're always the first to know about problems.
• Review full details of schema and data changes going into your servers, including SQL CREATE scripts with highlighted differences, so you can deploy updates with zero errors.
• Save time writing SQL with code completion, code formatting, and customizable SQL snippets.

Try the full bundle for free