December 7, 2016 at 3:16 am
I'm trying to find a solution to a scenario i have. Hopefully it comes across clear as its hard to explain in writing.
We currently have a OLTP prod database within Australia that is used by all our clients. We have just got a new client in another country. This client connects to its own database we created for reading data. no writing is required on this database as its mainly a datawarehouse type DB.
However for 1 part of the system the overseas client has to enters data. The data they enter is into our australian hosted database. Its entered via an website / application. This has to remain the same.
With the above performance is affected for them on there write process. So im trying to setup i guess a mirrored DB scenario where they can enter and read and we can do the same. This would resolve performance issues However IDs need to be unique and not conflict or same IDs exist on creation so.when it.merges it errors.
Has anyone ever had or heard of a scenario to handle this?.any advice would he great.
December 7, 2016 at 3:28 am
Sounds like a job for merge replication. Have you looked into that?
John
December 7, 2016 at 3:36 am
Thanks for the quick reply. I haven't looked at merge replication as I'm very green in this type of area (rep, mirroring etc).
I'll have a read of it now and see how i go, ill come back with some notes
December 7, 2016 at 3:45 am
Correct me if I'm wrong, but merge replication from what ive read will resolve the conflicts in IDs etc... but the read process, they will still have to query the DB hosted in Australia, pull the data back and then write.
The read will still be affected by performance. Im hoping the read could be improved but merge replication it doesn't appear it will work in this case. Is that right?
December 7, 2016 at 4:18 am
No, with merge replication you read and write against your local database and the conflicts are resolved and changes merged so that the two (or more) databases stay in sync.
John
December 7, 2016 at 4:28 am
Sorry if im misunderstanding... so this setup would work?
Original country 1 - host database and no changes, read/write etc
New Country 2 - has a copy of country 1s Db on there local server.
Country 2 then reads from there database, makes changes and then it syncs to the DB which is hosted on country 1.
Country 1 now sees all data together
And vica verca if Country 1 updates /inserts into its local db it then gets sent to the country 2 db
December 7, 2016 at 4:37 am
That's right. But don't take my word for it - do some reading on merge replication, and then set up a test if you think it fits your requirements.
John
December 7, 2016 at 4:41 am
I've read a few articles, some good some bad.
I think i understand but still little confused on setup. I'll definitely run a test, need a bit of hands on to understand this further.
Really appreciate your help.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply