This reviewer is part of lesson in Implementing High Availability in SQL Server 2012. This covers about 12% of the certification exam.
Database mirroring is a process of creating and maintaining an always up-to-date copy of your database on another SQL Server instance. Transactions applied to principal instance is also applied to the mirrored instance.
There are two modes you can apply when setting up database mirroring in your environment.
High-safety mode enables failover to occur without data loss from committed transactions. Hot standby is possible when you are using the high-safety mode. When using this mode, transactions are committed on both partners after they are synchronised. The main drawback here is that there is increase in transaction latency. If a witness server is present you can enable automatic failover in this mode.
High-performance mode enables failover to occur, but data loss is possible. During this mode, the primary instance does not wait for the mirror instance to confirm that it has received the log.
Database Mirroring Prerequisites
When setting up mirroring, remember that you can mirror only databases that are using the full recovery model. Database mirroring supports only a single principal instance and a single mirror instance.
The principal, mirror and witness instance must all be running the same version of SQL Server. The primary and mirror instance must run the same edition, whereas the witness instance must run on SQL Server edition that supports witnessing.
When starting to setup mirroring session, do a restore of the database in the mirror instance using the NO RECOVERY option. You will need a full backup and a transaction log backup of the database from the principal instance. Then restore the full backup and followed by restoring also the transaction log backup still using the NO RECOVERY option.
After having successfully completed the above, your database in the mirror instance must be in ‘RESTORING’ status.
If you are configuring your mirroring setup to use Windows authenticated SQL service accounts, make sure that your principal, mirror and witness (if you have one) are members of the same active directory domain or trusted domains.
Configuring Database Mirroring
After all the prerequisites from above are followed, you can now start to configure mirroring in your environment.
In your SSMS, right click on the database you want to mirror, select Tasks then select Mirror. This will open the Database Mirroring wizard.
On the mirroring page, click Configure Security to open the Configure Database Mirroring Wizard. Click Next.
On the Include Witness Server Page, choose No if you are not setting up witness server, else choose Yes.
On the Choose Servers to Configure page, choose whether to save security configuration information on the Witness server instance. This is saved by default on the Principal and Mirror server instances.
On the Principal Server Instance page, verify if the default listener port or configure an alternate port.
On the Mirror Server Instance page, click Connect to open Connect to Server dialog box. You need to make this connection to the mirror instance by using a login or an account that has a CREATE ENDPOINT permission, or use a sysadmin account.
On the Witness Server Instance page, choose your witness instance and click Connect to open Connect to Server dialog box. Again for this case, when connecting to your witness server, use an account with CREATE ENDPOINT permission, or use a sysadmin account.
On the Service Accounts page, enter the domain accounts that are to be used for SQL Service on each instance. Click Next.
After verifying your setup, click Finish.
If you are setting up your mirroring using a certificate authentication, you will need to backup your certificate in the principal instance and copy them to your witness and mirror instance so you can create logins and users based on these certificates. After the logins and certificates for the mirror instance has been created, you need to grant permissions to the login on the endpoint.You may run statement below
GRANT CONNECT ON ENDPOINT :: EndpointMirror TO [login];
Go back to your principal instance, right click on the database your are going to mirror, select Tasks, select Mirror and click Start Mirroring. You are now mirroring your principal database depending on your selected mirror mode. If the configuration is successful, your principal database should now be on ‘Principal’ status.
Database Mirroring Monitor
To verify your mirroring status and monitor, open your SSMS, right click on the principal database, select Tasks, and click on Launch Mirroring Monitor. This will open the mirroring status window similar to below.
From this window, you can view the status of your mirrored database, check size of unsent logs, data transfer rate and oldest unsent transaction. Database mirroring monitor can also provide the status of the connection to the witness if one is present, the witness address and the operating mode.
Alternatively, you may run the T-SQL statement below to check that status of your mirrored database.
SELECT mirroring_state_desc, mirroring_role_desc, mirroring_safety_level_desc,
Similarly, there are built in stored procedures in SQL Server 2012 to manage monitoring of mirrored databases;
Future of Mirroring
Microsoft intends to remove mirroring in the future releases of SQL Server. Plan on implementing AlwaysOn availability groups. This will be my topic in my next post :)