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
158 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
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 69

Assisted Solution

by:Qlemo
Qlemo earned 50 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 50 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 350 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

832 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