Microsoft SQL Server Migration Assistant

In August 2010, Microsoft announced an updated line of SQL Server Migration Assistant products – free tools that accelerate and simplify the migration of various corporate DBMS to Microsoft SQL Server platform.

SQL Server Migration Assistant for MySQL 1.0 software can be used for fast and accurate data migration from the popular open MySQL DBMS to Microsoft SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 platforms. The application supports conversion or migration of MySQL control operators, cursors, DML operators, stored functions, stored procedures, tables, transactions, triggers and views.

Support for the platforms listed above is also implemented in the new version of SQL Server Migration Assistant for Access 4.2, which allows you to minimize the risks and reduce the cost of data migration from Microsoft Access DBMS (Access 97 and later versions).

With the ability to link Access tables to SQL Server tables, end users will not have to give up using familiar applications that interact with the corporate database. Both tools also support Microsoft SQL Azure cloud-based DBMS and allow customers to appreciate the flexibility and scalability of this modern solution.

Customer organizations using Oracle 7.3 DBMS or Sybase ASE 11.9 (and later versions), which would like to switch to an alternative platform, are offered products of SQL Server Migration Assistant series. This software guarantees fast migration to SQL Server 2005, SQL Server 2008 or SQL Server 2008 R2 (SQL Azure is not included in the list of supported platforms).

In the updated line Microsoft developers implemented extended support for 64-bit platforms and ensured compatibility with new versions of common databases, including Access 2010, Oracle 11g R2 and Sybase 15.5.

The latest version of SQL Server, codenamed “Denali”, has a lot of attractive features and will certainly move many companies to move to SQL Server. Despite the high performance and attractive total cost of ownership (TCO) of SQL Server, many organizations are not in a hurry to move to SQL Server, fearing the costs and risks associated with the migration of databases.

Fortunately, Microsoft provides a special tool, SQL Server Migration Assistant (SSMA), to automate the migration process. The latest version of SSMA v.5.1 (released simultaneously with SQL Server Denali CTP3 release) supports migration from Oracle, Sybase, MySQL and Access databases to SQL Server. You can use SSMA to facilitate your database migration project. As an example, let’s take a look at the migration process from an Oracle database – in other DBMS the process and migration steps are the same.

Database access

SSMA automates the migration of most database objects, including stored procedures, functions, packages and triggers. The current SSMA version supports all data types except for a few special object and spatial types. In addition, it may not be possible to automatically convert some complex PL/SQL instructions. SSMA can be used to evaluate the migration of your Oracle database and see if it contains such instructions.

The summary evaluation reports contain the following information:

• Schema tree view: A list of objects in the original Oracle database schema.
• Conversion rate: The percentage of instructions that SSMA is able to convert automatically. In this example (pic.1), SSMA can convert 99.39% of all Oracle instructions and source schema.
• Object count: the number of database objects found in the Oracle schema and the number of objects “with errors” (we’ll talk about them a bit later).
• Conversion Message summary: description of problems found in the migration of the original Oracle schema.

1.Evaluation of Oracle database migration conversion capability

There are three types of messages that you may encounter during the SSMA migration: error, warning and information message.

• An error message appears when SSMA is unable to convert a database object or instruction in a database object.
• The warning message appears when SSMA is able to convert an Oracle instruction, but the instruction resulting from the conversion may in some cases not return the same result as the original one. In particular, SSMA converts SUBSTR() to Oracle to SUBSTRING() to SQL Server. In most cases, SUBSTRING() returns the same results. But in some situations, the results may differ. For example, SUBSTR() in Oracle supports negative character positions in a string. In Oracle, the SUBSTR(“TechNet”,-3,3) expression will return “Net”, and the SUBSTRING(“TechNet”,-3,3) result in SQL Server will return an empty string.
• The information message serves as SSMA to inform the user about the features of conversion of some objects.

Each error message contains a link to the object that caused the error. There is also a view where you can directly compare the original instructions on the left and the conversion result on the right (pic. 2). The bug report also contains an estimate of how many hours of manual conversion are usually required to solve the problem.

2. Examples of migration error messages

Most organizations often evaluate multiple Oracle database schemas. They use the overall conversion speed and overall estimate of manual conversion times to compare and prioritize the Oracle database schema for migration.

Database schema conversion

SSMA provides many options for circuit conversion. For example, you can change the data type mapping. SSMA provides a mapping of data types between Oracle and SQL Server by default. However, you can change the type mapping in one or all tables, for a specific object (such as a storage procedure or function) or for a specific application (such as the data type in a column, in a variable, or in the procedure’s input or output parameter).

The conversion of the database schema is started by clicking the Convert Schema button. After that you can go to another database object and compare the original and converted schema object (pic. 3).

3. Schema conversion presentation

If there is an instruction in the object that SSMA is not able to convert automatically, the program will add a description of the migration error, comment on this instruction or replace it with a common type. The isolation-based approach allows you to continue the database migration and deal with the problem later.

You can also solve the problem and modify the instruction directly in SSMA. For example, in the pic. 3 shows a custom TENURE function that returns data of INTERVAL type, which SQL Server does not support. You can change the return value type to NUMBER (pic. 4) and repeat the function conversion. This solves the problem and results in the return value type being converted to float(53).

4. In SSMA you can change instructions to resolve incompatibility issues

You can also change the converted instructions. For example, you can replace the type of return value from float(53) with INT. Note that all changes made in SSMA are stored locally. Changes made to the original instructions do not apply to the Oracle database production schema.

Similarly, any changes made to the resulting SQL Server instructions do not immediately apply to the server. This allows fixes and improvements to the converted schema to be made without affecting the target server.

To deploy the converted schema on the target SQL Server, right-click the schema name in the SQL Server Metadata Explorer window. You can also generate an entire schema script that can be performed on the target server.

Microsoft SQL Server data migration

After creating the database schema on the SQL Server target server, you can use SSMA to migrate Oracle data. However, SSMA is not the only data migration option – you can also use SQL Server Integration Services (SSIS) for this purpose. At the same time, you can use the same type mapping in SSMA for data migration as for schema conversion. SSMA also solves standard problems with migrating data from Oracle to SQL Server.

For example, Oracle supports a wider range of date types than SQL Server. By default, if this situation is detected, SSMA initiates an error of data migration. You can configure SSMA so that it automatically converts out of range date values to NULL or the nearest supported SQL Server date. To change this parameter, select Tools/Project Setting/General/Data Migration sequentially (pic.5).

5.Error handling options for data migration

When the data migration is complete, SSMA displays a report indicating the number of rows migrated, the proportion of records successfully migrated and the time spent on migration of each table.

Checking data migration

After a successful database migration, you should check it. When migrating from Oracle or Sybase, SSMA allows you to compare the source and the migrated database. You can specify a set of checks, after which SSMA will perform them in the source and the migrated databases. It will compare the results as well as any changes that the tests have made to the tables.

To specify a test, select New Test Cases from the Tester menu. The Test Case Wizard opens and allows you to create test runs. You can also select individual database objects to be checked. For example, there is a procedure called ADD_EMPLOYEE. It inserts a new record into the employee table based on the value passed in the input parameter. The specific input values to be used in the check are set on the Call Values tab (pic.6). You can set as many check values as you need.

6. Definition of test values in the test creation wizard

In addition to comparing the test execution, SSMA can also check for changes in the corresponding table. For example, when executing a stored ADD_EMPLOYEE procedure, the SQL Server inserts an additional row into the EMPLOYEES table. SSMA compares the changed rows in the table between the source and receiver. You can also specify the level of detail of the comparison if necessary.

The last step in determining the check is to set additional parameters. One of the important parameters is to indicate whether to roll back the changes made in the table during the check (pic.7). In our example, when running the stored procedure ADD_EMPLOYEE in both databases – the source in Oracle and the receiver in SQL Server – a new record is made. If you select the option to rollback changes to the data, SSMA will delete the inserted record after the check is completed.

7. Defining verification parameters

You can run the created check as many times as you need. Each time you run a check, you will receive a report containing a comparison of the results.

8.SSMA provides a full performance report

SSMA provides a wide range of database migration automation tools. This tool can be used to assess the complexity of the database to be migrated, convert the database schema, solve popular problems encountered during database migration, migrate data from the source database, and validate the migrated database.

SSMA is intended for migration to SQL Server, it also supports direct migration to SQL Azure (from MySQL, Sybase and Access databases). When migrating to SQL Azure, SSMA takes into account the requirements of SQL Azure platform. For example, SQL Azure requires that tables have a clustered index. If there is no master key or clustered index in the source table, SSMA can automatically add a ROWID column and create a clustered index based on it during the conversion.

You can download SSMA from Microsoft SQL Server website. This tool is not only free, but also offers free email support from the Microsoft Service and Support Center. For more information about SSMA, see the SSMA team blog, where you will find a demo video and articles with instructions, as well as tutorials to solve standard problems encountered during the migration process.

Server Migration Assistant