Due to my background working with other major database platforms such as Oracle, MySQL, and PostgreSQL, I’ve long been an advocate for the SQL Server Migration Assistant (SSMA). This powerful and easy product can quickly and effectively migrate most popular relational database platforms into SQL Server. The migration process using SSMA usually results in a 80/20 or 90/10 migration, in which 80%-90% of the database objects migrate without an issue. But 10%-20% typically requires additional hand-holding and manual migration work.
What is the SSMA Tester?
Microsoft SQL Server Migration Assistant Tester (SSMA Tester) relieves the pain of one of most pernicious issues when migrating between database platforms: ensuring all converted database objects are validated and that the data transferred is verified. Normally, these steps are done manually. Instead, you can use the SSMA Tester to verify all converted objects work the same way on the target SQL Server—for example, that stored procedures accept parameters and produce output in the same way before and after, and that all data was transferred properly.
The most recent release, v8.19, includes migration support from two database platforms, Oracle and SAP ASE (formerly Sybase ASE). (Support for additional database platforms are sure to follow.) The SSMA Tester also currently targets Azure SQL Database and Managed Instances as the migration destinations and offers the ability to store and run test cases through a streamlined process on your client. Once done, you can easily share test cases by simply copying the “tester” folder.
What Can You Migrate?
SSMA currently automates the migrations to SQL Server from Oracle, Access, Db2, MySQL, and SAP ASE, including the conversion of database schemas to Microsoft SQL Server schemas and deployment of those schemas, in addition to helping with data migration to the target SQL Server and validating the migrated objects. Just to be explicit, you can migrate the other previously mentioned database platforms to SQL Server 2012 and later, Azure SQL Database, Azure SQL Database Managed Instance, and Azure SQL Data Warehouse (Azure Synapse Analytics).
There are plenty of useful resources for you to explore. Microsoft has both video and documentation to help you get up to speed. For example, Microsoft offered an overview of SSMA Tester on Data Exposed Live at www.youtube.com/watch?v= TdUacpMsSrY&t=793s. More videos will be forthcoming on the Microsoft YouTube channel at https://aka.ms/azuresqlyt.
For general documentation about SSMA, go to https:// docs.microsoft.com/en-us/sql/ssma/sql-server-migration-assistant?view=sql-server-ver15. If you are already familiar with SSMA, you can jump directly to the content focused on getting started with SSMA Tester at https://docs.microsoft.com on these pages:
- Testing Migrated Database Objects (OracleToSQL)
- Testing Migrated Database Objects (SybaseToSQL)
What sort of migration projects are you working on? Are you moving from more expensive platforms (Oracle, Db2, SAP ASE) to SQL Server or Azure SQL? Or are you interested in migrating to other open source platforms or even adding NoSQL platforms to the mix? Are you moving from on-prem databases to cloud-based databases? If so, I’d love to hear about your problems, issues, and experiences! Drop me a note on social media at @kekline (Twitter and LinkedIn) or email at email@example.com.