Let`s consider a situation when you need to build a server with SQL Server used by multiple client applications. Here are two options to consider.
Single instance that host ALL the databases
Multiple instances with each having subset of the databases.
We`ve created a table with comparison of these two options.
| Single instance | Multiple instances |
Separate Security between databases and applications |
| Better |
Memory usage | Better |
|
TempDB disk usage | Better |
|
SLA differences |
| Better |
CPU usage | better |
|
IO | Better |
|
Ease of administration | Better |
|
Overall performance | Better |
|
Multiple test and dev environments |
| Better |
Cost of licensing | Cheaper |
|
Different SQL Server versions for backward compatibility |
| Better |
Demo purposes |
| Better |
Monitoring | Better |
|
Conclusion
From the table above we can see that having multiple instances of SQL Server on a single server could be beneficial only in a specific cases when you need a great degree of isolation for the environment, otherwise consider using single instance installation.
