Cloud Migration 101: Moving from SQL Server to Azure

More and more organizations are recognizing the benefits of moving from a solely on-site database infrastructure to a cloud or hybrid cloud infrastructure. Cloud computing platforms like Microsoft Azure offer several advantages over on-premise setups, including:

• Cost savings: With little to no on-site hardware to buy and maintain and low-cost storage options, moving to Azure will cut direct expenses immediately.
• Better scalability: In the cloud, you can scale computing needs up or down as needed without leaving expensive resources sitting idle during slow periods.
• Increased security: Azure employs state-of-the-art data security measures both in the cloud and in their physical data centers. Most organizations can’t afford to match that level of protection.
• Easier administration: Data security and management can be handled centrally through Azure, which cuts down on the time DBAs spend on repetitive tasks.
• More reliable disaster recovery: Azure data centers are geo-independent, so if a catastrophic event hits one data center, it automatically fails over to an unaffected one with no loss of data or service.

If you are ready to make the move from on-premise SQL Server to Azure SQL Database, there are four main steps in the migration process you will need to navigate. Here is a brief overview of each with links to in-depth resources. 

Step One: Choose Your Migration Method

Before you begin migrating SQL Server to Azure, you need to determine whether an offline or online migration using Azure Database Migration Service is the best option for you. There are a few things to consider during this process.

With an offline migration, downtime starts when the migration begins. With an online migration, downtime is limited to the amount of time needed to cut over to the new environment after migration. So if minimal downtime is a hot-button issue for you, online migration is a better choice.

You can also minimize downtime during the migration process by using the Always On Availability Group option or the transactional replication option to transfer data into Azure Virtual Machines (VMs). However, the best way to transfer the data is to migrate the database files into the Azure VM using a compressed backup file. 

If you can’t use any of these methods, you’ll have to manually migrate your database into an Azure VM using one of the following methods:

• Conduct on-premises backup using compression, then manually copy the backup file into the Azure VM
• Backup to URL, then restore into the AzureVM from the URL
• Detach, copy data, and log files to Azure blob storage, then attach to SQL Server in Azure VM from URL
• Convert on-premises machines to Hyper-V VHDs, upload to Azure Blob storage, then deploy as new VM
• Ship the hard drive using Windows Import/Export Service
• Add Azure Replica Wizard
• Use SQL Server transactional replication

Step Two: Identify Existing Data Sources and Assess On-Premise Instances

Step two is considered “pre-migration,” and these instance assessment activities will help you configure your current system to facilitate the migration. 

First, you need to take inventory of the databases you need to migrate to ensure you have them all optimized for the move. Once you have identified all of the databases, you need to assess them for potential migration issues or blockers and resolve any issues you find. 

Then, you need to identify all of your organization’s SQL Server instances and their versions and features in use. When the data sources have been identified, use the Data Migration Assistant (DMA) to assess your source database.

Here are the steps to follow when using the DMA to assess your instances:

• Create a new assessment project in DMA
• Name the project and make SQL Server the source server type and Azure SQL Database the target server type
• Select which assessment reports you want to generate
• Identify your SQL Server’s source connection details, connect to the source database, and start the assessment
• When finished, review the assessment reports for blocking issues and feature parity
• Select a database compatibility level
• Select an Azure SQL Database managed instance SKU for your on-premises workload

Get detailed information on how to perform the assessment process here.

Step Three: It’s Migration Time!

Now that the prerequisites are taken care of, it’s time to start the migration to Azure—first schema; then data.

Microsoft provides this high-level look at how to migrate schema using DMA:

• Open DMA, and then begin creating a new migration project
• Specify a project name, select SQL Server as the source server type, and then select Azure SQL Database as the target server type
• Specify the migration scope as Schema only, and then create the project
• Specify the source connection details for your SQL Server, and then connect to the source database
• Specify the target connection details for the Azure SQL database, and then connect to the database you had pre-provisioned in Azure SQL Database
• Specify the schema objects in the source database that need to be deployed to Azure SQL Database
• Generate SQL scripts, and then review them for any errors
• Fix the objects that report errors by leveraging the recommendations provided by your DMA assessment
• Deploy the schema to Azure SQL Database, and then check the target server for any anomalies

Here are the specific steps for online and offline schema migrations.

Microsoft provides this high-level look at how to migrate data using Azure DMS :

• Register the Microsoft.DataMigration resource provider
• Create an instance of DMS
• Create a migration project in DMS
• Specify source details for the migration
• Specify target details for the migration
• Run the migration
• Monitor the migration

Here are the specific steps for online and offline data migrations. 

Step Four: Test the New Environment After Migration

Now that you’ve moved your SQL Server database to Azure SQL Database, here are four ways to thoroughly test the new environment to make sure the source database migrated properly: 

Develop validation tests: Create validation queries to run against the source database and the target database.

Set up the test environment: Place a copy of the source database and a copy of the target database in an isolated test environment.

• Run validation tests: Run the validation tests against the source and target databases, then examine the results.
• Run performance tests: Check performance of source database and target database, then compare and analyze the results.

Strategic planning is important to a successful migration of your database to a cloud or hybrid cloud environment. Once your migration is complete, you’ll be able to reap the benefits of working with a cloud-based database.