October 5, 2009 at 1:28 pm
I am the process of planning an upgrade of a DEV, TEST, and PROD SQL 2000 server environment to SQL 2008. My plan is to upgrade DEV first and then TEST a few weeks later and then PROD a few weeks after that. During this process, objects (Stored Procedures, etc.) will need to be moved up through the enviornment since I can't stop development.
Are there any know issues on moving objects from a SQL 2008 server to a SQL 2000 server. There must be some articles on this subject but couldn't find any.
Thanks for any help in advance.
October 5, 2009 at 3:53 pm
To get to 2005 start reading here:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/4760732b-aa3c-4f07-96ec-ba920476dd69.htm
Then you can proceed to:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/c10eeaa5-3d3c-49b4-a4bd-5dc4fb190142.htm
Go to the TechNet site:
http://technet.microsoft.com/en-us/default.aspx
and input: Backward compatability SQL 2005 for a rather large selection of references.
Repeat at Technet site for Backward compatabiliyt SQL Server 2008
Also remember you can move the DBs (in test environment) and test at various compatability levels
60 = SQL Server 6.0
65 = SQL Server 6.5
70 = SQL Server 7.0
80 = SQL Server 2000
90 = SQL Server 2005
100 = SQL Server 2008
And using sp_dbcomptlevel step by step bring the level up and test at each level - be carefull going back to a lower level is not supported.
Another approach is to start with the Upgrade Advissor
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/d85b0833-ddeb-42e3-9397-97ea60d521b7.htm
October 5, 2009 at 8:15 pm
You've got two choices. First, and probably best, you need to leave all your databases in 8.0 compatibility mode until the production upgrade is completed. That way, none of your developers can create objects or code that works on 2008, but not on 2000. The other option is to review each and every line of code before it goes out to ensure it works on 2000. That's going to be very difficult to do depending on the amount of changes you're dealing with.
A better approach might be to coordinate your 2008 upgardes with development deployments. Deploy to dev, get them to run a full regression test to validate that it's working. Then, the next time you deploy to the Test machine, upgrade it to 2008, do a full regression test... You get the drill, and move the structures up to 2008 along with the servers and the code.
Another option, the one we're taking, is to create a second set of 2008 servers. Get all the testing and migration done for a given application, capture any changes made to the 2000 server since the initial migration, then roll their entire process up to production. That way, as with you, we don't interrupt development too much.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 6, 2009 at 8:55 am
In the scripts section of this site, there's a good script on testing proc compatibility from 2000 to 2005/2008. It simply takes each proc and attempts to create and then drop a copy of it. It doesn't test functionality, but it does at least get you through the "does it compile" test very, very rapidly.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply