다음을 통해 공유

Azure SQL: Schedule A Data Migration From On-Premise To Azure Cloud

  • 아티클

 

↑ Back to top

Introduction

In this post, we will make a brief mention to the latest updates in the Azure SQL service and we see how someone can migrate a database to Azure SQL from an on-premise infrastructure. Before we begin the procedure we must know what Azure Database Services offer.

 

↑ Back to top

Prerequisites

What we need to complete the migration process from scratch is all the following prerequisites.

  • On-Premise SQL Server installation
  • Microsoft Account
  • Azure Subscription
  • Data Migration Assistant Tool
  • SQL Database Instance

 

↑ Back to top

Procedure for Azure SQL Database Deployment

At this point, we log in to the Azure Portal and follow the next steps to create a SQL Database Instance.

1. Search The Service

From the left main blade click + Create a resource, type 'sql database' and hit Enter.

2. Create SQL Database

Click the button Create to begin the deployment.

First, we must create the database, with all important parameters like Server, Pricing Tier, Collation, etc.

Setting Value
Database name Type the database name
Subscription Select an Azure Subscription
Resource group Select an existing or create a new Resource group
Select source Select between a blank, a sample database or create a new from an existing backup
Server* Select an existing or create a new server
Use Elastic Pool** Select elastic Pools for managing multiple databases within a fixed budget.
Pricing tier*** Select the pricing tier for the SQL database service
Collation Default database collation is SQL_Latin1_General_CP1_CI_AS, for this demo purposes we've changed to Greek_CI_AS

2.1. Create Server

If the Server exists this step is not mandatory, but if not then we must see how to deploy a new server.

Setting Value
Server name Type the server name, this MUST be unique
Server admin login Type the Server admin login
Password / Confirm password Type and re-type the Server admin password
Location Select the Location for the Server
Allow Azure services to access server Check this option to allow other Azure applications to access this server
Advanced Data Security A unified security package for discovering and classifying sensitive data, detect anomalous activities that could be harmful to the database.

2.2. Use Elastic Pool

An elastic pool is a good option if we have to manage more than one databases. Imagine

If the SQL elastic pool option is checked

then, a new blade is open and we have to type the elastic pool name and select the Configure pool purchase model.

2.3. Select Pricing Tier

It is quite important to know what are the required resources for the databases because based on those we are able to select the correct tier.

For more details about Services Tiers and their cost, check the following links.

 

↑ Back to top

Procedure to Install DMA in the On-Premises Environment

The first we must to do is to download and install the Data Migration Assistant (DMA).

At the following steps, we can see how easy is the DMA installation.

1. Wizard welcome screen

In the first step click Next to begin installation

2. End User License Agreement

Read the EULA, select the check-box {I accept the terms in the License Agreement} and click Next

3. Privacy Statement

Read the Online privacy statement and select Install.

4. Installing Microsoft Data Migration Assistant

The installation has begun and the new files are copying.

5. Completed the Microsoft DMA Setup Wizard

The installation is completed, and we select Finish to exit from the Wizard.

 

↑ Back to top

The Migration Process

0. Create New DMA Project

Before we start we need to create a new Data Migration project {Assessment / Migration}, type Project name, select Source server type, Target server type, Migration scope and click Create.

1. Select source

The first of the six steps is to select the source server. Select the Server name that the source database is located and click Connect. If the connection is successful then choose the database to migrate and select Next.

2. Select target

Select the target server (SQL Database), a database for the migration process and click Next.

Note

If we intend to migrate Windows users, make sure the target external user domain name is set correctly.

3. Select objects

In the 3rd step, we need to select the schema objects from the source database that shall be migrated to the target database, and click the button Generate SQL script.

4. Script & deploy schema

The SQL script is generated for all selected object we can review, edit (if this needed), and click Deploy schema, to begin the deployment in Azure SQL Database.

If this completed then select Migrate data.

5. Select tables

Select the tables that we want to migrate data to Azure SQL Database and click Start data migration.

6. Migrate data

If we select data migration from the previous step then we see how the migration process is running.

 

↑ Back to top

Migration Demo Video

At the following video file, we can see all the necessary steps for database migration from On-Premise SQL Instance to the one that is deployed in Azure.

View

 

↑ Back to top

Migration Results

Last but not least is to check if the database schema and the data were migrated successfully.

1. Connect via SSMS

Open the SQL Server Management Studio (SSMS) and type Server name, Login, Password and click Connect.

2. Querying the Database

We connect to the database via SSMS and we only need to open tables and check schema and data.

 

↑ Back to top

Conclusion

In this post, we talk about database migration in Azure from an On-Premise infrastructure and how easy is this to be done in a few steps. We created an Azure SQL Database Instance and we migrate an On-Premise database by using the Data Migration Assistant v.4.2 tool.

 

↑ Back to top

See Also

 

↑ Back to top