Database Mirroring is a high availability solution for database availability. It maintains two copies of database to ensure database availability to minimize or not downtime. These two copies maintained on two different servers, one principal server – which serves the user requests and database on principal server is in available mode to users and the second mirror – which acts as a hot standby for principal server and database is in restoring mode (not available to users). We can bring secondary server (mirror server) online if something goes wrong with Principal server or it can be automated using Witness server.
When databases are in synchronized state the secondary server acts as a hot standby that supports immediate failover without data loss from committed transactions
When databases are not in synchronized state the secondary server acts as a warm standby (with data loss).
Requirements
Database mirroring is available in SQL 2005, 2008, 2008R2 and 2012
Database mirroring is available in enterprise and standard edition (In STD there are certain limitations)
Database mirroring works only on FULL recovery model, Simple and bulk-logged recovery models doesnot support.
Mirroring cannot be configured on system databases Master, Model, MSDB and Tempdb.
For automatic failover you need to configure witness server must have SQL Server 2005+ and can be any edition (standard, enterprise or express edition also)
We need to initialize the mirroring database (need to restore principal server full backup on mirror server on norecovery mode)
Mirroring Modes
Synchronous (High Protection Mode) – With this mode transactions cannot commit on the principal until all the transactions logs are copied to mirror server, mirror server sends acknowledgement to principal server in this mode. This mode guarantees that both principal and mirror servers are in sync and there wont be any data loss if principal server fails. It acts similar to High availability mode except the automatic failover (principal to mirror) process is manual.
Asynchronous mode (High Performance) – With this mode the overall architecture as a warm standby and doesn’t support automatic failover. The data transfer between principal and mirror server are asynchronous, Transaction log records are committed only on the principal server before the log records shipped to mirror server, in this mode if there is anything happen to primary database there will be a data loss.
High Availability (High Safety with witness) – It provides synchronous transfer of data between principal and mirror server databases also it supports automatic failure detection and supports automatic failover. This mode requires all three servers principal, mirror, witness servers, There will be no data loss because it uses synchronous data transfer.
In SQL Server 2008 few additional functionality been added to Database Mirroring
Automatic Page Repair -- If a corrupt page found in the principal (or mirror) server then database mirroring system will attempt to obtain page from the mirror (or principal) database then repair the corruption.
Logstream compression -- By compressing the log records before sending them to the mirror database which increase the performance of log records while travel across the network.
When databases are in synchronized state the secondary server acts as a hot standby that supports immediate failover without data loss from committed transactions
When databases are not in synchronized state the secondary server acts as a warm standby (with data loss).
Requirements
Database mirroring is available in SQL 2005, 2008, 2008R2 and 2012
Database mirroring is available in enterprise and standard edition (In STD there are certain limitations)
Database mirroring works only on FULL recovery model, Simple and bulk-logged recovery models doesnot support.
Mirroring cannot be configured on system databases Master, Model, MSDB and Tempdb.
For automatic failover you need to configure witness server must have SQL Server 2005+ and can be any edition (standard, enterprise or express edition also)
We need to initialize the mirroring database (need to restore principal server full backup on mirror server on norecovery mode)
Mirroring Modes
Synchronous (High Protection Mode) – With this mode transactions cannot commit on the principal until all the transactions logs are copied to mirror server, mirror server sends acknowledgement to principal server in this mode. This mode guarantees that both principal and mirror servers are in sync and there wont be any data loss if principal server fails. It acts similar to High availability mode except the automatic failover (principal to mirror) process is manual.
Asynchronous mode (High Performance) – With this mode the overall architecture as a warm standby and doesn’t support automatic failover. The data transfer between principal and mirror server are asynchronous, Transaction log records are committed only on the principal server before the log records shipped to mirror server, in this mode if there is anything happen to primary database there will be a data loss.
High Availability (High Safety with witness) – It provides synchronous transfer of data between principal and mirror server databases also it supports automatic failure detection and supports automatic failover. This mode requires all three servers principal, mirror, witness servers, There will be no data loss because it uses synchronous data transfer.
In SQL Server 2008 few additional functionality been added to Database Mirroring
Automatic Page Repair -- If a corrupt page found in the principal (or mirror) server then database mirroring system will attempt to obtain page from the mirror (or principal) database then repair the corruption.
Logstream compression -- By compressing the log records before sending them to the mirror database which increase the performance of log records while travel across the network.
No comments: