Sync Data Between Two Diff SQL Servers

Question

I have two SQL servers that I need to have identical. Sync should occur maybe a couple times per day, automatically. The two servers have no "direct" connection between them, so no linked db etc. (They are on totally diff machines, networks, domains, with firewalls between etc) You can use the Internet though, like Web Service, FTP...

CopyFromDB holds a lot of tables, but only about 20 tables (data only, no schema changes for now) should be synched to CopyToDB. Only about half of the tables will have new/updated rows every day. The others a couple of times per year. I'm guessing it's only about a couple of thousand rows per day right now. This could however grow in the future, to about 100,000 rows a day. So maybe not too much data I guess.

And also (for now, might change though) the data should only be synced one way, from CopyFromDB to CopyToDB. It would also be nice if it doesn't "break" just because somebody adds an extra column on a table on the CopyFromDB, but that new col shouldn't be synced to the CopyToDB automatically. (Only the cols that should get synced reside on the CopyToDB, there might be other columns that should not be synced)

It should do Insert, Update or Insert. In worst case I guess it could to a total Delete and Insert, but I don't like this. (There are of-course FK etc so the data has to be inserted in the correct order)

For now there is only one "CopyToDB", but this might increase. The solution should also be quite easy and not too complicated. :-)

My question is; what would be the best way to accomplish this? I have a couple of ideas below.

• WS on CopyToDB side, called from the CopyFromDB-side by a service or something? Pushing the data, only the changes since last sync.
• FTP on the CopyToDB side that takes an "sql"-file with the changes. Like Insert/Update/Delete. This is pushed there by the CopyFromDB.
• Something else? Built in tool that already does this? Or a 3d part tool? Something like Red Gate SQL Data Compare, only automatically.

Thank you for your thoughts and/or answers!

---

I have searched the forum for my question. But I can only find questions where the two servers have some kind of "direct" connection between them or you do a backup/restore. But if the question already has been answered, I beg you pardon, and could you please tell me where? :)

Answer 1

You can use FTP delivery with SQL Server replication: How to: Deliver a Snapshot Through FTP (Replication Transact-SQL Programming)

Answer 2

Write Web Service for it and use utility like TableDiff for data & OpenDBDiff for schema. There is no other simpler way.

Answer 3

If the replication is going one-way, use log shipping. The primary server can put the log backups on any UNC path, and then you can use any file sync tools to manage getting the logs from one server to another. The subscriber just automatically restores any transaction log backups it finds in its local folder.

The subscriber will be read-only, but that's exactly what you want - otherwise, if someone can update records on the subscriber, you're going to be in a world of hurt.

Answer 4

If you have access to Visual Studio DataBase Projects, you can set up a build task (if you are using a build system) that can generate T-SQL diffs. Then, it's a matter of shipping the diffs over and applying them, but this a much easier task (WCF, email, whatever).

The only trick is to make sure that your development/modified database and target database have the same schema when you start this process.

However, if you can use a replication technology, I would highly recommend that. While rolling your own solution is possible, the management of it will be a nightmare in the future. Getting it working now isn't hard. Making sure it keeps working for the next XX years is.

Erick