The Multifunctioning DBA

March 10, 2010  8:44 PM

You know about SQL Server and need a new Netbook? Win One.

Colin Smith Colin Smith Profile: Colin Smith

If you are a SQL Server Guru, probably more so than I, you might be able to win a Netbook or an even bigger prize pack. Technet is doing a contest where all you have to do is answer some questions for a chance to win and win pretty big. The big prize is worth over a$1000.00 so good luck.

February 26, 2010  10:00 AM

Test, Dev, and Production Databases in one Instance?

Colin Smith Colin Smith Profile: Colin Smith

NO, NO, and once again I say NO. Somewhere in the world someone may have a good reason to do this. I can not think of it but hey, I do not know it all. I have seen places that have Instances with all three database types and I told them to change. My thoughts are simple really. I have two major reasons why I think that this is a horrible idea.

          1. Security – I do not know about you but I do not want a Developer to have to call me everytime they need something changed in the developement environment. That would turn into my entire day. I am a fan of giving them the sysadmin role in a Dev environment and then they can do anything they want. Break it and I just do not care. I can rebuild anytime from production so whatever. If I have Dev and Prod on the same server well then I can not do that without running the risk of some Developer doing something that brings the system to its knees and affects production or even dropping a production database. (Explain that to your boss)

         2. Patching – How can you do validation testing on Service Packs and things like that when you have Test and Production on the same instance. Service packs are applied to the instance and not the database. That means that you have to test the patch in production and that is no good. (Again, explain that to your boss.)


Those are my two major reasons for not allowing this practice. I know you can save money this way but it just is not worth it. Just my two cents though.

February 25, 2010  10:00 AM

Powershell Sysadmin Role Audit Script

Colin Smith Colin Smith Profile: Colin Smith

I am working on a Powershell script that will audit my SQL Servers Logins and tell me who is a member of the sysadmin role. I think that this is a good idea for any DBA. I know that I do not want to have very many people with this type of access to data that I am in charge of protecting. This script will enumerate a list of users and groups with sysadmin role and then for each group it will list the members of the group. I am doing this because the Local Admin group has the sysadmin role on all of my servers. I have a vested interest in knowing who is a part of that group. I have already found some dev application service accounts that are a member of the local admin group on one of my servers. I do not like this and I do not like applications that require it. If an application does require sysadmin role then I am a big fan of giving that appllication its own instance of SQL Server.

Another reason that I am doing this is to show how many people have access that they do not need. I will use this as ammunition to remove the local admin group from the sysadmin role. I would like to have very tight control over that role and not turn over the keys to the SQL kingdom to anyone or anything that I do not deem worthy of it.  Now that is not to say that I think that the people in that group are not technically sound, I just do not want more hands in the cookie jar than are necessary.

February 24, 2010  10:30 AM

Service Accounts with sysadmin role

Colin Smith Colin Smith Profile: Colin Smith

I have been finding so many servers that I have now that have application service accounts in the local administrators group. I HATE this. Why why why, do we not determine what the application needs and just give them that. I am only ranting about this because in SQL Server 2005 the local admin group, by default, is sysadmin role on the sql server. In 2008 MS took that out, but most of the 2008 instances I am coming up on still have had it put in. Not a good idea. Why would I ever want to give some generic applications service account the keys to my SQL Server kingdom. I do not even want most real people that I can talk to to have sysadmin role in my servers. I am responsible for that server and all the data in it, I do not know what the random application might do, all I know for sure is that it has the option to do anything, ANYTHING, that it wants. Watch out for it and when you see it, do whatever you can to gte rid of it.


That is all for now.

February 23, 2010  9:46 PM

MSDB Problems

Colin Smith Colin Smith Profile: Colin Smith

Just the other morning at 3:00 AM I get a page, Love being on call, and I see some errors about MSDB. Well this concerns me but it is 3:00 AM and my head is not really on straight. I doze back to sleep, in ten more minutes I get two pages, This time I get errors about MSDB again and I also get another page telling me that the instance that is associated with this MSDB is down.

Well now I get up, I know that this instance is a production instance or I would not be getting pages at 3:00 AM. I know this because I wrote all of our monitoring scripts. (I love Powershell) So I drag out of bed and go to my computer log in and what do I see. Errors saying that the solution may be to restore MSDB from a previous backup. Well I know that I back MSDB up but I honestly have never attempted to restore MSDB. I will be doing that soon though. Anyway, I start researching and I find that usually restarting the SQL Server will repair the issue and if not then restore. Well the service was already not running so all I did was started it. Everything came up fine but I was not really convinced yet so I did a dbcc checkdb and found that all actually did seem to be well.

I then wiped the sweat off my brow and continued to look for a reason that this may have occured and why the instance shut down. I have found nothing but I have had no issue with the server from that point until now. I would really like to know what went wrong but I can not find anything in the SQL Server logs or the Windows Logs. If anyone knows where I should look I would appreciate it.

That was a good experience for me, now I really understand that I do need to back up my system databases as well as verify that I can restore them. I would also like to set up a server and start simulating Database Corruption and work on how to recover. Document it and have a plan just in case.

February 19, 2010  10:30 AM

70-443 Chapter 4

Colin Smith Colin Smith Profile: Colin Smith

More security and some repeat of chapter 3. This is good though, they really hammered home to make sure that you are using AV, Strong Passwords, and things like that from Chapter 3. New information about NTLM and Kerberos Authentication, Certificates for encryption, and a lot of great information about service accounts best practices. The big things with service accounts are to not use the local system account as it is a admin on the box and you do not really need that. Use domain accounts that have as little permission as they need to do the job. It also talked about the groups that are created that are set with those permissions, on the local machine at least. If you need your SQL Agent to access data in a file share then you will need to make sure that the domain account you are using to get at that data actually does have access to that data.

Great information about using firewalls to block traffic on TCP1433 and UDP1434 from the internet. Also about removing protocols that you do not need like Netbios and SMB.

Again if you do not know everything about all I just mentioned. Read that chapter. I know I will again.

February 18, 2010  8:25 PM

70-443 Chapter 3

Colin Smith Colin Smith Profile: Colin Smith

Chapter 3 was all about designing SQL Server Security in the enterprise. Again something that I need to start digging into and fixing in my environment. Should be fun though.

This chapter talked alot about Domain Level Group Policy as well as Local Group Policies. Using GP in order to force password requirments on SQL Logins. Also not to allow exceptions unless you have a real business need to do so, and even them keep them as minimal as possible. It talked about securing from attacks. Make sure that you have AV installed and running and update the DAT files. Do not let the SQL Database face the internet. Put it in a DMZ and let the web server face the public but do not let them interact with the SQL Server. In order to minimize SQL Injection do not use Dynamic SQL, Verify the input, and do not run services with high level accounts.

The next section talks about SQL Server Security at the instance, and database levels. Logins and Users and the Instance Roles that a login can be a part of and the database level roles that a user can be a part of. The different types of authentication that SQL server can use. Window or Mixed Mode.

That is a braod overview of chapter 3 and if you do not think that you know all there is to know about this stuff then read it. I am sure that I will again.

February 16, 2010  3:48 PM

What is the Primary Job of a DBA?

Colin Smith Colin Smith Profile: Colin Smith

So this post is about DBA’s like me. I am a systems DBA and not a developer. In fact I know very little about the language of SQL and I need to learn a lot more about it and hopefully I can integrate using more SQL into my job. Anyway, I ask the question of what is the primary responsibilty of a DBA? Is it Preformance, making sure Connectivity is available, Tuning SQL, Helping Developers, or making sure we can recover (Making sure that our data is safe.)?

I am still pretty new but I strongly believe that it is making sure that the data is safe and the we can recover. This seems like an after thought, like something that is so simple that we do not even need to talk about it. I disagree. I have done a ton of reading and watched more than a few webcasts about how to be a good DBA and one thing that comes up over and over is data recoverability. That, in my view, is the most basic function of the Primary DBA. When the Developer, who would never really do this, trys to get around the checks and balances that you should have in place and develops some code in production that delets 10,000 rows from a table and commits the transaction, who do they call? They call us, the Production DBA and in a panic they beg you to save them.

Are you sure that you can save them? How are you sure? Have you tested your backups to make sure that they are recoverable? Do you have the correct backup strategy in place for this database to make recovery faster and easier on all? 

Backing up is easy but having the ability to restore is what matters most. If you have taken a backup that is corrupt then what good was the backup? Because of this I am building a server whos only purpose will be restoring databases. That is what it will do all day and all night. I am going to automate it to go through each instance I have and each database that I have and restore them all and notify me of any issues. I have to know that I can restore that data. Another good thing about this is it will give me an idea of how long of an outage I will require if a full restore is required. That is good to know. That way users, and the application teams will know exaclty what to expect and will be able to determine if they should throw money at it to get a bigger better server, or some better backup recovery tool in order to make the recovery faster. All good things.

Brent Ozar says it all very nicely in his post about Backup Best Practices. Check it out as Brent is full of great information and presents it in a great easy to understand way.

February 13, 2010  8:00 AM

70-443 Chapter 2

Colin Smith Colin Smith Profile: Colin Smith

This chapter is all about cosolidation and multiple instances. Not a very long chapter but a great read. I have to say that this cleard up some misconceptions on my part and I really enjoyed the chapter. This chapter talks about instance design and consolidation strategy. I must say that I was a firm believer in one instance per appliction. This works great for keeping things seperate and that way I can give a named instance to each application. Easy to know what instance I need to look at when the application team calls with an issue. This however is not so great when you think about the resources on the server. You can install multiple instances on one server in order to condolidate hardware but at what cost. Each instance needs its own resources and unless you need to have seperation for security purposes it may make more sense to host multiple application Databases on on instance. You will be able to save resources and have more applications running from less servers. This will save a ton of money in hardware and software licensing. You still need to know what each database is doing so you can make sure not to overload the server. remember that you have DISK IO, Memory and other things that will come into play. For instance, each connection to the instance will take about 500 KB of Ram. The more databases that your instance is hosting, the more connections you will have and the more RAM you will be using. Make sure to take all that into consideration when doing your consolidation or instance design.

February 12, 2010  3:42 PM

The Money will come

Colin Smith Colin Smith Profile: Colin Smith

Mr, Denny Recently wrote a post called Do what you love, love what you do, or let someone who does, do.

I thin that he hits it all right on the head. Before I got into IT I did a job that I loved for a while. I know that you will all laugh at me but it was at McDonalds. I was an assistant Store Manager and I met my wife my first day on the job. I had a great time but did not make much money. While working at McDonalds I found myself getting into computers after my personal machine broke. I had a friend that had some computer knowledge and he helped me tear it down and build it back up. I was in love with it after that first time. Ever since that day I reall hated going into work because it was no longer what I liked. I still liked all the people and enjoyed hanging out with them at work but I no longer enjoyed the job.

My computer geek friend was graduating HighSchool and asked if I would like to head back to school with him. I did and I love it. I graduated with my computer Engineering Degree. I thought that the world was mine and that I would be making no less than $60,000.00 right out of school. Man was I wrong. That may happen to a select few and good for them, but I think that Mr. Denny is correct when he talks about that being a disservice to them in the long run.

I have had to work hard and start at the bottom of the IT ladder and work my way up to where I am. I have loved every step and I am thankful for my first Operations/Helpdesk job. it really gave me a good understanding of how the company has the infrastructure set up, the common issues that people have, how things comminicate, and many other things. Then I moved into our workstations department and worked on PC’s, and Macs. I learned a ton doing that and I just added to the knowledge about how things work at my company. Now I know the infrastructure, and the way the apps are setup and configured and work on the client machines that users are using every day. Next it was on to a Windows Admin, and oh man I thought that was the best it would ever get. I loved it and still do love doing Windows Admin work. AD, Managing Servers, filesystem backups and recovery, helping application teams troubleshoot and so much more….AWSOME stuff. Learned a ton of great things as a Windows Admin. At this point the money was better but not great. But I did not really care I just loved walking to my desk and preparing for what may happen that day.

Then the opportunity was given to me to be a DBA. I was not sure about it and I thought long and hard about it. I thought it would be great to do something new and learn some new skills but I was not sure if I would enjoy it as much as I did an Windows Admin. The money was a bit better but the career possibilities, I thought were better so I said what the hell. I took the job and loved it for a while and then started to feel like maybe I had made the wrong choice. I was not eager to learn and just did not really care about it. I missed the Windows work to be honest.

Then my job changed big time. I was moved to our parent company as a DBA and I was really worried about it at that point. But that turned out to be a great thing for me. Now I am the only one responsible for our entire SQL Server environment and man it is a lot. Now I feel even more in love with my job than I did as a Windows Admin. I love to read new things and practice them. I am getting my certifications and i never even thought about it as a Windows Admin. I love to watch webcasts, I am going to the SQL PASS SUMMIT. I absolutley love it now that I am the only one and now that I have so much to do with just one platform instead of three. I can not wait to get to work in the morning and now I stay late just to read, or try something with a specific feature of SQL Server.

The money is still not the best and not what I thought I would be making at this time in my career, but when I had those thoughts I was young and naive. I have only been in IT for about 6 years now and I think that I have moved up pretty quickly and I think I have had some luck in that. After you do some job you hate for just a little while you will realize that no matter what you do you need to LOVE it. You spend more time working than doing just about anything else in life other than sleeping, Maybe. It does not matter if you love something that pays just enough to get by or something that you can make millions. Just do what you love and that is the bottom line.


Thanks MR. Denny for your post.

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: