Azure SQL hybrid data movement
As Cloud computing is getting more and more popular today, many companies are choosing to deploy a hybrid environment using a mix of on-premises data center and public cloud. It gives the businesses greater flexibility and more data deployment options. For example, a company can host business critical or sensitive data in on-premises data centers and deploy less-critical data or test and development environment in the public cloud. A hybrid cloud environment will also help large companies to migrate on-premises data center to cloud in multiple stages without interfering with the business.
Moving data around efficiently in a hybrid cloud environment is critical and challenging. In this blog, we are going to introduce options in different data movement scenarios built on top of on-premises SQL Server, Azure SQL VMs and Azure SQL Databases:
We are going to mention the following technologies and tools in this blog:
The goal of this blog is to help you to choose the right technologies and tools to implement different scenarios. Implementation details and step by step instructions will not be covered in this blog, however we will provide links to related resources.
When you are migrating existing data from on-premises SQL Server databases to Azure, there are a few key facts you should measure and consider:
If you can afford some downtime, or if you are performing a test migration, you can use bacpac to migrate your databases to Azure SQL Databases. See the blog of “Migrating from SQL Server to Azure SQL Database using Bacpac Files” for detailed instructions.
When you migrate databases, especially large databases using bacpac, plan for a long enough application downtime. Depending on the database size, the downtime can be hours.
When you cannot afford to remove your databases from production during the migration, you can consider using transaction replication as the migration solution (SQL Azure Database as push subscriber). See the “Migration from SQL Server to Azure SQL Database Using Transactional Replication” and “Replication to SQL Database” for details, including the limitations of transaction replication.
See “SQL Server database migration to SQL Database in the cloud” for more about migration to Azure SQL Databases.
If you decide to migrate and host your data in Azure SQL VM, you will have several more options, including creating a Always On replica, backup/restore, etc. See “Migrate a SQL Server database to SQL Server in an Azure VM” for more details.
You can also use DMA (Data Migration Assistant) to migrate on-premises SQL Server databases to Azure SQL VM. DMA can migrate not only data, but also other server objects like logins, users and roles. DMA can also be used to detect compatibility issues before the migration. See the document of “Data Migration Assistant (DMA)” for more details.
Disruptive events can happen in any data platform and cause data loss or your databases and application to become unavailable. Capability of fast recovery from data loss or database downtime is important for business continuity, especially for business-critical databases and applications.
Azure SQL Databases automatically have more than one copy created to ensure the high availability (99.99% availability SLA). To prevent or mitigate business discontinuity in event of a data center outage, you can either create Active Geo-replication or restore database from geo-redundant backup.
Active geo-replication will provide minimum downtime and data loss during the data center outage. It can also be used to scale out read-only workloads (will discuss in next scenario); however, this will introduce extra cost to have active geo-replication replicas. Consider this option for business-critical databases. See “Overview: SQL Database Active Geo-Replication” for more details about active geo-replication.
You can also recover your database from geo-redundant backup only when necessary.
