We have little experience with MS SQL Server, but yesterday, one of my
co-workers set up replication of that database in a matter of an hour or
so. I will admit, I'm not familiar with SQL server and I don't know
what kind of latency issues exist with it but it appeared to be fairly
easy and straight forward to do. While we do not like SQL Server, the
third party solution that is being implemented as part of the system we
are working on uses it.

The software we develop uses ASA 9.0.2.

I was wondering what are the replication options for ASA 9.0.2.

I know MobilLink is one option and SQL remote, but both of these seem to
be options that have latency issues and also must be manually
performed. I'm not very knowledgable about either product so if I'm
wrong, tell me.

I have also heard of Sybase Replication Server but I have yet to receive
a straight answer as to whether that product works with ASA or if it
will perform like we want it to.

What we are looking for is a way for a production database to be
constantly replicated onto another backup computer. This would allow
much quicker switch over in the event of a failure of the production
system and also allow activity such as reports to be performed against
the backup computer's database so the load does not affect the
production system as it runs.

Basically we are looking for a solution that we can startup two
databases on two different machines, setup the replication from one to
the other and be done with it. When a change occurs on the main
database, the change is automatically transfered to the backup database
so they stay in sync.

Thanks

Both SQL Remote and MobiLink are very well suited to do exactly what you'd
like to do. Although Rep Server will also need your needs, IMHO it's
overkill for the type of functionality you're looking for.

There is latency in ANY replication system you implement. Both SQL Remote
and MobiLink can scan the active transaction log for changes continuously,
and there is no need for a manually process to be run, as both SQL Remote
and the MobiLink processes can run continuously, and can also be setup as
services, so you never need to manually start them. If you let me come into
your office with a floppy disk, I could set up this system for you in 5
minutes, with most of time spent looking for a machine with a floppy drive,
and most of the remaining time waiting for the database file to copy from
machine to machine.

Here's an outline of how I'd use MobiLink to setup this system. I've just
typed this in from scratch, so I've likely got a typo somewhere, but you
should get the idea.

1) Temporarily shut down your "main" database, and copy the database file to
the backup machine. If you'd like this backup machine to be a flavour of
Linux or Unix, but your main system is windows, that's fine too. The db
file can be copied from platform to platform.

2) Bring up the main database again and create a publication that includes
all your tables, a synchronization user, and a synchronization subscription.
Here's what the commands will look like :

create publication p1 ( table t1, table t2, table t3, ..., table tlast );
create synchronization user u1;
create synchronization subscription to p1 for u1 type tcpip address
'host=ip_of_backup_machine' option sv='v1',sch='every:00:00';

It's important that no data be added to the main database until these three
commands have executed, so the paranoid among the group would start the
engine up with dbeng9 to ensure they are the only ones connected, then shut
down and start dbsrv9 again.

3) On the backup machine, start the backup database and created an ODBC DSN
that can be used to connect to the database.

4) Start the MobiLink Server and connect to the backup database with the
following command line :

dbmlsrv9 -c dsn=whatever -za -zu+

5) Run dbmlsync for the first time against the main database with a few
special options to create the synchronization scripts in your backup
database.

dbmlsync -c "eng=main;..." -uo -is -e "SendColumnNames=on"

6) Connect to the backup database and delete all the download_cursor
scripts, since information is flowing only one way from the main to the
backup database. The SQL to delete all the download cursor scripts would
look like :

begin
declare c1 cursor for select table_name from ml_table_scripts where event =
'download_cursor';
declare @tn varchar(128);
open c1;
fetch first c1 into @tn;
while sqlcode = 0 loop
call ml_add_table_script( 'v1', @tn, 'download_cursor', NULL);
fetch next c1 into @tn;
end loop;
close c1;
end;

7) Start dbmlsync on the main database, it will scan the transaction log
continuously for changes to the main database and send them to the backup
database.

8) Setup the MobiLink Server, the backup database server and dbmlsync to
start as services at your leisure.

Aside : dbmlsync scans the log continuously for changes when you specify
sch='every:00:00', and can be CPU and I/O intensive. If you'd like to add a
delay between scans of the transaction log in increments of minutes, change
the "every:00:00" code the read the appropriate number of hours and minutes
(i.e. every:hh:mm). If you want a delay in seconds, leave "every:00:00" and
create a stored procedure in the main database, where 'XX' is the number of
seconds between synchronizations :

create procedure sp_hook_dbmlsync_delay()
begin
UPDATE #hook_dict
SET value = XX
WHERE name = 'delay duration'
OR name = 'maximum accumulated delay';
end;


--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swd/base.do
-> Choose SQL Anywhere Studio
-> Set filter to "Display ALL platforms IN ALL MONTHS"


"Bill Williams" <billwilliams (AT) qcsoftware (DOT) com> wrote

Thank you for the detailed description. I will look into that.

Reg Domaratzki (iAnywhere Solutions) wrote:

On 13 Apr 2006 06:57:33 -0700, Bill Williams
<billwilliams (AT) qcsoftware (DOT) com> wrote:

SQL Remote is extremely easy to set up as well, especially for two
identical databases. MobiLink is far more flexible, with the penalty
being somewhat more setup complexity.

In the real world, synchronizing or replication more than once per
minute is probably not a good idea with either SQL Remote or MobiLink.
They were both designed for "occasionally connected" computer
applications, not continuous replication or mirroring.

However, both solutions can be completely automated to run on a
schedule.

Replication Server absolutely does work with SQL Anywhere, and is
documented in the V9 Help:

ASA Database Administration Guide
Replicating Data with Replication Server

Rep Server brings the latency down to a few seconds.

Are you willing to divide and conquer? Have *three* machines instead
of two?

Live transaction log backup to one machine can provide quick failover
in the case of a disaster, with very little (or no) loss of data. Live
backup is different from "online backup" in that transaction log
records are pulled across the network as they are created. There is
very little loss, but startup is not instant. FWIW hot failover is
coming in the next version (Beta starting soon).

SQL Remote to another machine can provide a reporting database which
you are free to bog down with all those OLAP queries-from-hell without
crippling the main database. Latency may vary, so this database may
not qualify as a failover site.

Another approach is to simply dbbackup the main database once a day to
create a new somewhat-stale reporting database. That is entirely
sufficient for some or all of the reporting requirements in some
shops, and the exceptions can run against the main database.

Breck

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhe...ers_Guide.html
breck.carter (AT) risingroad (DOT) com

The hot failover sound interesting. Is there any timeframe on when the
next version will be released?

In the mean time, I will look into your suggestions.

Thank you

Breck Carter [Team iAnywhere] wrote:

The Beta is going to start this quarter, and you can sign up now
http://www.ianywhere.com/promos/sqlanybeta/index.html


On 13 Apr 2006 11:34:43 -0700, Bill Williams
<billwilliams (AT) qcsoftware (DOT) com> wrote:

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhe...ers_Guide.html
breck.carter (AT) risingroad (DOT) com