Switch to standard view 
  Sybase logo
 
 
 



Contents

Introduction
Required Software
Overview
Create an ODBC Data Source for the Sakila MySQL Database
Create a New SQL Anywhere Database
Migrate Schema and Data from MySQL to SQL Anywhere
Examine the Migrated Database Schema and Data
Migrate Views
Migrate Database Logic
  User-Defined Functions
  Stored Procedures
  Triggers
Ensure Correct Behaviour
Verify Correct Behaviour
Conclusion

Introduction

This tutorial demonstrates how to migrate a MySQL database to a SQL Anywhere database. To migrate the schema and data, we use the Migration Wizard included with SQL Anywhere. To migrate store procedures, user-defined functions, and triggers, we perform slight modifications to the T-SQL code to ensure the correct behaviour is achieved. This tutorial migrates the MySQL sample database "Sakila" into a new SQL Anywhere database.

Required Software

Ensure that the Sakila sample database is properly installed and configured in your MySQL Community Server installation.

Optional: Once you complete this tutorial, you'll have a SQL Anywhere version of the Sakila database. If you'd like the reload script to generate the schema of this database (but no data), download this file: reload-sakila.sql.

This tutorial is written for the Windows operating system.

Overview

This tutorial covers the following areas:

The whitepaper Migrating a MySQL Database to SQL Anywhere is also available and describes the language differences between SQL Anywhere and MySQL.

Create an ODBC Data Source for the Sakila MySQL Database

The SQL Anywhere migration tools require an ODBC connection to the MySQL database. Sybase Central creates a "remote database server" using the ODBC data source, allowing you to access and query the MySQL database using the SQL Anywhere administration tools (Sybase Central and Interactive SQL). This functionality is called Remote Data Access.

  1. Open the ODBC Administrator (Start -> Programs -> SQL Anywhere 12 -> Administration Tools -> ODBC Data Source Administrator) and click Add.
  2. Select "MySQL ODBC 5.1 Driver" from the list and click Finish.
  3. Name the data source Sakila-MySQL and enter the appropriate parameters to connect to the database.

  1. Click OK to close the MySQL Connector/ODBC dialog. Click OK to close the ODBC Administrator.

Create a New SQL Anywhere Database

All remaining operations will be performed using SQL Anywhere's administration tool Sybase Central.

  1. Launch Sybase Central (Start -> Programs -> SQL Anywhee 12 -> Administration Tools -> Sybase Central). Close the Tips dialog and/or Welcome dialog if they appear.
  2. From the Tools menu, select SQL Anywhere 12 -> Create Database. The Create Database Wizard appears.
  3. Choose Create a database on this computer and click Next.

  1. Save the database file as sakila.db.

  1. At this point you may change any default settings for the new database (page size, encryption, case sensitivity, etc.), but that is not required for this tutorial. Click Finish to create the database.
  2. When the database is successfully created, click Close to dismiss the Create Database Wizard.

Sybase Central automatically connects to the newly created "sakila" SQL Anywhere database using the default user "DBA". This database is empty (no schema or data).

Note: Alternatively, you can create a new SQL Anywhere database using the dbinit command-line utility and then connect to this database using Sybase Central.

Migrate Schema and Data from MySQL to SQL Anywhere

To migrate the schema and data from the MySQL database to our newly created SQL Anywhere database, we use the Migrate Database Wizard. This wizard takes care of creating all tables, indexes and key relationships to match the schema of the source (MySQL) database. It then copies all the rows from the source to the target (SQL Anywhere) database.

  1. In Sybase Central, from the Tools menu, select SQL Anywhere 12 -> Migrate Database to launch the wizard.
  2. Select the "sakila" database and click Next.

  1. The database objects we are migrating are stored in the "sakila" MySQL database. Enter sakila in the text box and click Create Remote Server Now.

  1. Enter SakilaMySQLRemoteServer as the remote server name and click Next.

  1. Select "MySQL" from the remote server type list and click Next.

  1. The wizard will now use ODBC to connect to the MySQL database. Enter MySQL-Sakila as the connection information (it's the ODBC data source created earlier) and click Next.

  1. You can choose to make the remote server to be a read-only data source. Simply leave the default setting and click Next.
  2. Your connection to the SQL Anywhere database is under the name "DBA". The remote server (MySQL) has no knowledge of this user, therefore you must create an external login that maps the DBA user (from SQL Anywhere) to the MySQL user that owns the Sakila database schema ("root" in this case). Check Create an external login for the current user and enter the correct credentials to log in to the MySQL database server. Click Next.

  1. A summary page appears showing the SQL statements that SQL Anywhere will execute to create the remote server. Click Finish to create the remote server.
  2. You are now back at the Migrate Database Wizard. Select the newly created "SakilaMySQLRemoteServer" and click Next.

  1. All the tables in the Sakila MySQL database appear in the left list box. We'll be migrating all the tables, so click Add All, then Next.

  1. At this point you can choose to add the tables to the user "DBA"; however, let's create a new user instead to own the Sakila database objects. Click Create New User Now.
  2. The Create User Wizard appears. Enter sakila for the new user's name and click Next.

  1. Make sure to check Assign a password to this user and use the password sakila.

  1. There's no need to change the default settings for the new user, so click Finish to complete the wizard.
  2. Back at the Migrate Database Wizard, select the newly created user "sakila" and click Next.

  1. The Specify Migration Options page appears. You have the option to migrate the foreign keys and/or data. The migration process uses proxy tables to query the tables in the remote database server. Leave all options checked and click Next.

  1. The Summary page appears displaying the SQL statements that will be executed to migrate the schema and data. These statements, composed of system stored procedures, are executed by the Migrate Database Wizard, however it is possible to execute them inside an application. The migration system stored procedures are useful when you need to migrate specific tables or remove foreign key mappings.

  1. Click Finish to begin the migration. The Migrate Database Wizard will migrate the schema and data from the Sakila MySQL database to the Sakila SQL Anywhere database. When the operation completes successfully, click Close to dismiss the wizard.

Examine the Migrated Database Schema and Data

  1. In Sybase Central, double-click "Tables" in the right pane. All the tables from the MySQL Sakila database were migrated to the Sakila SQL Anywhere database. Notice all tables belong to the user "sakila".

  1. Double-click the table "Actor" to see the column definitions, matching the schema from the MySQL database.
  2. You can look at the different tabs to see the constraints, foreign keys, indexes, etc. Click on the "Data" tab to see the rows.

You can examine the other objects in the database using Sybase Central, including users, groups, remote servers, stored procedures, and much more.

Migrate Views

The Migrate Database Wizard will not migrate any views or materialized views defined in the MySQL database, so you must perform this step manually. Fortunately, the queries used to generate the views in MySQL are very similar to the ones in SQL Anywhere, only requiring a few modifications. There are seven views defined in the original Sakila MySQL database. The SQL code below is derived from the file sakila-schema.sql included in the Sakila MySQL sample database Version 0.8.

  1. In Sybase Central, switch to the Folders view by choosing Folders from the View menu.
  2. In the left pane, right-click "Views" and select New -> View from the popup menu.
  3. Enter the view's name as actor_info and select "sakila" as its owner. Click Next.

  1. Enter the following SQL statement to define the view:
SELECT a.actor_id, a.first_name, a.last_name,
  LIST( DISTINCT (SELECT c.name ||  ': ' || 
(SELECT LIST (DISTINCT f.title, ', ' ORDER BY f.title) FROM sakila.film f
INNER JOIN sakila.film_category fc
ON f.film_id = fc.film_id
INNER JOIN sakila.film_actor fa
ON f.film_id = fa.film_id
WHERE fc.category_id = c.category_id
AND fa.actor_id = a.actor_id)
), '; ' ORDER BY c.name) AS film_info
FROM sakila.actor a
LEFT JOIN sakila.film_actor fa
ON a.actor_id = fa.actor_id
LEFT JOIN sakila.film_category fc
ON fa.film_id = fc.film_id
LEFT JOIN sakila.category c
ON fc.category_id = c.category_id
GROUP BY a.actor_id, a.first_name, a.last_name
  1. Notice the differences between the SQL dialects in MySQL and SQL Anywhere.
  2. Complete the wizard by clicking Finish. Sybase Central will issue an error if there are problems with the SELECT statement.
  3. Click the Data tab on the right pane to see the rows.

Repeat the steps above to create six additional views: customer_list, film_list, nicer_but_slower_film_list, sales_by_film_category, sales_by_store, and staff_list.

SQL statement for the customer_list view:

SELECT cu.customer_id AS ID, (cu.first_name || ' ' || cu.last_name) AS name, a.address AS address, a.postal_code AS 'zip code',
       a.phone AS phone, city.city AS city, country.country AS country,
       ( CASE cu.active WHEN 1 THEN 'active' ELSE '' END ) AS notes,
cu.store_id AS SID
FROM sakila.customer AS cu JOIN sakila.address AS a ON cu.address_id = a.address_id JOIN sakila.city ON a.city_id = city.city_id JOIN sakila.country ON city.country_id = country.country_id

SQL statement for the film_list view:

SELECT film.film_id AS FID, film.title AS title, film.description AS description, category.name AS category, film.rental_rate AS price,
film.length AS length, film.rating AS rating, LIST (actor.first_name || ' ' || actor.last_name, ', ') AS actors
FROM sakila.category LEFT JOIN sakila.film_category ON category.category_id = film_category.category_id LEFT JOIN sakila.film ON film_category.film_id = film.film_id
JOIN sakila.film_actor ON film.film_id = film_actor.film_id JOIN sakila.actor ON film_actor.actor_id = actor.actor_id GROUP BY film.film_id, film.title, film.description, category.name, film.rental_rate, film.length, film.rating

SQL statement for the nicer_but_slower_film_list view:

SELECT film.film_id AS FID, film.title AS title, film.description AS description, category.name AS category, film.rental_rate AS price,
       film.length AS length, film.rating AS rating,
       LIST (UCASE(SUBSTR(actor.first_name, 1, 1)) || (LCASE(SUBSTR(actor.first_name, 2, LENGTH(actor.first_name)))) || ' ' || 
UCASE(SUBSTR(actor.last_name, 1, 1)) || (LCASE(SUBSTR(actor.last_name, 2, LENGTH(actor.last_name)))), ', ') AS actors FROM sakila.category LEFT JOIN sakila.film_category ON category.category_id = film_category.category_id LEFT JOIN sakila.film ON film_category.film_id = film.film_id
JOIN sakila.film_actor ON film.film_id = film_actor.film_id JOIN sakila.actor ON film_actor.actor_id = actor.actor_id
GROUP BY film.film_id, film.title, film.description, category.name, film.rental_rate, film.length, film.rating

SQL statement for the sales_by_film_category view:

SELECT c.name AS category, SUM(p.amount) AS total_sales
FROM sakila.payment AS p
INNER JOIN sakila.rental AS r ON p.rental_id = r.rental_id
INNER JOIN sakila.inventory AS i ON r.inventory_id = i.inventory_id
INNER JOIN sakila.film AS f ON i.film_id = f.film_id
INNER JOIN sakila.film_category AS fc ON f.film_id = fc.film_id
INNER JOIN sakila.category AS c ON fc.category_id = c.category_id
GROUP BY c.name
ORDER BY total_sales DESC

SQL statement for the sales_by_store view:

SELECT (c.city || ',' || cy.country) AS store, (m.first_name || ' ' || m.last_name) AS manager, SUM(p.amount) AS total_sales
FROM sakila.payment AS p
INNER JOIN sakila.rental AS r ON p.rental_id = r.rental_id
INNER JOIN sakila.inventory AS i ON r.inventory_id = i.inventory_id
INNER JOIN sakila.store AS s ON i.store_id = s.store_id
INNER JOIN sakila.address AS a ON s.address_id = a.address_id
INNER JOIN sakila.city AS c ON a.city_id = c.city_id
INNER JOIN sakila.country AS cy ON c.country_id = cy.country_id
INNER JOIN sakila.staff AS m ON s.manager_staff_id = m.staff_id
GROUP BY s.store_id, cy.country, c.city, m.last_name, m.first_name
ORDER BY cy.country, c.city

SQL statement for the staff_list view:

SELECT s.staff_id AS ID, (s.first_name || ' ' || s.last_name) AS name, a.address AS address, a.postal_code AS 'zip code', 
       a.phone AS phone, city.city AS city, country.country AS country, s.store_id AS SID
    FROM sakila.staff AS s
    JOIN sakila.address AS a
        ON s.address_id = a.address_id
    JOIN sakila.city
        ON a.city_id = city.city_id
    JOIN sakila.country
        ON city.country_id = country.country_id

Migrate Database Logic

Due to differences in the SQL dialect between databases, there is no tool capable of performing a full migration of stored procedures, user-defined functions, and triggers. However, despite their differences, there are many similarities in the SQL dialect between MySQL and SQL Anywhere. In some instances, the same SQL statements will work properly in both products, but typically you will need to make a few modifications to ensure the business logic executes as expected. The SQL code below is derived from the file sakila-schema.sql included in the Sakila MySQL sample database Version 0.8.

User-Defined Functions

There are three functions defined in the original Sakila MySQL database. Migrating these to our Sakila SQL Anywhere database requires a few small modifications.

  1. In Sybase Central, right-click "Procedures and Functions" in the left pane and select New -> Function from the popup menu.
  2. The Create Function Wizard Appears. Enter the function name get_customer_balance and change the owner to "sakila". Click Next.

  1. You can write your function code using different SQL dialects and languages. Select Watcom-SQL and click Next.

  1. This function returns a DECIMAL data type of size 5 and scale 2. Name the return variable current_amount_owing.

  1. There is no need to change any settings, so click Finish to complete the wizard.

You are now presented with the code editor to write the SQL code for the function. Enter the following code for the get_customer_balance function:

ALTER FUNCTION "sakila"."get_customer_balance"( p_customer_id INT, p_effective_date DATETIME )
RETURNS DECIMAL(5,2)
DETERMINISTIC
BEGIN
DECLAR
E current_amount_owing DECIMAL(5,2);

DECLARE v_rentfees DECIMAL(5,2);
DECLARE v_overfees INTEGER;
DECLARE v_payments DECIMAL(5,2);

SELECT IFNULL( SUM(film.rental_rate), 0, SUM(film.rental_rate) ) INTO v_rentfees
FROM film, inventory, rental
WHERE film.film_id = inventory.film_id
AND inventory.inventory_id = rental.inventory_id
AND rental.rental_date <= p_effective_date
AND rental.customer_id = p_customer_id;

SELECT IFNULL( SUM( IF (DAYS(rental.return_date)-58) - (DAYS(rental.rental_date)-58 ) > film.rental_duration
THEN ( DAYS(rental.return_date)-58) - (DAYS(rental.rental_date)-58) - film.rental_duration
ELSE 0
ENDIF)
, 0
, SUM (IF (DAYS(rental.return_date)-58) - (DAYS(rental.rental_date)-58 ) > film.rental_duration
THEN ( DAYS(rental.return_date)-58) - (DAYS(rental.rental_date)-58) - film.rental_duration
ELSE 0
ENDIF)
) INTO v_overfees
FROM rental, inventory, film
WHERE film.film_id = inventory.film_id
AND inventory.inventory_id = rental.inventory_id
AND rental.rental_date <= p_effective_date
AND rental.customer_id = p_customer_id;

SELECT IFNULL( SUM(payment.amount), 0, SUM(payment.amount) ) INTO v_payments
FROM payment
WHERE payment.payment_date <= p_effective_date
AND payment.customer_id = p_customer_id;

SET current_amount_owing = v_rentfees + v_overfees - v_payments;

RETURN current_amount_owing;
END
  1. Save the function by clicking on the Save button on the toolbar or using the File menu. Sybase Central will inform you if there are any errors in the code.

Repeat the steps above to create two additional functions: (1) inventory_held_by_customer returns the variable "v_customer_id" of type INT and, (2) inventory_in_stock returns the variable "in_stock" of type BIT.

SQL code for the inventory_held_by_customer function:

ALTER FUNCTION "sakila"."inventory_held_by_customer"( p_inventory_id INT )
RETURNS INTEGER
DETERMINISTIC
BEGIN
    DECLARE v_customer_id INTEGER;
    SELECT customer_id INTO v_customer_id
        FROM rental
        WHERE return_date IS NULL
        AND inventory_id = p_inventory_id; 
    RETURN v_customer_id;
END

SQL code for the inventory_in_stock function:

ALTER FUNCTION "sakila"."inventory_in_stock"( p_inventory_id INT )
RETURNS BIT
DETERMINISTIC
BEGIN
    DECLARE in_stock BIT;
    DECLARE v_rentals INT;
    DECLARE v_out     INT;
    SELECT COUNT(*) INTO v_rentals
        FROM rental
        WHERE inventory_id = p_inventory_id;
    IF v_rentals = 0 THEN
        SET in_stock = 1;
    END IF;
    SELECT COUNT(rental_id) INTO v_out
        FROM inventory LEFT JOIN rental
        WHERE inventory.inventory_id = p_inventory_id
        AND rental.return_date IS NULL;
    IF v_out > 0 THEN
        SET in_stock = 0;
    ELSE
        SET in_stock = 1;
    END IF;
    RETURN in_stock;
END

Stored Procedures

There are three stored procedures defined in the original Sakila MySQL database. Migrating these to our Sakila SQL Anywhere database requires a few small modifications, as well as two additional procedures to handle situations where both a result set and a return value (OUT parameter) is required.

  1. In Sybase Central, right-click "Procedures and Functions" in the left pane and select New -> Procedure from the popup menu.
  2. The Create Procedure Wizard Appears. Enter the procedure name film_in_stock and change the owner to "sakila". Click Next.

  1. Similar to creating functions, use the Watcom-SQL dialect and click Next.
  2. Click Finish to complete the wizard.

The code editor appears again for you to write the SQL code for the procedure. Enter the following code for the film_in_stock procedure:

ALTER PROCEDURE "sakila"."film_in_stock"( IN p_film_id INT, IN p_store_id INT )
BEGIN
SELECT inventory_id
FROM inventory
WHERE film_id = p_film_id
AND store_id = p_store_id
AND inventory_in_stock(inventory_id) = 1;
END
  1. Save the procedure by clicking on the Save button on the toolbar or using the File menu. Sybase Central will inform you if there are any errors in the code.

Repeat the steps above to create four additional functions: film_in_stock_count, film_not_in_stock, film_not_in_stock_count, and rewards_report.

SQL code for the film_in_stock_count procedure:

ALTER PROCEDURE "sakila"."film_in_stock_count"( IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT )
BEGIN
    SELECT COUNT(*) INTO p_film_count
        FROM inventory
        WHERE film_id = p_film_id
        AND store_id = p_store_id
        AND inventory_in_stock(inventory_id) = 1;
END

SQL code for the film_not_in_stock procedure:

ALTER PROCEDURE "sakila"."film_not_in_stock"( IN p_film_id INT, IN p_store_id INT )
BEGIN
    SELECT inventory_id
        FROM inventory
        WHERE film_id = p_film_id
        AND store_id = p_store_id
        AND inventory_in_stock(inventory_id) = 0;
END

SQL code for the film_not_in_stock_count procedure:

ALTER PROCEDURE "sakila"."film_not_in_stock_count"( IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT )
BEGIN
    SELECT COUNT(*) INTO p_film_count
        FROM inventory
        WHERE film_id = p_film_id
        AND store_id = p_store_id
        AND inventory_in_stock(inventory_id) = 0;

END

SQL code for the rewards_report procedure:

ALTER PROCEDURE "sakila"."rewards_report"( IN min_monthly_purchases TINYINT, IN min_dollar_amount_purchased DECIMAL(10,2) )
BEGIN
    DECLARE last_month_start DATE;
    DECLARE last_month_end DATE;
    IF min_monthly_purchases = 0 THEN
        SELECT 'Minimum monthly purchases parameter must be > 0';
        RETURN;
    END IF;
    IF min_dollar_amount_purchased = 0.00 THEN
        SELECT 'Minimum monthly dollar amount purchased parameter must be > $0.00';
        RETURN;
    END IF;
    SET last_month_start = DATE( DATEADD(MONTH, -1, NOW(*)) );
    SET last_month_start = DATE( YEAR(last_month_start) || '-' || MONTH(last_month_start) || '-01' );
    SET last_month_end = DATE( DATEADD(DAY, -1, DATEADD(MONTH, 1, last_month_start)) );
    CREATE LOCAL TEMPORARY TABLE tmpCustomer (customer_id UNSIGNED SMALLINT NOT NULL PRIMARY KEY);
    INSERT INTO tmpCustomer (customer_id)
        SELECT p.customer_id
            FROM payment AS p
            WHERE DATE(p.payment_date) BETWEEN last_month_start AND last_month_end
            GROUP BY customer_id
            HAVING SUM(p.amount) > min_dollar_amount_purchased
                AND COUNT(customer_id) > min_monthly_purchases;
    SELECT c.*
        FROM tmpCustomer AS t
        INNER JOIN customer AS c
            ON t.customer_id = c.customer_id;
    DROP TABLE tmpCustomer;
END

Triggers

There are three triggers defined in the original Sakila MySQL database and migrating these to our Sakila SQL Anywhere database requires a few small modifications.

  1. In Sybase Central, right-click "Triggers" in the left pane and select New -> Trigger from the popup menu.
  2. The Create Trigger Wizard Appears. Select the table "film" and enter the trigger name ins_film. Click Next.

  1. Check the Insert event and click Next.

  1. You can create row-level or statement-level triggers, depending on when you want the trigger to be fired. Select Row-level and click Next.

  1. You can also choose whether the trigger is fired before, instead of, or after the event, as well as its execution order. Select Before the event and click Finish to complete the wizard.

The code editor appears again for you to write the SQL code for the trigger. Enter the following code for the ins_film trigger:

ALTER TRIGGER "ins_film" BEFORE INSERT
ORDER 1 ON "sakila"."film"
REFERENCING NEW AS new_film
FOR EACH ROW
BEGIN
    INSERT INTO film_text (film_id, title, description) VALUES (new_film.film_id, new_film.title, new_film.description);
END
  1. Save the trigger by clicking on the Save button on the toolbar or using the File menu. Sybase Central will inform you if there are any errors in the code.

Repeat the steps above to create two additional triggers on the film table: upd_film (define as AFTER UPDATE event) and del_film (defined as AFTER DELETE event).

SQL code for the upd_film trigger:

ALTER TRIGGER "upd_film" AFTER UPDATE
ORDER 1 ON "sakila"."film"
REFERENCING OLD AS old_film NEW AS new_film
FOR EACH ROW
BEGIN
    IF (old_film.title != new_film.title) OR (old_film.description != new_film.description)
THEN
UPDATE film_text SET title = new_film.title, description = new_film.description, film_id = new_film.film_id
WHERE film_id = old_film.film_id;
END IF; END

SQL code for the del_film trigger:

ALTER TRIGGER "del_film" AFTER DELETE
ORDER 1 ON "sakila"."film"
REFERENCING OLD AS old_film
FOR EACH ROW
BEGIN
    DELETE FROM film_text WHERE film_id = old_film.film_id;
END

Ensure Correct Behaviour

Once the schema, data and logic are migrated, it is important to make sure the new database behaves identically to the old database. Some application testing will help here, as well a looking at any SQL scripts used to generate the original database. In the case of the Sakila database, there are three additional triggers that are created after the inital data is loaded: customer_create_date, payment_date, and rental_date. These can be found by looking at the file sakila-data.sql and need to be migrated to ensure correct behaviour.

Create three triggers: customer_create_date (define as BEFORE INSERT event on customer table), payment_date (define as BEFORE INSERT event on payment table), and rental_date (define as BEFORE INSERT event on rental table).

SQL code for the customer_create_date trigger:

ALTER TRIGGER "customer_create_date" BEFORE INSERT
ORDER 1 ON "sakila"."customer"
REFERENCING NEW AS new_create_date
FOR EACH ROW
BEGIN
    SET new_create_date.create_date = NOW();
END

SQL code for the payment_date trigger:

ALTER TRIGGER "payment_date" BEFORE INSERT
ORDER 1 ON "sakila"."payment"
REFERENCING NEW AS new_payment_date
FOR EACH ROW
BEGIN
    SET new_payment_date.payment_date = NOW();
END

SQL code for the rental_date trigger:

ALTER TRIGGER "rental_date" BEFORE INSERT
ORDER 1 ON "sakila"."rental"
REFERENCING NEW AS new_rental_date
FOR EACH ROW
BEGIN
    SET new_rental_date.rental_date = NOW();
END

Verify Correct Behaviour

The last step in the migration process is to verify that the migrated (SQL Anywhere) database behaves just like the original (MySQL) database. You will likely need to perform your standard testing procedures for the application and confirm there is no unexpected behaviour. At a minimum, you should execute a few SQL queries to ensure the result sets are correct.

At this point, the remote server you created earlier (SakilaMySQLRemoteServer) inside the SQL Anywhere database mapping to MySQL is no longer needed, so you can delete it using Sybase Central.

Conclusion

SQL Anywhere provides a Database Migration Wizard and a set of system stored procedures to quickly enable migration of data from different databases, such as MySQL. Migrating table objects is fairly straight forward using the wizard. When using custom data types, or when a more flexible approach is required, use the migration stored procedures as they will offer you much more granularity for the operations you want to perform.

To migrate views and database logic, a manual approach is more appropriate due to the differences in the SQL dialect between SQL Anywhere and other databases. Fortunately, there are many similarities in the SQL code which makes the migration process easier.

Once you've migrated the schema and data, ensure that your application behaves properly using the new SQL Anywhere database. Execute your testing procedures to verify there is no unwarranted behaviour.

For more information regarding migrating MySQL database to SQL Anywhere, please refer to the whitepaper Migrating a MySQL Database to SQL Anywhere.



Back to Top
© Copyright 2010, Sybase Inc.