Archive for the ‘SQL’ Category

It’s never easy is it?

We recently made a business decision that upgrading some of our business systems to current versions didn’t make much sense – they don’t really meet the needs of the business and the upgrade project would take almost as many resources as replacing them.  The replacement project has kicked off and it looks like we will have new systems in place within 18 months or so.

This is all well and good – migrations are always a more interesting project for me than upgrades, but it left me in a bit of a quandary about what to do with the SQL install that supports these systems.  I’m still running these databases on SQL 2005 (I know! I know!) and could really take advantage of page/row compression for some of them.  Of course, to do that I need to be running at least SQL 2008 so it is time to start planning and testing my upgrade paths.  After doing some research on what versions of SQL the applications supported with our current version of software I realized that as much as I wanted to I couldn’t upgrade to SQL 2008 R2, and instead was limited to SQL 2008.  It’s not great, but better than supporting  these systems on 2005 for another 18 months.

I have a big old honking test system that holds five named instances of SQL for test/dev/qa.  Because we were going to upgrade the software to current it was a mixed bag as far as my SQL versions.

My current reality looked like this:

Instance1 – 2005 sp2

Instance2 – 2005 sp2

Instance3 – 2005 sp2

Instance4 – 2008 R2

Instance5 – 2008 R2


I needed it to get to here:


Instance1 – 2008 sp3

Instance2 – 2008 sp3

Instance3 – 2008 R2

(Instances 4 and 5 could be removed – they were installed for the application upgrade, and not needed any more).

Easy-peasy right?  Not so much!

Initially I (naively) assumed that because I was dealing with named instances I could upgrade the 2005 instances to 2008 with ease even though I had a those two 2008R2 instances living out there.. I mean, I don’t care what version of SSMS is running, I just cared about the engine version for each instance.

That was my first poor assumption.  After attempting the upgrade and it failing for Instance2 I realized that I needed to remove the 2008 R2 bits from the server.

No problem I thought – I’ll remove 2008 R2 via the control panel since I don’t really need it currently and go on with my upgrades.  If only it were that easy!

I went round and round with this, and eventually wound having to use Aaron Bertrand’s guidance in this blog post to remove all of the 2008 bits completely.

Once that was complete I was able to perform in place upgrades to SQL 2008 SP3 for Instance2 and Instance3 and only once those were complete did I dare to attempt the upgrade on Instance1 to 2008 R2.

All in all I spent entirely too many hours on this, but it would have been even longer had Aaron not blogged about removing the eval bits of 2008R2 back in 2010.


For me it was just another reminder that you should ALWAYS test before attempting upgrades or changes in production.   Certainly not my favorite way to spend a Sunday afternoon, but it would have been much worse had it been in production and I had a steadily decreasing maintenance window.

Categories: SQL, SQL2008

T-SQL Tuesday #028 – Jack of All Trades, Master of None?

March 13, 2012 3 comments


Argenis Fernandez is hosting this month’s T-SQL Tuesday and he asks us the following questions:

Are you specialized? On something? Or anything at all? Has that been a good or a bad thing? Why?

Are you the SQL guy at work? Or the one who does everything?

Do you code? And configure wireless routers at work also?

If you had to pick one thing to specialize on, what would it be?

I’ve mentioned at least once or twice that I fell into, not only DBA work, but IT work in general several years ago.  When I did get that help desk position back in 1999 event though my primary responsibility was to provide first level support, answer the help desk and escalate calls to other, more experienced technicians I found myself quickly picking up the skills needed to resolve an ever increasing number of trouble tickets on my own.  In addition, I found myself taking it upon myself to take on systems themselves.  I worked in a small IT Operations team – there were 6 of us in total and we managed everything from the desktops, network, phone system, server systems, application support, licensing, etc, etc…  I found myself picking up systems that no one else on the team wanted or had time to properly manage.  Things like our old VMS system, Anti-Virus system, and even hardware support.  At one point I could rebuild an Okidata printer in less than 30 minutes – just because I did it so often.

Because I tended to pick up the red headed step systems in our network I have gathered a general knowledge about many areas of IT, and I don’t consider myself an expert in any of them – and honestly I’m okay with that.  It kept me on my toes for the early years of my career.  I never knew if I would be working on a queue problem in VMS or a virus outbreak when I got to work.

Even now – with my official title of DBA I tend to wear a lot of different hats.  I manage licensing for the business, I manage most of our hardware purchases, I am a back up Exchange administrator, I still help out at the Help Desk when needed, and I’m a resource for the newer folks on our Operations Team.  There are times, when my databases are behaving themselves that I don’t even open Management Studio for days at a stretch.

It’s a mixed blessing in my opinion.  Taking this path into IT and being more a generalist than a specialist has given me the confidence that I can tackle any area of IT and become proficient, but the flip side of that coin is that I, at times don’t see a clear path to becoming a specialist, let alone an expert in any single area.

With all that being said, I suspect that I am the most specialized person in my team at this point, and I agree with what many others have said today.  Specialization is best when it follows a well rounded base knowledge.  You have an edge that others might not have with that base knowledge.  You can often see the bigger picture quicker, and you have alternate paths to explore when troubleshooting issues.

So, go be a Jack of all Trades for awhile – and when you’re done doing that think about the one area of IT that really caught your attention and focus on that for a while.  I would guess that it will serve you well just like it has me.

Categories: SQL, T-SQLTuesday

Slip Sliding Away

Time is creeping away from me these days.  I’ve had some major life changes in the last year and while I’m not going to air those here, I’ve at times felt it was all I could do to keep up with my day job, community responsibilities, and all those things that must be done to keep life rolling.  Needless to say, keeping up with this blog has been at the end of my long list of high priorities while I’ve attempted to find my new rhythm.  I think (hope) that I’m back to a place that will allow me to keep this semi-regularly updated from here on out.

Here goes nothing!

While I was away I managed to pack in Summit 2011, SQL Saturday 104, and SQL Saturday 109 as well as an ERP Vendor Shootout.

I’m not going to spend time trying to summarize my experiances at Summit and SQL Saturday 104 – It’s been too long, and I certainly wont’ do justice to either.  You should know that Summit 2011 was one of the best yet  – every year Summit gets better and better.  SQL Saturday 104 was great as well.  I delivered a Backup Basics session to a great group of folks, and the organizers did a fantastic job doubling the size of their event (or did they triple it?) from the first year.  Colorado Springs will be an event I don’t plan on missing in the future – and you should make the effort to join them next year.

I will tell you a little bit about SQL Saturday 109 though.  I have to hand it to Mark Ginnenbaugh, Ross Mistry and their team of volunteers.  They put on a great show, with an all star cast of speakers for their inaugural event.  I believe the attendee count was right at 450, which is a feat in itself for the first SQL Saturday in Silicon Valley.  The session selection was top notch – in fact I had tough choices to make for each session slot, and really wished that sessions had been recorded for playback after the event.  I think the top session for me was Grant Babb’s session on Pro-I : open source security monitoring on SQL2008 because it was completely new material for me.  Don’t get me wrong – security isn’t a new concept for me, but I’d not considered what it would take to create a proactive monitoring system end to end using SQL Server and the work he and his team did is fascinating to me.  In fact I plan on sharing it with my co-workers this week as something to consider in our shop.

Other sessions I attended included Angel Abundez’s session on Reporting Services for mobile platforms, Wendy Pastrick’s Replication session, Denise McInerney’s session on Begin.. Commit, and Allan Hirt’s session on High Availability in 2012.  The day was rounded out with the Women in Tech lunch panel.  I had the pleasure of sitting on the panel with Wendy Pastrick (@wendy_dance), Jen Stirrup (@jenstirrup), and Nikila Srinivasan (@nikisrinivasan), moderated by Denise McInerney (@denisemc06).  We had a great discussion about why we keep talking about Women in Tech.  Both Jen and Nikila brought diversity and insights to the panel that we’ve not always had at previous SQL Saturday WIT events.

I’m still gathering my thoughts on the ERP Shootout – while it wasn’t really a SQL centric event in any way, the format was interesting and I can see a place for that type of event in our community – imagine if we had an event to see all of the 3 party packages for say, backups following the same scripts, using the same databases, on the same hardware, back to back.  Would that be valuable to you?  I could see how it would be if your company was in the process of evaluating toolsets.   I have more to say on the event in general and the format as well.

Categories: Community, SQL, SQLSaturday

Lessons learned: Filming video is hard work

Last week I headed to Tucson AZ to record 3 sessions for and I learned a few things.


Lesson # 1:  Presenting for cameras is hard work!

Seriously, I’ve done a few SQLSaturdays where I’ve presented more than one session and those were tiring, but filming all day is something else.  Without an audience in front of you the impetus is all on you to keep the energy up.  I’m worried that I wasn’t able to do that towards the end of the day.  I had no idea it would be so exhausting.  It was fun though.  The folks at SSWUG took great care of me, and were fun to be around.


Lesson #2:  A 60 minute session in front of people != 60 minutes in front of a camera!

I knew that my sessions would go a bit more quickly with no interaction, but I had no idea how much more quickly..  I had done full run-thrus at home and the time was short – but I figured it was just because I was doing them at home.  I wound up having to create new demo’s and add even more info on site to meet my time obligations.  And that was after adding more slides prior to leaving Albuquerque.


Lesson #3:  Tucson is HOT in June!

I’ve spent time in Tucson previously.  In fact, up until last year I was in Tucson every February for at least a week if not two.  I’m not sure why it didn’t register with me that June would be significantly warmer than February, but it didn’t.  If you travel there in the summer be prepared for hot weather, and don’t forget the sunscreen.  The sun seems stronger in the desert, and I managed to deepen my tan on the little short walks I took while I was there.


Lesson #4:  Rent a car while in Tucson.

I opted not to rent a car for this trip and I wish I had.  Because of the way flights worked out I had quite a bit of down time even after working some long hours from the hotel.  There is a mall within walking distance, and there are restaurants close by, but I would have had more fun if I had been able to head down to 4th street for some live music, or even been able to drive around the mountains a bit.  The other thing I realized is that I prefer to be able to hit a grocery store while I travel.  I’m used to having fresh veggies, fruit, and greek yogurt every day and I just don’t feel the same eating out for every meal.  If\when I go back I will be sure to rent at least a small car so I can get to where I want to go when I want to go.


Lastly, the reason I was asked to film in the first place is for the upcoming SQL Server Disaster Prevention and Recovery event on June 17th.  I am presenting on Backup Basics, and I am planning on being available in the chat room for any questions.  See you then!

Categories: SQL

Of course that’s how you spell sp_MSacquireHeadofQueueLock!

April 25, 2011 1 comment



If you are a blogger or speaker you probably cringe a little bit every time you have to reference a system object in your post or presentation and you see that little red squiggle under a system object name that you know is spelled correctly.. I know I did, and after the tweet below I knew that others were doing the same thing.


So, I stepped up to the challenge.


And the mssql.dic custom dictionary for Office was born. 

At this time the dictionary contains qualified and non-qualified names of system objects only (and only those that exist in sys.all_objects).  I’m sure that there’s a ton of other words that would be great to have in a dictionary, but hey – this is the first rev and I’m a busy DBA .  This has been tested on both Office 2007 and Office 2010, and the object names were pulled from SQL Server 2008 R2.


This is easily extended to create custom dictionaries for your environment – use the following query to pull object names for your specific databases:

SELECT AS schemaname , AS objname

INTO    dba.dbo.dictionary

FROM    sys.all_objects

        JOIN sys.schemas ON sys.all_objects.schema_id = sys.schemas.schema_id

You can either then save the results as text or insert them into a table in your management database (this is what I did).

Feel free to download the file and use it for all of your documentation and presentation needs – did I mention that this works in Power Point as well?

Anyways, the file is here: mssql  Once you have the file downloaded save it as a unicode text file called mssql.dic in the following path:

 …\appdata\Roaming\Microsoft\UProof\  (same place as CUSTOM.DIC and your ExcludeDictionaryxxxxxx.lex files for office)

the instructions to import it into Office are here (use method 2).

Enjoy, and please leave any feedback or suggestions in the comments – I will continue adding to this dictionary and releasing it as needed.



Categories: Community, SQL

SQL Saturday #73 in the mostly sunny state

April 12, 2011 2 comments

I’m freshly home from SQL Saturday #73 in Orange County California and wanted to jot down some notes before they slip my mind.  These days it seems I’m just as much a forgetful DBA as I am a lonely DBA 🙂

Denny Cherry (t|b), Andrew Karcher (t|b) and the rest of the crew did a great job planning and organizing SQL Saturday #73.  In fact, the only piece of feedback I have for them is that they could have used more signs on campus to guide us all to the Technology building for the event. 

I presented first thing at 8:30, and had a good number of folks for my Help Desk to DBA in 60 talk.  When I started I had about 10 folks in the room, and a few more trickled in as I spoke so I would guess I had right around 15 attendees.  They had some good questions and seemed to enjoy the material I presented to them.  If you attended, please drop a comment or an email to me with feedback – it’s the only way I can improve what I do!

Next up I watched Denise McInerney (t|b) give her DBA as Protector of Data session.  She had one of the best explanations of the difference between NULL, ‘NULL’ and ” that I’ve seen.  If you have a chance to see this session I suggest you go.

Lynn Langit(t|b then presented on Azure and did a great job as usual.  I don’t have a business case to use Azure yet, but I do like to keep up on the changes so that I am at least aware of what my options are. 

During lunch Denise, Lynn, Diana Dee, and I were on a WIT panel discussing ways to encourage the next generation of girls to look at IT.  We had a great conversation with the audience and I even learned of some new tools I can use with my munchkin at home to get her started with programing.  I will have a separate post out soon with a run down of tools  and options.   While I am on the subject – if you are in SoCal and so inclined, Lynn is looking for teachers during May.  Check out all the details here.

After the lunch panel I took some time to decompress and network a bit with Juan Soto (t|b).  After talking to him I was convinced that his was the next session I should attend so I happily sat in the back of his Access and SQL Server tips and strategies session.  I know, I know – I’m a DBA – why an Access session?  Well, whether I like it or not I have to support Access creations from time to time.  Juan gave me some ideas on how to make the best out of the situation as well as some tips and tricks I can take back to work.

The last session of the day for me was Denny Cherry’s (t|b),  Index Internals talk.  This was a new deck for him, and he ripped through it – even with questions.  It worked out though, as he had plenty of time to answer questions at the end and dive a bit deeper into corruption and a few other things.

I watched the drawings then went on to join other speakers and others at the sparsely attended after party.  That’s something that I can’t really wrap my mind around – why don’t folks go to the after parties?  I see them as fantastic opportunities to continue networking, ask deeper questions, and get to know my peers; yet it seems that the vast majority of SQL Saturday attendees don’t go to them.

All in all, this was yet another great SQL Saturday to attend – I mean really, what could be better than a full day of free training and networking with other SQL geeks?  The big question now is where to next?


April 4, 2011 3 comments

I’ve been tagged by Gabriel Villa (b|t) to participate in #mememonday.  Today’s meme is “write a SQL blog post in 11 words or less” and here goes my effort.

Despite a DBA’s best effort garbage in still means garbage out.

I’m tagging these fine folks from Colorado 🙂

Chris Shaw (b|t)

Rebecca Mitchell (b|t)

Categories: PASS, SQL