Determine Availability Group Synchronization State, Minimize Data Loss When Quorum is Forced
First published on MSDN on Nov 11, 2014 When Windows Cluster quorum is lost either due to a short term network issue, or a disaster causes long term down time for the server that hosted your primary replica, and forcing quorum is required in order to quickly bring your availability group resource online, a number of circumstances should be considered to eliminate or reduce data loss.
Best practices for safely performing schema changes in Azure Database for MySQL
Azure Database for MySQL - Flexible Server is built on the open-source MySQL database engine, and the service supports MySQL 8.0 and newer versions. This means that users can take advantage of the flexibility and advanced capabilities of MySQL’s latest features while benefitting from a fully managed database service. While newer versions and features can provide a lot of value, the recent issues identified with MySQL versions 8.0+ makes it important to be aware of potential risks that can occur during certain operations, particularly if you are making online schema changes. Issues with data loss and duplicate keys with Online DDL Online Data Definition Language (DDL) operations are a powerful feature in MySQL, enabling schema changes like ALTER TABLE or OPTIMIZE TABLE with minimal impact on table availability. These operations are designed to reduce downtime by allowing concurrent reads and writes during schema modifications, making them an essential tool for managing active databases efficiently. However, a recent post on the Percona blog, Who Ate My MySQL Table Rows? highlights critical risks associated with MySQL 8.0.x versions after 8.0.27 and all versions beyond 8.4.y. Specifically, the open-source INPLACE algorithm, commonly used for online schema changes, can lead to data loss and duplicate key errors under certain conditions. These issues arise from constraints in the INPLACE algorithm, particularly during ALTER TABLE and OPTIMIZE TABLE operations, exposing vulnerabilities that compromise data integrity and system reliability. These risks are called out in the following bug reports: Bug #115511: Data loss during online ALTER operations with concurrent DML Bug #115608: Duplicate key errors caused by online ALTER operations Documented issues related to the INPLACE algorithm (used for online DDL) can cause: Data Loss: Rows may be accidentally deleted or become inaccessible. Duplicate Keys: Indexes can end up with duplicate entries, leading to data consistency issues and potential replication errors. Problems arise when INPLACE operations, such as ALTER TABLE or OPTIMIZE TABLE, run concurrently with: DML operations (INSERT, UPDATE, DELETE): Modifications to table data during the rebuild. A purge activity: Background cleanup operations for old row versions in InnoDB. These scenarios can lead to anomalies resulting from race conditions and incomplete synchronization between concurrent activities. Impact on Azure Database for MySQL - Flexible Server Customers For Azure Database for MySQL Flexible Server customers using MySQL 8.0+ and all versions after 8.4.y, this issue is particularly critical as it affects: Data Integrity: During schema changes such as ALTER TABLE or OPTIMIZE TABLE run using the INPLACE algorithm, data rows may be lost or duplicated if these operations run concurrently with a DML activity (e.g., INSERT, UPDATE, or DELETE) or background purge tasks. This can compromise the accuracy and reliability of the database, potentially leading to incorrect query results or the loss of critical business data. Replication Instability: Duplicate keys or missing rows can interrupt replication processes, which rely on a consistent data stream across the primary and replica servers. These issues can arise when there are concurrent insertions into the table during schema changes, leading to data inconsistencies between the primary and replicas. Such inconsistencies may result in replication lag, errors, or even a complete breakdown of high-availability setups, requiring manual intervention to restore synchronization. Operational Downtime: Resolving these issues often involves manually syncing data or restoring backups. These recovery efforts can be time-consuming and disruptive, leading to extended downtime for applications and potential business impact. Recommendations for safe schema changes on Azure Database for MySQL flexible servers To minimize the risks of data loss and duplicate keys while making schema changes, follow these best practices: Set old_alter_table=ON to Default to COPY Algorithm Enable the server parameter old_alter_table system variable so that ALTER TABLE operations without a specified ALGORITHM default to using the COPY algorithm instead of INPLACE. This reduces the risk for users who do not explicitly specify the ALGORITHM in their commands. Learn more on how configure server parameters in Azure Database for MySQL. Avoid using ALGORITHM=INPLACE Do not explicitly use ALGORITHM=INPLACE for ALTER TABLE commands, as it increases the risk of data loss or duplicate keys. Back up your data before schema changes Always perform a full on-demand backup of your server before executing schema changes. This precaution ensures data recoverability in case of unexpected issues. Learn more on how to take full on-demand backups for your server. Avoid Concurrent DML during schema changes Schedule schema changes like ALTER TABLE and OPTIMIZE TABLE during application maintenance windows when no concurrent writes activities occur. This minimizes race conditions and synchronization conflicts. Use External Tools for Safer Online Schema Changes Consider using external tools like pt-online-schema-change to modify table definitions without blocking concurrent changes. These tools enable you to make schema changes with minimal impact on availability and performance. Learn more about pt-online-schema-change. Disclaimer: The pt-online-schema-change tool is not managed or supported by Microsoft; use it at your discretion. Mitigation plans To address these risks, we’re actively working to integrate the necessary fixes to ensure a more robust and reliable experience for our customers. New Servers Fully Secured by End of February 2025 All new Azure Database for MySQL Flexible Server instances created after 1 st March 2025, will include the latest fixes, ensuring that schema changes are safeguarded against data loss and duplicate key risks. Rollout for Existing Servers For existing servers, we will roll out patches during upcoming maintenance windows by end of Q1 of Calendar Year 2025 We recommend monitoring your Azure portal for scheduled maintenance windows and Release notes for announcements about critical updates and patches. Priority updates available upon request If you require an urgent update outside of the scheduled maintenance windows, you can contact Azure Support. Provide the necessary server details and an appropriate maintenance window, and our team will work with you to prioritize the patching process. Note that priority patching will be available by February 2025. We recommend monitoring Release notes for announcements about critical updates and patches. Conclusion Safely managing schema changes on MySQL servers requires understanding the risks associated with online DDL operations, such as potential data loss and duplicate keys. To help safeguard data integrity and maintain server stability, implement best practices, for example enabling the COPY algorithm, using offline operations if feasible, or scheduling changes during low activity periods. Fixes are expected by the end of February 2025, and new Azure Database for MySQL flexible servers will be fully protected against these bugs. We will apply updates to existing servers during maintenance windows in Q1 2025. Following the recommendations above will help ensure that you can confidently make schema changes while preserving the reliability and performance of your server.Always On - Synchronize SAP login, jobs and objects
SQL Server AlwaysOn is one of the High Availability solutions available for an SAP system. It consists of two or more computers each hosting a SQL Server with a copy of the SAP database. A listener points to the actual primary copy and is used from the SAP system as the only connection point. For details how to setup and configure an SAP system together with SQL Server AlwaysOn see this blog post and its referenced blog posts. During the setup the SAP System is configured from the current primary node and all non-database related objects such as SQL Server Agent Jobs, logins etc. are created only on the current primary database. In a case of a (automatic) failover to one of the secondary nodes of AlwaysOn these objects are then missing. Jürgen has introduced a script (sap_helprevlogin) in his initial blog post about the database load after setting up AlwaysOn. This script will transfer only the logins, but will fall short on transferring jobs, server level permissions and other assignments. One of the SAP developers working in our team has built a comprehensive PowerShell script (sap_synchronize_always_on.ps1) to perform all these tasks and to transfer all the SAP objects from the initial installation to all the other nodes of the AlwaysOn system. The script connects to the primary instance, reads the configuration of the secondary nodes and then synchronizes the objects and jobs with these nodes. The script must be executed by a domain administrator which has SQL Server sysadmin privileges on all AlwaysOn instances. The script uses up to three input variables: The server name of the SQL Server instance or the listener name of the High-Availability group. The default is (local) The name of the SAP database, which must be in an High-Availability group on the given server Single login (optional): Only one login gets copied along with SAP CCMS jobs owned by the login. By default all logins mapped to the database are copied. The script will execute: Create a procedure CheckAccess in the master database (see this blog about the details about it) Discover which logins are mapped to the database Discover which SAP CCMS jobs belong to those logins If the job does not use CheckAccess then change the job step to use CheckAccess and run the job step in master Open a connection to each secondary and: Create procedure CheckAccess in the master database Create the logins if they don't exist already using the same sid. Create the jobs if they don't exist already. If a job exists and if the job does not CheckAccess then change the job step to use CheckAccess and run in master If new SAP CCMS jobs are added because of remote monitoring from a different SAP system using DBACOCKPIT, the script can be re-executed. It will then copy only new objects which have not been copied before. You can find this useful script attached, which makes the synchronization of the SAP Systems in an AlwaysOn environment so much easier. Please ensure that you test the execution in your test environment first, before you run it in production. Neither SAP nor Microsoft takes any responsibility from using this script, you run it on your own risk. Update January 2017: New script version that copies the sid<adm> and SAPService<SID> logins from the SAP System as well. Best regards | Bless! Clas & GuðmundurAvailability Group Database Reports Not Synchronizing / Recovery Pending After Database Log File Inaccessible
First published on MSDN on Nov 29, 2017 You may find that one or more availability group databases is reported ‘Not Synchronizing / Recovery Pending’ on the primary replica or ‘Not Synchronizing’ on one of the secondary replicas.Ignite 2022: Announcing new features in Azure Database for MySQL – Flexible Server
Today, we're pleased to announce a set of new and exciting features in Azure Database for MySQL - Flexible Server that further improve the service's availability, performance, security, management, and developer experiences!Real‑World Cloud & Azure SQL Database Examples Using Kepner‑Tregoe
The Kepner‑Tregoe (KT) methodology is especially effective in modern cloud environments like Azure SQL Database, where incidents are often multi‑dimensional, time‑bound, and affected by asynchronous and self‑healing behaviors. Below are practical examples illustrating how KT can be applied in real Azure SQL scenarios. Example 1: Azure SQL Geo‑Replication Lag Observed on Read‑Only Replica Scenario An application team reports that changes committed on the primary Azure SQL Database are not visible on the geo‑replica used for reporting for up to 30–40 minutes. The primary database performance remains healthy. Applying KT – Problem Analysis What is happening? Read‑only geo‑replica is temporarily behind the primary. What is not happening? No primary outage, no data corruption, no failover. Where does it occur? Only on the geo‑secondary, during specific time windows. When does it occur? Repeatedly around the same time each hour. What is the extent? Lag spikes, then returns to zero. KT Insight By separating data visibility delay from primary health, teams avoid misdiagnosing the issue as a platform outage. Public DMVs (such as sys.dm_geo_replication_link_status and sys.dm_database_replica_states) confirm this as a transient redo lag scenario, not a service availability issue. Example 2: Error 3947 – Transaction Aborted Due to HA Replica Redo Lag Scenario Applications intermittently hit error 3947 (“The transaction was aborted because the secondary failed to catch up redo”), while primary latency remains stable. Applying KT – Situation Appraisal What needs immediate action? Ensure application retry logic is functioning. What can wait? Deep analysis—since workload resumes normally after retries. What should not be escalated prematurely? Platform failover or data integrity concerns. KT Insight KT helps distinguish protective platform behavior from defects. Error 3947 is a deliberate safeguard in synchronous HA models to maintain consistency—not an outage or bug. Example 3: Performance Degradation During Business‑Critical Reporting Scenario Customer reports slow reporting queries on a readable secondary during peak hours, coinciding with replication lag spikes. Applying KT – Decision Analysis Possible actions: Route reporting queries back to primary during spike window Scale up replica resources Move batch processing off peak hours KT Decision Framework Musts: No data inconsistency, minimal user impact Wants: Low cost, fast mitigation, minimal architecture change Decision Temporarily route latency‑sensitive reads to the primary while continuing investigation. This decision is defensible, documented, and reversible. Example 4: Preventing Recurrence with Potential Problem Analysis Scenario Recurring redo lag spikes happen daily at the same minute past the hour. Applying KT – Potential Problem Analysis What could go wrong? Hourly batch job may generate large log bursts How likely is it? High (pattern repeats daily) What is the impact? Temporary stale reads on replicas Preventive actions: Break batch jobs into smaller units Shift non‑critical workloads outside reporting hours Monitor redo queue size proactively KT Insight Rather than responding reactively each day, teams use KT to anticipate and reduce the likelihood and impact of recurrence. Example 5: Coordinated Incident Management Across Regions Scenario An Azure SQL issue spans EMEA, APAC, and US support teams, with intermittent symptoms and high stakeholder visibility. Applying KT – Situation Appraisal KT helps teams: Prioritize which signals are critical vs. noise Decide when to involve engineering vs. continue monitoring Communicate clearly with customers using facts, not assumptions This prevents “analysis paralysis” or conflicting interpretations across time zones. Why KT Works Well in Cloud and Azure SQL Environments Cloud platforms contain self‑healing, asynchronous behaviors that can be misinterpreted Multiple metrics may conflict without structured reasoning KT brings discipline, shared language, and defensible conclusions It complements tooling (DMVs, metrics, alerts)—it doesn’t replace them Closing Thought In cloud operations, how you think is as important as what you observe. Kepner‑Tregoe provides a timeless, structured way to reason about complex Azure SQL Database behaviors—helping teams respond faster, communicate better, and avoid unnecessary escalations.
