Introduction
Collecting assessment information across a large data estate is a large manual effort. To optimize this process, our team has created a set of scripts to collect both Pre-SSMA information (high level CSV information) and SSMA projects.
Each set of scripts consist of a PowerShell script that is the main execution engine, an Excel spreadsheet that allows the customer to provide required details about each Oracle server and either SQL scripts (Pre-SSMA) or an xml script (for SSMAConsole) to do the actual commands to collect the required information/SSMA project.
OLEDB Component Pre-requisite
The PowerShell scripts read the Excel spreadsheet using the Access OLEDB driver. You may need to install this on your local machine to execute the scripts. You can download and install the driver here: https://www.microsoft.com/en-ca/download/details.aspx?id=13255
Input Configuration
The Excel spreadsheet (servers.xlsx) has several columns that need to be provided as shown below;
Both Excel spreadsheets are the same format, so a single copy can be reused (either copied or the local in the OLEDB specification can be updated).
Before executing the .ps1 PowerShell script files, the locations of the Excel spreadsheet and tool and script locations may need to be updated.
The easiest way of editing the PowerShell scripts is with the PowerShell ISE. In this case we are showing the PreSSMA script in the ISE;
The Excel spreadsheet location is in the yellow rectangle, the Oracle sqlplus executable location in the green rectangle and the two PreSSMA scripts location in the blue rectangle. Once the appropriate values are inserted, the script can be executed using the green start button in the ribbon.
For the SSMA script, shown in the ISE below;
The Excel spreadsheet location is in the yellow rectangle, the SSMAConsole xml script in the green rectangle and the SSMAConsole executable location is in the blue rectangle. Once the appropriate values are inserted, the script can be executed using the green start button in the ribbon.
Executing the Scripts
As noted above, the scripts can easily be run in the ISE or they can be run in a stand along PowerShell console window or by right clicking the file and select Open or Run with PowerShell (Windows 11 preview shown below).
PreSSMA File Capture Analysis
Provided in the PreSSMA folder is a Power BI Workbook template (PreSSMAResults.pbit) that will import the captured CSV(s) into a data model that you can use for high level reporting. The workbook contains a few sample reports, but you can easily create new reports or customize the existing reports.
In the produced data model, there are two Conversion Effort… tables that contain approximate complexity and conversion hours ratings to attempt to categorize the levels of effort required to convert each schema and a roll up to instance. Note that these are very approximate metrics and may not reflect the reality of the actual conversion effort.
Next Steps
Generating a report from an SSMA capture creates a report.xml file that we can then read with Excel and subsequently load into a SQL data model. The next step would be to create an automated mechanism (likely PowerShell) to create this report and load it into SQL Server to roll up the more detailed warnings/errors and analysis that SSMA provides.
The .zip with all of the scripts can be downloaded from the Download Center.
Feedback and suggestions
If you have feedback or suggestions for improving this data migration asset, please contact the Azure Databases SQL Customer Success Engineering Team. Thanks for your support!
Note: For additional information about migrating various source databases to Azure, see the Azure Database Migration Guide.