SQL Database Mirroring for DPM Administrators
This blog focuses on providing a very light-weight overview of how to implement SQL database mirroring and it is not intended to be a complete how-to reference. DPM administrators benefit by understanding, even at a basic level, how to install and configure the applications DPM will be protecting. This blog is intended to provide some of that knowledge as a starting point.
A video demonstration is provided to help guide you through the database mirroring scenario.
Need to Know
Support for SQL Database mirroring was introduced with SQL 2005 SP1. Before that, it was not a supported feature in SQL Server 2005.
Before implementing database mirroring, you must confirm that the Recover Model of the database is set to FULL. Full is the only recovery model that mirroring supports. The Full recovery model provides the normal database maintenance model for databases where durability of transactions is necessary.
Log backups are also required. This model fully logs all transactions and retains the transaction log records until after they are backed up. The full recovery model allows a database to be recovered to the point of failure, assuming that the tail of the log can be backed up after the failure. The full recovery model also supports restoring individual data pages.
For more information, see Backup Under the Full Recovery Model.
SQL Server credentials must match across the servers involved in the mirror or credentials must be provided during the configuration of the mirrored database. In the example which follows, we will work under the premise that the SQL Server service accounts on all servers involved are using the same credentials.
If the mirror will span multiple domains, then Certificates will need to be implemented. Since the purpose of this blog is to provide DPM administrators with a cursory understanding of how to implement mirroring, the use of certificates will not be covered here.
When seeding the mirrored database, the recovery of the database must be done using the (RESTORE WITH NORECOVERY) option. This option leaves the database in a read-only mode to users but allows the Principal SQL Server to still restore transactions to the mirrored copy. SQL Administrators will not be able to pull up the properties of the mirrored database while in this configuration.
Witness servers are a feature that allows SQL to detect failures and automatically failover a database. Without a witness server (which is a 3rd SQL server distinct from the Principal and Mirror servers) manual failover is the only way to switch the mirror roles.
With the release of SQL Server 2008, a new feature is supported in SQL Server called ‘FileStream’. SQL Database Mirroring does not support this feature and if configured, a detailed error will appear during the creation of the mirror.
Mirroring a Database
The mirroring process between SQL 2005 and 2008 has enough over-lap that we can cover one, in this case mirroring of a SQL 2005 database, and still have a sound enough understanding of how a SQL Server 2008 database can be mirrored using the same steps. For this reason, we will cover the SQL 2005 database mirroring process. Further details can be found in the SQL Server section of the TechNet web site at https://technet.microsoft.com/en-us/library/bb545450.aspx.
Mirroring AdventureWorks
As with any database that will be mirrored, start by opening the properties and setting the Recovery Mode to “Full”, if it is not already.
Once this has been set, you will now need to make a backup of the database. Make sure it is a Full backup and for convenience, make sure that you are not using the Append option so that the BAK file size is minimized and the restore is less confusing.
On the mirror server, you will need to restore the backup of the database that you just made as part of the seeding process.
Make sure that when you perform the restore that you choose the middle radio button as shown below. If the restore is performed without the “RESTORE WITH NORECOVERY” option, SQL will not be able to setup mirroring between the two servers.
Now that the mirror server has a copy of the database restored to it, go to the Principal server and begin the mirror setup. When the Mirroring page of the database properties appears, click on the Configure Security button in the upper right corner of the window as shown below.
Decide whether you will be using a 3rd SQL Server as a Witness server and then click on Next.
If you have not setup mirroring previously, you will need to specify the port for mirroring to use and give the endpoint a name. This endpoint will be created on each server and can be viewed from SQL Management Studio.
Configure each server and confirm connectivity as well as the security credentials. Once connectivity has been confirmed and the SQL Service account credential information has been specified, click on Finish to allow the mirroring to be established between the two servers.
Once mirroring is configured, it is not started so you will have to click on the Start Mirroring button as shown below to begin the transaction replication from the Principal to the Mirror server. If all goes well, you will see the databases appear like the following.
If you want to try to failover the database so that Principal and Mirror roles are switched, click on the Failover button. This will cause a warning dialog to appear confirming your decision. Choose Yes to allow the failover to occur.
Basic Troubleshooting
As a DPM administrator, there is often not a lot of time allocated to troubleshoot setup and installation issues and these types of issues are common when working with unfamiliar technologies. Here are a few of the most common database mirroring configuration issues that arise. Take a look at https://TechNet.Microsoft.com for more detailed information about other failures that you encounter.
If the database is not in Full Recovery mode, the following is displayed. If the restored copy of the database did not have Full Recovery mode setup when it was backed up, then you will need to delete it and create a new backup after making this setting change.
If the mirror copy was not restored using the “RESTORE WITH NORECOVERY”, the following error will be displayed. Simply remove the restored copy of the database and restore it using this option to work around this issue.
In a busy environment, there may be transactions that are not captured. If this is the case, the following error may be displayed. You may need to place the Principal database in single-user mode and backup the transactions. Once these are restored to the mirror server, then try to configure mirroring.
Summary
Database mirroring can be established where one side of the mirror is on a Failover Cluster. You can even have the same SQL server acting in a mirroring partnership with 3 or 4 or more other SQL Servers. This is not a recommended practice however as this configuration can create confusion and, when it comes to DPM protection, all servers participating in a mirroring partnership must have the DPM agent installed. If the agent is not installed on all servers, DPM will not be able to protect the mirrored database.
In an upcoming blog, we will cover the protection of a mirrored database using DPM 2007 SP1. Subsequent blogs will also discuss the recovery of a mirrored database to its original location. When reading the blog on recovering mirrored databases, you may need to refer back to this blog as the recovery process requires the database mirror to be broken and re-established in order to complete the restore.
Vic Reavis
Support Escalation Engineer
Microsoft Enterprise Platforms Support