?
Solved

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

Posted on 2007-04-04
4
Medium Priority
?
169 Views
Last Modified: 2013-11-27
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.
0
Comment
Question by:swansonplace
4 Comments
 
LVL 16

Assisted Solution

by:rboyd56
rboyd56 earned 200 total points
ID: 18850641
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
 
LVL 71

Assisted Solution

by:Qlemo
Qlemo earned 200 total points
ID: 18850650
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
 
LVL 21

Assisted Solution

by:Kevin3NF
Kevin3NF earned 200 total points
ID: 18850682
Dev - maybe...you'll have options available that are not available to your prod system
Test - No.  For the reasons that rboyd56 mentioned
0
 
LVL 6

Accepted Solution

by:
DocGyver earned 1400 total points
ID: 18850720
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question