Is it okay to run sql 2000 in production and sql 2005 in development? Please rush with thoughts

Is it okay to run sql 2000 in production and sql 2005 express edition in test?

A system was developed using sql 2000.  It currently runs in production using sql 2000.  We are in the
process of updgrading the system, and the development system database system does not match the production system.  Does anyone have any thoughts on this?

I am leary of this environment since it could cause unnecessary issues with compatiblity.  Keep in mind that we are not upgrading the stored procedures or tables to be sql 2005 compliant.  We are just going to copy the sql 2000 stored procedures and tables to the test environment.  Make changes and then move them back to production.

I appreciate any help with this.  I do not have a database background, but I have seen issues in the past where the production and development systems are not the same or similar.  It had caused issues that were very difficult to find to say the least time consuming.

Please rush with any assistance.
swansonplaceAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rboyd56Commented:
I would not recommend this. There are too many differences in the behavioral changes of SQL Server between SQL Server 2000 and SQL Server 2005. The optimizer most likely will handle the queries and procedures differently between versions. It would be best to have the development environment the same exact build as teh production environment.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Yes, if you make sure the db compatibility level remains on 8.0 (=2000), you should be ok with this solution. Minor differences are in syntax only, and can be resolved instantly, if they occur - they should not.
0
Kevin HillSr. SQL Server DBACommented:
Dev - maybe...you'll have options available that are not available to your prod system
Test - No.  For the reasons that rboyd56 mentioned
0
DocGyverCommented:
Depends on what you mean by "is it okay".
I would not recommend it for the reasons you specify and a few others that I'll list below.  Technically this can be made to work but you will have to be careful to do certain things and avoid certain things.

Do
Keep the database in 8.0 compatibility mode on the 2005 server.

Read the change document for 2005 especially as it pertains to extensions to TSQL and make sure anyone developing procedures does as well.  Anything listed as new should be carefully avoided.


Avoid
Trying to restore a backup made on the 2005 server to the 2000 server.  Even though the database is in 8.0 compatibility mode there are still differences that will keep a restore from working.  The only way to deploy from the 2005 server to the production 2000 box is with scripts.

DTS modifications.  If you are using DTS then you will have to make those changes directly to the 2000 machine.  Technically you could do this on the 2005 machine but with SSIS poised to replace DTS you are asking for trouble there.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.