SQL Server 2008 Database Mirroring

Check below videos for Database Mirroring

Remove the Witness from a Database Mirroring Session

At any time during a database mirroring session, the database owner can turn off the witness for a database mirroring session.

Turning off the witness changes the operating mode in accordance with the transaction-safety setting:

• If transaction safety is set to FULL (the default), the session uses high-safety, synchronous mode without automatic failover. For more information, see Synchronous Database Mirroring (High-Safety Mode).
  
  
• If transaction safety is set to OFF, the session operates asynchronously (in high-performance mode) without requiring quorum. Whenever transaction safety is turned off, we strongly recommend also turning the witness off. For more information, see Asynchronous Database Mirroring (High-Performance Mode).

The transaction safety setting of the database is recorded on each partner in the sys.database_mirroring catalog view in the mirroring_safety_level and mirroring_safety_level_desc columns. For more information, 

To turn off the witness

1. Connect to either partner.
   
   
2. Issue the following statement:
   
   ALTER DATABASE <database_name> SET WITNESS OFF
   where <database_name> is the name of the mirrored database.
   
   Ref: MSDN

Database Mirroring

Database Mirroring provides database redundancy by transferring data from the transaction log to another instance of SQL Server. There are several operating modes that can be used such as: high performance, high safety with automatic failover and high safety without automatic failover. Based on the operating mode, we can have complete or almost complete database redundancy. More information can be read from this MSDN library article.
Use:

• This is a good option when there is the need to have automatic failover for a database. It can be near real time failover of a database depending on the options used. Also a good choice if there is a need to provide database connectivity with minimal downtime.

Recovery:

• Recovery Time Objective (RTO) - Depending on the options used it could be almost immediately for a database, because the mirror copy will become the primary copy. Also, if you use the Failover Partner option in the connection string the application should be able to find the new server without any other configuration changes.
• Recovery Point Objective (RPO) - If there is no data loss (due to data deletion), there is minimal to no data loss during the failover. Because this is an exact copy of the primary database all transactions will be replicated to the mirror copy as they were done on the primary server.

Pros:

• It provides automatic failover. (if used with a witness)
• Snapshots of the database can be created against the mirrored copy for read only access and off-loading reporting to another server.
• It provides near real time failover of a database, depending on the options used.
• No additional cost, except for the need to have another available server for the mirror and possibly a third for the witness.
• If secondary server is used in a passive mode (only for failover), additional SQL Server licensing is not needed.

Cons:

• High safety without automatic failover setting may be network overhead.
• A third server is required for "automatic failover" (witness - SQL Server Express can be used)
• Other items need to be handled outside of mirroring such as logins, SQL Agent jobs, etc...
• Requires additional storage for mirrored copy
• If Snapshots are used for read only, the snapshot is only as current as when the snapshot was created.

How many databases can be mirrored on a single instance of Microsoft SQL Server?

We can configure 10 databases for 32-bit operating system.On a 32-bit system, database mirroring can support a maximum of about 10 databases per server instance because of the numbers of worker threads that are consumed by each database mirroring session.

For 64-Bit Operating system we can mirror more than 10 databases depending on the number of processors and worker threads. Many company has deployed more that 10 Databases as mirrored.