The Multifunctioning DBA

October 24, 2012  9:36 PM

Haunted House

Colin Smith Colin Smith Profile: Colin Smith

This year I decided to go to a haunted house. I thought it would be a lot of fun and I was hoping that it would be scary as well.So I went to the 13th Floor here in Phoenix. This is supposed to be one of the better haunted houses in the country. I got some friends together and we all took off. My wife also joined us, she is a scaredy cat, so I thought at the very least it would be fun to see her scared. We bought VIP tickets so that we would not have to wait in line and it also included a second haunted house that is all Zombies.

We arrived and we all expected great things and perhaps that was the issue. I went in with High hopes and they were dashed. Nothing about it was scary at all, in fact one of the actors that should have been scaring me asked if I was having a good night. Nothing scary about a creature asking if you are having fun… ooo I was shaking. So we went through, and I will say that the sets they had were well done and even the actors, for the most part, did a good job. It just was not scary. It was predictable for one thing and they shove you in so close together that they do not have a chance on timing the scares correctly for ever group that is in the house. So if this is one of the tops in the country, save your money and rent a scary movie instead. I have been more scared after watching a movie then I was walking through the haunted house.

October 24, 2012  9:22 PM

PowerShell CName Reporting

Colin Smith Colin Smith Profile: Colin Smith

Recently my team started using CName records instead of server names for applications to connect to. This makes moving things around on backend much easier. So here is the script that I am using.

Snapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

Invoke-Sqlcmd -ServerInstance dbhubsql -Database dba -Query "truncate table SqlCname"
$cnames = dnscmd DCName /EnumRecords Domain . /Type CNAME
foreach($cname in $cnames)
if ($cname -like "sql*")
$cname = $cname.Replace("3600","")
$cname = $cname.Replace("CNAME","")
$cname = $cname.Replace(".domain.","")
$mid = $cname.IndexOf(" ")
$alias = $cname.Substring(0,$mid)
$server = $cname.Substring($mid+1)
$alias = $alias.Trim()
$server = $server.Trim()
$uid = Invoke-Sqlcmd -ServerInstance server -Database db -Query "Select UniqueID from instance where InstanceName = '$server'"
$uid = $uid.UniqueID
Invoke-Sqlcmd -ServerInstance server -Database db -Query "insert into SqlCname (UniqueID, Alias) Values ($uid, '$alias')"


I am pushing the results into a database that I already have, just a new table. This allows me to report on this data anytime I need to.

Hope this helps.

October 23, 2012  7:30 PM

Virtualization Issues

Colin Smith Colin Smith Profile: Colin Smith

We recently had our bits upgraded to the latest and since that we have had some issues with the product. We have run into issues where the SQL Server that has copies of your databases in a restoring state, have stopped grabbing the log backups and applying them. This is an issue as it has made me run out of space in the areas that I am storing my log backups. This happens because I have a Powershell script that finds out what logs have been applied and then goes and deletes the log backup files that have already been applied. So the issue is that if logs are no longer being applied, the script can not remove the files. This prompted me to add some alerting into that script and now I am alerted when more than x number of files are on the disk. That way I can take a look at what is going on and figure it out before it becomes an issue. We think we have figured out the reason that we are having this issue though. We have to install some software on the SQL Host and I think that when we upgraded our bits, this software was not updated. So I removed the software and installed the new version and this caused some huge issues. Now I am dead in the water and waiting for support to call me back to help me out. So I think this is a big bug. We need to have a way to upgrade all components of the product without causing these issues.

October 23, 2012  5:52 PM

PowerShell Table and Index DDL

Colin Smith Colin Smith Profile: Colin Smith


$server = “Instance”
$tables = “Table Name”
connect-sql $server
$db = $s.databases | where {$ -eq “EventManagement_Test”}
foreach ($table in $tables)
$outdir = “C:\ddl\$server”
if (!(Test-Path $outdir))
New-Item -ItemType container $outdir
$tbl = $db.Tables | where {$_.Name -eq $table}
$name = $

$tbl.Script($so) | Out-File $outdir/$table.sql -Append

October 23, 2012  5:17 PM

New TV Purchased

Colin Smith Colin Smith Profile: Colin Smith

Last Month I Told you that I was looking at getting a new TV. Well I did it. I spent the money and I feel like I did not settle on anything that I wanted. I was able to get 65 inches, 3D, Edge Lit LED (full panel would have been nice) and the TV has many smart features. Here is what I purchased. Samsung 65 Inch. I have had the TV for about 3 weeks now and all I have to say is WOW… Compared to my previous rear projection TV this thing is amazing. I have it mounted on my wall with my Skype Camera mounted on top. Skype is great with this TV. I no longer have to hold my baby on my lap while skyping. I can set the baby in his play area in the living room and the camera just picks it up. That way whoever I am talking to can see him play. The TV looks great no matter what I am watching but the 3D is fantastic. I love that I can force 2d content into 3D. I have watched many football games in 3D now and it is great. it is not as good as a movie that was meant to be 3D but it is still very nice. So if you are looking for a good TV with these features. I recommend this TV.

September 28, 2012  9:12 PM

New TV

Colin Smith Colin Smith Profile: Colin Smith

