SQL Server Migration Assistant: 8 Steps from Assessment to Validation

SQL Server Migration Assistant helps teams assess and convert supported non-SQL Server data stores for a Microsoft SQL target. It can automate parts of schema change and data move. Yet, it does not automatically convert each app rule, integration, or day-to-day process that depends on the old data store.

This article explains what this tool does, eight useful move steps, and the checks a business needs before cutover. It focuses on using the assistant for a supported source data store, rather than treating it as a universal tool for each SQL Server move.

What SQL Server Migration Assistant supports

Microsoft’s official SQL Server Migration Assistant overview lists support for migrations from Microsoft Access, Db2, MySQL, Oracle, and SAP ASE to Microsoft SQL targets. Source-specific tools and needs apply. So, confirm your source, version, and intended target in the current docs before planning the project.

This tool helps assess data store objects, convert supported schema elements, deploy the converted schema, and move data. The details depend on the source and target. It is not a general replacement for each backup, restore, upgrade, or continuous replication process.

SQL Server database migration involves more than tables. An app may rely on stored logic, scheduled tasks, reports, access rights, and engine-specific behavior. Those dependencies need their own review even if the main data transfers successfully.

For the wider planning process, see our database migration services guide. Here, the key question is how to use this tool’s output without mistaking automated schema change for a complete business move.

8 steps for a controlled SSMA migration

1. Confirm the source and target path

Find the source data store engine and version, then choose the target platform. SQL Server, Azure SQL Database, and Azure SQL Managed Instance have different operating characteristics. Do not assume a feature available in one target works identically in another.

Check the supported path and source-specific prerequisites. These may involve drivers, connectivity, access rights, and tool components. Next, confirm who can access the source and whether the review can run against a safe copy.

Keep the target choice tied to business needs. Hosting, day-to-day skills, integrations, and recovery needs matter alongside schema change effort. A successful tool run does not settle those choices.

2. Inventory dependencies before conversion

List tables, views, routines, triggers, jobs, and outside connections. Also find the apps and reports that read or write the data store. For example, an old desktop app may contain queries that never appear as data store objects.

Assign an owner to each key dependency. A reporting query may belong to finance, while a scheduled export belongs to operations. So, move checks need input beyond the data store team.

Record data volume, growth, and peak-use periods. These facts help gauge transfer time and the cutover window. Yet, measure them from the real environment rather than relying on a generic move timetable.

3. Run and interpret the assessment

Use the right source-specific tool to connect to the supported source and assess the objects in scope. Save the project and output so the team ca review the result consistently. Next, separate errors, warnings, and items that need manual work.

Microsoft’s assessment report guidance explains how the report presents schema change results and can help gauge project scope. Treat that rough cost as an input, not a complete delivery quote.

A high schema change share can still hide a key blocker. If one unsupported routine controls bill totals, it matters more than dozens of simple tables that convert cleanly. Prioritize findings by business impact and dependency.

4. Review conversion settings and mappings

Schema change may change data types, names, and engine-specific constructs. Review those mappings before deploying them. For example, dates, text comparison, numeric precision, and empty values can affect app behavior.

Check key fields and relationships carefully. A converted table may look correct while a changed type alters sorting or calculations. Also, confirm how the target handles identifiers and values the source allowed.

Record intentional changes. If the team renames a field or changes a type, the app and reports need to follow the same choice. Do not let tool defaults become unreviewed business rules.

5. Convert and deploy to a test target

Start with a non-production target. Review generated schema and resolve key schema change findings before loading the full data set. Next, confirm that the target has the needed capacity and setup.

Keep deployment repeatable. The team should know which project, settings, and manual changes created the target schema. Otherwise, a second rehearsal may produce a different result from the first.

Use change tracking for scripts and choices. Manual fixes should not exist only in one person’s notes. A clear record helps the team apply the same corrections during the final run.

6. Migrate data and reconcile it

Move a trial data set, then a representative full set when feasible. Check row counts, key relationships, and business totals. Yet, equal row counts do not prove that each value arrived correctly.

Compare key fields and edge cases. Include high-precision amounts, old dates, long text, null values, and characters outside basic English. Also verify attachments or outside files that the data store references but does not store.

Microsoft’s data migration report documentation describes the report available after data movement. Use that output alongside independent reconciliation, especially for records that affect money or operations.

7. Validate the application and performance

Point a test copy of the app at the target and run real business workflows. Confirm login, access rights, searches, calculations, exports, and integrations. The app may need query or connection changes even when the schema change succeeds.

Compare results with a controlled source data set. For instance, the same bill should produce the same approved total unless the team intentionally changes the rule. Next, test larger lists and reports under realistic data volume.

Review indexes and query plans when performance changes. A different data store engine can execute the same-looking query differently. So, plan time for tuning rather than promising that schema change alone will preserve speed.

8. Rehearse cutover and recovery

Define how the team will stop writes, move final changes, verify the target, and switch apps. The method depends on the supported tools and your downtime needs. Do not assume this tool automatically provides continuous synchronization for each path.

Write a rollback plan with a clear choice point. If users start writing to the new target, reverting to the old source may lose or require reconciling those new changes. So, recovery planning must address data divergence, not only connection settings.

Run a rehearsal and measure the real time. Then use that proof to choose a realistic maintenance window. Assign owners for each action and confirm how staff will communicate a go or no-go choice.

A practical example: moving a MySQL-backed CRM

Imagine a business wants to move a custom CRM from a supported MySQL source to a Microsoft SQL target. The data store holds clients, jobs, bills, and activity history. The PHP app also contains engine-specific queries.

First, the team inventories the data store and app dependencies. The tool finds straightforward table conversions and several areas that need review. The team pays special attention to numeric types used in bill totals and text comparisons used in client search.

Next, staff convert the schema into a test target and migrate a copy of the data. Finance checks bill totals, while operations tests job statuses and search results. Coders review the PHP connection layer and queries rather than expecting this tool to rewrite the whole app.

A test reveals that one report returns different ordering for names. Another query takes longer on a large history table. The team reviews collation and indexing, documents the changes, and repeats the affected checks.

Finally, a cutover rehearsal measures the final load and check steps. The business uses those timings to plan downtime and decide how to handle new records during the switch. The assistant reduces some move work, but the team still owns app correctness and release planning.

Understand what the tool does not settle

This tool does not replace an app move plan. Connection strings, query syntax, transactions, and error handling may need changes. Likewise, scheduled jobs and outside services may require separate setup.

It also does not decide security roles for the new environment. Review users, service accounts, and access rights according to the target platform. Avoid giving the app broad access simply to make the first test pass.

Day-to-day readiness remains a separate task. Backups, recovery tests, monitoring, maintenance, and support ownership need to work before production use. A target with all its data but no tested recovery process is not ready for business dependence.

If the wider project replaces older app behavior, cloud migration tooling may form part of the plan. Yet, do not combine each tool’s purpose into one assumed automatic move path.

Plan validation around business evidence

Create a checklist of key records and outcomes before the move. Finance may compare bill and payment totals, while operations checks open jobs and stock balances. Give each check an owner and an expected result.

Use both technical and business tests. A row count can find missing records, while a report total can reveal a value or logic problem. Also, spot-check single records and their relationships.

Include historical data, not only recent records. Older formats and unusual values often expose schema change issues. Also verify which time zone and date meaning the app expects, especially for deadlines and reports.

Document accepted differences. Some changes may be intentional, such as a corrected legacy calculation. The team should explain them clearly so checks do not treat each mismatch as an unexplained defect.

Estimate effort from the findings

Build the rough cost around schema change work, app changes, data transfer, checks, and cutover preparation. The review helps find some tasks, but it does not capture each outside dependency. So, review its output with the people who run the business workflows.

Separate mandatory move work from optional improvements. A data store move may create a chance to clean data or redesign screens, but adding those changes can make checks harder. Agree on the scope so staff know which results should remain the same.

Allow time for repeated rehearsals and fixes. The first run provides proof about time and edge cases. A schedule that assumes it will also be the final run leaves little room to resolve those findings.

Choose the right delivery support

A move partner should explain the supported path, manual work, test method, and recovery plan. Ask how they will prove key business results rather than accepting a schema change share as the only measure.

For a custom CRM, custom crm development can support the app changes around the data store move. The team should understand both the data layer and the workflows that depend on it.

Also clarify who supports the target after launch. Data store hosting, backups, app fixes, and integration support may involve different owners. Make those responsibilities concrete before users switch to the new environment.

Use small data checks that expose big risks

Start with a price field. Pick a row with a value such as 19.99 and a second row with more digits. Check the saved values on both sides, then run the same sum. If the totals differ, find the cause before staff use the new system for live bills. A row count will not catch this fault.

Next, check a blank field and a field with no value. Some apps treat those states in distinct ways. For instance, a blank phone field might mean that staff cleared it, while no value might mean that no one has checked it yet. Make sure the app keeps the meaning your team needs.

Test a date at the end of a month and one near midnight. Open the record in the app, run a date filter, and check its place in a report. A correct value in a table can still show the wrong day if the app applies a time shift.

Then test a name with mixed case and a name with an accent. Search for each one in the old and new apps. Check sort order too. If a search now finds too many or too few rows, review the text rules and the query. Do not assume that a clean data load makes those results the same.

Set clear stop rules for the final switch

Write down which checks must pass before users start work. Give each check a named owner. For a small CRM, the list might include open job counts, bill totals, login tests, and one key export. Keep proof of each result with the run notes.

Also set a time limit for the go or no-go call. If the final load runs late, staff need to know when to stop and keep the old app in use. Do not let a long load use up all the time set aside for checks.

Once users write new data, a switch back needs more care. Keep a plan for those new rows and changes. Test that plan with a few records during the trial. The team should know how to keep that work, not just how to point the app back to its old host.

Use the assessment to guide a controlled move

SQL Server Migration Assistant can reduce repetitive schema change work on supported paths. Start with its report, assess the impact of findings, and check the app on a test target. Then rehearse cutover with measured timings and a clear recovery plan.