Migrating SQL workloads to Microsoft Azure: Databases Trip to Azure SQL Database

Migrating SQL workloads to Microsoft Azure: Databases Trip to Azure SQL Database

In this article, we will discuss how to migrate an on-premises SQL Server database to Microsoft Azure SQL Database.

In the previous article of this series, Migrate SQL workloads to Microsoft Azure: Databases trip to SQL Server on Azure VM, we went through the methods that can be used for assessing your on-premises SQL Server databases before the migration process then migrating it to a SQL Server instance hosted in a Microsoft Azure VM.

In this article, we will discuss how to migrate an on-premises SQL Server database to Microsoft Azure SQL Database.

Why Azure SQL Database

When migrating your SQL Server database to Microsoft Azure SQL Database, you will enjoy several benefits on the target database platform. These benefits include:

  • Being categorized under Platform-as-a-Service (PaaS) platform, Azure SQL Database removes the need to administer and manage the infrastructure of the hosting machines
  • Azure SQL Database provides us with an automatic backup process, with long retention period up to 10 years and restore the backup to a specific point in time
  • Azure SQL Database comes with built-in high availability that consists of three secondary replicas, leading to a 99.99% availability guarantee
  • Azure SQL Database allows you to replicate your database between Azure regions, using the Active-geo replication feature
  • The ability to scale your Azure SQL Database up and down automatically
  • The ability to scale out your Azure SQL Database using multiple shards
  • The ability to share the compute resources between different Azure SQL Databases using the Elastic Pools model
  • Azure SQL Database provides support for Transparent Data Encryption (TDE), which is enabled by default, Dynamic Data Masking, Row-level Security and Always Encrypted security features
  • Azure SQL Databases provides you with two purchasing models, the DTU model for predictive costing level based on the provided tiers and vCore model that allows you to scale the storage and the compute individually
  • Read Scale-Out that provides you with the ability to direct the read-only SQL workloads to the secondary replica

Planning to Migrate

Before migrating your database that is hosted in an on-premises SQL Server instance to Azure SQL Database, you should prepare a pre-migration plan that contains:

  • The tools that should be used to discover and assess the current on-premises database, such as the Microsoft Planning and Assessment tool that can be used to review the on-premises resources and configurations, the Data Migration Assistant that is used to assess the on-premises database for any compatibility issue that may affect the migration process and provide enhancement recommendation for the new setup in Microsoft Azure, and the Database Experimentation Assistant tool that helps in assessing the current workload in the target database platform

  • The alternatives that will be used to replace some of the features and functionalities that are not supported in Azure SQL Database. For example:

  • The SQL Server Agent Jobs, that can be replaced by the Azure Automation and Elastic Database jobs

  • The Windows Authentication logins that can be replaced by Azure Active Directory Authentication

  • And other features and functionalities that are no longer available in Azure SQL Database. Check the Features Comparison documentation for your reference

  • Consider performing some changes on the source or target database that may speed up the migration process. For example, you can disable the auto-statistics, partition the database tables and indexes, drop the indexed views and move the historical data to another database and migrate it separately. Once the migration completed, you can update the statistics and recreate all the dropped objects

Create Azure SQL Database

In order to migrate the database to Microsoft Azure SQL Database, we should prepare the target Azure SQL Database. To achieve that, search for SQL Databases in Azure Portal and click on *Create SQL database *option, as shown below:

Create SQL Database

In the Create SQL Database window, provide the subscription under which the database will be created, specify an existing Resource Group to host that database or create a new one, provide a unique name for the Azure SQL Database and choose the logical SQL Server where the database will be hosted from the existing SQL Servers or create a new one.

When creating a new SQL Server to host that Azure SQL Database, provide a unique name for that server, the region where the server will be created and the credentials that will be used to connect to and administer that server.

You will be asked also to specify whether to create a single database or use an elastic pool to host that database, with other databases that share the same resources pool.

Finally, configure the resources that will be assigned to the created database. It is highly recommended to assign the highest possible service tier and compute size based on the planned budget to get the best transfer performance, with the ability to scale the Microsoft Azure SQL Database down when you finish the migration process to lower the cost.

After configuring the new Azure SQL Database, click Review + Create to proceed with the Azure SQL Database creation, as shown below:

Create SQL Database page

Take into consideration that, we may need only to create an Azure SQL Server for some migration methods, while we need an empty Azure SQL Database to migrate using other methods.

Once created, open the Azure SQL Database then review and edit the configurations of that database to meet your requirements, before migrating the on-premises SQL database to Azure, as below:

Azure SQL database created

To be able to connect to the Azure SQL Database from your machine, click on the Set server firewall option and choose to Add Client IP from the firewall settings page, as shown below:

Set Server Firewall

After that, copy the FQDN name of the SQL Server that is hosting the Azure SQL Database from the portal, and use the SQL Server Management Studio (SSMS) to connect to that server from your machine, providing the previously defined administrator account credentials, as shown below:

Connect to SQL Server

azure sql sql-server microsoft-azure

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

Introduction to Structured Query Language SQL pdf

SQL stands for Structured Query Language. SQL is a scripting language expected to store, control, and inquiry information put away in social databases. The main manifestation of SQL showed up in 1974, when a gathering in IBM built up the principal model of a social database. The primary business social database was discharged by Relational Software later turning out to be Oracle.

SCHEMAS in SQL Server -MS SQL Server – Zero to Hero Query Master

This is part 3 of “MS SQL Server- Zero to Hero” and in this article, we will be discussing about the SCHEMAS in SQL SERVER. Before getting into this article, please consider to visit previous articles in this series from below.

Migrating SQL workloads to Microsoft Azure: Services Selection

In this article, we will go through the different database services that are provided by Microsoft Azure to help you in selecting the proper service that can serve your SQL workload when migrating it to Microsoft Azure

Migrating SQL workloads to Microsoft Azure: Planning the jump

In this article, we will discuss a number of points that should be considered when planning to migrate the on-prems SQL workload to the Microsoft Azure cloud services.

SQL Server on Azure VM Overview | Azure SQL for beginners (Ep. 4)

This video covers the challenges, solutions, and key benefits to SQL Server on Azure VMs. For the full Azure SQL Fundamentals learning path on Microsoft Lear...