So I am sitting here doctoring (not nursing) a beer listening to Jimmy Eat World and Nerf Herder and thinking about a huge decision I had to make this week.  The more I thought about it, the more I wanted to pose this scenario, this quote unquote real-world scenario to get some feedback on how other DBAs or managers might handle the same situation.  I know from doing this for so long and the people I have met, that this happens quite a bit to DBAs. 

The scenario:

You have a slow running front end application. It has been getting progressively slower over the past few months, however, it has always had issues with certain processes. You, as the DBA,  know you are dealing with millions of records in some tables. These tables have a tossed salad approach to indexes, one clustered index and up to 30 or more non-clustered indexes. You discover that a former DBA has added non-clustered indexes based on the sound advise of the index tuning wizard or database tuning advisor (dta). When things run slow you analyze and see thousands of sustained shared locks on one of the dta indexes; no blocking but users complain of slowness. You also know that developers are working on a solution to address a potential bottleneck with code you can not see.

Business is...well...pissed. A decision is made to move to a new, bigger server. This always fixes problems with code, right?  The other variable is that since we are moving to a new server, why not go ahead and go from a 32-bit SQL 2000 Enterprise install to a 64-bit Standard SQL 2005 install? It would be WAY easier to manage and should gain a 20 to 30% performance increase OOTB.

Okay, so User Acceptance Testing (UAT) has been performed on SQL 2005 but for functionality only.

You also learn late in the game that the server you want to move to is a single RAID 5 array of local disks, not SAN attached.

Wait...you also just learn the vendor does not officially support SQL Server 2005. They have customers there but do not know how it will work for us.

Two days prior to moving to this new server with double the processors with the same version of SQL (2000) the vendor responds and says they now will support 2005 and beckon us to go there.

Now you have to decide:

1.) Do you stall the upset users and work to address the slow running code by working with the developers and tuning/deleting the dta indexes in hopes that this will buy enough time to test SQL Server 2005 for a future upgrade (within two weeks)

2.) Do you go ahead with the SQL Server 2000 migration to the new server over the next weekend in hopes that it will address the users slowness and loss of productivity, though the problem may still exist on the new faster server.

3.) Do you move forward with SQL Server 2005 64 bit Standard, knowing, wow, knowing there is no easy way back if there are problems.

4.) Do you spend the next two days testing with the developers, delete the ferel index from dta, re-create all of the large clustered indexes late on a non-maintenance weeknight knowing that SQL 2000 does not have an online rebuild functionatliy and will require whatever is not completed to be rolled back and could impact production?

5.)  Add you own decision here.

I will relay what I did (ed - will have to do) with this scenario in a reply.

With gas prices here in the panhandle of Florida where I comfortably reside soaring to over $3.60 per gallon, I knew I would spend at least $110 toting myself and my beautiful fiancee from Pensacola to Jacksonville, host city of the most recent SQL Saturday event this past weekend. Another $89.00 per night for 2 nights, not to mention food and beer (the biggest cost) all so I could present my session on a DBA repository that I have put together over the past year using SSIS and SSRS. In hindsight, it was certainly money well spent for a chance to participate in a most worthwhile event. Imagine..a free day of training with regional presenters, authors and developers most of whom have MVP tagged to their illustrious careers. 

Cheers to Brian Knight, who obviously worked very hard to organise the event with the help of many SQL PASS volunteers from Jacksonville.  Chris Rock, Andy Warren, Tim Mitchell and Joe Healy are a few other that I met who were most cordial. It is not always easy to tolerate a 6 foot 5, 270 lb obnoxcious oaf like myself who has pounded down 5 strong micro brew IPAs and wants to play SQL alphabet and win at pool.

By the way, I highly recommend SQL alphabet. Simply start with "A" and everyone has to choose a keyword or term from SQL services before continuing to "B".

Generally it moves quickly. "ABS()", "AND", "AVG", etc. It gets more difficult with more than 4 participants and more fun after each beer. By the time you get to "P" (pun intended) it is a stretch. I thought I had everyone out matched with "Publication" but the last person on line came up with "Partition" without hardly thinking.

I know there will be a convergence on Orlando next month for TechEd. I hope to see everyone there. Maybe we can finally get past "X". Extended stored procedures do count.

Sometimes in my career as a DBA I feel like a doctor. Actually, this started before my stint as DBA...way back in my help desk days. Someone on the other end of the phone begins to impart their symptoms and like an ER intern, I listen intently to every detail. Often, like most good practioners, I interjected my questions at the appropriate pause, all the while formulating my conclusions that I would unveil when the person had exhausted themselves with putting together sentence after sentence.  A recent case study went something like this (and as I parenthetically do in these little snippets, I will "sign post" my point - I ultimately would resolve how multiple Windows group membership in SQL worked and pretend all along to have known this while diagnosing the disease of the ODBConomia Braingonemisis):

