The Multifunctioning DBA

April 30, 2010  10:00 AM

Powershell Rant

Colin Smith Colin Smith Profile: Colin Smith

As you all know I am a big fan of Powershell and I think that it is a great language. I do not think that it is the best or only language that an Admin should know. I think that it depends on your job and what you are attempting to accomplish. I think that if you do most of your work in a unix environment that Powershell will not be a whole lot of use to you. I do think that if you work most of the time in a Windows world that it is worth learning and at least understanding how it works. Powershell is integrated into Sharepoint, Exchange, SQL Server, Desktop OS’s as well as Server OS’s and is a very good tool if that is where you spend the majority of your time.

I work with one Windows Admin, all he does is Windows, and he out right refuses to learn it. I have attempted to explain as well as show him why it is such a great tool. He will not listen. I am done trying to help him and I have moved on but I had to rant about it just a bit. I do not understand why you would not want to get a better tool to do the job. just recently we had an issue where we needed to change a setting on 11 servers. Now 11 is not that many but I thought what a great reason to break out the good old PS. I mentioned to him that I thought we could script this out in about 5 lines or less of code or he can manually log into each server, navigate to where the setting is, right click on it, and change it, and then log out. Now this is about a 3 to 5 minute process and he will have to do it 11 times. So that is 30 minutes up to an hour of time. Or we can write a 5 to 10 minute PS script that will do it for us. I know what option I want to choose but he chose the latter. makes no sense to me but I do not care anymore. All of the other Windows Admins that are learning and using PS will be more productive and he will eventually fall way behind all of them. His choice and I think he will suffer for it.

That is all I have to say about that.

April 29, 2010  11:00 PM


Colin Smith Colin Smith Profile: Colin Smith

I have just set up a test environment using virtualbox by Sun. this is a free Vitualization platform. I created two VM’s that are both running Server 2008 and SQL Server 2008. I will use this setup to test things like mirroring, replication, and just other generall testing. I am sure I will be doing some Powershell stuff here as well. It should be good but this is the first time I have used Virtual box. I am hoiping that it will work out for me and I will let you all know about any issues that I have and how I overcome them.

April 29, 2010  9:32 PM

ddlgen in Sybase

Colin Smith Colin Smith Profile: Colin Smith

I completed an upgrade from Sybase 12.5 to Sybase 15.0 recently and I found that it ended up breaking some scripts that we use to generate ddl for all instance devices and all create databases. The scripts that we were using were writting log ago by people that are no longer working for the company. I decided to change this up to use ddlgen to create all this for us. ddlgen is a very nice little program that is able to generate the ddl and output it into files for use, if needed, in the future. Here is what I did to get all the devices ddl out of an instance.

ddlgen -U username -S servername -TDBD -N% -O outputfilename

That is simple enough to do and man it works great. This will get you the DDL needed to init all of the devices on the instance.  Now I need to get the DDL to create all of the databases. For this I wrote a little shell script to get a listing of all the databases and then do the ddl gen for each. Here is that.

dblist=`isql -S servername -Usa << endit | tail +4
use master
set nocount on
select name
from sysdatabases
echo $dblist
for db in $dblist
ddlgen -Usa  -SCIRC_DBA -D $db -TDB -F% -O $db.definitions.out


April 21, 2010  3:51 PM

SQL Saturday 47 Phoenix Session

Colin Smith Colin Smith Profile: Colin Smith

Well I have done it now. After a Debate with a few people on twitter talking about whether or not Powershell is worth the time to learn I decided I would do a session about it. Why a DBA needs to know Powershell. It will be a basic 100 level session about what powershell is, how to install and configure it, and how to use it in general. Then I will dive into some of the reasons that it is useful for a DBA to know. I will have plenty of demo’s and example code that you will be able to use as well. I think Powershell is a vital tool for anyone doing Administration in a Windows world, and it is time to show why I believe that to be so. So please sign up for SQL Saturday 47 in Phoenix, and if I get picked, come check it out.

Now I have to go start putting a 65 minute presentation together. Wish me luck on that.

April 19, 2010  8:12 PM

Quick Powershell script to clean up text file

Colin Smith Colin Smith Profile: Colin Smith

I had a script that was a list of user tables in a database. This was gathered from a sybase instance using the following simple sql query.

select name from sysobjects

where type = ‘U’

Simple enough but my output had blank lines between entries and this was no good. I needed to strip the spaces and new lines out as well as add a database name to the begining of each table name. I needed that for the script that is going to use this file as an input. Here is what I did in Powershell to make it all work.

$tables = $NULL
$tables = Get-Content “c:\tables.txt”
foreach($table in $tables)
  $table = $table.trim()
  $table = “DBNAME.$table”
  $table >> “c:\newtables.txt”


So I had a input file name tables.txt and that had my list of tables. Formatting was wrong and I needed the DBNAME.tablename to be in the new file.

I simply read the file in and go thru each line. I use the trim() function to get rid of all spaces and then I add “DBNAME.” to the front of the table name. I then output that to a file called newtables.txt and now I have the input file, properly formatted, for the script that needs it.

April 19, 2010  4:50 PM

SQL Saturday in Phoenix

Colin Smith Colin Smith Profile: Colin Smith

I have posted this before, but I was asked to post about it again. SQL Saturday is a go for Phoenix. We have a date of July 17th at the DeVry University Phoenix Campus. I know that we have at least one Sponser for the event but I think we have more. We are still hoping to get more and more presenters and just any type of help that we can for the event. I do not for sure of any speakers being confirmed but I have heard, via twitter, that Denny Cherry (Blog | Twitter), SQL Server MVP will be in town presenting for us. I know that I am really looking forward to meeting him and also look forward to hearing him speak.

Here is a link to the official Registration for SQL Saturday 47. Also if you are on Twitter, and you should be if you want to be a part of the community, please follow hashtag #sqlsatphx for updates and for the latest news about the event. You can also contact Samson Loo (Blog | Twitter). Just click the twitter link and follow him.  More information can also be found via the Event Home Page. Please sign up come help us make this event great so we can have many more in the future.

April 15, 2010  10:00 AM

Useless Meetings

Colin Smith Colin Smith Profile: Colin Smith

So, today I have a two hour long meeting that is a new employee welcome meeting. I have worked for my company for about 6 years in all and I was moved over to the corporate team just under a year ago. Since the move to corporate I guess that makes me a new employee. I am OK with that, since I did not lose any vacation, or any other benefits that come along with over 5 years of employment. But I have to say that I think this two hour meeting of department heads talking about what a great company I “now” work for is a wate of my time. As Brent Ozar (Blog | Twitter) says in his article Tuning SQL Server Transactions – and Meetings, “Meetings are like bad blocking quries.” And let me just say that I hate both. I am a multitasker and I will continue to work on other things while attending the meeting, but I know that it will slow me down. I have a lot to do and I do not really enjoy taking time out of my day to attend a two hour meeting that I doubt I will get anything out of and I certainly will not get anything pertinant to my work out of it.


Well that is my rant for today and I hope that the meeting is better than I expect. Also did I mention that it is all over the web. Not even face to face. But, I guess that really plays in my favor.

April 13, 2010  5:50 PM

Get a quick Idea of Space used for SQL

Colin Smith Colin Smith Profile: Colin Smith

A couple weeks ago I had my SAN admin ask me about how much disk I would need to backup all of my sql instances to the SAN. Well I do not know that off the top of my head but I do have a list of all my instances and I know a little bit about powershell. So I decided to throw together a quick and dirty script to connect to all instances, gather size information about all databases on those instances and get me a total. Here is what I did.

############################################################ ## DOC This script will create an email containing the ## DOC combined size of all databases in instances defined ## DOC in the sqltab.txt file. This will be used to estimate ## DOC the amount of SAN disk that we need for backups. ############################################################ #Read thru the contents of the SQL_Servers.txt file $size = 0 $outfile = "\\share\Size.txt" Clear-Content $outfile $servers = Import-Csv "\\sharename\input.txt" ######################################################### foreach ($entry in $servers) { $machine = $entry.server $iname = $entry.Instance $torp = $entry.TorP if ($torp -eq "Prod") { if ($iname -eq "Null") { $instance = "$machine" } else { $instance = "$machine\$iname" } $instance = $instance.toupper() #Connect to SQL Server and get database size information using SMO [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null # Create an SMO connection to the instance $s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance if (!$?) { Echo "Error connecting to $instance, Please check" >> $outfile } $dbs = $s.Databases ## Get infomation on all dbs in instance. Add up all database sizes foreach ($db in $dbs) { $dbname = $db.Name $dbsize = $db.Size $size = $size + $dbsize Echo "$dbname on $instance is $dbsize MB" >> $outfile } } } $size = $size / 1048576 ## dividing by 1 TB so I can get my output in TB. $size = [math]::Round($size, 2) ## rounding number to get two decimal places. Echo "Total Size of all Production databases that I could connect to is $size TB." Echo "Total Size of all Production databases that I could connect to is $size TB." >> $outfile

I am using the same input file that I have written about in the past. It is a txt file that is comma seperated and here are the headers.


I use this file for my scripted monitoring so you will not need all the fields that I have in the file for this script. Please let me know if you have any questions.







































April 13, 2010  5:00 PM

Powershell: AD Groups

Colin Smith Colin Smith Profile: Colin Smith

Two weeks ago was part of implementing a large upgrade in one of our Sybase environments. In order for this upgrade to be succesful we had to do some work in some AD groups. We needed to find all the users that would need to be a member of the new group that we were creating and take them out of the old group. The old group still needed to be around though, for users that were not affected by the upgrade. I knew that I would we had one group, that all the affected users were already a member of, so I could copy that groups members into the new group. However, if I did not also take these users out of the old group, then nothing would work. So here is what I did.

$users = Get-QADGroupMember "GroupA" foreach ($user in $users){ $user | Remove-QADGroupMember "GroupB" $user | Add-QADGroupMember "GroupC"

Keep in mind that you will need the Quest AD cmdlets installed in order for this to work.


Let me know if you have any questions.

April 7, 2010  9:00 AM

Phoenix SQL Saturday Date and Location Confirmed!!

Colin Smith Colin Smith Profile: Colin Smith

I got a message last week verifying that SQL Saturday will be ion Phoenix on July 17th at the DeVry university Campus. Man I am excited about this. I think we have some good sponsers lined up for the event and I hope that we can get, at least one sponser, no names just yet, to lend us an MVP or two for the day. That would be awsome. I am excited after seeing some of the sessions that the organizers are hopeful for. It is going to be a great day of free training, networking, and just fun.

I am also glad that it will be at DeVry. Since I am a 2003 Grad of DeVry I am biased but I like the campus and I think that the classrooms will be perfect for the sessions. Not to big and intimidating for the presenters but big enough that the sessions willbe able to have a good number of people attending. Please let me know @smithco32 on twitter if you would like to help or present. Or you can also contact Sampson Loo directly Twitter @sqlsamson, email, or give him a call @ 480-389-6930.

Please come out and make this a good event so we can have more in the future.

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: