Hideously Dangerous Techniques you wont find a KB article on (I)

Although I hope it does not happen in your practice, you may someday find yourself with a SQL Server database stuck in the 'recovering' state.


If it is a large database, say, a terabyte or so, recovery may take fifteen or sixteen hours.


This may be problematic to the ongoing operations that you are responsible for.


Here is an ugly, ugly hack that will let you bypass the recovery checks.   It carries some risk.   It is dependent on you having a good backup of the recovering database.    It is dependent on you having a good backup of the recovering database.


This operation requires an instance stop. 


1.   Stop the impacted instance.
2.  Rename the .mdf/.ldf/.ndf files for the database stuck in 'recovery.'
3.  This will cause all sorts of unpleasantness.  Expect it.
4.  Start the instance.    Note the database is now 'suspect.'
5.  Restore your good backup to a different name.
6.  Delete the suspect database.
7.  Rename the restore to the correct name.
8.  Conduct a forensics analysis of the process that led to the failure.
9.  Write profligate emails of apology.
10.  Avoid this process in the future.


This emerged in my own practice during a deployment, time-critical, of course.   High-dollar consultants were sidelined while the operation was in progress.   Developers were frustrated.


It was the result, of course, of hurrying and not thinking a large-scale ALTER COLUMN all the way through.


Here is a beautiful song to soothe your anxiety after reading about such hackwork.


If you botch this process, feel free to call Microsoft for support.   You will enjoy their dry, unamused chuckles and inability to help you.

Tough Week

Prepping for a deployment this week.   Ruined my good cutting board, burned my feet.

A Bitterness 
Mary Oliver

I believe you did not have a happy life.
I believe you were cheated.
I believe your best friends were loneliness and misery.
I believe your busiest enemies were anger and depression.
I believe joy was a game you could never play without stumbling.
I believe comfort, though you craved it, was forever a stranger.
I believe music had to be melancholy or not at all.
I believe no trinket, no precious metal, shone so bright as your bitterness.
I believe you lay down at last in your coffin none the wiser and unassuaged.
Oh, cold and dreamless under the wild, amoral, reckless, peaceful flowers of the hillsides.



Stephen Fry's excellent monologue seeded some meditations.

I have found, in the most bitter and angry parts of myself, a hideous expectation.

The wailing of a child, 'BUT YOU SAID LIFE WAS FAIR!,' a delusional and egocentric need.

I have a deeply ambivalent relationship with hope. To hope is to begin the process of disappointment.

A poet I am misquoting noted that Bitterness is really a sense of being cheated, of not being given one's due.

Envy and resentment grow well in the shade there.

It destroys gratitude in this sense- it is impossible to see blessings, focused on absences.

How deeply disappointed I have become.

Configuring heterogenous data sources with SQL Server 2008 R2

Meditation:
The most common cause of problems is ignorance.


Story:


I've been swept up into the SQL 2008 R2 media blitz.   Soon, my end users will be wearing sharp outfits and assembling their own reporting via 'self-serve' business intelligence.   What a happy day that will be.   IT will finally be out of the BI loop, customers will be free to explore the fertile fields of data without a guide, and all will be well.


Central to this is moving towards modeled data.   At present, we use far too many ad-hoc queries against undocumented sources.    Most certainly a centralized, shareable model is the way to go.


This cryptic blog post from 2006 started me thinking about simplifying heterogenous data access- one of our most common, and difficult problems for our relational data.  


So, I fired up BIDS, cracked open the report modeling projects, and proceeded to spend ten hours in a frustrating battle of wits:  Adding, removing objects to the data source view layer.   Changing objects from tables to Named Queries.   Nothing would build to a successful, deployable project.


The ultimate resolution came only after two days of frustration and despair:

As you can see in the properties window- heterogenous data sources must be fully qualified.   By implication, linked servers must be used for off-server datasources.

If you can find this spelled out anywhere in BOL, or any of the MSDN blog posts about R2, I salute your keen observation and attention to detail.    I failed to find this information during the preparation phase of this experiment.

The only cure for my ignorance was proceeding forth stupidly until project building was blocked by BIDS.  

