Assuming you have the budget to spend, you will get better performance by “scaling out” your SQL Servers onto multiple SQL Server servers than running them all on the same physical server. The reason why this is true is obvious to DBAs, but trying to justify this extra cost to managers or accountants is not always easy. Here’s some ammunition you can use to help build your case for running multiple SQL Servers instead of one real big SQL Server.
Benefits of Using Multiple Smaller SQL Servers Instead of One Large SQL Server
- In fact, purchasing several smaller SQL Servers may be less expensive than buying one huge SQL Server, although this may not seem logical to many people not familiar with server hardware. Generally, smaller servers can be purchased at commodity prices, while very large servers are special orders and have a cost premium associated with them.
- In many cases, your current, older SQL Server may not be upgradeable, or if it is, it may not be cost-effective to upgrade it as compared to purchasing new physical servers.
- As physical servers get larger (more CPUs), there is more and more CPU overhead generated, which in effect reduces the overall performance of the server. Each additional CPU adds a decreasing amount of additional CPU power. Two 4-CPU servers are more efficient from a CPU perspective than a single 8-CPU server is.
- Some databases need to be tuned differently than other databases. If all of your databases are located on a single server, then you can’t take advantage of SQL Server-wide performance tuning techniques to tune each separate database, as each database will have to share all SQL Server-wide performance tuning settings.
- If the single, large server goes down, then all the databases go down. If the databases are on separate servers, then only one database goes down.
- Most SQL Server-based applications grow over time. If each database is located on its own server, then it is easier to add incremental hardware (such as RAM) to the servers that need it. On a large server, you may find that you can’t expand beyond a certain point.
- Some databases require a different sort order than others.
Of course, there are no easy answers. Where I work, critical databases are given their own individual servers. But for less critical database, we often share the same server, especially if they are small, not used much, or are used only for testing purposes.
]]>