What is database mirroring and  How to create database mirroring?

Database mirroring is software solution  for increasing database availability.It maintains two copies of single database,one as principal database and other as mirror database.So during disaster if primary database server fails then mirror database can be used by application to access the data.This reduces the down time caused during disasters. Mirrored database can not be accessed by application unless roles are switched (Transferring principal role to mirror server) but database snapshot can be created on mirrored database which can be used for read only queries (Load balancing). Refer SQL Server database snapshot steps to create snapshot   

Database  Mirroring can be  implemented  only on databases,that use the full recovery model. The simple and bulk-logged recovery models do not support database mirroring. 

Database mirroring is not applicable in system databases(master,msdb, tempdb, or model databases)

Database mirroring maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine.

There are two mirroring operating modes.

1.High-safety mode:Commit changes at the principal and then transfer them to the mirror.It is asynchronous.

2.High-performance mode:Always commit changes at both the principal and mirror server.It is synchronous

All database mirroring sessions support only one principal server and one mirror server.

Steps to create mirroring:

I have two instances SQLPC\PRADEEPA  which will act as principal server and SQLPC\PK92 will act as mirrored server.

Choose a database in the principal instance which will act as Principal database,

I have created database named “Mirrordatabase” for which we are going to start mirroring.

Create a backup for the Mirrordatabase in the principal server.

Set the backup type in full recovery mode.Select back up destination as disk.Select add to add the destination folder.

  In destination,add the destination folder with .bak extension and click ok. 

Click ok and complete the backup. 

Now restore the backup of the database in mirrored server (destination server) with same name.

  Type the name of the database and restore the database from device.

Select the backup file and click ok.

Now backup location is added.click ok and continue.

Select the backup sets to restore.Then click Options from select a page.

Select the recovery state as RESTORE WITH NORECOVERY.Also change the restore file name in restore as if you are creating mirror on same server for testing.Click ok.

Database restore completed successfully.

Configure mirroring in primary database by selecting properties.

In Database properties window select mirroring and configure it.

Here we dont have witness server.But we can include witness server instance also.Click Next.

Specify principal server instance,listener port and endpoint name.Click Next.

Connect mirror server instance.

Connect it.

Click Next and continue.

Service accounts could be created in this window.

Click Finish and complete the wizard.

Close the window after configuring database mirroring.

Start the mirroring by clicking start mirroring.

We could select either High performance mode or High safety mode based upon our application need from below window.

Mirroring is successfully created. 

You could perform failover between primary database and mirror database by selecting Failover option in properties window.