2 Instances vs 2 DB's in SQL 2K

[Chris Spiess]

I have to agree with Tom. The production environment should be separated from the dev and test environments as much as possible, preferably on it's own box. I have seen developers do very odd and bad things to dev databases that spike memory or processor and ultimately affect the production system when things are on the same machine. At the very least, using separate instances gives you the option of shutting down the dev/test systems w/o affecting the production database.
Be aware, though, that each instance on a given machine requires a separate license.
"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message news:%23P%23zv5KPDHA.2768@tk2msftngp13.phx.gbl...
Well, this is an interesting situation. Since all instances are on the same server, they share all of the resources on that server and that includes memory. You do have the option of setting memory limits for each instance. You can also shut down each instance without affecting the others. My only concern is having dev and prod on the same box, since bad things can happen during test and if a developer crashes the server, then he's also crashed production.

I'd look at having prod on its own server and then go with 2 instances for dev and test on a separate box.

HTH

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
[url]www.pinnaclepublishing.com/sql[/url]


"London Developer" <dev@nowhere.com> wrote in message news:ui0fg0KPDHA.1720@TK2MSFTNGP11.phx.gbl...
Hi,

Currently, we have 3 databases on our (only) SQL Server. These reflect dev,
test & production.

I suggested creating separate SQL2K instances for these; in an attempt to
'totally' separate the environments. I thought this would make it easier to
perform backups, restores and releases if they were on separate servers.

Will we see a performance hit if I go ahead and create more instances on our
server? Or do the instances 'share' any memory and so don't take up 3 times
as much memory as our current single installation?

Any advice would be appreciated.

Thanks

[malcolm k]

I think the main issue is with releases ? You will need to ensure that
when moving from dev to test to prod, you can retain the environments
for each as required. No point in developing something moving to test
and then immediaitley overwriting with a dev mod !

You may wish to access data from, say a 'Master Database' and the dev on
the 'Operational Database' needs to change some Master Data. This would
be no good if testing is using it, worse still production.

Also what do you do about DTS packages under dev/test/prod ?

Better to have a way of completely isolating the systems. We have dev,
test, QA, Prod and make sure that the environments are identical. When a
dev finishes, the work moves to test and so on.

*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!