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, Powershell, Time-savers

Mystery of the missing files

2 Comments

About 3mos ago I had my work box rebuilt.  The encryption software glitched and it just wouldn’t boot.  So the helpdesk girl was able to slave the drive to another box and copy my files to a NAS.  I looked and all the folders were where they should be.  Great.

She then rebuilt the box and when I got it back I copied all the folders back.  I let it rest and I was very pleased that I was able to preserve all my scripts, etc.  A few days passed and I needed a script, so I went to that folder and it was empty.  WTx dude?  I looked and everything was still out on the NAS so I tried to get it off of there but they were missing there too.    OK, now I’m upset.  I’ve just lost most everything I cared about on my box.  It took me a while to accept it but I finally did.

Well, today (this is now several wks later) I finally went to replace one of the things I lost by downloading it again.  When I went to save it though, it said that it already existed and did I want to replace it.  See, I saved it to the same folder as before because I’m OCD that way.  Needless to say it took me about 2secs to figure out what had happened.  Normally I setup my box the way I want right away, but this time I got busy and just forgot.  And as it turns out my files were there, just hidden.  And I didn’t have ‘show hidden files’ checked in folder properties.  Now that I know the problem, how do I fix it because there’s a lot of files and folders to go through.  The answer of course is Powershell.  I got it right on the first try and the code is so small, and so compact, and so easy to come up with, it just screams PS.  And it is the perfect example of what PS is all about.  So here’s the query I used… enjoy

dir -recurse -force | %{$_.attributes = 'Archive'}

Now, here’s a brief explanation of a couple of the flags.

-recurse — means to run it against all the subfolders too.
-force — this is a good one because it tells PS to show you all the files.  If they’re hidden you won’t see them unless you use this flag.

So that’s it really.  A single line of code to unhide hundreds of files in dozens of folders and subfolders.  And since this is my first day back after vacation, I’d say I’m starting it off on a decent note.

MidnightDBA News

DBAs@Midnight X-mas present for you.

With the popularity of the cloud, we’ve decided to give you a present in the cloud this year.  If you don’t like it you can thank Microsoft for trying to get everything in the cloud.

Go to http://webshow.midnightdba.com/ to see the live feed of your gift this year.  It’ll be up 24/7 until xmas morning.  And it really is a live feed, not a recorded loop.

You’ll just have to give you name for the chat console, but you’re not obligated to say anything.  Also, if the page doesn’t look right, then you need to add the URL to your compatibility settings in IE.  Anyway, enjoy, and Merry X-mas from the MidnightDBAs.

Admin

2 Addictions worth fighting

5 Comments

A recent email chain with a friend has spawned me to write this post.  What I’m trying to do here is help the 2 extremes of people you’ll find in your company:  the idiot, and the DBA.  Ok, I know all DBAs aren’t super smart, but I had to call the non-idiot something.  So this email chain I had was with a DBA who was stressing-out about something stupid his company is doing even though he warned them.  So what you’ve got to understand is that both sides of this equation are an addiction.

The Addiction of Stupidity:

It’s easy to get caught-up in the fact that our users are morons, or that the other IT guy is an idiot, but you’ve got to have a little compassion.  You have to understand that stupidity is an addiction.  It may have started through a combination of them not knowing anything at all about SQL, and them not having any qualified DBAs to help them learn.  How it started though doesn’t really matter in the end.  What does matter is that now you’ve got to help them through it.  You need to advise them on the proper way to do things.  At first they won’t listen.  Keep it up though and you’ll start to prove yourself a little at a time.  Then they’ll start to trust you.  Even when they trust you though, they’ll still fall back into being stupid all the time.  You have to urge them to fight it.  It’s hard though, right?  They really want to fall back into their old habits but you can’t let them.  It’s all they know and a little knowledge is empowering and dangerous.  So you have to guide them and not let them slip back into their bad habits of making stupid decisions.  Remind them that trusting you in the past has paid off and it will again. 

 

The Addiction of Caring:

The other side of this is the DBA himself.  See, as DBAs we tend to own the DBs under our control.  We want everything to work together like a well-rehearsed ballet.  And when it goes that way it can be a beautiful thing.  But it almost never goes that well, does it?  The company doesn’t see things the way we do.  The addicts in the above section quite often have the power to block our intelligent moves and even put things into play without us even knowing.  People in power are quite often worse than anyone else.  So how do you deal with this?  How do you get them to do the right thing?  Well, you can discuss it with them and get them to see reason, but if they don’t then you have to let it go. 

