As we all know that Microsoft has released SQL Server 2005 sometime back and is planning to release the next version KATMAI before end of 2008. Also it has announced the end of life cycle support for SQL Server 2000. Its high time that we start with upgrading our instances with SQL Server 2005 and upgrade before the life cycles support ends.
The following versions should be available to perform an upgrade to SQL Server 2005 from a lower version database server.
SQL Server 7.0 Service Pack 4 (SP4) or later
SQL Server 2000 SP3, SP4, or later
SQL Server 2005 RTM or later
The upgrade can be done with either of the two methods as given below.
1. In-place upgrade
2. Side-by-Side upgrade
With In-place upgrade, the earlier SQL server instance is directly overwritten by the SQL Server 2005 instance. Doing by this method involves a lot complexity and time and if any problems occur in middle of the process the restoration path becomes complex.
Microsoft goes with the suggestion saying us to perform a side-by-side upgrade. There are few techniques available with this method of upgrading the database server.
a. Backup/restore method
b. Detach/attach method
c. Copt database wizard method.
Backup and Restore method:
With this method, we will install an instance of SQL Server 2005 database server and take a backup copy of the user databases and restore the same in the higher version. The logins and their permissions can be scripted out from the lower version.
Detach and attach method:
This method is same as the backup/restore method except that we detach the database from the lower version and copy the database file to the higher version server and attach the same. As same with the previous method the logins and their permissions will be scripted out.
Copy database wizard:
Copy database wizard uses a tool that is available with SQL server to migrate the database from the lower version to higher version. Here also the logins and the necessary permissions have to be scripted.
Tools to be used:
1. SQL Server upgrade advisor
2. SQL Server 2000 Enterprise Manager
3. SQL Server 2005 Management Studio
Upgrade pre-requisite decisions:
Before we start to upgrade, we have to take certain decisions to proceed further. Those decisions are critical to the upgrade process and should consider the business needs too. Listed below are a few
a. components to upgrade
b. features to be used/being used
c. any other technologies/scripting languages used along with SQL Server like VB Script, windows batch commands, etc
d. Edition used and edition to be upgraded.
Once these decisions are taken and listed, the upgrade path and process can be documented step-by-step process and used in the deployment.
Usage of Upgrade Advisor:
This tool given delivered by Microsoft can be used to find any incompatibilities in lower version of SQL server and those identified should be rectified before we proceed with the process of upgrading to SQL server 2005. This tool gives a XML report along with the normal GUI based report delivered.
Upgrade can be started once the errors identified by upgrade advisor are rectified. Upgrade as said earlier should be performed by either of the methods described earlier. And to mention that SQL server 2005 requires a separate upgrade process for every component to be upgraded.
This is the most important of all the steps that is needed. Most DBAs avoid this step as they would lack either necessary hardware resources or time. But this should be considered and included as a preliminary prerequisite to the process.
1. Take a backup of user, master and msdb databases.
2. Script out all logins and permissions.
3. Take a backup of SQL Server binaries and data files.
4. Take a backup to tape of the system state backup of the Windows Server system.
1.) SQL Server 2005 upgrade manual. (This document can be used as a guide to proceed with the upgrade of SQL Server instance)
2.) Upgrade handbook