Every database migration is a complex undertaking. When you’re moving your databases from one environment to another, you need expertise in both your old and new systems and must minimize downtime and maximize value for your business during the migration.
But a database migration is worth the effort. It is a necessity to futureproof your network – especially when moving from an on-premise to cloud-based environment, as this provides your business with the security, scalability and reliability to meet the needs of your users.
When it comes to moving across to an SQL Server environment, you’ll need an Oracle database migration checklist to make sure you’ve covered every base and adequately protected your business.
In this post, we’ll introduce the SQL Server Migration Assistant (SSMA) and uncover some of the things you’ll need to cover on your Oracle database migration checklist, based on Microsoft best practices.
The SQL Server Migration Assistant
SQL Server 2019 now provides businesses with a broad range of new features. The SQL Server Migration Assistant (SSMA) for Oracle is a comprehensive environment that helps you quickly migrate Oracle databases to SQL Server, Azure SQL DB, or Azure SQL Data Warehouse.
Using SSMA for Oracle, you can review database objects and data, assess databases for migration, migrate database objects to SQL Server, Azure SQL DB, or Azure SQL Data Warehouse, and then migrate your data to a SQL Server, Azure SQL DB, or Azure SQL Data Warehouse environment. However, please note, you cannot migrate SYS and SYSTEM Oracle schemas using SSMA.
To migrate your database, you simply create a new SSMA project, connect your Oracle database server to an instance of SQL Server and map your schemas, before conducting your migration.
On paper, this is a simple undertaking. In reality, most migrations are complex and often fraught with problems – which is where Qubark can help. We recommend organisations, create and implement an Oracle database migration checklist to achieve a seamless and error-free migration. Let’s look at some of the steps you may want to put on your migration checklist now.
Your Oracle database migration checklist
#1 Before you start – check your resources
You must check you have the resources, tools and features in place for a smooth migration. Many Oracle features are not compatible with SQL; the partitioning is different between both. With Oracle, we have partitions and sub-partitions, and with SQL server, we only have partitions.
Plus, the duration and complexity of your migration depends on your current Oracle environment, including the number of databases, their size and their design. You must verify that the source environment is supported and that you have addressed any prerequisites.
It's also important to note that the biggest problem with such a migration lies in the PL/SQL code, which may contain notable differences with the T-SQL, hence the need to review some of the procedural code.
#2 Pre-migration – work out your approach
After verifying that your source environment is supported and ensuring that you have addressed any prerequisites, you are ready to start the pre-migration stage.
Here, you must complete an inventory of the databases that you need to migrate, assessing those databases for potential migration issues or blockers, and then resolving any items you have uncovered.
For heterogeneous migrations such as Oracle to Azure Database for PostgreSQL, this stage also involves converting the schema(s) in the source database(s) to ensure their compatibility with the target environment.
To achieve this, you’ll need to use the MAP Toolkit to perform an inventory scan, and then implement the SSMA to review your database objects and data, assess your databases for migrations, before you complete your migration.
Once complete, you can decide which is the best migration approach – phased or all-in-one? There are pros and cons to both. All-in-one requires a complete shutdown of your resources. It is simple, effective and robust – but often infeasible for most businesses due to the period of required downtime. A phased migration allows you to run specific jobs at specified intervals, reducing the likelihood of complications or failures and allowing for increased testing.
#3 Migration – can it be accelerated?
After you have completed assessing your databases and addressing any discrepancies, the next step is to execute the migration process. A migration typically involves two steps – publishing the schema and migrating the data.
SSMA for Oracle is the correct tool to use for this stage. However, there are many ways you can further accelerate your migration. You could:
- Disable/drop indexes and recreate them, post migration.
- Set the recovery mode on SQL to “simple” skips transaction logging, speeding up the inserts.
- Set the initial file size of the SQL database to the expected size to avoid allocations and re-allocations of data.
Bear in mind, there is a certain level of risk associated with this step. So, you must only complete these steps if you have fully assessed your Oracle environment.
#4 Post-migration – is it working?
After you have successfully completed the Migration stage, you need to go through a series of post-migration tasks to ensure that everything is functioning as smoothly and efficiently as possible. At Qubark, we provide customers with a SQL Server Performance Optimisation service to ensure you’re operating at peak performance.
You also need to check your applications. After the data is migrated to the target environment, all the applications that formerly consumed the source need to start consuming the target. To achieve this, you may need to make changes to your applications.
Finally, you must run a series of tests to validate your environment and check its performance, before optimizing your environment, reconciling any data accuracy issues and verifying completeness, as well as addressing performance issues with the workload.
In conclusion
To conduct an Oracle to SQL Server migration, you need a certain level of expertise in both databases. In particular, it’s important to have a thorough Oracle database migration checklist to guide you through the necessary steps before, during and after a migration, to help you minimize potential issues.A database migration is a complex undertaking but, at Qubark, we have helped businesses both large and small migrate their databases safely and securely, with minimal cost and with minimal disruption. To find out more about this service, please click here.
