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
153 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 50 total points
Comment Utility
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 68

Assisted Solution

by:Qlemo
Qlemo earned 50 total points
Comment Utility
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 50 total points
Comment Utility
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 350 total points
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Viewers will learn how the fundamental information of how to create a table.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now