Friday, October 12, 2007

SQL Server 2005 Upgrade

Upgrading from SQL Server 2000 to SQL Server 2005 is always a daunting task if you have had a chance to contemplate as to what needs to be done before and after the upgrade process. On the other hand, if you had a chance to plan out everything before hand, then it is very unlikely that you would land yourself in a soup unless and until the Gods choose to be very unkind towards you on that fateful day. :)

So, first let us understand what are the two procedures of upgrading from SQL Server 2000 to SQL Server 2005.

1. IN PLACE UPGRADE
2. SIDE BY SIDE UPGRADE

So, before I go ahead and explain these two upgrade procedures, let us dwell on the pre-requisites for upgrading to SQL Server 2005.

1. Testing: Make sure all your applications are thoroughly tested and function as expected on a SQL Server 2005 environment before deciding to take the final step
2. Fall-back plan: This is what I call "insurance". It's a hassle but when a problem does happen, this is what takes care in helping you out of the problem. I believe it is better to be safe than sorry.
3. Backups: This should have been included in Point 2 but this is something of utmost importance, so I thought I would re-iterate this again. We need to have backups of all the user and system (YES! we need them.) databases in case we need to use our fall-back plan.
4. Upgrade Advisor: Run the upgrade advisor on your existing SQL Server 2000 installation and find out if there are any REDFLAGS in upgrading to SQL Server 2005 based on your current configuration. (Please check the following link for more details: https://partner.microsoft.com/40001607)

In-place upgrade process
The upgrade process which refers to upgrading all the existing installed components of SQL Server 2000 to SQL Server 2005 on the same server.

Side-by-side upgrade process
The upgrade process which refers to leaving your existing SQL Server installation intact and installing a fresh SQL Server 2005 instance on the same server or a different server and then migrating over all your databases. Once, all the components (Databases, Plans, DTS Packages etc.) have been migrated the SQL 2000 installation can then be phased out.

Both these upgrade processes have their PROs and CONs which are clearly documented in the TechNet Article mentioned below.

Upgrade Handbook for SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/sqlupgrd.mspx
Upgrading to SQL Server 2005
http://www.microsoft.com/sql/solutions/upgrade/default.mspx
Technet Article on Upgrading SQL Server 2000 to SQL Server 2005
http://www.microsoft.com/downloads/details.aspx?FamilyID=3d5e96d9-0074-46c4-bd4f-c3eb2abf4b66&DisplayLang=en

So, what do we need to take care of when upgrading to SQL Server 2005 from SQL Server 2000?

1. Replication: If you do have replication configured (subscriber/publisher/distributor), you need to find out if your replication topology is compatible and will continue to function smoothly if the current server is upgraded?
2. DTS Packages: These need to migrated to SQL Server 2005 using the Migration Wizard. Related Article: http://msdn2.microsoft.com/en-us/library/ms143501.aspx
3. Maintenance Plans: Again they are different from their SQL Server 2000 counterparts because in SQL 2005, we use SSIS in the background.
4. Logins: In case of an side-by-side upgrade, then these need to be migrated too.

These are a few of prime areas of concern that come to my mind. A whole bunch of other considerations are mentioned in the above mentioned Technet Whitepaper. Please do read through that if you are planning to upgrade to SQL Server 2005.

No comments: