G
gyanisinha
Azure Database for MySQL - Single Server is on the retirement path, scheduled for retirement by September 16, 2024. As a result, we strongly recommend that you migrate your single servers to Azure Database for MySQL – Flexible Server at your earliest opportunity. This will help ensure that you have ample time to run through the migration lifecycle, leverage the benefits that come with Flexible Server, and maintain the continuity of your business. For more information about these changes, see the articles What's happening to Azure Database for MySQL single server? and What's new in Azure Database for MySQL - Flexible Server.
Single server architecture can no longer accommodate all the new features, functions, and security enhancements that we are bringing to Azure Database for MySQL – Flexible Server, so with your migration you will also be able to take advantage of the latest benefits in terms of security, resiliency, performance, operational excellence, cost optimization, and scalability.
Embarking on the journey of migrating your Azure Database for MySQL single servers to the Flexible Server deployment mode can be an exciting venture, but ultimate success lies in the details. Before you set out on this path, it's essential to carefully consider various factors that can impact the move and explore different migration approaches to ensure that you’re taking the best approach for your business.
I’ll go over all these details for you in this blog post, with each step leading you closer to your final goal!
Migration considerations
When you are contemplating such a project, you need to consider factors in several categories, as illustrated in the following graphic:
Cost & sizing
COST. With Flexible Server, you can take advantage of stop/start features, a burstable service tier, and reserved instances for savings, and pay for storage only when your database has stopped running. For more details, see Flexible Server Pricing - Azure Database for MySQL.
Best practice: Estimate the flexible server cost with the help of pricing calculator and review with the stakeholders.
SIZING: Azure Database for MySQL - Flexible Server offers three different service tiers: Burstable, General Purpose, and Business Critical. For choosing the right tier, consider vCores, Memory per vCore, Storage size, Database backup retention period, current usage, and business requirements. The compute tier, compute size, and storage size can be changed after you have created the server. For more information read: Azure Database for MySQL - Flexible Server service tiers.
Best practice: Regardless of the SKU of the Azure Database for MySQL – Single Server you are migrating from, choose a Flexible Server SKU with at least similar configurations as in Single Server to complete the migration quickly.
Version compatibility
Migrating from the same version of MySQL Single to Flexible server is ideal and simpler. Migration with an upgrade to higher version is also feasible. For example, refer to: Migrating an Azure Database for MySQL single server (v5.6) to a flexible server (v5.7).
Best practice: Application compatibility issues can arise when migrating to a different version of MySQL. These issues are primarily due to changes in the database system's behaviour, features, or syntax between versions, deprecated features, changes in default setting, extension compatibility, characterset, etc. Comprehensive testing is crucial to identify and address compatibility issues before migrating to a higher MySQL version. For more information, see Version support policy - Azure Database for MySQL - Single Server and Flexible Server and Azure Database for MySQL - flexible server - major version upgrade.
Migration downtime
Regardless of the migration approach employed for workloads, there exists a potential risk of downtime. This is often a result of the necessity to quiesce or temporarily pause an application or dataset to guarantee the continuity of its copy, validation, resetting the server parameters and other critical processes.
Best practice: It's important to connect with business and application teams to inform, plan, and analyze the impact of downtime during the cutover.
Networking and security
Azure Database for MySQL - Flexible Server supports following three ways to configure connectivity to your servers. Once you choose a networking option, you cannot modify it later, except for enabling or disabling private endpoints or public access. Therefore, you should carefully consider your connectivity requirements before creating a flexible server instance.
For more information, see Networking overview - Azure Database for MySQL - Flexible Server.
Additionally, when using the single server to flexible server migration tool, the Azure Database for MySQL – Single Server and Flexible Server must be able to connect via network. If the single server has public access, you can use it as is, but if the single server has private access, please refer to the network configuration below.
Performance
Performance considerations for the migration activities: There are several factors that can affect the performance of migration from Azure Database for MySQL - Single Server to Flexible Server, such as:
Operational performance considerations for the target Flexible server: Some of the operational performance considerations are physical proximity, accelerated networking, connection efficiency, partitioning, segregating reads and writes, write scaling and sharding and memory. For more details refer to: Performance best practices - Azure Database for MySQL
Resiliency
Azure Database for MySQL – Flexible Server supports two high availability deployment models: Zone-redundant HA and Same-zone HA. Because the high availability configuration deploys identical compute and storage to the primary, its costs must also be estimated. For more information, refer to: Zone-redundant HA with Azure Database for MySQL - Flexible Server
Best practices:
Support
Make sure you have a plan in place to ensure that you receive the necessary support and guidance as you migrate. Contact Microsoft support team.
Migration options and approaches
Although there are different options for migrating from Azure Database for MySQL – Single Server to Flexible Server, broadly migration approached can be categorized as either offline or online, as illustrated in the following graphic:
Offline migration
With an offline migration, the source server is taken offline, and a dump and restore of the databases are performed on the target server. Offline migrations are always the preferred choice if your application can afford some downtime, as they are simple and easy to execute.
Refer to the decision matrix for selecting the right tool for offline migration:
Online migration
With online migration, the application can continue to access the database during the migration process, except for a short cutover window when the final switch is made from the source to the target server.
Online migration is the best choice if your application can only afford minimal downtime. Migrations of most OLTP systems, such as payment processing and e-commerce, fall into this category. However, online migration is more complex and requires more planning and testing than offline migration. You need to ensure that the source and target servers are compatible, replication lag is minimal, and the cutover window is short and well-timed.
Refer to the decision matrix for selecting the right tool for online migration:
Recommendation: For ease of use and cost effectiveness it is recommended to start with Azure DMS (offline/online), which is inhouse fully managed service and has the capability to migrate large databases.
Migration phases
There are several phases included in the migration process, as depicted in the following graphic.
Guidance for each of these migration phases is included in the following sections:
Discovery and assessment
For discovery of existing single server details, you may use Kusto Query below on Azure Resource Graph Explorer. You would need to have access to the subscriptions where the single servers are and read access on the resources.
resources
| where type == "microsoft.dbformysql/servers"
| project name, type, subscriptionId, sku = sku["name"], tier = sku["tier"], capacity = sku["capacity"], family = sku["family"], location, version = properties["version"], publicNetworkAccess = properties["publicNetworkAccess"], privateEndpointConnections = properties["privateEndpointConnections"], backupRetentionDays = properties["storageProfile"]["backupRetentionDays"], geoRedundantBackup = properties["storageProfile"]["geoRedundantBackup"], storageAutogrow = properties["storageProfile"]["storageAutogrow"], storageMB = properties["storageProfile"]["storageMB"], fullyQualifiedDomainName = properties["fullyQualifiedDomainName"], minimalTlsVersion = properties["minimalTlsVersion"], replicationRole = properties["replicationRole"], replicaCapacity = properties["replicaCapacity"], infrastructureEncryption = properties["infrastructureEncryption"], userVisibleState = properties["userVisibleState"], byokEnforcement = properties["byokEnforcement"], sslEnforcement = properties["sslEnforcement"], tags
Identify details of single servers to be migrated. Perform a detailed assessment of the identified single servers to be migrated for: application dependencies, SKU, database sizes, resource usage, version, server parameters, network configurations, users and access management, read replicas, etc. For example:
In addition, be sure to confirm downtime expectations with your stakeholders and estimate cost optimizations. For more information, see Flexible Server Pricing - Azure Database for MySQL.
Plan and design
In the plan and design phase, be sure to consider the following points.
Migrate
Monitor and govern
Optimize and modernize
Known issues and limitations
It’s also important to consider the known issues and limitations of the migration tools you choose to use. Refer to the links below for more information:
Conclusion
Migrating from a single server to a flexible server in Azure Database for MySQL is a strategic leap towards enhanced database performance and scalability. By meticulously considering various factors, employing the right tools, adhering to best practices, and addressing limitations, you can seamlessly transition your database to a more efficient environment. Success lies in thorough preparation, comprehensive testing, and ongoing performance monitoring. Make the move!
If you have any feedback or questions about the information provided above, please leave a comment below or email us at AskAzureDBforMySQL@service.microsoft.com. Thank you!
Continue reading...
Single server architecture can no longer accommodate all the new features, functions, and security enhancements that we are bringing to Azure Database for MySQL – Flexible Server, so with your migration you will also be able to take advantage of the latest benefits in terms of security, resiliency, performance, operational excellence, cost optimization, and scalability.
Embarking on the journey of migrating your Azure Database for MySQL single servers to the Flexible Server deployment mode can be an exciting venture, but ultimate success lies in the details. Before you set out on this path, it's essential to carefully consider various factors that can impact the move and explore different migration approaches to ensure that you’re taking the best approach for your business.
I’ll go over all these details for you in this blog post, with each step leading you closer to your final goal!
Migration considerations
When you are contemplating such a project, you need to consider factors in several categories, as illustrated in the following graphic:
Cost & sizing
COST. With Flexible Server, you can take advantage of stop/start features, a burstable service tier, and reserved instances for savings, and pay for storage only when your database has stopped running. For more details, see Flexible Server Pricing - Azure Database for MySQL.
Best practice: Estimate the flexible server cost with the help of pricing calculator and review with the stakeholders.
SIZING: Azure Database for MySQL - Flexible Server offers three different service tiers: Burstable, General Purpose, and Business Critical. For choosing the right tier, consider vCores, Memory per vCore, Storage size, Database backup retention period, current usage, and business requirements. The compute tier, compute size, and storage size can be changed after you have created the server. For more information read: Azure Database for MySQL - Flexible Server service tiers.
Best practice: Regardless of the SKU of the Azure Database for MySQL – Single Server you are migrating from, choose a Flexible Server SKU with at least similar configurations as in Single Server to complete the migration quickly.
Version compatibility
Migrating from the same version of MySQL Single to Flexible server is ideal and simpler. Migration with an upgrade to higher version is also feasible. For example, refer to: Migrating an Azure Database for MySQL single server (v5.6) to a flexible server (v5.7).
Best practice: Application compatibility issues can arise when migrating to a different version of MySQL. These issues are primarily due to changes in the database system's behaviour, features, or syntax between versions, deprecated features, changes in default setting, extension compatibility, characterset, etc. Comprehensive testing is crucial to identify and address compatibility issues before migrating to a higher MySQL version. For more information, see Version support policy - Azure Database for MySQL - Single Server and Flexible Server and Azure Database for MySQL - flexible server - major version upgrade.
Migration downtime
Regardless of the migration approach employed for workloads, there exists a potential risk of downtime. This is often a result of the necessity to quiesce or temporarily pause an application or dataset to guarantee the continuity of its copy, validation, resetting the server parameters and other critical processes.
Best practice: It's important to connect with business and application teams to inform, plan, and analyze the impact of downtime during the cutover.
Networking and security
Azure Database for MySQL - Flexible Server supports following three ways to configure connectivity to your servers. Once you choose a networking option, you cannot modify it later, except for enabling or disabling private endpoints or public access. Therefore, you should carefully consider your connectivity requirements before creating a flexible server instance.
- Public access: Your flexible server is accessed through a public endpoint, with configurable permissions for allowed IP addresses as firewall rules.
- Private Endpoint: You can use private endpoints to allow hosts on a virtual network VNet to securely access data over a Private Link.
- Private access (VNet Integration): Virtual Network (VNet) integration for an Azure Database for MySQL - Flexible Server enables you to lock down access to the server to only your virtual network infrastructure. Your virtual network (VNet) can include all your application and database resources in a single virtual network or may stretch across different VNets in the same region or a different region.
For more information, see Networking overview - Azure Database for MySQL - Flexible Server.
Additionally, when using the single server to flexible server migration tool, the Azure Database for MySQL – Single Server and Flexible Server must be able to connect via network. If the single server has public access, you can use it as is, but if the single server has private access, please refer to the network configuration below.
- Deploy Azure Database for MySQL – Flexible Server in the same VNET as Azure Database for MySQL – Single Server's Private Endpoint and allow connections with NSG.
- Azure Database for MySQL – Flexible Server is deployed in a VNET different from the Private Endpoint of Azure Database for MySQL – Single Server, and the VNET in which the Private Endpoint is created and the VNET in which Azure Database for MySQL – Flexible Server is deployed are connected using VNET peering.
Performance
Performance considerations for the migration activities: There are several factors that can affect the performance of migration from Azure Database for MySQL - Single Server to Flexible Server, such as:
- The size of the source database and the amount of data to be transferred.
- The network bandwidth and latency between the source and target servers.
- The configuration and performance of the source and target servers
- The type and complexity of the database objects and features, such as tables, indexes, views, triggers, stored procedures, functions, events, encryption, authentication, replication, and backup.
- The migration method and tool used, such as online or offline, In-place automigration, Azure DMS, Azure MySQL Import, mysqlshell or manual backup and restore.
- Data import configurations: It is recommended to use optimal settings for data import operations, such as disabling binary logging, increasing the innodb_buffer_pool_size, and using parallel threads.
Operational performance considerations for the target Flexible server: Some of the operational performance considerations are physical proximity, accelerated networking, connection efficiency, partitioning, segregating reads and writes, write scaling and sharding and memory. For more details refer to: Performance best practices - Azure Database for MySQL
Resiliency
Azure Database for MySQL – Flexible Server supports two high availability deployment models: Zone-redundant HA and Same-zone HA. Because the high availability configuration deploys identical compute and storage to the primary, its costs must also be estimated. For more information, refer to: Zone-redundant HA with Azure Database for MySQL - Flexible Server
Best practices:
- Consider the trade-offs between availability, performance, and cost when choosing HA architecture for your database. Zone-redundant HA provides the highest level of availability and isolation across multiple availability zones, but it may incur higher network latency. Same-zone HA provides high availability within a single availability zone with lower network latency, but it does not protect against zone failures.
- Implement retry logic in your application to handle the transient errors or timeouts during failover process and monitor the failover status using Azure Monitor.
Support
Make sure you have a plan in place to ensure that you receive the necessary support and guidance as you migrate. Contact Microsoft support team.
Migration options and approaches
Although there are different options for migrating from Azure Database for MySQL – Single Server to Flexible Server, broadly migration approached can be categorized as either offline or online, as illustrated in the following graphic:
Offline migration
With an offline migration, the source server is taken offline, and a dump and restore of the databases are performed on the target server. Offline migrations are always the preferred choice if your application can afford some downtime, as they are simple and easy to execute.
Refer to the decision matrix for selecting the right tool for offline migration:
| Type | Migration approach | Recommendations |
| Offline | In-place | Only for Single Server database workloads with Basic or GP SKU, data storage used < 10 GiB and no complex features (CMK, AAD, Read Replica, Private Link) enabled. For more information refer to: In-place automigration from Azure Database for MySQL – Single Server to Flexible Server |
| Offline | MySQL Import | Suitable for all sizes of workloads, extremely performant for > 500 GB workloads. For more information refer to: Migrate Azure Database for MySQL - Single Server to Flexible Server using Azure MySQL Import CLI |
| Offline | Azure DMS | Suitable for < 1TB workloads; cross-region, cross-storage type and cross-version migrations. There are no costs incurred while running the migration using DMS. For more information refer to: Migrate Azure Database for MySQL - Single Server to Flexible Server offline using DMS |
Online migration
With online migration, the application can continue to access the database during the migration process, except for a short cutover window when the final switch is made from the source to the target server.
Online migration is the best choice if your application can only afford minimal downtime. Migrations of most OLTP systems, such as payment processing and e-commerce, fall into this category. However, online migration is more complex and requires more planning and testing than offline migration. You need to ensure that the source and target servers are compatible, replication lag is minimal, and the cutover window is short and well-timed.
Refer to the decision matrix for selecting the right tool for online migration:
| Type | Migration approach | Recommendations |
| Online | Azure DMS | Fully managed service that helps you migrate your databases to Azure with minimal downtime. There are no costs incurred while running the migration using DMS. For more information refer to: Migrate Azure Database for MySQL - Single Server to Flexible Server online using DMS |
| Online | MyDumper / MyLoader | Open-source tool, not actively developed. For more information refer to: Migrate Azure Database for MySQL – Single Server to Azure Database for MySQL – Flexible Server with open-source tools |
| Online | Mysqlshell | Oracle managed tool, actively developed. For more details refer to: Migrating from Single to Flexible Server with minimal downtime using MySQL Shell |
| Online | MySQL Import | In Preview – Coming soon! |
Recommendation: For ease of use and cost effectiveness it is recommended to start with Azure DMS (offline/online), which is inhouse fully managed service and has the capability to migrate large databases.
Migration phases
There are several phases included in the migration process, as depicted in the following graphic.
Guidance for each of these migration phases is included in the following sections:
Discovery and assessment
For discovery of existing single server details, you may use Kusto Query below on Azure Resource Graph Explorer. You would need to have access to the subscriptions where the single servers are and read access on the resources.
resources
| where type == "microsoft.dbformysql/servers"
| project name, type, subscriptionId, sku = sku["name"], tier = sku["tier"], capacity = sku["capacity"], family = sku["family"], location, version = properties["version"], publicNetworkAccess = properties["publicNetworkAccess"], privateEndpointConnections = properties["privateEndpointConnections"], backupRetentionDays = properties["storageProfile"]["backupRetentionDays"], geoRedundantBackup = properties["storageProfile"]["geoRedundantBackup"], storageAutogrow = properties["storageProfile"]["storageAutogrow"], storageMB = properties["storageProfile"]["storageMB"], fullyQualifiedDomainName = properties["fullyQualifiedDomainName"], minimalTlsVersion = properties["minimalTlsVersion"], replicationRole = properties["replicationRole"], replicaCapacity = properties["replicaCapacity"], infrastructureEncryption = properties["infrastructureEncryption"], userVisibleState = properties["userVisibleState"], byokEnforcement = properties["byokEnforcement"], sslEnforcement = properties["sslEnforcement"], tags
Identify details of single servers to be migrated. Perform a detailed assessment of the identified single servers to be migrated for: application dependencies, SKU, database sizes, resource usage, version, server parameters, network configurations, users and access management, read replicas, etc. For example:
In addition, be sure to confirm downtime expectations with your stakeholders and estimate cost optimizations. For more information, see Flexible Server Pricing - Azure Database for MySQL.
Plan and design
In the plan and design phase, be sure to consider the following points.
- Ensure to evaluate the key considerations and the assessment in the discovery step impacting the migration plan.
- Define and establish target state architecture. Finalize the migration method/approach.
- Validate and finalize the network connectivity strategy between single to flexible server
- Review migration readiness
Migrate
- Perform smoke test/pilot migrations before actual migrating for production:
- If you are migrating to a higher MySQL version, test for application compatibility.
- All stakeholders who need to be involved should be available and ready. The production migration requires close monitoring.
- For an online migration, the replication must be completed before you perform the cutover, to prevent data loss.
- The key steps for migration are:
- Prepare the source and target servers. This includes creating the flexible server with the desired configuration, setting the server parameters, configuring the networking and firewall rules, and creating the databases and users on the target server.
- Start the migration and monitor the progress and status.
- Perform the cutover when the replication lag is minimal, and the application traffic is low.
- Refer to the migration documentation for the selected method and tool.
- Post migration activities:
- Perform validation and data integrity checks.
- Redirect all dependent applications to access the new primary database and make the source server read-only. Then, open the applications for production usage.
- After the application starts running on the target flexible server, monitor the database performance closely to see if further performance tuning is required.
Monitor and govern
- Azure Database for MySQL uses Azure Monitor to provide several options for monitoring your cache instances. Use these tools to monitor the health of your Azure Database for MySQL instances and to help you manage your caching applications. Use insights to understand performance and utilization.
- For more details refer to: Configure audit logs by using Azure Database for MySQL - Flexible Server
Optimize and modernize
- Identify opportunities to further optimize cost, performance, security, scalability and operational excellence. Conduct well-architected (WAF) review to ensure that the workloads are optimized as per the best practices and standards.
Known issues and limitations
It’s also important to consider the known issues and limitations of the migration tools you choose to use. Refer to the links below for more information:
- Limitations for online migration using DMS via the Azure portal
- Limitations for offline migration using DMS via the Azure portal
- Known issues, limitations, and troubleshooting
- Troubleshoot common errors - Azure Database for MySQL | Microsoft Learn
- Compare the differences in server parameters for single and flexible server. For example: sql_mode, lower case table names, temp table uses myisam in single (uses innodb in flexible)
- Review character set compatibility: Some arabic character and chinese characters does not get translated in flexible because server and application collation does not match
- Creation time is more in Flexible server compared to Single because of the architecture
Conclusion
Migrating from a single server to a flexible server in Azure Database for MySQL is a strategic leap towards enhanced database performance and scalability. By meticulously considering various factors, employing the right tools, adhering to best practices, and addressing limitations, you can seamlessly transition your database to a more efficient environment. Success lies in thorough preparation, comprehensive testing, and ongoing performance monitoring. Make the move!
If you have any feedback or questions about the information provided above, please leave a comment below or email us at AskAzureDBforMySQL@service.microsoft.com. Thank you!
Continue reading...