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.

Resources:

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

,