The Multifunctioning DBA

May 28, 2010  8:00 PM

Oracle on Windows

Colin Smith Colin Smith Profile: Colin Smith

I work in an environment that does use Sybase, Oracle, and SQL Server as the main RDBMS’s. All of our Sybase and Oracle are running in Solaris Unix and or Linux. I just found out that I am about to have the opportunity to install and configure Oracle on a Windows Server 2003 R2 box. I am looking forward to this as I am not that great with Oracle so I am sure that I will learn alot but also I will get to gather some performance metrics about Oracle on a Windows Host. This instance is going to be very small and very low load so I will not really be able to compare it to anything that I have running now. Should still be a good time and I should learn a lot.

May 28, 2010  6:16 PM

Database Restores

Colin Smith Colin Smith Profile: Colin Smith

In my environment we have a few instances where the customer would like to have a refresh done from Production to a test server. This is good for me since it gives me a chance to test my backups and make sure that they are good. It is good for the developers because then they are working witht the current data set. Unfortunatley, it is not good for them to always have a full copy of current production data on that system, so I can not do replication or even better log shipping. I have to manually restore a backup of production to the test instance. This is not a big deal but it is sometimes time consuming. I am working on a PowerShell script to automate this process for me. Should be pretty easy to do and I will post what I come up with.

May 27, 2010  10:00 AM

Powershell Gui

Colin Smith Colin Smith Profile: Colin Smith

A while back, about a year ago, I wrote some custom scripts to monitor all of our SQL Server instances for errors. I have a driver file that has all the information that the script needs in order to find errors on the SQL Servers and it just walks thru them and checks for any errors that I might care about. If it finds an error it sends out a page to the on-call DBA and also sends emails out to the DBA team about the error found. I also wrote a script called workit, because we are working the issue, that when run, will output the errors found on all servers that have errors. This script has many functions to aid in resolving any issues that are found and also stops the emails and paging about the error. Everything really works well, but I was watching a training video done by Buck Woody(blog | twitter) and he had an example of using the .net windows.forms assembly. That got me thinking that I could wrap my workit script into a GUI. I went out and found a quick basic tutorial on using the forms assembly and now I am hard at work building a gui around my scripts. This will allow me to let others, that are not so savvy with command line, to run this script and help us to resolve issues. This should be a fun project so I will keep you up to date on my progress. I love powershell because I can always find something useful and fun to do with it.

May 26, 2010  5:00 PM

Technet and why I love it

Colin Smith Colin Smith Profile: Colin Smith

Not long ago I decided to purchase a Technet Subscription from MS. This is one of the best things that I ever did. I get pretty much unlimited access to all MS products and I get to try them all out. I do not get to use the products in production of course, but for learning this is the best. I use it all the time especially for SQL Server. I have multiple VM’s set up in a lab so that I can test test and test some more. I have learned a lot by having the ability to download and install as much as I need. Currently I am working on building a server 2003 VM that I will install SQL Server 2000 on and then I will use that in conjunction with a server 2008 VM running SQL Server 2008 to try and test out replication from 2000 to 2008. I currently have a business need for this but I do not have a true test environment for that. So I went home and set it up in my personal lab so that I can work it all out before I throw it into production at work. This is great for me. I get to learn first hand about all the new products and have them fully licensed. That means that I do not have to rebuild every 120 days when the trials run out. I will be able to use these VM’s forever in my testing. If you do not have a Technet Subscription then I highly recommend that you go out and get ond. For someone that works in IT I think it is a must. Here is a link to Technet. Also if you do a google search you will be able to find a discount code. Totally worth it for the full price though.

May 26, 2010  7:00 AM

Powershell to get unique login count from SQL Server

Colin Smith Colin Smith Profile: Colin Smith

Just like I needed to get a count of unique logins to an application on one of our Sybase instances I also needed to get these values for a few of our SQL Servers. Since SQL Server uses mixed mode authentication and we are using AD groups to aid in the security of the SQL Server, I could not just write a query against the instance to get a list of unique logins. I could have but it would have returned the groups and not the individuals that are members of the groups. So I wrote a PS Script to go out and connect to the SQL Server, get a list of logins and then parse the AD Groups to get me a listing of all the members of those groups. Then it takes that list and gets unique logins, since a user may be a member of more than one group, and then counted the number of users. First I made a connection to the SQL Server using SMO, then I find all the logins and filter them based on whether they are a user or a group. If it is a group then I query the domain to find the group members and output all those users as well as the local sql users to a file. Once I have looped thru all groups then I get the contents of the file and sort and get unique and then I select a count of the new object. That is it. If you have any questions or would like to see the code then just let me know.

May 25, 2010  5:00 PM

Sybase SP_Who modified

Colin Smith Colin Smith Profile: Colin Smith

A while back I wrote a post about how I took a system stored Proc called SP_WHO and I modified it to only give me results for a certian subset of logins to the Sybase server. I let it run once an hour every hour for two weeks and then presented the numbers to my boss. He was very pleased that I was able to get these numbers from him and that the numbers validated what the application team was telling him. This gave him the insights that he needed in order to better estimate the licensing requirments for a new project that is coming down the pipe. I think that this is important to know so that you will not be afraid to modify some code to get what you need. By modifiying something that Sybase already had done, really saved me a lot of time and effort in order to get my boss what he needed. Just remember to rename the proc so that you do not replace the originals.

May 25, 2010  2:38 PM

SSAS Class

Colin Smith Colin Smith Profile: Colin Smith

I recently attended a deep dive bootcamp course in SQL Server Analysis Services. The class was geered more towards developers but it gave me a better understanding of what a cube is and how it works. The part that I was most interested in was the security portion and the 2008 version gives us a lot of ways that we can lock down access to the cubes. This was a good part of the class for me as securing our data is very important to me. I am currently working on a data mart project so the class was very timely for me. If you have the chance I highly recommend looking into classes like this. They are expensive but they really do give you some good insights of how the system works and how you as a DBA can make it better faster and more secure.

May 12, 2010  5:00 PM

Modify sp_who in Sybase

Colin Smith Colin Smith Profile: Colin Smith

After the Sybase upgrade, my boss needed to know how many users we have connecting to a specific database using a specific application. In order to accomplish this I extracted sp_who and modified it to gather the information that I needed. I also did not overwrite the original sp_who but I had it create it as a new proc. Make sure that you do not overwrite the original sp_who as you may need it in the future. I went to the application team and got a list of logins that they use for the application to connect to the database, this way I could filter off of these to get accurate counts. So here is what I did.


































































OBJECT_ID(‘dbo.sp_circ’) IS NOT





DROP PROCEDURE dbo.sp_app 


IF OBJECT_ID(‘dbo.sp_app’) IS NOT






‘<<< FAILED DROPPING PROCEDURE dbo.sp_app >>>’









‘<<< DROPPED PROCEDURE dbo.sp_app >>>’



/* Sccsid = “%Z% generic/sproc/%M% %I% %G%” */

/* 4.8 1.1 06/14/90 sproc/src/serveroption */


** Messages for “sp_circ” 17nnn


** 17231, “No login with the specified name exists.”




procedure sp_app @loginame varchar(255) = NULL


declare @low intdeclare


@high intdeclare


@spidlow intdeclare


@spidhigh intdeclare


@len1 int, @len2 int, @len3 int




@@trancount = 0





set chained






transaction isolation level






@low = @@minsuid, @high = @@maxsuid,@spidlow = @@minspid, @spidhigh = @@maxspid





@loginame is not





select @low = suser_id(@loginame), @high = suser_id



if @low is NULL








if @loginame like “[0-9]%”








select @spidlow = convert(int, @loginame),@spidhigh = convert(int, loginame),@low = @@minsuid, @high = @@maxsuid 














/*** 17231, “No login with the specified name exists.”
























select distinct loginame=suser_name(suid),dbname=db_name



into #whoappresult 


from master..sysprocesses 


where suid >= @low

and suid <= @high 


and spid >= @spidlow

and spid <= @spidhigh 





and suser_name(suid) like





or suser_name(suid) like















exec sp_autoformat @fulltabname = #whocircresult,@selectlist =

“loginame, dbname”,@orderby =

“order by loginame”






drop table #whoappresult  









sp_procxmode ‘dbo.sp_app’,‘anymode’






OBJECT_ID(‘dbo.sp_app’) IS NOT






‘<<< CREATED PROCEDURE dbo.sp_app >>>’






‘<<< FAILED CREATING PROCEDURE dbo.sp_app >>>’






EXECUTE ON dbo.sp_app TO



May 11, 2010  5:16 PM

Sybase DR

Colin Smith Colin Smith Profile: Colin Smith

Recently I did an upgrade from Sybase 12.5 to Sybase 15.0 and this upgrade changed some system tables that broke the scripts that we had in place to generate DDL for all the devices and database creates. I have been working on fixing this issue and I have resolved the issue by modifying our scripts to use DDLGen that comes with Sybase. I chose this solution because DDLGen is a Sybase tool that has been around for a while and I do not think that it will be going away anytime soon. DDLGen is a very cool utility that allows you to get DDL for all objects in the server. Here is what I did since I only wanted to get DDL for the devices to be added and the Create Statements for all databases on the server. I am not getting DDL for all DB objects like tables, triggers, constraints, and things of that nature.

This is how I generate a list of all the Devices that need to be part of the Server.

ddlgen -Usa -S $server -P $password -TDBD -N% -O $dis_rec_dir/$server.devices.sql

This will gather all the DDL for all devices and place them in the dis_rec_dir folder in a file called servername.devices.sql. In the case of a disaster, now I can just run this script and all the devices, as long as the raw files are available, will be added to the server.

Here is what I am doing to get all the Database Create DDL.

ddlgen -Usa -P $password -S $server -D $db -TDB -F% -O $dis_rec_dir/create.$db.sql

This will gather the DDL for all databases on the server and put them into the dis_rec_dir in a file called create.databasename.sql. Now I can run this script to create the database on the server on all the correct devices and with data and log on the correct devices.

The script is written so that it will loop thru and generate the DDL for all databases on the server.

April 30, 2010  6:00 PM

VMWare and Powershell

Colin Smith Colin Smith Profile: Colin Smith

The other day I was asked to put a script together to disable all the backup nics that we have installed on a set of Virtual machines. This was cool for me as I had not done anything with VMWare in Powershell. I went out and downloaded the VMWare Powershell tools and I was on my way. Here is what I did and this disabled any NIC with a name like bak on a set of servers that I was able to filter by a common name string.








= Get-VM name*





($machine in $machines){



$bak = Get-NetworkAdapter -VM $machine | where{$_.networkname -like “*bak*”} } 



Set-NetworkAdapter -NetworkAdapter $bak -Connected $false Confirm:$false }

That is it. It went out got all machines with a name like “name* and disabled the NIC that had a name like “*bak*”. Easy and way faster than manually making the changes.

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: