Azure SQL MI Replication – New Possibilities for Hybrid Environments
In his latest post, App Dev Manager Wyn Lewis-Bevan discusses the new Azure SQL Database Managed Instance (SQL MI) which is a fully managed relational cloud database service that provides the broad SQL Server engine compatibility. He also shares resources on how you can get started on using Azure SQL Server MI.
As customers continue to have more applications deployed in Azure, it’s common that data from those on-premises applications has to be shared with the new cloud applications or vise-versa. A simple batched methodology may be suitable for table data that is rarely updated, but for data that changes frequently, this may introduce an unacceptably high latency and undermine any data concurrency, expected by the business users.
One of the limitations of SQL Azure database is that it can’t support a SQL agent, so replication, while possible from on-premises to the cloud, is not available in the opposite direction. One solution is the use of the relatively new Azure SQL Database Managed Instance, SQL MI, that allows replication from the cloud replicated back to an on-premises SQL database.
For customers who want live data movement between the SQL Server in the cloud and an on-premises SQL Server database, SQL Server Managed Instance is envisioned as the preferred, go to, platform for SQL Server in the cloud. Compared to an IaaS instance, SQL MI is nearly 100% compatible with SQL Server Enterprise Edition but since it is built as a PaaS solution, it comes with has reduced management overhead since SQL/OS patching and version updates are not the customer’s responsibility. Additional features like backup and high-availability, a 4-9s uptime SLA, just to mention a few come with SQL Server MI.
While most transactional replication features carry over from the on-premises version, there are limitations worth mentioning here. For example, merge, updating subscriber and Peer-to-Peer transactional replication types are not supported in SQL MI. File stream data is not supported in SQL MI, so direct replication between an on-premises database with FileTable storage and a SQL MI database is also not possible at this time.
While most of the transactional replication features will work in the same way as in on-premises SQL Server Enterprise Edition there are caveats. The publisher/distributor/subscriber databases can be hosted either on-premises or in SQL MI, but the publisher/distributor need to be located together, either on-premises or in the cloud on the same vNet.
Assuming an overall migration to the cloud, where the cloud is now the focal point of future development and deployment efforts, let’s assume the focus is to have data synchronization originate in the cloud. Since a requirement is that the publisher and distributor must reside in the same environment, they both have to be located in the cloud and in a single vNet. If the subscriber database is also in the cloud, then it also has to reside in the same vNet or a peered vNet.
The steps to build all the components are well documented.
- Creating an Azure SQL Server MI is described in this QuickStart: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-managed-instance-get-started.
- An initial migration to Azure SQL MI can be conducted using the Data Migration Service, DMS, (see https://docs.microsoft.com/en-us/azure/sql-database/sql-database-managed-instance-migrate for more details on DMS) or by other means as necessary, the transactional replication can be set up between the SQL Server MI instance and subscribers in Azure SQL DBs, another Azure SQL MI instance or an on-premises SQL database.
The following requirements exist for the publisher and distributor to reside on Azure SQL MI:
- At this time, all instances of SQL Server MI must be in the same vNet for replication to between cloud instances.
- Connectivity between the replication participants requires SQL Authentication (see the Manage database access section of https://docs.microsoft.com/en-us/azure/sql-database/sql-database-security-tutorial#creating-database-users for details on setting up a user using SQL authentication)
- The replication working directory requires an Azure Storage Account (see https://docs.microsoft.com/en-us/azure/storage/common/storage-quickstart-create-account?tabs=azure-portal#create-a-storage-account for details on how to create a suitable storage account.) Port 445 (TCP Outbound) needs to be open in security rules of the SQL MI subnet to access the Azure working directory.
- Port 1433 (TCP outbound) needs to be open as the Publisher/Distributor is on a SQL Managed Instance and the subscriber is on-premise.
- Once you have connected to the SQL MI database, the distributor and distribution database can be added, the publisher configured for replication etc. (See https://docs.microsoft.com/en-us/azure/sql-database/replication-with-sql-database-managed-instance for detailed steps on how to configure the replication.)
Conclusion and Summary
The addition of transactional replication that can originate in the Azure cloud is a significant addition to the arsenal of the database architect. Furthermore, being able to move the data from a publisher in the cloud to either other cloud databases (SQL Azure, SQL MI or Elastic Pools etc.) or to an on-premises subscriber is very powerful. This is because it allows the control of a single source of the truth to a single source of data located in the cloud that can be shared across multiple applications.
Organizations can now use transactional replication to have near real-time data concurrency between databases within the cloud or between the cloud and on premises. The recent addition of bidirectional transactional replication (at the time of writing, in public preview,) though not discussed in this blog, will add a new dimension that brings additional possibilities on how to enable hybrid data solutions requiring data input either in the cloud or on-premises for consumption by applications that in either location leading to a truly hybrid Azure architecture.
Light
Dark
0 comments