SQL Server Migration Assistant (SSMA) is famously known for its ability to migrate database schemas from Access, Db2, MySQL, Oracle and Sybase to Microsoft SQL Server. However just as important as the migration itself, is the ability to assess the difficulty of a particular migration. Let's face it, each customer's circumstances and use cases differ. It is absolutely crucial that there are no surprises during a migration.
The latest release of SSMA has brought with it a completely revamped assessment report experience:
The brand new SSMA reports are available in all flavors of SSMA 8.22 and above, compatible with all popular web browsers. Let's take a deeper look at some of the changes!
Before reading an SSMA report, it's important to understand how our conversion process works. Upon generating a report, SSMA goes through many of the same steps as with a conversion. During this process we evaluate what objects of your database SSMA can convert automatically, what objects SSMA cannot convert and everything in between. Often times SSMA will try its best to convert an object, but perhaps not in its entirety, in this case messages are added to explain this to the user. The most basic, least severe message we use is an "info" message, which requires no manual intervention on the user's behalf. An object with a "warning" will be converted by SSMA but should be investigated thoroughly to ensure the conversion was done appropriately for the situation. An object with an "error" cannot be converted by SSMA and should be handled by the user. In the case of error, the converted code will be completely commented out so the user can evaluate the best path forward.
Likely one of the first major differences you'll encounter is our statistics charts. Fully interactive, these charts allow users to look at their objects converted, manual conversion time and breakdown of child objects.
Objects conversion pie chart (left side) shows all descending objects, classified by the highest severity of message attached to them. For example, in the screenshot above, we can see that the "views" category contains a total of 7 objects, 3 of which have at least one error, meanwhile one object has at least one warning. Objects that have no messages, or simply info messages attached to them are considered "Automatic" since they don't require any manual user intervention.
Manual conversion time pie chart (right side) shows descending hours of conversion messages. Unlike objects pie chart, this is not classified by highest severity of message for a given object. Instead this looks at all estimated conversion times and breaks them down based on whether the time is coming from an error or warning.
The remaining bar charts on the screen provide a breakdown of children objects with messages attached. Clicking on the title of one of these bar charts will navigate to the appropriate object.
Tabs at the top of the statistics window allow the user to switch back and forth from overview to details, providing a similar experience to previous versions of SSMA.
Code view has already existed in SSMA for some time now., however a recent update has completely revamped the experience. We now use Monaco editor to display source and target SQL which provides a much more modern user interface.
Some features to take note of are:
Red (error), yellow (warning) and blue (info) lines are present in the margins to bring attention to areas of code which have messages attached.
Easily view conversions by clicking an area of code either on source (left) or target (right) and both editors will highlight.
Navigation buttons on top of editor allow for easily navigation between all info/warning/errors.
At a first glance, the messages window may seem unchanged, however a closer look reveals some exciting new features!
Ability to change message sorting order by clicking a tab more than once.
Clicking on a message will immediately navigate to the object in question and highlight the applicable code if there is any.
"Messages for" dropdown has been added to allow users to switch between viewing messages for all objects (default) and only current object. In the screenshot above, we can see "All Objects" is selected, and there are many messages: errors (5), warnings (3) and info (4). Meanwhile in the screenshot below, we can see that "Current Object" is selected and there are significantly fewer messages: errors (1), warnings (2) and info (1) visible.
Objects tree is the primary navigation to objects in the database. Labels are added next to objects with estimated conversion time. The color of each label will reflect the highest level of message for that object, for example in the screenshot below, sales_by_store has at least one warning, but no errors, thus we show its 6 hour estimate as yellow to denote this.
SSMA Reports have been featured on an episode of data exposed! Watch us walk through SSMA reports in the video below.