Same-platform migrations are straightforward: dump the database on the source server, and load it into the target. This may not be possible across platform, depending on the platforms involved. For example, copying databases between Unix and Windows has long been complicated by the inability to load dumps between these two platforms.
Recent releases of Sybase offer both cross-platform dump-and-load, and a new utility (sybmigrate) that migrates across platforms. As usual, one solution does not fit all cases. Either or both these options may be precluded by factors including the release version of the source and target database servers, the volume of data, and the complexity of the schema. Even when the new functions are available, the “older” migration options may be preferable.
Migration Strategies
Cross-platform migration options can be grouped under six broad strategies:
1. sybmigrate (Sybase migration utility)
2. cross-platform dump and load
3. create schema, bcp transfer
4. create schema, CIS and proxy tables
5. Replication Server
6. third-party tools such as DTS
This document is the first in a series of articles, each describing one of the strategies.
Choice of Strategy
The appropriate choice of strategy is not easily predictable. Some strategies will have difficulties with certain data models, being unable to cope with complex user-defined datatypes or certain referential integrity constructions. Other strategies may demand additional memory resources on the source server, when often the reason for migration is a creaking source server overstretched for memory and CPU resources.
The best way to ensure a successful migration is to perform trial runs both early on as a feasibility study, and then again as near as possible to the production migration.
sybmigrate: the Sybase migration utility
Source server: 12.5.0.1 or later
Target server: 12.5.0.1 or later
The Sybase migration utility was developed for database migration across server page size. As an added bonus, versions from 12.5.0.1 onwards allow migration between cross-endian platforms such Unix/Windows. With this utility in particular, we recommend upgrading both source and target server with the latest revision and patches for 12.5.X.
The utility comes with a graphical wizard that allows the DBA to set up the migration process with a few clicks. The migration plan can be saved to a configuration file and run in batch mode at the time of choice.
The great advantage of sybmigrate is that if it works there is minimal scripting, and the need to delve into the source database structure.
Getting Familiar with the Tools
Find a bit of spare device space on the source server and create a small database with a couple of tables, a constraint or two, and a few rows of data. Create a database (no schema) of the same name and size on the target server, start the migration utility, and work through the GUI wizard. If at this stage, problems are encountered with logins, the tool seeming to hang etc, jump to the next section.
Browse through the very good help screens for each step of the process - knowing what is happening behind the scenes (e.g. use of CIS tables etc) will be of assistance when migrating a larger database. This documentation is more useful than the manual pages.
This trial run may seem to copy the database “like magic”. Behind the scenes, sybmigrate is actually automating a set of actions that some have performed manually with a lot more effort. The database schema is generated using ddlgen (another relatively recent bundled utility), and CIS is used to copy the data.
Planning the Migration Process
* Identify additional server elements for migration
The sybmigrate utility has its own chapter in the Utility Guide. One of the most useful sections is the list of what the utility DOES NOT migrate. In particular, user-defined thresholds, cache configurations and log I/O size may be the most common extras the DBA will need to migrate separately.
* Identify additional data model elements for migration
The sybmigrate utility may seem to take away the need for an in-depth understanding of the data model, and this may be true for certain databases. In reality, there are modeling aspects which the utility can not migrate successfully. A review will be necessary of stored procedures that rely on # temporary tables, and object references to other databases.
* Additional storage space
The utility creates at least two additional databases on the source server as part of the migration process, one repository database to hold the control information, and a work database per migrated database. Although some migrations are conducted precisely because the source platform has run out of space, the space requirements are actually quite small. Don’t extrapolate from a test run of a small database (or the pubs2 database), as the space requirements for a much larger database may be similar (and in the region of 10-20MB in total).
* Additional Memory
When dealing with the migration of large databases, acceptable performance during the transfer may require using extra memory on the source server. If the reason for migration is a creaking source platform, this may be problematic. In particular, increasing the default data cache may improve performance, and may be necessary for a large migration to complete. In addition, increasing the CIS batch size should improve performance. Both options use additional memory on the source server.
Prepare the servers
* Upgrade to the latest release of 12.5.X
Take the trouble to upgrade both source and target server with the latest patches, instead of migrating from a lower 12.5 version and upgrading the target server at a later date. As a new utility, sybmigrate needs polishing. What may be minor errors in some migrations become major difficulties in others. In particular, the ddlgen utility which sybmigrate uses to migrate the schema is more reliable in later versions.
* Verify the utility is functional
Before attempting a full migration from source to target, we recommend the migration of a small or test database between the servers. Some irritating glitches in the utility may be immediately apparent, depending on the version.
Right from the start, the utility may report connection failure. As a work-around, use the ‘sa’ login with the same password on both servers. That’s right, not a login with the sa role but ‘sa’ itself (which is probably locked on the source server: ignore good practice and unlock it for the migration!).
Although the GUI is very useful, you may need to check/uncheck different options a few times to get the checkboxes to “take”.
* Prepare the source server
The migration utility itself will create the “helper” databases on devices you specify, so storage space must be provided. This may be in the order of 10-20 MB.
Large database migrations may need some tweaking upwards of CIS configurations and of the default data cache which in turn require additional memory and CPU usage. If the process hangs or is proceeding unacceptably slowly, investigate these options.
* Prepare the destination server
Initialize the devices on the destination server, and create the database (no schema). If necessary, create named segments and configure cache.
Performing the migration
* Running the migration utility
The user can choose to run the migration via the wizard or set up a resource script for the utility to run in unattended mode. The utility allows migrating a subset of objects e.g. deferring index creation.
Full article...
