Steps to migrate SQL Server Database to Azure.

Based on the criticality of the database, migration can be classified into two ways:

·           Offline Migration.

·           Online Migration.

Offline Migration:

As the names are self-explanatory if the database that needs to be migrated to Azure is not a highly critical database and downtime is allowed then we can migrate the database through offline migration. During offline migration, the databases will not be available for access and in turn, the applications relying on it also will not be available.

Online Migration:

If the database is highly critical then we should plan an online migration of the database with very minimal downtime. In Online migrations, the source database will be available during migration activity and once the entire database is migrated then the application connections need to be pointed newly migrated Azure database. After diverting connections to the Azure database any pending transactions from the source servers needs to be replicated to the Azure database.

Which database can be migrated to Azure:

We can migrate a SQL Server database if the database is of SQL Server 2005 and above version. We can migrate DB2, MySQL, MongoDB, Amazon Web Services Elastic Compute Cloud (AWS EC2), Compute Engine (Google Cloud Platform (GCP)), Amazon Relational Database (AWS RDS) and many others. The complete list of different source databases that can be migrated to Azure is mentioned here.

Which Azure tier should I move my on-premise database to?

Another important decision we need to make before migrating the on-premise database is “Which Azure tier subscription should I move my database to?”. As we know there are different tiers in Azure like Basic, Standard, and Premium. Each tier is suitable for specific workloads, performance requirements, space requirements, and so on. This link helps in determining the suitable tier and DTU’s.

You can read about what are DTUs and eDTUs here.

How much money you can save by migrating to Azure:

We can use the Total Cost of Ownership (TCO) calculator which gives us details of how much money we would be saving if we migrate our existing environment to Azure. Once we enter the details of our existing environment like how many servers, databases, their configuration and so on this calculator will generate a detailed report showing what are different costs involved in our environment and how much cost we would be saving if we migrate to Azure. 

https://azure.microsoft.com/en-in/pricing/tco/calculator/

 Below is one small part of the TCO report, along with this it provides much more detailed information report:

Tools\Methods Available For SQL Server Database Migration:

There are many tools and methods available for migrating an on-premise SQL database to Azure. Depending on the environment and requirement each tool can be used for migration.

·        Data Migration Assistance (DMA)

·        Database Migration Service (DMS)

·        Transactional Replication

·        Bulk Load

·        Export Data-tier Application

·        Deploy Database to Microsoft Azure

The migration tools list is not limited to these, there are numerous others as well. The entire list of Microsoft and Third-Party tools that are useful in Planning, Assessment, and Migration of databases from different sources to Azure is mentioned in detail in this link.

We will discuss two important tools that are very useful during database migrations that are DMA and DMS.

Data Migration Assistance (DMA):

This is one of the very useful tools that help during database migrations. With this tool, we can find out what existing features of our source server are invalid in the Azure environment. This tool is helpful in identifying the unsupported or deprecated features of the source database. With the DMA version 5.2.4764.4 we can assess both the database engine and SSIS.

Below is sample assessment report of DMA:

You can see in the screenshot after assessing my SQL Server 2017 version database it is giving details like that there are 2 “Unsupported Features” in my database. SQL Server Agent jobs and Azure Key Vault integration. It will give clear information on what would be the impact and will provide recommendations like what are the alternatives\fixes for the issues found. This assessment report can be saved for reviewing later.

Remember we can assess and migrate as well the source database with the DMA tool. But DMA is mainly preferred for migrations of small databases and also we can migrate one database at a time.

Database Migrations Service (DMS):

DMS tool helps in migrating databases both offline and online. Azure Standard tier supports only offline migration. Online migration is supported only with the premium pricing tier of the Azure. DMS is useful in migrating big databases and with DMS we can migrating multiple databases at a time.

Below are the basic steps to migrate SQL database to Azure:

Below steps are written assuming we are migrating the on-premise SQL database to an Azure database. In future blogs, we will see how to migrate the SQL database to the Azure database using different tools and methods.

·         Gather details of the on-premise SQL database.

·         Create an Azure SQL Server.

·         Create an Azure SQL database.

·         Install Data Migration Assistant (DMA).

·         Assess the source database using DMA.

·         Fix if any discrepancies found.

·         Test the migration in the lower environment first.

·         Migrate the schema and data from the source database  to the Azure database.

·         Verify the migrated data in the Azure database.

The above are not fixed migration steps. Depending on the type of migration, source server, target server, size these steps will change. Also haven’t included standard steps that need to be followed for any type of database maintenance activities like scheduling during a suitable maintenance window, prior client approvals, backups, and so on.

Post Migration:

Once the database is migrated successfully, point the applications to Azure and perform testing. Make sure the required logins also migrated and perform if any performance tuning required.

Hope this article has given you a brief idea about different steps involved in the database migration project.

Let me know in the comments section below which method and tool you use for performing database migrations in your environment.

Thanks VV!!

#azure #azuredatabase #databasemigration #sqlserver #azuresql #migration #sql #AzureSQL