Sybase Migration - Sybmigrate

Introduction

Sybmigrate lets you migrate databases and servers across Adaptive Server versions, platforms, and page sizes. It is supported by Adaptive Server versions 12.0 and later when used as the source server.

Benefits of sybmigrate

• Aids users in changing the page sizes of their database applications.
• Provides a manageable and smooth migration process.
• Allows customers to take advantage of the variable page size feature for existing databases with user data, thus realizing the full benefit of Adaptive Server versions 12.5 and later.

Data Affected

During the setup portion of the migration process, the following server data is migrated to the target Adaptive Server:

• Remote servers
• Logins
• Login attributes
• Server roles
• Login roles
• Role attributes
• Users
• Alternate users
• Roles
• Permissions
• Remote logins
• External login attributes
• Timer
• Resource limits
• Replication attributes
• Display level attributes
• User messages in the master database
• Java classes in the master database
• JAR files in the master database

During the migration portion of the migration process, the following database-specific data is migrated to the target database:

• Defaults
• User-defined datatypes
• Rules
• User tables
• User table data
• Views
• Triggers
• Indexes
• Stored procedures
• Extended stored procedures
• Users
• Logins
• Roles
• Remote servers
• Database data
• Users
• Alternate users
• Roles
• Role attributes
• Permissions
• User messages
• Java classes
• JAR files
• Defaults
• Rules
• User-defined types
• Tables
• Indexes
• Referential constraints
• Views
• Stored procedures
• Triggers

The following items must be migrated manually:

• User-defined thresholds
• Abstract plan definitions maintained in sysqueryplans
• All system databases except the model database
• Any required database options like cache binding, recovery order, and the associated log I/O size as specified by sp_logiosize
• Proxy databases
• Engine groups
• Engine bindings
• Execution classes
• Cache configurations
• Auditing tables and auditing configuration
• Server-wide row-lock promotion settings
• Access rules
• Drop access rules before beginning data migration; they can prevent the Database Owner from accessing all rows in a table, which prevents complete data migration.
• Compiled objects with hidden SQL text
• User-defined segments
• Constraints are migrated but when they are bound by name to user-defined message numbers, the bindings must be re-created manually
• Settings for objects such as ascinserts, maxwritedes, indextrips, oamtrips, datatrips, and sortbufsize created using dbcc tune
• Device definitions
• SQLJ functions
• Proxy tables for external files
• Audit options and audit events
• Server configuration
• Database suspect threshold
• Recovery orders

Prerequisites

• Set $SYBASE_JRE to $JAVA_HOME.
• Make sure that you can connect to Source server from target and vice versa.
• Run full dbcc checks on your database(s) to be migrated, and fix reported problems.
• Perform backups of the database(s) to be migrated.
• sybmigrate requires allow resource limits to be set to 0.
• Manually create segments on target database before starting migration
• There are three types of data that are migrated: server data, database data, and user objects. To migrate metadata (the server and database data), the target Adaptive Server must be newly installed so that the migrated metadata does not conflict with any residual data from previous usage.
• Determine the size of the named caches and buffer pools on the target Adaptive Server. sybmigrate does not migrate cache configurations. You can use the information that is generated by ddlgen and apply it to the target Adaptive Server, or you can choose to configure larger amounts of memory, in light of the larger page size being used.
• If multibyte character sets are configured on the target Adaptive Server after initiating the migration process, you must manually run dbcc fix_text on the sysattributes and sysxtypes system catalogs to make the text columns in these catalogs consistent with the multibyte character sets.Sybase recommends that you configure the target server character set first, and then initiate the migration process.
• Check the following Parameters in source:
• cis packet size – should be equal to max page size of the target Adaptive Server.
• number of user connections – should be high enough to accommodate the migration of multiple tables simultaneously according to the value of COPY_THREADS and INDEX_THREADS
• max parallel degree – should be set to a value that is larger than the largest number of partitions in a single table.
• number of worker processes – data migration for partitioned tables requires one worker thread per partition. Therefore, if  10 partitioned tables with 2 partitions each are migrating simultaneously, configure a total of 10*2 worker threads on the source Adaptive Server.
• Check the following parameters on Target:
• Number of user connections : should be set to accommodate the migration of multiple tables in parallel and partitioned tables.
• Number of sort buffers : Default is 500 which is sufficient during migration process.

During migration process 2 databases will be created on source server, make sure that you have enough free space in devices(approx 20% of Databases to be migrated)

Migration Process

Execute the sybmigation in GUI mode / commandline.

Following steps are involved:

            Set up target database for migration

           

Select database and click Add path below

Migrate Data

Validate Data

Post Migration

• Need to create statistics for NonIndex Columns
• Any message requiring user attention preceded by the word “attention” and logged in the migration log.
• Run the object migrations status report to verify that all objects have been migrated