How Automatic Failover Works in DB Mirroring?

If the principal server is still running, it changes the state of the principal database to DISCONNECTED and disconnects all clients from the principal database.

The witness and mirror servers register that the principal server is unavailable.

If any log is waiting in the redo queue, the mirror server finishes rolling forward the mirror database.

The former mirror database moves online as the new principal database, and recovery cleans up all uncommitted transactions by rolling them back as quickly as possible.

When the former principal server rejoins the session, it recognizes that its failover partner now owns the principal role. The former principal server takes on the role of mirror, making its database the mirror database. The new mirror server synchronizes the new mirror database with the principal database as quickly as possible.

What is Automatic Page Repairing?

After certain types of errors corrupt a page, making it unreadable, a database mirroring partner (principal or mirror) attempts to automatically recover the page. The partner/replica that cannot read the page requests a fresh copy of the page from its partner. If this request succeeds, the unreadable page is replaced by the readable copy, and this usually resolves the error, this whole process is called Automatic Page Repair.

What are the page types which are not repairable using Automatic Page repair option?

File header page (page ID 0).

Page 9 (the database boot page).

Allocation pages: Global Allocation Map (GAM) pages, Shared Global Allocation Map (SGAM) pages, and Page Free Space (PFS) pages.

In a database mirroring if the failover occurs, how application will automatically connect to the failover partner? Is their any setting from the database server end to achieve this?

Connection string is:  

string cs = 

Data Source=myServerAddress;

Failover Partner=myMirrorServerAddress;

Initial Catalog=myDataBase;

Integrated Security=True;

Can we configure mirroring between Standard Edition & Enterprise Edition or Vice Versa?

Nope, its not possible, both principal and mirror should have the same edition.

Does mirroring support FILESTREAM file group?

NO. Database mirroring cannot be configured for a database that contains FILESTREAM filegroups.

DB Mirroring

What is the default of end points (port numbers) of principal, mirror and witness servers?

The default port numbers of principal, mirror and Witness servers are 5022, 5023 and 5024

What are the Database Mirroring states?

1) SYNCHRONIZING:­ The contents of the mirror database are lagging behind the contents of the principal database. The principal server is sending log records to the mirror server, which is applying the changes to the mirror database to roll it forward. At the start of a database mirroring session, the database is in the SYNCHRONIZING state. The principal server is serving the database, and the mirror is trying to catch up. 

2) SYNCHRONIZED:­ When the mirror server becomes sufficiently caught up to the principal server, the mirroring state changes to SYNCHRONIZED. The database remains in this state as long as the principal server continues to send changes to the mirror server and the mirror server continues to apply changes to the mirror database. If transaction safety is set to FULL, automatic failover and manual failover are both supported in the SYNCHRONIZED state, there is no data loss after a failover. If transaction safety is off, some data loss is always possible, even in the SYNCHRONIZED state.

3) SUSPENDED:­ The mirror copy of the database is not available. The principal database is running without sending any logs to the mirror server, a condition known as running exposed. This is the state after a failover. A session can also become SUSPENDED as a result of redo errors or if the administrator pauses the session SUSPENDED is a persistent state that survives partner shutdowns and startups. 

4) PENDING_FAILOVER:­ This state is found only on the principal server after a failover has begun, but the server has not transitioned into the mirror role. When the failover is initiated, the principal database goes into the PENDING_FAILOVER state, quickly terminates any user connections, and takes over the mirror role soon thereafter.
5) DISCONNECTED:­ The partner has lost communication with the other partner.

What are the Restrictions for Database Mirroring? 

a) Maximum 10 databases per instance can support on a 32­bit system. 

b) Database mirroring is not supported with either cross­database transactions or distributed transactions

What is End Point?

An endpoint is a network protocol which is used to communicate Principal, Mirror and Witness servers over the network. 

Creation of an end point:­ 

Create endpoint State=started/stopped/disabled as tcp (listener port=5022/5023) for database_mirroring (role=partner/witness)

How to set Automatic failover timeout?

The default timeout for communication between the principal, mirror, and witness servers is 10 seconds.

Adjusting the automatic failover time for SQL Server Database Mirroring

ALTER DATABASE dbName SET PARTNER TIMEOUT 20

How to do manual failover?

To perform a manual failover

ALTER DATABASE AdventureWorks SET PARTNER FAILOVER;

You can specify FAILOVER only on the principal server. This option is valid only when the SAFETY setting is FULL (the default).

FORCE_SERVICE_ALLOW_DATA_LOSS -- is available only on the mirror server and only when The principal server is down and WITNESS is set to OFF.

Pausing or Removing Database Mirroring?

To remove the database mirroring session

ALTER DATABASE AdventureWorks SET PARTNER OFF;

Removes a database mirroring session and removes mirroring from the database. You can specify OFF on either partner. 

To resume the database mirroring session

ALTER DATABASE AdventureWorks SET PARTNER RESUME;

You can specify RESUME only on the principal server

To pause the database mirroring session

ALTER DATABASE AdventureWorks SET PARTNER SUSPEND;

You can specify SUSPEND on either partner.

Enhancements in 2008

·         Automatic page repair(823,824,829 page errors).

·         compression of mirroring data stream.

·         Log send buffers - efficient use

·         Write-ahead event enhanced in 2008

·         Page read-ahead during the undo phase

Can we configure mirroring on different domain ?

Yes. both domain's should be trust each other.