a Bus drove through my neighborhood. Really! THROUGH it!

Not SQL related. But ME related.

I came home from work yesterday, and noticed the neighbors at the end of the cul-de-sac were all standing around a hole in their fence. I don't remember there being a hole in the fence... Beyond the hole, I could see bits and pieces of wood. Seems odd that they would be tearing out a small section of fence and replacing it with vinyl. So I wandered over, and met another neighbor along the way. Asking him what happened, he smilingly teased me with various options that could have occurred, slowly revealing the story as we approached the carnage. Into my neighbors back yard we walked, noticing a trail of destruction. Grass was chewed up, bits of concrete and old fence wood littered the area as we traversed the backyard. People were milling about, as if they had all become afternoon construction workers. Stuff was everywhere. Piles had started to form and I could only slightly make out what had occurred. Apparently, a bus crashed through my neighbors' yards in our cul-de-sac earlier in the afternoon. The driver had finished dropping off kids, made a wrong turn, and then, well, we don't actually know why, but went into my neighbors yard, thru their fence, down their hill, taking out their obviously offensive shed minding its business in the corner of their property. After dealing with the shed, the bus decided it hated the fence on the back of this neighbors property, and went thru it. Now it had more area to hate, and proceeded to hate a couple trees, and the corner of that property behind my neighbors. Was the hating bus done? No! It saw another fence that it hated, and killed it too. There, it stopped hating, and sat on its haunches, waiting to be pulled out of its newly acquired parking spot. The driver exited and entered the bus a number of times, and no one knows yet why this bus or its driver apparently hated all these fences.


Here's the local news report.

No children were on the bus. No children were playing in any of the 3 yards where the hating bus traversed its seeds of destruction. No one was hurt. Just stuff.

We were blessed.

Who owns my database?

We often get database generated by a TestLab group, or QA group. They can have VMs cranked out with cookie cutter databases on them. We will get these dbs, apply pieces, and hand them on to the next group. This is a process that one becomes accustomed too, and one ends up not noticing the small things, like who owns the database.
So, we often get to verify that the correct owner in fact owns it. However, its usually a quick task that forces one to simply rely on the UI to perform the verification and subsequent alterations. Using the UI is not a bad thing. But i was taught early in my career to not trust the UI, but to learn what is being done underneath, dig into it, understand it, and if possible script out the actions.
I do love that management studio allows for much more scripting than previous UI tools did. I tend to use this and profiler to perform the snooping, then i massage the results into a script or job or something that i can use to repeat.

Today, i finally got tired of using the UI to verify the owner of the database. I have previously used the wonderful proc sp_changedbowner to alter the ownership. And often, simply cheated, and assigned it to 'sa' without even checking if it was owned or not by 'sa'. I dug into some procs, traced some actions of Management Studio, and saved me a simple script that can validate the owner, and even spit out for me the subsequent change sql needed to alter owner. I'm only sharing this with the rest of you because, if you are like me, you like having these scripts handy. Maybe they will help you out. Someday, you'll remember this, look for it, and viola! save yourself precious seconds or minutes.

So, here is it.

select
sd.name as [DatabaseName],
suser_sname(sd.sid) as [Owner],
case
when suser_sname(sd.sid) <> 'sa' then
'use ' + name + ' exec sp_changedbowner ''sa'''
else ''
end as ChangeDatabaseOwner,
sd.*
from master.dbo.sysdatabases sd
where suser_sname(sd.sid) <> 'sa'


run this with or without the where clause. I like to do both. Take the results, which look like this...


use db1 exec sp_changedbowner 'sa'
use db2 exec sp_changedbowner 'sa'
use db3 exec sp_changedbowner 'sa'
use db4 exec sp_changedbowner 'sa'


and execute them. Not elegant. Not a fancy UI. But little thought needs to go into it. Quick, dirty, and works. I'm saving this into a file in my [_helpers] folder for future use. already, I've quickly altered 50+ databases on 4 db servers. Blamo!

SQL Quiz 5: SANs Mirroring

My turn to answer Chris Shaw’s questions “Do I feel I have a reliable SAN solution?” and “Describe Database Mirroring for the layman?” Thanks Grant for tagging me. More important to me than the sharing of knowledge, is the building the community through our interactions. Besides, I'm not an expert or even novice in these subjects. So be forewarned before proceeding.

SAN:
I recall in the beginning of my DBA career, a Database server was a box I could see, touch and if necessary, kick. One day, it became a smaller box that was in a backroom somewhere, in a 'rack'. With access to this special room, I could still kick it. Now, we use SANS. What the heck are these things? I don't have access to them. I can't see them. I cant even find the room where they are at. I'm definitely no longer allowed to kick them.
Now we have to go through folk with magic fingers and pixie dust to get some space for a db server. They do all kinds of things that I don't understand. But if they do it right, I get an IP address or name of a DB server, and its so much better than it used to be, with much more stuffs than I could ever hope for previously. Tons of space, and it's uptime is so much better. When something does break, its usually not the DBA's fault. Like when converting from generator power to real power, and forgetting to connect the SAN your standby topology is on at the same time. Fun stuff! But I do miss the days I could walk into a room and kick my DB server and it was all in 1 box. Mainly I liked to see it sitting there and often, kick it.



Mirroring:
Many years ago, in the beginning of our marriage, we went to a car dealer. They had a special on two cars at the time. We almost bought them both, and would have had two identical cars. If we kept them both tuned and in good working order, I could go out in the morning and take one of them. If something happened to that one, I could take the other, and not really notice the difference. My tasks would still be accomplished in the same way, and if I didn't pay close attention, I'd be none the wiser.
This is how I imagine mirroring functions. There are two dbs out there, that are identical. Something keeps them running the same. If one has an issue, we start using the other. Quite magically. You, the end user, simply gets in the DB, and drives off, returns it to its parking space, and viola, repeats when necessary.
You don't need to know that behind the curtain there are any number of processes, people and other stuffs switching things out, configuring things, setting things up, and so on. What you see is a functioning item on your side of the fence, and its available when you need it.


To sum this all up in my own words wouldn't be as much fun as realizing my conclusion is the same as Grant's. So to plagiarize Grant, "So to sum up, the answer to both questions is, I don’t know. Pretty pathetic, but honest." -Grant Fritchey

Now tagging Pat Wright and Andy Leonard.