The Multifunctioning DBA

January 15, 2012  8:00 AM

Connect-sql Function

Colin Smith Colin Smith Profile: Colin Smith

This is a function that I keep in my profile so I can call it in any of my scripts that will run from my profile. It makes it easier for me so it might for you.

function connect-sql ($arg)
if($arg -eq $null)
$server = Read-Host “enter instance name to connect to”
$server = $arg
$global:s = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $server


This function allows you to make SMO connections to your instances very easily. You can pass it the instance name or it will ask you if you do not. Hope that helps

January 14, 2012  8:00 AM

PS script to check SQL Server Groups

Colin Smith Colin Smith Profile: Colin Smith

This is the script that I threw together to check and see if a user is in multiple AD Groups that are logins to the SQL Server instance. This helped me resolve an issue and it may help you look for things like explicit deny or some other reason why a user does not have the access they need or perhaps they have too much.

connect-sql “sql server name”
$groups = $s.Logins | where {$_.LoginType -eq “windowsgroup”} | where {$_.Name -like “ssg\*”}
##$groups | select name

foreach ($group in $groups)
$gname = $group.Name
$ingroup = $null
$ingroup=Get-QADGroupMember $gname | where {$_.samaccountname -eq “usersamname”}

if ($ingroup -ne $null)

Hope that will help

Look for the connect-sql function in tomorrows post

January 13, 2012  5:35 PM

Strange Issue with MSSQL error 18456

Colin Smith Colin Smith Profile: Colin Smith

I know that this is everyone’s favorite error message that SQL Server spits out. I love it because it has so many states that can give you an idea about what the root cause of the issue is. The downside is that some of the states really do not give you any good information and are just generic. So I ran into an issue where a user lost her ability to connect to an instance and it appeared to be out of the blue.

I looked in the log and I found Error 18456 Severity 14 State 16. I went to look up State 16 and lucky me it is one that does not really tell you much. Just says basically that the SQL Server was unable to authenticate the user. This error points out to look at AD and make sure the DC is accessible. Well I know all that is good so I decided to do some of my own testing to figure out what the issue was.

1st I found out that the user was getting access to the database via a group membership. I thought perhaps it is an issue with that group. I added her account explicitly and granted her the access she needed to no avail. Same error. Then I thought I would have someone else in that group test it and I also thought about elevating her access and seeing if that helped. If that helped then it would rule out an issue with her AD account.

Second I did just that, I granted her sysadmin role and it worked like a charm. So I took that access away as soon as I verified that the elevated access worked. Now I thought that it must be something on the SQL Server that is causing the issue.

I put together a script to check all the AD Groups that were logins on the SQL Server and then check to see in AD if she was a member of any of those groups. Turns out she was a member of another group. So I thought that perhaps that group was not granted access to connect or perhaps an explicit deny some where was causing the issue. So I looked deeper at that group login and found that the group had no mappings to any online databases on the instance. I happen to know that the group did have access to one database on the server but that database is offline. Since that group had no access as long as that database was offline, I removed the login group and like magic the user has access again.

Keep an eye out for this as it was a difficult one to track down. It seems buggy to me that membership in a group with a mapping to a database that is offline will keep a user from connecting to a database that they have access to with a valid mapping.

I will post the powershell script in another post. Have to do some cleanup first.

January 13, 2012  8:00 AM

SQL Server on VM Training

Colin Smith Colin Smith Profile: Colin Smith

Not too long ago I was lucky enough to get my work to pay for a training session about SQL Server running on VM. The reason I was able to get this is that my shop was having some latency issues in our SQL Server VM’s.  It took us a long time but we finally tracked down the issue right before the training but I am here to tell you that the training was great and I would recommend it to anyone. By the way, our issue was that we were using concatenated disk and not mirrored disk luns on the SAN. Making that change took us from over 10 seconds, yes seconds, of latency per write on average to less than 30 milliseconds. HUGE improvement. So the training I am referring to is by Brent Ozar and here is a link to his upcoming events. The session that I was able to attend was not a free session so I can not give you a link but I will say that for a four hour presentation, and the ability to re-watch and ask questions later, the price is good. Brent does so much for the SQL Server community for free that I was happy that I was able to pay him for this class. And the class, like I said, was great!! Brent really is an expert and he has a knowledge like few others that I have ever met. He has forgotten more about tech than I bet I will ever know and he has a great style of sharing that knowledge. He gets deep into technical items without putting you to sleep, he is witty and funny and that really does help when you are doing a 4 hour web conference about technical material. Don’t get me wrong, I love this stuff, but when the presenter is monotone and dry sometimes it is hard to stay awake. So great job Brent and thanks again for all the knowledge sharing you and your team do.

January 12, 2012  2:28 PM

CES Story I heard

Colin Smith Colin Smith Profile: Colin Smith

Today as I was driving to work I was listening to Tech News Today and they were talking about Motorola saying that the carriers do not want them to deliver phones with stock Android. They want to be able to differentiate the phone based on the software and that is why things like Sense UI and the other overlays really exist. Well here is what I think about that.

First I do not have an issue with you having an overlay that differentiates your phone. You can put all the bloat ware on it that you want if that is what pleases the carrier. As a customer though, I would say to hell with the carrier, I am the one purchasing the phone. Why does it matter what they want? So I have a perfect solution and it really should be pretty simple and may even cut down on people rooting and ROMing phones. Give us the option to install the OS with the overlay and all the bloatware on it and then you can differentiate your device based on the krappy software that you are forcing me, the customer, to have on my phone. But how about you also give us the ability to install just a base Android OS that is certified on the device and all your hardware. I mean I am am sure you have to get that to work before you start piling all your own sh!t on top of it anyway right.

This is a win win I think. This lets you, the manufacturer, do what you think you need to in order to get the carriers to buy off on your device. But this also gives the consumer the option to have the type of experience they are looking for. I am buying an android phone for a reason you know. That is because I like Android the way Google made it.

Another point I would like to make is, why do you have to differentiate with software? You are a hardware manufacturer correct? If my assumption is correct then why not differentiate by making your hardware better then the rest. Part of the experience of using a mobile device is the actual device. I know that I want a good camera, a good processor, more memory, option to have sd card to add memory, nice screen that is very responsive and will last over time, and I do want to be able to upgrade to new versions of the OS as long as the hardware can deliver on that platform, and I almost forgot, get me a battery that will last a while. I hate that I can not use my devices for a full day without running to the wall outlet to get some juice. That is how you should be differentiating yourself.

I am picking on Motorola here since they said this at CES, but this is for all manufacturers. I really think that the majority of customers that have a clue about this type of tech want stock Android and at the very lease a good option to install stock Android. And those customers that are not in the know still do not want most of the bloatware that you put on the devices. Let me choose if I want Nascar or NFL or NBA apps installed on my phone. Do not install them and not give me the option to remove them.

Anyway, I hope that someone who has some pull will read this and take it to heart. If you agree with me then please tweet this out and please leave a comment so if someone that matters does see this, they will see that they are actually pissing the consumer off with the custom OS’s that they are putting on my device and forcing me to use.

January 10, 2012  8:00 AM

DBA is a full time job

Colin Smith Colin Smith Profile: Colin Smith

Today I received a call from a co-worker at my previous job. It was great to hear from him and catch up, but the main purpose of his call was not just to see how I was doing with the new kid. Of course not right, why would I even think that. Now do not get me wrong, I do not mind getting calls from previous co-workers about work and I am willing to help them out if and when I can. I really have no issue with that. But the point of this post is to all managers out there that think dba work is not a full time job and that one of your windows admins can do this in there spare time. First off I bet that the guys on your team that are not DBA’s are actually busy doing work that is in there job descriptions and do not have much time to focus on DBA work. Second is that being a DBA is a full time job in any enterprise. In this particular case I would say that they need three or more full time DBA’s to get done what needs to get done.

I am not sure I understand why some managers do not think that being a DBA is a full time thing and they think that they do not need more than one guy who can manage databases in there spare time. That is just CRAZY!! What is the most important asset to any company these days?  I think that the answer is DATA. Where does the data live? That is correct, it lives in the DATABASE. So why would you not think that managing your most important asset is a full time job? Companies spend hundreds of thousands of dollars or more per year on database licensing and hardware costs but then they do not think that the investment of a couple full time DBA’s is worth it? Really, i mean what happens if you lose a critical database or you have corruption in a critical database or a database that you have housing sensitive information gets hacked? How much does that cost? Ask Sony about that and I am sure they will tell you how significant that is. How do you prevent this from happening or at least mitigate your losses if something does happen? You have a DBA staff that can help design and implement security strategies, restore strategies, high availability strategies and Disaster Recovery strategies. This really is a full time job and that is not even taking into consideration things like new deployments and break fix issues that happen every day all the time? We have all heard, “My query is running slow today HELP..” Who fixes that? A DBA…

So please listen to me when I say that you need to hire full time DBA’s and make sure you have enough to handle the work that needs to be done. I have been in a few interviews as of late looking for a new DBA and they are hard to come by. So if you find a good one, make sure that you treat them good and do not overwork them. Staff appropriately and just understand what a big and important undertaking being a DBA is. Do not let your lack of understanding what it means to be a DBA be the reason your company can not recover from a data loss situation. Hire good DBA’s and give them the support and training that they need in order to keep your companies number one asset safe and secure and accessible.


January 9, 2012  5:39 PM

Back to Work

Colin Smith Colin Smith Profile: Colin Smith

Today I will be back at work by the time this post goes live. This will be the first full week that I have been back at work since the birth of my child. This is a double edge sword for me, I really want to get back to work as I miss being in the mix and I feel like I have been gone for so long even though it really has not been too long. On the other hand I do not want to leave my newborn, not that I do not trust my wife or anything but I know that I will miss holding him and yes even changing him. Good thing I have set up an IP Camera so that I can keep an eye on things from my phone anywhere that I am. So I think that I really am ready to get back to work as I really do love my work and the company that I work for. SO by the time this post hits I hope to be at least 50% through all the email that I have missed.

