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:

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:

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:

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:

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:

#azure #sql #sql-server #microsoft-azure