About Me

"I love being the underdog"

Anthony DeSalvo

Dad, Husband, Roller Derby Widow, Triathlete, Web Developer and RCRD Volunteer!

Mirror Failover and Recover with SQL Server 2008 R2 Standard

by adesalvo 14. January 2011 02:48

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.

 

 

Tags: ,

Tech