How to configure SQL Server database mirroring

Database mirroring is a SQL Server solution for managing database availability. Mirroring provides recovery at the database level, and can be used by itself, or as a supplement to log shipping or clustering. There are several ways to put it together, but in this example I will give you the basic instructions for setting up high performance database mirroring.  

First, we will create our endpoints. An endpoint is an object that enables SQL Server to communicate over the network. For database mirroring, each instance requires its own, dedicated endpoint. All mirroring connections on each instance will use this endpoint.

/* Create endpoint on the Principal server instance  */
CREATE ENDPOINT [EndPoint_Mirroring]
    AUTHORIZATION [MyDomain\sqlservice]
    STATE = STARTED
    AS TCP (LISTENER_PORT= 5023, LISTENER_IP = ALL)
    FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE,
    ENCRYPTION = REQUIRED ALGORITHM RC4)          

/* Create endpoint on Mirror server to mimick the Principal endpoint  */
CREATE ENDPOINT [EndPoint_Mirroring]
    AUTHORIZATION [MyDomain\sqlservice]
    STATE = STARTED
    AS TCP (LISTENER_PORT= 5023, LISTENER_IP = ALL)
    FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE,
    ENCRYPTION = REQUIRED ALGORITHM RC4)      

Now we will check that the database we intend to mirror has FULL recovery model.  If not, we will need to change it.

     -- check the recovery model
     SELECT name, recovery_model_desc
     FROM sys.databases
     WHERE name = 'DBName';

    -- change the recovery model if it is not FULL
   USE master;
   ALTER DATABASE DBName SET RECOVERY FULL;

At this point, we need to backup the database on the Principal server that you intend to mirror. Here you will backup of both the database and log, writing to separate files as I have shown here:

  -- full database backup
  BACKUP DATABASE DBName
  TO DISK = '\\MirrorServerName\D$\DBName.bak'
  WITH FORMAT;

 -- full log backup
 BACKUP LOG DBName
 TO DISK =  '\\MirrorServerName\D$\DBName_Log.bak'
 WITH FORMAT;

Important note, you see that we backed up across the network to the Mirror server. Because I did that, there is no need to copy the files.  If I had backed them up locally to the Principal, it would still be necessary to physically copy the files to the Mirror server location.  We will now restore them as follows:

 USE master;
 RESTORE DATABASE DBName
     FROM DISK = 'D:\DBName.bak'
     WITH REPLACE, NORECOVERY;

  USE master;
 RESTORE LOG DBName
 FROM DISK = 'D:\DBName_Log.bak'
 WITH NORECOVERY;

We're almost there.  Now we need to create our partnerships between the mirrored servers.  You can see that this is done on both servers, Principal and Mirror:

-- On MIRROR, create parternship to PRINCIPAL
ALTER DATABASE DBName SET PARTNER =    
          N'TCP://PrincipalServerName.DomainName.Local:5023'

-- On PRINCIPAL, create partnership to MIRROR.
ALTER DATABASE DBName SET PARTNER =
        N'TCP://MIrrorServerName.DomainName.Local:5023'

Lastly, we will run this on the Principal server to set the partner safety off.  Thus, enabling High Performance mirroring:

  

   ALTER DATABASE DBName SET PARTNER SAFETY OFF;

That's pretty much it.  As I said, this is just a very basic deployment of Asynchronous database mirroring, or high performance mirroring.  Often times you'll need to get in there and manually adjust the Mirroring.  I have included several statements below that can be used for the different actions I have outlined with each. 

    Remove Mirroring 
         ALTER DATABASE DBName SET PARTNER OFF;

   The above statement will leave the database in a restoring state.  You can run this to 
   complete that restore, and make the database usable again:
        RESTORE DATABASE DBName WITH RECOVERY:

   To pause mirroring, possibly while applying a service pack update:  

       ALTER DATABASE DBName SET PARTNER SUSPENED;

   To resume a mirroring session, connect to either partner and run this statement:

       ALTER DATABASE DBName SET PARTNER RESUME;

  To remove a Witness server:
       ALTER DATABASE DBName SET WITNESS OFF

  Manually failover the database:
     ALTER DATABASE DBName SET PARTNER SAFETY FULL;
     ALTER DATABASE DBName SET PARTNER FAILOVER;

 After failing over, you should run this on the new mirror to verify the results:
        SELECT db.name, m.mirroring_role_desc
        FROM sys.database_mirroring m JOIN sys.databases db
          ON db.database_id = m.database_id
        WHERE db.name = N'DBName';

You should also take a look at this other post of mine, which provides more detail about checking the status of your mirror sessions:
  http://www.sqlfingers.com/2014/07/what-is-your-database-mirror-status.html