Friday, March 27, 2009
In my opinion easy and short answer: install one (1) instance and use SQL Server Standard edition 64bit with enough memory (at least 8GB) for SQL Server to run comfortably.
Why? with multiple instances on the same server you have to make assumptions about how much memory you configure to each instance, leading to a less optimal configuration.
Also multiple -housekeeping- tasks scheduled on the seperate SQL Agents have to be configured and monitored separately.
On a dedicated SQL Server I usually opt for giving all but 1-2GB of memory (dependent on the features installed like SSIS or Reporting Services) to Windows and all other memory to SQL Server and definitely go for a 64 bit (32=dinosaur) environment. So if you need additional memory to support more databases on the same instance it can easily be installed without the hassle of boot.ini switches and the performance overhead of AWE. (and AWE is only supported by the more expensive SQL Server Enterprise Edition)
Do not forget to set an upper limit of how much memory SQL Server can max. use, to leave enough room for other apps and services.
sample: on a 16GB server you can set a maximum of 14GB for SQL Server to use, leaving 2GB for the OS and other stuff.
(click to enlarge)
With the single instance approach together with Tools for performance and so reducing the workload you can have more databases on the same single instance server, leading to significant cost savings.