Link to home
Start Free TrialLog in
Avatar of suzn cas
suzn casFlag for Syrian Arab Republic

asked on

two replication servers one for each replicated model in the same replication system

Dears, I have the following case and need some help please,

and sorry for my fresh experience with Sybase Replication.

I have one production server (sybase ASE 15.7) stores one production database.

I will install a replication server on a different server (but it is in the same building, so it will connect with the production server over LAN )

I will install replication server and create a new database repDB on it, and make employees retrieve reports from this database (to reduce load on production database)

so what is model  should I use for this replicate database?

On the other hand, I will install a new replication server on a far place (over WAN), and here I have to use Warm standby application, is this right,  even though the primary and standby database will be managed by different replication servers?? kindly note here I will use the first repDB (located on replication server in the same building with production server) as primary database.

Am I in the right way, and what are your suggestions about this issue?

please advice.

Thanks in advance
Avatar of Joe Woodhouse
Joe Woodhouse

Warm Standby must use only one Replication Server. It's not a good choice for WAN, where you really want a RS on each side of the WAN link for the best possible network latency.

WS also can only send a primary/active database to one replicate/standby. That won't give you what you need.

I think the best for your situation here is what's called MDA: "Multi-Site Availability".

I like it for you because it is simple (you replicate at the database level) it can work with one or two RSs, and you can send one primary database to multiple replicate databases.

You could choose something like this:

A -> B -> C

or

             A
           /   \
         B     C

In other words replicate over the LAN A to B and then B to C over the WAN, or send A to B and C directly. You could have a RS on each side of every link which if you aren't constrained by licensing costs is technically the cleanest and fastest way to do this.

So you'd have ASE and RS on each host A, B, and C.

The Replication Server Design Guide talks about these sorts of things at a good high level. The Administration Guide discusses the differences between WS and MSA in more detail... but WS cannot give you what I think you're asking for.
Avatar of suzn cas

ASKER

Dear Joe, Thanks in advance.

So, if I suppose A is the current server which holds primary database, I won't install replication server on it (to not affect its performance), I will install replication server in host B and the transactions will transfer from A to  replicate database on B over LAN,
On the other hand, this replication server will connect with another replication server on C host and the transactions will transfer from database on B to replicate database on C over WAN.
then I can balance loading between the primary database on A and its copy on B, so users can access B just for read operation (I will think if I can implement this scenario), and the database on C is offline till any failure done with database on A.
if this is right, I will start reading about setup implement MSA and the whole scenario
please excuse me for any annoyance.
Best Regards.
I recommend each RS be local to its ASE. The performance impact is mild - unless you're talking about hundreds of databases each moving vast numbers of transactions it will be very small. However there is network latency issues in having RS not on the same host, so on balance especially in your scenario where you're talking about replicating only one database, I'd say just keep each RS on the same host as each ASE.

So that looks like this:
Host A: ASE A and RS A.
Host B: ASE B and RS B.
Host C: ASE C and RS C.

For your scenario I suggest replication topology like this: A -> B -> C

This is because the link B -> C will be slower than A -> B. If you replicate both out of A (e.g. A -> B and A -> C) then things will be held up in A until replication to both has finished. Also if A goes down then there is no more replication into C at all.

But if you replicate A -> B -> C then each link is independent of the other. If A is down C will still receive everything that gets done to B.

Note that C will have to be up to receive replication, but you can lock all user logins so they cannot connect. Or you can use multiple listener ports, one for DBAs and RS and one for users, and only bring the user one up when people are allowed to access C directly.

Note also that read-only access to B can and will compete with replication A -> B, because readers always block writers. So long as queries and reports are small and fast this shouldn't be an issue. You can't put the database in read-only mode though because the replication maintenance user (used to apply replicated transactions) must write. My advice is keep the database in "dbo only" mode - and then alias the maint user to dbo.

Number one surprise for people new to Replication Server - you need to make your transaction logs bigger. Any replication problem means transactions remain in the logs and so logs will grow faster and larger than before.

Number two surprise is that you are not replicating data. You are replicating transactions. The transaction is sent to the destination and then re-run there. So if the transaction takes 10 minutes to run in the primary it will take at least 10 minutes to run in the replicate.

Number three surprise is the "at least" in my comment above. Transactions are not applied exactly as-is, for good technical reasons. Depending on some things they can be much much slower in the replicate side, if transformed badly and if there is no unique PK.

So that's number four surprise - you really need a unique PK in every table in this database. If that isn't true today then stop implementing replication until that is fixed. :)
Dear Joe,
Actually, I don't know what to say, very big thanks for your help and explanation.
but let me tell you what I found:
there is some gap in my previous scenario, when I said that I will make B as reporting server and then make new replication way from B-> C:
here there are two issues:

1- I have not to replicate the whole database to B (just define some tables from which the reports retrieve the data), because: when users connect and login to DB in B, these logins will recorded in the transaction log but from different site rather than A so the data between A and B will not be synchronize so maybe the replication will be stop with error, is this right?
 (I think this is happened when my company was using replication server and ASE )
2- another important point, continuing to the previous point, I want the whole standby database on C as Disaster recovery solution, and this is impossible cause B have part of database and cannot replicate whole database to C.

there is still some confusion in my mind, so please excuse me.

Another inquiry please, why should I increase the transaction log size, the transaction are still in it even there is no replication, so why after apply replication will be bigger than previous.

Thanks in advance.
ASKER CERTIFIED SOLUTION
Avatar of Joe Woodhouse
Joe Woodhouse

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Dear Joe, Thanks again,
I was doing some scenario in my environment guided by your advises,
Looks like your comment was truncated...
Every question asked has been answered...