This article gives the overview of the Database Migration Assistant Tool to access, plan and migrate the SQL Server from an old version.
Database migration is part of the DBA job. We can’t avoid it, and there are multiple options that can be taken:
- From SQL Server to SQL Server
- From another platform (Oracle Database, MySQL, PosgreSQL…) to SQL Server
- From SQL Server to another platform (Oracle Database, MySQL, PosgreSQL…)
We can also perform a so-called “in-place” migration or a “side-by-side” migration. In the first one, everything will be performed on the same server. We could say that the source for the migration is also the destination of that migration. In the second, source and destination roles are physically separated. This means one server is the source and another server is the destination. Choosing one or the other depends on the migration context and environment, but in my career, we always chose a side-by-side migration because the OS and the server had to be upgraded or changed.
You will definitely opt for a side-by-side migration when you want to migrate an Oracle database on Linux to a SQL Server on Windows so as a SQL Server on Windows to a SQL Server on Linux.
To help DBAs in this task that can be quite risky, Microsoft provides a bunch of tools that we will review in present article. You will find below a non-exhaustive list of these tools.
- Database Migration Guide
- Microsoft Assement and Planning Toolkit a.k.a MAP
- Database Migration Assistant a.k.a. DMA
- Database Experimentation Assistant a.k.a DEA
- SQL Server Migration Assistant a.k.a. SSMA
- For SQL MySQL
- For Oracle
- For Access
- …
- …
Let’s review some of these tools…
Database Migration Guide
Database Migration Guide is at first a website that will help you in a database migration. You can follow this link to get to the tool.
Here is how it looks like:
It’s organized as follows:
- First, there is a part to create a migration guide manual adapted to your migration context
- Then, there is a case studies section and also a partner tools sections.
We will focus on the first part and click on “Start Here” button. It opens a form where we will first select a source data type with most commonly used ones:
The “other options” are at the moment this article has been written:
- PostgreSQL
- SAP ASE
- Access
- MongoDB
- Azure Table Storage
Every time we select a source, all destination options will be displayed (either on-premise or in the Azure cloud). Once the source and destination RDBMS are defined, we’ll get to a summary of what has to be considered and how we can do it using.
For instance, if we select SQL Server as source, we will have two options: SQL Server or Azure
If we click on “SQL Server”, we will be redirected to a page that will sum up the work that has to be done:
If you want to get more details about this kind of migration, you can go to this page.
If you don’t know where to start, it’s the appropriate tool to use.
Microsoft Assessment and Planning Toolkit a.k.a. MAP
This tool has been designed by Microsoft to help DBAs to perform common tasks that has to be done when we want to migrate:
- Inventory the existing system
- Check for breaking changes and mandatory code adaptations before being able to migrate
- etc
This tool has been well covered by an article entitled “How to use Microsoft Assessment and Planning Toolkit for SQL Server” written by Musab Umair and we won’t discuss it further, here.
Database Migration Assistant a.k.a DMA
What is it?
With Database Migration Assistant, you will be able to assess, plan and effectively upgrade older versions of SQL Server, starting SQL Server 2005, to a more recent version, from on-premise to Microsoft’s cloud.
It can be downloaded following this link.
Let’s review its installation process.
Installation
Click on next button.
Read the terms and accept them then click on Next button.
Then you must agree the privacy policy and finally click on the Install button.
If the installation is successful, you will get following pane. You can choose to either launch Database Migration Assistant or not when you click on the “Finish” button.
Using Database Migration Assistant
Here is how the interface looks like at startup:
Let’s try it out and click on the “+” button on the left hand sidebar pointed out by the “Get started here” area.
A form will appear letting us choose between an assessment and a migration task. No matter the chosen option, we can see that a project name is absolutely necessary:
Using Database Migration Assistant to plan a migration
Let’s first choose the assessment project type and try to plan a SQL Server to SQL Server migration.
One every parameter is set; we can push the “create” button.
Once this button has been clicked, the tool will ask us more and more questions, starting with the target environment. We have following choices:
As I don’t have any SQL Server 2016 or 2017 available at the moment, let’s choose SQL Server 2014.
By default, there is an option that will give advices on the new features that we would add benefits to the current situation.
Once we set everything as we want, we can click on the next button to provide information about the source database(s).
Here is the screen where we provide source servers. We are directly on a form to add a new source where we can specify connection properties like server name, authentication type or connection properties like encryption usage.
Notice the “SQL Server permissions” section on the screen that tells you what permissions are mandatory for the login used for connection.
Once you provided correct credentials with appropriate permissions and clicked on the connect button, you will be shown a list of databases existing on the source server instance. You can select one or more databases and click on the “Add” button to actually add them as source databases for migration process. These databases will be analyzed to check if they can be migrated as is to the destination version of SQL Server.
For instance:
Once we added all the databases, we get a summary with database names and sizes and we can click on a “Start assessment” button.
We will have to wait a moment before getting back the results of this assessment in Database Migration Assistant. While waiting, we can notice that the report will either show compatibility issues or feature recommendations and that we will be able to look for a particular database. These filters can be found on the left part of the report pane:
Once everything is done, we see that an assessment report is divided in multiple parts:
First, there is an icon that visually tells you directly if the database can be migrated (orange rectangle in following screen capture). Then, we see that the tool will run an assessment task for each compatibility level option from current one to the one corresponding to destination’s SQL Server version (zone rounded in purple on following screen capture). Furthermore, for each compatibility level, we will see what the tool discovers in terms of breaking changes, behavior changes and depreciated features and we can click on each discovery to get a full explanation of the discoveries, and which objects are impacted (rounded in green in following screen capture). Finally, we can see there are three actions in Database Migration Assistant that we can perform:
- Restart the assessment
- Delete assessment results
- Export assessment report (to JSON or CSV)
Using Database Migration Assistant to migrate databases and logins
Click on the “Plus” button on the left sidebar and create a new project, but this time, for migration purpose.
Once clicked on the “Create” button, we will be directed to a form where we will first specify source and target server connection details. Once done, we can click on “Next” button.
Once again, notice the “SQL Server permissions” text area that tells you exactly the permissions that are needed for the tool to run as expected.
If the provided credentials are correct and DMA can connect to source and destination server instances, we are asked to provide the list of databases that we have to migrate.
Actually, following screen will reveal how Database Migration Assistant will actually do the migration: using backup-restore technique.
Thus, we need to provide a set of parameters inherent to this technique:
- Destination shared folder path for backup files
- Destination path for data files
- Destination path for transaction logs
These parameters will be used for all the databases selected in the tree panel on the left. We can specify different values for these parameters when clicking on a particular database in tree hierarchy
We can see that, in the middle of the screen, there is an option that tells DMA to perform the backup, then copy backup files to a location that the service account under which SQL Server is running on destination server can read. If we click on that checkbox, here is what is added to the view:
Once we selected the databases to migrate and set parameters for backup-restore migration, we are invited to tell DMA which logins have to be kept. Database Migration Assistant will check for already existing logins and tell whether these logins are ready to be move, already exists or if there is a problem for them to be transferred.
Once we are ready, we can click on the “Start Migration Button”.
If the migration is successful, you will be happy to get following view with 0 failed operations:
For each object considered, we can check logging information in a “migration details” column. If anything went wrong (with warning or error), we will be able to look at a more in-depth log:
My opinion on the Database Migration Assistant
This tool is user-friendly in more than one way.
It provides valuable information for planning a migration and I would recommend using it.
It can be used to perform migrations for small databases in small businesses or for non-IT people. However, as an IT professional, I don’t give a lot of credits to this tool as a professional migration tool that could be used in the environments I’m used to (24/7 with databases with database sizes over 100 Gb). Moreover, it won’t copy SQL Server Agent Jobs and settings, it won’t copy Linked Servers, it won’t copy Service Broker Endpoints…
It’s however a good starting point for Microsoft as it’s quite a recent tool and it will evolve over time…
Database Experimentation Assistant a.k.a. DEA
This tool allows a DBA to take the activity from source server and run it against destination server in order to check for performance issues, queries that have incompatibility errors, queries with degraded behavior or plans.
It can be download on following page.
A dedicated article will be online in following weeks.
SQL Server Migration Assistant a.k.a. SSMA
SQL Server Migration assistant has the same purpose as Database Migration Assistant except that it’s designed for migrating data from a particular RDBMS that is not SQL Server.
There are multiple versions of SSMA, one for Oracle, one for MySQL, one for DB2…
It can be download on following page.
A dedicated article will be online in the following weeks.
Next articles in this series:
- Migrating an Oracle Database to SQL Server with Microsoft Data Migration Assistant – Installation Process and Short Overview
- A concrete example of migration between an Oracle Database and SQL Server using Microsoft Data Migration Assistant
I'm one of the rare guys out there who started to work as a DBA immediately after his graduation. So, I work at the university hospital of Liege since 2011. Initially involved in Oracle Database administration (which are still under my charge), I had the opportunity to learn and manage SQL Server instances in 2013. Since 2013, I've learned a lot about SQL Server in administration and development.
I like the job of DBA because you need to have a general knowledge in every field of IT. That's the reason why I won't stop learning (and share) the products of my learnings.
View all posts by Jefferson Elias
- How to perform a performance test against a SQL Server instance - September 14, 2018
- Concurrency problems – theory and experimentation in SQL Server - July 24, 2018
- How to link two SQL Server instances with Kerberos - July 5, 2018