The software company I work for is planning to migrating our infrastructure into the cloud. Our current focus is to find the best database replication method.
Current Servers:
SQL Server 2008 SE – Located in NY (In house – Master)
SQL Server 2012 SE – Located in Chicago (Rackspace – Slave)
We have been considering log shipping, but we are also aware of mirroring and replication.
Can someone please provide some insight on what our best approach for this task may be? We simply want to keep both databases in sync with each other. Real time does not matter yet, but it will in the near future.
Note: Our long term goal is to make Rackspace our primary, and NY our secondary.
Best Answer
In my opinion, log shipping has those advantages:
I understand the direction is only one-way (master to slave). Replication is a complex solution better suited to two-way data replication or replication to multiple sources.
Log shipping doesn't need a special design of the database (e.g. primary keys as GUID's and not as autoincrement integers).
Log shipping is a less drain on resources, is easier to set up and maintain.
As the question is quite broad and opinion based, I can't give you an ultimate solution. This is just a bit of advice that you may hopefully find helpful.