Case study: Database errors in an active-active replication scenario

Hi All,

I am putting together a document that highlights the problems that could arise from an active/active database design. Essentially what I am proposing is having a database in Sydney and an identical copy in UK, and a software called Oracle Golden Gate to perform the replication between. Now this software is costly and I need to justify why we need it.

I understand that without some sort of intelligence behind this proposed scenario, there could be scenarios like table and database lockups. Can someone be able to list a number of possible problems that such a design would attract without a 3rd party app like golden gate?
Network_PadawanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
We had planned to use Golden Gate some time back..
And we have used the below architecture.

Network Load Balancer
DBServer1    DBServer2

* Network Load balancer shares a common IP using which both Database servers can be accessed.
* For SQL Server, there is one limitation and hence if you have Identity columns, then in DBServer1 use IDENTITY(1,2) and in DBServer2 use IDENTITY(2,2)

Having a Active / Active node would act as a load balancer as well as a failover at database level..
0
Network_PadawanAuthor Commented:
In your scenario are both servers in the same local network?

Mine are across a vast geographical spread. Replication needs to be asyncronous.

We do not need load balancing, just two active db's that replicate changes to each other.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
We tried Asyncronous setup only separated across few states but not like in your case..

>> We do not need load balancing, just two active db's that replicate changes to each other.

Without load balancing, what's the purpose in having Active/ Active replication mode. You can have Active / passive replication itself right..
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Network_PadawanAuthor Commented:
Well the idea is that our European customers will access the database located in the UK, and asian-pacific users connect to our database in sydney.

Its more about latency to the database (european users connect to Sydney database).
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
We have tested it out in two different locations connected via Internet only..
But we saw latencies of only around 1 to 3 seconds only..
And hence for your scenario, I don't think it would take around 5 - 60 seconds max if you have huge activity running in your server.

But I don't think it would be an issue since this is ideal in geographically dispersed networks..
0
Network_PadawanAuthor Commented:
Right, but lets say the sql db is sydney writes to a particular table and replicates that information to the same table in the UK at the same time that table is being written to by users (though the apps) on that UK database...won't that cause the database to lock up and potential corrupt?
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Then you need to go for Merge replication to avoid these kind of issues..
Having ordinary replication would give these kind of errors.
0
Network_PadawanAuthor Commented:
Yes thats what I was thinking. Do you know what kind of problems I would run into without merge or some 3rd party app to manage the replication process?
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> Do you know what kind of problems I would run into without merge or some 3rd party app to manage the replication process?

Advantages:
* Only way you can manage consolidating multiple server data and keep everything in sync.

Disadvantages:

* Time intensive to replicate and synchronize both ends.
* Less consistency as lot of parties has to be synchronized.
* In order to avoid Conflicts, conflict resolution needs to be placed which adds complication and slowness to the whole process.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Network_PadawanAuthor Commented:
Thanks rr,

as always, you are a great help.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Welcome..
And glad to help you out..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.