Microsoft SQL server offers a high availability solution for servers or databases that reduce the effect of software or hardware failure and maintain high availability of databases. It also minimizes the downtime of database. The main motive of high availability solution is to recover SQL server database from any corruption. A SQL server database can be corrupt or damaged due to several reasons like virus attack, hardware problem, power outage and so on.
SQL server provides many options for maintaining high availability solution like database mirroring, log shipping, failover clustering, replication, scalable shared databases, and more. In this article will try to explain two of them: Database Mirroring and Log Shipping
Database Mirroring: It is a software solution to increase the database availability and support almost instantaneous failover. It is used for single standby database that is also known as mirrored database. In this, production database is known as principle database. To illustrate it, see the below image.
Server A------ Witness----Server B
Where server A is the principle server and server B is the mirrored server.
If server A gets corrupt due to any above mentioned reasons then database administrator can restore corrupt database from mirrored server that is server B.
Server A Witness---Server B
In the above figure, server B works like principle database because server A has been corrupted.
Note: To use database mirroring solution on database, your database must be in full recovery mode.
Log Shipping: Like mirroring, it also works on the database levels but it is used to maintain one or more standby databases. In this, production server is known as primary server and the log shopped (standby) servers are known as secondary servers.
Server A
!
Backup share
!
Server B Server C
Where server A is the primary server and server B & server C are the secondary servers
Note: It offers flexibility for supporting multiple standby or secondary databases.
How to choose high availability solution for your database? If you have a database and want a high availability solution then you can choose database mirroring or log shipping. A given database can be mirrored or log shipped, even can simultaneously mirrored or log shipped. To choose what high availability solution to use, consider the below points:
How many designation (secondary) servers do you need?
- If you have required only one designation server then mirroring is recommended.
- If you have required more than one designation servers then log shipping is recommended.
If you need to delay restoring on the designation server then use log shipping with or without database mirroring.
No comments:
Post a Comment