All posts by Sean McCown

I am a Contributing Editor for InfoWorld Magazine, and a frequent contributor to SQLServerCentral.com as well as SSWUG.org. I live with my wife and 3 kids, and have practiced and taught Kenpo for 22yrs now.
Admin

T-SQL Tuesday: A file delete exercise

1 Comment

This is my submission for T-SQL Tuesday on Files and filegroups.  You can find the blog tsql2sday.

 

One of the things I’m known for in the shops I work in is giving my DBAs some interesting exercises.  And sometimes they even come with prizes.  This is an exercise I’ve been giving off and on for a few years now.  I typically point them to a test server and send them to the MyDocs folder under one of the profiles.  In there is a list of files that I want them to delete.  Typically the email has similar verbiage to this:

 

I have an assignment for you guys.  I want you to go to the following location and delete this list of files.

And whoever gets to that server first and deletes those files first gets their pick of these 3 books.

 

They always rush to be the first there and I can hear their initial failure one at a time as they find out it’s not just an exercise in speed.  Just for fun, here’s a screenshot of the files I have them delete.

 FileList

About that time I follow-up with another email.  It says:

You’ve just discovered that the files cannot be easily deleted.  The only hint I’ll give you is that I did it by highlighting a feature in the new version of SQL Server. 

Good luck.

 

For a few years now, the race has been whoever could find the obscure setting in filestream the fastest (because they figure that’s the best place to go).  There has to be something in filestream that’s doing it.  So they dig through everything filestream-related they can find.  They dig and they dig and they dig.  They put together some test DBs and do their best to recreate the issue.  I hear all kinds of wacky theories flying around.  But they never hit that magic bullet that makes it all come together (if you don’t mind me mixing metaphors).

It typically takes them 2-3 days before they give up for good.  I’ll tell you something… in the years I’ve been doing this I’ve never had anyone actually get it with no prompting.  So then at the end, we come together and talk about the process they went through to troubleshoot this issue and their reasoning.  They talk about handles and locked files and permissions, and all the new features in SQL Server that would cause something like that, but they’re just not sure because they couldn’t find anything on google and they don’t know the undocumented XPs, etc. 

And as it turns out, this exercise has nothing to do with the files at all.  I mean, it does, but it’s really meant to serve a much greater purpose.  I want to teach them that the customer always lies, or does his best to throw you off track by sprinkling whatever piece of misinformation he has into his request.  And you never know what they’ll do to their systems.  I want to teach them the following things:

  1. Listen to the wording.
  2. Users lie.
  3. Think for yourself, don’t get distracted by what they tell you.
  4. Ask Questions… for the love of GOD ask questions.

 

So what’s the resolution?  Tell ya what, I’ll give you the code to repro it and that’ll tell you what the issue is.

create database UserDB

on

(

name=UserDB1,

filename=’c:\users\sean.midnight\my documents\Master20120105.bak’

),

 

(

name=UserDB2,

filename=’ c:\users\sean.midnight\my documents\BlogPost.docx’

),

(

name=UserDB3,

filename=’ c:\users\sean.midnight\my documents\Expenses.xlsx’

)

logon

(

name=UserDBLog,

filename=’ c:\users\sean.midnight\my documents\LovePets.pdf’

)

 

It’s not always exactly like that, but this is one variation of it.  Now, you may wanna say that I lied, but didn’t.  What I said was that this hinged on a feature in the new version of SQL Server.  But I didn’t say it was a new feature.  It is in fact a feature in the new SQL Server, it just so happens to be a feature of all of them (well, most of them).  And that feature is the ability to create DB files of any name and any extension you like.

Happy T-SQL Tuesday, and I hope this fits in with the theme.

Admin, Development, Time-savers

Nothing beats practice

1 Comment

For a very long time now I’ve preached to my various teams the value of doing your 20s.  And I don’t expect most of you to know what that is, so here’s a quick explanation.  I believe there’s certain syntax you should know by heart.  Things like backup/restore, DB creation, working with user accts, etc. should be so ingrained you never forget them.  And the only way to do that is to do it again and again and again.  So when I start new DBAs down their road to discovery, I start them on their 20s.  And that means writing the command you’re working on 20x a day for 2 weeks.  And by the end of the period, you’ve written it at least 200x.  Now, in the case of backup/restore, I typically pick a test server with around 10-15 DBs (I often create empty DBs) on it and make them type the commands for every DB 20x a day for 2 weeks.  And that means that they’re writing the commands 200x for each DB.  And a little quick math tells you that the total they’ve written the backup command by the end is at least 2,000x.  Now, name me something you’ve done 2,000x and have forgotten easily. 

The whole point of this is to give them tons of experience with the commands in a very short period of time.  Innovation and understanding don’t come when you’re struggling to remember syntax and they don’t come if you don’t know any of the options available to you.  So by forcing my DBAs to learn like this, I’ve found that they really start to become competent and ultimately they appreciate that I forced this on them.  And it’s incredible how simple things start getting when you know what you can and can’t do and now that you know how to code it you’ve got a world of options available to you.  And I still find way too many DBAs are button monkeys and don’t know anything that isn’t presented to them in a wizard.

So anyway, the main reason I wrote this is to say that it’s proven itself to me today.  We needed to add a PK to a table and I was in a room with a bunch of SQL devs.  They started to lookup the syntax and I said, wait, give me a crack at it.  I sat down and busted it out without any slips.  And the thing is that this is one of the things I’ve practiced a lot.  But I haven’t touched it in almost a year so I’m a little surprised to find that it didn’t leave me.  Then again, when you do it as much as I have you won’t forget it anytime soon.  So my point here is that if you practice enough you can go a few months without doing it and you’ll still be ok.  And the funny thing is I didn’t even really remember the syntax myself.  My fingers did it for me.  I love how muscle memory works.  So hey, if you really want to get good at SQL then practice some of the more common syntax.  Do your 20s.  You’ll really be glad you did.  And you’ll get things done a lot quicker cause you’re not always looking up basic syntax.

Uncategorized

The new job problem

1 Comment

Ok, so you’re in an interview and their questions are finished and it’s time for them to answer yours.  So you start asking them different Qs about their shop.  You ask about servers, versions, support, team layout, upcoming projects, etc.  You cover all the bases and it all sounds pretty good.  You leave and an offer comes in a few days later and you accept. 

Fastforward to your 2nd week on the job and you’re starting to discover something.  All these people around you are idiots.  Not a single one of them really knows what’s going on and even worse you’ve heard a couple things that make you believe that all grand ideas the hiring manager had in your interview were just HIS grand ideas.  They’re things he’d really like to do.  As it turns out though, nobody else has neither the desire nor the skillset to make any of it happen. 

So what do you do now then?  You’ve saddled yourself to this new gig where you’re surrounded by morons and everything you came here for is now defunct and you know it.  Personally, I would probably start looking for another gig in a hurry.  The older I get the more I just can’t bear to work in those types of shops.  And I really wish that the interview process were such that you could tech screen the guys you’re supposed to be working with.  As it stands that kind of thing is presumptuous.  It would be considered pretty rude to come in and interview someone for their own job.  But what are you supposed to do?  Don’t you have the right to make sure you’re coming into a good shop too?  They don’t want to hire someone who’s an idiot, but certainly they can recognize that we don’t want to be put in that position either, right?  Well, I haven’t seen it yet, but I’m hoping that one day we can get to that point.

Unfortunately, I don’t have any answers other than to cut your losses and maybe get a little more clever about how you vet the next shop.  Maybe ask them Qs that are meant to see how they’re going to design something that they talked about and see if they know what they’re doing.  I’ve even flatout asked the hiring manager if the plans were just his or if there were an active project to make it happen.  You can also find out a little about their office politics by asking the right Qs, but you have to be sneaky.  Ask them about their top 5 support issues.  That’ll tell you a lot.  For example if one of their big issues is that they keep having unauthorized prod changes, then you know that they’ve got devs or end users in the DB.  So now you can ask them outright why those users have rights to prod.  Then you can ask what they’re doing to change it and what the roadblock is.  This can tell you a lot about the experience of the upper mgmt as well as the hiring mgr because based off of his answers you’ll know if he knows what he’s doing and if the company is too immature to keep their users under control. 

You can also ask them about their HADR.  Ask them what they’ve got in place and whether their happy with it.  Then you can ask what they’d do differently and why, etc.  This line of questioning can take you a long way in finding out things about the people you’re about to work with.  Maybe it’s a SQL group that’s pretty on the ball but when you ask why something is done the way it is you find out that the windows team is poor and blocks everything they try to do.

You can also ask the guy how he got started in the business and what his experience is.  I’ve asked hiring mgrs outright if they were technical or not.  The point is that while you can’t whip out your big list of Qs like if you were on the other side of the table, you can still find out quite a bit with a few well-placed questions.  The trick is to gear it toward something he said so it just sounds like you’re asking more info about the shop.  When what you’re really doing is finding out if these are the people you wanna work with.

Also, don’t fool yourself into thinking that if someone answers the Qs well that they’ll be good.  All the discussion boils down to is that they can at least talk the talk.  And I’ve met plenty of guys who can do that.  You get them in the interview and they say all the right things and really appear to know their job.  But when you get them in your shop they somehow can’t do the simplest things.  So this is no sure-fire way to vet your new team, but it’s all you’ve got. 

Another big point of this is to start you thinking in this direction.  You don’t have to just shut up and take what they give you.  Too often people are just so grateful to be offered something that they take the first offer they get no matter how bad the company is.  And then they sit there for years and put up with garbage every day.  But you don’t have to.  You’re (hopefully) a professional, which means you’re in demand.  You should be calling some of the shots.

Development

SODA Architecture

I was asked by a user to write this post.  And while I’m gonna beat around the bush a little, it’s only to give you some background so you understand the context of what this is all about.  Because everything’s connected and my goal here is to show you how a bunch of these things are related, especially when you break them down into essentials.

Quite often you’ll have a DB that just doesn’t perform no matter what you do to it.  You’ve spread-out the I/O, you’ve indexed the hell out of it, and you’ve thrown hardware at it.  And maybe it’s not strictly performance you’re concerned with.  Maybe you’re concerned with backup/restore time, or security, or something else.  Well, enter SODA.  SODA stands for Service-Oriented Data Architecture.  And what it really means to do is to split your DB into different pieces based off the services they’re going to perform.  For those of you who are really DB literate, that’s all you need, but I’ll go ahead and flesh this out a little bit for those of you who would like a little more explanation.

Background Basics

Now, before I really get into SODA itself, I’m going to go into some performance basics to give you some background on how this fits into the grand scheme of things.  Let’s take a look at disk access. 

Of course, whenever you need to get resting data, you need to get it from disk, and whenever you get it from disk, that’s a physical I/O that needs to be performed.  And like anything else that performs physical work, you can overload the disk and requests start to build-up.  And this is what the disk queue counters in perfmon are all about.  The disk queue counters tell you how many I/O requests are currently waiting in line (the queue).  And of course the larger the queue, the further behind the disk is. 

That’s not entirely relevant these days because most of us are using some kind of SAN and these counters are harder to measure on SANs.  The reason is because in order to interpret disk queues you need to know how many disks are in your array.  Every disk array in your system has an acceptable disk queue limit.  And what that functionally means is that if you have a single disk, say in your laptop, that single disk has a number of requests it can fit in its queue before it can be considered “falling behind”.  So there’s a certain level of queuing that can happen and still be considered normal behavior.  And of course, with all the different drive vendors and types of drives out there, you can imagine that this acceptable queue number would be different for them all.  And technically, you’re right.  If you want to know the exact acceptable disk queue for your given drive, you need to dig through the vendor specs and then take a lot of the numbers in there and plop them into a nice long formula that spits out the number of the acceptable disk queue for that drive.  And I’ll tell you right now that’s a bit hard to manage for all but the most dedicated IT folks (and even for them too, really). 

So the way we get around this is we just use a standard measure of 2 per disk.  So for any given single disk you can have an acceptable disk queue of 2.  That means that if you have an array with 10 disks in it, then you can have an acceptable disk queue of 20.  Get it?  That’s why this measure isn’t really as useful on a SAN because quite often you don’t have any real visibility into the specs of your SAN arrays.  Without this information, you can’t really say what an acceptable disk queue would be because you don’t know how many disks are in the array.  And, SANs are all mostly shared amongst many apps anyway so you don’t even have dedicated storage to begin with.  The counter you’ll use to diagnose disk problems on a SAN is avg. disk secs/read (or write).

Ok, so that seems like a pointless digression, but it ties in.  Like I said though I just wanted to give you a little background so we’re more or less on the same page.  Now that we have that background, we can say that in a situation where we’re disk queuing, we have an underlying cause for our issue.  What would you say that underlying cause is?  Go ahead and take a second to consider it real quick before I give you the answer. 

The answer is we have disk queuing because we’re overworking the disk.  We’re pushing more work at it than it can do so it has to stack up that work.  I know that sounds simplistic, and it is, but it has to be understood before we can go any further.  With that understood, how can we fix this issue?  This is the heart of what I want to talk about in this piece.  There are 2, and only 2 ways to fix this issue.  Those 2 ways are: decrease the activity on the disk or increase the throughput on the disk.  That’s it.  There’s nothing else you can do to fix a disk queuing issue.  It’s the methods for doing each of these that can get involved and where you have the most leeway to be creative.  Hang on, I’ll show you how this ties into SODA soon enough.  And of course I’m not going to get into a full discussion here about all the different ways you can solve the issue, but to give a couple examples, a common way to decrease the activity on the disk is to separate the different files onto different LUNs, and a common way of increasing the throughput is to add disks to the LUN.  Again, this isn’t going to be a full discussion on that topic, so I’m going to stop there.  We have enough info to continue with our current discussion on SODA. 

Let’s look at another common performance issue we have in DBs… that of table performance.  Again, you’ve got a single table that could be a bottleneck and you’ve got to make it perform faster.  And again, your choices are limited.  There are really only 2 ways to solve this bottleneck.  You can decrease the volume of the requests against the table, or you can increase the throughput of the table.  Well, quite often these 2 solutions go hand in hand, and while this isn’t a discussion on table performance, a couple of the more common ways to solve this problem are:

a) improve the indexes to decrease the volume of requests (by decreasing the I/O)

b) partitioning the table and placing the different partition data on different LUNs to increase the throughput of the table itself. 

So hopefully you’re seeing a pattern form here.  For any given bottleneck you’ve really only got 2 choices:  Decrease the requests or increase the throughput.  It’s in the execution of either of those where the art comes into performance tuning.

Now let’s talk about SODA.  SODA stands for Service-Oriented Data Architecture.  And its main purpose is to help get around some kind of bottleneck, typically at the system level.  That’s not the only reason to use SODA so nobody email me and say I’m an idiot cause I said the only reason to use SODA was for system-level performance.  So anyway, SODA is really just a way to break up your DB into pieces that are more manageable.

Let’s take a quick example.  Take a DB with something like 3,000 tables and thousands of SPs.  That’s bound to be a very busy system and you could easily run into both memory and CPU bottlenecks.  What people will commonly do is split up the DB based off of the services it provides.  They may split out the Shipping components from the rest of the DB, and they may split out the Orders portion as well.  This huge DB we started with could potentially be split into many separate DBs based off of the services or the functions they provide.  Then all the devs have to do is just teach these new DBs to talk to each other (usually through SSIS, BizTalk, web services, or Service Broker, etc).  You now have a DB architecture based off of the services provided by each one.  You have a Shipping DB, and an Orders DB, and a Manufacturing DB, and an Inventory DB, etc.  This is what SODA is.  It’s splitting off the different portions of a large DB into their own DBs based off of their services.  Now these DBs can go onto different servers and have their own security, resources, etc.  So you can see how this is solving the bottleneck problem.  You’re decreasing the requests on each single DB and increasing the throughput by giving them their own resources on their own servers.  And of course they can stay on the same server if you like.

And hopefully you can see why I wanted to give you the background on solving bottlenecks so you could see that this is no different.  It’s the same thing as before, just at the DB level.

Now that I’ve covered SODA, I think it only fitting that I also cover its counterpart, DDR.  DDR is Data-Dependent Routing, and it’s yet another way to manage the influx of data you’ve got coming in.  Imagine the same DB as above with many different modules inside it.  Only this time you’re not going to split it up by function, but by data range.  So maybe you’ll keep 5 copies of the exact same DB and each copy will contain a different region of data, or maybe a different country.  This is actually pretty common practice.  I’ve seen many apps that split their data apart like this.  And there are several reasons for it.  It could be performance, data size, archival, etc.  And the reason it’s called ‘data dependent’ is because quite often there’s a layer on the front end or maybe even the middle tier that decides where the request should go.  So for example, maybe you’re looking up customers in TX.  You submit the request from the front end, and it has knowledge of where the different regions are stored and it dynamically sends you to the DB that has the TX data.  So every request has to go through this process because every request potentially goes to a different DB.  These DBs can be on different servers, but that’s not always the case.  In fact, most of the time whenever I see a server with hundreds or thousands of DBs it’s because this design was used.  A good example of this is with online training companies.  I’ve see a few of them that have chosen to keep all of their corporate customers in their own DB instead of having everyone together in the same DB.  There’s no need to put them on different servers because it’s not a performance decision.  It’s just an organizational decision.  Maybe it’s based on security, maybe it’s based on data size, or maybe it was adapted from a much smaller app and this was the best way they had to expand their business model with the least effort. Now, whether that’s a good design decision or a bad one doesn’t really matter here because putting all customers in the same DB and putting them in separate DBs both have their pros and cons so it really just depends on where you want your pain to reside.

So just to summarize:

SODA – putting each function or module in a DB into its own DB.  The schemas in the new DBs will be different as they all have different tables.

DDR – Keeping several identical copies of the same DB but with different data.  The schemas will be identical, but the data will be based off of some business rule you’ve chosen.  These rules can be date, region, country, alphabetical, etc.  It can be anything you like.

Ok I think that’s about all I have on this topic.  I know I went in kind of a roundabout way of talking about this, but I really felt you should have a little context.  Don’t think of these as completely separate things because they’re not.  Everything is related and if you can break them into basics then you’ll be much better at coming up with solutions.  And everything I talked about today is just another way to either increase throughput or decrease requests. 

Development

Why load test?

I was recently asked to document some justifications around buying a load testing tool like Benchmark Factory.  And for the record, while it’s not the only tool out there, I’m a huge fan of Benchmark Factory.  I’ve used it for years and it’s a tremendous tool.

So anyway, I just jotted down some things in an email and here I’ve just pasted it directly into this post.  And it’s not an inclusive list, but I think it covers pretty much everything you’d need to get someone to write a check.

Here are a couple use cases for load testing builds before pushing into prod:

  1. Lock contention – there are 2 types of lock contention, live locking and deadlocking.  Both of these can wreak havoc on a prod system and should be tightly monitored and controlled.  And even minor changes to simple code can cause a drastic increase of either of these 2 locking scenarios.  And while there’s almost no way to eliminate these effects in prod entirely, you can ensure that you greatly reduce them by proper planning.  Currently the best way to mitigate these issues in prod is to catch them before they go into prod.  And the best method for catching these scenarios in pre-prod environments is to perform load testing.  If your load test mimics your prod environment closely enough, then you should see this increased activity and make the necessary changes before it impacts your customers.
  2. Rollout testing – not all DB changes require the system to be down.  And being able to load test with a production-level scenario will ensure that you don’t take unnecessary downtimes because you’ll know exactly what to expect when the rollout happens.
  3. Capacity planning – this is perhaps the most overlooked, but also one of the most important aspects of production work.  Being able to certify your server to a certain number of users or a certain workload is essential in capacity planning.  In these load tests you can increase the number of users, increase the amount of data, etc. to see how your system will behave as your business grows.  You can also see where it will fail first.  So as your business doubles, where will you see the effects first?  Will it be CPU, disk, blocking, etc?
  4. Performance – Not only can you test the perf of the system in general, but you can also test the performance of specific methods of doing things.  If you’re developing a new process and you need to see which method will perform the best, there’s no substitute for load testing it to see which one is best.  And you also see if there are going to be any locking or indexing issues.  For developing new processes, there’s no better way to solidify a new method.
  5. Upgrade – test upgrades of both the DB system itself, but also patches and hardware.  Often times a perceived improvement doesn’t materialize in prod or can even have unanticipated side effects.  Load testing these scenarios can greatly reduce surprises as well as ensure you’re putting efforts where they need to be.
Uncategorized

Healthcare isn’t ready

8 Comments

I just left the healthcare industry for the 2nd time and it’s sad the level of ignornace and superstition that exists around computers… and SQL especially.  The entire industry treats computers like big electronic pieces of paper.  They print things they can easily email, they manually enter in things they could easily write a form for, and they perform repetative manual tasks they could easily script.  It’s pathetic how far behind the industry as a whole is and the people who work in it are so close-minded I don’t see how they ever get anything done.

Part of the problem is the doctors.  Doctors think that because they’re doctors that they know everything.  Several times I’ve had one doctor or another tell me specifically how they wanted me to do something in SQL.  They didn’t know the first thing about it, but they heard a few terms here and there so they decided to run the show.  And here they are in meetings insisting that I follow their HA architecture that was just ridiculous.  I got a reputation in my company for being difficult to work with because I always called them on it and told them to let me do my job.  Then they would complain and my boss would be at my desk the next day.  It’s just incredible ego to think that you’re a expert in all fields because you’re an expert in your own.

However, doctors aren’t the only problem.  Vendors are also a huge problem because they’re very slow to adapt to new technologies.  And by slow, I mean 15-20yrs too slow.  We’ve had so many vendors who only code against SQL2K.  Their support personnel is pathetic to say the least as well.  These vendors know nothing.  And they’re guiding hospitals in their implementations.  And of course now you’ve got the blind leading the blind because while there’s nobody at the vendor who knows what he’s talking about, there certainly isn’t anyone at the hospitals to call them on it.  And when they do get someone in there who knows what they’re talking about they can’t keep them because what really good IT person wants to work with an entire floor of people who don’t know the first thing about IT?

The biggest issue we had with staffing was that everyone who does the hiring thinks that you have to have hospital experience to be able to work in IT at a hospital.  So they end up hiring ex nurses, or other clinical people and give them jobs as programmers, system admins, etc.  These people don’t know the first thing about being in IT or about C# yet they’re given positions based off of their hospital tenure.  So someone who wanted a career change could come in as a Sr. Programmer yet they’ve never even had a simple online coding course.  So now they’re in there trying to figure this stuff out.  They’re architecting solutions that they could barely qualify as end users for.  And anyone in IT who knows what they’re doing has to put up with this idiocy.  And make no mistake… it is idiocy.

The industry itself has too many older managers in it and they need to bring in some fresh blood that actually knows something about IT and how to actually get things done.  As it stands they’re just too scared of the change, too scared of the data, too scared of being sued, too scared of pissing off the doctors, and too scared of technology in general.  Oh sure, they’ll bring in iPads for the doctors to carry around, but big deal.  They’re not doing anything cool with them, and everything they put out there costs tons of money in support because they weren’t put together correctly.  Want a perfect example of how far behind they are?  Whenever you go to a new doctor you still have to fill out all that damn paperwork by hand don’t you?  You have to put your name, address, SSN, DOB, etc on like 9 forms.  Doesn’t that sound like something they should be able to get past by now?  And there’s more to that specific story than just being afraid of computers.  That particular one is caused by the system itself.  I won’t go into specifics though.  I’ve also seen plenty of people print online forms, fill them out, and then scan them back in and store that into the DB in a text column.  Seriously dudes?

So what can they do to change?  How can healthcare move into the 80’s?  For starters they can hire some younger more hip managers who understand how IT works and the benefits it brings, and give them the power to do what they need to do.  Next they can stop hiring from hospitals.  C# coders, or SQL guys don’t have to know crap about your business.  They have to know their business, which is IT.  And they’ll have to pony-up the money for some real IT folks.  IT folks aren’t going to work for peanuts… not when they can go somewhere else and get 20-30K more.  Oh yeah, and you’re also going to have to start treating them like they’re professionals.  IT guys don’t want to hear how much the doctors know about IT.  They want you to let them do their jobs.  So seriously, stop treating them like they’re nothing compared to the doctors.  Doctors are essential to hospitals, but your IT staff is too.  It’s getting so that hospitals are crippled without IT.  So why do you still insist that all IT guys are the same?  Hell, even all janitors aren’t the same.  I can easily tell the difference between one who cares about what he does and one who doesn’t.

Here’s a scoop for you.  Healthcare is going to need to get their act together or else.  The government is mandating that everyone have their health records in a meaningful use format by 2015 so the time of getting by on the idiots you’ve got is over.  You’re going to have to get some real talent and do what it takes to keep them.  If that means paying them a good salary, and listening to them, then all I can say is ‘you poor baby’.  Hospitals jump through hoops all the time to attract some new doctor because of what he brings to the network.  If anyone in healthcare is reading this then you’d better start planning now.  Start gathering some talented IT guys and let them do their jobs.  And NO, before you ask, you don’t know what IT talent looks like.  Get someone to help you find that talent.  And I’m not talking about recruiters either.  Go to the Microsoft MVP site and google someone in the field you’re looking for and start emailing them.  Ask them to help you interview a few guys.  I’m sure they’ll charge you a little, but it’ll be more than worth it.  Then once you get these guys on staff don’t treat them like 2nd-class citizens to the doctors.  You’ve got no choice anymore.  You have to do something.  You can’t keep this up.

My guess is that it’ll probably take about another decade before this starts really turning around though.

Admin, Powershell, Time-savers

Why is adopting Powershell so hard?

3 Comments

One of the biggest battles I fight when I teach PS or show it to different companies is adoption.  DBAs and managers just don’t get why they should be using PS to manage their environments.  And the reason so many of them are slow to adopt is because the right message is very hard to come by.  In fact, I see the wrong message all the time.  This was driven home again just now as I received my “SQL Server Pro UPDATE” email (read it here).  The editorial was about why DBAs should care about PS.  It was written by Michael Otey (pronounced Otee) and I’m sorry to say that as the Sr. Technical Director he’s completely missed the mark.  He’s managed to relegate the most important management tool DBAs have to a mere sideline player that allows you to combine SQL work with OS-level work.  Dude, you’ve completely missed the boat!!!

I’ve said this a number of times and I’ll say it again here.  Let’s get the right message out about PS.  Let’s start telling everyone that truth about it and that truth is that you’re going to get left behind if you don’t know PS and you’re not going to be able to provide true value to your company.

I’m a working production DBA in a very large shop.  And I can tell you with absolute authority that PS is more than just a way to give you some OS-level functionality in your SQL processes.  PS is vital to the management of my shop.  The primary role of PS is to allow you to manage hundreds of objects at once with just a few lines of code.  As you’ve seen in my videos again and again, and in my classes again and again, you just can’t manage any decently sized environment by hand.  It’s just too much and it’s accident-prone.

Now, a large environment doesn’t have to mean hundreds of servers.  Oh no, it can mean dozens of DBs on a single server.  It can mean dozens of schemas in a single DB, or dozens of tables, SPs, views, etc.  Or it can even mean dozens of jobs or logins.  It doesn’t matter what you’re cycling through.  The point is that PS is much more efficient at managing these objects than T-SQL will ever be.  There may be a built-in SP for running a T-SQL statement against all the DBs on your server, and there’s even one for running something on all the tables I believe.  But there’s nothing for all the jobs, or SPs, or views, or logins, or credentials, or any of the other many objects you can manage natively with PS.  With just a few characters of code you can manage all the tables in your DB.  And with just a few more characters you can do that same operation against all the DBs on your server.  And with yet just a few more characters of code, you can do that same operation against all the DBs across as many servers as you like.  THAT’S THE POWER OF POWERSHELL.  It’s more than just a way to manage files while you’re doing some T-SQL operations.

A perfect example is adding user accounts to DBs on several boxes.  We quite often get contractors who come in and do specific work for us and they need to be given rights to all of the environments for a given application.  So this one we have has 500+ DBs on each server.  There are 7 prod servers, 3 QA servers, and 5 dev boxes.  All of them have 500+ DBs on them.  So that’s 15 servers, and at least 7500 DBs that I have to add these users to.  And using windows groups will only take you so far because when the next contractor comes in he may need different rights so you still find yourself modifying the perms regardless.  The point is I go through this exercise about once every 2mos and PS has made it a trivial matter.  And that’s just ONE example.

