Storage
Mobility
Protection
Analysis and control
TOPICShare
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 in Action: 5 Use Cases
Azure SQL Database can serve a number of different use cases, including:
- Business continuity
- Dev/test environments
- Backup and restore
- Analytics
- Scaling out read-only workloads.
In this section we’ll take a look at each of these cases individually.
1. Business Continuity
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.
2. Dev/Test Environment
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.
3. Backup and Restore
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.
4. Advanced Analytics
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.
5. Scaling Out Read-Only Workloads
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.
The 6 Methods for Data Migration
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.
1. Backup and Restore
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.
2. Export to Flat File
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:
- Export the database onto a flat file
- Create database and table in target location
- Create a format file to describe the schema
- Copy the flat file to the destination
- Finally, import the flat file onto the target database using BCP.
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 publisher
- The subscriber
- The distributor.
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.
4. Azure Data Factory
Azure Data Factory (ADF) is used when the database needs to be migrated continuously in hybrid use case scenarios.
The ADF architecture mainly revolves around what are known as “pipelines”. A pipeline is a logical grouping of activities, and each grouping determines what will be performed on datasets. With the data processing done by these pipelines, orchestration and management of the complicated data and processing dependencies is done by ADF.
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.
5. Always-On Availability Groups (AGs)
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.
6. SQL Server Migration Assistant (SSMA)
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.
Other Considerations
Some other things to keep in mind while maintaining a hybrid footprint:
- Consider choosing the highest performance SQL tier possible during migration and then scale down when the migration has completed.
- Try to use a low latency network for migration and consider using a VPN or Microsoft Azure ExpressRoute for a secure connection.
- If the size of a database is too large, consider using Microsoft’s Export/Import service to transfer the data.
- Drop indexed views during migration and then recreate them once the migration has been completed.
- If you have several database instances, try to use Elastic Pools to better manage resources and ensure performance elasticity.
- Data migration with NetApp’s Cloud Volumes ONTAP (formerly ONTAP Cloud): Cloud Volumes ONTAP brings data management for NFS, CIFS, and iSCSI file shares into the cloud with Azure. With powerful high availability capabilities, increased processing power to handle more workloads, and cost-saving storage efficiencies such as data deduplication, compression, thin provisioning and more, Cloud Volumes ONTAP adds an extra level of functionality when migrating to Azure.
- With FlexClone® technology within Cloud Volumes ONTAP, you can create a golden (clone) image, and deploy all your working environments off of clones without expanding your storage footprint. Along with the storage resource efficiency, each of these clones can be created instantaneously, allowing you to rapidly build out hundreds of environments in minutes
Final Note
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.