"Several users can not connect to our new budget application. They have the same rights as all of the other users who can connect. We have tried for 3 days to figure this out so while you are in town, we wanted to see if you could help," the conversation began.

I pondered for a moment and finally asked, "So other users can connect?"

"Yes," came the reply.

"And do the users who can not connect get an error message?"

I knew the response would be affirmative, but lacking the crucual details. It always happens that way. "They can not connect" is generally as far as it gets.

"So what did it say?"

"It is an ODBC error. I did not write it down."

"Is there a user we can test this on?"

"Yes, but she is at lunch."

"Ok.." I wanted desperately to dispatch the interrogator to the corner office to sit an wait for said user while I moved on to other interesting issues. But I was caught in the web of mystery. That is how it goes. I can not give up until I know.

"Who is the user?" I asked finally. I will check out the database.

I got the name. I got the database. I got the server. I got it all. I was onsite with a group of people whom I had met only once and I had all of the information I needed to troubleshoot and damn I troubleshooted (just wanted a past tense there) until I had the answer.  I am skipping ahead swiftly because I am bored with the dialogue. In reality I waited for the user to return from lunch and go through the rote task of making an ODBC connection from her computer and recreated the problem myself...on and on..until all at one I was satisfied. Here it is with no more Quotes.

In SQL, this user had her Windows account assigned to two groups...Group 1) Anatomically Correct and Group 2) Bipolar Dissonance. (Surely these are made up...but we can call them Group A and B..the point being alphabetically sequential.)

In Group A she was assigned to a default database to which she had no access. In Group B she had been granted all required access to the budget database to which she was trying to connect.

I determiend that her group membership alphabetically in Group A, having no access, was preventing Group B from allowing access. (and the other users having identical problems - two birds two birds two birds)

Simply changing the default database for Group A to "Master" to which everyone has access (publicly) allowed her to fall through like Marshall, Will and Holly to Group B and finally get past the ODBC error that had been driving everyone mad for 3 days prior to my arrival.

I explained this in detail to everyone, again as if I had known all along that group membership in SQL Server is apparently alphabetical. And I then quietly finished this blog waiting for all of the readers to dispute this with long treatises on how it really works and making me feel worse and worse until finally I break down and write another book on Reporting Services....Sans dialogue.

They tend to not write as much and make very long titles to their blog entries.  Though I have about 35 ideas right now that I want to get down from my recent travels to a Houston SQL PASS chapter meeting, I can only effectively get down 1 because the back-space key is making me want to drink to curb the pain. Funny thing about beer....hard to drink left handed, like everything else when you are a righty.

But...I wanted to send a quick thank you to the folks in Houston who had many good ideas that will eventually make their way into my daily work. One is the bane of batch processing, waiting for one task to complete (error free) before the next begins. On restores of 100+G databases, this is,of course, not efficient. The solution (avoiding linked servers) is to kick off a threaded SSIS package or SQL Agent job instead, which should not require a return code before the next can be initiated. Failure becomes the responsibilty of the called server/package, though.

Ouch....bone pain and back space.

I would like to thank those of you who read, voted and commented on my recent article on temporarily changing the sa password. Given godspeed healing powers, I hope to have the follow up article (as true to life as the previous) done and posted in the next week. 

Going numb...must quit soon.

The final thought is pronunciation of some SQL terms. We all know that SQL is pronounced S - Q - L not sequel, right? Well, how do you pronounce the following?

GUID.

I said "Goo Id" for the last 5 years until a conference when a MS techno pronounced it like "SQUID".

So...now where is the calimari? Getting hungry for SEQUEL.

Forgive the spelling. If I could just...reach....my....utility....belt.

That title should garner some comments, I suspect, or if not then the following questions should:

Does there exist a way to provide real-time - or a close proximity thereof - replication from a SQL production database to a reporting database with the following rquirements:

1.) No source shema changes (even assuming that the source DB does not have primary keys defined)

2.) Does not require SQL Server replication and the overhead and administration implied therein.

3.) Is not a failover/data mirror non-accessible representation of the source data that has to have snapshots for reporting.

4.) Costs less than 60,000 pounds or whatever astronimical amount that translates to in US dollars.

5.) Does not require a team of sales people to meet with you and your boss in person to discuss the "solution" over a lunch that they will pay for surely, preferably after hours, so that they can get you slogged enough to believe that 60,000 pounds is reasonable.

I dare say I have yet to find such a solution. Shall I handcraft one for myself and my company, languish in the accolades for a few weeks and then sell it? I could do it. Really, I could. BUT...that would take precious time away from me babying my other hard working SQL servers that complain little and work for next to nothing.

With the myriad questions in this post, I expect to see some comments. Okay, all 431 of you viewers...stop reading and write something. Does it exist?

It has been a full two weeks, actually more, since my last post. You may recall my last entry dealt with my inability to find quality DBA's after months of searching. I am happy to report (and recant part of my frustrated recitation on not finding anyone) that I now have two very solid DBAs on my team....but that is not the subject tonight.

Tonight, I wanted to post a quick little query for those who shun best practices. I am sorry, I seem to be doing this much lately. I should say, really, I adhere to security manifestos as part of my job and I eschew, like other DBAs, any security privilege that is unnecessary in practice. That being said, here is a query that I use to ascertain the service accounts used to fire up and run any SQL Service on any server. It uses WMIC (Windows Management Instrumentation Console) and xp_cmdshell, both of which require special privileges to run. However, assuming the service account for SQL Server has administrator pivileges or is Local System, the script should work just fine. When dealing with 40+ SQL Servers and requirments to change known passwords (in an imperfectly secured glass box world) the following script can be a cornerstone of a plan to get the ball rolling in that direction.  The few things worth noting in this simple script is the pipe to findstr "grepping" SQL and the use of the charindex function to parse the service name and service account. Give it a shot IF you have admin privileges with your account or IF you have your SQL Server logon credentials for the service with admin privileges (not advised as best practice and defintely not going to garner me any Microsoft MVP nominations). One server? Yeah do the work manually...two?Sure...same...20+ servers?...IF THEN ELSE.

As I sat at my desk, like most days, pondering what a partial database restore truly is in regards to object-level restores and filegroups and noting that though this sentence will go on longer than I intended I will not finish it prior to finishing the initial thought, my phone rang and the question came to the DBA, me, "How can I search for code in a view or stored procedure that contains a specific string?".

Here I ponder weak and weary...

It should have been obvious...the answer should have come immediately, however,with a bit of time to spare, I explained I would return the call momentarily as I had another critical issue to deal with.

So, how does one do what was asked in the first long sentence of this entry?

I knew that the "sysobjects" table contained the objects themselves: views, stored procedures, tables, etc. But where was the actual code that defined these objects?  Then it came to me:

sp_helptext

This little gem of a system stored procedure will return the code for which my interogator wanted to search. So where does sp_helptext get its info? Again, simple:

try:

sp_helptext sp_helptext

In the bowels of the sp_helptext stored procedure itself was revealed the missing table:

syscomments

So the query issued forth that would expose the TEXT field which holds the answer. A join from sysobjetcs to syscomments on the ID column was all that was required.

Throw in criteria for the XTYPE field, and you can narrow your search to a View(V) or Stored Procedure (P).

Fianlly, the impetus for a Reporting Services report was born. One that can pass in a parameter as the object name with other parameters for database and even server that would control the data source itself via an expression. One would only have to run the report, pass in the search string and the report would show not only the objects that contained the string, but the code too. This report, like the cat in the box or the winning numbers of a lottery ticket do not exist yet. But...

I will make this report if there is interest in it. I will make it and I will distribute the RDL for all who have read this far and agree there is a need. I can even have it posted by this Friday. I work well under deadline.

Rodney

Author "Pro SQL Server 2005 Reporting Services"

I was on vacation last week. Ok, you may call it Holiday...but with four kids in tow to Orlando, FL to spend as yet un-tallied sums of cash, it is hard to call it relaxing.  The trip did afford me the ability to see the convention center in Orlando, the designated focal point of the upcoming TechEd conference. As always, I was awestruck to even drive by. With the Ripley's Believe It or Not building leaning askance in it's surreal way, I could not help but wonder who all I will meet in June and will they be as surreal or have experienced something odd in life to report. With that said, I am sad to say I have not had one thought of SQL in the 9 days of Vay Kay. I checked the Crack Berry peridically, but only once a day. Hard to believe I only had one missed call, even though this last Windows update snowed under the SQL 2000 mail capabilities and 20 some odd servers all need some level of attention.  But...

Since I have already titled this entry as Beer and SQL..I must report on what I learned that is even more important now than SQL, which I promise to stringently belly flop into tomorrow, if the head cold abates and I can focus on something other than cat napping:

Budweiser....you know the king of beers here where I am in America...is reportedly on a "buying spree", according to the bartender at the Hospitality House in Tampa's Busch Gardens, where I enjoyed several (wait only allowed two beers), ok, a couple of complimentary doses of amber courage.  This spree includes Landshark out of Jacksonville, Florida, but also other beers which my SQL brethren across the pond may know...

Stella Artois...Kirin... and a certain percentage of Tsingtao to name just a few. 

 If you have ever heard of / imbibed any Redhook products (especially the deliciously hoppy IPA) you will be happy to know that though not owned outright by Bud, some distribution deals have been made.  I am only reporting what I am told AND it may not be the place so I will have to focus my next entry solely on SQL or face the wrath of others who are seeking tech talk, or as Phil has laid it out, geek speek every week.

Done......goes down smooth.