Posted: Tue Dec 09, 2008 12:44 pm Post subject: [Tutorial] Upgrading SQL 2000 to SQL 2005
Upgrading from SQL 2000 to SQL 2005
By Anthony Zschusschen
As one of the most popular database servers, Microsoft SQL Server has been used for quite some time now. In 2005 Microsoft released an updated version of the SQL 2000 edition that was launched in 2001 called SQL Server 2005. Now you’re probably asking yourself, if SQL 2000 does the job, why should I upgrade to SQL 2005? Well, SQL 2005 offers many new features and improvement compared to its older brother SQL 2000. A few of the important features are as below:
- Online Database Mirroring
- Maintaining stronger and more flexible security
- Greater Manageability for VLDB’s
- Better development features
- Better Business Development solutions
Backing Up the database
Let us start off by making a backup of the existing database (just to make sure nothing goes wrong).
1. Open the MS SQL Enterprise Manager and connect to the server.
2. Expand the databases folder and right click the database you want to backup.
3. Select All tasks, then select backup database.
4. Provide a name for the backup in the Name text box. Leave the Database – complete radio button selected since we are performing a complete database backup.
5. Select the Overwrite existing media checkbox to initialize the destination file or device or select the Append to media checkbox to append the current backup to existing file or device.
6. To select a destination for the backup, click the Add button.
7. Select an existing file or enter a new file name.
8. Click the Options tab.
9. Select the Verify backup upon completion checkbox to verify the backup upon completion.
10. Once all the necessary options are selected, either click the OK button to start performing the backup or check the Schedule checkbox to schedule this operation for periodic execution.
Methods for upgrade
There are 2 methods to upgrade to SQL Server 2005:
- In-place upgrade: SQL Server 2005 takes over completely from SQL 2000. (Easier and faster/complex rollback and cannot upgrade partial systems)
- Side-by-side upgrade: SQL Server 2000 and SQL Server 2005 run side by side in completely separate instances. (more control and no downtime/more time, more intervention and more resources)
Things to do:
Before the upgrade:
- Run upgrade advisor and fix any issue/blocker for upgrade
- Take proper downtime before starting upgrade
- Once all issues / blockers have been resolved make a complete backup of all the databases (including master, model and msdb)
- In case you are planning to use Reporting Services in the future, install IIS
- If the operating system is server 2003, make sure that Windows service pack 1 is installed
- Install prerequisites in the database server (.net framework 2.0 and SQL Native Client
- Download the latest service packs and some cumulative hot fixes
- Make sure there is enough free HDD space
- Remove all fulltext indexes. And afterwords run the script below to remove all leftovers:
exec sp_configure 'allow updates',1
reconfigure with override
update sysobjects set ftcatid = 0 where ftcatid <> 0
declare @a int
set @a = 1
while @a > 0
set rowcount 300000
delete from sysfulltextnotify
select @a = @@rowcount
update syscolumns set colstat = colstat & ~16
update sysindexes set status = status & ~33554432
update sysobjects set status = status & ~200, ftcatid = 0
exec sp_configure 'allow updates',0
reconfigure with override
- Detach the user databases, before starting the upgrade. This way the database stays untouched during the setup
- Attach the databases
- Use the script below to change the compatibility of all the databases:
EXEC sp_dbcmptlevel @dbname ='<Database Name>', @new_cmptlevel = '90'
Alter authorization on database:: '<Database Name> to sa
Alter database [Report] set parameterization forced
- Recreate all the full text indexes removed from the databases
- Update the statistics for all the databases
- Test the application with the new database server 2005
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum