Migrating on-premises databases to Azure SQL database is becoming a great option for good scalability, cost savings, and flexibility. However, the migration process can be complex, time-consuming, and pose significant risks if not executed properly. In this article, we will provide you with everything you need to know about migrating on-premises databases to Azure SQL databases. We will discuss the benefits of migrating to Azure SQL and will provide tips and tricks to help you plan and execute a successful migration and to ensure a smooth transition to the cloud.

What is Azure SQL Database?

Tips to Migrate On-Premises Database to Azure SQL Database

1. Choose the optimal Service Tier and compute size based on the Budget

Choose the highest service tier and compute size that your budget allows maximizing the transfer performance. You can scale down after the migration completes to save money. Microsoft provides three services tiers which are designed for different types of applications. So, you can choose according to your requirements:

• General Purpose/standard service tier: It offers a balanced budget-oriented option with compute and storage suitable to deliver applications in the middle and lower tiers. Redundancy is built in at the storage layer to recover from failures. It’s designed for most database workloads.
• Business Critical/premium service tier: It is for high-tier applications that require high transaction rates, low-latency I/O, and a high level of resiliency. Secondary replicas are available for failover and to offload read workloads.
• Hyperscale service tier: It is intended for all customers who require higher performance and availability, fast backup and restore, and/or fast storage and compute scalability. This includes customers who are moving to the cloud to modernize their applications as well as customers who are already using other service tiers in Azure SQL Database. The Hyperscale service tier supports a broad range of database workloads, from pure OLTP to pure analytics. It is optimized for OLTP and hybrid transaction and analytical processing (HTAP) workloads.

2. Minimize the Distance between your BACPAC file and the Destination Data Center

If you use BACPAC files, minimize the distance between your BACPAC file and the destination data center. One way to minimize the distance between your BACPAC file and the destination data center is to upload the BACPAC file to Azure Blob storage in the same region as your target Azure SQL Database. Then you can import the BACPAC file from the storage account to the database. This will reduce network latency and improve the transfer performance. Here we have some steps which you can use to upload a BACPAC file to Azure Blob Storage using Azure Portal:

STEP 1: Open the appropriate server page and select Import database on the toolbar.

STEP 2: Now, select Select backup. Choose the storage account hosting your database, and then select the .bacpac file from which to import.

STEP 3: Specify the new database size (usually the same as the origin) and provide the destination SQL Server credentials. Click OK.

STEP 4: To monitor an import’s progress, open the database’s server page, and, under Settings, select Import/Export history. When successful, the import has a Completed status.

STEP 5: To verify the database is live on the server, select SQL databases and verify the new database is Online.

3. Disable auto update and auto-create statistics during migration

Disabling auto-update and auto-create statistics during migration from On-premises SQL DB to Azure SQL DB can have a significant impact on the performance of the migration process and the resulting Azure SQL DB instance.

1. Reduced workload: When auto-update and auto-create statistics are enabled, SQL Server constantly checks the data in tables to update and create statistics. During the migration process, this can create an additional workload and lead to slower migration times. By disabling these features, you can reduce the workload and speed up the migration process.
2. Improved performance: When auto-update and auto-create statistics are enabled, SQL Server can generate suboptimal execution plans that can negatively impact query performance. By disabling these features and manually creating and updating statistics after the migration, you can ensure that the execution plans are optimized for the new Azure SQL DB environment.
3. Consistency: Disabling auto-update and auto-create statistics can help ensure that the migration process is consistent and predictable. By manually managing statistics, you can ensure that the statistics are updated consistently and that the migration process is not affected by any unexpected changes in the data or the statistics.

To disable auto-update and auto-create statistics in SQL Server, you can use the following syntax:

To disable auto-update statistics:

ALTER DATABASE [database_name] SET AUTO_UPDATE_STATISTICS OFF;

To disable auto-create statistics:

ALTER DATABASE [database_name] SET AUTO_CREATE_STATISTICS OFF;

Note that disabling auto-update and auto-create statistics can have a significant impact on query performance, so it is recommended to manually update and create statistics as needed after the migration to Azure SQL DB is complete. You can use the following syntax to manually update statistics:

UPDATE STATISTICS [table_name] [index_name];

And the following syntax to manually create statistics:

CREATE STATISTICS [statistics_name] ON [table_name] ([column_name]); Replace [database_name], [table_name], [index_name], [column_name], and [statistics_name] with the appropriate names in your database.

4. Partition Tables and Indexes

