I have 3 sql server 2012 databases, located in different places (Miami, etc). They all have the same schema. The data gets updated/inserted/read frequently.The 'ids' are generated concatenating a constant value (depending of the server) + a value that is stored in a different table, something like this:

[Table_Name] -------- [Last_Value]

Customer  ---------------   594

Employee  ---------------   899

So if i want to insert a new customer in the Miami Db, which has a constant value of 100 and the last value for the last inserted record of that table would be 594 then the corresponding id will be 100594. This should avoids conflicts with other records from others dbs

What I want is to be able to keep this database synced. For example if a insert a record in db1, i should be able to see it in db2 and db3 in at least 5 min and vice versa

I have been reading that i could use peer to peer transactional replication, but the boss told me that they already did some form of replication in the past and had troubles with the synchronization, so probably this wouldn't be an option

So I don't know if there are other alternatives to keep this dbs synced, any help would be appreciated, thanks

  • If possible, I'd get more history on the old replication attempt. They may have been trying a different type of replication, or may have had configuration issues or something. That said, primarily look for what the exact problems were, so you can make sure that a new solution covers them. Even if they messed up the old replication solution and yours would work beautifully, it'll be difficult to convince them to do something too similar if the old failure was spectacular/expensive enough. – RDFozz Jun 8 '18 at 20:12
  • Your best seem to be transaction replication or migrating all three to a single cloud database on Azure. – Wes Jun 8 '18 at 20:31
  • @RDFozz i would try to see if i cant get more info. – Efrain Bastidas Berrios Jun 8 '18 at 20:41
  • @Wes when you say migrating all three to a single cloud database, you mean something like db1 syncs to a db on Azure, db2 and db3 does the same as well? – Efrain Bastidas Berrios Jun 8 '18 at 20:44
  • @EfrainBastidasBerrios That's one way. I was thinking that, perhaps, site to cloud VPN's to Azure to a SINGLE database... take all three off sight.. maybe more ambitious than Transnational Replication, but it's an option. – Wes Jun 8 '18 at 20:52

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.