• Microsoft’s Azure platform for databases can help to reduce costs and support a higher-functioning IT infrastructure.
• Migrating to the cloud could help you to strengthen cyber security and increase productivity.

Pre-requisite for Migrating an on-premises database to Azure.

1. Explore Database compatibility: Before we begin a database migration to Azure, we will need to ensure our current on-premises database is compatible with the Azure database. Compatibility issues occur when features that exist in an on-prem database – such as linked servers, trace flags or file streams – are not available in the cloud. You can check for compatibility issues using Microsoft’s Data Migration Assistant.
   
2. Select the right Azure Service Model: The overall pricing, service tier, storage and performance of the Azure relies upon the service model we choose, so choosing the correct one in the first instance will save you time further down the line.
   The database will need deploying as either an individual Azure SQL database, within an Elastic Pool, and Azure SQL Managed Instance, or within an Azure Virtual Machine.
   
3. Identify your required disaster recovery level: Azure offers various levels of disaster recovery protection, including high availability and geo-replication across global data centres. What level of disaster recovery we select should be based on your organisation’s recovery point objective (RPO) and recovery time objective (RTO).
   
4. Devise a migration strategy: Before we migrate our on-prem database to Azure, we need to plan our migration strategy. The types of things we should consider include, choosing and online or offline strategy by establishing if database downtime required for the move is a possibility, and which migration tool we’re going to use.
   
5. Migrate a test database: It’s important to have a trial run of migrating the live database before officially migrating the live system to ensure that everything works as expected and to identify any potential issues. During the migration of test copy, we should check for:
   Migration errors – if any of these are reported, you will need to analyse and fix them ahead of the official migration.
   How long it takes to migrate schema and the database – if the downtime is too long, you may want to consider an online migration.
   Any external application or user connection issues (VPN, firewalls etc.)
   Data readability – can data be read and written to the Azure database as expected via the application and direct SQL connections or roles.
   Performance level – do a test run of some of the actions that you may take on the database i.e., running a report and record the time it takes to assess if the performance level is acceptable.
   
6. Migrate your Databases: Once you’ve worked your way through each of the steps above, you are ready to successfully migrate your database to Azure.

How can you validate whether your database is compatible with Azure SQL Database?

This can be validated in multiple ways:

• Create a BACPAC file: If you can create the BACPAC file from your database that means your database can be migrated to Azure SQL Database
• Use a script: Generate a schema of the on-premises databases and create the same in the Azure SQL environment.

What are some ways that data be migrated?

There are several ways to migrate the data. The following are some of the methods that can   be used for data migration:

• Create BACPAC files and export them.
• Use SQL Server Management Studio; generate scripts and use the import/export data methods.
• Use the Data Migration Assistant Wizard
• Leverage Transactional Replication to port the data over to the cloud.
• Use PowerShell and SQLPackage.exe, an SSDT tool.

Considering a career in Azure?

Start with a free course in Azure Fundamentals AZ-900

Exporting database using export data-tier application

Create BACPAC files using Management Studio

1. Open SQL Server Management Studio (SSMS) and connect to the source database in Object Explorer. In this case, the source database is XXXXX
2. Right-click XXXXX database in Object Explorer, point to Tasks, and click Export Data-Tier Application.
3. In the export wizard, click Next to bypass the default setting page.
4. In the Export Settings tab, configure the export to save the BACPAC file to either a local disk or to Azure blob storage and click Next.
5. In this case, let us follow the default settings.
6. Click the Advanced tab and clear the Select All checkbox to skip exporting the data. Our goal at this point is only to test for compatibility.
7. Select the Save to Microsoft Azure option to save the bacpac file to Azure Blob storage.
8. Next, go to the Azure portal to get the storage account details. In this case, dbmigratestg is the storage account created for this exercise.
9. Select the storage account and copy the access key. Paste the key in SQL Server Management Studio and click Connect.
10. Now, you can access the Azure Blob storage.
11. Click Advanced. In this pane, you can select the intended objects that are going to be part of the migration.
12. Before you click the Finish button, let us validate the summary in detail.
13. Click Next and then click Finish.
14. The Export wizard performs the database compatibility checks. If any issues are found, they will appear after the wizard validates the schema.

Importing file using Import database option

In the process of migrating on-premises SQL Server database to Azure SQL Database, now we do the import of .bacpac file.

Importing the BACPAC file to Azure

Now that we have the BACPAC file ready, we can now use the same to migrate the data over to Azure. This BACPAC file could be stored locally, or on Azure Blob storage (standard).

For now, the Azure portal will allow you to only create a single database in Azure SQL Database, and this can be done only from a BACPAC file. Follow the steps below to import the BACPAC file you saved.

15. Connect to Azure portal and open the SQL database page. Navigate into your resource group and create a new instance of an Azure Database. The Assumption is that Azure PaaS SQL Database is available.
16. Next, select the Import database on the toolbar.
17. Next, locate the Blob storage account and its respective container for the BACPAC file.
18. Type in the new database name, size, and the SQL admin credentials.
19. Clicking OK will begin the process of importing the BACPAC file into the new Azure Database, and you should be good to go.

Using SSMS, generate scripts and use import/export data methods

