Microsoft’s SQL Server Migration Assistant allows you to 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 simple yet very effective tool for migrating databases in a few easy steps. While it lacks some premium features, Microsoft’s free-to-use assistant has everything you need for a successful migration.
TO SEE: The essential Microsoft SQL Server training bundle (TechRepublic Academy)
Learn more about key features, pros and cons, and how this solution will work with different budgets and tech stacks in this review of Microsoft SSMA.
Jump to:
What is Microsoft SQL Server Migration Assistant?
Microsoft SQL Server Migration Assistant is one of the Microsoft database tools that simplifies the process of migrating a database, updating a database to a new version of SQL, or restoring a backup. For example, if you want to move a database from Oracle or MySQL to Microsoft, you can do so quickly and easily with SSMA.
SSMA is available in several versions to meet different needs, including Microsoft SQL Server Migration Assistant for Access, DB2, MySQL, Oracle, and SAP ASE. In addition, SSMA has several options for target migration. It supports SQL Server versions 2012 through 2019, as well as Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics.
TO SEE: Steps to perform a successful data migration (TechRepublic)
Many options are supported for the original and desired migrated database. SSMA is relatively easy to use no matter where you get 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 simple tool for migrating to a Microsoft database from another relational provider. One of the biggest advantages of SSMA is that it is free to use and you can download the necessary version directly from Microsoft.
Pro: Easy data migration process
SSMA is easy to operate. The basic process for migrating a database consists of seven steps, using all of SSMA’s tools.
After downloading and opening the Microsoft SQL Server Migration Assistant, create a project and set the desired target database. You then connect to your source database by selecting the provider and entering the necessary credentials. Then choose the data you want to load into SSMA for migration.
Pro: Conversion reporting
A useful feature built into SSMA is the conversion reporting tool. This allows you to perform a practice conversion without converting or migrating your schema and data. The conversion report will identify potential errors or issues during the migration.
SSMA can fix many errors automatically, but it will also tell you if a problem in your origin database needs to be fixed manually. It is highly recommended to run a conversion report as it will streamline the process.
Pro: SQL comparison function
You can start your migration after running your conversion report and analyzing the results. This connects you to your destination database, where you can also correct 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 do not perfectly match the objects in your new Microsoft database.
Pro: Easy management of converted schedule
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. With SSMA, you can easily review your converted schedule to make sure everything is correct.
Pro: Automatically generated migration reports
If you are satisfied with the schema conversion, you can proceed to the last step, which is to select the button to migrate your data. SSMA automatically generates a handy data migration report that you can keep for future reference, detailing everything you’ve migrated and the various steps you’ve taken.
Con: Lack of interface and toolset
Since SSMA is free to use, it is somewhat spartan in design. The UI is minimal and a bit dated, though it’s certainly functional. The main drawback of Microsoft SSMA is the lack of additional tools, features, and support compared to premium products.
Con: Restrictions for users who have no knowledge of SQL, Python or C#
SSMA can 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 accessible to people unfamiliar with the programming language.
If you’ll be working with databases often, learning SQL – as well as Python and C# – can be helpful in understanding, optimizing, and navigating your data and schema.
Downside: less reach 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 advanced visualization features beyond the basic toolset.
Is Microsoft SQL Server Migration Assistant Free?
Microsoft SSMA is free; you can download it directly from Microsoft’s website. This is perhaps the biggest advantage 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 perform a basic migration from your legacy 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 is great for database management and migration teams who need to complete basic migrations to Microsoft databases.
It lacks some useful and advanced 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 in ensuring a successful migration.
Read more: Top tools for cloud and application migration (TechRepublic)
