The scope of this document is to show the process of mirroring a SQL Server 2008 r2 database without a witness server, perform a manual failover, and then recover the principal database. This document will not show how to install, manage, and setup basic SQL Server 2008 R2 services.
Post Installation Setup
After both the SQL Server 2008 R2 installations are completed set the “Log On As” accounts to your domain service account in the SQL Server Configuration Manager for both the Principal and Mirror Servers. This account should the same for both installation’s, and should not be a domain administrator account.
Pre-Mirror Configuration
In order for the principal database to update the mirror database the mirror database needs a full and transactional log restore of the principal database. The mirror database also needs to be restored the NO RECOVERY option.
*If you plan on using the Mirror server as a failover box be sure to set up any database, user accounts ahead of time. Be sure the mirror server is and their databases are in a working state before setting up the mirror.
Backup Principal
To perform a FULL backup of the principal database right click the database and select; Tasks > Backup. Be sure to select FULL under source >> backup Type.
Restore Mirror
When restoring the mirror database it’s important to restore the DB using the NO RECOVERY option. If you don’t use the NO RECOVERY option the mirror will fail after clicking the start mirror button later in the document. To restore the DB right click the DB and select Tasks >> Restore >> Database. After selecting your sources click the options tab and make sure the “RESTORE WITH NO RECOVERY” option is checked.
To restore the transaction log right click the DB and select Tasks >> Restore >> Transaction Logs. After selecting your sources click the options tab and make sure the “RESTORE WITH NO RECOVERY” option is checked.
Create the Mirror
Once the above pre-requisites are setup the mirror can be created fairly easily. Right click the DB and select Tasks >> Mirror. This will open the Mirroring Security Wizard. Click the Configure Security button. Select “No” when prompted to include a witness server. Select the server instance, set an endpoint name, click Encrypt data through the end point and click next. In the Mirror Server Instance click connect and use the familiar “Connect to Server” dialog box to connect to the Mirror server. Then set an endpoint name, click Encrypt data through the end point and click next. In the service accounts text boxes use the service accounts we used in the post installation setup. DOMAINNAME\USERNAME. Click next, finished, and the start mirroring.
Manual Failover
In the event of a failure to your principal server your mirror server is useless until you perform some basic functions, and run some scripts. The scripts below will put your DB in ready state.
Turn mirroring off on the mirror DB.
ALTER DATABASE <<DB NAME>> SET PARTNER OFF
Set the recover mod the DB to RECOVERY
RESTORE DATABASE <<DB NAME>> WITH RECOVERY
Fix and orphaned users if you are using SQL authentication
EXEC sp_change_users_login ‘Auto_Fix’ , ‘<<username>>’
Recovery
Once your Principal server is back online you will want to update that server with the latest transactional data, and then recreate the mirror.
Set the Mirror DB in read only mode.
ALTER DATABASE <<DB NAME>> SET READ_ONLY
Backup the Mirror DB and restore it to the Principal DB, and start the process of creating a mirror.
