Durability in SQL Server refers to the property that ensures that once a transaction has been committed, its changes will persist even in the face of unexpected events such as power failures or system crashes.

SQL Server uses a variety of techniques to ensure durability, including:

Write-ahead logging: Before making any changes to the data, SQL Server writes a record of the change (in the form of a log record) to a transaction log. This log record includes information about the change, such as the type of operation (insert, update, delete) and the data being modified. By writing the log record first, SQL Server ensures that the change can be undone if necessary (e.g., if the transaction is later rolled back).

Checkpoints: Periodically, SQL Server will create a checkpoint, which is a point in time at which all dirty pages (i.e., pages that have been modified in memory but not yet written to disk) are written to disk. This ensures that the data on disk is in a consistent state, and that any changes made by committed transactions can be recovered in the event of a failure.

Full Database Backup: Regularly taking full database backups also help to ensure the durability of the data, These backups can be used in case of disaster recovery or to restore data to a specific point in time.

Mirroring and Replication: These are the High Availability feature of SQL server, It keeps multiple copies of the same data in different locations, it helps to ensure data durability and availability.

Always On Availability Groups: This is another High Availability feature of SQL server, It keeps multiple copies of the same data in different locations, it helps to ensure data durability and availability, also it provide automatic failover in case of any failure.

Example

Here is an example of durability in SQL Server:

Suppose you have a table called "Employees" with columns "EmployeeID" (primary key), "Name", and "Salary". You want to update the salary of an employee with ID 1001 from $50,000 to $55,000, and you want to make sure that the change is durable.

Here's how you can do it using a transaction:

BEGIN TRANSACTION

UPDATE Employees
SET Salary = 55000
WHERE EmployeeID = 1001

COMMIT TRANSACTION

When you execute this code, SQL Server will start a transaction, update the salary of the employee with ID 1001, and then commit the transaction. Once the transaction is committed, the changes made to the "Employees" table will be durable, meaning that they will be persisted even in the event of a system failure or power outage.

If SQL Server were to crash before the transaction is committed, the changes made by the transaction would be rolled back, and the salary of the employee with ID 1001 would remain $50,000. But once the transaction is committed, the changes made by the transaction will be durable and will persist even in the event of a system failure or power outage.

SQL Server durability is critical for maintaining data integrity and protecting against data loss. These features help to ensure that changes made by transactions are permanent and can be recovered in case of any failure.