Azure SQL database is a relational database as a service. This provides all the features of RDBMS; including transaction consistency, data integrity, and multi user concurrent data access system. Azure Sql database is based on the same principle as Microsoft sql server. It also provides high availability at lower cost.
Data Integrity in Azure SQL
DBAs used some traditional techniques in SQL Server to monitor data integrity. For Example DBCC, CHECKDB etc. some various methods to recover from database corruption. Microsoft developed new techniques that should be automatically handled classes of corruption and also without having losses of data.
Here we can consider some techniques ,their works and impact it has on customers concerned with what steps they should take to safeguard their data in Azure SQL Database.
How we manage data integrity for Azure SQL Database
Data integrity in Azure SQL Database involves a combination of techniques and evolving methods are:
- Extensive data integrity error alert monitoring: whenever data integrity gives errors or unhandled exceptions, azure sql database produces alerts, every alerts forwarded to Azure’s Engineering team for manually handles and solving those issues.
- Backup and restore integrity checks: Continuously, Azure SQL Database engineering team tests the restore of automated database backups of databases, In this time database also receive integrity checks using DBCC CHECKDB.if the system caught some issue or error during integrity check, it will give alert to the engineering team.
- I/O system “lost write” detection: Azure SQL Database also have additional functionality to detect physical corruptions issues. According to this functionality, it will track pages and their LSN (Log Sequence Number).Any block of data page from disk will be compared to expected LSN, if this is not matched then the page will be considered corrput, sending an immediate alert to the engineering team.
- Automatic Page Repair: Azure SQL Database has one more advantage is automatic page repair for business continuity purposes and services. Which is the same technology used for SQL Server database mirroring and availability groups. In that case page cannot be read or load due to data integrity issue, a Fresh copy of page will be replacing the unreadable page without data loss or customer downtime.
- Data integrity at rest and in transit: Databases created in the service are by default set to verify pages with the CHECKSUM setting, Checksumming is a well known method for performing integrity checks. Checksums can be computed for disk data and can be stored persistently. Data integrity can be verified by comparing the stored and the newly computed values on every data read. Checksums are generated using a hash function. Transport Layer Security (TLS) is also used for all communication in addition to the base transport level checksums provided by TCP/IP.
How we handle data integrity incidents : Data integrity issues pose such a high risk and are not always easily detectable. The goals when handling integrity incidents are to minimize unavailability and minimize the amount of data loss.
- Addressing System Data Integrity Issues: database availability will be corrected without notifying customers. Examples include those incidents that automatic page repair can address, or corruption to internal database metadata or telemetry that does not affect customer data or query results.
- Addressing Customer Data Integrity Issues: All wrong-results and customer data corruption issues are communicated to impacted customers as soon after confirmed detection as possible. Azure SQL DB engineers will:
- Gives options to explain the scope of corruption, outlining recovery options for allowing the customer to choose the option that works best for their application and scenario.
- Identifying whether data corruption has caused the application to change other data in an unexpected way.
Repair is achieved using various methods, and with steps taken in conjunction with customers. Options can include but are not limited to:
- Rebuilding the index – for example for a non-clustered index where the clustered index or heap is not also corrupted.
- Running DBCC CHECKDB with REPAIR_REBUILD where the repair has no possibility of data loss.
- Running DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS where repairs can cause some data loss.
Running integrity checks in Azure SQL Database
- Initiate Integrity Checks—Easily run a CHECKDB integrity check for any database, on-demand
- Identify Corruption—View integrity check results to identify database corruption
- Verify Last Integrity Check—Track date of last successful integrity check
- Automatic Notifications—Receive timely reminders to run integrity checks
For managing data integrity in Azure SQL database the Azure Engineering team will takes the responsibility. It is not necessary for customers to run integrity checks in Azure SQL Database.With the existing monitoring and protection provided by the service, customers can still choose to execute user-initiated integrity checks in Azure SQL Database. For example, customers may optionally run DBCC CHECKDB.