September 13, 2009 at 6:37 am
Hi,
We have move our current production databse from sql server 2000 to sql server 2005 by simply backup and restore process, we have not modified any stored procedure or user defined fucntion. Do you think this is best appraoch, are we getting the full advantage of new features presented in sql server 2005 and enhancement of database engines.
What changes should we perform in our code to get the best from sql server 2005 database engine.
thanks in advance
September 13, 2009 at 6:51 am
It's always a best practice to run upgrade advisor which lists all potential problems beforehand so that you can prepare well. Backup/restore is a good way to migrate databases. Once a database has been upgraded, there is no going back. you cannot go back to previous version (unless you hv a backup which was taken in 2000 database).
September 13, 2009 at 12:17 pm
I strongly suggest that you use Books On Line starting with:
Backward Compatibility
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/4760732b-aa3c-4f07-96ec-ba920476dd69.htm
Reading each subject listed on that page, paying particular attention to the fact that your can not utilize many of the features of 2005 without altering the compatibility level of what was your SQL 2000 db. Further strongly suggest doing this in a test environment, NOT in a production environment. When you fully understand and have corrected any problems encountered consider moving to production.
September 13, 2009 at 4:07 pm
I'm with bitbucket. Read about it, run the Upgrade Advisor, and then restore to a test environment and test to be sure all your code will still work.
September 14, 2009 at 7:34 am
Make sure you update the stats after the restore/upgrade.
September 14, 2009 at 3:27 pm
There are dozens of new features available in SQL Server 2005 vs 2000. It is really too broad of a question to answer adequately on here with out a lot more detail & background. As several other have said, running the Upgrade Advisor is a great start to identify any code from your 2000 database that may not work in 2005. Run the advisor, address the issues and test everything in a test environment before you do anything to your production database. That would be the bare minimum.
Whether or not it would be a good time to modify/optimize any existing procedures would depend on several things:
- Are you having performance issues?
- Do you expect the workload to grow?
- Is the database meeting the needs of the application / business adequately?
- Is the code / database secure?
- Is there good error handling in the code?
- .... and a lot more
Another point to consider, it is possible to go from 2000 straight to 2008 and skip over 2005. Is there a reason why you want to upgrade to 2005 and not 2008?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply