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

It's T-SQL Tuesday again, and this month Argenis Fernandez (@DBArgenis/Blog) is hosting with the topic of "Jack of All Trades, Master of None?"

This topic has become especially relevant to me in the past six months as I have left my previous life as an in-house DBA to become a consultant at House of Brick Technologies.  As an in-house DBA at a company (University of Nebraska-Lincoln, Alegent Health, and First National Bank of Omaha) I always  considered myself a specialist *and* a generalist.  I specialized in SQL Server (I didn't write VB code or administer Oracle instances) but I was a generalist in all areas of SQL Server (installation/troubleshooting/tuning/Profiler/DTS) with as much Windows knowledge as was necessary to gather Perfmon data and scan the Event Logs for relevant errors.  I usually didn't install my own Windows or rack my own servers; I installed the product after other professionals had performed these important functions.

Over the last few product versions of SQL Server, it has become more and more difficult to be a SQL Server generalist, especially working for a company (First National) where we developed almost none of our own software.  The SQL product keeps getting broader and broader - SSIS/SSAS/SSRS/SS-fill in the blank-S/Extended Events/Powershell integration/etc. etc. etc - that it seemed nearly impossible to keep up, especially as much of my job was interacting with software vendors and troubleshooting their code in our environment.  If I worked with SSRS it was because we purchased an application that required SSRS and that app usually used it (or misused it) in a very particular way.

As I have written about previously, just to stay a general DBA Microsoft is leading us down these paths as they killed DTS (forcing SSIS) and now as they have deprecated Profiler (forcing Extended Events).  I keep waiting to hear that they will deprecate T-SQL in favor of Powershell. {-:

As I noted after reading Rob Farley's T-SQL Tuesday blog "Be the Surgeon" I have always been told it is better to be the specialist (the surgeon) - and many of the blogs and Tweeters I follow exemplify this, whether it is:

• Mr. Extended Events Jonathan Kehayias (@SQLPoolBoy/Blog)
• Mr. SSIS Andy Leonard (@AndyLeonard/Blog)
• Mr. DBCC Paul Randal (@PaulRandal/Blog)
• Mrs. Indexing Kimberly Tripp (@KimberlyLTripp/Blog)
• Ms. Love Your Data Karen Lopez (@Datachick/Blog)

 ...and so on and so on.

(Of course the extreme example of this is here:)


http://www.bazingajournal.com/2012/03/words-to-live-by.html

Do I think these SQL Server "specialists" just know Extended Events, or SSIS, or DBCC? Of course not - these people and the others like them are geniuses about the product, and you can't know SSIS without knowing T-SQL, and how indexing works, etc.

The  other side of this is the generalist - the all-star as a generalist in our current SQL Server world is the Microsoft Certified Master (MCM).  To become an MCM you have to first be an MCITP in Database Administration and an MCITP in Database Development, followed by additional testing and labs.  Some MCM's may debate this, but to me the requirement to be both a DBA and a developer is by definition generalist, and it is the reason that I crossed the MCM off of my list of potential certifications - I can't believe I would ever have enough time to put into becoming a certified Database Developer to get past the prerequisite.  The other large batch of people I follow fall into this pile - not all MCM's, but all of them not known for a specific piece of the product:

• Brent Ozar (@BrentO/Blog)
• Kendra Little (@Kendra_Little/Blog)
• Jeremiah Peschka (@peschkaj/Blog)
• Thomas LaRock (@SQLRockstar/Blog)
• Robert Davis (@SQLSoldier/Blog)
• Argenis Fernandez (@DBArgenis/Blog)

...and many, many more.

(As always when I print a list like this, there are plenty of people beyond those listed here - I currently follow 425 people on Twitter and roughly 2/3 of them are SQL Server people - #sqlfamily)

When I came to House of Brick Technologies it was to expand my horizons and learn VMWare, as we primarily consult for implementing virtualization for critical applications (AKA databases).  I have limited VMWare experience (some of my past SQL Servers were VM's {-:) but House of Brick was willing to hire a strong SQL Server DBA and teach them VMWare, reasoning that this is simpler than trying to turn a VM expert into a DBA.  I have been through VMWare Boot Camp and am beginning to work double assignments with some of our SQL Server/VMWare pros to gain some practical knowledge with the product.

So I end with a question - am I becoming more general or more specialized?  I am expanding my horizons into a new technology (general) but I am starting to limit myself into a speciality of Virtualization DBA...

So which is it?



http://www.flickr.com/photos/torek/4444673930/sizes/m/in/photostream/

Double-Dash Comments, the Hidden Menace

While writing my last post on cleaning up old maintenance plans, I was reminded of something I heard in a presentation once (and unfortunately I can't remember who said it) that has stuck with me for the last several years:

Word Wrap is Your Enemy.

Why is this?  If you use double-dash inline comments:

--This is a comment, at least it is supposed to be.

SELECT COL01, COL02, from dbo.mytable

...and your code gets opened in Notepad/Wordpad or some other editor that utilizes Word Wrap, it could look like this:

--This is a comment, or at least it is sup

posed to be

SELECT COL01, COL02, from dbo.mytable

Good luck executing that code after it has been copy-pasted in this format into a query tool....something like "Improper syntax near 'posed'"

Even if you don't try to execute it, it can be difficult to read and troubleshoot when similar pieces of code are near each other and one line or the other is double-dash commented out.

So the hint to this is always, always, ALWAYS use star-slash comments /* */ even if your comment is short (even a single word) - it's not a bad habit to get into and doesn't cost you much (two extra characters) to protect you from this problem.

/*This is a comment, or at least it is sup

posed to be (and still is!)*/

SELECT COL01, COL02, from dbo.mytable 

Hope this helps!
 

Cleaning up Old Maintenance Plans

This is something I trip over every now and then on systems we inherit from our clients, and I thought I would write it up in case it helps someone else {-:

In the most recent case, I was looking at a SQL Server 2008 server that had been upgraded from SQL 2005 without installing Integration Services, so the maintenance plan jobs were failing with an error like this:

The SQL Server Execute Package Utility requires Integration Services to be installed by one of these editions of SQL Server 2008: Standard, Enterprise, Developer, or Evaluation. To install Integration Services, run SQL Server Setup and select Integration Services. The package execution failed. The step failed.

Rather than installing the relevant hotfix (http://support.microsoft.com/kb/961126) or upgrading to a service pack that they haven't tested, I took another step in my never-ending battle against SQL Server Maintenance Plans in favor of Ola Hallengren's Maintenance Solution.  If you don't already use it - check it out (all the cool kids are doing it {-:)

I installed MaintenanceSolution.sql and configured schedules comparable to the broken maintenance plans, and then went to delete the maintenance plans and their jobs:

The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'job_id'.

Sigh.

I have seen this more than once for a variety of reasons, including plans that get deleted before the jobs and just flat out broken maintenance plans - but the fix is relatively easy (if you're OK deleting rows from tables in MSDB):

/*Look in the plans table to find the plan you wish to delete*/

select * from msdb..sysmaintplan_plans

/*Copy-Paste the Plan's ID (will be in GUID format) from the previous query into the where clause of the following statements and run them to clean up the foreign key relationships in the proper order*/

delete from msdb..sysmaintplan_log where plan_id = '11111111-AAAA-BBBB-CCCC-222222222222'

delete from msdb..sysmaintplan_subplans where plan_id = '11111111-AAAA-BBBB-CCCC-222222222222'

delete from msdb..sysmaintplan_plans where id = '11111111-AAAA-BBBB-CCCC-222222222222'

/*Check the plans table again to verify the plan id gone */

select * from msdb..sysmaintplan_plans

At this point the maintenance plan is gone and you can now safely (and successfully) delete the offending maintenance plan job(s) from Management Studio or from the command line.

Hope this helps!