Microsoft’s SQL Server Migration Assistant lets you move your data and schema to different SQL Servers. Here’s what you need to know about the tool.

The Microsoft SQL Server Migration Assistant is a basic but highly effective tool for migrating databases in a few simple steps. While it lacks some premium features, Microsoft’s free-to-use assistant has everything you need for a successful migration.

SEE: The Essential Microsoft SQL Server Training Bundle (TechRepublic Academy)

In this review of Microsoft SSMA, learn more about its key features, pros and cons, and how this solution will work with varying budgets and tech stacks.

Jump to:

What is Microsoft SQL Server Migration Assistant?

Microsoft SQL Server Migration Assistant is one of several Microsoft database tools that simplifies the process of migrating a database, updating one to a new version of SQL or restoring a backup. For instance, if you want to move a database from Oracle or MySQL to Microsoft, you can use SSMA to do that quickly and easily.

SSMA is available in several versions to suit different needs, including Microsoft SQL Server Migration Assistant for Access, DB2, MySQL, Oracle and SAP ASE. Additionally, SSMA has various target migration options. It supports SQL Server versions 2012 through 2019, as well as Azure SQL Database, Azure SQL Managed Instance and Azure Synapse Analytics.

SEE: Steps for performing a successful data migration (TechRepublic)

Plenty of options are supported for the original and desired migrated database. SSMA is relatively easy to use, regardless of where you are relocating your database from. Data and schema migration are often major challenges during this process, but SSMA includes tools to simplify both.

Pros and cons of SSMA

Pro: Free migration tool

SSMA is a relatively straightforward tool for migrating to a Microsoft database from another relational provider. One of the biggest benefits of SSMA is that it is free to use, and you can download the necessary version directly from Microsoft.

Pro: Straightforward data migration process

SSMA is easy to operate. The basic process for migrating a database consists of seven steps, all using the tools provided in SSMA.

Once you have downloaded and opened the Microsoft SQL Server Migration Assistant, you create a project and set the desired destination database. Next, you connect to your source database by selecting the provider and inputting any necessary login information. You then choose the data you want to load in SSMA for migration.

Pro: Conversion reporting

One helpful feature baked into SSMA is the conversion report tool. This allows you to do a practice conversion without converting or migrating your schema and data. The conversion report will identify any potential errors or issues during the migration.

SSMA can automatically repair many mistakes, but it will also tell you if a problem in your origin database needs to be resolved manually. It is strongly recommended that you run a conversion report, as it will streamline the process.

Pro: SQL comparison feature

You can initiate your migration after running your conversion report and analyzing the results. This will connect you to your destination database, where you can also fix or change any end-type mapping preferences or compare your old and new SQL code side by side.

This SQL comparison feature is especially useful if objects in your original database don’t line up perfectly with those available in your new Microsoft database.

Pro: Simple management of converted schema

After your destination database is set up the way you want, you can synchronize your replica metabase in SSMA with your real destination database. You can then deploy your converted schema. SSMA lets you easily view your converted schema to ensure everything is correct.

Pro: Automatically-generated migration reports

If you are happy with the schema conversion, you can move ahead to the last step, which is to select the button to migrate your data. SSMA automatically generates a convenient data migration report that you can save for future reference, which includes info on everything you migrated and the various steps taken.

Con: Lacking interface and toolset

Because SSMA is free to use, it is somewhat spartan in its design. The user interface is minimal and a little dated, although it is certainly functional. The main drawback of Microsoft SSMA is the lack of additional tools, features and support compared to premium products.

Con: Limitations for users who don’t know SQL, Python or C#

SSMA may be somewhat difficult to navigate for those who are not fluent in SQL. Other database migration tools such as SnapLogic use drag-and-drop UIs that may be more approachable for those who are unfamiliar with the programming language.

If you will be working with databases frequently, learning SQL — as well as Python and C# — can be helpful for understanding, optimizing, and navigating your data and schema.

Con: Lesser scope than other migration tools

SSMA is somewhat limited in the scope of what it can do compared to other database migration tools. It only works for migrations to Microsoft databases, and only certain origin providers are supported. SSMA also lacks sophisticated visualization features beyond its basic toolset.

Is Microsoft SQL Server Migration Assistant free?

Microsoft SSMA is free; you can download it directly from Microsoft’s website. This is arguably the biggest perk of using Microsoft’s SQL Server Migration Assistant as opposed to other database migration tools. If you know your way around SQL and want to do a basic migration from your old database provider to Microsoft, SSMA is the perfect tool for the job.

Is Microsoft SSMA right for me?

The Microsoft SQL Server Migration Assistant is a functional tool that’s great for database management and migration teams that need to complete basic migrations to Microsoft databases.

It lacks some convenient and sophisticated features offered by premium tools, but for a free-to-use program, it is more than enough to get the job done. Features like the conversion report and side-by-side SQL comparison are rudimentary but effective for ensuring a successful migration.

Read next: Top cloud and application migration tools (TechRepublic)