Microsoft’s SQL Server has been one of the most popular relational databases for quite some time. The cloud version of SQL Server is called Azure SQL Database.
Azure provides the following options to move data from existing on premises SQL Server to Azure SQL database :
- SQL Server Stretch Database: This option moves some of the data from SQL Server 2016 or above to Azure. It identifies the cold rows which are accessed infrequently by users and moves them to the cloud. These rows can still be accessed and queried by users, but the performance would be a bit slower. This helps in lowering costs as storing data in Azure is cheaper than provisioning new storage on premises. It also results in quicker backups for the on-premises database.
- Azure SQL Database: This option is suitable for organizations that want to go ahead with a cloud only strategy and move the whole database to Azure. There are some limitations to consider while migrating your data from SQL Server to Azure SQL Database, since they are not 100% compatible with each other. E.g. a table cannot have more than 1023 columns on Azure SQL Database.
- SQL Server Managed Instance: This is one of the configurations supported on Azure Database as a Service (DBaaS). Microsoft provides this option where the database maintenance is taken care of by Microsoft and the database is almost 100% compatible with on premises SQL Server.
- SQL Server on a Virtual Machine: If a customer wants to have complete control over the database management and maintenance, then this is a suitable option. This ensures a 100% compatibility with the existing on premises instance.
Microsoft provides a tool called Data Migration Assistant which helps customers identify which options are suitable based on the customers’ existing on premises SQL Server setup. This tool provides useful information e.g. the tables suitable for configuring cloud storage for SQL Server Stretch Database, how much restructuring and re-engineering would be required to move the SQL Server instance to Azure SQL Database.