Horizontal scaling SQL Server 2008

Question

Is it possible to somehow horizontally scale an SQL Server 2008 database to allow for performance increase by adding extra servers to a cluster?

Thank you

Answer 1

It's possible to spread data across multiple instances horizontally, though it will likely be a headache to maintain. You can partition the data based on a common scheme. IE, a Person table might be partitioned by putting certain sections of names in different databases. For example one server takes of A-E, another takes F-I, etc. You'll then need a middle tier to determine which db to query. This is a rough approach to take when you need to query the entire database, as there is a lot to orchestrate.

If there is data that is not queried very often you can PARTITION the data. This is usually on a date basis and uses something referred to as a 'sliding window'. Where the last X days/months/years of data is in one table and the rest is in another table that rarely gets hit. (You could even put this other table in another DB if need be). This reduces the clutter in the table that gets hit often. A tutorial is here http://msdn.microsoft.com/en-us/library/aa964122%28SQL.90%29.aspx

Answer 2

Thanks a lot Mike, this is really helpful.