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 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.
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.
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).
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.
- Julia is Techarex Network’s Content Marketing Associate for Intuit products. She has worked as a B2B and Marketing Strategist for the last seven years, helping businesses with software implementation for Customer Service, CRM, and Email Marketing. Julia thrives in helping companies find the right software solution for their projects. Aside from his obvious passion for Marketing, Julia admits to being a foodie and travel enthusiast.
- Microsoft Sharepoint2018.03.30How to Select the Best between SharePoint Server and SharePoint Online
- SharePoint Hosting2018.03.22Avoid SharePoint Compliance Risk by implementing a Robust Information Governance Plan
- Dedicated Hosting2018.03.20Guide to Selecting the Best between Office 365 Hosting and Hosted Exchange
- QuickBooks2018.03.07Boost Up Your Accounting Performance with Managed QuickBooks Support Services