Which SQL Server version?

From my understanding, it is only necessary for me to discuss SQL Server versions later than SQL Server 2000. I include SQL Server 2000 because the truth is that many people are still using this product. I know that the shop I work in has mostly SQL Server 2000 instances and a couple of SQL Server 2005 so that is where I’m coming from. Some of the consideration may be out of your control because your shop may not have a SQL Server 2000 server. I hope to present the pros and cons that go with the order in which you choose to begin your journey. I also hope that others will add their opinion to back me up or correct me.

SQL Server 2008
If you aren’t working with a certain SQL Server version at your workplace then I would think that going with SQL Server 2008 would be your best option. It’s the latest and greatest and you will give yourself the most shelf-life for your effort. SQL Server 2008 was released in August of 2008 and you can see a build list here. One negative I noticed is that some of the books (MCTS Self-Paced Training Kit (Exam 70-432): Microsoft® SQL Server® 2008 Implementation and Maintenance (PRO-Certification) are still being finalized for this version at the time of this writing. At the bare minimum, I would recommend becoming familiar with this version and go grab the Express Edition.

SQL Server 2005
You still may want to look at this version first if you are breaking into the field. There are tons of resources for SQL Server 2005 and you can focus on laying the ground work for a successful base to your knowledge. SQL Server 2008 does have new features, but I am not sure they are make or break for the beginner DBA. Personally, I have only gone as far as touching SQL Server 2005 and 2000. I am in the process of beginning to test SQL Server 2008, but since it’s not deployed in my work environment I feel that I gain more by focusing on maintaining and upgrading from 2000 to 2005. The amount of resources available to someone starting out with 2005 will be far greater than 2008 at the time of this writing, but there are many variables to add up when making your decision and I can’t account for all of them. One negative aspect that I saw last night would be finding a development version of SQL Server 2005 to start your test lab with. Amazon did not have any copies in stock so you would have to find a reseller if you wanted to use more advanced features that weren’t available in the Express Edition. SQL Server 2005 is up to SP3 so you are past the initial growing pains that come with a product without a service pack. You can grab the Express Edition here.

SQL Server 2000
Support for this product is only available through extended support. If you are currently in a shop that primarily uses SQL Server 2000 then you first step should be learning how to help maintain what is already there. If you are going to be working with a product then it does not make sense to me to only prepare for the future. This is the situation that I inherited so I can speak more clearly on it than the others. When this package fell into my lap we were only using SQL Server 2000. Even though the product life cycle is basically expired, the product still has to be managed by someone. Given the current economy, there’s a pretty good chance that budgets are frozen and upgrades to SQL Server 2005 may not be possible. This does not mean you should not prepare for the future, but it does mean you better become an expert on what your shop is using right now if you want to be the “go-to-guy” where you are at. There are tons of resources out there for SQL Server 2000, and that has helped me a great deal when it came to becomming the person who wants to answer the questions. If your shop is currently using SQL Server 2000 then you should have access to the necessary materials to build a good test lab to begin the learning process. I made the choice to go ahead and go with the 2005 certification track since that is the direction we are going, but I still needed to know how to do the same things in 2000. Having a certification of MCITP Database Administrator in SQL Server 2005 helps give those around me the confidence that I am able to facilitate an upgrade from SQL Server 2000 to 2005.

The SQL Server version you choose to go with definitely depends on variables that only you know. Whether or not you actually get to touch the SQL Servers at your workplace also play a huge role in what kind of decision you make. I would still download an Express Edition copy of 2005 and 2008 in order to get your feet wet, but you have to focus on what you feel will give you the edge where you work or where you would like to work. It all goes back to establishing what your goal is and then taking action to make it happen. If you want to become a SQL DBA where you work then you have to know if there’s even a possibility of that happening before you expend too much energy going down that path. Pick a target and set some goals to try and hit it. If you never set them then you’ll miss it every time. And by all means, if you want different results then you have to change what you are doing.

Comments { 0 } Posted on February 17, 2009 in SQL Server 2000, SQL Server 2005, SQL Server 2008

Ebooks, Twitter, and Contacts

I was checking out a new contact I made on Twitter, which I was convinced I should use by Brent Ozar, and Marlon’s post on a free eBook made me think back to some other great finds that I’ve seen on Red Gate. If someone thinks of any others please feel free to post.

Best of SQLServerCentral.com, Vol. 1

Best of SQLServerCentral.com Vol. 5 and 6
You can also download 3 good ebooks from Brad @ Red Gate.. Brad’s Sure Guide to SQL Server 2008, Brad McGehee’s DBA Best Practices, How to Become an Exceptional DBA.

“Dissecting SQL Server Execution Plans”
by Grant Fritchey

I’m sure I”m missing some links that I had, but I’ll update if I find them. I hope noone at Red Gate has a problem with me posting them like that because they should drive people to try the products.

Comments { 4 } Posted on February 14, 2009 in Education, SQL Server 2000

Keeping the ball rolling

Once I passed the 70-431 I think it gave me the positive reinforcement that I was able to do this and I just need to keep at it no matter how difficult things got. Here I am, not a SQL DBA by title, but getting thrust into the position due to circumstances and desire to get it done I have to try and become really good at what I do. I decided to go with the 70-443 next so I purchased the 70-443 Self Paced Training Kit from Solid Quality Learning as well as the Sybex MCITP Administrator: Microsoft SQL Server 2005 Database Server Infrastructure Design Study Guide. I’ve since bought all 3 of the sybex in this series as I like them for referrence better than the Self Paced kit. I continued to spend hours every night pouring it into those books and practice. I also set a date and paid my testing fee to give myself a deadline, and I passed the test on my first try. The only test left was the 70-444 and I was relieved to hear that it may not be as hard as the 443. I purchased the Self Paced kit for this exam and went through the same process to finally become a MCITP Database Administrator. For me, it wasn’t about becoming a “Paper Tiger” as much as it was forcing myself to learn. I wanted to gain credibility and experience in a field that was new to me in a sense. I also understand that it’s not that hard for some people to pass these test without knowing the material. For me, there’s no way I could have passed it without an understanding because of how I function. It helped me to stay focused and I would encourage you to find what will keep you focused as you seek to be the best DBA or developer that you can be.

Since that time, I’ve tried to take advice from others about getting involved in the community. I’ve gone from reading blog posts and forum questions to writing blog posts and answering questions. Sometimes the best way to learn something is to try and find an answer for someone who can’t find it themselves. That covers most of the introductions for now and I hope you’ve gotten a little better idea of how I got to where I am at and how we can learn from each other and continue on our journey of becomming a master SQL DBA (or whatever you want to master in the SQL world).

Comments { 1 } Posted on February 13, 2009 in General

SqlSecurityHandler is invoked

I had an admin come to me this morning regarding a SQL Server box that had suddenly gone down. The SQL Service and SQL Agent where both stopped. When I looked at the SQL Server error log I noticed the following error: “SqlSecurityHandler is invoked. Potential buffer overrun detected – server is terminating.” I went to the trusty google box and didn’t find a ton of useful info, but I did finally come across a KB from Microsoft.

This SQL Server is handling a third-party database whose developers have decided it’s ok to use “SELECT *” in many of their queries. They have also opted to not use stored procedures alone. Both of which I think are bad ideas. They have deadlocks that pop up all of the time, and I see many holes in their reasoning but there’s not a ton I can do to change any of that. They’ve never tested under the load that they are getting with us, so I believe they are learning the hard way that their product might not be as scalable as they thought.

Anyway, I’ve run many a trace against the box through perfmon, profiler and DBCC TRACEON without incidence, but it turns out that it’s possible to crash the server when the following criteria are met:

“The SQL Server process may end unexpectedly when you turn on trace flag -T1204 and a profiler trace is capturing the Lock:DeadLock Chain event in SQL Server 2000 SP4”. We were able to determine that the vendor had been running a profiler trace capturing that event during the time that this happened.  The server was patched up to 2040, but obviously this hotfix is supposed to fix this issue. I decided this would be a good time to go ahead and bring it up to hotfix 2273 given the problems that we’ve had. That means I chose to bypass 2050 and go with 2273. That means I chose QFE over GDR. I’ve included a link to an explanation that helped me understand those two items. Normally, it would be recommended that you stay with the GDR unless you need a specific fix above that hotfix. At least that’s the way I understood it.

Cumulative list of SQL Server 2000 hotfixes


Microsoft Security Bulletin MS08-040

Comments { 0 } Posted on February 12, 2009 in SQL Server 2000

Getting over the Hump

I hope to give you a little background on myself in the first few posts as well as catch you up to speed on where I am today and how I got here. While this may not seem technical a sense, I believe that’s important for those who are embarking on the journey from the beginner mindset. Everyone has been there at one time or another, and the more resources they have the better chance that something will click.

After sitting in the IT World for so long and becoming someone who knows a little about a lot, I decided it was a good time to become the guy who knows a lot about a little. Having a wide range of knowledge has its advantages, but if you really want to excel in your career it helps to have at least one primary focus. One thing that you are as good at/or better than anyone you know. If you are not there and you are working on it, then that’s the same thing. Personally, I felt that it was time to try and become a SQL DBA Master and that’s how I started the journey.

It was a blessing in disguise that I was asked to look over a problem that had been occurring with SQL Server 2000 at my new job. I had not done any work with performance tuning, and my experience with SQL Server involved creating the database and items lower than that. I really dove into the deep end with a passion, and found that this was something that I enjoyed doing. I learned some things the hard way, but that is how it goes sometimes. If you haven’t experienced a moment where you realized you “don’t know it all” then I welcome you to have a moment and come back in a few minutes. I had found the area that I wanted to pour myself into and try to become the best I could be.

I had been holding onto the 70-431 Self Paced Training Kit from Solid Quality Learning for several months trying to set goals only to see everything else get in the way. Never mind the fact that I had a young child and pregnant wife so my responsibilities were many. It was real easy to put the book aside and play with my little boy. As I stated in my earlier post, the ebook from Brad helped get me over the hump. It was time to put in the sacrifice to become an exceptional dba. For me, that meant putting a great deal of effort to learning from the 70-431 book and getting ready to take the MCTS : SQL Server 2005 Implementation and Maintenance exam. I also determined that it help me a great deal to go ahead and pay for the testing fee and set a date in the near future to take the test. If an emergency came, I could always move it back if I had to. What I found that is even when an emergency(a death in the family and a flight back from Florida the day of the exam) came, I was still so passionate about getting this under my belt that I went ahead and took the test and passed. Getting that first certification was a big deal for me since it gave me confidence and the energy to move forward. Throughout this whole time, I was dedicating 2-3 hours a night to studying and trying to learn. For someone with ADD and a terrible memory this was a huge step for me. In my opinion, this journey takes sacrifice. It takes learning how to manage priorities in a way that I could have never imagined myself being able to do. It means making a committment and being ready to stick to it. I hope that I can one day inspire someone the way that Brad’s ebook inspired me.

Comments { 0 } Posted on February 12, 2009 in General