Lebanese (7/13/2011)
Site1 has 90% of the users so i what i was thinking is sql1 would take all the requests from site1 and site2.If sql1 is down, all the requests will go to sql2.
Peer-to-Peer, as i understood it, can replicate changes both ways, so if sql1 was taking all the requests, all the changes will be replicated to sql2. if sql1 went down, sql2 will be taking all the requests and once sql1 comes back up, sql1 will start taking all the requests and all the changes that happened on sql2 will be replicated to sql1.
now, would that work with a windows cluster where i can specify that sql1 server is the primary and sql2 server is the backup?
What you are specifying maybe be supportable by clustering but seems to be built on a misunderstanding of the way clustering works. Keep in mind that a cluster involves 2 (or more) nodes and SQL instance(s). However 1 or 10 instances they all work the same. An instance of SQL runs on a SINGLE node, ALWAYS, should that node become unavailable a failover occurs and that instance of SQL is started on another available node, still just one node. Same name, same ip, same databases, same SQL Server. Clients only see a brief disruption in connections.
You certainly could have a second instance on one of the other nodes that you are replicating between, but WHY?
If your desire is for load balancing then clustering is not really needed, if you are looking for high availability then P-2-P replication is not needed. So the real question is what are you trying to accomplish? And don't approach it from what solution to use, but what you need to accomplish and then we can work a solution.
CEWII