| Author |
Topic |
|
deonvisser
Starting Member
16 Posts |
 Posted - 2012-06-15 : 23:32:26
|
| HiI need to synchronize two databases, over a wan, realtime. The problem is that the schema changes daily (new tables etc get add constantly). 1. The way I see it, with replication, you have to manually add new articles and then recreate a snapshot. Is there a way to automatically add new articles to merge replication and also not have to recreate a snapshot?2. Would it perhaps be better to use 3rd party Data/Schema Compare and update tools to keep the databases in sync?3. Any other suggestions on how to automate the synchronization?4. My other concern is the performance due to the fact that this must be done over a wan with a 500ms ping latency.Does anyone have any suggestions or currently doing something similar?Thank you in advanceDeon |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-06-15 : 23:41:36
|
| Do you need to write to both databases independently? If one side is considered the "master", you could also use log shipping instead of replication. You can set the secondary to be in standby mode so it can be readable too. It is also fully automated but not realtime, you'd have latency of approximately 5 minutes.Database mirroring does support realtime synchronization but would suffer severe performance degradation with the latency you describe. The mirror also can't be written to or read, it's only intended for availability. |
 |
|
|
deonvisser
Starting Member
16 Posts |
Posted - 2012-06-18 : 03:00:05
|
| Thank you for your reply.Both Databases will be updated from independently from a data perspective and need to be merged. I considered log shipping, but dont think it will work in this scenario. Schema updates will only happen from the publisher and then pushed to the subscribers.Redgate, Delta and a few other vendors offer Compare products which compare two databases and then update them from both a schema as well as data point of view. Have you had any experience with this?RegardsDeon |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-06-18 : 07:53:49
|
| I've used the Redgate schema compare tool a lot, but I don't know of any products that offer realtime comparison and sync. You'll have to look at Merge or Peer-to-Peer replication in SQL Server. Other third party products may do the same but I'm not familiar with them. |
|
|
|
deonvisser
Starting Member
16 Posts |
Posted - 2012-06-18 : 10:13:00
|
| Thank youMerge replication may be the way to go. My question is, would I need to create a brand new snapshot every time I add new articles for merge replication or would any new articles added, automatically replicate?RegardsDeon |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-06-18 : 10:45:54
|
| I don't think so, but I've never used merge replication. It's thoroughly documented in Books Online. The replication stored procedures are also thoroughly documented and will typically warn you if something needs to be re-snapped. |
|
|
|
deonvisser
Starting Member
16 Posts |
Posted - 2012-06-18 : 11:05:58
|
| Thank you for your replay and assistance. I have performed merge before but cannot conclusively say that new articles get replicated automatically. It seems that sometimes it does and sometimes it does not?I will take your advise and have a look at BOL.Thanks againRegardsDeon |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-06-18 : 11:06:41
|
| You might want to look at another technology if you want to do this. This is definitely not something that sql server (or any relational database I've used) does wellDepending on your use case you may get a lot less pain with a datastore designed for independent merges.CouchDB is built around the concept for example. It supports Master - Master models which sound like your use case.....http://couchdb.apache.org/Transact CharlieMsg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. |
|
|
|
deonvisser
Starting Member
16 Posts |
Posted - 2012-06-18 : 11:14:40
|
| HiFYIAfter adding an article to a publication, you must create a new snapshot for the publication (and all partitions if it is a merge publication with parameterized filters). The Distribution Agent or Merge Agent then copies the schema and data for the new article to the Subscriber (it does not reinitialize the entire publication).It seems that you need to add parameters to the snapshot creation process in order for the snapshot to only initialize the new articles and not the whole database. This maybe the solution I needs, although I am still going to have manually add the new articles for replication. Not ideal, but will have to do for now. |
|
|
|
deonvisser
Starting Member
16 Posts |
Posted - 2012-06-18 : 23:51:49
|
| Thanks Transact CharlieI had a look at the URL you provided. Very interesting. My problem is that the project must be completed before the end of the month and secondly, this is a mature database and application which would mean data conversion. Last, huge amount money was already invested in SQL. Thank you. This is a technology I will definitely investigate for future projects, but cannot take the risk for transitioning a mission critical db to this platform at this stage. I need a solution for SQL.RegardsDeon |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-06-19 : 04:14:33
|
| Hi Deonvisser.Those are all fair points.What about if we looked at your initial statement:"I need to synchronize two databases, over a wan, realtime. The problem is that the schema changes daily (new tables etc get add constantly). "Why do new schema objects appear all the time? Is this maybe a weakness of the design. Is each new table it's own entity?What does the application (or whatever is using the db) do to make new tables etc? Is it an ERP system or similar?Transact CharlieMsg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. |
|
|
|
deonvisser
Starting Member
16 Posts |
Posted - 2012-07-02 : 13:03:44
|
| HiThank you for your reply. My apologies for the delay. No notifications? The application is a growing app. New modules are added constantly which require new tables etc. These are created by the development team. You could say it is an evolving ERP system.I have managed to establish that you can in actual fact add new schemas without needing to recreate the snapshot.RegardsDeon |
|
|
|
|
Best option for schema and data sync