The Multifunctioning DBA

January 28, 2011  2:11 AM

Disabled SA account

Colin Smith Colin Smith Profile: Colin Smith

I recently had a situation where something happened to my SQL Server and I could no longer log in using windows authentication. I will get more into what happened in another post. Let me just preface all this by saying that I inherited this mess. Anyway, thanks to @afernandez and @banerjeeamit on twitter, and the article here explaining how to enable sa, I was able to get the sa account up, change the password, and then log in as sa to do what I needed to do. So thanks to all on #sqlhelp on twitter and if you are not on twitter yet DO IT!.

January 28, 2011  1:18 AM

SQL Saturday 47 Phoenix

Colin Smith Colin Smith Profile: Colin Smith

The schedule is out and the tracks are set. It really looks like it is going to be a great day of learning and hopefully a good day for presenting. This is my first time presenting so I am pretty nervous but I am very excited about it. I am excited to be a contributing member of the community and I really hope that this event is a big success so we can have many more. It really looks like a great list of speakers and sessions and I wish i could attend them all. Here is a link to the schedule. So please come out and have a free lunch and get some free learning on.

January 26, 2011  8:00 AM

What is DR??

Colin Smith Colin Smith Profile: Colin Smith

What is DR? How do you define it? I would really like to know. Please put your thoughts in the comments but a recent event has made me re-think what DR truly is.

I have a SQL Server that is very mission critical and if the database is not up and accessible the company is losing money. This is very important and needs to have as close to 100% uptime as possible since this application is part of a consolidation project and that means that the server is taking transactions just about 24 hours a day.

This is a server that I inherited and it seems like the vendor has had control of the environment pretty much exclusively. I have been brought in and I have made some recommendations to help improve performance and I have made some comments about HA and DR. I have made comments because the vendor is essentially using Transactional Replication as a solution for both. They do not have Primary keys in some tables so that means we do not have those tables on the replicated servers. They also are not replicating Indexes, Triggers, and Stored Procs. This would be OK if they had a system in place to script them out and apply them on the replicated database but they do not.

So, Something happened that caused kerberos to break and now we can only use sql server authentication to the instance.  We only have one SQL Server account and that account is dbo in the production database and has no other roles except public. The SA account has been disabled, and on top of that no one knows the password for it.

So to me this is a disaster. I also forgot to mention that the Agent will not start up. I do not think that a cluster would have helped us since the server/instance name for the cluster would be the same even when failed over to different hardware. So I classify this as a situation where DR needs to be in place.

What say you about what a Disaster is. I do not think it is when your entire data-center is taken out anymore. It could be just about anything and if you have not planned for it then you will pay the price as I did.

January 25, 2011  6:02 PM

Free Training Contest

Colin Smith Colin Smith Profile: Colin Smith

Who wants some free training? I know I do and that is why my last post was what it was. Here is a link to the competition. Get in and get your post done today for your chance to win some Awesome Training.

I think all should enter but I really do hope that I win. Good luck though and thanks again to the SQLSkills group for doing this for the community.

January 21, 2011  7:02 PM

SQLskills Training: Why I need it.

Colin Smith Colin Smith Profile: Colin Smith

About three years ago I became a DBA. This was not by design and I never set out to be a DBA. I started out at a help desk, worked my way into a techshop (Working on deploying and repairing laptops and desktops for the company), and then finally got to what I thought my end game was, a Windows System Administrator. I loved it, came into work every day and was very happy to do so. I thought I would be happy forever. Honestly I bet I would have if the opportunity to become a DBA at my company had not come up. One day my boss asked if I would like to change roles and try out being a DBA. At first I wanted nothing to do with it but I wanted to do some research and determine what was best for me.

After a few days of research and talking to some other DBA’s, I found that being a DBA would be a big challenge. I mean, I had a lot to learn about Windows Administration still but at my company things in that area were becoming stale. I was not learning a lot everyday anymore and I was good at what I did. I really did want a challenge again. I also found that a good DBA is more sought after then a good Windows Administrator and I thought that if I can rise to the challenge and be a great DBA then I will have great security in what I do.

So with that in mind I took the job and I knew nothing about databases. I mean I had installed SQL Server many times, and now I know I did a very poor job at that, but that was about all I had ever done with SQL Server. Databases were very intimidating to me and that was part of the challenge. Getting out of my comfort zone and learning something new.

So far I think that I have done well, I have read books, I am working on MCITP: DBA, I try to attend webinars and free training wherever I can, and I was also able to go to PASS for the first time. All of that is great, but I have never had a formal MSSQL training class. I would love to be able to learn from the experts (and SQLskills has those) and talk with them one on one when I am having trouble wrapping my head around something. Blog posts are great but nothing can replace, for me, the immediate response you get when learning face to face. So I think that I would really gain a lot from attending one of these classes. Pass was GREAT and I learned a lot and I have already put some of that new found knowledge into practice and seen great results. I really think that attending a more formal training would be just so much more beneficial to me than even PASS.

To be a great DBA, I know that I need to understand the internals of how SQL Server works and I know that I do not have that skill set yet. I am striving to get it, again by watching training like the MCM training videos, but sometimes that is over my head and I think if I had a better understanding of what is going on under the covers I would be able to better serve my customers, have servers that run faster, and save my company money all at the same time. That is my goal as a DBA. And that is why I would love to attend a training event put on by SQLskills.