January 1, 2012  1:09 PM

Happy New Years

Colin Smith Colin Smith Profile: Colin Smith

Today is new Years Day 2012… So happy new years.

Also I wanted to let you all know why I did not blog at all last month. I was crazy busy but this time it was not with work. My wife had our first baby on December 19th at 10:00PM. He was 7 lbs 6 ounces and was 19.5 inches long. So as you can imagine I am short on sleep. We did not know if it was a boy or a girl and we would have been happy either way but I am very excited to have a baby boy.  His name is Mason, and when we picked the name months ago, it was not a trending top 5 name. We picked it for 2 reasons.

1. We like it

2. My father-in-law was a mason for pretty much his entire life.

So that is what is going on with me but I will certainly do my best to make sure I have some tech posts going forward and get back on track.

November 29, 2011  5:35 PM

Powershell Gather Table Data

Colin Smith Colin Smith Profile: Colin Smith

I had a person on my team that wanted to identify tables in our enterprise that might be good candidates for compression. I thought it might help him out if I had a script that would look at every table in our enterprise that exists in a SQL Server database and give him some basic info that may help out. Like Row Count and Data Space Usage. So here is the powershell script that I came up with.

$i = 0
$output = @()
$sqlinstances = Invoke-Sqlcmd -ServerInstance servername -Database dba -Query “select instance  from instance where isactive = 1”

foreach($sqlinstance in $sqlinstances)

$iname = “$sqlinstance.instance”
$s = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $iname
$dbs = $s.Databases | where{$_.Name -ne “Master” -and $ -ne “Model” -and  $ -ne “tempdb” -and $ -ne “msdb”}
foreach($db in $dbs)
{$i = $i +1
$t = 0
$dbname = $db.Name
$tables = $db.Tables
$numtable = $tables.count
foreach($table in $tables)
$t = $t + 1
echo “table $t of $numtable”

$tname = $
$tschema = $table.schema
$tpart = $table.ispartitioned
$tcomp = $table.hascompressedpartitions
$trows = $table.rowcount
$tdata = $table.dataspaceused
$Object = New-Object PSObject
$Object | add-member Noteproperty Instance $name
$Object | add-member Noteproperty Database $dbname
$Object | add-member Noteproperty Table $tname
$Object | add-member Noteproperty Schema $tschema
$Object | add-member Noteproperty Partitioned $tpart
$object | add-member noteproperty Compressed $tcomp
$Object | add-member Noteproperty RowCount $trows
$Object | add-member Noteproperty TableSize $tdata

$output = $output + $object

$output | Sort-Object -Property RowCount -Descending | select Instance, database, table, rowcount | Out-GridView

Hope this helps.

November 29, 2011  10:00 AM

The Transformer

Colin Smith Colin Smith Profile: Colin Smith

I recently Ranted about my experience with trying to purchase the ASUS Eee Pad Transformer TF101-B1 32GB 10.1-Inch Tablet (Tablet Only) but I did not mention that I purchased the tablet anyway. I think that this is the best Andriod tablet on the market and I will post more once I get it and play with it some more and let you know if I still feel this way. I was debating about the Kindle Fire, Full Color 7″ Multi-touch Display, Wi-Fi, and of course the IPad 2. So you can see that the prices go up a significant amount when looing at the Fire and the IPad2. SO I chose the middle of the price ground but it was not just based on price. I got the 32 Gig model and I paid a sale price of 399.00 so that is much better than the 599.00 price of the 32 Gig Ipad2. Also the Fire only has 6 Gigs and they did that to keep the price down and because of the Amazon media integration. I had a chance to play with both of these over the past few days. The Fire is the perfect device for your mom. Easy to use and the Amazon integration makes it a nice experience in the Amazon world and they did a great job of making everything super simple to use. But for me it has a few shortcomings. No camera front or back… Only 6 Gigs and no expansion slot, and only 7 inch screen. I wanted more and I do not want to be encumbered by the forked version of the OS. So out goes the Fire for me. It is a great device at a great price though. I also played with the IPad2 and I love it. That is a fantastic device especially if you like Apple products. Very simple to use, Screen is amazing, you can get it in 32 GIg model, has the cameras, easy to use IOS. But I Love Andriod and I hate that Apple has such a tight hold on the OS the Apps and the hardware. Also no expansion slot. So I chose the Transformer. I have not played with one yet so I am taking a bit of a risk but it has it all. Everything I am looking for, 32Gig internal plus an expansion slot up to 32Gigs more for a total of 64. Love that. Running Android and I will be able to upgrade to ICS when released. Has the cameras 10.1 inch screen and I have heard it is pretty nice, keyboard dock that I will get.

So that is why I went with the Transformer hope I did not make a mistake.

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: