July 3, 2008 at 10:52 am
Good afternoon. What do I need and how do I convert an existing SQL Server 2000 Database to SQL Server 2005?
July 3, 2008 at 11:00 am
SQL Server will upgrade it to 2005 for you. You'll need to either restore or attach the DB to a 2005 instance or upgrade your 2000 instance to 2005 by running the 2005 installation disk.
I would recommend running the SQL Server 2005 upgrade advisor on your DB first so that you can look at depricated functionality and possible issues that you may face after the upgrade.
July 3, 2008 at 11:26 am
In addition to:
I would recommend running the SQL Server 2005 upgrade advisor on your DB first so that you can look at depricated functionality and possible issues that you may face after the upgrade.
I would recommend reading sp_dbcmptlevel (Transact-SQL) BOL link:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/508c686d-2bd4-41ba-8602-48ebca266659.htm.
If you have the luxury of disk space etc., a possible path to follow is to detach the db, copy the db and log file to another directory, and attach the COPY to the 2005 instance. Of course re-attach the original to the 2000 server so as to keep it operational. Then test, test, test the 2005 instance using the initial compatability value and then re-set the compatability value if you want to do so.
July 3, 2008 at 11:44 am
change your compatibility level to 90(SQL 2005) and
Always after and upgrade run the following statements.
DBCC UPDATEUSAGE (aspnetdb) and DBCC CHECKDB(dbname). This makes sure nothing went/is wrong in the database. and it will updates the pages.
Maninder
www.dbanation.com
July 3, 2008 at 1:07 pm
Mani Singh (7/3/2008)
change your compatibility level to 90(SQL 2005) andAlways after and upgrade run the following statements.
DBCC UPDATEUSAGE (aspnetdb) and DBCC CHECKDB(dbname). This makes sure nothing went/is wrong in the database. and it will updates the pages.
I have a similar situation. I just attached the SQL2000 DB to SQL2005 and changed the compatibilty to 90. Thats all - and it seems to work, - but performance seems sluggish, would any of these DBCC commnands help? Once I've started changing data in the DB at level 90, would the upgrade wizard still work?
July 3, 2008 at 1:50 pm
Check out thebelow link
http://www.aspfree.com/c/a/MS-SQL-Server/Moving-Data-from-SQL-Server-2000-to-SQL-Server-2005/
http://www.sqlservercentral.com/articles/Administration/installingandupgradingtosqlserver2005/1943/
Thanks -- Vj
July 3, 2008 at 2:34 pm
Tom Brown (7/3/2008)
90. Thats all - and it seems to work, - but performance seems sluggish, would any of these DBCC commnands help?
Update statistics on all of your tables.
SQL 2005 keeps more detailed stats than SQL 2000 did. The 2005 optimiser can use the 2000 stats, but not very well.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 3, 2008 at 2:44 pm
Yes the DBCC commands are essential to check your Database integrity after the move. and the UPDATEUSAGE will help to rearrange the pages.
Also if Possible REINDEX and stastics as Gila pinted out before DBCC CHECKDB
Maninder
www.dbanation.com
July 3, 2008 at 4:30 pm
OK So I have a copy of the database in question on my laptop, and developer edition, so I tried first DBCC UPDATEUSAGE - this told me it had fixed loads of rowcounts.
I first tried DBCC CHECKDB WITH ESTIMATEONLY - and seing an estimate of only 111Mb I ran it. Well memory usage soared to 3.6GB (the maximum available to Windows XP 32-bit), 100% CPU and the system became unstable (keystrokes altered , mouse buttons reversed!). The command eventually finished with no errors, I couldn't scroll down in the results pane to see all the output. I had to reboot.
Well in for a penny... I tried sp_updatestats - it didn't have the dramatic effect of CHECKDB - but took quite a while - and reported nearly all indexes had statistics updated.
Eventually I tried my baseline reports again, and have about a 15% improvement. I'll reccommend these steps to our DBA for the production system.
July 3, 2008 at 11:59 pm
When you run checkdb, run it with no_infomsgs, unless you need to see the count of rows and pages per table (usually not), and note that it is a very intensive operation that should not be run when users are trying to access the system.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 4, 2008 at 12:01 am
Mani Singh (7/3/2008)
and the UPDATEUSAGE will help to rearrange the pages.
All update usage does is fix the metadata with regards to the size of tables. It doesn't rearrange anything.
2000 tended to get the recorded size wrong, 2005's a lot better. You should only need to run updateusage once, after the upgrade.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply