Archive | SQL Server 2005 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

SQL Server 2005 won’t install

A colleague of mine had tried to install SQL Server 2005 several times onto a x64 server. He kept gettting the following error:

The SQL Server service failed to start. For more information, see the SQL Server Books Online topics, “How to: View SQL Server 2005 Setup Log Files” and “Starting SQL Server Manually.”

After searching through the logs and trying multiple items I finally ran accross a fix for our case:

From Microsoft, this can happen when the ratio between logical processors and physical sockets is not a power of 2.

So how do you know if you are affected by this?

This was on a Dell PowerEdge R900, so if you are working with that then you may be in the same situation.

You could download and run WinAudit or cpuz to see if you can determine anything from the results. You could also possibly query wmi.

Comments { 0 } Posted on June 26, 2009 in SQL Server 2005

Duplicate ErrorCode Columns in SSIS

If you are using SSIS to handle a column that has the name of ErrorCode you could run into an issue. You can find more information on the specifics @ Microsoft Connect.

Your error may look similar to this:
Validation error. Data Flow Task: DTS.Pipeline: The package contains two objects with the duplicate name of “output column “ErrorCode” (180)” and “output column “ErrorCode” (13)”.

In my case, here’s how I fixed it:
On the task(OLE DB Source) that gave me the error, I went into the advanced editor. Go to the “Input and Output Properties” tab and select the Error Output Columns. I simply changed the name of the second instance to ErrorCode2 and it worked for me. It may be better to actually rename your column in the table but that requires more work. I guess it’s possible that this could affect your error logging but for my case it did not.

Comments { 1 } Posted on June 19, 2009 in SQL Server 2005, SQLServerPedia Syndication, SSIS