SQL Server database mirroring is a disaster recovery and high availability technique that involves two SQL Server instances on the same or different machines. One SQL Server instance acts as a primary instance called the principal, while the other is a mirrored instance called the mirror. In special cases, there can be a third SQL Server instance that acts as a witness
Configuration Types
One of the common mirroring configuration is the environment with two SQL Servers (SQLServer-1 and SQLServer-2), two instances (SQLInstance-1 and SQLInstance-2), and one mirrored database named SQLDB-1
Illustration of one of the common SQL Server database mirroring configuration
The second common configuration is the environment with one SQL Server machine, two SQL Server instances, and one mirrored database named SQLDB-1. This solution has a major flaw because if SQLServer-1 goes down, both instances will be unavailable.
Illustration of mirroring configuration with one SQL Server machine, two SQL Server instances, and one mirrored database
Operating modes
SQL Server database mirroring can be set to provide high availability or disaster recovery. Depending on the needs, a DBA can choose among three available modes
• High safety – Data is written and committed on the principal and mirror databases synchronously. Only after committing on both databases, the database application can continue with activity
o Might produce delay and slower operation because transactions must be committed on both databases.
o If the principal database goes down, two options are available:
Do nothing – wait for the principal to become available again. During that time, the SQL Server instance is unavailable. Mirroring will continue where it has stopped.
Force the SQL Server instance on the mirror database – the mirror database becomes the principal. Possible data loss due to committed transactions on the original principal database which are not yet committed on the mirror currently acting as the principal
• High safety with automatic failover – Three servers are necessary. Data is written and must be committed synchronously both on the principal and mirror databases. Only after committing on both databases, the application can continue running.
o Might produce delay and slower operation because transactions must be committed on both databases.
o If the principal database goes down, only one option is available.
Let the automatic failover process complete, the mirrored database becomes the principal
• High performance – the asynchronous communication, data is written and committed on the principal server, and later sent and committed to the mirror server. Automatic failover isn’t possible and the witness server can’t be used
o The high performance mode is only available in the Enterprise edition of SQL Server
o If the principal database goes down, three options are available:
Do nothing – wait for the principal to become available again. The SQL Server is unavailable. Mirroring will continue where it has stopped
Force the SQL Server instance on the mirror database – the mirror database becomes the principal. Greater possibility for data loss, due to asynchronous communication between databases
Manual update – to reduce data loss, take the tail of the log backup if the failed server allows, remove mirroring and restore the tail of the log on the previously mirrored database
Using SQL Server database mirroring has multiple benefits: a built-in SQL Server feature, relatively easy to set up, can provide automatic failover in high safety mode, etc. Database mirroring can be combined with other disaster recovery options such as clustering, log shipping, and replication
Database mirroring will be removed from SQL Server in future versions in favor of AlwaysOn Availability Groups. Also, database mirroring is per database only solution, which means that logins and jobs from the principal SQL Server must be manually recreated on the mirror. There is also possibility for delay, which can only be reduced with better hardware.
More Info Refer:
https://technet.microsoft.com/en-us/library/ms189852.aspx
https://technet.microsoft.com/en-us/library/ms190941.aspx
Configuration Types
One of the common mirroring configuration is the environment with two SQL Servers (SQLServer-1 and SQLServer-2), two instances (SQLInstance-1 and SQLInstance-2), and one mirrored database named SQLDB-1
Illustration of one of the common SQL Server database mirroring configuration
The second common configuration is the environment with one SQL Server machine, two SQL Server instances, and one mirrored database named SQLDB-1. This solution has a major flaw because if SQLServer-1 goes down, both instances will be unavailable.
Illustration of mirroring configuration with one SQL Server machine, two SQL Server instances, and one mirrored database
Operating modes
SQL Server database mirroring can be set to provide high availability or disaster recovery. Depending on the needs, a DBA can choose among three available modes
• High safety – Data is written and committed on the principal and mirror databases synchronously. Only after committing on both databases, the database application can continue with activity
o Might produce delay and slower operation because transactions must be committed on both databases.
o If the principal database goes down, two options are available:
Do nothing – wait for the principal to become available again. During that time, the SQL Server instance is unavailable. Mirroring will continue where it has stopped.
Force the SQL Server instance on the mirror database – the mirror database becomes the principal. Possible data loss due to committed transactions on the original principal database which are not yet committed on the mirror currently acting as the principal
• High safety with automatic failover – Three servers are necessary. Data is written and must be committed synchronously both on the principal and mirror databases. Only after committing on both databases, the application can continue running.
o Might produce delay and slower operation because transactions must be committed on both databases.
o If the principal database goes down, only one option is available.
Let the automatic failover process complete, the mirrored database becomes the principal
• High performance – the asynchronous communication, data is written and committed on the principal server, and later sent and committed to the mirror server. Automatic failover isn’t possible and the witness server can’t be used
o The high performance mode is only available in the Enterprise edition of SQL Server
o If the principal database goes down, three options are available:
Do nothing – wait for the principal to become available again. The SQL Server is unavailable. Mirroring will continue where it has stopped
Force the SQL Server instance on the mirror database – the mirror database becomes the principal. Greater possibility for data loss, due to asynchronous communication between databases
Manual update – to reduce data loss, take the tail of the log backup if the failed server allows, remove mirroring and restore the tail of the log on the previously mirrored database
Advantages and disadvantages of using SQL Server database mirroring
Using SQL Server database mirroring has multiple benefits: a built-in SQL Server feature, relatively easy to set up, can provide automatic failover in high safety mode, etc. Database mirroring can be combined with other disaster recovery options such as clustering, log shipping, and replication
Database mirroring will be removed from SQL Server in future versions in favor of AlwaysOn Availability Groups. Also, database mirroring is per database only solution, which means that logins and jobs from the principal SQL Server must be manually recreated on the mirror. There is also possibility for delay, which can only be reduced with better hardware.
More Info Refer:
https://technet.microsoft.com/en-us/library/ms189852.aspx
https://technet.microsoft.com/en-us/library/ms190941.aspx
No comments:
Post a Comment