Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Duplicating data

4 posts in Windows NT Last posting was on 1998-02-12 20:42:46.0Z
Connie Felt Posted on 1998-02-11 22:16:33.0Z
Message-ID: <34E22341.7442@ga.wa.gov>
Date: Wed, 11 Feb 1998 14:16:33 -0800
From: Connie Felt <cfelt@ga.wa.gov>
Organization: General Administration
X-Mailer: Mozilla 3.01 (Win95; I)
MIME-Version: 1.0
Subject: Duplicating data
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 18
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5210
Article PK: 1081289

We are in the process of developing an on-line catalog that will running
against a sybase 11.5 for NT database. This server will be outside of
the firewall. We need to be able to pull over this information on a
regular basis to another Sybase 11 database running on UNIX. Once the
information is processed by another application, the data will need to
be sent back to the NT database. I have been tossing around some
different ideas of how this might get accomplished. Any thoughts on
this would be greatly appreciated.
* Would replication be the best way to accomplish this?
* Would scheduled BCP be better?
* Would stored procedures work even better?

Thanks for your help


Eoin Woods Posted on 1998-02-12 20:42:46.0Z
Message-ID: <34E35EC5.5A98C767@sybase.com>
Date: Thu, 12 Feb 1998 12:42:46 -0800
From: Eoin Woods <eoin.woods@sybase.com>
Reply-To: eoin.woods@sybase.com
Organization: Sybase Inc.
X-Mailer: Mozilla 4.04 [en] (WinNT; I)
MIME-Version: 1.0
To: Connie Felt <cfelt@ga.wa.gov>
Subject: Re: Duplicating data
References: <34E22341.7442@ga.wa.gov>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 35
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5200
Article PK: 1081281


Connie Felt wrote:
> We are in the process of developing an on-line catalog that will running
> against a sybase 11.5 for NT database. This server will be outside of
> the firewall. We need to be able to pull over this information on a
> regular basis to another Sybase 11 database running on UNIX. Once the
> information is processed by another application, the data will need to
> be sent back to the NT database. I have been tossing around some
> different ideas of how this might get accomplished. Any thoughts on
> this would be greatly appreciated.
> * Would replication be the best way to accomplish this?

Replication would be a neat and tidy solution - however, can the two
servers "see" each other from a TCP/IP point of view given you have a
firewall?

> * Would scheduled BCP be better?
That would work fine (provided that the latency was OK) - again, you
need to move data between the two machines however with this approach
it is a batch problem which is easier (e.g. FTP puts and gets from
behind the firewall).

> * Would stored procedures work even better?
Probably not as this involves major rework as things change.

Eoin.
--
Eoin Woods, NT Development, ASE Engineering, Sybase Inc.
Sybase (UK), Crown Lane, Maidenhead, Berkshire, SL6 8QZ
Tel: +44-1628-597215 Fax: +44-1628-597000
Email: Eoin.Woods@sybase.com


Reinoud van Leeuwen Posted on 1998-02-12 09:30:44.0Z
Message-ID: <34E2C144.41C1@sybase.com>
Date: Thu, 12 Feb 1998 10:30:44 +0100
From: Reinoud van Leeuwen <reinoud@sybase.com>
Organization: Sybase Inc.
X-Mailer: Mozilla 3.0 (Win95; I)
MIME-Version: 1.0
To: Connie Felt <cfelt@ga.wa.gov>
Subject: Re: Duplicating data
References: <34E22341.7442@ga.wa.gov>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 26
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5206
Article PK: 1081284


Connie Felt wrote:
>
> We are in the process of developing an on-line catalog that will running
> against a sybase 11.5 for NT database. This server will be outside of
> the firewall. We need to be able to pull over this information on a
> regular basis to another Sybase 11 database running on UNIX. Once the
> information is processed by another application, the data will need to
> be sent back to the NT database. I have been tossing around some
> different ideas of how this might get accomplished. Any thoughts on
> this would be greatly appreciated.
> * Would replication be the best way to accomplish this?
> * Would scheduled BCP be better?
> * Would stored procedures work even better?

Well, since you use 11.5 you can use the built-in Omni features. This
enables you to move data between servers without having to hassle with
bcp or Rep server.
This could easiliy be acomplished by some scheduled stored procedures.

Reinoud van Leeuwen
Sybase Professional Services Maarssen / The Netherlands


Mark A. Parsons Posted on 1998-02-12 00:07:44.0Z
Message-ID: <34E23D50.1BA7@compuserve.com>
Date: Wed, 11 Feb 1998 19:07:44 -0500
From: "Mark A. Parsons" <Iron_Horse@compuserve.com>
Reply-To: Iron_Horse@compuserve.com
Organization: Iron Horse, Inc.
X-Mailer: Mozilla 3.01 (Win95; I)
MIME-Version: 1.0
Subject: Re: Duplicating data
References: <34E22341.7442@ga.wa.gov>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 108
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5209
Article PK: 1081290


Connie Felt wrote:
>
> We are in the process of developing an on-line catalog that will running
> against a sybase 11.5 for NT database. This server will be outside of
> the firewall. We need to be able to pull over this information on a
> regular basis to another Sybase 11 database running on UNIX. Once the
> information is processed by another application, the data will need to
> be sent back to the NT database. I have been tossing around some
> different ideas of how this might get accomplished. Any thoughts on
> this would be greatly appreciated.
> * Would replication be the best way to accomplish this?
> * Would scheduled BCP be better?
> * Would stored procedures work even better?
>
> Thanks for your help

Ummm .... It Depends!! <eg>

How much data are you looking to pull over during a given period of
time?

If you're talking about 'small' bits of info you'll probably find it
easier to write a few small bcp scripts (hint, with the new versions of
bcp you can bcp from view's, i.e., dynamically create views that contain
the desired 'where' clause!!) to do the job ... as opposed to all the
management overhead of setting up and maintaining a rep server. Of
course, using stored procs could work, too ... depending on how you
answer the next question.

Do you want to pull this info over in batches or real-time or 'near'
real-time?

If you can handle doing the transfer in batches then bcp (from views??)
should work just fine (assuming you can handle the issue of
'transactional consistency' between the various tables for which you'll
be pulling data.

If you need real-time movement of the data I'd look at using remote
stored procs, of course, if these become part of your application logic
then these operate syncronous with your modifications on the primary
dataserver ==> if the remote dataserver is unavailable then you either
won't be able to do any work on the primary or you'll have to deal with
the headaches of how to 're-sync' the systems when the remote dataserver
is again available.

If you need 'near' real-time you could look at using rep server.

Oh, one other ... kind of a mix between the bcp and remote stored proc
... have a cron job (kicked off on your UNIX box) periodically log into
the primary dataserver and kick off a stored proc which does the
'pulling' of data. This stored proc could figure out which data needs
to be moved and then makes the appropriate remote stored proc calls to
the secondary dataserver.

How you get the data back to the primary dataserver (from the remoted
dataserver)? Same ideas as above ... as long as you don't get into an
infinite loop passing the same chunk of data back and forth.

The bcp method requires a little work on your behalf to write scripts to
generate the appropriate view's, get the data out and in, deciding on
what to do if some of the files don't load ==> transactional
inconsistency between your tables being loaded???

Using Rep Server can make sure data gets across while maintaining
transactional consistency (depending on how your application(s)
access/modify the database(s)). Rep Server will take some time to setup
and to maintain ... not to mention the additional licensing req's and
increased use of memory/cpu/disk for the rep server environment.

Using the remote stored proc method will probably be your best bet if
you're not dealing with *TONS* of data and you can stand some delays. I
would not recommend making these remote stored proc calls part of your
application logic ... instead ... kick off cron jobs from your UNIX box
to periodically go in and pull out the desired data (modify your
application to either flag 'new' data or put a 'marker', along with the
'new' data's key, in a status table?). This cron job would execute a
stored proc(s) that would pull together all data related to a specific
key and then transmit the whole 'record', via a remote stored proc call,
to the remote system. If throughput is an issue ... look at kicking off
multiple 'scavenger' stored procs ... each making it's own call to the
remote dataserver.

So many options ... so little time ... what to do?? ... It Depends!
--

Mark Parsons
Iron Horse, Inc.
[Team Sybase]