You’re not being paid to do a good job.  You’re being paid to make your boss happy.  I’ve said this many times before, and it’s still true.  Turn yourself into an internal fulltime consultant.  Advise them of the right thing to do, but if they make the wrong decision then it’s their business and their decision.  You can’t make them run their business the right way.  You can only advise them.  Remember that.  It’s not your business.  So stop insisting that things be done a certain way.  Tell them what should be done and let them make the decision.  And if they make the wrong decision just make sure you’re covered with an email.  You want it on record that you advised them correctly and they made the wrong decision.

But caring to the point where you’re pulling your hair out is just as much of an addiction as being stupid.  You have to constant remind yourself that you’re just a consultant and if they want to pay you a bloated salary to ignore you and have you spend your days cleaning their messes up instead of providing real value then that’s their business.  Again, you’re being paid to make them happy, not to do a good job.  Plenty of people disagree with me on this, but they’re wrong.  It just so happens that some bosses want you to do a good job and want you to guide the company, but that’s just a case where their priorities align with yours.  If their priorities were to change then I bet you’d find that you’re all of a sudden butting heads.  I’ve been in several companies where everyone in the world has sa and they’ve even got lots of apps connecting with sa and everyone knows the password.  I’ve advised them how unsafe this is, but they insisted that it’s too engrained and can’t be changed.  Fine, I put my advice in an email and went on about my business.  Things kept happening to the servers and they wanted it to stop, but I said I can’t stop it because everyone has sa.  They said it’s impossible.  So ok then.  Then they started failing audits.  They lost clients because they couldn’t produce  the report of the passed audit.  Now they came to me again and said how can we pass this audit.  I said you have to get everyone out of sa and do about 10 other things.  They said but the effort would be too great.  I said ok.  Then they lost another client.  Then they asked me what it would take to pass the audit.  I repeated the same thing.  They said it would be too hard.  I said ok.  They said can you give me another way?  I said no, but have you looked into what it would take?  What’s the actual effort?  They said no, we haven’t looked into it, but I know it would be really hard.  I said, then why not ask the question to the right people?  As it turns out it was a single VP who was blocking it because she had NO IT knowledge and just thought this kinda thing was really hard.  All they had to do was change a connection string in a .ini file and all was well.  Then they just told everyone to get over it and they weren’t getting sa anymore.  They would have to stop doing things that a DBA needs to do.  All of a sudden the environment became much more stable, they passed their next audit, and they started getting those contracts they were losing before.  That’s a true story BTW.

So find a way to divorce yourself from the outcome of these things.  I like to look at it like this… if you see a guy on the street getting into his car and he looks like he’s about to run the red light, and you call into his window and warn him there’s a cop sitting there, and he does it anyway you just look at him and say dumbass.  Chances are you probably won’t get upset and give yourself an ulcer over it because you’re just not that invested in the outcome of your advice.  You warned the guy but he didn’t listen… so what!  And that’s the attitude you have to take with your own company.  You can’t invest yourself in the outcome of your advice.  It’s not your company.

So anyway, I’m just trying to help you keep a little perspective.  If you’re truly in a job where they hang on your every word then consider yourself lucky.  The vast majority of us don’t have that luxury so we have to find ways to cope with the fact that everyone doesn’t listen to everything we say. 

And remember, both of these are addictions.  As much as we have to help our users fight against being stupid, we have to constantly fight against becoming too invested in the outcome of our advice.  And I struggle with this with different degrees of success.

 

Admin, Videos

The answer is No

4 Comments

There are times in a DBA’s life when the answer is No.  And for once I’m not talking about a DBA asking a girl out.  This time I’m talking about requests we get from our users.  I had such a request today.

A manager of another group came up and asked for a generic account that he could use to connect to the DB from an application.  The answer is No.  Sorry dude, but we don’t give out generic account unless it’s absolutely necessary.  And of course, then the insistance sets in that he has to have the generic account because it’s an application.  I said no, the app itself is running under an acct, and I’ll be happy to give that acct the perms.  He said, well it’s not that easy, this is a web app.  And I said well, that changes things significantly, and you’re right it’s not that easy.  He said see, I told you.  I said, it’s even easier.

You see, with a web app you can set the app pool to run under any acct you like, so connecting to a DB under certain credentials is wicked easy.  And it really is.  He said well, we also need to be able to connect using SSMS to be able to run a couple SPs manually.  So we were hoping to be able to use that acct for that as well.  Again, No.  Give me the accts you want to have these perms and I’ll make it happen. 

Now that’s the end of the conversation, but here’s a word for you guys reading this.  There are several ways to make this happen depending on how you layout the group (either in AD or SQL).  The point I’m trying to make here is that sometimes you have to make sure you service your customers in a way that’s best for the company.  They quite often ask for something specific that you can’t give them, but you can give them the equivalent.  Often times users ask for things a certain way because that’s all they know.  They get too caught up in solving a problem so they try to solve the problem the only way they know how… and in this case it was asking for a generic account.  But this is where we as DBAs need to step up and have the courage to guide them.  They may get upset, they may even insist that it be done their way, but they’re not DBAs, and they’re ass isn’t on the line.  It’s our job to make sure that it gets done right.

Now, just for completion here’s a video I did quite some time ago that shows you how to run a website under a specific account.  It’s in IIS 6, but it’ll show you how it’s done, and you should be able to transfer that skill to IIS 7 if you have to.  And if the app has a windows service it’s even easier… just run the service under the account you like and you’re golden.

MidnightDBA News, Uncategorized

Presenting at PASS Today

I was tagged at the last minute to do my Backup Tuning class at 4:15pm rm 4C1-2 today. Heres the abstract…

 

The Backup Tune-up

Have you ever gotten tired of your 1TB database taking 4+ hrs to backup? Are you sick of having your users breathe down your neck for 2hrs because it’s taking too long to restore a DB? Well now you don’t have to worry about that anymore. I’m going to show you some little known tricks, methods, and trace flags you can use to tune your backups just like you would a query. Backups actually have kind of an execution plan that you can access if you know how, and knowing how to get the individual portions of your backup process down will allow you to knock 80% and even more off of your backup and restore time. I’m not holding anything back in this session. This is a method I’ve used for 15yrs to tune my backups and I’ve had great success with it.

Uncategorized

PASS Day 3 Keynote Live Blog

1 Comment

Today’s keynote with Dr. DeWitt starts at 8:15am EDT. Click here to watch today’s keynote live. For information on today’s keynote, titled “Big Data – What is the Big Deal?“, see the SQLPASS.org page on Keynotes.

If you wanna hear about the first part of the keynote, you can see it here.  I introduce the topic of big data there.

So here we are listening to Dr. DeWitt talk about big data and specifically he’s talking about data loss and node failures in HDFS.  And in his usual fashion, this is a very detailed accounting of how the file system performs its redundancy.  I’m not going to go into detail because I really can’t, but I believe this discussion will be available online at the URL above.  However, I will say that the most interesting thing about HDFS is that it gets it redundancy without any use of mirroring or RAID.  The file system itself takes care of everything.

I’m going to break away from talking about the keynote and give an update on the rest on the Summit.  I personally manned the Enterprise Mgmt expert pod in 2-4hr shifts this week, and I fielded dozens and questions about everything from replication and backup, to how to do things in Powershell.  So it was an excellent opportunity for me to either give some users good answers to their questions, or bring their prod boxes down.  Only time will tell which one it was.

There have been lots of vendor parties as usual and so far the most noteable to me was the Idera party.  Now, what makes a noteable party to me?  The food of course!  And Idera had their party at the Tap House and they had these lovely pulled pork spicy sliders. 

Right after the Idera party was the big Summit party at GameWorks.  Frankly I’m not a very big game guy so I’m not usually interested in playing the games… I’m in it for the food.  And the food at GameWorks was horrible.  There was only one thing there was was done any good at all and it was actually done very well.  That of course is the roast beef.  For some reason, while the macaroni was mushy and bland, the salad dressing was bitter, and the mashed potatoes looked like instant, the roast beef on the slicing table was perfectly medium rare, and seasoned well.  I guess the cook doesn’t like that other stuff cause he never bothered tasting them.

I blogged on one of the other days that the best part of the Summit is getting to see our friends that live all over the world, and that still holds true.  And we were even talking yesterday with some of the top-level MS guys that the Oracle conference doesn’t have anything that even resembles this level of community.  If you go to OpenWorld, you’ll be surrounded by people in suits and none of the vendors are really that interested in talking to you unless there’s a real possibility of getting any money from you.  So all of us are pretty glad we’re on the MS side of things, and we’re thrilled to be able to come to the Summit and take part in all of this.  Now, I read everywhere for one event or another where people like meeting new friends, etc, but that really takes on a different meaning here.  Those of us who come every year are so close and we watch out for each other so well, that I think regular conference friends in other technologies would be surprised to see how we relate.  It’s an amazing group of folks we hangout with here… and yes, I’ve actually made some new friends this year that I really think will fold into the group just fine if they keep coming.

Uncategorized

PASS Day 2 Keynote Live Blog

Today’s keynote with Quentin Clark starts at 8:15am EDT. Click here to watch today’s keynote live. For information on today’s keynote (“a demo-packed adventure across SQL Server solutions that span traditional boxed software, appliances and cloud”) see the SQLPASS.org page on Keynotes.

 Ok, here we are on day 2 of the PASS Summit 2011.  If you want to know about the first part of the keynote you can see that here. 

Now, I know in my past post that they were starting on appliances and I wasn’t interested in writing about them, but they’re up on stage now pimping the hardware itself and the machines are very impressive-looking machines.  It’s good to be Dell right now to be able to get your hardware on stage like that.  I dind’t notice before but they’re also showing some HP boxes too, so it’s not gonna just be a big Dell-fest.

Talk about a big box though… the new HP has 2TB of RAM, 196 cores, and enough spare drives that “you won’t have to worry if one of your drives fails”.  It’s their new internal cloud fast track, and that’s another blog entirely, but basically the internal cloud offering is this HP box that comes configured with Hyper-V, System Center, and some other cloud management stuff.  It comes basically ready to plug in so once you bring it in the door you’re ready to start building cloud machines.  Now, this is really just a big VM server that you spin-up VM guests in, so don’ t get too excited.  It comes with some specialized mgmt stuff I believe, but overall you could do all of this yourself if you prefer.

Now, the really cool part of this for me is the new ODBC drivers for linux for CDC and Oracle.  What this means is that now you’ll be able to pull data out of the Oracle logs themselves, and put them right into your SQL Server DB.  So you can literally use CDC with Oracle now.

Now Michael Rys is on stage talking about semantic search.  I don’t really know enough about it to put into words what he’s doing, but I’ll just say that it’s really cool.  I know that’s a lame description, but it’s almost the best I can do.  I will describe one thing though.  He ran a semantic search against a folder of pdf docs he had and the semantic search was smart enough to know the general subjects of the books, and only showed him the titles that met his criteria.  There’s a lot that goes on behind the scenes there that I can’t speak to, but I do know enough to get a sense of what a leap that is for search. 

Now we’re talking about SmartTVs.  I’m not sure why yet though.  Oh, I see, they’re on Azure to sync the TV data.  Ok, they’re talking a lot about clouds so that’s it for today.

Uncategorized

PASS Day 1 Keynote Live Blog

Today’s keynote with Ted Kummert starts at 8:15am EDT. Click here to watch today’s keynote live. For information on today’s keynote, see the SQLPASS.org page on Keynotes.

I’ve already blogged about some Summit topics here, but I wanted to continue the discussion on this blog.

Ok, during this opening keynote, Ted just announced the official name for Denali and it’s going to be SQL Server 2012 and it’s exact release date is in the first half of 2012.  We’re alredy in CTP 3 so MS has some work to do before now and then.

One of the big themes in Denali is the cloud.  MS is investing heavily in the cloud and until now the message has been very militant.  For the longest time now we’ve been getting the message that we’re all going to be out of a job if we  don’t transfer our skills to the cloud within the next 2-5yrs.  Now they’ve backed off quite a bit and are saying that the cloud is just oging to enhance your current skills and just give you another avenue when it comes to deciding where your data’s going to go.  Frankly I’m glad to see them calm down finally because it was making a lot of people very angry.  Now, just because MS says it that doesn’t make it so, so why was everyone getting so angry?  Well, in a word because of the offering.  They’ve been telling us that we’re all in trouble if we move our skills to the cloud, but their cloud offering is anything but complete.  So when there are such big holes in their offering, people get upset when you tell them their jobs are going away because it makes the scenario impossible.  So anyway, calm down guys, your job isn’t going anywhere.

Ted also just announced that SQL Server will be supporting Hadoop for ultra-large data sets.  They will also be making submissions to Hadoop to make it run properly for SQL Server.  Now, I know what you’re thinking… you’re a SQL Server guy, not a Hadoop guy.  Well, so am I, but it looks like we may have to at least get up to speed at least to a perfunctory level because we’re going to start seeing it in our DBs.  So to get you up and going on Hadoop, have a look at some of these links:

Hadoop Tutorial

Microsoft is getting in to BIG DATA.  One of their new goals going forward is to be able to work with the super-large datasets from many different sources.  A lot of this data is going to be unstructured, and that can be the hardest data to get at so bringing on products like Hadoop will help with that.  Because let’s face it… as DBAs we really want for everything to have a nice queryable structure, but it’s not always stored like that.

Data Explorer is an interesting topic too.  Microsoft has a new Azure Marketplace and apparently they’ll not only offer an app marketplace like we’re all used to seeing, but they’re also going to offer it as an internal service.  What it sounds like this means is that they’ll allow companies to have their own app marketplace that they can use to give their internal people  apps.  Now, this is a new market for sure, but how useful is it?  From a technology standpoint I get where they’re coming from, but I just don’t know how much of a use there is for something like that.  If it does get picked up then it’ll mean a big paradigm shift for pretty much every company in the world.  But this is definitely something to watch out for because it’s an interesting concept.  I remember when MS came out with web services.  They didn’t really know what to do with it, but they stood in front of their customers and said here’s this new technology, now we’d like for you to go see what you can do with it.  I’m waiting to see if this’ll be like that again, and I’m also interested to see if this has any real teeth. 

 

 

Admin, Powershell, Time-savers

Comparing DB objects in Powershell

2 Comments

I got a request today to compare 2 DBs and see if they’ve got all the same tables. It seemed easy enough since I’ve got the redgate Schema Compare tool, but it actually isn’t. See, for some reason Schema Compare doesn’t work very well on my box, and it’s a big overkill for what I needed. All I really needed was to see if the same tables existed. I wasn’t interested in comparing their actual definitions. So I thought I’d give Powershell a try.

I started by picking my cmdlet, which was going to be Compare-Object. I use its alias ‘Diff’. Now, this cmdlet isn’t typically used for comparing DB objects, but there’s nothing that says it can’t be. After giving it a little thought I decided to write the following code to get my comparison. Oh, and I’m just using sample DBs for my examples.

$a = (cd sqlserver:\sql\localhost\default\databases\AdventureWorks\tables).dir;
$b = (cd sqlserver:\sql\localhost\default\databases\AdventureWorksLT\tables).dir;

Diff -reference ($a) -difference ($b) -property name

OK, that’s the base-level syntax you need to do a comparison on table names. That code will give you the following results.

The left arrows indicate tables that are only found in the reference object ($a), while the right arrows indicate objects that are only in the difference object ($b). And that’s really it. The only thing you need to know is that while the differences are outlined, objects that appear in both sets aren’t listed. So your best bet would be to have an empty result set because that would mean that all of the objects exist in both places.

Now, keep in mind also that I only matched on the name property. So there are opportunities for this to go wrong because I’m not taking schema into account. If I wanted to take schema into account though, I would just add it to the property list like this:

$a = (cd sqlserver:\sql\localhost\default\databases\AdventureWorks\tables).dir;
$b = (cd sqlserver:\sql\localhost\default\databases\AdventureWorksLT\tables).dir;

Diff -reference ($a) -difference ($b) -property schema, name

This time you notice that the Address table shows up twice in different schemas. And in the first result set the Address table wasn’t there, so that means that the Address table was considered a match in both DBs because we weren’t taking schema into account.

However, there’s one final piece that you may find useful. What if you want to see all the objects, even the ones that match, you can throw in the -IncludeEqual flag. I’ll run the original Diff to show you that now the Address table shows backup and is indeed considered equal when you compare it just by name.

$a = (cd sqlserver:\sql\localhost\default\databases\AdventureWorks\tables).dir;
$b = (cd sqlserver:\sql\localhost\default\databases\AdventureWorksLT\tables).dir;

Diff -reference ($a) -difference ($b) -property name -IncludeEqual

Now that we’ve got all the plumbing worked out you can apply this to SPs, functions, or whatever you like.
It won’t give you an actual schema compare against these objects, but it’s really helpful in quite a few instances to be able to just see which objects exist.

That’s it for this time.

Admin, Powershell

Finding SQL VMs: Part 2

Ok, I’ve got a couple other methods so I thought I’d pass them along to you guys. These are methods passed to me by users.

The first one is: xp_readerrorlog 0,1,’Manufacturer’
I tried this on a number of my boxes with very mixed results. It didn’t have anything that specifically told me the VM wan’t virtual, and it didn’t work on any of my VMWare boxes. It appeared to work well enough with Hyper-V though.

The 2nd is: Win32_BaseBoard
This WMI class worked just fine for finding Hyper-V instances, but didn’t prove fruitful at all on VMWare. And again, there wasn’t really way way to tell whether it was physical or just not being reported correctly.

So there you have it. We’ve got 2 more methods, that I haven’t had much luck with, but maybe you’ll have more luck if you’ve got the right environment. So if you really don’t like the one I gave you yesterday, then pick one of these.