I've got a question regarding database replication. Let me describe my problem space first. I am sure it's a common problem that many company would run into.
We've built an application that has been running in 2 factories situated in 2 different cities. It has been decided that we need to merge these data together for reporting and tracking purposes. The database structure supporting this application is rather complex, thus we've plan to do this in 2 phases. Might do it in one phase if you guys can suggest reasonable solutions.
Eventually the data from the 2 isolated system will have to be merged somehow, probably into a third database. But for starters, we want to makes sure that we have a copy of each database at the 2 locations. To do this I was thinking about using Transaction Log, after a bit of research, I've realized that I can use database replication tools provide by SQL server.
We plan to have data replicated at the end of each night, now because the database is 800MB (the reason why I wanted to use log shipping initially) it's not feasible to transfer all 800MB of data each night. After reading a bit on replication. I think my options are either a merge replication or a transactional replication.
I am noobie on database replication. Do you guys have any opinion as to how I could go about replicating data from one location to another transfering only the most updated data. Are there better alternatives to SQL replication for my case?