The Multifunctioning DBA

December 29, 2010  8:27 PM

Thanks to ITKE

Colin Smith Colin Smith Profile: Colin Smith

I wanted to take some time to say thank you to ITKnowledgeExchange for sending me some swag to hand out during my SQL Saturday# 47 session on Powershell for the DBA. I am really looking forward to the session and the the event as a whole. I think that we have two powershell presentations that will be going on and I am very excited to go see the other session as well. But thanks again to ITKE for sending me the shirts and stickers and the information about the site to hand out. I only have 6 shirts so if you want one you need to come to the session and I will have some way to hand them out.

December 29, 2010  10:00 AM

DB Mirror Alerts

Colin Smith Colin Smith Profile: Colin Smith

While I was back east at the home office I was asked to look into a way to monitor DB Mirroring. I had a couple thoughts on this as I know that I can look at the status of the mirror via SMO using Powershell and then alert if the status is not Synched. But I was in a time crunch so I did some quick searching thinking that someone had to have done something like this in the past and I was right, someone had and they were using a status change alert. Here is a link to what I found and I set it up and it does work.

I did the alert on state change event and like I said it does work. Just not as good as I would like. It seems to send out a lot of alerts and I think it is due to network Latency. So if it gets a bit behind the state changes and an alert goes out. Well that is great but I would like to verify that we really have an issue before sending out alerts so I am going to have that alert kick off a PS script that will wait about a minute and double check the state of the mirror. If it is still in a failed state then I will send out the notifications.

Thanks and I will post the script when it is complete.

December 28, 2010  9:00 PM

Another Cool Free App

Colin Smith Colin Smith Profile: Colin Smith

While I was traveling for work I was told about another great app for note taking. This came in very handy since I was working with Microsoft on a database issue that we were having. I was able to take notes on my laptop as we worked the issue and found things and then I put the app on my phone as well. Now I can access the notes anywhere and I can read them as well as edit them if I need to. I thought it was very nice because when I was out to dinner my boss called and wanted to know some things about the issue and I was able to reference my notes while on the phone with him to make sure that I was giving him the most accurate information. The app is Evernote and I recommend giving it a try. It is free with the option to pay for an upgrade.

Check it out…

December 28, 2010  7:00 PM

Cool Andriod Travel App

Colin Smith Colin Smith Profile: Colin Smith

Two weeks ago I was traveling for work and I ran into some delays due to weather in Atlanta and that was not all that much fun. But I did find a great travel app that helps keep me up to date on what is happening with all of my travel plans. It is called TripCase. It is free and it is great. Emails about when flights are late and you can also have it notify someone else. I set mine up to let my wife know about my flight back home so she would know when to pick me up. Very cool app so check it out.

December 28, 2010  3:00 PM

PASS Summit DVD’s are here!!!

Colin Smith Colin Smith Profile: Colin Smith

Today I came back into my office after being out for a couple of weeks. One week I was traveling for work and then I was on Vacation for a week and that was nice. Well when I got back to my desk I had a package sitting on my desk and I thought “hmmm what could that be?” I opened it up and it was my copy of the PASS Summit DVD’s. I am so excited to finally get the chance to watch all the sessions that I wanted to go to but could not make it to. I will also again be watching all the sessions that I did go to. I wish I had another week off so I could just go home and watch them all. My wife is not going to be happy tonight when she wants to watch TV I put one of these DVD’s in. I can not wait. I am shocked that I got them already. I really thought it would take longer to get the. Anyway off to start watching..

Hope you got yours as well.

It really is a Very Merry Christmas

December 7, 2010  9:14 PM

Setting Min and Max Memory settings in SQL with Powershell

Colin Smith Colin Smith Profile: Colin Smith

So today I was thinking about some of the things that are currently not set up properly on some of my servers. I got to thinking that a lot of them were never set up properly for Min and Max Server memory. Most of them, that were installed before my time, are set up with the defaults and as you all know, that is not usually the best practice.

I thought it would be nice to have a way to progamatically set those values based on how much memory the server has total. I started tooling around in Powershell using SMO and I found the settings that I was looking for. It is really pretty simple to change theses values as I have tested it on my own local SQL Server. Here is what you need to do.

[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null
$s = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) “ServerName”

$s.Configuration.MinServerMemory.ConfigValue = 1024
$s.Configuration.MaxServerMemory.ConfigValue = 1024

The above code will set both the Min and Max Server memory settings to 1024 MB. This change will not be in affect until you restart sql server. But I now know that I can do this progamatically if I come up with a standard for these settings that is based on something that I can progamatically determine. My first thought is to do this based on the total Memory in the server and then figure out how to set both values. I will post more as I come up with it but I thought this was a nice little tidbit.

December 3, 2010  9:07 PM

Powershell SMO database size property incorrect

