April 28, 2009 at 2:12 am
Are there any reasons as to why you can't upgrade a database straight from SQL2000 to SQL2005. Is there anything you need to be careful about, redesign etc.
I ask as one of our suppliers has an application written in FoxPro 9 thats uses a SQL backend. When they upgraded to the last version they just converted a Fox database to a SQL database with minimal coding changes. This gave shocking performance, mulitple threads, and many errors which they slowly attempted to correct. I really don`t want to go back through the same problems if we can help it !!
April 28, 2009 at 2:41 am
Simon Smith (4/28/2009)
Are there any reasons as to why you can't upgrade a database straight from SQL2000 to SQL2005.
You can. I've done so on a number of occasions.
There are some things that you need to do after the upgrade.
DBCC UPDATEUSAGE because SQL 2000 was a little lax in keeping the space used metadata correct
Update all statistics because SQL 2005 keeps more detailed stats than 2000 did and while the optimiser can use SQL 2000 stats, it doesn't do so very well.
DBCC CheckDB with DataPurity because there were some forms of corruption that SQL 2000's checkDB ignored.
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
April 28, 2009 at 2:47 am
Thats great stuff. I`ll ensure that the relevant tasks get done. I have no belief that the suppliers would even understand what you have recommended !
April 28, 2009 at 2:56 am
Bear in mind that there have been changes to what's considered valid SQL in SQL 2005. If the app uses any of that it will break on SQL 2005.
Perhaps I should have said, there's no problems providing you've done sufficient testing and have fixed any deprecated and removed features.
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
April 28, 2009 at 3:00 am
I`m advising our mangement that upgrading is not the way forward as I don`t believe the application will work correctly. For example the concept of locking seems to have bypassed them and locking whole tables whilst reporting is common.
I could do without the hassle of setting up a test environment, testing, etc
April 28, 2009 at 3:12 am
Simon Smith (4/28/2009)
For example the concept of locking seems to have bypassed them and locking whole tables whilst reporting is common.
*twitch* Ouch
I could do without the hassle of setting up a test environment, testing, etc
If they do insist on upgrade, there's another thread discussing code changes - http://qa.sqlservercentral.com/Forums/Topic703774-361-1.aspx
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
April 28, 2009 at 3:15 am
Great stuff. Time for some reading before I start upsetting people.
Thanks again for your help.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply