ftp.pasteur.org/FAQ//ftp-pasteur-org-FAQ.zip/FAQ/databases/sybase-faq/part3

Internet Message Format | 2004-04-21 | 22.6 KB

Path: senator-bedfellow.mit.edu!dreaderd!not-for-mail
Message-ID: <databases/sybase-faq/[e-mail]>
Supersedes: <databases/sybase-faq/[e-mail]>
Expires: 2 Aug 2004 13:43:10 GMT
References: <databases/sybase-faq/[e-mail]>
X-Last-Updated: 2003/03/02
From: [e-mail] (David Owen)
Newsgroups: comp.databases.sybase,comp.answers,news.answers
Subject: Sybase FAQ: 3/19 - REP
Reply-To: [e-mail] (David Owen)
Followup-To: comp.databases.sybase
Distribution: world
Organization: Midsomer Consultants Inc.
Approved: [e-mail]
Keywords: FAQ, DATABASES, SYBASE, ASA, ASE, REP
Originator: [e-mail]
Date: 20 Apr 2004 13:45:02 GMT
Lines: 574
NNTP-Posting-Host: penguin-lust.mit.edu
X-Trace: 1082468702 senator-bedfellow.mit.edu 561 18.181.0.29
Xref: senator-bedfellow.mit.edu comp.databases.sybase:106201 comp.answers:56947 news.answers:270287

Archive-name: databases/sybase-faq/part3
URL: http://www.isug.com/Sybase_FAQ
Version: 1.7
Maintainer: David Owen
Last-modified: 2003/03/02
Posting-Frequency: posted every 3rd month
   A how-to-find-the-FAQ article is posted on the intervening months.

                       Sybase Frequently Asked Questions


Sybase FAQ Home PageAdaptive Server Enterprise FAQAdaptive Server Anywhere FAQ
                          Repserver FAQSearch the FAQ
                                     [bar]

                           Sybase Replication Server



        1.    Introduction to Replication Server
        2.    Replication Server Administration
        3.    Troubleshooting Replication Server
        4.    Additional Information/Links



































                      Introduction to Replication Server



    1.1    Introduction
    1.2    Replication Server Components
    1.3    What is the Difference Between SQL Remote and Replication Server?



Thanks go to Manish I Shah for major help with this introduction.

next prev ASE FAQ

-------------------------------------------------------------------------------

1.1    Introduction

-------------------------------------------------------------------------------

What is Replication Server

Replication Server moves transactions (insert, updates and deletes) at the
table level from a source dataserver to one or more destination dataservers.
The dataserver could be ASE or other major DBMS flavour (including DB2,
Informix, Oracle). The source and destinations need not be of the same type.

What can it do ?

  * Move data from one source to another.
  * Move only a subset of data from source to destination. So, you can
    subscribe  to a subset of data, or a subset of the columns, in the source
    table, e.g. select * from clients where state =  NY
  * Manipulation/transformation of data when moving from source to destination.
    E.g. it can map data from a data-type in DB2 to an equivalent in Sybase.*
  * Provide a warm-standby system.  Can be incorporated with Open Switch to
    provide a fairly seamless fail-over environment.
  * Merge data from several source databases into one destination database
    (could be for a warehouse type environment for example).
  * Move data through a complicated network down to branch offices, say, only
    sending the relevant data to each branch.

(* This is one of Sybase replication's real strengths, the ability to define
function string classes which allow the conversion of statements from one SQL
dialect to match the dialect of the destination machine.  Ed)

How soon does the data move

The data moves asynchronously. The time it takes to reach the destination
depends on the size of your transaction, level of activity in that particular
database (a database as in Sybase systems), the length of the chain (one or
more replication servers that the transaction has to pass through to reach the
destination), the thickness of pipe (network), how busy your replication server
is etc. Usually, on a LAN, for small transactions, this is about a second.

Back to top

-------------------------------------------------------------------------------

1.2    Replication Server Components

-------------------------------------------------------------------------------

Basic

Primary Dataserver

The source of data where client applications enter/delete and modify data.  As
mentioned before, this need not be ASE, it can be Microsoft SQL Server, Oracle,
DB2, Informix.  (I know that I should get a complete list.)

Replication Agent/Log Transfer Manager

Log Transfer Manager (LTM) is a separate program/process which reads
transaction log from the source server and transfers them to the replication
server for further processing. With ASE 11.5, this has become part of ASE and
is now called the Replication Agent.  However, you still need to use an LTM for
non-ASE sources.  I imagine there is a version of LTM for each kind of source
(DB2, Informix, Oracle etc).  When replication is active, you see one
connection per each replicated database in the source dataserver (sp_who).

Replication Server (s)

The replication server is an Open Server/Open Client application.  The server
part receives transactions being sent by either the source ASE or the source
LTM.  The client part sends these transactions to the target server which could
be another replication server or the final dataserver.  As far as I know, the
server does not include the client component of any of the other DBMSes out of
the box.

Replicate (target) Dataserver

Server in which the final replication server (in the queue) will repeat the
transaction done on the primary. You will see a connection, one for each target
database, in the target dataserver when the replication server is actively
transferring data (when idle, the replication server disconnects or fades out
in replication terminology).

Back to top

-------------------------------------------------------------------------------

1.3    What is the Difference Between Replication Server and SQL Remote?

-------------------------------------------------------------------------------

Both SQL Remote and Replication Server perform replication.  SQL Remote was
originally part of the Adaptive Server Anywhere tool kit and is intended for
intermittent replication.  (The classic example is that of a salesman
connecting on a daily basis to upload sales and download new prices and
inventory.)  Replication Server is intended for near real-time replication
scenarios.

Back to top

-------------------------------------------------------------------------------

next prev ASE FAQ

                       Replication Server Administration



    2.1    How can I improve throughput?
    2.2    Where should I install replication server?
    2.3    Using large raw partitions with Replication Server on Unix.
    2.4    How to replicate col = col + 1
    2.5    What is the difference between an LTMs an a RepAgent?
    2.6    Which Should I choose, RepAgent or LTM?

next prev ASE FAQ

-------------------------------------------------------------------------------

2.1    How can I improve throughput?

-------------------------------------------------------------------------------

Check the Obvious

First, ensure that you are only replicating those parts of the system that need
to be replicated.  Some of this is obvious.  Don't replicate any table that
does not need to be replicated.  Check that you are only replicating the
columns you need.  Replication is very sophisticated and will allow you to
replicate both a subset of the columns as well as a subset of the rows.

Replicate Minimum Columns

Once the replication is set up and synchronised, it is only necessary to
replicate those parts of the primary system that actually change.  You are only
replicating those rows and columns that need to be replicated, but you only
need to replicate the actual changes.  Check that each replication definition
is defined using the clause:

create replication definition rep_def_name
with primary...
...
replicate minimal columns

Second Replication Server

This might be appropriate in a simple environment on systems with spare cycles
and limited space on the network.  When Sybase replicates from a primary to a
replicate using only one replication server the data is transferred across the
network uncompressed.  However, the communication between two replication
servers is compressed.  By installing a second replication server it is
possible to dramatically reduce the bandwidth needed to replicate your data.

Dedicated Network Card

Obviously, if replication is sharing the same network resources that all of the
clients are using, there is the possibility for a bottleneck if the network
bandwidth is close to saturation.  If a second replication server is not going
to cut it since you already have one or there are no spare cycles, then a
second network card may be the answer.

First, you will need to configure ASE to listen on two network connections.
This is relatively straightforward.  There is no change to the client
configuration.  They all continue to talk to Sybase using the same connection.
When defining the replication server, ensure that the interfaces/sql.ini entry
that it uses only has the second connection in it.  This may involve some
jiggery pokery with environment variables, but should be possible, even on NT!
You need to be a little careful with network configuration.  Sybase will
communicate with the two servers on the correct address, but if the underlying
operating system believes that both clients and repserver can be serviced by
the same card, then it will use the first card that it comes to.  So, if you
had the situation that all of the clients, ASE and the replication server were
on 192.168.1.0, and the host running ASE had two cards onto this same segment,
then it would choose to route all packets through the first card.  OK, so this
is a very simplistic error to correct, but similar things can happen with more
convoluted and, superficially, better thought out configurations.

+---------+                                +-----------+   +-----------+
|         |--> NE(1) --> All Clients...    |           |   |           |
| Primary |                                | repserver |   | replicate |
|         |--> NE(2) --------------------->|           |-->|           |
|         |                                |           |   |           |
+---------+                                +-----------+   +-----------+

So, configure NE(1) to be on 192.168.1.0, say, and NE(2) to be on 192.168.2.0
and all should be well.  OK, so my character art is not perfect, but I think
that you get the gist!

No Network Card

If RepServer resides on the same physical machine as either the primary or the
replicate, it is possible to use the localhost or loopback network device.  The
loopback device is a network interface that connects back to itself without
going through the network interface card.  It is almost always uses the IP
address 127.0.0.1.  So, by applying the technique described above, but instead
of using a dedicated network card, you use the loopback device.  Obviously, the
two servers have to be on the same physical machine or it won't work!

Back to top

-------------------------------------------------------------------------------

2.2    Where should I install replication server?

-------------------------------------------------------------------------------

A seemingly trivial question, but one that can cause novices a bit of worry.

There are three answers: on the primary machine, on the replicate machine or on
a completely separate machine.  There is no right answer, and if you are doing
an initial install it probably pays to consider the future, consider the
proposed configuration and have a look at the load on the available machines.

It is probably fair to say that replication is not power hungry but neither is
it free.  If the primary is only just about coping with its current load, then
it might be as well looking into hosting it on another machine.  The argument
applies to the replicate.  If you think that network bandwidth may be an issue,
and you may have to add a second replication server, you may be better off
starting with repserver running on the primary.  It is marginally easier to add
a repserver to an existing configuration if the first repserver is on the
primary.

Remember that a production replication server on Unix will require raw devices
for the stable devices and that these can be more than 2GB in size.  If you are
restricted in the number of raw partitions you have available on a particular
machine, then this may have a bearing.  See Q2.3.

Installing replication server on its own machine will, of course, introduce all
sorts of problems of its own, as well as answering some.  The load on the
primary or the replicate is reduced considerably, but you are definitely going
to add some load to the network.  Remember that ASE->Rep and Rep->ASE is
uncompressed.  It is only Rep->Rep that is compressed.

Back to top

-------------------------------------------------------------------------------

2.3    Using large raw partitions with Replication Server on Unix.

-------------------------------------------------------------------------------

It is a good practice with production installations of Replication Server on
Unix that you use raw partitions for the stable devices.  This is for just the
same reason that production ASE's use raw partitions.  Raw devices can be a
maximum of 2GB with replication server up to release 11.5.  (I have not checked
12.)

In order to utilise a raw partition that is greater than 2GB in size you can do
the following (remember all of the cautionary warnings about trying this sort
of stuff out in development first!):

add partition firstpartition on '/dev/rdsk/c0t0d0s0' with size 2024
go
add partition secondpartition on '/dev/rdsk/c0t0d0s0' with size 2024
starting at 2048
go

Notice that the initial partition is sized at 2024MB and not 2048.  I have not
found this in the documentation, but replication certainly seems to have a
problem allocating a full 2GB.  Interestingly, do the same operation through
Rep Server Manager and Sybase central caused no problems at all.

Back to top

-------------------------------------------------------------------------------

2.4    How to replicate col = col + 1

-------------------------------------------------------------------------------

Firstly.  While the rule that you never update a primary key may be a
philosophical choice in a non-replicated system, it is an architectural
requirement of a replicated system.

If you use simple data replication, and your primary table is:

id
---
1
2
3

and you issue a:

update table set id=id+1

Rep server will do this in the replicate:

begin tran
update table set id=2 where id=1
update table set id=3 where id=2
update table set id=4 where id=3
commit tran

Hands up all who can see a bit of a problem with this!  Remember, repserver
doesn't replicate statements, it replicates the results of statements.

One way to perform this update is to build a stored procedure on both sides
that executes the necessary update and replicate the stored procedure call.

Back to top

-------------------------------------------------------------------------------

2.5    What is the difference between an LTM and a RepAgent?

-------------------------------------------------------------------------------

As described in Section 1.2, Log Transfer Managers (LTMs) and RepAgents are the
processes that transfer data between ASE and the Replication Server.

LTMs were delivered with the first releases of Replication Server.  Each LTM is
a separate process at the operating system level that runs along side ASE and
Replication Server.  As with ASE and Replication Server, a RUN_<ltm_server> and
configuration file is required for each LTM.  One LTM is required for each
database being replicated.

Along with ASE 11.5 a new concept was introduced, that of RepAgent.  I am not
sure if you needed to use RepServer 11.5 as well, or whether the RepAgents
could talk to earlier versions of Replication Server.  Each RepAgent is, in
effect, a slot-in replacement for an LTM.  However, instead of running as
separate operating system process, it runs as a thread within ASE.  Pretty much
all of the requirements for replication using an LTM apply to the RepAgents.
One per database being replicated, etc. but now you do not need to have
separate configuration files.

Back to top

-------------------------------------------------------------------------------

2.6    Which should I use, RepAgent or LTM?

-------------------------------------------------------------------------------

The differences between RepAgents and LTMs are discussed in  Section 2.5.
Which then to choose.  There are pros and cons to both, however, I think that
it should be stated up front that RepAgents are the latest offering and I
believe that Sybase would expect you you to use that.  Certainly the
documentation for LTMs is a little buried implying that they do not consider it
to be as current as LTMs.

LTM Cons:

  * Older technology.  Not sure if it is being actively supported.
  * Not integrated within ASE, so there is a (small) performance penalty.
  * Separate processes, so need additional monitoring in production
    environments.

LTM Pros:

  * Possible to restart LTM without having to restart ASE.

RepAgent Cons

  * If it crashes it is possible that you will have to restart ASE in order to
    restart RepAgent.

RepAgent Pros

  * Latest, and presumably greatest, offering.
  * Tightly integrated with ASE so good performance.
  * Less to manage, no extra entries in the interfaces file.

Back to top

-------------------------------------------------------------------------------

next prev ASE FAQ

                      Replication Server Trouble Shooting



    3.1    Why am I running out of locks on the replicate side?
    3.2    Someone was playing with replication and now the transaction log on
    OLTP is filling.

next prev ASE FAQ

-------------------------------------------------------------------------------

3.1    Why am I running out of locks on the replicate side?

-------------------------------------------------------------------------------

Sybase replication works by taking each transaction that occurs in the primary
dataserver and applying to the replicate.  Since replication works on the
transaction log, a single, atomic, update on the primary side that updates a
million rows will be translated into a million single row updates.  This may
seem very strange but is a simple consequence of how it works.  On the primary,
this million row update will attempt to escalate the locks that it has taken
out to an exclusive table lock.  However, on the replicate side each row is
updated individually, much as if they were being updated within a cursor loop.
Now, Sybase only tries to escalate locks from a single atomic statement (see
ASE Qx.y), so it will never try to escalate the lock.  However, since the
updates are taking place within a single transaction, Sybase will need to take
out enough page locks to lock the million rows.

So, how much should you increase the locks parameter on the replicate side?  A
good rule of thumb might be double it or add 40,000 whichever is the larger.
This has certainly worked for us.

Back to top

-------------------------------------------------------------------------------

3.2    Someone was playing with replication and now the transaction log on OLTP
is filling.

-------------------------------------------------------------------------------

Once replication has been configured, ASE adds another marker to the
transaction log.  The first marker is the conventional one that marks which
transactions have had their data written to disk.  The second is there to
ensure that the transactions have also been replicated.  Clearly, if someone
installed replication and did not clean up properly after themselves, this
marker will still be there and consequently the transaction log will be filling
up.  If you are certain that replication is not being used on your system, you
can disable the secondary truncation marker with the following commands:

1> use <database>
2> go
1> dbcc settrunc(ltm, ignore)
2> go

The above code is the normal mechanism for disabling the trucation point.  I
have never had a problem with it.  However, an alternative mechanism for
disabling the truncation point is given below.  I do not know if it will work
in situations that the previous example won't, or if it works for databases
that are damaged or what.  If someone knows when you use it and why, please let
me know (mailto:[e-mail]).

1> sp_role "grant", sybase_ts_role, sa
2> go
1> set role sybase_ts_role on
2> go
1> dbcc dbrepair(dbname, ltmignore)
2> go
1> sp_role "revoke", sybase_ts_role, sa
2> go

This scenario is also very common if you load a copy of your replicated
production database into development.

Back to top

-------------------------------------------------------------------------------

next prev ASE FAQ

                         Additional Information/Links



    4.1    Links
    4.2    Newsgroups

next prev ASE FAQ

-------------------------------------------------------------------------------

4.1    Links

-------------------------------------------------------------------------------

Thierry Antinolfi  has a replication FAQ at his site http://pro.wanadoo.fr/
dbadevil that covers a lot of good stuff.

Rob Verschoor has a 'Replication Server Tips & Tricks' section on his site, as
well as an indispensible quick reference guide!

Back to top

-------------------------------------------------------------------------------

4.2    Newsgroups

-------------------------------------------------------------------------------

There are a number of newsgroups that can deal with questions.  Sybase have
several in their own forums area.

For Replication Server:

    sybase.public.rep-server
    sybase.public.rep-agent

for SQL Remote and the issues of replicating with ASA:

    sybase.public.sqlanywhere.replication

and of course, there is always the ubiquitous

    comp.databases.sybase.

Back to top

-------------------------------------------------------------------------------

next prev ASE FAQ