Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Replication (2005)  Replication with HA

Author  Topic  daunce Starting Member 11 Posts Posted - 2008-08-27 : 20:14:21 Hi, We're looking to see if it's possible to build the 'ultimate' SQL server. To have it load balanced, and high availability. We are looking at using NLB and peer to peer transaction replication. (2 servers: windows 2003 EE R2 with sql server 2005 EE). The goal is to have it so we can have a server down due to failure or maintenance, and still have the DB's working, with little or no intervention from us. That's why we like the idea of using NLB with 1 hostname for clients to connect to. The idea would be to migrate our old DB's onto this new solution with little or no changes. But with p2p transaction replication there's a problem with Identity columns, so i'm not sure how easy that would be. I'm also worried about the latency between DB updates. 1) Is this solution viable ? 2) How can we work around the fact that every DB we'll import will be using Identity columns? (Seeding each entry, per node, or inserting a new column for Server1/Server2, makes the DB's different, so what happens when you have to restore the DB? And inserting a Server1/Server2 entry, would that be done by the DB itself, because the client wouldn't know which DB it's connecting to). 3) How can we minimise the risk of updates on both DB's causing a conflict? 4) Is there perhaps a better solution? 5) Is this how large companies run their DB's? (assuming they use MS SQL) 6) Does SQL Server 2008 provide anthing that may help in this situation? We are looking at something similar to http://technet.microsoft.com/en-us/magazine/cc160974.aspx Using MSCS isn't technically possible at this point in time. Any advice much appreciated.