The time has come to stop downplaying the role of Powershell in SQL Server.  DBAs need to be running to it.  We’re being asked to do more and more with less time and fewer resources.  And there are too many courses being taught, too many SQLSaturday sessions, too many videos (FREE on MidnightDBA.com) for you to remain in the dark any longer.  I’ve managed 900+ servers by myself using mainly PS.  I’d say it goes quite a bit deeper than being a helper to keep you from using xp_cmdshell.

Admin

Cluster Upgrade Error: 19019

So we upgraded a cluster from 2008 to R2 tonight.  We upgraded the inactive node to R2 SP2 and then went to failover the cluster so we could upgrade the other node.  But when we tried to failover it failed.  The only thing the critical events for the resource said was that it couldn’t failover.  So no help there.  The only other error was in the event viewer and it said that the shared component upgrade failed… even though nothing in the upgrade process failed.  There actually were other errors in the event viewer but I’ll let the link below cover those.  The big important error was Error:  19019.

I remembered something from a few yrs ago that involved a registry fix so I started looking at the registry.  After opening regedit I went to the most logical place:

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL10_50.SQL2008

Once there I saw the Setup folder, which seemed like a good place to start.  Inside there I saw a key called SQLDataRoot and its value started with E:\

Well, I have a pretty standardized environment so I know the SQL root was supposed to be on D:.  I changed the location to D:\ and the SQL Server resource came online instantly. 

So again, that registry key is:

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL10_50.SQL2008\Setup\SQLDataRoot

This is not only an interesting fix, it’s also a testament to what having a highly standardized environment can do for you.  I could have compared this entry with node1 to see if they matched, but it may not have even been flagged as an issue if we  didn’t always install SQL on D:\.

So once I had the fix in place, I had something to search for, so I went to google to see if I could find anything about it out there.  I came across this great blog post that even gives more detail on this error, so rather than try to reproduce the entire thing here, I’ll just point you to that post. 

http://blogs.msdn.com/b/jorgepc/archive/2010/10/14/onlinethread-sql-cluster-shared-data-upgrade-failed-error-installing-cumulative-update-in-sql-server.aspx

 So this is why I call myself the bumbling DBA.  I don’t actually know anything, but I manage to find my way through solutions somehow.  And I know I’ll get lots of comments on this with everyone telling me they knew this cause they’ve seen it 100x and all I can say is bully for you.  I’ve only seen it once and it was a few yrs ago.  I don’t get out much.

Uncategorized

SQL PASS Summit Day 2 Keynote

Here are today’s announcements.

After 5 years in Seattle, PASS Summit is traveling to Charlotte, NC, in 2013 to bring the world’s largest SQL Server and business intelligence conference closer to database pros who may not have been able to attend in the past.

PASS Summit 2013 will be held at the Charlotte Convention Center, in the heart of the Queen City, Oct. 15-18, 2013, with two days of Pre-Conference Sessions beginning Oct. 14. Early registration opened today with a 3-day Full Summit rate of $1,095 and a special $100 alumni discount, both good until Jan. 4, 2013.

“We’re excited about bringing the PASS Summit experience closer to DBAs, developers, BI architects, and IT pros who haven’t been able to travel to Seattle,” noted PASS Vice President, Marketing, Thomas LaRock. “And we’re also excited to experience the beautiful city of Charlotte in what will be a jam-packed week of learning and connecting with community members from around the world.”

To learn more about PASS Summit 2013 and to register, see www.sqlpass.org/summit/2013.

24 Hours of PASS In addition, PASS is rounding out 2012 with two 24 Hours of PASS virtual events, extending its reach to the Portuguese and Spanish SQL Server communities. Both events will feature 24 free, live, back-to-back technical webcasts. The Portuguese event takes place Nov. 26-27, with the Spanish edition on Dec. 5-6. For details, see http://www.sqlpass.org/Events/24HoursofPASS.aspx.

PASS Business Analytics Conference

Yesterday, PASS also announced the PASS Business Analytics (BA) Conference, a new event supporting business intelligence and analytics professionals using Microsoft Office, Microsoft SharePoint, SQL Server, and Windows Azure.

Over 1,200 business analysts, data scientists, line-of-business managers, and IT pros are expected to take part in the inaugural PASS BA Conference at the Chicago Hotel and Towers, April 10-12, 2013, for real-world insights, prescriptive guidance, best practices, and strategic vision for analyzing, managing, and sharing business information and insights through Microsoft’s collaborative BA platform.

Registration for the PASS BA Conference is also open now, with the $1,095 early-bird rate available until December 7, 2012. For more information and to register, visit http://www.passbaconference.com.

The Professional Association for SQL Server (PASS) today presented its most prestigious award to volunteer Jen Stirrup during the PASSion Awards ceremony at PASS Summit 2012in Seattle.

The PASSion Award is presented annually to a volunteer who demonstrates exemplary service and commitment to the PASS organization by inspiring the community to collaborate, learn, and grow through knowledge sharing and peer-based learning. PASSion award recipients are nominated by their fellow PASS members and selected by the Board of Directors.

 

Jen (blog | twitter) has worked diligently over the past 2 years with various groups in Europe to promote support for Women in Technology. A SQL Server MVP, she is a frequent speaker at such events as PASS Summit, SQLBits, SQLRally, SQLSaturday, 24 Hours of PASS, and local user groups and has been an active volunteer with European SQLSaturday events. At PASS Summit 2012, she is part of the panel discussing “Women in Technology: Where Have We Been and Where Are We Going” at the 10th anniversary WIT Luncheon.

 

In nominating her for the PASSion Award, fellow volunteers shared the following:

  • “She has gone above and beyond making contacts with people at Microsoft Europe and user group leaders to bring together a fledgling community of WIT support and build it into a more recognized, if not more accepted, group of PASSionate people.”
  • “Jen continues to support the PASS community at large as a recognized MVP and frequent speaker. She always is eager to share her positive experiences with this organization to encourage others to join in.”

In addition, PASS named Amy Lewis and Jesus Gil Velasco as 2012 PASSion Award Honorable Mentions for their contributions over the past year:

  • Amy (LinkedIn | twitter) is Co-Chair of the PASS DW/BI Virtual Chapter, Co-Manager of the PASS Summit 2012 Program Committee, event coordinator for this year’s Phoenix SQLSaturday and next year’s event, and new coordinator of the Arizona SQL Server User Group.
  • Jesus (twitter) is founder of the Mexico City PASS Chapter, PASS Regional Mentor for LATAM, and organizer of the LATAM 24 Hours of PASS virtual event.

PASS also recognized Ryan Adams, Mark Broadbent, Andrey Korshikov, and Sarah Strate as Outstanding Volunteers.

Congratulations to all our honorees, and thank you for your service to PASS and the SQL Server community! For more information about PASS and the PASSion Awards, see www.sqlpass.org.

About PASS PASS is an independent, not-for-profit association dedicated to supporting, educating, and promoting the global Microsoft SQL Server and Business Intelligence community. From local user groups and virtual chapters to webcasts and the annual PASS Summit, PASS enables knowledge sharing, in-depth learning, access to the Microsoft SQL Server team, and the ability to influence the direction of SQL Server technologies. For more information, visit the PASS Web site at http://www.sqlpass.org.

Uncategorized

SQL PASS Summit Day 1 Keynote

* Announcing the PASS Business Analytics conference in Chicago, Il on April 10-12 2013.

* Announcing SP1 for SQL Server 2012.

We arrived sunday for PASS 2012 and immediately met up with some friends and had a great time.

Following that we were both in precons yesterday.  I was in Private Clouds with Allan Hirt and Ben DeBow, and Jen was in t-sql with Itzik Ben-Gan.  If you’ve never been to a precon before I recommend you give it a try.  It’s an endurance trial though.  8hrs of concentrated training on a topic isn’t easy to get through, but if you throw a little water in your face you’ll get through it.

* Announcing Hekaton,  a new in-memory technology that will ship in the next major version of SQL Server. In-memory computing is a core element of Microsoft’s strategy to deliver a data platform that enables customers to analyze all types of data while also accelerating time to insight. Currently in private preview, “Hekaton” will complete Microsoft’s portfolio of in-memory capabilities across analytics and transactional scenarios, offering customers performance gains of up to 50 times. Because Microsoft’s in-memory capabilities are built into SQL Server, customers don’t need to buy specialized hardware or software and can easily migrate existing applications to benefit from the dramatic performance gains.  The live demo showed a 9x perf increase in a workload by using Hekaton to pin the table into memory.  They then optimized a stored procedure with Hekaton and got a 28x performance increase.

Also, a preview of the next version of SQL Server 2012 Parallel Data Warehouse (PDW), Microsoft’s enterprise-class appliance, available in the first half of 2013. SQL Server 2012 PDW will include a new data processing engine called PolyBase, which will enable queries across relational data and non-relational Hadoop data to drastically reduce time to insight.

* CTP Microsoft HDInsight Server is now available.  HDInsight Server is the name given to the next iteration of Hadoop capabilities.

PolyBase was also demoed.  PolyBase is a new query construct that allows  you to query multiple data sources easily and merge them together.  So tsql and hadoop for example, are well within reach now in a single query.

*PowerView and PowerPivot are now fully integrated into Excel2013.

The integration is being billed as something that’s extremely easy for end users to setup.  I tried the same demo he did on stage with a very simple data set I created in excel on the spot and I was able to recreate his demo very easily on the spot without ever having played with it before.  This is the first time MS has billed something as being easy in the BI platform and I’ve actually been able to duplicate what they’ve done.

Great job guys.