This tutorial demonstrates how to migrate a Microsoft SQL Server database to a SAP 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.
This wiki covers the following area:
- Create an ODBC DSN for the Northwind SQL Server database
- Create a new SQL Anywhere database using Sybase Central
- Use the Migration Wizard to migrate the schema and data from the SQL Server database
- Create a remote database server that points to SQL Server
- Migrate the schema and data into the new SQL Anywhere database
- Modify the SQL code to generate the stored procedures and additional database logic inside the SQL Anywhere database
- SQL Anywhere 12.0.1 or later
- Microsoft SQL Server (tested under version 2008 R@ Express, but also works on other versions)
- Northwind sample database
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.
Creating 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.
- Open the ODBC Administrator (Start -> Programs -> SQL Anywhere 12 -> Administration Tools -> ODBC Data Source Administrator) and click Add.
- Select "SQL Server " from the list and click Finish.
- 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.
- Click Finish to complete the wizard. Click OK dismiss the SQL Server Setup 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.
- Launch Sybase Central (Start -> Programs -> SQL Anywhee 12 -> Administration Tools -> Sybase Central). Close the Tips dialog and/or Welcome dialog if they appear.
- From the Tools menu, select SQL Anywhere 12 -> Create Database. The Create Database Wizard appears.
- Choose Create a database on this computer and click Next.
- Save the database file as northwind.db.
- 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.
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.
- In Sybase Central, from the Tools menu, select SQL Anywhere 12 -> Migrate Database to launch the wizard.
- Select the "northwind" database and click Next.
- 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.
- Enter NorthwindMSSQLRemoteServer as the remote server name and click Next.
- Select "Microsoft SQL Server " from the remote server type list and click Next.
- 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.
- You can choose to make the remote server to be a read-only data source. Simply leave the default setting and click Next.
- 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.
- 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.
- You are now back at the Migrate Database Wizard. Select the newly created "NorthwindMSSQLRemoteServer" and click Next.
- 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.
- 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.
- 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.
- 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.
- 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
- 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.
- Double-click the table "Customers" to see the column definitions, matching the schema from the SQL Server database.
- 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.
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.
- In Sybase Central, switch to the Folders view by choosing Folders from the View menu.
- In the left pane, right-click "Views" and select New -> View from the popup menu.
- Enter the view's name as Invoices and click Next.
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,
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
- Notice the differences between the SQL dialects in SQL Server and SQL Anywhere, although the code is almost identical.
- Complete the wizard by clicking Finish. Sybase Central will issue an error if there are problems with the SELECT statement.
- 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:
SQL statement for the Product Sales for 1997 view:
SQL statement for the Category Sales for 1997 view:
SELECT CategoryName, SUM(ProductSales) AS CategorySales
SQL statement for the Current Product List view:
SELECT ProductID, ProductName
SQL statement for the Customer and Suppliers by City view:
SELECT City, CompanyName, ContactName, 'Customers' AS Relationship
SQL statement for the Order Details Extended view:
SELECT "Order Details".OrderID,
SQL statement for the Orders Qry view:
SELECT Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate,
SQL statement for the Order Subtotals view:
SELECT OrderID, SUM(CONVERT(money, (UnitPrice * Quantity) * (1 - Discount) / 100) * 100) AS Subtotal
SQL statement for the Products Above Average Price view:
SELECT ProductName, UnitPrice
SQL statement for the Products by Category view:
SELECT Categories.CategoryName, Products.ProductName, Products.QuantityPerUnit, Products.UnitsInStock,
SQL statement for the Quarterly Orders view:
SELECT DISTINCT Customers.CustomerID, Customers.CompanyName, Customers.City, Customers.Country
SQL statement for the Sales by Category view:
SQL statement for the Sales Totals by Amount view:
SELECT "Order Subtotals".Subtotal AS SaleAmount,
SQL statement for the Summary of Sales by Quarter view:
SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal
SQL statement for the Summary of Sales by Year view:
SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal
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.
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.
- In Sybase Central, right-click "Procedures and Functions" in the left pane and select New -> Procedure from the popup menu.
- The Create Procedure Wizard Appears. Enter the procedure name CustOrderHist. Click Next.
- You can write your procedure code using different SQL dialects and languages. Select Watcom-SQL and click Next.
- 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) )
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
- 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 )
SQL code for the CustOrdersOrders procedure:
ALTER PROCEDURE "DBA"."CustOrdersOrders"( IN @CustomerID NCHAR(5) )
SQL code for the Employee Sales by Country procedure:
ALTER PROCEDURE "DBA"."Employee Sales by Country"( IN @Beginning_Date DATETIME, IN @Ending_Date DATETIME )
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 )
SQL code for the Ten Most Expensive Products procedure:
ALTER PROCEDURE "DBA"."Ten Most Expensive Products"()
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.
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.
- 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.
- Type the following SQL query to retrieve the contents of the Customers table:
SELECT * FROM Customers;
- 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.
- 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 "Employee Sales by Country"( '1998-01-01', NOW() );
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.
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.