The Multifunctioning DBA

June 25, 2009  6:12 PM

Powershell Query AD

Colin Smith Colin Smith Profile: Colin Smith

I have talked in the past about some cmdlets that Quest Software provides for Powershell. Well today they came in usefull for me. My boss came up and wanted to know when the last time a certain service account had authenticated to the domain. Now we log all users logging in via our login script but since this is a service account, the login script does not fire and therefore we do not get a log of the event. So using the cmdlets from Quest Software I did the following and got what we needed.

$1600users = Get-QADUser | where {$_.logonname -like “*p1600-10*”}
foreach($1600user in $1600users)
$name = $1600user
$date = $1600user.LastLogon
echo “$name logged in on $date”

That is it and it let us know that we have two accounts that have to do with the server services that we were interested in and also let us know the last logon time for each.

Hope this is helpful.

June 25, 2009  3:30 PM

SQL Server Health Report

Colin Smith Colin Smith Profile: Colin Smith

Yesterday I posted about a great article that I found on how to use Powershell to create an excel file and also use SMO to get information about SQL Server. I am working on a report that I will have run daily. This report is going to give me an overall health check of my SQL Servers. I am going to have it report to me if all of my important jobs are set up and running properly, These jobs being things like backups, tranlog backups (if the Database is using the Full Recovery Model), Rebuild indexes, and things like that. it will tell me how big each Database is, where the MDF and LDF files are on the filesystem, How much drive space we have available for those databases on the filesystem, how much space allocated to the DB is Free, and finally, for instances that have mirroring set up it will tell me the state of the mirror.

For the servers that I am working with on this report I feel that this really does give me a good health check of the server. I am sure that as time goes on I will add more and perhaps modify this to output as a web page and then put some nice eye candy on it for things like cache hit ratio and other metrics like that. I will be posting the code for this report when I am complete with it.

June 24, 2009  3:54 PM

Microsoft Live Mesh

Colin Smith Colin Smith Profile: Colin Smith

So, I do not know about you allI can say that since I know something about computers I get hit up for free computer help from family all the time. I do not really mind this but it is sometimes hard to get to them in a timely manner. Well I stumbled upon Microsft Live Mesh the other day and it is pretty cool. Here is a link so you can check it out.

This gives you 5GB of space on a virtual desktop so that you can synch and share files between computers, and it will let you remote a computer while letting the people on the other end watch, or you can hide it from them. I have used this a couple times and I like it. It is still in Beta so take it with a grain of salt. I hope that it gets better but I really love the Idea. Before I found this I was tempted to sign up for another pay service to do remoting. All you need for this is a Windows Live ID.

Let me know what you think about it.

June 23, 2009  7:54 PM

Powershell and SQL Server

Colin Smith Colin Smith Profile: Colin Smith

Today I wanted to use Powershell to create a report about my SQL Servers. I found a great article about how to do this using Excel at this link.  the author does a great job here and has a great sample script that you can start with and then modify as you need. I am doing some modifications and adding some information from the filesystem to the report as well. If you want a good quick easy way to get some eyecandy reporting done in Powershell this is a great article. Thanks to Edwin Sarmiento for putting this out there.

June 23, 2009  2:34 PM

Still working towards Cert.

Colin Smith Colin Smith Profile: Colin Smith

Sorry that I have not posted in so long. I have had some family things come up and I have ben very busy. I think that I have all that worked out, except for being busy, and I should be back to posting at more regular intervals.

I am still working on getting my MCITP DBA. I, and the guys I work with, are still working on the first book. I must say that I really do still have a lot to learn but I love that. That is why I chose to get into IT, the education never ends. I just never have enough time to do all that I want and to read all that I want. I am still working with Powershell alot and trying to do things with SQL Server and Poweshell combined. I am working on some small scripts that will gather important information from SQL Server for us and I will share them as I complete them. Anyway, I still have about 5 chapters to go in the first study guide book and then I think that I will read it again and this time I will do all the practices and examples as I go. I am not this time around because I am just attempting to absorb as much as I can and get a basic understanding. Once I have that then I will be able to go though it again and dig deeper and do the stuff and really understand what it is all about and how it works.

Anyone have any thoughts on how I can better prepare myself for the MCITP exams I am all ears.

Thanks and I will be posting again soon.

May 29, 2009  10:01 PM

Oracle Upgrade Final Test this weekend

Colin Smith Colin Smith Profile: Colin Smith

The Oracle 9 to 10GR2 upgrade that I completed last week will be getting its final test on Sunday. We will be failing over to our standby server and see what happens. I am not worried about this as all the files that are associated with the upgrade are on disk that is shared between the two cluster nodes. The only exception is in /var/opt/oracle and I have modified those files to be what they need to be. Keep your fingers crossed for me but I think I am in good shape.

May 29, 2009  9:58 PM

Exchange and Powershell

Colin Smith Colin Smith Profile: Colin Smith

I was just asked to take a look at writing a script for our new Exchange environment. Now I do not have a lot of experiance scripting for Exchange but I love a Powershell challenge. The problem was that all of our distribution groups had been set up with one option incorrect. I was asked if I could script a change for all groups. If not then this would take days, perhaps weeks to fix by clicking on every group and changing the option and saving.

I logged into the exchange server and started tooling around. I found that I could get a list of all the groups by doing a simple get-distribution group command as follows:

get-distributiongroup | select name

This gave me the names of all groups. I sent that to a flat file to use later. I then used the get-member command to find the option that I needed to modify. Then I was able to do a set-distributiongroup “name of group” -proertyname 0. That  command made the modification that we needed. After verifying that I wrote a foreach loop and I was done in less than 30 minutes. When I called our IT director back and let him know that it was complete he was shocked and very happy. Powershell can make you look very good to upper managment.

Let me know if you have anything that you need a script for.

May 29, 2009  3:02 PM

Love the Internet

Colin Smith Colin Smith Profile: Colin Smith

I love the internet. I really do, I mean I do not know how I lived without it. The internet makes so many things possible. I am good at my job and I am a smart guy, but I do not know how IT people did it before the internet. I Google and search online for so many issues that I have never seen before and odds are someone else has and they have put the solution, or at least what they tried, on a blog of somesort. But it is so much more than that. Where else can you go shopping 24 \ 7 and almost always find the crazy off the wall thing that you were looking for? Like I went Golfing last week and I was having a horrable game. I got mad and threw a club down at the ground after miss hitting the ball with that club 3 times straight. The club broke, clubhead sheard right off the shaft. This was a set that I had gotten a few years ago and is not made any more. I got home searched for the club and three days later I had a replacement club that matches my set. This would have been near impossible before the internet. I mean really how did anyone get anything done?

May 29, 2009  2:56 PM

Notepad style editor

Colin Smith Colin Smith Profile: Colin Smith

We all like notepad since it is quick easy and very lightweight. But what if it just does not do all that you want? Well I have used programs like Ultra Edit and I love it. The downside is that it is not as lightweight, you have to buy it, and the intallation is a pain. Great App and it does just about anything that you could wish for. I have found an alternative to this though. It is lightweight, free, and install could not be more simple. It is Notepad++. This may not have all the features of Ultra Edit and other programs like that but it can do so much. Multiple tabs, text highlighting, great search and replace, and if a feature that you need is not there then you may find a plugin for it. Like I really like the compare feature of many editors and this did not have it. I was able to get it though. Just download and install the plugin and you are ready to go. If you do not see a plugin that works for you then you can write it. I love that. I have not done that but I know I can try if I need added functionality.

Give it a try: get it at

May 29, 2009  2:49 PM

Powershell SQL Load Script

Colin Smith Colin Smith Profile: Colin Smith

In my environment we have some servers, test servers, that are loaded from the Production Servers on a regular basis. Since this is the case I have decided that I would like to attempt to script this. I just started but I am already well on my way to getting this done. I am pretty sure that I will be able to do what is needed here, I just need to take it slow and work through the issues as they arise.

So far I have been able to have the script list all the instances that may need to be loaded and ask what instance you would like to load. I select the number next to the instance, try to avoid typing errors, and the script then determines what host this instance needs to be loaded from.  So essentially I have done all of my variable setup at this time. Here are the rest of the steps that I have in mind for this script.

Once I know the host and the destination servers I will connect to the destination and delete any backups that I might have on disk. I will not need them anymore since I am loading from Prod anyway. I will then check to find out the amount of free space that I have on the disk and compare that to the size of the production backup file. If I have room then I will copy the prod backup to the test host. Once that is complete I will lock all logins, kill any spid in the target DB, and then load the DB. After that is complete I will have to deal with users in the DB and making sure that I can match them up with the appropriate logins. Then I will make the DB available to users again by enabling the logins.

Simple enough plan but I am sure that I will run into some hurdles along the way. Should be a fun script to do though. Any thoughts or ideas about this script please let me know.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: