SQL Server: Manage the Migration
Migrating massive databases can never be called an easy task, but Microsoft has a tool available that can help ease the way and ensure that you’re ready.
Welly Lee
The latest iteration of SQL Server, code-named “Denali,” offers many compelling features and will drive many organizations to move to SQL Server. Even with the performance and total-cost-of-ownership (TCO) advantages for moving to SQL Server, though, some organizations may have concerns about the cost and risk for database migration.
Fortunately, Microsoft provides a tool, SQL Server Migration Assistant (SSMA), to automate the migration process. The latest SSMA v.5.1 (released this month at the same time with SQL Server Denali CTP3) supports migration from Oracle, Sybase, MySQL and Access databases to SQL Server. You can use SSMA to ease your database-migration project. As an example, here’s a look at the process of migrating from an Oracle database—but the process and steps are the same when migrating from other databases.
Assess Your Database
SSMA automates migration of most database objects, including stored procedures, functions, packages and triggers. There are few special data types like object type or spatial type that aren’t supported by the current version of SSMA. In addition, you may also have complex PL/SQL statements that can’t be automatically converted. You can use SSMA to run a migration assessment on your Oracle database and determine if your database schema contains such statements.
The assessment summary reports contain the following information:
- Schema tree view: a list of objects from the source Oracle database schema.
- Conversion rate: the percentage of statements that SSMA is able to convert automatically. For this example (see Figure 1), the SSMA tool can convert 99.39 percent of all statements in the source Oracle schema.
- Object count: the number of database objects found in the Oracle schema and a count of objects “With errors” (more on this later).
- **Conversion Message summary:**a description of the issues encountered while migrating the source Oracle schema.
There are three types of conversion messages you’ll encounter during a migration using the SSMA tool: error, warning and information:
- An error message is raised when SSMA is not able to convert a database object or a statement within a database object.
- A warning message is raised when SSMA is able to convert the Oracle statement, but the converted statement may not produce the same result for some cases. For example, SSMA converts Oracle SUBSTR() to SQL Server SUBSTRING(). In most cases, SUBSTRING() would return the same outputs. There are some situations, however, where the results are different. For example, the Oracle SUBSTR() function supports negative values for character position. SUBSTR(‘TechNet’,-3,3) returns ‘Net’ in Oracle, whereas SUBSTRING(‘TechNet’,-3,3) would return an empty string in SQL Server.
- An information message is for SSMA to provide additional information about how it converts certain objects.
.jpg "A look at the Conversion Rate when converting from an Oracle database")
Figure 1 A look at the Conversion Rate when converting from an Oracle database.
Each error message includes a link that displays the object containing the error. There’s also a side-by-side comparison with the original statement on the left and what the converted statement would look like in SQL Server (see Figure 2) on the right. The error message also includes an estimate of how many hours of manual conversion would typically be required to resolve the issue.
.jpg "You will occasionally get a Migration Error message")
Figure 2 You will occasionally get a Migration Error message.
Most organizations often perform assessments against a number of Oracle database schemas. They’ll use the total conversion rate and total estimated manual conversion time to compare and prioritize Oracle database schema for migration.
Converting Database Schema
SSMA gives you many options for schema conversion. For example, you can modify data type mapping. SSMA provides default data type mapping between Oracle and SQL Server. However, you can customize the data type mapping for a specific table, for all tables, for a specific object (such as a stored procedure or function) or for different usage (such as data type in column, data type in variable or data type in input/output parameter of your procedure).
Convert the database schema by clicking the “Convert Schema” button. Then you can navigate to the different database object and compare the original schema object and the converted object (See Figure 3)
.jpg "The Schema Conversion view")
Figure 3 The Schema Conversion view.
When an object contains a statement that SSMA is unable to convert automatically, the tool will add a migration error description, comment on the specific statement or replace it with a generic type. This isolation approach lets you continue with the database migration and resolve the issue later.
You could also resolve the issue and modify the statement directly from SSMA. For example, in Figure 3, there’s a user-defined function called “TENURE.” This returns an INTERVAL data type that SQL Server doesn’t support. You can modify the return type to NUMBER (see Figure 4) and reconvert the function. This removes the error and converts the return value to float(53).
.jpg "You can modify statements in SSMA to resolve incompatibilities")
Figure 4 You can modify statements in SSMA to resolve incompatibilities.
You can also modify converted statements. For example, you can replace “float(53)” return type with “INT.” Note that any modification you make with SSMA is stored locally. The changes you make to source statements aren’t applied to the Oracle database schema you have in production. Similarly, any changes made to the target SQL Server statement aren’t immediately applied to the server. This lets you continue to refine and make necessary modifications to converted schema without impacting your target server.
You can deploy converted schema to the target SQL Server by right-clicking the schema name on the SQL Server Metadata Explorer window. You can also generate a script to create the entire schema information, which you can then deploy on your target server (see Figure 5).
.jpg "Deploying converted schema to SQL Server")
Figure 5 Deploying converted schema to SQL Server.
Migrating Data
After creating the database schema in the target SQL Server, you can use SSMA to migrate Oracle data. SSMA isn’t the only option for migrating data, though. You could also use SQL Server Integration Services (SSIS). However, migrating data with SSMA lets you use the same type of mapping for schema conversion. It also handles some of the common data-migration issues when migrating from Oracle to SQL Server.
For example, Oracle has a wider range of supported date types than SQL Server. By default, SSMA raises the data migration error when it finds such a case. You can have SSMA automatically convert out-of-range date values with NULL or the nearest date SQL Server can support. You can modify this setting through Tools | Project Setting | General | Data Migration (see Figure 6).
.jpg "There are options for handling data-migration errors")
Figure 6 There are options for handling data-migration errors.
After completing the data migration, SSMA will display a report with the number of rows migrated, the success rate and the times taken to migrate each table (see Figure 7).
.jpg "SSMA will give you a full data-migration report")
Figure 7 SSMA will give you a full data-migration report.
Testing Database Migration
After you’ve successfully migrated your database, the next step is validation. When migrating from Oracle and Sybase, SSMA lets you compare the source database and the migrated database. You can define a series of test cases; then SSMA runs the test cases on both the source and the target database. It will compare the results, as well as any changes the test cases made on the underlying tables.
To define a test case, select New Test Cases from the Tester menu. A Test Case Wizard will walk you through the process of creating a test case. You can also select specific database objects to test. For example, there’s a procedure called ADD_EMPLOYEE. This procedure inserts a new record in the employee table based on the value provided from the input parameter. You can define specific input parameters to use in the test through the Call Values tab (see Figure 8). You can define as many call values as you need.
.jpg "Specifying call values in the Test Case Wizard")
Figure 8 Specifying call values in the Test Case Wizard.
Besides comparing test object execution, SSMA can also test changes to the underlying table. For example, when executing the ADD_EMPLOYEE stored procedure, SQL Server will insert an additional row into the EMPLOYEES table. SSMA compares the changed rows in the affected table between the source and the target. If needed, you can also specify the level of granularity for the comparison (see Figure 9).
Figure 9 Specifying underlying tables for comparison.
The final step in defining the test case is additional settings. One important setting is whether to roll back any changes made to the table as a result of testing (see Figure 10). In my example, when executing the ADD_EMPLOYEE stored procedure, a new record will be added to both the source Oracle database and the target SQL Server database. If you select the data rollback option, SSMA will remove the inserted value after the test is complete.
.jpg "Defining test case settings")
Figure 10 Defining test case settings.
After defining the test case, you can run it as many times as needed. For each run, you’ll receive a test result report that compares results (See Figure 11).
.jpg "SSMA will give you a full test result report")
Figure 11 SSMA will give you a full test result report.
SSMA gives you rich functionality to automate database migration. You can use the tool to assess the complexity of the database you plan to migrate, convert the database schema, resolve common database-migration issues, migrate data from the source database and validate the migrated database.
The tool is designed for migrating to SQL Server, but it also supports direct migration to SQL Azure (from MySQL, Sybase and Access databases). When migrating to SQL Azure, SSMA takes into account requirements for the SQL Azure platform. For example, SQL Azure requires that its tables have a clustered index. If the source table doesn’t include a primary key or clustered index, the tool can automatically add a ROWID column and set the clustered index on the column during the conversion.
You can download SSMA from the Microsoft SQL Server Web site. Not only is the tool free, but you can also get free e-mail support from Microsoft Customer Service and Support. For more information on SSMA, visit the SSMA team blog for a video demonstration and how-to articles, as well as guidance on resolving common migration issues.
.jpg "WellyLee")
Welly Lee is a senior program manager with the SQL Server Migration Assistant team, and is the feature owner for the database-migration tool from Oracle and MySQL to SQL Server. Prior to joining Microsoft in 2007, he worked as consultant on database solutions development and enterprise application implementation for more than 10 years.