One DBA's Ongoing Search for Clarity in the Middle of Nowhere


Yet Another Andy Writing About SQL Server

Tuesday, March 20, 2012

Get Free Learning

A couple of different events going on today and tomorrow:

Today (03/20) at 3pm CDT - "Choose Your Own Adventure: Performance Tuning" at the PASSMN monthly meeting.  

What's that you say?  You're not in Minnesota?

Darn.  Oh well...

But wait - PASSMN is one of the chapters that runs their meetings as LiveMeetings so people everywhere can join in the fun. (There are several chapters that do this, as well as the PASS Virtual Chapters.)

Today's presentation is by Jason Strate (@StrateSQL/Blog) from Digineer and Thomas LaRock (@SQLRockstar/Blog) from Confio - they are both great presenters so don't miss out - details are here:


Then starting tonight at 7pm CDT (00:00 GMT) is the 24 Hours of PASS (#24HOP), with twenty-four individual sessions presented sequentially through the block.  The presentations are always great, and this year there are multiple SQL 2012 sessions so check out the schedule and register here:

Don't miss out on these great opportunities - you have been warned!

Friday, March 16, 2012

Help I'm not Sysadmin!

I am currently working on some client servers to upgrade multiple SQL 2008 R2 Express instances to 2008 R2 Standard.  On several of them, the only sysadmin is sa, and we don’t have the password.  BUILTIN\Administrators is only in dbcreator by the configuration in place, even though by the client standard it should be sysadmin.
To correct this I had to:

Stop the service:


Start the service in single-user mode (/m):


Connect a query window to the server and run the following query to add BUILTIN\Administrators to sysadmin:

sp_addsrvrolemember 'BUILTIN\Administrators','sysadmin'

then restart MSSQL$EDIENTERPRISE w/o any flags to return SQL to regular multi-user mode



Since 2005 (and at least through 2008 R2  - dunno about 2012) when SQL Server (any SQL Server, not just Express) is started in single-user mode, a Windows Admins who connects is a sysadmin even if BUILTIN\Administrators aren't actually a member of the sysadmin group - this allowed me to connect in single user mode and add the Admin group to sysadmins for real.

This can save your bacon (as in this case) or be a real security hole (if you have purposely tried to lock your Windows Admins out) - either way be aware!

Tuesday, March 13, 2012

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:
 ...and so on and so on.

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

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:

...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?