I have a reasonable server connected to a SAN which will be running SQL servers for multiples of the same application. There are no security issues with one application being able to read anothers database.
We are unfortunately in 32 bit windows as well.
I'm of the opinion that it would be better to use one instance on the server, enable AWE so that the server instance can use almost all of the ram we have and then run each of the databases in the one instance.
However I've been overruled by the gods of the IT department on this one, so I'm really curious to hear your thoughts on this. From a performance point of view, am I incorrect that one instance of SQL is better than two?
I know that we could do some failover stuff, but doing that on one blade only seems like overkill to me.
Best Answer
SQL Server 2000 and 2005 Workgroup and Standard (32 bit, I'm not sure about 64 bit) will only use a maximum of 2GB memory so having two instances would allow you to use the full 4GB. This would be true even if you're running 32 bit SQL Standard on x64 Windows, more so in fact since you'd want an instance per 2GB of memory.
In principle using a single instance with two databases is faster than two instances with one database each, though I'm unconvinced it's a huge difference. Having separate instances can be useful for management. For example if you use SQL logins, and there are different sets of logins for different databases, using separate instances can make it easier to keep track of logins.
So the answer to your question is "It depends" :-)
JR
Re Spence's comment: SQL Server Standard on 32 bit Windows can use a maximum of 2GB memory. SQL Server Enterprise can use 3GB if you use the /3GB switch. On Windows 2003 Enterprise with AWE up to at least 16GB memory can be used (possibly more) so you can get better value out of your memory by running more than one instance of SQL Server.
I'm afraid the answer is still "it depends". If you have lots of memory, i.e. 8GB or 16GB, then you want to put the biggest databases in separate instances so they can have 2GB (or 3GB with /3GB) each. If you only have 4GB then I'd probably use a single instance and the /3GB switch as the small amount of extra memory used by having two instances wouldn't be worth the overhead.
As others have commented below there can be other considerations. If you reference two databases at the same time, e.g. in a select query or if you insert from one database into another, then you want them in the same instance for speed.