Category Archives: Powershell

Audit service accounts with Powershell

I thought I’d kick off the week with a useful little PS script I wrote last week.  It happens from time to time where you need to find out what AD accts your SQL boxes are running on.  It could be that you simply want to see how diverse they are or maybe you want to change the password of an acct and you want to see what the impact will be.  All the same, it can be useful to audit the startup accts for your SQL services. 

So here’s a little script you can use to get that done.  This again is part of a much larger process I have, but I’ve pulled it out and simplified it to its base componets for you.  I’ve also added the DB side of the process for completion. 

Here’s the PS piece:
$Server = ‘localhost’
$StatsDB = ‘DBStats’                  
 $SourceSQLScript1 = “\\Server1\F$\SQLServerDBA\Scripts\Collector\SQLQueries\AllSQLBoxes.txt”; 

$SqlCmd1 = Invoke-Sqlcmd -ServerInstance $Server -Database $StatsDB -inputfile $SourceSQLScript1
$SqlCmd1 | %  {
        $_.ServerName; ####Print ServerName
        [System.Int32]$ID = $_.InstanceID;
        [System.String]$ServerName = $_.ServerName;
        $ExecutionDateTime = Get-Date -Format “%M/%d/%y %H:m:ss”; 

$a = Get-wmiobject win32_service -ComputerName $ServerName -filter “DisplayName like ‘%sql%'” -ErrorVariable err -ErrorAction SilentlyContinue 

$a | % { $DisplayName = $_.DisplayName;
   $StartName = $_.StartName; 

 Invoke-Sqlcmd -ServerInstance $Server -Database $StatsDB -Query “Collector.spServiceAcctInsert ‘$ExecutionDateTime’,’$ID’,’$DisplayName’,’$StartName'”
}

Most of this is the same as the other scripts I’ve posted so I’m not really gonna go into every line like I have in the past.  I am going to explain the params for the invoke-sqlcmd line though.  In this line I’m just calling an SP that I’ve put in place to log these details to a table and you can use as much or as little of this as you wish.  The $ID param is the ID of the server.  In my complete solution I have a server table and I log things to other tables using the ServerID that comes from that table.  You’re free however to just capture the ServerName here instead.  But you notice that in the query, I get the ID and the ServerName and I use those in the script.  I’m just presenting you with how I do it and you can change it. 

Here’s the SP that I use in the DB that gets called by this script.  It’s very simple. 

CREATE procedure [Collector].[spServiceAcctInsert]

@ExecutionDateTime datetime,

@InstanceID int,

@ServiceName varchar(50),

@StartName varchar(50)

 AS

 Insert dbo.ServiceAcct

Select

@ExecutionDateTime,

@InstanceID,

@ServiceName,

@StartName  

And now here’s the table that you’re logging to:

CREATE TABLE [dbo].[ServiceAcct](

      [ID] [int] IDENTITY(1,1) NOT NULL,

      [ExecutionDateTime] [datetime] NULL,

      [InstanceID] [int] NULL,

      [ServiceName] [varchar](50) NULL,

      [StartName] [varchar](50) NULL

And that’s all there is to it. From here, now that you’ve got all of your service accts and the AD accts associated with them, you can easily query this table to see the impact of changing a password or just see how bad your security is. This is useful info to have. You don’t have to run this kind of thing often though. In fact, it’s not a query that I have scheduled. I typically just run it when I want to know something specific, but you may have a reason to schedule it.

OK guys, that’s all I’ve got on that.  Enjoy powershelling.

Today’s Powershell Fun

I’m having big fun with PS today because I’m installing several new R2 boxes on Windows Server R2.  Where does PS come into play you ask?  Well, good question… I only wish I had a good answer.  Ok, I do.

In order to install SQL Server R2 on Windows 2008 R2 you have to first install the .NET Framework.  However, the SQL installer doesn’t do it for you.  It just tells you that you have to install it.  So the normal way is to go into the server manager and then to ‘roles’ and then ‘add a role’.  Once you’re in the add role wizard choose ‘Application Server’.  Here are some brief screens of the process.

As you can see I’ve already installed it on this server, but it’s pretty easy to figure out from here.  Just install the .NET Framework (it’ll actually already be checked for you) and you’re golden.  Now, that takes too much time for me, especially when I’m installing several boxes at once.  So what I like to do is call on powershell for this task.  I’m going to take some extra time here to show you a couple things, but the process once you get it down is much faster than the wizard.

First of all, you have to load the windows modules into your PS session.  To do it manually, you can open PS and type the following command:

> Import-Module ServerManager

Here’s a screen:

However, now you’ll be able to run the next command.

> Get-WindowsFeature

This will show you all the features available and which ones are already installed.  Here’s a screen of the output.

And of course, you’ll notice on mine, it’s already installed, but it’ll be unchecked on yours.  OK, now you’re ready to do the install itself.

And for that, you need yet another command, but don’t worry, it’s easy. 

> Add-WindowsFeature AS-NET-Framework

Notice I put an arror by the name of the feature above that you’ll feed to the Add-WindowsFeature cmdlet.  So in case you didn’t catch it, the syntax is simply the cmdlet and the name of the feature you want to install. 

Here’s a screen of the line as it’ll look when you type it.

And here’s what it looks like while it’s installing:

And that’s it guys.  The install is actually much quicker from the cmdline and you don’t have to worry about making a mistake through the GUI.  And of course you don’t have to go through all these steps every time.  Why not write a short script to do it for you?  It would look like this:

Import-Module ServerManager
Add-WindowsFeature AS-NET-Framework

That’s it.  That’s all it takes to install it from a script.  Now you can save that off to the network somewhere and just use it whenever you like. 

OK, that’s all I’ve got.

Oh y, one more thing… you don’t have to import the servermanager module every time you run this.  You can use the PS shell that loads all the windows modules for you.  You can find it here:

Ok, THAT’S all I’ve got.

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.

How to Monitor SQL Services with Powershell

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.

How to save your life with powershell.

I’m always preaching the wonders of powershell, but I think quite often I forget to stress the impact that it can provide. And today is one of those times when I’m able to give you a real life impact of how PS can save your life… ok, your job anyway.

One of the things PS does really well is scripting DB objects. It’s always one of the first things I show that’s really useful in my classes and it always gets a appropriate amount of gasp. But other than creating dev systems from prod, what good is it? Well, it’s a fabulous way to script your prod environment on a regular basis just to have the scripts handy. If you’re able to use PS to easily script your tables, SPs, views, users, etc. then you’re going to have a history of your DB that you can pull from anytime. And if you’re smart enough to store those scripts in txt files with dates appended to them, then you can always get back a complete version of your DB for any given day. Cool huh?

And the implications of this are huge. Not only can you create a complete environment for any given day so that you can compare it with the current version, or any other version you like, but much more likely is the scenario where someone makes a mistake in prod and needs some small piece of code replaced. So someone drops an SP, or alters a view, or kills a job, or anything else along those lines. Of course, this won’t help you with data accidents, but imagine the possibility of being able to pull your butt out of the fire because you were able to recover from a mistake you made… or anyone else made.

This came not only in handy, but actually saved my ass at my last gig where I had these scripts running and accidentally dropped all the SPs in our prod DB. I could have sworn I was on dev, right? So I had my PS scripts to import the objects again too, so as soon as I realized my mistake I recreated all the SPs and all was well with only a minor hiccup to our reporting cycle. But that’s exactly what I’m talking about. If you have these things ready to go you’re ready for whatever kind of disaster comes up and if you not only have the scripted objects sitting there, but also the scripts to put them back, then you’re in the best shape you can possibly be in.

My favorite way to store these like I said is in txt files.
I put them in a folder named for the DB, then another subfolder after the object type, then I name the files by schema.objectname.txt. So a sample path to an object would look like this:
c:\scripts\DB1\SPs\dbo.SP120100714.txt

Now to parse the date back out is a simple matter and can be handled with a simple PS script. And the scripts you need to restore can be as easy or complicated as you like. You can have any or none of the following:

Restore all tables
Restore all indexes
Restore all SPs
Restore all views
Restore single SP
Restore single view
etc…

You could also combine them into a single PS script more or less that you just pass an object type to and it figures out the path and does the restore for you that way. Either way is good really.

So setup your PS scripts to script your DB objects and store them someplace. It’s only txt so you can keep them pretty far back if you like, and even archive them in zipped files if you like.

Good luck.

24-hrs of PASS is Online

If you haven’t gotten your email yet, or if it’s just gone into your spam, the 24-hrs of PASS recordings are ready.

You’ll find the main page here (it’ll make you login): http://www.sqlpass.org/LearningCenter/24Hours.aspx

And don’t forget to look specifically for my session here: http://passfiles.sqlpass.org/uploads/24Hours/05_20_2010_05AM_downloadHFP/Engine/Default.htm?http%3A%2F%2Fpassfiles.sqlpass.org%2Fuploads%2F24Hours%2F05_20_2010_05AM_downloadHFP%2F

If the link above doesn’t work then mine is Session #18.

Exciting News

Hey guys I’ve got some exciting news!! As it turns out you can use powershell to admin your servers.  That’s right!

And to prove it, here’s a nice little script I wrote this morning to change the service acct and passwords on all the boxes I wanted.

Actually, I’m just going to show you the single box version, but you all know how easy it is to turn this into a multi-box script.  Here it’s the method that’s important.

I’ll show you 2 ways to do it and I think they’re both equal really.  It just depends on your preference.

$service = gwmi win32_service –filter “name=’MSDTC’”
$service | %{$_.Change($null,$null,$null,$null,$null,$null,”NewUserAcct”, “NewPassword”)}

Here’s a screenshot of the same code.

image

That’s it.  That’s all you need to change the user acct and password for a service.  But we’ve still got a few things to talk about so you understand everything that’s happening here.

  1. If you want to use this against a remote box, then just use the –computername parameter.
  2. Notice I used the –filter param also.  This filters the results on the server itself so you don’t pass all the services across the wire and then filter them.  That method would look like this: > $service = gwmi win32_service | ?{$_.Name –eq “MSDTC”}
  3. The Change() method takes a fixed number of params so you have to put in all the params at least as placeholders.  That’s why all the $null values are in there.
  4. I’ve seen other methods for doing this where they name the params and don’t actually pass in all of them physically, but I haven’t had any luck with these.
  5. Here’s a site that explains all the other parms and the error codes you can get when using this method.  http://www.scriptinternals.de/new/us/Support/Internal/WMI_Win32_Service_Change.htm

Ok, I know I promised you 2 ways to do this but I’m out of time so this one will have to do.  The other one uses the same method, it’s just a different way to script it so you’re not really losing anything.