Prior Understanding
If you migrate a database from SQL Server to Azure SQL, a number of things will not work. Examples might include extended-stored procedures, Windows authentication (Azure has Entra, formerly Azure Active Directory, but the way it works is different), table partitioning with multiple filegroups, use of trace flags, referenced object from another database and CLR support. With Microsoft’s Data migration assistant (DMA) you can first do a pre-migration assessment on compatibility, performance and reliability issues. This gives you advance insight into which issues you may encounter after the migration. If it is clear what will be migrated, there are several options.
Data migration assistant (DMA)
DMA is a tool that you install on-premises that allows you to migrate a SQL Server database (from SQL 2005 onward) to SQL Server on-prem (upgrade) or to an Azure SQL database. You can also do a pre-migration assessment with Microsoft DMA. This tool is easy to use. With a few actions, you’ll find out if you can actually migrate the database you have in mind.
DMA is free to download and if you install it on the database server there are no additional costs. The disadvantage is that during the migration the database cannot be changed. That means you have to deal with a long downtime.
SQL Server Migration assistant (SSMA)
SSMA is a tool that lets you migrate other DBMSs (Oracle, MySQL, SAP ASE, DB2 and Access) to SQL Server on-prem and in the cloud. Oracle you can also migrate to Azure Synapse. As with DMA, there is no additional cost if you install it on the database server. However, this tool does not work (yet?) when the source is SQL Server.
Azure Database Migration Services (Azure DMS)
Azure DMS is a service within Azure that lets you bring a database from on-prem to Azure. The source can be SQL Server or Oracle and the target Azure SQL database or Azure SQL Managed Instance. It is good to know that Microsoft is building more and more of DMA and SSMA into this service.
This solution works online, so a database in Azure can be updated until it is actually deployed, with minimal downtime. The advantage of this solution is that no installation is required. The disadvantage is that you pay according to usage, but the service only runs during migration. So the cost depends mainly on the size of the database and the updates that occur after the services are set up.
Transactional Replication
You can also migrate to Azure SQL database by setting up transactional replication. Azure SQL Managed Instance can also handle other forms of replication. This requires you to place a distributor and configure replication. Of course, this takes some work to set up. The advantage is that again, you can flip the switch at the last minute, so there is little downtime. If you can still put the distributor on the existing database server, you have little extra cost here as well.
SQL Server BACPAC
SQL Server, in addition to creating a DACPAC (Data-tier Application Component Package), also has the ability to create a BACPAC (Backup Package). This exports not only the structure, but also the data. You can upload this BACPAC file to an Azure Blob storage (standard only).
Then, on the overview tab of an Azure SQL Server, you choose “import database. With this method of migration, there is only a cost for the Blob storage. The method is fairly straightforward and easy to execute. The disadvantage is the long downtime, due to the database being offline during the migration. Of course, this depends on the size of the database.
Bulk copy program (bcp)
Bcp is a command-line tool that has been included with SQL Server for a long time. You can export the database to a csv file and you can import that back into Azure SQL database. Before you do that, you can still edit the csv file. By itself, Bcp is the fastest tool to import and export data.
Because you make two passes (and possibly one edit), there is still a lot of downtime in total. Because you have to issue one command per table for each ex and import, there must be a script for the entire database. Bcp is a free tool, so there is no additional cost.
SQL Server Integration Services (SSIS)
SSIS – when used for migration to Azure – is actually a shell around Bcp. SSIS uses the same techniques in the background as Bcp. The script you have to write with pure Bcp solves SSIS within the tool. SSIS is Microsoft’s extract-transform-load (ETL) tool and has a graphical interface.
It is easier to learn than building a script in Bcp. Since it is otherwise similar to Bcp, it also has the same advantages and disadvantages. SSIS is also a free tool. There are another large number of ETL tools you can use to perform a migration, such as Informatica PowerCenter, Apache Airflow, Oracle Data Integrator and Talend Open Studio. They all have their own advantages and disadvantages. I have kept them further out of the scope of this blog.
Azure Data Factory (ADF)
ADF is also an ETL tool, but I want to mention it separately here because it is an Azure service. Its advantage is that you don’t have to install the tool, only configure it. It is the Azure counterpart of SSIS and has many of the advantages and disadvantages of SSIS. It is a service, though, so you pay according to usage. Furthermore, you have to install an integration runtime on an on-premises server. Because of this, you probably need the help of a system administrator.
SQL Data Sync
With Azure SQL Data Sync, a bi-directional connection can be established. This allows the application that uses the on-premises database and the application that uses the Azure SQL database to run simultaneously. So you can test the new application with a small group first, so that you can gradually make the switch.
To set up synchronization, you need to create a sync group in the Azure SQL database. This also adds a hub database, which incurs additional costs. Furthermore, you must also install a SQL Azure Data Sync Agent on-premises, which again requires cooperation from system or network management. The method is not very well known yet, but generally one does experience a fairly long latency.
Every advantage has its disadvantage
As you can see, even when migrating to Azure SQL database, every advantage has its disadvantage. Or the other way around, it just depends on how you look at the world. But with the right tools and approach, we can meet any challenge.
Want to know more?
Wondering how we can help you with your database migration or cloud database configuration? Feel free to contact us, we are happy to help you.