Are there any software packages that will keep two servers databases updated and syncronized with each other in real time? For example when a change is made on one server it is automatically changed on the other server and visa versa.
Thank you for your quick responses. We are using MSSQL 7.0 on both servers. I just gave it a quick read but will use transactional replication will the updates be instantaneous? Right now we have about 100 users hitting our main server what we want to do is have 50 users hit server 1 and the other 50 hitting server 2 and any updates or inserts be made on both servers. Is this the way to go?
Based on your original description of what you want, Merge Replicaiton is exactly what you need. You can use transactional replication with updating subscribers, but that can get ugly with the 2pc problems that it induces and the issues with RPC performance.
The short answer boils down to something like: Without knowing a lot about your setup (servers, network, clients, expectations, planned growth, etc) there is no way to give you a "rock solid" answer. Based on what you've expressed so far, Merge Replication would still be my suggestion.