Migrating SQL Server Databases to the Cloud

Migration of databases from on-premises environment to the cloud is becoming increasingly popular owing to the consumption-based pricing model, unlimited scalability, flexibility, and other benefits available in the cloud setup.

In fact, 75 percent of databases are expected to be deployed in or migrated to the cloud this year, according to a Gartner’s report .

However, moving SQL Server workloads from on-premises to the cloud is a challenging task as it can cause downtime and requires code restructuring. So, it’s vital to choose the right cloud service provider (CSP) to get greater returns from your cloud migration.

There are multiple cloud service providers to choose from. The leading ones are Amazon Web Services (AWS), Microsoft Azure, and Google Cloud. However, SQL Server runs best on Microsoft Azure . The Azure-based cloud service (such as Azure SQL Database) offers various benefits, such as built-in security features, one-click backup and recovery capabilities, etc.?

There are several ways to migrate SQL Server database to Azure SQL Database:

• Assess and migrate data to Azure SQL DB using Data Migration Assistant
• BACPAC Export/Import
• Use Transactional Replication to replicate your on-premises database(s) to the cloud

This article will discuss the first two methods of migrating databases running on SQL Server on-premises to Azure SQL Database. For information on using transactional replication for the migration, see this Microsoft documentation . Before we proceed, let’s first discuss the reasons behind migrating databases to Azure SQL Database.

Why Migrate SQL Server Databases to Azure SQL Database?

Azure SQL Database is a relational database service hosted on the Microsoft Cloud. This Azure-based cloud service can handle mission-critical database workloads. Plus, it provides advanced security options and features like point-in-time-restore, automated backups, etc., to increase business continuity. Further, it supports SQL Server tools, APIs, and libraries, making it easier for you to deploy the SQL Server solutions to the cloud without learning new skills.

Ways to Migrate SQL Server Databases to Azure SQL Database

To migrate your on-premises SQL Server database to Azure SQL Database, you can use any of these methods:

1. Migrate Data using Data Migration Assistant

Azure Database Migration Service is designed to support offline and online migration from multiple database sources (such as SQL Server, Oracle, MySQL, PostgreSQL, and MongoDB) to Azure data platforms. The Azure service helps migrate SQL Server databases to Azure SQL DB using the Microsoft Azure portal or PowerShell.

Before proceeding with the migration process, it’s vital to assess your on-premises database for any blocking issues (such as compatibility problems) that might prevent the migration. Once the assessment is complete, you can migrate the sample schema to Azure SQL DB. The Azure service provides Data Migration Assistant (DMA) tool for data assessment and migration.

Before using the DMA tool, you need to meet these prerequisites:

• Create a new database in Azure SQL Database using the Azure portal.
• Must have the latest version of Data Migration Assistant tool installed on your system.

Following are the step-by-step instructions to assess and migrate the on-premises database to Azure SQL Database using the DMA tool.

Step 1 – Assess your On-Premises Database

• Launch Data Migration Assistant. In the window that opens, click the New (+) icon, and then choose Assessment?option under project type.
• Type Project name, select Database Engine from the?Assessment dropdown list, and select the source server type.
• Select Azure SQL Database from the Target server dropdown list.?
• Click the Create button for creating the project.

While assessing the source database, you can choose to check one or both of these reports: Database Compatibility and Feature Parity.

• In the DMA, click Next on the Options screen.
• In ‘Select sources' screen, specify the connection details of the server in the Connect to a server dialog box, and then click Connect.
• In 'Add sources' window, choose the database you want to migrate, click Add, and then choose Start Assessment. The following screen is displayed upon completion of the assessment process.

Once you are done with the assessment, move to the next step.

Step 2 – Migrate Data to Azure SQL Database

• Click the New (+) icon in the DMA tool and select Migration under?Project type.
• Enter a project name, choose SQL Server from the Source server dropdown list, and then choose Azure SQL Database from the Target server type textbox.
• Choose Schema only under?Migration Scope.

After executing these steps, here’s how the Data Migration Assistant interface will look like:?

• Click Create?to create the project.
• In the screen that appears, enter details to connect to the source SQL Server, click?Connect, and then choose the?on-premises database (i.e., AdventureWorks2016?in our case).

• Click?Next.
• Under?Connect to target server, enter details of the target Azure SQL Database, choose?Connect, and then choose the?Azure?database pre-created in Azure SQL Database.

• Choose?Next?to skip to the?‘Select objects’?screen. In this screen, select the schema objects you need to move to Azure SQL Database.

• Create SQL scripts by clicking on Generate SQL script. Once the scripts are generated, review them for any errors.

• Click Deploy schema for deploying the selected schema to Azure SQL Database.?

After deploying the schema, check the target Azure SQL Database instance for any anomalies.

2. BACPAC Export/Import

Another option to migrate databases from SQL Server to Azure SQL Database is by using a BACPAC File. It is a Windows file with a .bacpac extension that contains all the schema and data to be migrated.

The process starts with exporting schema and data from an on-premises database to the BACPAC file using the Export Data-Tier Application Wizard. And then, you can import from the BACPAC file into an Azure SQL DB using the Azure Portal.

The detailed steps are as follows:

Step 1 – Export to a BACPAC File using Export Data-Tier Application

• Open SSMS and connect to the database you want to migrate.
• Right-click the database, hover over to Tasks, and then click Export Data-Tier Application.

• On the ‘Export Data-tier Application’ window, click Next to skip the default settings page.

• From the ‘Export Settings’ screen, choose to save the BACPAC file to Microsoft Azure. Doing so will upload the file to Azure Blob Storage.

Step 2 – Import from BACPAC File into Azure SQL DB using Import Database

Follow these steps to import from BACPAC file into a new database in Azure SQL Database:

• Open an appropriate server page using the Azure portal . Once the page opens, click Import Database from the toolbar.

• Choose a storage account and container for the BACPAC file.
• Choose the BACPAC file from which you need to import the schema and data.
• Enter the new database name, size, and credentials of the destination SQL Server.?

• Click?OK to initiate the process of importing the BACPAC file into the new Azure DB.?

Note: For large databases, the import procedure can take a lot of time to complete. So, it’s recommended to monitor progress of the Import/Export process by selecting the Import/Export history tab.?

You will see a?Completed?status once the import process is complete.

Note: You can import a BACPAC file into a single Azure DB. For migrating multiple databases, you will need to repeat steps 1 and 2.

Refer to this link for further information on migrating from an on-premises location to Azure SQL Database using BACPAC files.?

End Note

You can use Data Migration Assistant to move multiple databases concurrently. However, avoid using it for migrating large databases greater than 1 TB. Instead, you can use the BACPAC export/import method for quick and easy migration. But, you can only use BACPAC export/import to migrate small to medium-sized databases (less than 1 TB). Use Transaction replication to keep your SQL Server database online during the migration.

When migrating multiple databases from on-premises SQL Server to Azure SQL Database, databases can become corrupted. To save downtime and avoid the risk of losing table structures, using Stellar Repair for MS SQL can come in handy. The software can help fix corruption in the database(s) and recover the database objects in their original state.?