Trying to determine the best way to handle database "replication" to a colo for disaster recovery as well as database access to remote offices.
Currently I have a production SQL server 2008 R2 and have log shipping setup in the same server room on a different identical server for redundancy. The databases are of course in read only mode and of course I'd need to recover them from read only mode to full access in case of emergency for local apps.
However, with business growing and databases growing as well, I've been asked to look into replicating 3 databases to a colo and maybe to remote offices. To colo, because of disaster recovery and access to web apps. In case our redundant ISPs are down, clients still would be able to access our site and database from the Colo location.
Also we do have remote offices where they're going to be running local apps and write to local databases and replicate the data in both directions, from main offices to remote offices vice-versa.
I have looked into all the methods that MS SQL server offers and I think Merge Replication is what fits to our need.
I just wanted to see if anyone has any input or any recommendation. Main important thing is to have our local databases replicated outside our local server room and be able to write and read from any location and sync the data.
Many thanks in advance.