|Labeled (SQL) database icon with shortcut identification (Photo credit: Wikipedia)|
SQL Server provides a set of high availability methods that users can use to prevent SQL Server database from data loss & provide protection to SQL Server Database. There are lots of methods available in SQL Server. Here I am going to explain Database Mirroring high availability method:
What is Database mirroring: This feature is newly added in SQL Server 2005. It contains two copies of a single database that must reside on different server instances of SQL Server Database Engine. These server instances reside on computers in different- different locations. The server that you want to mirror is called the Principal server. The server that receives the data and acts as the backup is called the Mirror.
Why we use Database Mirroring:
Database mirroring improves the availability of the production database during upgrades & failovers. If media failure occurs, database mirroring provides the feature to database server administrator to recover data without having to take the database server offline.
Modes of Database Mirroring:
High-Safety mode or synchronous mode: In this mode, the principal server sends the transaction. It waits until the transaction is committed on the mirrored server. Then the transaction is committed on the principal server.
High-Performance mode also known as asynchronously: In Asynchronous mode, the principal server sends the transaction to the mirrored server but it does not wait for the transaction like synchronous mode on the mirrored server to commit.
Some marking points about database mirroring:
- The principal database should be in the FULL recovery model. Database mirroring does not support simple and bulk-logged recovery models.
- The mirror database & principal database both should have the same name.
- The mirror database must be initialized from a restore of the principal database with NORECOVERY, followed by restores in sequence of principal transaction log backups.
- Mirror Database cannot be accessed directly as the mirror database is in a recovering state, you can create database snapshots on the mirror to indirectly read the mirror database at a point in time.
This article explained the basics of the Database mirroring feature in SQL Server. It also illustrated different modes of database mirroring. If above methods won't work in case of database corruption & get fail to recover database then you should use SQL Database Repair software for database recovery. The software recovers database from SQL Server version 2000, 2005, 2008 & 2008 R2.