This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.

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 SQL script (Oracle_PreSSMA_Analysis.sql) that will import the captured CSV(s) into a SQL Server data model that you can use for reporting. The script uses the data captured to approximate the complexity of the migration of each of the schemas and attempts to calculate a very high-level approximation of the number of hours required for the conversion of each schema/server. The resulting database can then be used as a data source for a Power BI report.

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 Data SQL Engineering Team (datasqlninja@microsoft.com). Thanks for your support!

Note: For additional information about migrating various source databases to Azure, see the Azure Database Migration Guide.