In this article we dwell on the concept of database mirroring in Ms SQL Server and understand the different available modes.
When it comes to databases that are accessed by thousands of people, it is very critical to build in redundancy and facilitate continuous availability. In SQL Server, database Mirroring is used to achieve the same by using two database instances simultaneously. The first instance, also known as the principal has its data mapped into a backup instanced called the mirror. In some case a third instance too can be introduced and is typically known as witness.
Basics of Database Mirroring
It should be noted that whichever database is to be mirrored should always be on full recovery mode as system databases cannot be recovered. For a mirroring session to begin, the database owner or the administrator must ensure the creation of the mirror database, set up key endpoints and logins, and, if need be, also create and set up certificates. Database mirroring can be done in two modes.
- High safety mode– this mode supports synchronous operation. Under this mode, at the beginning of a mirroring session, the mirror database is completely synchronized with the primary database by the mirror server, as quickly as possible.
- High performance mode – This specific mode is designed to reduce latency. Herein the principal server after sending a log record to mirror server, send a confirmation acknowledgement to the client. This is done without waiting for the mirror server to confirm. While this may lead to some potential data loss scenarios, it allows for quicker transaction processing.
The Database Mirroring feature is available in the 2005 edition of SQL server and its later iterations. There are however plans to discontinue it in future editions, by going in the favor of ‘Always on availability groups’.
Key Advantages of Database mirroring
One of the first advantages of database mirroring relates to database availability. In case of mirrored implementation, the chance of database going offline is remote. In case of a crash, the automatic failover process induces the standby database online. In case of an alternate operating mode, the administrator can choose to force the service to the backup or mirror copy. The next advantage relates to data protection by inducing complete redundancy. When errors are noticed or a reading query is experiencing a block, the system automatically requests a fresh copy from the mirrored partner. Last but not the least, mirroring allows the availability of the production database when you run upgrades. The instances of mirrored solutions are sequentially taken over for a rolling upgrade which allows line of business applications to function during the upgrade period.
In a large business losing customer data can be a nightmare
If you are using the SQL Server database to maintain customer data in a large firm, the idea of a SQL crash with a possible data loss scenario would look ominous. To avoid any such hassles in future, always keep a sql server recovery tool like DataNumen SQL Recovery installed on your Windows Server. In the event of a crash, just run this tool to get back the contents stored in the crashed SQL file. This tool can help you extract contents from a compromised SQL file without being limited by the storage media type and can even work with virtual drives.
Alan Chen is President & Chairman of DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and sql recovery software products. For more information visit https://www.datanumen.com/