scantoria (10/8/2014)
So what is the approach? Keep working with the 8 servers and just add more memory (RAM and HDD) when needed?We have over 100 databases across these servers. I am looking for best architectural setup for this kind of scenario.
You may not be able to put everything on ONE server, but you shold be able to reduce the number of server you manage.
You can identify the databases that do not generate a heavy load and do not depend on other databases (e.g stored procs referencing tables in other databases) and consolidate those on a single server.
Then come up with a strategy for the heavy hitters. It will be easier to analyze once the noise is reduced.
Do you have applications that have peak loads at different times of day? Then those could be combined on a server...