Grant Fritchey

13 May 2021

• Flyway
• Cloud

How Can Redgate Tools Help with a Cloud Migration?

There are a lot of different cloud migration tools and services that can help migrate on-premises databases to a cloud platform such as Azure or AWS, or even to migrate a database between cloud platforms. Some of them will have simple processes. For example, depending on the cloud-based technology, the process might be as simple as to create a backup of your local database, transfer it to blob storage in the cloud, and then restore the database to the cloud platform. In more complex cases, your cloud migration might require the use of scripts as well as bulk data export and import. Whichever way you do it, you need to plan your cloud migration strategy carefully.

Planning for cloud migrations

When preparing your cloud migration strategy, you’ll need to research your chosen cloud platform, make sure it supports all the database features and services that you require, and do some capacity planning. You’ll need to understand exactly what components need to migrate, alongside the database itself, and choose the tools and methods you’ll use to move them. But there is more to it than that. For example, you will need to also think about how you will:

• Verify that the migration succeeded – you need a way to test that the schema and data in the in the migrated cloud database is exactly as it should be. You’ll also need to monitor carefully for any performance degradation, having completed the migration.
• Automate deployments to the cloud – How will you transfer all the release artifacts to the cloud and deploy them? Will you need to adapt your current deployment pipeline and automation?
• Perform ongoing database development – will the migration disrupt existing development practices? Can teams still create local copies of the database for development, builds and testing?

Redgate tools make lot of this planning and implementation much easier. The schema and data comparison tools will allow you to verify the migration. For SQL Server and PostgreSQL databases, you can also use SQL Monitor for ongoing performance monitoring of your Amazon or Azure-hosted databases.

The version control and deployment automation tools (Flyway Desktop and Flyway CLI) work seamlessly with all the major cloud platforms, making it easy to extend automated deployment pipelines to a cloud-based database. Also, many of the DevOps practices that you may already use to help keep development and deployment processes functioning smoothly (automation, testing) will help make your cloud migrations more efficient.

Types of database cloud migrations

When you look at how data is managed in the cloud, you’ll soon see two broad categories. There are the ‘Infrastructure as a Service’ (IaaS) offerings which are really just virtual machines. Then there are the ‘Platform as a Service’ (PaaS) offerings which are fundamentally different from the standard servers and services that contain databases. Instead, the database itself is the service.

With IaaS, the move from your local instances, whether running on iron or within virtual machines, is relatively simple. Since you have a server locally and you’re looking at a server on the cloud, for most database management systems, you can simply restore your local backups out to the cloud. This aspect of the migration to the cloud is frequently very simple. However, even here, you need to validate your data structures and data after the move, just in case. Further, automating your deployments to the cloud is just as important as automating those deployments locally.

The move to a PaaS offering in the cloud can be more difficult. Only some of them allow for a restore from a native backup. In many other cases, you’re moving the data and structures using some mechanism, possibly involving traditional scripts, data exports and imports, and there are all sorts of places where these processes can go wrong. Assuming you make the migration on this step, you’re still going to need to automate your deployments as well.

So, the places where you need help with the migration vary depending on where and how you’re migrating. You may need a tool to perform the migration. You need a way to validate your data and data structures were successfully migrated. You’re also still going to need to automate your database deployments, even after the migration is done. Finally, after you’ve migrated to the cloud, you’re going to need the ongoing support for building and managing databases that you have when you work locally.

Ways to migrate databases to the cloud

Depending on your database system and cloud platform, there are many possible ways to migrate a database to the cloud. I’ll mention briefly just a few of the common approaches, starting with the easier ones.

For IaaS migrations, probably the most common way to perform a migration is simply to restore a database backup, or there are often simple wizards or other tools to help set up and automate the migration. For Azure offline migrations, for example, you can transfer the backups to Azure Blob Storage and restore them to a Managed Instance. If you have BACPAC you can also migrate to Azure SQL Database. There are also tools that will help you automate the migration, both of the database itself and the server objects that are needed to support its operation.

The Azure Database Migration Service will not only migrate SQL Server but can also move other popular database platforms such as Postgres or even an Amazon RDS instance of SQL Server over to Azure SQL Database, and you can also use it to do online migrations, in some cases.

However, there may be migrations where a simple Backup-Restore approach isn’t possible. In these cases, a database migration becomes a bit like an extreme form of database deployment except we now need to deal with bulk data transfers, moving existing server processes, and so on. This means that the tools you use require a more complex process, such as scripting out the database and then bulk copying, transferring, and importing the data. More can go wrong here. Classes of objects, such as triggers, can be missed in the scripting process, constraints can accidentally be left disabled, for example. Then, the data migration itself, depending on the tools used, may fail, in whole or in parts.

You need to verify that the migration worked exactly as you intended. So, what do you need to check?

Validating the cloud migration

Many of the checks that you need to perform to verify a cloud migration are the same as those that you need to perform to ensure that a deployment worked as you expected, and that all the database objects and data are in exactly the right state. You may also want to run your suite of automated tests on the migrated database, just as you would as part of a standard database deployment process.

You’ll also want to perform ongoing monitoring of the cloud database, to validate the overall success of the migration and its impact on performance and throughput.

Checking the schema and data

If you’ve moving a database to the cloud or between different cloud platforms, there is no inherent mechanism to allow you to validate that all the objects have been successfully moved into your new cloud platform. Of course, you’re likely to see outright errors during the migration itself if certain objects can’t be migrated, but that’s not a guarantee. Scripted approaches can fail in more subtle ways, leaving objects, or even parts of objects, behind in the migration. Without a way to compare objects across two databases, you may be reduced to relying on very basic mechanisms such as object counts to check that the objects were successfully moved.

This is where the Redgate schema and data comparison tools come into play. Both Schema Compare for Oracle and SQL Compare allow you to compare the original and migrated databases to identify differences between their schema objects. Further, both tools support connections to all the standard cloud platforms, from Azure to AWS and Google Cloud Platform (GCP). The data comparison tools will allow you to check the data.

Once the migration completes, you can compare the structures between your existing database, whether it’s local on iron, in a local VM, or even on a cloud VM, and the new cloud platform. The Compare tools will find all missing, incomplete or misconfigured database objects. Not only can you get a visual comparison, showing the specific differences, but you can generate a report that can be shared, helping to document your migration success, or migration issues. You can even use the command line interface for these tools to automate this validation as a part of the migration itself, so you have a repeatable way to validate any cloud migration, and immediately document any problems.

Automated testing

For every cloud migration, it makes sense to run a post-migration check that all the basic database processes are working just as they were before. If you already have these tests in place, as part of your automated deployments, you can simply run the same checks.

For example, for SQL Server you can use SQL Test to generate a suite of tSQLt unit and integration tests. The development team will run these tests every time they build a new version of the database. You can also run these exact same checks to validate a migration to a cloud platform. This tool can also be incorporated into whatever DevOps approach you take.

Monitoring cloud databases

For SQL Server and PostgreSQL databases, SQL Monitor provides a standardized set of metrics and alerts across all platforms, plus specialized metrics for each cloud platform, designed to monitor CPU, IO and memory resources that will be ‘constrained’ depending on your chosen instance class (for Amazon RDS SQL Server databases) or service tier (for Azure SQL Databases or Azure SQL Managed Instances).

You can create ‘baselines’ for the standard performance, availability, and other relevant metrics of your on-premises database, plus those metrics that you’ll want to keep a close eye on in the cloud environment, like CPU usage and disk transfers per second (IOPS). Once the migration is complete, ongoing monitoring of your cloud database, checking for regressions or improvements, will help validate the success of the migration.

DevOps deployments to the cloud

Redgate’s DevOps tools make it simple to extend your existing deployment pipeline to cloud platforms. Also, many the good practices that you’ve already adopted while implementing a DevOps style methodology, such as automated deployments and automated testing, will also help you to improve the speed and reliability of your cloud migrations.

The following tools can all form part of an automated deployment pipeline, to get your database code to cloud-based databases, in addition to the schema and data comparison tools mentioned previously:

• Version Control – SQL Source Control for SQL Server and Flyway Desktop (for SQL Server, Oracle and PostgreSQL)  work with cloud VMs as well as Platform as a Service (PaaS) databases
• Deployment Automation – Flyway CLI is a migration-based deployment tool that supports twenty different relational database systems, and connects to VMs and PaaS offerings. It will help you control and automate ongoing cloud database deployment, using CI/CD pipelines.

Together, these tools will help you optimize team-based development processes, and then automate database deployments, from version control, both locally and in the cloud.  They support command line automation so that you can deploy through a variety of standard DevOps flow control tools such as Octopus Deploy, Jenkins, AWS Developer Tools, Azure DevOps and GitHub Actions.

With the ability to automate your database deployments using some combination of these tools, targeting that deployment to a cloud provider is largely just a question of setting it up.

Ongoing development in the cloud

After you’ve started the process of migrating to the cloud, you’re going to need to keep performing standard development work there. Many of the Redgate tools that you’re used to working with will continue to function in the cloud. Many of these are only functional with SQL Server, but they work regardless of where your SQL Server instance lives.

SQL Prompt works with AWS RDS, Azure SQL Database, Azure Managed Instance, and any of the VMs running instances of SQL Server. Other ‘toolbelt essential‘ tools like SQL Doc, SQL Data Generator and SQL Search also work across this spectrum of SQL Server-style cloud offerings. For SQL Server, you can also use data virtualization, or cloning, during development to provision local copies of Azure-hosted databases (similar functionality is coming soon for other databases).

All the functionality that these tools offer will also work in the cloud. So even after your migration, you’ll still be getting the kind of assistance you’re used to with Redgate tools.

Conclusion

More and more organizations are starting the process of moving some of their data management to the cloud. Depending on the data and the mechanisms used to migrate it, the move to the cloud can sometimes be quite challenging. However, the good news is that many of the Redgate tools can directly help you ensure that your migration to the cloud is as painless as possible. You can work with these tools locally as well as in cloud environments, so you can easily learn them, and then apply them, when it’s time to move to the cloud.