Switch to standard view 
  Sybase logo
 
 
 



Contents

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

Introduction

This tutorial demonstrates how to migrate a Microsoft SQL Server database to a Sybase SQL Anywhere database. To migrate the schema and data, we use the Migration Wizard included with SQL Anywhere. To migrate database logic (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 SQL Server sample database "Northwind" into a new SQL Anywhere database.

Required Software

Ensure that the Northwind sample database is properly attached and configured in your SQL Server installation.

SQL Server is configured using Mixed Mode Authentication and the system administration "sa" is enabled.

This tutorial is written for the Windows operating system.

Overview

This tutorial covers the following areas:

SQL Server also includes a more elaborate sample database called AdventureWorks. A SQL Anywhere version of this database is available here: http://www.sybase.com/detail?id=1061455.

Create an ODBC Data Source for the Northwind SQL Server Database

The SQL Anywhere data migration tools require an ODBC connection to the SQL Server database. Sybase Central creates a "remote database server" using an ODBC data source, allowing you to access and query the SQL Server 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 "SQL Server " from the list and click Finish.
  3. The Microsoft SQL Server DSN Confuguration wizard appears. Enter the following options to connect to the database (click Next or Back to navigate the wizard):

    • Data source name is Northwind-SQLServer.
    • Server name or IP address of the SQL Server instance you want to connect to.
    • Proper client configuration (TCP/IP, server alias name, etc).
    • Proper login credentials (login ID and password).
    • Change the default database to Northwind.
    • Use the default settings for all other options.
  4. Click Finish to complete the wizard. Click OK dismiss the SQL Server Setup dialog.
  5. 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 northwind.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 "northwind" SQL Anywhere database using the default user "DBA". This database is initially 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 SQL Server to SQL Anywhere

To migrate the schema and data from the SQL Server 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 (SQL Server) 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 "northwind" database and click Next.

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

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

  1. Select "Microsoft SQL Server " from the remote server type list and click Next.

  1. The wizard will now use ODBC to connect to the SQL Server database. Enter Northwind-SQLServer 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 (SQL Server) has no knowledge of this user, therefore you must create an external login that maps the DBA user (from SQL Anywhere) to the SQL Server user that can manipulate the Northwind database schema ("sa" in this case). Check Create an external login for the current user and enter the correct credentials to log in to the SQL Server 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 "NorthwindMSSQLRemoteServer" and click Next.

  1. All the tables in the Northwind SQL Server 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" or to any other user. For simplicity, we'll add the tables to the DBA user. Select it 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 Northwind SQL Server database to the Northwind 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 SQL Server Northwind database were migrated to the Northwind SQL Anywhere database.

  1. Double-click the table "Customers" to see the column definitions, matching the schema from the SQL Server 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, indexes, and much more.

Migrate Views

The Migrate Database Wizard will not migrate any views or materialized views defined in the SQL Server database, so you must perform this step manually. Fortunately, the queries used to generate the views in SQL Server are very similar to the ones in SQL Anywhere, only requiring a few modifications. There are 16 views defined in the original Northwind SQL Server database. The SQL code below is derived from the file instnwnd.sql included in the Northwind sample database for SQL Server. You can also use SQL Server Management Studio to see the SELECT statement for each view.

  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 Invoices and click Next.

  1. Enter the following SQL statement to define the view:
SELECT Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry, 
Orders.CustomerID, Customers.CompanyName AS CustomerName, Customers.Address, Customers.City, Customers.Region,
Customers.PostalCode, Customers.Country, Employees.FirstName + ' ' + Employees.LastName AS Salesperson, Orders.OrderID,
Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Shippers.CompanyName AS ShipperName,
"Order Details".ProductID, Products.ProductName, "Order Details".UnitPrice, "Order Details".Quantity, "Order Details".Discount,
CONVERT(money, ("Order Details".UnitPrice * "Order Details".Quantity) * (1 - "Order Details".Discount) / 100) * 100 AS ExtendedPrice,
Orders.Freight
FROM Shippers INNER JOIN (
Products INNER JOIN (
(Employees INNER JOIN
(Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID)
ON Employees.EmployeeID = Orders.EmployeeID)
INNER JOIN "Order Details" ON Orders.OrderID = "Order Details".OrderID)
ON Products.ProductID = "Order Details".ProductID)
ON Shippers.ShipperID = Orders.ShipVia
  1. Notice the differences between the SQL dialects in SQL Server and SQL Anywhere, although the code is almost identical.
  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 the 15 remaining views.

SQL statement for the Alphabetical list of products view:

SELECT Products.ProductID,
Products.ProductName,
Products.SupplierID,
Products.CategoryID,
Products.QuantityPerUnit,
Products.UnitPrice,
Products.UnitsInStock,
Products.UnitsOnOrder,
Products.ReorderLevel,
Products.Discontinued,
Categories.CategoryName
FROM Categories INNER JOIN
Products ON Categories.CategoryID = Products.CategoryID
WHERE (Products.Discontinued = 0)

SQL statement for the Product Sales for 1997 view:

SELECT Categories.CategoryName,
Products.ProductName,
SUM(CONVERT(money, ("Order Details".UnitPrice * "Order Details".Quantity)
* (1 - "Order Details".Discount) / 100) * 100) AS ProductSales
FROM (Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID)
INNER JOIN
(Orders INNER JOIN "Order Details" ON Orders.OrderID = "Order Details".OrderID)
ON Products.ProductID = "Order Details".ProductID
WHERE (Orders.ShippedDate BETWEEN '19970101' AND '19971231')
GROUP BY Categories.CategoryName, Products.ProductName

SQL statement for the Category Sales for 1997 view:

SELECT CategoryName, SUM(ProductSales) AS CategorySales
FROM "Product Sales for 1997"
GROUP BY CategoryName

SQL statement for the Current Product List view:

SELECT ProductID, ProductName
FROM Products AS Product_List
WHERE (Discontinued = 0)

SQL statement for the Customer and Suppliers by City view:

SELECT City, CompanyName, ContactName, 'Customers' AS Relationship
FROM Customers
UNION
SELECT City, CompanyName, ContactName, 'Suppliers'
FROM Suppliers

SQL statement for the Order Details Extended view:

SELECT "Order Details".OrderID,
"Order Details".ProductID,
Products.ProductName,
"Order Details".UnitPrice,
"Order Details".Quantity,
"Order Details".Discount,
CONVERT(money, ("Order Details".UnitPrice * "Order Details".Quantity) * (1 - "Order Details".Discount) / 100)
* 100 AS ExtendedPrice
FROM Products INNER JOIN "Order Details" ON Products.ProductID = "Order Details".ProductID

SQL statement for the Orders Qry view:

SELECT Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, 
Orders.ShipVia, Orders.Freight, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion,
Orders.ShipPostalCode, Orders.ShipCountry, Customers.CompanyName, Customers.Address, Customers.City,
Customers.Region, Customers.PostalCode, Customers.Country
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

SQL statement for the Order Subtotals view:

SELECT OrderID, SUM(CONVERT(money, (UnitPrice * Quantity) * (1 - Discount) / 100) * 100) AS Subtotal
FROM "Order Details"
GROUP BY OrderID

SQL statement for the Products Above Average Price view:

SELECT ProductName, UnitPrice
FROM Products
WHERE (UnitPrice >
(SELECT AVG(UnitPrice) AS Expr1
FROM Products))

SQL statement for the Products by Category view:

SELECT Categories.CategoryName, Products.ProductName, Products.QuantityPerUnit, Products.UnitsInStock, 
Products.Discontinued
FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
WHERE (Products.Discontinued <> 1)

SQL statement for the Quarterly Orders view:

SELECT DISTINCT Customers.CustomerID, Customers.CompanyName, Customers.City, Customers.Country
FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE (Orders.OrderDate BETWEEN '19970101' AND '19971231')

SQL statement for the Sales by Category view:

SELECT Categories.CategoryID,
Categories.CategoryName,
Products.ProductName,
SUM("Order Details Extended".ExtendedPrice) AS ProductSales
FROM Categories INNER JOIN
(Products INNER JOIN
(Orders INNER JOIN "Order Details Extended" ON Orders.OrderID = "Order Details Extended".OrderID)
ON Products.ProductID = "Order Details Extended".ProductID)
ON Categories.CategoryID = Products.CategoryID
WHERE (Orders.OrderDate BETWEEN '19970101' AND '19971231')
GROUP BY Categories.CategoryID, Categories.CategoryName, Products.ProductName

SQL statement for the Sales Totals by Amount view:

SELECT "Order Subtotals".Subtotal AS SaleAmount,
Orders.OrderID, Customers.CompanyName, Orders.ShippedDate
FROM Customers INNER JOIN
(Orders INNER JOIN "Order Subtotals"
ON Orders.OrderID = "Order Subtotals".OrderID)
ON Customers.CustomerID = Orders.CustomerID
WHERE ("Order Subtotals".Subtotal > 2500)
AND (Orders.ShippedDate BETWEEN '19970101' AND '19971231')

SQL statement for the Summary of Sales by Quarter view:

SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal
FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID
WHERE (Orders.ShippedDate IS NOT NULL)

SQL statement for the Summary of Sales by Year view:

SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal
FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID
WHERE (Orders.ShippedDate IS NOT NULL)

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 SQL Server 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 instnwnd.sql included in the Northwind sample database for SQL Server.

Stored Procedures

There are 7 stored procedures defined in the original Northwind SQL Server database. Migrating these to our Northwind SQL Anywhere database requires a few small modifications.

  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 CustOrderHist. Click Next.

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

  1. There's no need to provide comments to the procedure. 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 CustOrderHist procedure:

ALTER PROCEDURE "DBA"."CustOrderHist"( IN @CustomerID NCHAR(5) )
BEGIN
SELECT ProductName, SUM(Quantity) AS Total
FROM Products P, "Order Details" OD, Orders O, Customers C
WHERE C.CustomerID = @CustomerID
AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID
GROUP BY ProductName
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 the 6 remaining procedures.

SQL code for the CustOrdersDetail procedure:

ALTER PROCEDURE "DBA"."CustOrdersDetail"( IN @OrderID INT )
BEGIN
SELECT ProductName,
ROUND(Od.UnitPrice, 2) AS UnitPrice,
Quantity,
CONVERT(INT, Discount * 100) AS Discount,
ROUND(CONVERT(money, Quantity * (1 - Discount) * Od.UnitPrice), 2) AS ExtendedPrice
FROM Products P, "Order Details" Od
WHERE Od.ProductID = P.ProductID AND Od.OrderID = @OrderID
END

SQL code for the CustOrdersOrders procedure:

ALTER PROCEDURE "DBA"."CustOrdersOrders"( IN @CustomerID NCHAR(5) )
BEGIN
SELECT OrderID, OrderDate, RequiredDate, ShippedDate
FROM Orders
WHERE CustomerID = @CustomerID
ORDER BY OrderID
END

SQL code for the Employee Sales by Country procedure:

ALTER PROCEDURE "DBA"."Employee Sales by Country"( IN @Beginning_Date DATETIME, IN @Ending_Date DATETIME )
BEGIN
SELECT Employees.Country,
Employees.LastName,
Employees.FirstName,
Orders.ShippedDate,
Orders.OrderID,
"Order Subtotals".Subtotal AS SaleAmount
FROM Employees INNER JOIN
(Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID)
ON Employees.EmployeeID = Orders.EmployeeID
WHERE Orders.ShippedDate BETWEEN @Beginning_Date AND @Ending_Date
END

SQL code for the SalesByCategory procedure:

ALTER PROCEDURE "DBA"."SalesByCategory"( IN @CategoryName NVARCHAR(15), IN @OrdYear NVARCHAR(4) DEFAULT '1998' )
BEGIN
    IF @OrdYear != '1996' AND @OrdYear != '1997' AND @OrdYear != '1998' THEN
        SET @OrdYear = '1998'
    ENDIF;
    SELECT ProductName,
           ROUND(SUM(CONVERT(DECIMAL(14,2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0) AS TotalPurchase
    FROM "Order Details" OD, Orders O, Products P, Categories C
    WHERE OD.OrderID = O.OrderID 
        AND OD.ProductID = P.ProductID 
        AND P.CategoryID = C.CategoryID
        AND C.CategoryName = @CategoryName
        AND SUBSTRING(CONVERT(NVARCHAR(22), O.OrderDate, 111), 1, 4) = @OrdYear
    GROUP BY ProductName
    ORDER BY ProductName
END

SQL code for the Sales by Year procedure:

ALTER PROCEDURE "DBA"."Sales by Year"( IN @Beginning_Date DATETIME, IN @Ending_Date DATETIME )
BEGIN
SELECT Orders.ShippedDate,
Orders.OrderID,
"Order Subtotals".Subtotal,
DATENAME(yy,ShippedDate) AS Year
FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID
WHERE Orders.ShippedDate BETWEEN @Beginning_Date AND @Ending_Date
END

SQL code for the Ten Most Expensive Products procedure:

ALTER PROCEDURE "DBA"."Ten Most Expensive Products"()
BEGIN
SELECT TOP 10 Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice
FROM Products
ORDER BY Products.UnitPrice DESC
END

User-Defined Functions and Triggers

The Northwind sample database does not contain any user-defined functions of triggers. However, the process of converting these objects from SQL Server to SQL Anywhere is the same as converting stored procedures. Make sure to familiarize yourself with the semantics of how SQL Anywhere defines and calls functions and triggers because they are slightly different than SQL Server.

If you'd like to see examples of converting functions and triggers from SQL Server to SQL Anywhere, please refer to the sample database AdventureWorks 2008 for SQL Anywhere.

Ensure and Verify Correct Behaviour

Once the schema, data and logic are migrated, it is important to make sure the new (SQL Anywhere) database behaves identically to the old (SQL Server) 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.

  1. In left panel of Sybase Central, right-click "northwind - DBA" and select Open Interactive SQL from the popup menu. This will launch Interactive SQL (the graphical tool to execute ad-hoc queries) and automatically connects it to the Northwind SQL Anywhere database.
  2. Type the following SQL query to retrieve the contents of the Customers table:

SELECT * FROM Customers;

  1. Run the SQL query by pressing F5 or clicking the execute button in the toolbar. The results from this query appear in the bottom panel.

  1. Verify that the result set is what you expect. Run a few other queries to test the views and stored procedures. Here are some examples:

SELECT * FROM Invoices;

SELECT * FROM "Category Sales for 1997";

CALL CustOrdersOrders('LAUGB');

CALL "Employee Sales by Country"( '1998-01-01', NOW() );

CALL SalesByCategory('Beverages');

SELECT * FROM "Ten Most Expensive Products"();

At this point, the remote server you created earlier (NorthwindMSSQLRemoteServer) inside the SQL Anywhere database mapping to SQL Server 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 SQL Server. 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.



Back to Top
© Copyright 2010, Sybase Inc.