I am going to be purchasing a new TV but I just do not really know what to get. I am replacing a 65 inch Mitsubishi DLP tv and it is really a nice TV but I want to upgrade. My problem is that I want to have LED, 3D, Good contrast Ratio, Internet apps, and I would like it to be 65 inch or a bit larger. I also do not want to pay a ton. So I am looking at the Samsung 60 inch which is a bit smaller then I would like, and the Sharp 70 inch. The problem is I would like to get the one with the best picture and best feature set. I am open to looking at others as well so if you have any thoughts of what might fit my needs let me know.

September 28, 2012  5:05 PM

Poweshell and user input driven menu

Colin Smith Colin Smith Profile: Colin Smith

I needed to be able to display options to users and let them make simple choices..  to make a selection of what to do. I wrote the following in order to accommodate my needs. So I display a list of choices and the user makes a selection. After the selection is made the script calls the function that is associated with the choice.

Here you go:

$i = 0
$functions = "Function_1", "Function_2", "Function_3", "Function_4", "Exit"
$funarray = @()
foreach ($function in $functions)
$funarray += $i
$funarray += $function
Echo "$i $function"
$Selection = Read-Host "`nMake Selection"
$av = 0
echo "`n"
foreach ($value in $funarray)
if ($selection -eq $funarray[$av])

if ($funarray[$av] -eq "Exit"){exit}

& $funarray[$av]




So if you run this here is what you will get.

Powershell Menu

Display a simple menu to user

September 27, 2012  6:18 PM

New Virtualization Bits

Colin Smith Colin Smith Profile: Colin Smith

So I got some new bits for the Virtualization beta that I have been working on and the changes that they made, are all to the CLI so no real GUI still. That would be ok but all the Powershell scripts that I had written no longer work since they interface directly with the CLI. So I had to make some modifications to my scripts, but I will say that after seeing the changes that were made, I was able to make my scripts much more efficient. Soon I hope that I will not have to use my scripts. For now though, using my scripts it takes me about 1/2 the time to provision databases and find the snapshot that I want to provision from. My script also makes it very easy for me to provision a new VDB from an existing VDB. It is very cool and very fast. They also fixed the file_id issue that I was having earlier. It is pretty nice being in the Beta for this and being able to provide feedback that really drives the product.

More to come on this as I get new bits.

September 26, 2012  10:35 PM

Chicago in May

Colin Smith Colin Smith Profile: Colin Smith

Like I said in my last post, I will be in Chicago in May for training. I am so excited about the training but also very excited to go to Chicago. My wife and I will be in town on a small vacation with our son, he will be about 18 months) for 5 days after the training. I am not sure what to do wile we are in Chicago. I think I would like to do a Segway Tour but I am not sure what the rules are with that and a small child. So I am sure I will find too much to do in my research leading up to the trip, but if anyone out there knows things that are just must do or must see items, please let me know. And if you are local and know the best food places, please let me know and perhaps we can meet for Lunch or Dinner one night if you like.

Let me know what I should do please.

September 25, 2012  4:48 PM

Server 2012 Core

Colin Smith Colin Smith Profile: Colin Smith

I read an interesting post that I just thought I would have to talk about as well. So with the release of Window Server 2012 and SQL Server 2012, we can now install SQL Server on a Core version of the OS. In the article Jeremiah kind of pokes at the process a bit. Like not being able to just fireup regedit in order to make a change to a reg change, and he also pokes a bit of fun about how long it will take to get all the scripting done that you might need to manage the server and the Instance.

From my perspective, I see things a bit different. Jeremiah makes some great points and I agree with him that making the change will not be easy. I am a Windows GUI guy through and though and I have minimal exposure to No GUI IX systems. And honestly I do not really love working on IX boxes. The other thing that I think some people may not understand, is that you will still be able to manage SQL Server with the same tools that you always have. You will just have to connect remotely. Really you should be doing that anyway correct. How many of us are running SSMS from the SQL Server itself? Oh I see you raising your hand, well stop it, run Core and you will have to stop it. Not only will this make Windows run more efficiently, less overhead for GUI and interrupts, but it will also save us from ourselves. I am just as guilty as anyone when it comes to running SSMS on my server. You may think it is no big deal, but think about it. If you are like me, then you try and give SQL all the memory that you can. This leaves the OS with not a whole lot. I always try to leave enough, but if I RDP to the server and pull up SSMS and another member of my team does the same, well that is not helping our cause at all. Core eliminates the possibility of something like that happening.

Sure, core will change the way you work with Windows and you will have to use the command line. Is that really a bad thing though? I have been working with PowerShell since the beginning and I love it. I choose to work in the command line sometimes and would almost rather look at some aspects of SQL Server using PowerShell and SMO then SSMS. I can script things once and use it over and over and on any instance or server. Management is really much easier. You will have a learning curve for sure, but if I can do it I think anyone can. I am a GUI guy but I LOVE PowerShell. I am not saying go upgrade all your instances to 2012 core before you know what you are doing, but start with your own box and grow it out as yu learn and feel comfortable. I would not move Production until you are good to go and have a very good handle on things. SQL Server 2012 introduced a couple new PowerShell cmdlets but Windows Server 2012 gives us a ton. Install it and play and learn and move when you are ready if it is a good fit for you and your team.

I have been saying this for years and now I am saying it again, if you administer anything Microsoft, or that runs on a Microsoft OS, then YOU need to learn PowerShell. At least the basics. With just the basics you can really do a lot. So go play with PowerShell and try to understand all the ways that CORE may make your servers run more effectively and faster. Remember that you will still be able to manage SQL Server remotely using the SSMS tools. Be careful, move slow, and have fun!

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: