• Call Toll Free: 1-855-909-3300

Disaster Recovery Solutions: Always On & Mirroring In SQL

Are you involved in SQL server in any manner? Well, if yes, then you must be well versed with multiple techniques involved in it to achieve high availability of SQL server database. Microsoft offers some advanced features for the purpose mentioned below:

AlwaysOn

AlwaysOn is the recent and most advanced technique from Microsoft on SQL server. It is a combination of highly availability and disaster recovery. It maximizes the availability of the databases for an enterprise.

 An availability group creates a failover environment for a distinct set of user databases, known as availability databases. An availability group maintains a set of primary databases and one to eight sets of corresponding secondary databases.

 The secondary databases have to be maintained by updating their transactional logs on a regular basis. The primary and secondary sets of databases are hosted by the availability replicas respectively.

Subscribe to Reliable Application Hosting Services

Redundancy at the database level is provided by these availability replicas. Hence, data synchronization at the database level is easily handled by primary replicas which make the primary database available for read-write connections from clients.

 The primary replica circulates the transaction log records of each primary database to each of the secondary databases. Every secondary replica passes the same transaction log records to the next secondary database.

 Therefore, any of the primary or secondary replicas can be suspended or fail without affecting the other databases, thus making the server highly available.

Window Server

[Source: Microsoft]

Database Mirroring

The Database mirroring feature of SQL is a high-availability and disaster recovery solution for increasing the availability of SQL server database.

This feature supports two copies of a single database residing on different server instances of SQL Server Database Engine.

These server instances are on computers located in different areas. The mirroring on a database establishes a relationship between these server instances, known as a database mirroring session.

The principal server is the server instance that serves the database to clients. The other server instance plays the role of the standby server .Both the server instances acts like partners in a database mirroring session.

When the session is synchronized, database mirroring maintains a hot standby server that supports rapid failover without any data loss from committed transactions.

When the database mirroring session is not synchronized, the mirror server is typically available as a hot standby server (with the possible loss of data).

Subscribe to Reliable Application Hosting Services

 The principal and mirror servers perform complementary roles in the session. During any course of time, one partner performs the principal role, while the other partner performs the mirror role.

The partner that plays the principal role is known as the principal server, and its copy of the database is the current principal database. The partner that plays the mirror role is known as the mirror server, and its copy of the database is the current mirror database.

When database mirroring is implemented in the production environment, the principal database is known as the production database.

Every database operation that occurs in the principal database has to be reflected on the mirror database as soon as possible.

Redoing of database operations such as insert, update, delete involves sending a stream of active transaction log records to the mirror server, which applies log records to the mirror database, in sequence, in the shortest possible time span.

Window Server

2 thoughts on “Disaster Recovery Solutions: Always On & Mirroring In SQL”

Leave a Reply

Techarex NetWorks Products