Once back from that training I think that I would be able to better assess the environment that I have to deal with now. I inherited this from someone who was not a DBA and honestly things are messy right now and need a lot of work. So I think that this class would give me the ability to correctly assess what is happening in my environment and make the right choices to get things where they need to be and configured properly. Right now I have about 200 Instances of SQL Server installed and my boss has talked with me about trying to do a consolidation of these instances. The knowledge gained from a class would be invaluable to me, especially right now, when I am working on figuring everything out. I am talking about purchasing new hardware, upgrading SQL Server licensing, moving into virtual environments, and I do not want to screw things up. With the amount of money that is going to have to be spent to do things right I need to make sure that I also do things right and make sure everything performs better than it should when we are done. All the knowledge that I gain is knowledge that I can put into practice from day one being back from the class.

For more details on the class check out

December 31, 2010  12:00 PM

Happy New Year

Colin Smith Colin Smith Profile: Colin Smith

I hope that you are all out having fun and not reading my blog today.  I also hope that you all have a great time and have a great new year and most importantly be safe and do not drive drunk

December 30, 2010  10:00 PM

New PS Project

Colin Smith Colin Smith Profile: Colin Smith

I have just been informed that I have to come up with a way to create a Dynamic Distribution Group in exchange. I have the criteria but I have no clue what a Dynamic Distribution group in and how it is different then a Distribution Group. So off to the learning I go but I think this will be fun and should be pretty simple. I will let you know how it goes and I will post the code for you as well.

December 30, 2010  4:50 PM

PASS Summit DVD’s already helping me out

Colin Smith Colin Smith Profile: Colin Smith

So I just got my DVD’s on Tuesday after getting back to the office after vacation. I have watched a few and all that I have seen are very good. But I must point out the session called “Transactional Replication: Beyond the Basics” by Kendal Van Dyke (Blog | Twitter).  Maybe it is just me and perhaps only because I got pulled into an environment that is using a lot of transactional replication and is having some issues. The issues are not only with replication but we have many issues with replication. I am also fairly new to replication. I understand what it does and the basics of how it works.

Kendal’s session really helped me out though. I have a much better understanding of what is going on “under the hood” with replication and I have some ideas of how I think we need to change our topology to better fill the needs of the application. He showed a the options that you have with replicating tables, procs, etc.. I had no clue that I had so many options. Now I can really dig into the replication and find out if we really are replicating all the data and information that we need to. I know for sure that all the tables do not have Primary keys and that is a big problem since that is a requirment to be able to replicate a table.

Another thing he went over is setting up alerts and doing it the correct way. This has been a problem for us but will not be any more.

Kendal is also very active on twitter with the #sqlhelp hashtag and gives out some great information about many topics to all.

Any way I just wanted to Tell Kendal that I enjoyed the session and I am sure that I will watch it again. I wanted to thank him along with all the other presenters at PASS. I have not watched them all yet but this one helps me now and gives me a good idea of where to start and how to accomplish what I need to get done.

The only negative thing I could say about the session recording, and I doubt this is Kenal’s fault, is that the audio cuts out at about 64:45 into it and does not come back until about 75:15 minutes. So that is about 10 minutes worth of information that I am missing out on. But a great session for me. Thanks again.

December 30, 2010  8:35 AM

DB Corruption found by DBCC

Colin Smith Colin Smith Profile: Colin Smith

The other day I got a call saying that a users database was corrupt and I needed to look at it. They said that DBCC CheckDB was failing. So of course I made sure we had backups and then logged in to take a look. Below is the error that was found.

Extent (1:11478864) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it. Extent (1:11478872) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it. Incorrect PFS free space information for page (1:11478795) in object ID 1733581214, index ID 1, partition ID 72057596405219328, alloc unit ID 72057594049986560 (type LOB data). Expected value 80_PCT_FULL, actual value 95_PCT_FULL. CHECKDB found 2 allocation errors and 0 consistency errors not associated with any single object.

After doing some searching I found that this is just a problem with the GAM that has a bit set that did not need to be since I did not have an index that it was referring to. More infor here.

So I took a backup of the database and did a restore to a different DB name. I ran a DBCC CheckDB Restore_with_Data_Loss and I was able to get the database to run a DBCC with no errors and had the users validate that we did not infact lose any data. All is well with the world.

December 29, 2010  10:00 PM

SSRS Data Source issue

Colin Smith Colin Smith Profile: Colin Smith

Today I ran into an issue with DataSources in SSRS in SQL Server 2008 R2. This system was upgraded from SQL Server 2000 and we had some minor hicups getting it all upgraded and running but we were able to do it without too many issues. We handed the server over to the dev team and they started testing and getting RS configured. They were able to use all the reports and datasources that they had and did not have any major issues. Until today. I got a call saying that they were getting access denied when setting up a new datasource. I searched and searched for the issue. The RS log only showed that the user did not have access to create the object. What is even more strange is that is the case for all users. Even me and I am sysadmin in SQL Server and RS and I am in the content manager role as well. Same error. I found, after some searching, that this may be because IIS is set to allow anonymous access. That might make sense but since I am now on 2008 R2 and that does not use IIS it did not make sense but I changed it anyway to no avail. The other thing that came up was UAC. Well I am on Server 2003 so that is not it either.

Then I found out that if I create the data source and save it without testing it that I can then go back into it and test it just fine and the data source does work to pull data for the reports. I still have not found a resolution to this and would love to hear what some of you think it might be and if anyone else has run into this issue. It is not something that is stopping production but it is something that I would like to get resolved.

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: