Database mirroring is a solution for increasing the availability of a SQL Server database. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model.

Benefits of Database Mirroring:

Increases availability of a database: In the event of a disaster, in high-safety mode with automatic failover, failover quickly brings the standby copy of the database online (without data loss).

Increases data protection: Database mirroring provides complete or almost complete redundancy of the data, depending on whether the operating mode is high-safety or high-performance

Improves the availability of the production database during upgrades: To minimize downtime for a mirrored database, you can sequentially upgrade the instances of SQL Server that are hosting the failover partners. This will incur the downtime of only a single failover. This form of upgrade is known as a rolling upgrade.

Requirements:

Principle database recovery model should be full.

Mirror database recovery state should be no recovery.

Endpoint: Is nothing but a logical service point associated with a port no. It is a communication channel between servers in network.

PrincipalServer:[ADMIN-0FFC6F203\MSSQL2008] DBName: [Princ_Mirror]

Mirrored Server:[ADMIN-0FFC6F203\MSSQLSERVERTEST] DBName: [Princ_Mirror]

Pre-Requisites

- Ensure that SQL Service /SQL Agent Services on all the machines start with the same domain account, otherwise mirroring wont work.

Step 1 : Install database on Mirrored Server

Create a backup of the MirrorDB in the source SQL Server (Principal database) & restore it in the destination server with the same name. Make sure to restore the database with NORECOVERY. The destination server MirrorDB is called as Mirror database.

In order to get the database onto the mirrored server, we do a full backup of the ‘Princ_Mirror’ database on the Principal server, followed by a backup of the Transaction Log.

• Perform a full backup of the database on the Principal server.

         BACKUP DATABASE Princ_Mirror TO DISK = 'C:\Princ_Mirror_Full.Bak'

• Perform a Transaction Log backup on the Principal server.

  BACKUP DATABASE Princ_Mirror TO DISK = 'C:\Princ_Mirror_tran.Bak'

• Copy the backup files to the Mirror.

• Do a restore of the full backup With NORECOVERY (if you use the visual tool, go to Options, then ensure you check the No Recovery option)

RESTORE DATABASE Princ_Mirror

FROM DISK = 'C:\Princ_Mirror_Full.Bak' WITH NORECOVERY,

MOVE Princ_Mirror _Data' TO 'D:\Data\ Princ_Mirror.mdf',

MOVE Princ_Mirror _Log' TO 'C:\Data\ Princ_Mirror.ldf'

• Perform another restore of the Transaction Log, also with the NORECOVERY option. (This is important; otherwise you'll get an error when starting the mirror).

RESTORE LOG Princ_Mirror FROM DISK ='C:\ Princ_Mirror_Tran.Bak' WITH NORECOVERY

                Types of Mirroring:

We have 2 types of database mirroring

1) Asynchronous database mirroring.

2) Synchronous database mirroring.

Database Mirroring can be operated in 3 modes.

1) High performance.

2) High protection

3) High availability

                                       How to fail over Database Mirroring in SQL Server 2005 using T-SQL

I've previously shown how to setup Database Mirroring in SQL Server 2005 with T-SQL, but how do you failover to the mirrored databases using T-SQL?  Here's how:

--Run on principal

USE master

GO

ALTER DATABASE dbName SET SAFETY FULL

GO

ALTER DATABASE dbName SET PARTNER FAILOVER

GO

--Run on new principal

USE master

GO

ALTER DATABASE dbName SET SAFETY OFF

GO

If you are using synchronous database mirroring, just ignore the "SET SAFETY" commands. 

We use asynchronous database mirroring for performance reasons as our sites are 300 miles apart.  Even though we have a fast network in between the two sites, the latency is too high when we tried it in synchronous mode.

Verifying the Database Mirroring Configuration using the below query\dmv's

select name,type_desc,state_desc,port,is_dynamic_port,ip_address from sys.tcp_endpoints

go

select database_id,mirroring_state_desc,mirroring_role_desc,mirroring_partner_name,mirroring_partner_instance from sys.database_mirroring

****************************************************************************************************************************************************************************************************************************************

Note:   If the paths differ:

If the path of the mirror database differs from the path of the principal database (for instance, their drive letters differ), creating the mirror database requires that the restore operation include a MOVE clause.

https://technet.microsoft.com/en-us/library/ms189047(v=sql.105).aspx

https://technet.microsoft.com/en-us/library/cc917681.aspx

  Attached Related Documents  Below :

********************************************************************************

How to add a database file to a mirrored SQL Server database

*********************************************************************************

Step 1 First, we should check the mirroring configuration and partner status. 

Step 2

we should disable any SQL Server backup jobs to reduce any futher complexity

Run the below command on the principal server to break the mirror.

Step 3 Next, create your database file on the principal server. I ran this statement to create a secondary database file on the principal server. You can use the GUI method as well to create this database file.

Step 5 Before restoring the log backup on the mirrored server, you can check whether your newly created file is captured in the log backup. Run the RESTORE FILELISTONLY statement to get this info.

We can see that the newly created file is captured in the log backup file, so go ahead and restore this log backup on the mirrored server using the NORECOVERY and MOVE options. We are using the MOVE option to place the file in a different location on our mirrored server.

Step 6 Now the principal database and mirror database have the new database file. Now we should re-establish mirroring between the servers for this database. First, add the partner server on the mirror server. The below SQL code will add the principal server on the mirror server.

Step 7 Now add the partner server on the principal. Run the below SQL code to add the mirror server to the principal server to resume mirroring.

Step 8Once the above commands are successful, you are done with your task to add a database file to a mirrored database.We can check and verify whether the mirroring configuration is established between both databases. Run this SQL statement along with sp_heflpfile on the principal server.