January 16, 2007 at 1:34 pm
What's the prerequisite for upgrading from SQL 2000 to SQL 2005?
January 16, 2007 at 10:55 pm
Could you expand more on your question? Are you asking for the prerequisites for installing 2005 on a new box or are you asking about migrating databases from sql 2000 to 2005?
If the later then you want to run the SQL Server 2005 Upgrade Advisor which will advise you on steps you need to take before migration of the database.
I have been migrating databases from 2000 to 2005 for the past 4 months, so if you could expand on your question then I'll try and help.
Thanks
-Dave
January 17, 2007 at 5:43 am
One thing to be wary of when moving databases from SQL 2000 to 2005 is that, if you want to run in full 2005 compatibility mode (level 90) then you may break some of your current stored procedures, as SQL 2005 is stricter about certain things, and neither the database engine nor the Upgrade Advisor will tell you what is affected.
If this possibility is ignored then the testers and/or (shuddder) end users will have to find the issues by trial and error.
To ensure that everything works you need to script out your SQL 2000 database and rebuild it under SQL 2005 - this will expose the stored procedures that no longer compile and you can then implement fixes for them. Therefore, the process you need to follow would be:
1. Script out all objects in the the SQL 2000 database
2. Rebuild the database under a level 90 SQL 2005 instance and note what doesn't compile
3. Fix the issues and rebuild until everything works ok (keep the scripts for the fixed sprocs in a separate folder)
4. Backupo your SQL 2000 database and restore in to the SQL 2005 instance
5. Run all the fixed sprocs against the restored database
This procedure is the most efficient way to ensure that your database isn't broken by the move to 2005.
If you would like to see some tools that can make this a breeze then check out DB Ghost (http://www.dbghost.com).
Malcolm
DB Ghost - Build, compare and synchronize from source control = Database Change Management for SQL Server
www.dbghost.com
January 17, 2007 at 6:40 am
You're saying "you may break some of your current stored procedures, as SQL 2005 is stricter about certain things,". Can you give some examples of what breaks?
January 17, 2007 at 7:44 am
There are some things on MSDN regarding breaking changes here. However, there are clearly some changes that have not been published or have been overlooked (have a look at the link in my above post for the Australian Government's experience)
Which strategy do you think is likely to ensure a successful outcome:
A. Use the Upgrade Advisor alone
B. Use the Upgrade Advisor and also rebuild your database from scratch?
The UA will highlight many issues that would only be discovered at run-time but it also misses some issues that can be caught at compile time. Personally, as the database is normally the bedrock for most applications I would go for a "belt and braces" approach every time, just to be sure. But, then again, I am biased towards proper change management procedures for SQL code and solid databases as it is what I do every day 😉
Malcolm
DB Ghost - Build, compare and synchronize from source control = Database Change Management for SQL Server
www.dbghost.com
January 17, 2007 at 8:14 am
There is a "compatability level" setting for each databases (master.sysdatabases.cmptlevel, sp_helpdb). This value is set at 80 for databases migrated from 2000 to 2005, and 90 for databases built on 2005. This value can also be manually set to 2005 compatibility with sp_dbcmptlevel.
Upgraded databases are set to 2000 level becuase there are changes in 2005 that effect behavior. For example, some non-ansi syntax (such as *=) is no longer supported. If your procedures include syntax that is no longer supported, they will not compile in a 2005 instance at 2005 compatibility, but they will compile in a 2005 instance with the database set to 2000 (80) compatibility.
The company I am currently at upgraded 2000+ databases from 2000 to 2005 with virtually no issues because they spent a reasonable amount of time preparing.
Spend a few days researching the changes in 2005, look for items in your database and application (use profiler) that may be effected, port one or two databases to 2005 as a test. Depending on the complexity of your environment, the hardest part will likely be coordinating db changes and app changes
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply