We are trying to increase our redundancy and would like to use SQL Server replication to do this. We have about 5 database servers that contain around 1000 databases. Some of these databases are used more often than others. We are a complete Windows shop.
The idea is to create a new big server that can hold all of the databases and have these be slaves of the databases on the 5 other machines. So all data will be replicated over to this new server. If we loose one of the 5 servers our application will immediately switch over and start using the slave version of the database until we can get the other server back up and running. Our application is a mostly read with small updates (occasionally large updates) Does any know if this will work? Are there better ways of accomplishing our goals of redundancy? Are there any links to articles about this?