If you're looking to migrate an SQL database to Azure, you're in luck. Azure provides multiple options for hosting SQL in Azure, namely Azure SQL Database (a PaaS) and SQL Server on Azure VMs (an IaaS). In either case, when you migrate SQL database to Azure, you're gaining a ton of advantages.
In this article, we’ll take a close look at each of the use cases that can benefit if you migrate your SQL database to Azure. After that, we’ll discuss methods for migrating data to SQL databases from on-premises environments.
Azure SQL Database can serve a number of different use cases, including:
In this section we’ll take a look at each of these cases individually.
One of the most important use cases for SQL on Azure is using it as a DR target to maintain business continuity.
Azure SQL databases can provide an SLA of up to 99.99% by maintaining several copies of the data. This provides business continuity, as it allows you to restore geo-redundant copies of the data or use active geo-redundant copies as failover points in case of outages at data centers or in regions.
Besides Azure SQL Databases, you can also use availability groups to fulfil business continuity demands. Not only can you use availability groups in Azure SQL VMs, but also use Azure SQL VM instances as a target for high availability (HA) and disaster recovery (DR).
You can find more details on using these HA/DR solutions and using Azure SQL Database for business continuity in the Azure documentation.
Another important use case for replicating or migrating data to SQL hosted on Azure is for dev/test environments.
Before deploying to the production environment, it is pertinent that the data is tested against dev/test environments; Azure SQL databases can act as a target for just such environments.
The live production environment can be replicated to the dev/test environment using a database copy.
Azure SQL databases are backed up automatically on a regular basis, and there are no storage costs for up to 200% of the maximum provisioned database storage. You can restore backups to any point in time going back to the retention period, which is determined by the Azure SQL service tier in use.
On-premises SQL server databases and transactional logs can also be backed up directly to Azure, using the “Backup to URL” feature, and stored in Azure storage. Azure SQL databases can also be stored on local storage by exporting them to BACPAC files.
Another important reason for hosting SQL in Azure is to make use of Azure’s advanced analytics platforms, such as Azure Storage Blob and Azure Data Lake Store.
A common scenario with advanced analytics is when users reference data from various data sources, use Azure Data Lake Store as the staging area, perform transformation activities using HIVE or Spark, and finally load the data onto Azure Data Warehouse for BI and reporting.
Apart from providing BC/DR capabilities, active geo-replication can also be used to offload read-only workloads such as reporting jobs to secondary copies. You can also extend on-premises SQL server instances using readable always-on replicas.
Data migration is the key to leveraging SQL databases. This section will highlight several methods that can be used to migrate on-premises databases to Azure.
Before you start migrating the SQL data, you need to ensure that the source SQL databases are compatible with the target Azure SQL database.
The Microsoft Data Migration Assistant (DMA) tool helps in verifying this information by providing an assessment workflow that lists migration blocking issues and unsupported features. It also provides recommendations to help remediate these issues.
After you run the DMA tool, make sure that you prepare any necessary fixes as transact-SQL scripts. Take a transactionally consistent copy of the SQL database and ensure that the database is quiesced to prevent further changes to the source database.
Apply the Transact-SQL fixes to the database copy created above.
After the fixes have been applied, export the database copy as a BACPAC file to a local hard drive. Import the BACPAC file using import tools such as SQLPackage.exe or, if the file is stored in Azure Blob Storage, directly from the Azure portal. This process is explained in detail here.
The SQL databases can also be backed up in compressed format using something like the SQL Server Management Studio (SSMS), copied to the Azure VM, and then restored to the target database.
Instead of backing up to a local disk, the database can also be backed up to a URL and restored on the Azure VM.
Another method of migrating bulk data is by using the Bulk Copy Program (BCP) to export and then import bulk data in the form of a flat file. The steps taken are as follows:
More details on exporting to flat files can be found here.
3. Transactional Replication
This type of migration is appropriate if you cannot afford to take the SQL server down for extended periods of time and want the migration to be completed with the shortest length of downtime possible.
There are requirements for transactional replication that you should make sure that your database meets before you continue with this solution. You should also check that the source database is compatible with the target Azure SQL database.
The three main components of this method are:
The target Azure SQL database acts as the subscriber to the on-premises SQL instance (the publisher). The distributor is responsible for communicating between the subscriber and the publisher, and synchronizes data from the publisher to the subscriber as new transactions are ongoing.
Once the synchronization is complete, the connection strings of the applications are pointed to the subscriber. The distributor syncs the delta changes to the subscriber and then cuts over to the subscriber as the production database.
At this point, transactional replication can be uninstalled.
Azure Data Factory (ADF) is used when the database needs to be migrated continuously in hybrid use case scenarios.
For example, let’s consider a scenario where you want to combine the two activities of migrating data to Azure Blob Storage and then migrating that same data to the Azure SQL database into a ADF pipeline.
Start by creating an ADF in the Azure portal. Next, create linked services between the Azure SQL database and the on-prem SQL server. This is achieved with the help of the ADF Integration Runtime, which is installed on the on-premises server.
After the linked services are created, create and define tables (which specify how to access the datasets) using JSON files. Finally, the ADF pipeline is created (using JSON files) and run.
You can find more details on the procedure here.
Availability groups provide high availability by using secondary replicas and failing over to these secondary replicas if something happens to the primary copy.
You can extend on-premises AGs by provisioning one or more Azure VMs with SQL Server and assigning these machines as the target replicas for the on-prem SQL AGs.
The process of adding an Azure replica is similar to adding any other secondary replica except that you choose the “Add Azure Replica” option on the wizard where you specify the replica information.
More details on this method can be found here.
This method is used for migrating non-MSSQL databases such as Microsoft Access, IBM DB2, MySQL, Oracle, and SAP ASE.
This tool automates the database migration process by converting the source objects into Azure SQL objects and then migrating them to the destination.
Steps and specific tool information can be found here.
Some other things to keep in mind while maintaining a hybrid footprint:
SQL databases are reliable resources for creating environments that help combine on-premises deployments with cloud-based services on Azure. The benefits of using SQL can clearly be seen, if only for it’s ability to lower costs, though there are a few other factors to keep in mind.
One of the main things to remember before starting the migration is to use the DMA and work out any incompatibilities between the source and target databases. Also, look out for egress traffic charges, which can swiftly increase your TCO.
It is important to choose the right SQL option in Azure based on factors like TCO, control over database, administration capabilities and the use case for which you want it.
Finally, it is a good idea to examine some case studies of companies that have used these SQL capabilities, how they are hosting SQL in Azure and how they achieved their migrations.
Head over to the Azure marketplace today to try a 30-day free trial of Cloud Volumes ONTAP on Azure.