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!"
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