Partitioning tables and indexes can help to improve the performance and scalability of a database and make it easier to manage, which can be particularly useful when migrating data from an On-premises SQL database to an Azure SQL Database. By partitioning tables and indexes in advance, you can optimize the performance of queries that are run against the migrated data and make the migration process faster and more efficient. Partitioning tables and indexes can help in migrating an On-premises SQL database to an Azure SQL Database in several ways:

1. Improved performance: Partitioning a table or index distributes the data across multiple physical partitions based on a partitioning key. This can improve query performance by reducing the amount of data that needs to be scanned to return a result. In a migration scenario, partitioning can help to optimize the performance of queries that are run against the migrated data.
2. Scalability: Partitioning allows for greater scalability by allowing data to be split across multiple physical partitions. This can help to improve the overall performance of the database by reducing contention for resources such as CPU, memory, and disk I/O.
3. Easier management: Partitioning can make it easier to manage large tables and indexes, by allowing you to manage each partition individually. This can make it easier to maintain the data and perform maintenance tasks such as backups, restores, and data archiving.
4. Reduced downtime: Partitioning can be used to migrate data in smaller chunks, reducing the amount of time required to perform the migration. This can help to minimize the amount of downtime required for the migration and make it easier to migrate large amounts of data without impacting ongoing operations.

5. Drop indexed views, and recreate them once finished

Dropping indexed views and recreating them after migration can help in Azure SQL DB migration in several ways:

1. Reducing migration time: Indexed views can significantly increase the time it takes to migrate a database. By dropping them before the migration and recreating them after the migration, you can reduce the overall migration time.
2. Smaller migration package: Dropping indexed views reduces the size of the migration package, which makes it faster and easier to transfer the database to the new environment.
3. Eliminating compatibility issues: In some cases, the target environment may not support certain features or versions of indexed views. Dropping and recreating them can eliminate compatibility issues that might otherwise arise during the migration process.
4. Ensuring optimal performance: Recreating indexed views after the migration ensures that they are optimized for the new environment and can perform as efficiently as possible.

However, it is important to note that dropping indexed views can have implications for the functionality of the database, so it is important to thoroughly test the database after the migration to ensure that all required features and functionality are still available.

Here are the steps to drop and recreate indexed views in SQL Server:

STEP 1: Use the DROP VIEW statement to drop each indexed view. For example:

DROP VIEW [dbo].[IndexedView1];
DROP VIEW [dbo].[IndexedView2];

STEP 2:Use the CREATE VIEW statement to recreate each indexed view. For example:

CREATE VIEW [dbo].[IndexedView1] WITH SCHEMABINDING AS
SELECT …
GO

CREATE UNIQUE CLUSTERED INDEX [IndexName] ON [dbo].[IndexedView1] (…)
GO

CREATE VIEW [dbo].[IndexedView2] WITH SCHEMABINDING AS
SELECT …
GO

CREATE UNIQUE CLUSTERED INDEX [IndexName] ON [dbo].[IndexedView2] (…)
GO

Dropping indexed views and recreating them once finished will help to reduce the size of the data to be migrated and avoid compatibility issues with the target environment. However, you should be careful not to drop any views that are required by your applications or other database objects. You should also test the performance of your queries before and after dropping and recreating the views.

6. Move Rarely Queried Historical Data to Azure SQL Database for Efficient Querying

Azure SQL DB Elastic Queries allows you to run a single T-SQL query that spans multiple databases in Azure SQL Database. With Elastic Queries, you can easily aggregate data from multiple databases without the need to manually manage database connections or data movement. You can improve the performance and cost of your migration by moving the historical data that is not frequently accessed to a different database in Azure SQL Database. This way, you can reduce the size of the main database and avoid unnecessary data transfer. You can still query the historical data from the main database by using elastic queries, which allow you to access remote tables or views as if they were local.

To query the historical data from the main database by using elastic queries, you need to do the following steps:

1. Create an external data source that defines the connection information for the remote database that contains the historical data.
2. Create an external table that maps to the table or view in the remote database that you want to query.
3. Use a regular SELECT statement to query the external table as if it were a local table.

For example, if you have a database called db1 that contains current data and another database called db2 that contains historical data, you can use elastic queries to query both databases from db1. The following code shows how to create an external data source and an external table for db2 in db1.

STEP 1. Create a master key

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘password’;

STEP 2. Create a database-scoped credential

CREATE DATABASE SCOPED CREDENTIAL ElasticQueryCred WITH IDENTITY = ‘username’, SECRET = ‘password’;

STEP 3. Create an external data source

CREATE EXTERNAL DATA SOURCE ElasticQuery WITH ( TYPE=RDBMS, LOCATION=‘server.database.windows.net’, DATABASE_NAME=‘db2’, CREDENTIAL= ElasticQueryCred,

STEP 4. Enable elastic query for this data source

ENABLE_ELASTIC_QUERY = ON ;

STEP 5. Create an external table

CREATE EXTERNAL TABLE [dbo].[db2Country] ( [CountryID] INT NOT NULL, [CountryName] NVARCHAR(50) NOT NULL ) WITH ( DATA_SOURCE = ElasticQuery, SCHEMA_NAME = ‘dbo’, OBJECT_NAME = ‘Country’ );

STEP 6. Query the external table

SELECT * FROM [dbo].[db2Country];

7. Threat Detection

Before you migrate your SQL Database to the cloud it is very important to understand that you enable threat detection features in Azure SQL because it is the most important part of your migration planning. Threat Detection helps customers to detect and respond to potential threats as they occur. Users can immediately explore the potential threat by using SQL database auditing to determine if the event is the result of an attempt to access, breach, or exploit data in the database. Threat detection can alert you to suspicious activities such as SQL injection attacks, data exfiltration, brute force login attempts, and more.

Azure SQL Database Threat Detection is easy to configure through Azure Portal. It has many benefits:

1. Receive clear email alerts when suspicious database queries are detected, indicating potential SQL injection events
2. Explore the audit log around the time of the event using the Azure portal or pre-configured Excel template.
3. No modification of database procedures or application code is necessary.

To set up Threat Detection, follow the below steps:

1. Sign in to the Azure portal.
2. Navigate to the configuration page of the server you want to protect. In the security settings, select Microsoft Defender for Cloud.
3. On the Microsoft Defender for Cloud configuration page:

a. If Microsoft Defender for SQL hasn’t yet been enabled, select Enable Microsoft Defender for SQL and  Select Configure.

b. Under ADVANCED THREAT PROTECTION SETTINGS, select Add your contact details to the subscription’s email settings in Defender for Cloud.

c. Provide the list of emails to receive notifications upon detection of anomalous database activities in the Additional email addresses (separated by commas) text box.

d.Optionally customize the severity of alerts that will trigger notifications to be sent under Notification types.

e. Select Save.

8. Monitoring and tuning your workloads

After you finish your migration it is important to start monitoring your workloads so you can understand and compare the performance before and after the migration and identify the issues well before in time. To monitor Azure SQL Database performance, begin by monitoring the workload’s CPU and IO resource usage in relation to the chosen database performance level for a particular service tier. Azure SQL Database emits resource metrics that are viewable in the Azure portal, which enables accomplishing this task. Below are some of the features and services available in the Azure portal to monitor and tune your workloads are:

• Metrics view: You can quickly monitor a variety of resource metrics such as CPU, memory, IO, or storage usage for your database or instance.
• Database advisors: You can get performance-tuning recommendations for single and pooled databases such as creating or dropping indexes, forcing query plans, or changing compatibility levels.
• Query Performance Insight: You can get query performance analysis for single and pooled databases such as top-consuming and longest-running queries, wait for statistics and resource utilization trends.
• Automatic tuning: You can enable Azure SQL Database to automatically implement the tuning recommendations provided by the database advisors.
• Intelligent Insights: You can get intelligent assessments of performance issues and improvement suggestions based on built-in intelligence and artificial intelligence. You can also enable the streaming export of these insights and other resource logs to one of several destinations for consumption and analysis.

9. Use Azure SQL Migration Extension for Azure Data Studio

The Azure SQL Migration extension for Azure Data Studio helps you to assess, get the right-sized Azure recommendations and migrate your SQL Server databases to Azure. Here are the benefits of using Azure SQL Migration Extension

• It offers a responsive UI for an end-to-end migration experience.
• The extension provides a mechanism to evaluate SQL Server instances and identifies databases ready for migration to Azure SQL targets.
• An SKU recommendation engine collects performance data from on-premises source SQL Server instances to generate right-sized SKU recommendations for the Azure SQL target.
• The extension uses Azure Database Migration Service to orchestrate data movement activities for a seamless migration experience.
• The migration can be run online or offline depending on business requirements.
• A self-hosted integration runtime can be configured to access source SQL Server instance backup files in on-premises environments.
• The extension provides a secure and improved user experience for migrating TDE databases and SQL/Windows logins to Azure SQL

Conclusion

Migrating an on-premises database to Azure SQL Database can be a complex process, but with the right preparation and execution, it can be a successful and smooth transition. It’s important to consider factors such as data security, compatibility, and performance requirements when planning the migration, and to use the appropriate tools and methods for transferring data to the Azure platform. By following the tips you can simplify the migration process and avoid potential pitfalls, allowing you to take full advantage of the benefits of Azure SQL Database.