MIRRORING

DATABASE MIRRORING
Database mirroring is used for high availability and also used to disaster
recovery.
Database mirroring we can call it as combination of Log shipping & Replication.
For database mirroring configuration we need 3 servers.

1.Primary
2.Secondary
3.Witness
IN mirroring the basic process is take a full backup of Principal database and
restored it is secondary database with no recovery option
When ever an application server inserts the records in to the principal database
1st application server writes the records in Log buffer and it sents to the log
file
Operating modes in db mirroring
High Availability
High Safety
High Performance
High Availability
In High Availability mode we require 3 servers.
Application server writes the transaction in the principal server database log
file.
It sends the transaction to the mirror server log file then the mirror server sends
the acknowledgement to the principal server
Then the principal server commits the transaction to the application server.
So there will be some latency issues.

Automatic failover will be happen in High availability mode.
In high avaialbility mode there will be no data loss
High Safety
In High safety we need 2 server to configure mirroring pricipal server & mirror
server
In this mode there is no automatic failover is happen only forced failover is
applicable
1st the transaction sends to principal server then it writes in log file and
transaction goes to the mirror server log file then it sends the acknowledgement to
the principal server saying that
it receives the transaction log file.
High Performance
In high performance mode the application server sends the transaction to the
principal server log file.
It doesnot wait for the mirror server to acknowldgement the transactions.
It immmediately commits the transaction to the application server.
In high performance mode forced failover is applucable.
In high performance mode send queue and redo queue will be configured in db mirror
Send Queue:
While sending the log records from the principal to the mirror, if the log records
can�t be sent at the accuracy or rate at which they are generated, a queue builds
up at the principal.
This is known as the send queue. The send queue does not use extra storage or
memory.
It exists entirely in the transaction log of the principal. It refers to the part
of the log that has not yet been sent to the mirror.
Redo Queue:
While applying log records on the mirror, if the log records can�t be applied at
the rate at which they are received, a queue builds up at the mirror.
This is known as the redo queue. Like the send queue, the redo queue does not use
extra storage or memory.
It exists entirely in the transaction log of the mirror. It refers to the part of
the hardened log that remains to be applied to the mirror database to roll it
forward.
-----------------------------------------------------
Mirroring Requirements
1.) Data and Log files path locations should be same.

2.)Collation and master code page should be same on both principal and mirror.
3.)Database name should be same on principal and mirror.
4.)We cannot mirror more than 10 databases on 32-bit server,you can on 64-bit but
it is not recommended.
5.)we cannot attach/detach we need to use tradition backup and restore.
6.)Ports for mirroring should be open.
7.)Service accounts should be same on sql & SQL-Agent on instance.
8.)Cross-database transactions and distributed transactions
9.)SQL SERVER versions should be same on Principal & Mirror.
-------------------------------------------------------------
Advantages of database Mirroring
1.) Hardware or software upgrades can be simpified
a) Principal and Mirror are running on sql server 2008 versions we need to upgrade
it to 2008R2
1st check the mirror is running in which mode (Synchronous or Asynchronous mode).
If the mirroring is configured in syncronous mode then we need to change to
Asynchronous mode.
Pause the mirroring and upgarde the mirror server 1st and resume the mirroring and
change the Asynchronous mode to synchronous mode
2.) Increases the data protection(distater recovery).If you use database mirroring
in async mode,then the purpose is typically disaster recovery methodology.
3.)Increases the database avaialble HA sync mode.
4.)Cost of the database mirroring is less compare to clustering.

5.)It is robust and efficient than log shipping and replication.
6.)It supports the Full-Text.
7.)Failover is fast compare to cluster.
8.)Mirror server can be used to host databases for other applications.
DisAdvantages of database mirroring

-----------------------------------
1.)It does not support filestream.
2.)Potential loss of data in Async mode(High Performance mode)
3.)Mirror server is not available for read-only purposes.
4.)It works at the database level,not at the server level.
5.)Multiple database failover.
----------------------------------------------
Mirroring Enhancements in SQL SERVER 2008

-------------------------------------------
1.)Automatic Page repairs
823 --Cyclic Redundancy fail Error

824 --Logical errors
829 ---Restore pending
This will be rectified automatically in mirroring
2.)Compression of mirroring data stream.
3.) Log send buffers--efficient use
4.)Write-ahead events --enhanced in 2008
5.)Page read-ahead during the undo phase
Licensing options
-------------------
1.)30-days allowable option for mirror
2.)Principal server can be standard,mirror can be enterprise --Not vice cersa
3.)If we use database snapshots on mirror server,then we need to purchase license

for mirror server]
4.)We can mix and match 32 & 64 Bit version

MIRRORING

Uploaded by

Copyright:

Available Formats

MIRRORING

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

MIRRORING

Uploaded by

Copyright:

Available Formats

DATABASE MIRRORING

Database mirroring we can call it as combination of Log shipping & Replication.

For database mirroring configuration we need 3 servers.

Operating modes in db mirroring

In High Availability mode we require 3 servers.

So there will be some latency issues.

1.) Data and Log files path locations should be same.

Advantages of database Mirroring

1.) Hardware or software upgrades can be simpified

3.)Increases the database avaialble HA sync mode.

4.)Cost of the database mirroring is less compare to clustering.

6.)It supports the Full-Text.

7.)Failover is fast compare to cluster.

8.)Mirror server can be used to host databases for other applications.

DisAdvantages of database mirroring

1.)It does not support filestream.

2.)Potential loss of data in Async mode(High Performance mode)

3.)Mirror server is not available for read-only purposes.

4.)It works at the database level,not at the server level.

5.)Multiple database failover.

Mirroring Enhancements in SQL SERVER 2008

1.)Automatic Page repairs

823 --Cyclic Redundancy fail Error

This will be rectified automatically in mirroring

2.)Compression of mirroring data stream.

3.) Log send buffers--efficient use

4.)Write-ahead events --enhanced in 2008

5.)Page read-ahead during the undo phase

1.)30-days allowable option for mirror

2.)Principal server can be standard,mirror can be enterprise --Not vice cersa

3.)If we use database snapshots on mirror server,then we need to purchase license

4.)We can mix and match 32 & 64 Bit version

You might also like