By: Mohammed Moinudheen | Updated: 2021-12-07 | Comments (2) | Related: 1 | 2 | 3 | 4 | > Azure SQL Database
Problem
There are various ways that a database can be migrated from on-premises to Azure SQL Database. In this article we look at various ways this can be done.
Solution
This tutorial assumes that you already have resources in your Azure environment and you are familiar with the Azure portal. If you are new to Azure, you can subscribe to a trial account here, which will give you access to Azure services for a year and a credit of USD $200. In this tip, we will explore the various options that are available to migrate on-premises SQL Server databases to the Azure SQL Database platform.
There are number of Microsoft tools that are already available and can be used for migrating on-premises databases to Azure SQL Database. We will describe the options at a high level to give you an understanding of the options that can be explored.
Azure Migrate
You can find this tool within the Azure portal when you search for Azure Migrate.
Azure Migrate is a centralized hub where a number of tools are available to perform tasks like discovery, assessment of your servers and perform the actual migration. As you can see Microsoft is promoting this like a one stop shop to plan your migration journey of your on-premises servers or databases to Azure.
This tool will be handy especially if you have a huge estate of on-premises servers as it will help you to perform the discovery of your data center and check for things like dependencies between servers and applications. In the assessment stage, you can use this tool to detect any sort of migration blockers, get SKU recommendations and get cost estimate for running similar workload on Azure. For the actual migration, based on the recommendation in the assessment stage, you could either use tools for server migration or database migration. The tool is straight forward to use as the required tools can be added from the hub based on your requirement.
Going deeper into this tool is beyond the scope of this tip as it only covers the migration options that are available at a high level.
Data Migration Assistant (DMA)
This is an easy to install desktop tool which can help in assessment and migration from on-premises to Azure SQL database.
In the assessment phase you can identify migration blockers and deprecated features which may prevent you from migrating your on-premises database to Azure SQL Database. You can also use this tool for getting SKU recommendations for Azure SQL Database based on the current workloads on your on-premises server.
The actual migration can be performed using this tool and you have the option to include both schema and data or either of the options as per your choice. The tool as such is very easy to use and can be installed on a local server if connectivity is possible to the source server. The tool can be downloaded from this link. You can refer to this tip which has the step by step instructions for performing the actual migration from your on-premises server to Azure SQL Database.
This tool is very handy especially if you are looking for quick options to migrate to Azure SQL Database. Make sure you have completed the assessment stage correctly and there are no migration blockers. Below is a screenshot of the Data Migration Assistant.
Azure Database Migration Services
This is an Azure service that can be used for the purpose of migrating on-premises databases to Azure. Migration support is available for other database sources as well like MySQL, PostgreSQL, MongoDB. Both online and offline migrations are possible using the Azure database migration service. At the time of writing this tip, only the offline option is possible while performing the migration from SQL Server to Azure SQL Database. You may refer to this Microsoft link which has the details of the various offline or online options that are available based on your requirements. The previous tool that is mentioned, Data Migration Assistant will still be used for the purpose of assessment of the source databases. If you search for Azure Database Migration Services on the portal, you will get this screen.
Once you click on Create, you will see this screen.
Describing the detailed steps of the Database Migration Service is beyond the scope of this tip. However, it is recommended that you explore the options that are available within this tool which will help to make the cloud migration process easier.
Other Migration Options
So far you saw the use of Microsoft tools specifically meant for the purpose of migration to the cloud. However, we can still make use of other alternatives in order to perform the cloud migration. As you know, we cannot make use of traditional methods like performing an on-premises database backup and then copying the same backup and restoring it as an Azure SQL Database. In the next section, we will explore some other options we can consider for the purpose of cloud migration.
Transactional Replication
Traditional methods like replication can be used for the purpose of migration from on-premises SQL Server databases to Azure SQL Database as transactional replication is supported and we can use the destination Azure SQL Database as a subscriber. The detailed step by step instructions are available in this tip.
As you know, there are few limitations with transactional replication as it only allows replication of tables with primary keys. If in your case, some tables don't have primary keys, then alternative options must be explored to migrate these tables along with this option. The benefit of this option is migration downtime can be minimized as replication can be configured in advance which will save considerable time during the migration when the subscriber Azure SQL Database can be promoted as the main database and the on-premises database can be taken offline.
As of now, Azure SQL Database can only be a push subscriber in transactional replication. If you refer to this tip, where you see the actual instructions, you can see snapshot replication works as well as it is one of the pre-requisite steps to be configured before setting up transactional replication. It is recommended to refer to this tip for the detailed step by step instructions of this method.
Import Export option using BACPAC
BACPAC is a zip file with a. bacpac extension that contains both the database schema and data. This is a straight forward option available to perform the export and import. You can use the Export data-tier application option on the on-premises database to export to BACPAC and using Azure portal, you can import the BACPAC as an Azure SQL Database or you can also use tools like SSMS. You can refer to this tip, under the section Using SSMS to export bacpac file to see the steps for performing an export of a database to a bacpac.
Once the bacpac file is available, you can perform a restore to Azure SQL Database by using the Import data-tier Application option. This option is extremely useful especially if you are dealing with the migration of small on-premises databases. When dealing with larger databases, it will be easier to make use of command line tools like sqlpackage. You can refer to this tip, under the Sqlpackage utility which has the detailed steps for performing the export operation. In this tip, you can also see how this option can be scheduled using SQL Server jobs.
Import Export option using BCP
Another option is to make use of the command line bcp utility. With this, we can export the contents of a table to a data file and then use the import option to import data into an Azure SQL Database. You can refer to this section – Using bcp option to export data from Azure SQL Database in this tip which describes the process to export a table from a SQL server database.
Once the table is exported as a data file, the import option can be used to import it into an Azure SQL Database. The import process can be done using the data file that was part of the export process. Also, the import will be into a table in an Azure SQL Database with correct schema and columns. If there are discrepancies with the table structure in Azure SQL Database, the bcp import process will fail. It is important to bear in mind that this option is not really a migration tool, but is something that can be used in specific scenarios where you may want to couple this option with something like transactional replication where you may need to import data from on-premises database tables without primary keys. The process is simple with easy commands, but you need to watch out for correct formatting for the table structures during the export and import process.
Using Azure Data Factory
You can make use of Azure's own ETL service for the purpose of copying data from on-premises SQL Server database to Azure SQL Database. Pipelines can be created where you can make use of the tools already available for copying data from the source tables to destination tables. For integration with the on-premises server for the purpose of connecting to the source database, you can install the self-hosted integration run time installer on the on-premises server. This can be done from within Azure Data factory where the install can be initiated.
Once you click on New, you will see this option, select Azure, Self-hosted.
You will be able to connect to your on-premises server from the Azure Data Factory pipeline only after successfully installing the self-hosted integration run time. In the copy tool, you can provide details of the source and sink data store. Sink refers to your destination, in this case- Azure SQL Database. Make sure the correct schema mappings are done between the source and destination table. Once the pipeline validation is done, publish it and trigger the pipeline which will complete the copy of the data from the table on the on-premises database to Azure SQL Database. To learn more about Azure data factory, you can refer to these tips.
Generate SQL Scripts
You can right click on the on-premises database in SSMS, and go to Tasks, and click the Generate Scripts option.
This option is useful for small databases. However, for large databases, this may not be convenient as the script generation process may take too much time and also the SQL file generated will be too huge. By using this option, you can script out just the schema, data or both schema and data as a SQL file. You can script out the entire database using this option. Make sure to select the correct options when you click on the Advanced option while scripting out.
By selecting the Microsoft Azure SQL Database for the Script for the database engine type option, a SQL file compatible for the Azure SQL Database version will be generated. This SQL file can be run on the Azure logical server and database gets created on Azure. So based on the option you selected earlier during script generation, the database may or not have data.
In this tip, you saw the various options that are possible for migrating an on-premises SQL Server database to Azure SQL Database.
Next Steps
- You can try out each of the options described in this tip in your test environment
- Note down the time required for the various options
- In order to learn more on Azure topics, you can refer to these Azure articles
About the author
Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2021-12-07
