Hello,

Is the multiple instances capability of SQL2K mature enough for a
Production system?

We're looking at upgrading our hardware but the proposed solution
calls for consolidating two currently separate SQL Server's onto one
machine with two CPU's.

Of the current two servers, one is for OLTP (~800Mb) and the other for
reporting (some ad-hoc, some canned queries, 2-3Gb). We purge the OLTP
db nightly and archive the purged data in the reporting server where
it stays for a couple of months before getting purged from there.

Maybe I'm being overly cautious here but the OLTP system is especially
time-sensitive. If anything causes it to slow down significantly we
lose $$$.


Thanks,

[quoted text, click to view]

I must admit that I've never used multiple instances for production,
but I would also be wary of this proposed approach. The two instances
will always be competing for system resources at some level - while
you can assign each to a CPU, and also limit the memory avilable to
each instance, if there is a usage 'spike' in the OLTP system, it may
be that there are no spare system resources to respond. In addition,
if the two instances use the same I/O subsystem, then long-running
queries on the OLAP side may slow down OLTP access to the disks.

Personally, I see the value of instances in situations where you need
to test something like a new replication topology but you don't have a
whole bunch of physical servers. In this case, multiple instances are
a good way to verify that the functionality works, although it's not
easy to test performance in a meaningful way without an accurate
physical system.

In any event, you should really test the proposed configuration to get
a proper answer, and especially under stress or limit conditions.