Home » Database Mirrroring

Database Mirrroring

sql server DBA 999

SQL Server Database mirroring modes and patner timeout

The Two modes of Database Mirroring in SQL Server for HA/DR Solutions are

  1. Synchronous mirroring( High Availability / HA )
  2. Asynchronous mirroring( Disaster Recovery / DR )

Synchronous mirroring :

In all SQL Server databases, data changes are recorded in the transaction log before any changes to actual data pages are made. The transaction log records are placed first in a database’s log buffer in memory, and then flushed to disk (or ‘hardened’) as quickly as possible. In database mirroring, as the principal server writes the principal database’s log buffer to disk, it simultaneously sends that block of log records to the mirror instance.The mirror receives the log records in mirror database’s log buffer and then hardens them to disk … Read the rest

sql server DBA 999

Setting up and Monitoring of Database Mirroring as DR Strategy

Quick Step by Step Procedure involved:

• Perform a full backup of the database on the Principal server.
• Perform a Transaction Log backup on the Principal server.
• Copy the backup file to the Mirror.
• Do a restore of the full backup into a new step, but before doing the restore, go to Options, then ensure you check the No Recovery option
• Perform another restore of the Transaction Log, also with the No Recovery option.
• Go to the Database Properties on individual databases and choose the Mirroring tab.
• Click “Configure Security”
• Click Next on the wizard
• Choose not to have a Witness server since Asynchronous Database Mirroring is used.
• Click Next
• … Read the rest

sql server DBA 999

Database Mirroring in SQL Server 2008

Database Mirroring in SQL Server 2008:
In database mirroring we can opt to either synchronous or asynchronous DB mirroring.
In asynchronous DB Mirroring the transactions commit without waiting for the mirror server to write the log to disk, which maximizes performance.
In synchronous operation, a transaction is committed on both partners, but at the cost of increased transaction latency.
There are two mirroring operating modes as below;
1. In High-safety mode supports synchronous operation. Under high-safety mode, when a session starts, the mirror server synchronizes the mirror database together with the principal database as quickly as possible. As soon as the databases are synchronized, a transaction is committed on both partners, at the cost of increased transaction latency.
2. In … Read the rest