Admin, Powershell, Time-savers

Cool Powershell Scenario

here’s a cool scenario where you need to set all of your DBs to simple mode and then back again.

Let’s say that you have a bunch of DBs on your server that are all in full mode and you’re setting up new backup routines on the server and you want to start everything from scratch.  So in this scenario you may want to switch all the DBs to simple mode to truncate the logs and then back to full again.  This is to prevent a huge log backup the first time, and you may not even have a full backup file to go with it anymore so what would be the point?

So here’s some simple code you could run to make this happen very quickly and easily.

> dir | %{$_.set_RecoveryModel(3)}  # Set recovery to simple to truncate the logs.

> dir | %{$_.set_RecoveryModel(1)}  # Set recovery back to full.

Everything you do with powershell doesn’t have to cure cancer.  It can just save you a couple mins or even a few mins of tedium.  Writing really cool scripts to do big things is awesome, but most of the stuff you’re going to do is this adhoc kinda stuff.  That’s the reason I love powershell so much, because you don’t have to do anything grandios with it.  You can just make your day easier.

Admin

Derived Column vs Script Component

2 Comments

I get asked this from time to time… when should I put a transform inside a Derived Column (DC) and when should I put it in a Script Component (SC).  Well the answer isn’t always easy, but here are a couple guidelines… because you do have to make this decision quite often in SSIS.

Scenario #1:

In short I’d use a DC when the transform is really simple and there’s not much chance of it creeping past what it is initially.  So if it’s going to be something like testing a simple condition and doing a simple replacement based off of it, then a DC is an easy choice to make.  However, if it’s likely that the scope of the transform will shift in the future to something really big then you’re better off with an SC.

Scenario #2:

If the above transform is going to rely on several other columns in the row, then you’re far better off with an SC because it’s much easier to read.  So if you have to compare 4 or 5 cols to get the answer then use an SC.

Scenario #3:

If the transform will be really complex and/or have a lot of sub conditions in it, then it may be possible to do it in a DC, but you’ll be far better off in an SC.  Everything in a DC is on a single line and that model breaks down pretty fast.  And of course there are some sub-conditions that you won’t be able to accurately represent in the very limited DC.

Scenario #4:

If you need to test a data type like IsNumeric(), you can’t do that in a DC at all.  Even though it’s a valid VBA function, SSIS doesn’t support it, so you have to rely on .Net inside of an SC.

Scenario #5:

If you’ve got a lot of simple transforms and you’ve got one that’s more complex or longer, etc, then you may want to consider using an SC simply to keep everything in one place.

That’s all I’ve got.

Admin

Disk monitoring isn’t easy by default

3 Comments

At my last gig one of the other managers was constantly pissed off at my refusal to not setup disk space alerts.  "We’re always filling up the logs." he says, "so we need to monitor for space and send an alert."  And of course, that certainly sounds reasonable, so why did I refuse then?  Well, put simply, something that sounds reasonable to someone who doesn’t know what he’s doing isn’t reasonable to those of us who know something about DBs… oh and mind your own business.

The issue wasn’t whether or not I thought alerting on disk space is theoretically a good idea or not.  The issue is whether it’s what you actually need.  See, in our situation, the company was really slow to buy disks.  So most of the time we would be running at over 90% capacity for all of our disks.  And a lot of them were running on vapors.  In fact, our big DW system was reporting .01% free space for 2mos before they got us new disks.  Now, tell me I don’t know how to manage space.  So the logs were always filling up the drives because there was nothing left for them to grow into.  And the source system would push these mass changes w/o telling us quite often and there would be 3x the transactions out of the blue one night and we would have to find a way to deal with it.  So in the middle of ETL the disk would go from .01% free to 0%.  What the hell am I supposed to alert on?  Since we’re always running to tight, by the time the alert got fired and the email was sent out, the processes would have stopped anyway, so what good is the alert going to do?  And no matter what I said to him he just couldn’t get it out of his head that an alert is what we needed.  When what we really really needed was to not be running on vapors.  Trust me dude, I can spell SQL so just let me do my job.  I know it seems like we’re neglecting your system, but we’re really not.  There’s just nothing we can do about it.

The real fix of course is to get some disks and not run them at capacity.  I almost had them there when I left.  I had been telling them for 4yrs that you can’t run disks at capacity and they were finally barely starting to listen.  But again, remember it took us at least a year to get the new disks approved so once we got them, we were already running low.  And when the log is filling up in the middle of a big operation you won’t get an alert in any reasonable time because they’re not run continuously, they’re run every few mins.  So it’s quite possible that all the action of the disk filling up happens between sampling internals. 

I kind of have the same problem at my current gig as well.  Disk space is at a premium and alerting has proven to be a challenge because those rogue processes are the ones that push the log over the top and they fill up an already stressed drive very fast.  And there are unseen consequences as well.  Let’s take a look at a specific example I had just yesterday.  One of these boxes had a rogue large transaction that took the size of the log through the roof and filled up the drive.  So the DB shut down and I had to fix it manually.  However, the log backups go to the NAS with a lot of the other backups in the LAN so with the log backups being so much bigger it took up a lot of unexpected space on that drive as well and failed lots of backups on other servers.  Now there’s nothing I can do about the rogue processes filling up the log, but currently I also can’t alert on them effectively either.

So if you really want to effectively monitor your disk space, then run your disks at about 50% or so to give your alerting process a chance to detect the threshold breach and do something about it.

Oh y, and I recently heard from the DBA that took my place that they’re still running at capacity and that other manager, without me there to stand in his way, has finally gotten his precious alerts.  And the alert comes about 10mins after the process stops and the DBA is already working on it. 

Admin, Time-savers

Poweshell wins again

1 Comment

It may seem a little redundant, but I love stuff like this. I was asked in front of a group of devs to script out a group of SPs on the prod box and copy them over to the new test box. These SPs stretch across a couple schemas and are named differently from the other ones in those schemas. As it turns out, there are something like 300 of them total. I don’t have a final count.

So when the guy asked me I said sure, that’ll take me like 60secs. And one of the other devs said, there’s no way. You have to check all of those boxes individually and and make sure you don’t miss anything. I said, of course I can. I’m a powershell guy (yes, i actually said that). He was like, even if you could script something like that out, there’s no way to easily get all the ones you need. You’ll be much faster in the wizard.

I told him, I accept your challenge. And for the first time, I gave a dev rights in prod and we had a face-off right there. We sat side by side and both of us started working feverishly to get our SPs scripted. Him in the wizard and me in powershell. Very quickly a crowd gathered. We prob had like 15-20 people gather. These were the PMs, other devs, report writers, etc. They all wanted to see if the bigshot DBA MVP could be taken down by a lowly dev.

Unfortunately like 90secs later, I completed my script and was building my file with my scripted SPs. He was still slugging his way through the wizard and wasn’t even close to having all his little boxes checked. When I finished, I just stood up and walked out as everyone clapped. When I left to come back upstairs he was still working at the wizard determinded to at least finish.

At least that’s how my powershell hero stories always play-out in my mind. I really did get that assignment today, but it was through email and I just did it without any pomp and circumstance. Oh well, a guy can dream can’t he?

Here’s the code I wrote today to pull SPs by schema and matching a pattern. I used the regex in powershell to make this happen. Enjoy.

PS SQLSERVER:\SQL\Server1\DEFAULT\Databases\ProdDB\StoredProcedures> dir | ?{$_.schema -eq “Bay” -or $_.schema -match “EBM”} | ?{$_.Name -match “Bay?_PR”} | %{$_.Script() | out-file C:\SPs.txt -append; “GO” | out-file C:\SPs.txt -append}

Maybe someday one of you will actually save the day with it.

Admin, Powershell, Time-savers

How to Monitor SQL Services with Powershell

5 Comments

Here’s the situation…

You get a call from one of your customers saying that the log has filled up on the DB and they can’t do anything any more.  So you connect to the server and find out that the log backups haven’t been running.  So you run the backup and everything is hunkydory.  But why did it fail to run in the first place?  Well about 3secs of investigation tells you that the Agent was turned off.  Ok, you turn it back on and go on about your business.  But this isn’t the way to do things.  You don’t want your customers informing you of important conditions on your DB servers.  And you certainly don’t want Agent to be turned off. 

And while there may be some other ways to monitor whether services are running or not, I’m going to talk about how to do it in PS.  There are 2 ways to do this in PS… get-service and get-wmiobject.  Let’s take a look at each one to see how they compare.

In the old days (about 2yrs ago), when all we had was the antiquated powershell v.1, you had to use get-wmiobject for this task because get-service didn’t allow you to hit remote boxes.  All that’s changed now so you can easily run get-service against a remote box with the -computername parameter.

get-service -computername Server2

And of course it supports a comma-separated list like this:

get-service -computername Server2, Server3

And just for completeness here’s how you would sort it, because by default they’re going to be sorted by DisplayName so services from both boxes will be inter-mingled.

get-service -computername Server2, Server3 | sort -property MachineName | FT MachineName, DisplayName, Status

Ok, that was more than just sorting wasn’t it?  I added a format-table (FT) with the columns I wanted to see.  You have to put the MachineName there so you know which box you’re gong against, right?  And the status is whether it’s running or not.

Remember though that I said we were going to do SQL services, and not all the services.  So we still have to limit the query to give us only SQL services.  This too can be done in 2 ways:

get-service -computername Server2, Server3 -include “*sql*” | sort -property MachineName | FT MachineName, DisplayName, Status

get-service -computername Server2, Server3 | ?{$_.DisplayName -match “sql”} | sort -property MachineName | FT MachineName, DisplayName, Status

so here I’ve used the -include and the where-object(?).  They’ll both give you the same results, only the -include will filter the results on the remote server and the where-object will filter them on the client.  So ultimately the -include will be more efficient because you don’t have to send all that extra text across the wire only to throw it away.

And of course, you don’t have to use that inline list to go against several boxes.  In fact, I don’t even recommend it because it doesn’t scale.  For purposes of this discussion I’ll put the servers in a txt file on C:.  Here’s how you would do the same thing while reading the servers from a txt file, only this time you could very conveniently have as many servers in there as you like.  And when creating the file, just put each server on a new line like this:

Server2
Server3

So here’s the same line above with the txt file instead:

get-content C:\Servers.txt | %{get-service -computername $_ -include “*sql*” | sort -property MachineName | FT MachineName, DisplayName, Status}

This is well documented so I’m not going to explain the foreach(%) to you.

Ok, so let’s move on to the next method because I think I’ve said all I need to say about get-service.  But isn’t this just gold?

get-wmiobject

Earlier I was talking about what we did in the old days and I always used to recommend get-wmiobject because of the server limitation imposed on get-service.  However, does that mean that get-wmiobject is completely interchangable with get-service now?  Unfortunately not.  I’m going to go ahead and cut to the chase here and say that you’ll still wanna use get-wmiobject for this task most of the time… if not all of the time, because why change methods?

You’ll notice one key difference between doing a gm against these 2 methods:

get-service | gm

get-wmiobject win32_service | gm

The get-wmiobject has more methods and more properties.

And the key property we’re interested in here is the StartMode.

If you’re going to monitor for services to see which ones are stopped, it’s a good idea to know if they’re supposed to be stopped.  Or even which ones are set to Manual when they should be set to Automatic.

And for this reason I highly recommend using getwmiobject instead of get-service.

Here’s some sample code using the txt file again.

get-content C:\Servers.txt | %{get-wmiobject win32_service -computernatm $_ -filter “DisplayName like ‘%sql%’ “} | FT SystemName, DisplayName, State, StartMode -auto

Notice that the names of things change between methods too, so watch out for that.  So like MachineName changes to SystemName.  You’ll also notice that I didn’t provide you with a full working example of a complete script.  That’ll be for another time perhaps.  The script I use fits into an entire solution so it’s tough to give you just a single script w/o also giving you all the stuff that goes along with it.  And that just gets out of the scope of a single blog post.

However, I’ll leave you with these parting pieces of advice when building your service monitor.

1.  Instead of pulling the servers from a txt file, put them in a table somewhere so you can run all of your processes from that location.

2.  Use get-wmiobject win32_service instead of get-service.  It’s more flexible.

3.  When you collect your data, just save it to a table somewhere instead of alerting on it right away.  In other words, there should be a collection and a separate alerting mechanism.

   *** Why you ask?  Well I’m glad you asked, because not asking something that should be asked is like asking something that shouldn’t be asked but in reverse.  Anyway though… I prefer to get a single alert on all my boxes at once instead of an alert for each box, or for each service.  And that kind of grouping is much easier to do in T-SQL than in PS.  Also, there may happen a time when a service is down for a reason and you don’t want to get alerts on it but you still want to get alerts on the rest of the environment.  This is easier to do in T-SQL as well.  And finally, you may want to also attempt to start the services that are down and that really should be a separate process so you can control it better.  Or you may just want to be alerted and handle them manually.  Again, what if the service is supposed to be down for some reason, you certainly don’t want the collection process going out and restarting it for you.  And the collection can be a nice way to make sure you remember to turn the service back on when you’re done with whatever you were doing.  You’ll get an alert saying it’s down, and you’ll be all like, oh y, I totally forgot to turn that back on and my backups aren’t kicking off.  All the same, you really do want the collection, alerting, and action processes to be separated.  But that’s just me, you do what you want. ***

4.  Keep history of that status of the services.  You can look back over the last few months and see which ones have given you the most trouble and you can then try to discover why.  It’s good info to have and you may not realize how much time you’re spending on certain boxes until you see it written down like that.

Admin

My favorite vendor code.

1 Comment

I was given this code the other day to review before it gets run against prod.  It’s code that a vendor wrote to help us clean-up some of the bad and old records in this one app table.

There’s really not much to say about it except that it’s by far the best vendor code that’s crossed my desk in a long time.

I hope you enjoy it as much as I do.

 BEGIN TRANTable1
SET touchedwhen = GETDATE(),
touchedby = 'mysupport',
statuscode = 'CISPA'
WHERE createdwhen < '2009-01-01 00:00:00.000' AND statuscode = 'AWDP'
-- Where createwhen < getdate()-1 and statuscode = 'AWDP'

ROLLBACK
COMMIT

So once I got this and the email chain I started based off of it was priceless.  I don’t feel really right about printing the email chain here, but rest assured that I’ve defended the logic of this query perfectly.

Admin

Whatever, I’m done.

19 Comments

Well, since I hear everyone is enjoying these today, I just finished my last phone screening and here are the nuggets I’ve discovered this time.  Man you just learn so much interviewing.  If any of you really cared for me though you’d shoot me right in the ass.

Q:  What’s a bookmark lookup? (his resume says he’s a query tuning expert)

A:  If there’s no PK on the table, SQL will keep a bookmark table that tells it what the PK should be and it uses that to lookup the data.

Q:  Say you have a prod server with the DB on D:\ and you need to restore it to a dev server that has no D:\.  But it does have an E:\… how would you restore that DB to E:\ instead of D:\?

A:  You have to restore it to the full recovery mode.

Q:  What is IsAlive vs LooksAlive? (he’s also a big clustering guy)

A:  LooksAlive mode when you want to hide the cluster from hackers so you put it in LooksAlive mode and IsAlive mode is when you have an internal box and you want to allow it to be seen from the network. (I’m so far beyond speechless I don’t even know what to do with myself.  Now I know why VanGough ripped off his ear.)

Q:  When would you use log shipping vs mirroring?

A:  Log shipping is what you use when you want to run it in full mode and mirroring is the same thing only you do it in simple mode…

Somebody make it stop!! The voices are getting so LOUD!!!

Admin

Somebody kill me.

12 Comments

I just had an initial phone screen with a guy who wants the DBA gig I’ve got open.  Here are the last 2 questions I asked him with his answers.

Q:  Say you have a prod server with the DB on D:\ and you need to restore it to a dev server that has no D:\.  But it does have an E:\… how would you restore that DB to E:\ instead of D:\?

A:  FTP.

Q:  Can you name 2 server-level security roles?

A:  DBA and Dev.

Sorry I asked.  I’ll just be over here.

Admin

Who’s afraid of cursors?

1 Comment

Ok, so one of my last posts talked about using a cursor for something and I came upon some criticism and/or some wincing about the fact that I used that horrible word.  However, cursors like anything have gotten a bad rap (xp_cmdshell is another).  People, there’s nothing wrong with using cursors as long as it’s what’s actually needed.  The problem comes with frontend coders writing cursors for everything and severely abusing this feature. 

But there’s nothing inherently wrong with them… esp for admin tasks.  There are so many legitimate uses for cursors I can’t even come close to naming them all, but here are a few…

Doing something to every DB on a server.

Doing something to every schema in a DB.

Doing something to every table in a schema.

Doing something to every user/login acct (like running changeUsersLogin to correct a ghosted user acct issue).

These are all perfectly legitimate uses of a cursor, whether it’s T-SQL or powershell.  So let’s get ove rthis hangup we’ve got against both the concept and the word.  There’s nothing wrong with it as long as you do it right.  Afterall, what is a cursor anyway?  It’s a way to store a list of values so you can perform an action on each one of them.  And the alternative is to hardcode each one of those lines yourself or use the GUI for everything and we all know that’s completely unacceptable. 

And in powershell everything you do is a cursor… even things that don’t need them.  Of course, they call it a foreach loop, but that’s just a more acceptable name for a cursor isn’t it?  It’s more specific… foreach one of these things, I want to do this…  hell, even almost every backup routine I’ve seen in the shops I’ve been in, and on the web cursor through the DBs on a server and back them up.

So deciding to not use cursors (or at least wince at them) because they get misused by some is like refusing to use a hammer on a nail because of someone you saw trying to open a banana with one.  It wasn’t the right tool for the job so it messed things up.  But it IS the right tool for a nail.

Instead of working, I blog.