<div>
If at all possible, you want to use dump and load.<br />
<br />
It will be the fastest technique. &nbsp;It will be the easiest to implement. &nbsp;It has the fewest gotchas (identity columns for instance). &nbsp;And it will be the least error prone.<br />
<br />
There are several reasons the load is running slower than the dump. &nbsp;First, the dump only deals with pages that actually have data on them whereas the load initializes every page in the database. &nbsp;Second, you may not have the memory configuration tuned yet on the target platform. &nbsp;Finally, your disk configuration on Linux may not be ideal.<br />
<br />
On the target platform:<br />
&nbsp;- avoid RAID-5 like the plague. &nbsp;Use mirroring for redundancy because random writes to RAID 5 arrays carry a serious performance penalty<br />
&nbsp;- avoid software RAID.<br />
&nbsp;- use a caching RAID controller. &nbsp;Hardware acceleration and caching are critical to write performance<br />
&nbsp;- make sure you have enough spindles. &nbsp;Everybody has a boss that want to save money and does not understand why you need 6, 146GB SAS drives to store 100GB database when you can get a single 1TB drive for less than 300 bucks. &nbsp;If the system is configured with three 500GB drives in a RAID-5 configuration, you WILL have performance problems, and not just with the Load.<br />
&nbsp; --If you are using ASE v15, you can use file system devices and the Direct I/O option<br />
&nbsp;-if you are using ASE 12.5.x, you should use RAW devices<br />
<br />
You don't mention the versions of either the source or target ASE installations nor the version of RedHat. &nbsp;Hopefully, you are going to RedHat EL4 or EL5 and 64 bit. &nbsp;You need the 64 bit version of Sybase as well as the 64 bit version of RedHat. &nbsp;It is kind of a waste at this late stage of the game to do a migration and not go to 64 bits.<br />
<br />
Another thing you can do to speed up the Dump/Load cycle if three hours is too long is to stripe the dump. &nbsp;Dumps are typically I/O bound and creating multiple stripes (to different physical devices) usually speeds things up considerably. &nbsp;You will also want to try using compression. &nbsp;Again, most systems have plenty of CPU and you can trade the CPU usage to compress the dump files and reduce the I/O to disk.<br />
<br />
Regards,<br />
Bill<br />

</div>


<div>
You're not going to be able to get reliable Gb/minute figures without actually testing each of the approaches. It depends far too much on hardware, network, exact configuration of Sybase in source and target, etc.<br />
<br />
There are other things we can do to speed up dump and load generally - giving Backup Server more memory will help (but only if you're using stripes). Bill's already given you a good starting place. <br />
<br />
I can tell you that sybmigrate performance (once you have it tuned) will be very similar to what you'd get from bcp through a named pipe that was doing an scp between machines. Bcp is only slower if you aren't pipelining it - ie bcp out, let that finish before starting a network copy, let that finish before starting a bcp in. <br />
<br />
Don't forget you'll be recreating indexes in all of these methods, too! Because this XPDL crosses Endian types, you'll be recreating many indexes afterwards with sp_post_xpload. <br />

</div>


<div>
Joe,<br />
<br />
Isn't it safe to say that dump/load will be faster than BCP, even if you are pipelining it?<br />
<br />
What all does sp_post_xpload do? &nbsp;Does it do reorgs or just rebuild suspect indexes. &nbsp;I wonder if it might not be better to plan on doing reorgs which should rebuild ALL the indexes and give a fresh set of statistics while it is at it.<br />
<br />
Regards,<br />
Bill<br />

</div>


<div>
Dump/load might be faster, but cross-platform is a lot slower than regular dump/load.<br />
<br />
Bcp has the advantage that only actual pages used need to be written, whereas as you say load database has to write every page. If there is a lot of free space then bcp could well be faster.<br />
<br />
You can also do more tuning of things like bcp packet size, bcping out of views or into specific partitions to maintain sorted data order, etc.<br />
<br />
sp_post_xpload drops &amp;&nbsp;recreates indexes, but not all of them. It is usually faster to manually do the equivalent since the proc does them only one at a time.<br />
<br />
<br />

</div>


<div>
Thanks a lot everyone for your quick responses.<br />
<br />
We are exploring following options (this is in case our client is not ready for upgrade to 15.0 and would like to stick to 12,5.0.3 on different platform):<br />
Case 1. Cross platform migration of Sybase ASE 12.5.0.3 from HP-UX 64-bit to Sybase ASE 12.5.0.3 on IBM-AIX 64-bit (same endian) - so guess with XPDL, we won't have to rebuild indexes. &nbsp;Cross platform dump and load is not available for 12.5.0.3 - hence we have options either to use BCP or Sybmigrate only.<br />
<br />
Case 2. Cross platform upgrade from Sybase ASE 12.5.0.3 from HP-UX 64-bit to Sybase ASE 15.0 on IBM-AIX 64-bit (same endian) - in this case we can use crossplatform dump and load.<br />
<br />
Hence looking for how much time will be required for BCP and Symigrate for a 100GB database.<br />
<br />
Any suggestions please? Thank you.
</div>


<div>
&quot;Hence looking for how much time will be required for BCP and Symigrate for a 100GB database&quot;<br />
<br />
But, it all depends on the indexes you have and whether you will spend time to tune index creation. It may take just one or two hours if you spend days to utilize a lot of engines very effectively or many hours if you use just one cpu.
</div>


<div>
Sorry alpmoon - XPDL statement got mixed up into Case 1<br />
<br />
Case 1. Cross platform migration of Sybase ASE 12.5.0.3 from HP-UX 64-bit to Sybase ASE 12.5.0.3 on IBM-AIX 64-bit (same endian) <br />
Source and target is 12.5.0.3 in first case - only platform change from HP-UX to AIX.<br />
Hence we have to use BCP or Sybmigrate<br />

</div>


<div>
To add to above,<br />
Cross platform dump and load will be possible for source&gt;11.9.x and target &gt;=12.5.3.
</div>


<div>
Ah. (I presume you mean me and not alpmoon?) Yes, if your target is 12.5.0.3, then you cannot use XPDL. <br />

</div>


<div>
Sorry for oversight Joe Woodhouse: :-) Thanks.
</div>


<div>
I would strongly suggest that your client NOT stay on ASE 12.5.0.3 as it did not seem to be nearly as stable as even 12.5.1 was.<br />
<br />
You could go to 12.5.4 and simply call it &quot;the latest patch level&quot; if they have FUD about an &quot;upgrade&quot;.<br />
<br />
The truth is now is the perfect time to upgrade to v15 (at least 15.0.2). &nbsp;In fact, there are a couple neat enhancements (virtually hashed tables) that are only available on AIX in version 15.0.3 so your best bet it to go for it.<br />
<br />
Regards,<br />
Bill<br />

</div>


<div>
Another option if you are trying to minimize production downtime is to mark the source database as read only. &nbsp;Depending on your application, it may be useful to have the app available read only for a few hours as opposed to not being available at all.<br />
<br />
After you get the Dump/Load/Reorg done, you can repoint the app tot he new database.<br />
<br />
Regards,<br />
Bill<br />

</div>


<div>
<div class="content wysiwyg-content">
Hi,<br />
<br />
Could anyone please share approximate time required for the following from the past experiences with Cross platform Sybase Migrations on Unix / Linux platforms?<br />
e.g. for cross platform 100 GB database migration (UltraSPARC/Solaris 9 to Xeon/Red Hat Linux), it took 32 min for Dump and 2 hrs 28 minutes for Load.<br />
What kind of performance (data transfer rate GB/min) can be achieved for the following approaches:<br />
&nbsp;1. BCP <br />
&nbsp;2. Sybmigrate<br />
&nbsp;3. Dump <br />
&nbsp;4. Load <br />
<br />
We require this information to determine duration for production roll out. We have experience with dump/load, but have not used BCP or Sybmigrate for whole database migration.<br />
<br />
Any pointers will be appreciated. Many thanks for your time.<br />

</div>
</div>


Hi,

Could anyone please share approximate time required for the following from the past experiences with Cross platform Sybase Migrations on Unix / Linux platforms?
e.g. for cross platform 100 GB database migration (UltraSPARC/Solaris 9 to Xeon/Red Hat Linux), it took 32 min for Dump and 2 hrs 28 minutes for Load.
What kind of performance (data transfer rate GB/min) can be achieved for the following approaches:
 1. BCP 
 2. Sybmigrate
 3. Dump 
 4. Load 

We require this information to determine duration for production roll out. We have experience with dump/load, but have not used BCP or Sybmigrate for whole database migration.

Any pointers will be appreciated. Many thanks for your time.


Sybase ASE : Data Migration Information required

Sybase, a subsidiary of SAP, builds a client/server relational database management system. Products include Adaptive Server Enterprise (ASE), Adaptive Server Anywhere (ASA), Sybase Unwired Platform (SUP) for mobile applications, Afaria for enterprise mobile device management and IQ for data warehouse and big data applications.