Archive | SQL Server 2000 RSS feed for this section

SQL Server 2005 Upgrade Fact Finding Mission

Due to constraints such as budgets, staff, and overhead it may only be feasible for a shop to move from SQL Server 2000 to 2005 instead of going straight to SQL Server 2008. There are plenty of resources on the internet that cover the advantages and pitfalls that one might encounter during the leap of faith, but I hope that by documenting my own experience I might help others have an easier transition.

One of the first stops for anyone interested in upgrading should be reading the following papers from Microsoft:
SQL Server 2005 Upgrade Handbook
SQL Server 2005 Upgrade Technical Reference Guide

After you’ve digested nearly 400 pages of information you should at least have a grasp on the initial concepts of what you are about to undertake and possibly even have a rough idea of what your game plan will be.  The biggest positive is that we can learn from all of those who have gone before us since we aren’t exactly breaking new ground. SQL Server 2008 has even been out for some time now, and hopefully you have investigated whether or not you should bypass SQL Server 2005. A few resources on that can be found at:,289483,sid87_gci1325303,00.html

In our case, the decision to make a slow move towards SQL Server 2005 was made based on a previous purchase of the software coupled with the later knowledge of the amount of resources required to redo what we had already begun.

Once you’ve decided to go forward with your upgrade it would be wise to download the Microsoft SQL Server 2005 Upgrade Advisor. This product will tell you what issues you could possibly run into. It’s not going to cover everything that could possibly trip you up, but it’s a good starting point in educating yourself on what you have and where you are going.

You may also find that it would be beneficial to run the Microsoft SQL Server Best Practices Analyzer to look for any other issues that may be there if you have an inherited system. You may find that it’s a good time to do a little bit of cleanup before you upgrade and there’s no time like the present. I’ve found this tool useful in tracking down issues such  as stored procedures with a sp_ prefix, usage of Select *, and objects calling deprecated built in functions.

In Summary, I think that it would be wise to use this planning time to clean up issues with the current SQL 2000 instance before you move forward if you have the opportunity. While some of the issues you’ll find won’t prevent you from going forward, it seems that while you have your sleeves rolled up and you’re inside the system a little cleanup would reduce future maintenance issues. Of course, if your situation is anything like mine you will run into issues that just have to wait because there’s no way that modifications can be made in a timely manner without sidetracking the upgrade.

Comments { 0 } Posted on September 18, 2009 in SQL Server 2000, SQL Server 2005, SQLServerPedia Syndication

Upgrading from SQL Server 2000 to 2005

This is part one in a series of upgrading from SQL Server 2000 to SQL Server 2005. The first question I want to address is why would you want to upgrade in the first place. It’s important to know why you are doing something if you are going to be able to truly understand what you are doing. Most of my opinion and experience comes from the admin DBA side of the fence so you won’t find a ton of developer jargon thrown around because that’s not a strong point for me. I’m in the process of upgrading from SQL Server 2000 to SQL Server 2005 as well, so I hope I can chronicle the journey and help others if they are in the same situation.

It goes without saying that the biggest reason for upgrading is that the product is basically End of Life. While you can still get extended support through 2013 it would still be wise to start the process of moving forward. There are plenty of resources on the internet about the new features that were introduced or approved upon with SQL Server 2005. Since I am more of an administrator I get a little more warm and fuzzy about Table and Index Partitioning, All Permissions Grantable, Mirroring, Snapshots, Online Restores, and Performance DMV’s.  While the new data types can be very useful and I’m sure CLR has its place it is hard for me to get as excited about those items. A full list of features can be found by digging through Microsoft’s full list or an abbreviated Top 30.

For us, SQL Server Integration Services this was one of the biggest hurdles to overcome because of the number of DTS packages that we are dependent upon. Microsoft has completely redone the way that data is moved from the ground up and there is a learning curve. We eventually decided to purchase a few licenses of a third-party piece of software to help with the package transition.

There’s also no more Enterprise Manager, and for the most part that’s a good thing. Visually, it’s much more appealing and it’s worth running a client to access SQL 2000 instances to do most things. This late in the game I’ve even been running a SQL 2008 client because of the added features that it gives me as an administrator.

Coming in part 2, Upgrade Education and Fact Finding.


Landing page for SQL Server 2005
Microsoft whitepaper on “Why Upgrade to SQL Server 2005”
FAQ: Why Upgrade to SQL Server 2005 now
What’s new in SQL Server 2005
SQL 2005 Books Online : What’s New
Top 10 new features in SQL Server 2005
Consider what’s new in Microsoft SQL Server 2005
New T-SQL Features in SQL Server 2005 Part 1
New T-SQL Features in SQL Server 2005 Part 2
The Differences Between SQL Server 2000 and 2005
The Differences Between SQL Server 2000 and 2005 – Part 2

Comments { 2 } Posted on September 16, 2009 in SQL Server 2000, SQL Server 2005, SQLServerPedia Syndication

Problems with SQL 2005 Upgrade Advisor

For those of us that are still in situations where we deal primarily with SQL Server 2000, there should come a time when we have to run the Microsoft SQL Server 2005 Upgrade Advisor. Some of you may be going straight to SQL Server 2008 and this issue may be resolved in the SQL Server 2008 Upgrade Advisor.

During the initial run of the wizard, an error will pop up without giving you many details. In my case, I look at the event viewer and see the following:

.Net 2 Runtime Error

.Net 2 Runtime Error

If you browse the internet you’ll find various ideas to fix it. Some suggest removing all .Net versions and reinstalling them. Some suggest not putting on .Net 2 SP1 until you run it. Another suggestion is to change the Aspnet.config file to read <legacyUnhandledExceptionPolicy enabled=”true” />. None of these suggestions worked for me.

The fix that got me working was to copy all of the files from within the \BPA\Bin folder and paste them into \BPA\.

I think I would try this first before going through all that other trouble. If this doesn’t work you can always explore the other options.


Comments { 1 } Posted on August 5, 2009 in SQL Server 2000, SQL Server 2005, SQLServerPedia Syndication

Install SQL Server from network

Whenever it comes time to install SQL Server to a server it’s usually not a big deal to walk back to the server with the disc or share my drive across the network. I ran into a different situation this weekend when I needed to install several versions on a server while on a vpn connection from home. I can’t switch the discs when needed because I’m over 30  minutes away so I needed to come up with a better way.

The problem arises when I have to deal with a .iso file. I needed a way to get the contents out of the iso and into a folder that I could use for a network install. After a small amount of searching I found that I had a few options. The option from Microsoft (Virtual CD Rom) didn’t play nice with the server permissions. I finally got ISOBuster to work for my needs.

Once I extracted the .iso file to a folder for each of the cd/dvds then it was a very easy process. I’m sure there are multiple ways to do this, but given my situation of being remote and only having the .iso files the solution I found seemed to do the trick.

Comments { 0 } Posted on March 8, 2009 in SQL Server 2000, SQL Server 2005

Jumbo Packets

In the past few months, we’ve gradually been implementing a new SAN solution from Dell that uses iSCSI. We are kind of learning as we go and there are always bumps in the road. I’d noticed we had some I/O issues on our main production SQL Server but nothing seemed to add up and I couldn’t find where the problem could be outside of tuning indexes or putting data files on a different drive. We are on SQL Server 2000 but we are moving towards 2005 and I’m sure that might have helped me troubleshoot the problem. I came across something the other day about Jumbo packets. As I read more and figured out where to check things I figured out I might have been onto something. Brent Ozar passed me this link that helps check on the packet size. I looked at the iSCSI connection(NIC card –> Configure –> Advanced) and noticed that the Jumbo Mtu was set to 1500. I asked more questions of our admin and everything else along the pipe had been raised to 9000, but somehow the card got missed. Once I set this to 9000 I saw that I had far fewer I/O problems. They are still there due to poor indexes and disk contention but they are better than they were. Just thought I’d pass this along in case anyone else had a similar issue with iSCSI.

Comments { 1 } Posted on February 26, 2009 in General, SAN, SQL Server 2000