So, that it may be useful:   To build a data source view under 2008 R2- with the intent of deploying a report model to an R2 SSRS instance- you'll need to use named queries (not secondary datasources!) for the non-primary datasets.   Those will need to be fully qualified, and the linked servers required will need to be in place.

I'm grateful this was merely and R&D project and not- for example- a production outage where ignorance can be catastrophic.

I'd done, what I thought was adequate preparation.   Reality proved me wrong.   The correct procedure was discovered only after hours of  flopping about.   

I'm somewhat impressed I managed to power through my own ignorance, and that it only took two days.   I might have spent either 30 seconds or a month on this earlier in my career.   It took just the right amount of time.

There is a cause.

Meditation:


One of the luxuries of our practice in IT is a rational, empirical framework.   There is always a root cause.


Story:


You are wearing your lucky socks.  You have been issued your security badge.   The HR paperwork is complete.


The friendly smiles from the interview process are strained, this Monday morning.   There have been 37 application timeouts since 6 AM.   Cash posting is delayed.  


The department has been informed, that you, the new DBA are the miracle cure for all existing application problems.


Within five minutes of sitting down at your new desk, you get a call from the VP of Finance.   They really need to figure out what's going on- and get it fixed- immediately if possible.   It's costing hours per day.   The folks in Order Entry have come to hate the slow, malperforming system.  


You get another call.    There's been another five timeouts in the last few minutes.  Things are getting worse.


You pull up the familiar outlines of SQL Management Studio.


Nothing.


Your phone rings again.  The comptroller is getting security warnings trying to run a critical report.  


Right-click connect.   At this point, you know none of the architectural internals of any applications.   In fact, all you know are server names.   No one knows which databases are exactly where, or even what is being hosted per database.    The world is unknown, sea monsters lurk:

The room is very, very small, and very very hot.   It takes 30 seconds to connect to the primary server and login.  


Nothing is documented anywhere.


What now?


Thus began my first pure DBA assignment.   I'd been working as a DBA for about ten years, but always as a member of a team, or in a secondary role.   This was my first morning of total ownership.  


I could feel the dissatisfaction growing with every passing moment.    50 time outs.   The cash posting group was wondering if maybe they should go home.


This particular gig hadn't had a DBA onsite for several years.  


Breathe.


At the root, our practice is about getting data to and from disk, then into memory, and into the CPU for processing.   All problems stem from blocked flow in one of these areas.   This is always true.


Breathe.


SSMS showed no blocked sessions.   Perfmon showed some disk activity- but only moderate memory use.   The CPU was pegged at 95 %.


An hour has passed;  Nothing is better.  You are sweating.

An investigation of wait state DMV's shows hundreds of CX_PACKET waits.   You think you read a paper about that once.


Can't that be caused by intraquery parallelism gone bad?  Poor indexing?


You right click on 'Reports->Index Physical Statistics.'


You stifle a scream.   Everything is fragmented to 99 %.


A few emergency rebuilds later, concentrating on the indexes with the highest utilization, and the timeouts start dying off.


By lunchtime, there hasn't been a timeout in an hour.


'Thank god you're here,' a colleague says.


It took three months to solve in its entirety.    The root cause was a series of bad/missing indexes coupled with poor query design.    This was leading to excessive CPU utilization due to bad query plans, under unconstrained parallelization.


The fix was some new indexes- but most importantly- weekly maintenance to make sure that indexes were unfragmented and statistics were correct.


Which I guess yields the takehome:  begin at the beginning.

Beginning with open hands.

This project is to augment my daily working notes.    

I am not selling anything.   I have no secret TSQL scripts that will improve performance on your server by 400 %.   I am not looking for consulting opportunities.    I am not grinding any axes about the death of relational databases.

My target audience is my fellow DBA's, primarily.   My secondary audience is my friends, and if I imagine a tertiary audience, it's folks with way too much time on their hands.

My intent is for it to be very much like a lab notebook or research journal.    Possibly augmented with recipes and book reviews and other narcissistic debris.


If it is helpful to anyone, then I have succeeded.

Comments and feedback are very welcome.   Thank  you for your attention.