1. In SQL Server Management Studio, connect to your local SQL Server
2. Create a new database named: XXXXX.
3. Right click on that database and choose from the drop-down context menu All Tasks | Import Data
4. This will open the SQL Server Import and Export Wizard dialog.
5. Click next to get past the starting page.
6. On the next page of the wizard you choose a data source. In this example, this is SQL Azure. The data source you need to connect to SQL Azure is the .NET Framework Data Provider for SqlServer.
7. Scroll to the bottom of the properties, here is where you need to enter your SQL Azure information.
8. Under Security set Encrypt to True.
9. For the Password enter your SQL Azure password.
10. For User ID enter your SQL Azure Administrative username.
11. Under Source for Data Source enter the full domain name (Server Name) for your account on SQL Azure. You can get this from the SQL Azure Portal.
12. For Initial Catalog enter the database name on SQL Azure.
13. Once you have all the data source information filled out, you can click on Next > and the Choose a Destination wizard step will appear.
14. The destination server is your local server in this example. Because we chose Import Data… our local database is already filled in for us. Enter your security credentials and click Next >. The Specify Table Copy or Query dialog will appear.
15. Chose Copy data from one or more tables or views radio button. Then press Next >.
16. The Select Source Tables and Views dialog will appear, and the SQL Server Import and Export Wizard will query the SQL Azure database and return a list of tables and views on SQL Azure. Check the ones that you want to import into your local database.
17. Once you have selected the tables and view press Next > and the Save and Run Package wizard step will appear.
18. Click the Finish >>|button and then Finish again and the SQL Server Import and Export Wizard will import your data from SQL Azure to your local SQL Server.

Level up your data expertise with Azure Data Fundamentals DP-900 training

Using Data Migration Assistant Wizard

Microsoft Data Migration Assistant

The Data Migration Assistant (DMA) tool is used mainly to check the compatibility issues that may affect the database functionality when migrating your databases to a new SQL Server version or to Azure SQL Database. DMA helps by identifying any feature in the current version that is not supported in the new version or in the cloud, what new features in the new version we can benefit from, providing recommendations to enhance the performance and the reliability in the new version and finally migrate the on-premises version to a newer version or to Azure SQL Database.

The Data Migration Assistant can be used to assess and migrate any SQL Server installed on Windows machine with version 2005 and later, to any SQL Server instance installed on Windows or Linux with version 2012 and later or to Azure SQL Database. DMA tool is recommended as an alternative to the SQL Server Upgrade Advisor tool to assess and upgrade to the new SQL Server versions. For migrating to Azure SQL Database, it is recommended to use the Azure Migration Service instead.

The Data Migration Assistant can be downloaded from Microsoft Download center and installed to your machine, using a straight-forward installation wizard, as shown below:

Once installed to your machine, you need to be a member of the sysadmin fixed SQL Server role in order to use that tool.

The Data Migration Assistant provides you with the ability to configure the number of databases to assess in parallel, Number of databases to migrate in parallel and the SQL connection timeout from the dba.exe.config configuration file before start using that tool. In this demo, we will use the default values for assessing and migrating without changes.

When you start the Data Migration Assistant, click on the (+) icon to create a new assessment project to check any blockers, unsupported features or recommendations before migrating your databases from on-premises site to Microsoft Azure SQL Database. You need to provide a meaningful name for the project, the type of assessment, source and destination data platforms, as shown below:

After that, you will be asked to provide the SQL Server name and the credentials that will be used to connect to that SQL Server instance, as below:

Once connected, all user databases hosted under that instance will be listed, providing you with the ability to choose the databases that will be assessed before migrating it to Microsoft Azure SQL Database, as shown below:

Now the databases are ready for the assessment. Click on the Start Assessment option to assess the databases in the selected list, as shown below:

Once the assessment process completed successfully, review the list of breaking points and unsupported features that are provided by the Data Migration Assistant, with the ability to save the assessment result, export it or upload it to Azure Migrate tool, as shown below:

Azure Database Migration Service

The Azure Database Migrate service provides you with the ability to perform online or offline database migration from a large scale of database sources, such as SQL Server, MySQL, Oracle, DB2, MongoDB and PostgreSQL, to Microsoft Azure Data platform using the Azure Portal and with the minimal downtime.

Internally, the Azure Database Migrate service uses the Database Migration Assistant tool to generate the assessment reports, providing all changes required before starting the migration process.

Before creating a new Azure Database Migration Service instance and use it to migrate the databases to Microsoft Azure, we should register the Microsoft.DataMigration resource provider.

This can be performed by opening the Azure portal and browsing the subscription under which we plan to create the Azure Database Migration Instance. From the selected subscription move to the Resources Providers option and search for Microsoft.DataMigration resource provider and register it. Once it is registered, the status will be changed to Registered.

Now we are ready to create a new instance of Azure Database Migration Service.

From the Azure portal, search for Azure Database Migration Service under All Services and click (+ Add) to create a new instance. In the Create Migration Service window, provide the Resource Group name where the instance will be created, or create a new resource group, choose a unique meaningful name for the migration service, choose the nearest region for you then click Next to move to the Networking configurations.

From the Networking configuration page, provide the name of your VNET where the migration service will be created, or provide a new name to create that VNET. The VNET is required for the Azure Database Migration Service to provide it with access to the source database and target environment. After providing all the required information, click Review + Create to proceed with the migration service creation.

Within a few minutes, the Azure Database Migration Service will be created successfully and ready for a new migration project.

To create a new migration project, click on the New Migration Project option, from the Azure Database Migration Service

From the New Migration Project window, provide a unique meaningful name for the project, the source and destination database platform type. You can see that the Azure Database Migration Service provides you with some points that should be performed before migrating the databases. This includes creating the destination database on Azure, assessing the database using DMA tool as we performed previously and fix the issues detected by the DMA then migrate the schema using the DMA tool,

When you create the migration project, it will move you to the migration wizard and ask you to provide the required information to connect to the source database, the destination database, map the source database with the destination one and select the list of tables to be migrated.

Author: Mayank Minal is a Principal Consultant at Fusion Practices