SQL Server Migration Assistant: How to assess and migrate data from non Microsoft data platforms to SQL Server


Follow these step by step instructions to migrate schema and data from non Microsoft data platforms to SQL Server. This post illustrates the migration of an Oracle HR schema to SQL Server vNext CTP1 on Linux.

Assess the source database and discover conversion rate and effort to resolve issues

  1. Click on the "File" menu and choose "New Project". Provide the project name, a location to save your project and the migration target.
    1. Selecting the migration target is important to accurately assess the conversion rate and effort.
  2. Click "OK".createprj
  3. Connect to the Oracle server by providing the connection details in the "Connect to Oracle" dialog.connectorcl
  4. Create the conversion report by selecting the Oracle schema in the "Oracle Metadata Explorer" by choosing "Create Report" from the right-click menu options or the menu bar on the top.crrpt
  5. This will generate an HTML report with conversion statistics and error/ warnings if any.htmlrpt
  6. Analyze this report to understand conversion issues and its cause.
  7. This report can also be accessed from the SSMA projects folder as selected in the first screen. From the example above locate the report.xml file from <Drive>:\Users\<username>\Documents\SSMAProjects\MyOracleMigration\report\report_2016_11_12T02_47_55\report.xml and open it in Excel to get an inventory of oracle objects and the effort required to perform schema conversions.

Perform schema conversion

  1. Before you perform schema conversion validate the default datatype mappings or change them based on requirements. You could do so either by navigating to the "Tools" menu and choosing "Project Settings" or you can change type mapping for each table by selecting the table in the "Oracle Metadata Explorer".typemap
  2. Dynamic or ad hoc queries can be added to the  "Statements" node by selecting that node and choosing "Add Statement" from the right-click menu options.
  3. For converting and moving the schema to SQL Server, connect to the SQL server instance  by providing the connection details in the "Connect to SQL Server" dialog. You can choose to connect to an existing database or provide a new name, in which case a database will be created on the target server.connectsql
  4. Convert the schema by choosing "Convert Schema" from the right-click menu options or the menu bar on the top.convrtschema
  5. After the schema has converted compare and review the structure of the schema to identify potential problems.schemacmp

Publish the schema to SQL Server

  1. After schema conversion you can  save this project locally for an offline schema remediation exercise. You can do so by choosing "Save Project" from the "File" menu. This gives you an opportunity to evaluate the source and target schemas offline and perform remediation before you can publish the schema to SQL Server.
  2. To Publish the schema, select the database from the "Databases" node in the "SQL Server Metadata Explorer" and choose "Synchronize with Database" from the right-click menu optionssyncschema
  3. This action will publish the Oracle schema to the SQL Server instance.

Migrate data to SQL Server

  1. After publishing the schema to the SQL Server instance, select the Oracle schema from the "Oracle Metadata Explorer"  and choose "Migrate Data" from the right-click menu options or the menu bar on the top.
  2. At this step you will be required to provide connection details for Oracle and SQL Server in their respective connection dialogs to migrate the data.migratedb
  3. After the migration is complete you will be able to view  the "Data Migration report".migratereport
  4. Validate the migration by reviewing the data and schema on the SQL Server instance by using SQL Server Management Studio (SSMS)verifymigration

    Comments (1)

    1. You did not include some very important steps.

      1) Must have:
      a) a oracle database server with your data.
      b) a ms sql server 2012/14/16
      c) oracle client(win64_11gR2_client), to be install on the sql server & the laptop that’s doing the migration.
      d) SSMA v7.1 for oracle, install on the laptop that’s do the migration.
      e) SSMA v7.1 expand-pack, install on the sql server

    Skip to main content