Colin Smith Colin Smith Profile: Colin Smith

Ran into an issue while writing a script to gather database properties via smo and one that I want is database size. I wrote the script and started testing it and my values were all wrong and the same for all databases in the instance. I did a few things trying to figure out what I was doing wrong and I even pinged Aaron Nelson (Blog | Twitter) about the issue and he was nice enough to help me out and take a look at the issue. He looked at the code and my results and had me check in SSMS that the results were correct. After I checked in SSMS and verified that the values were in fact correct he had me try getting the data using the provider, explained here in his Hey Scripting Guy article. I did that and now the data was correct. That is strange and I started thinking and I had not attempted to run this script from another host. I did that and I saw that, using SMO, the data came back was correct. Now I knew it was something with my machine but what?

I started searching and I found it. This is a bug in SQL Server 2008 SP1. I have a SQL 2008 instance running on my machine that I use for testing and developing and I had recently applied SP1. Here is a link to the article about the bug. I downloaded and applied CU3 for 2008 SP1 and now I am getting the correct results. What a pain. I am just glad that it is working again and it was not my code.

Big thanks to Aaron Nelson (Blog | Twitter) for taking the time to help me out. I really appreciate it.

Hope this helps

November 29, 2010  10:00 AM

SQL Saturday Presentation and PASS

Colin Smith Colin Smith Profile: Colin Smith

When I first heard about the SQL Saturday event in Phoenix being postponed I was really bummed out and thought that I might just skip it and not do a presentation. Then I went to the Summit and I was inspired again. But another thing that I got out of it was some great help on what to expect as a presenter.

I went to two very good sessions on Powershell and the presenters were very different in style and that was good for me. I got to see some of what did and did not work so well from each presentation and hopefully I will be able to learn from that and make mine the best it can be. I learned what type of questions to expect and how to handle them when I do not know the answer, and I am sure that will happen.

One thing I noticed while at the Summit is that not many, at least the sessions I attended, of the presenters handed things out during the presentation. Only one of the many that I attended did, and I thought that was really cool and it made it more engaging for the audience. I hope that when I am talking that the audience is engaged and enjoying what I am doing. I think a little bribery goes a long way. I also hope to inspire some pockets of conversation and get people into and exited about what I am talking about.

I really hope that I have learned from the presentations that I attended and really enjoyed, and I hope that I can take what I learned and use it during the first SQL Saturday in Phoenix.

If you have not done it already go and register to attend. I hope to see you!!

November 28, 2010  10:00 AM

How PASS inspires me

Colin Smith Colin Smith Profile: Colin Smith

I was very lucky and my company was nice enough to send me to the PASS Summit and I can not thank them enough for that. When I was getting ready to go to the Summit I was like a kid on Christmas Eve. The anticipation was killing me and I could just not wait to get to the event and have fun and learn as much as I could. And that is what I did and the Summit exceeded all of my expectations. Seeing all the smart people together in one place and so willing to share knowledge and experiences with one an other. And not only the presenters but all the attendees and Microsoft employees and I just mean everyone. It was a refreshing experience to say the least.

But the presenters especially inspired me to get out and try to do more in the community. They showed me that you do not have to be an expert and you do not have to know all the answers. That was also very refreshing to me. It really showed me that everyone has something to give that someone else will learn from. We all work hard and we all see a lot of the same issues as each other. If you have a way to deal with an issue that is better, or more efficient then my way then I have learned something. I think that I have some knowledge that others could benefit from. The speakers really showed me that they are all very smart for sure but that they are all just like you and me. They might have more experience and or knowledge in some or even most areas, but I might be able to teach even them a thing or two.

They taught me not to be shy. To be proud of what I know and to share it with others. To take the tough questions and it is OK to say I do not know but shoot me an email and I will find out. It is OK not to be an expert at everything and I can still share my knowledge.

That is one of the most beneficial things that I got out of going to PASS and I hope that more and more of us out there learn that and share the wealth of knowledge that you have, but do not be afraid to learn something new in the process.

November 27, 2010  4:00 PM

Getting My Slides and Demos Ready

Colin Smith Colin Smith Profile: Colin Smith

Working on setting up for my first Presentation and man, at first I thought that an hour was too long a time to stand up and talk about powershell in front of a large group. But now that I am getting into it again I do not know that an hour is enough time. It is so hard because I do not know how in depth I need to go on a lot of the beginning stuff before we can get into some of the really cool stuff. I am having fun getting it all ready though and I think I will go over all the basics and depending on the experience level of the session attendees I can run through it pretty quick and get to some more really good stuff if I have time. So hard to balance. Fun though and I am learning more and more as I go and that is part of what makes presenting so much fun and something that I really wanted to do.

I hope to present on something other than powershell at the next Phoenix SQL Saturday event

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: