MS SQL server: Single or multiple instances?

How costly (CPU or memory wise) is it to have multiple instances of SQL server 2005 instead of only one instance with prefixed databases?

A company have three application providers. They each will install one application and they each require two or three databases. Should they all use the same instance or should every provider use it's own named instance?

Is there any strong reason for one or other setup?

The only strong reason to install separate instances on same hardware is if you have very very stringent security isolation requirements. Otherwise is always better to have only one instance. One single instance can better optimize all hardware resources for the load you're facing. Multiple instances don't communicate to each other and they overlap the memory, I/O and CPU load resulting in worse performance.

Also a single instance is easier to administer, monitor and troubleshoot rather than separate instances.

If you break out your SQL Servers into individual instances (or perhaps separate virtual machines), the advantages are:

Better resource limiting. SQL 2008's Resource Governor is a good start, but still not that fine-grained, especially when it comes to limiting IO. With virtual servers, you can throttle CPU, memory and IO at the virtual machine level, thereby giving you the ability to limit resources even on older versions of SQL Server.

Easier performance upgrades and downgrades. If one virtual machine needs to be scaled up, like if its application suddenly becomes more popular, you can VMotion it to a more powerful machine without taking an outage. If you're using multiple instances, on the other hand, you're looking at a time-intensive and labor-intensive installation.

More flexible outage windows - if you have all of your databases on a single OS (multiple instances of SQL) then you have to do a lot of coordination to do Windows patches. If they're broken out onto different virtual guests, then you can do patching whenever it's most convenient for each individual guest (and its matching databases.)

Better security limits. If one SQL Server runs into problems and a third-party needs to get involved with troubleshooting, you can give them OS-level permissions without worrying about what they'll do to the other SQL Servers installed on the box.

Less problems with application compatibility. Some apps just aren't compatible with named instances of SQL Server.

It's not all unicorns and rainbows, though. Some drawbacks of the multiple-instance and/or virtual server approach include:

• Possibly more expensive - you have to pay for the virtualization layer, and depending on how some of your software is licensed, you may have to pay differently for it. Some utilities are licensed by the number of CPUs on the host machines, and not all of the CPUs may be allocated to SQL Server.
• Possibly tougher troubleshooting - some vendors like to point fingers at the virtualization layer.
• More OS management - every OS you add means more management and maintenance.

I did a webcast on consolidation vs virtualization with SQL Server experts Kevin Kline and Ron Talmage. Registration is required for that, though.

If you are looking to go this route, I would recommend separating the instances onto different hardware rather than running separate instances on the same machine. If each of these instances intends on getting hammered, I'd rather it all hammer one instance than hammer three instances on the same box with the added overhead involved keeping those instances up.