production or test server...or both

Hi, We currently have SQL2000 running in a production environment with 2 commercial applications. I would like to use the same server to upsize some access applications, test and develope new internal applications. I have been warned by our IS dept, however, that I should not have any test applications on a production server as I run the risk of closing down/screwing up the performance the server (looping queries...etc) through the test applications.
They say I should buy another server, set alone as a test server, and only add the internal applications to the production server after thorough testing.
Of course , our IS dept offers no test or production server for our dept to use - so this gets pretty expensive for us.
My question - can I do both on one server or not?
thanks!
JeffIMHO I agree with your IS dept 100%. Now if your Production box has ample resources and your Production apps can stand some interference or down time due to a screw ups then why not.

BIG DISCALMER!! I can guarantee you that at some point someone will run something that will drag the system down or even (gulp) bring the server down. Just like your IS guys have pointed out.

In some shops the above would be unacceptable, maybe not in yours. You sort of have to way all the pros and cons. Would a low end server really cost that much considering the potential problem?|||RE: They say I should buy another server, set alone as a test server, and only add the internal applications to the production server after thorough testing. Of course , our IS dept offers no test or production server for our dept to use - so this gets pretty expensive for us.

Q1 Why not simply buy and use a low end workstation pc (to lower the costs install Win 2k Pro or XP) with the dev version (or even the 120 day eval) of Sql Server. One ought to be able to easily create that kind of test server for under $1K.

Q2 If $1K is too much, how about making some poor user's workstation (say yours) a dual boot OS box (install the test platform on the new install)? If you have test workstations and you are the one doing the testing you wouldn't need your workstation while you are doing tests anyway, or would you?|||Be very careful of installing evaluation editions of anything by microsoft. I did this with sql server version 7 and had to reinstall which destroyed everything - according to documentation ms has gotten better - but do so at your own risk. Anyway, a better solution is to use msde 1 or 2000 depending on what your solution requires. The following is a link that tells you how to get msde ...

link (http://support.microsoft.com/default.aspx?scid=KB;en-us;324998&)|||Oh - to answer your question - of course you can install anything on the production server - the question is should you. Some people will debate that it is ok to have a test instance with a production instance. However, you can have so many issues with this design. IMO - You use the production for production only - no matter how well stacked your machine is - I have seen enough problems (like memory leaks, over enthusiastic sql statements ...) - Just get a cheapy peepy machine and install want you need - find free software or existing licenses so you have no/minimal costs with the software part.|||RE: Be very careful of installing evaluation editions of anything by microsoft. I did this with sql server version 7 and had to reinstall which destroyed everything - according to documentation ms has gotten better - but do so at your own risk.

Note: The low cost possibility (using a 120 day eval edition) was explicitly made in reference to use on a test server (as a means to further lower total test server costs).

RE: Anyway, a better solution is to use msde 1 or 2000 depending on what your solution requires.

NOTE: MSDE (and Desktop Sql Server editions) do have some intentional limitations built in. As long as you do not need to test any non-MSDE functionality, e.g. (DBs <= 2GB), MSDE is a good general functionality test environment (otherwise, the Sql Server dev. version is often "best", at a fairly reasonable cost). Accordingly, unless specifically deploying to MSDE, avoid using MSDE for any scalability / load testing purposes (or as a substitute for valid Sql Server product performance comparisons).|||MSDE is fully compatible with sql server - so once you have the money to buy that developers edition - no problems mate. Obviously, if you have the $500, then buy the developers edition. Even further - here is a quote from microsoft regarding msde:

"An attractive alternative to using the Microsoft Jet database, MSDE 2000 is designed primarily to provide a low-cost option for developers who need a database server that can be easily distributed and installed with a value-added business solution. Because it is fully compatible with other editions of SQL Server, developers can easily target both SQL Server and MSDE 2000 with the same core code base. This provides a seamless upgrade path from MSDE 2000 to SQL Server if an application grows beyond the storage and scalability limits of MSDE 2000."

MSDE is free without the headaches of the evaluation edition - a no brainer.

As far as the limitations - for the purpose of upsizing from access - this is no problem - even with the size limitation, since it is the same as access. The following article goes into depth about this:

article (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acmsdeop.asp)|||MSDE makes for a good general functionality test environment.

MSDE also often serves a variety of production purposes very well, (even though MSDE does have intentional limitations built in). However, MSDE is a licensed use product with licensed use requirements.

Some information (from MS) on some MSDE limitations: MSDE was TUNED { - "TUNED" may be regarded as a synonym for "CONSTRAINED" in this case - } to deliver the same performance you'd get using the SQL Server enterprise engine AT UP TO FIVE CONCURRENT USERS, and supports up to 2 gigabytes (GB) of data for any individual database. The storage limit is per database, not per server. A single MSDE server can support multiple MSDE databases, each containing up to the 2-GB limit. For more users, or when you need more data storage, you should use SQL Server or SQL Server Enterprise Edition for optimal performance at this higher level of scalability.

Unlike SQL Server, MSDE can't be a replication publisher (although it can act as a replication subscriber) in a transactional replication environment. Also, note that replication of MSDE databases is only possible if SQL Server Client Access Licenses (CALs) are purchased. If you require replication publishing, you'll want to use SQL Server Desktop rather than MSDE. (A CAL is also required for SQL Server Desktop.)


Some information (from MS) on products that permit licensed "free" use of MSDE:

Products that enable use and redistribution of MSDE:
SQL Server 2000 (Developer, Standard, and Enterprise Editions)
Visual Studio .NET (Architect, Developer, and Professional Editions)
ASP.NET Web Matrix Tool
Office XP Developer Edition
MSDN Universal and Enterprise subscriptions

Products that enable use but not redistribution:
Microsoft Access

Some other possible considerations: (see http://www.microsoft.com/sql/howtobuy/msdeuse.asp)
Using MSDE does not reduce or eliminate the need for CALs when interacting with SQL Server 2000 Standard Edition or SQL Server 2000 Enterprise Edition in a production environment.

Q. Can I use SQL Server tools and services in conjunction with MSDE?
A. You can only use SQL Server tools and services in conjunction with MSDE if you acquired MSDE via SQL Server 2000 (any edition) or if you are using MSDE in conjunction with a properly licensed copy of SQL Server 2000. Visit the How to Buy page to obtain a valid SQL Server license.

The following utilities are installed by the MSDE setup application and are provided without restrictions for use with the copy of MSDE that is installed by your application: bcp.exe, cnfgsvr.exe, dcomscm.exe, osql.exe, sqlmangr.exe, scm.exe, sqladhlp.exe, and svrnetcn.exe. The dtsrun.exe utility is also provided, but may not be used during development.

Note: The following Visual Studio family of products also include the right to use and redistribute MSDE in this scenario: Microsoft Visual Basic .NET, Visual C# .NET, Visual C++ .NET, and Visual J# .NET.

Several Microsoft product licenses convey the right to use and redistribute the Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) version 2.0. In all cases, you should refer to the End User License Agreement (EULA) for a full statement of the rights conveyed by your product license.