• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 264
  • Last Modified:

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?
0
Network_Padawan
Asked:
Network_Padawan
  • 6
  • 5
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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 & ArchitectCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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 & ArchitectCommented:
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 & ArchitectCommented:
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 & ArchitectCommented:
>> 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
 
Network_PadawanAuthor Commented:
Thanks rr,

as always, you are a great help.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Welcome..
And glad to help you out..
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now