We have five SQL servers running on five different physical servers. What are the downfalls for combining these SQL servers together onto a single physical server? Also, is it possible to run multiple instances of SQL Server on the same server?
The answer to this question depends on if the single physical server has the hardware capacity to run the five databases. If each of the five databases are lightly used, and if the single physical server can easily handle all five databases, then there is no problem doing what you want. But if one or more of the five databases is very heavily used (perhaps suffering from performance bottlenecks), then you may find out that locating all five of the databases on the same server may cause significant performance problems.
The only way to know for sure if combining all five database onto a single physical server is to use Performance Monitor to track the performance of each database, then evaluate if they all could be handled by one physical server. You might want to consider consolidating some of the servers, but not all. Perhaps two or three physical servers will handle your needs. Since I don’t know much about your server’s capacity or how busy they are, I can’t offer specific advice.
Another thing to keep in mind is that if you do combine all of the databases onto a single physical server, that if that one server fails, then all five databases are down. But if you have a database on each server, then only one database will be down, and the other four will still be up.
On the other hand, it generally takes less work to administer one physical server than five servers. So there are no easy answers to your question.
As a rule of thumb where I work, I put mission critical databases on their own clustered servers, and for less critical databases (assuming they are not heavily used), I generally combine them onto a single clustered server.
When it comes to SQL Server 2000 or 2005, I don’t recommend that you use multiple instances of SQL Server on the same physical server for production databases. Test or development databases are ok, but that’s it. The problem with multiple instances is that each instance takes up its own resources, resources that can’t be shared. This means that performance can be significantly harmed if you run multiple instances on the same physical server. For best performance, only run a single instance of SQL Server on a physical server that is dedicated solely to SQL Server.]]>