The Multifunctioning DBA

February 22, 2011  3:23 PM

First Time Speaking

Colin Smith Colin Smith Profile: Colin Smith

So Saturday was our local SQL Saturday event and my first time doing a presentation of any sort really. I was a bit nervous as people started to stroll into the room and sit down. At first I did not think that many people would be attending but as start time approached the room filled up. I had a room with 28 seats and 30 people in the room. Standing room only… that was a bit intimidating as I looked up at the room.

Well the time came to get started so I did. For the first 5 or maybe 10 minutes I felt nervous and I thought that everything I was doing was wrong. I did not really feel like I was in control of the room even though everyone was paying attention. I started in on my slide deck and just got going. Soon I really forgot that I was doing a big presentation and I was just talking about some cool tech with some other cool tech people.

After I got into it a little bit I got my first question. I actually just took it in stride, after so much worry about how and when to take questions. Then it really was interactive and became more so. I really enjoyed that the crowed was interacting with me and each other when appropriate. Now that I had the interaction it was even more like a conversation with a group of people that loved tech.

Before I knew it people were “OOHING and AAHING” at my demos and really seemed to be liking it. I looked at the clock and man I was out of time before I knew it.  Then I am trying to hurry up and finish up and finish my giveaways, Denny Cherry(Blog | Twitter) walks in and is presenting after me. Now I had to hurry and get all my stuff together to get out of there. I was amazed as I was trying to clean up and get out of the way, people kept coming up and asking me more questions and letting me know I did a great job.

All I can say is that is was AWESOME and I can not wait to do it again. I would really like to come up with some more sessions so that I am not a one trick pony. All and All I just want to say that if you have never done this and you have thought about it, YOU SHOULD DO IT. People are coming to get the content and not to see you, unless you have a name and brand already. I certainly do not and never thought I would have a session that was standing room only.

Now I would like to thank Brent Ozar(Blog | Twitter) for lighting the fire for me and getting me going. After reading his blog, seeing him speak, and then getting into a mini twitter argument with him about powershell, he really did inspire me to do this session and he, as well as others, inspire me to be the best DBA I can be. So thanks for that and also to all the other presenters, organizers, volunteers, and attendees for making my session a success as well as our entire event in Phoenix. It was our first and will not be the last.

February 18, 2011  10:29 PM

SQL Saturday Presenters Dinner

Colin Smith Colin Smith Profile: Colin Smith

So it is Friday before the big event and many of the speakers for tomorrows event are getting together for dinner tonight. I am really looking forward to this and I am about to pack up my stuff for the day and head over to dinner. This is a long drive for me but should be a lot of fun. I am looking forward to meeting so many smart people that work in the trenches everyday like I do. I am bit intimidated though, I am not sure I am worhty of being at the table with some of these people. I am very excited to see if I can hold my own though. Either way I am sure it will be a fun and educational night and a great way to get ready for the big day tomorrow. As I have said before I think I am ready to present and I do not think I will be too nervous after giving my session to my co-workers who are also very smart and I respect them all. And I think that they all respect me still after giving them my presentation. I think I have all the nerves worked out but I will not really know until it is time to stand up and speak in front of the attendees. I am worried that I will not have many people attending my session and I really hope that is not the case. But more importantly I really hope that I will be able to get good information out and show some DBA’s why Powershell is a great tool.

February 18, 2011  3:52 PM

SQL Saturday #47 is Tomorrow

Colin Smith Colin Smith Profile: Colin Smith

Tomorrow is the big day and I am very excited. I am a bit nervous but after giving my presentation to my coworkers I feel much better. I mean I really care what they think because I have to work with them everyday and it is important that they respect me and think some what highly of me. As for the attendees of the event, I still want them to like me and I hope that everyone gets something out of my presentation. I also hope that someone will actually show up to mine.  I really hope that knowing I have some shirts, stickers, and some software to give away will bribe some to attend and stay until it is complete.

I have had my presentation “done” for a while now but I keep going back and making small tweaks and new notes to make sure that I hit all the points that I want to talk about. Now, even though I am doing a basic session about powershell I will only be spending about half of the session on the basics and then I will be diving into some scripts that help me out everyday. These are a bit more advanced but I really want to showcase how powershell can be a good tool in your DBA Toolbelt. So hopefully I will show you enough of the basics so you will be able to follow the scripts and I hope that the scripts are cool enough that they will make you want to dive in and do even more with powershell.

January 31, 2011  11:00 PM

Microsoft Support

Colin Smith Colin Smith Profile: Colin Smith

I have been on the phone with MS way more than I would like to be in the past month or so. Three times and two of them were for critical issues. Both times it was not the best experience. They do not like to escalate the case to a senior engineer even when I ask nice. That is very frustrating. I hate hearing them say that they are talking to a senior person, Just put that senior person on the phone. One time the guy on the phone did not know what a command prompt was. Huh really, and you work for MS as support?

This time I am on with MS for a knowledge transfer and so far this is a much better experience. I guess that makes since now that I will not have to escelate the issue.

I really hope that support can improve or I think our money would be better spent with another consulting firm that really knows how to help.

January 31, 2011  7:37 PM


Colin Smith Colin Smith Profile: Colin Smith

Wow, who knew how big replication was. So many types, different requirements, I am learning a lot but for the problem I am facing I think I am most interested in peer to peer replication. Here is a good link about it.

January 30, 2011  9:21 PM

Triggers for protection

Colin Smith Colin Smith Profile: Colin Smith

I have recently had to find a way to prevent someone that has elevated privileges from removing logins, databases, as well as keep them from adding new databases to my production systems. I was amazed at how easy this was to do thanks to event level ddl triggers. I created a table in master called ddlaudit. The format is below:

create table DDLAUDIT
(PostTime datetime,
DatabaseName varchar(100),
Event nvarchar(100),
ObjectName varchar(256),
TSQL nvarchar(2000),
Login varchar(100))
Now that I have a table to push data into I just needed to create the triggers. Below is one of them but I did create all three.
Create Trigger AUDIT_Drop_Login
On all server
for drop_login
Declare @audit XML
Print ‘You are not allowed to drop logins from this instance of SQL Server. Please contact the DBA’

INSERT INTO DDLAudit (PostTime, DatabaseName, Event, ObjectName, TSQL,Login)
@ed.value(‘(/EVENT_INSTANCE/DatabaseName)[1]’, ‘varchar(256)’),
@ed.value(‘(/EVENT_INSTANCE/EventType)[1]’, ‘nvarchar(100)’),
@ed.value(‘(/EVENT_INSTANCE/ObjectName)[1]’, ‘varchar(256)’),
@ed.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]’, ‘nvarchar(2000)’),
@ed.value(‘(/EVENT_INSTANCE/LoginName)[1]’, ‘varchar(256)’)

Now that I have them create no one, not even me, can perform these actions. And when someone does attempt one of the actions you will have an audit trail of who did what and when they did it. Now if you want to be able to drop a login after this is in place you will need to disable or drop the trigger. You can find the trigger by running the following query.
select * from sys.server_triggers
Disable trigger trigger_name
on all server
Enable trigger_name
on all server
That way you, as the DBA, can make the changes you need to make.

January 30, 2011  6:45 AM

DBA Hours

Colin Smith Colin Smith Profile: Colin Smith

When I was in school I thought that being a sysadmin would be about the best job in the world. I was close, I did not know how much fun it would be doing the job of a DBA. So after I became a DBA I loved it and really do enjoy my work and the fact that no matter how much I learn about it I still have more to learn. I really love a job where I never stop learning. Little did I know about the hours that I would have to work as a DBA. I do not recommend this job for anyone that is not OK with being up all hours of the night to fix an issue on a production server. I have pulled may 24 hour or longer days. I think the longest was about 38 hours and that is not fun. Those days are few and far between, but I have had weeks when I am on call and things just happen. Some times I might not get more then 3 hours of sleep and I still show up to work in the morning. That is just how it is and this shows why it is so important to get your environment in order. This is easier said than done in some cases but it is something that you want to get done in order to alleviate the number of nights that you are up late.

January 29, 2011  8:00 AM

Powershell Formatting

Colin Smith Colin Smith Profile: Colin Smith

I did not get to see this during the live presentation but I am looking forward to seeing it especially after hearing some co-workers talk about it. If you have the time check it out. I am sure it is going to be great.

January 28, 2011  9:00 PM

Free SQLSkills Training Results

Colin Smith Colin Smith Profile: Colin Smith

Well I did not win any free training but I would still like to thanks SQLSkills for all that they do and for offering a great contest with a fantastic prize. I hope that they do this again as I think it is a great opportunity for any DBA. I would like to congratulate the winner and all the winners of the reduced cost training. I really hope that they all can take advantage of this gift. So check out the results and I hope you all have a great time in training.

January 28, 2011  5:48 PM

Recent SQL Disaster

Colin Smith Colin Smith Profile: Colin Smith

In my last post about the disabled sa account I mentioned that I would have more on why I could not use windows authentication to get to my SQL Server instance. Here is the short of it and I know many of you will be screaming at the screen as you read this. Again this is a server that I have walked into and I did not configure it but I think that now I will be able to resolve all the issues that we have. So my server stopped accepting logins from all domain accounts including mine. We did have one sql account that could connect to the instance but that account was limited in what it could see. Only a member of public on the server. This server is very critical and could not be brought down because after some application configuration changes users were able to work using sql authentication.

In our troubleshooting the error logs lead us down the path of Kerb auth failures. MS agreed with this assessment after looking at the logs and the behavior of the instance. We finally came to a point where the server had to be rebooted in order to continue troubleshooting. I thought I would take this time to also enable the sa account and see if we could get in with an elevated account and just check out some sql server configuration.  Unfortunatley I was not able to get the account up during our small window. I brought the server up in single user mode using the -m switch in the startup parameters but when attempting to connect via sqlcmd I got errors saying only one admin was allowed to log in. My time ran out and we had to start back up in multi user mode. The reboot did not resolve the issue and I still have no sa account. After another day of troubleshooting we decide to backup the database after hours and restore to another server that is not having the authentication errors.

After we get that done I can now play with the srever in question and attempt to fix it. I still want to get sa up so I restart in single user mode but still get the connection error using sqlcmd. I disconnect the network and try again and I am in. I enable the account and start up in multi user mode. What I say next was shocking and something that NEVER should have happend. I open up Logins and I show that all of the domain acounts are gone. Some one must have removed them. I added my account back and I can now connect. OUCH talk about a lesson in security. Time to make some changes. More on that later.

But please do not let something like this happen to you. Follow the security best practices so you can prevent this from happening to you. To it from the start and do not back down. I have found that if someone tells you to just let them have sysadmin now and we will lock it down later, IT NEVER HAPPENS!! Be strong. As a DBA you are in charge of making sure that the data is safe, recoverable, and accessible. In order to do this job well, you must set up security accordingly.

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: