Database Mirror How-TO
I have been to many sites and have visited the MSDN Technet forums, read up on all the details for Database Mirroring and even there, it doesn’t quite capture all the little things that must be done to create a successful mirror of a database.
Here is an extract from the Microsoft Website regarding Database Mirroring. Images are taken from the Microsoft Tech article as well from: http://msdn2.microsoft.com/en-us/library/ms189852.aspx
Database mirroring is a software solution for increasing database availability. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model. The simple and bulk-logged recovery models do not support database mirroring. Therefore, all bulk operations are always fully logged. Database mirroring works with any supported database compatibility level.
Database mirroring maintains two copies of a single database that must reside on different instances of SQL Server Database Engine (server instances). Typically, these server instances reside on computers in different locations. One server instance serves the database to clients (the principal server), while the other server instance acts as a hot or warm standby server (the mirror server), depending on the configuration and state of the mirroring session. When a database mirroring session is synchronized, database mirroring provides a hot standby server that supports rapid failover with no loss of data from committed transactions. When the session is not synchronized, the mirror server is typically available as a warm standby server (with possible data loss).
The idea here is not to repeat information that is already provided in the TechNet article, but to help those that are looking the multiple websites and provide an step by step guideline in creating a successful Database Mirror using a Witness server as seen in the following Microsoft image extracted from the website link above.
To accomplish automated failover in a production environment, you minimally require two instances of SQL Server Enterprise Edition and once instance of SQL Express which acts as the witness. The witness simply supports automatic failover by verifying whether the principal server is up and functioning. The mirror server initiates automatic failover only if the mirror and the witness remain connected to each other after both have been disconnected from the principal server.
Setup Prerequisites
- SQL Server SP1 applied to SQL Server install (via Microsoft updates). Database Mirroring supported with SP1 only.
- Make sure each server is on the same domain.
- Define one domain account that will be used for windows authentication on each system and add it to the administrator group on each system
- All three server must be reachable via a Fully Qualified Domain Name (FQDN).
- SQL Server Instance must run under the context of the Domain Account.
- Open SQL Server Management Studio on server hosting primary database
- Right click on the database that you would like to mirror and select Tasks –> Backup
- Perform a full backup and then a transaction log backup
- Copy the backup to the server hosting the secondary database
- Open SQL Server Management Studio (i.e., database should not exist at this point)
- Right Click on “Databases” and select “Restore Database”
- Type the name of the database in the “To Database” field
- Using the “From Device” option, select the copied backup.
- Select the “Full Backup”, and go to the Options Page.
- Make sure the second option (RESTORE with NORECOVERY) option is selected.
- Hit OK to restore database.
- Restore the Transaction Log database and make sure the “RESTORE with NORECOVERY” option is selected on the “Options”Page”
- At this point, you should see your database in the following state
(Restoring) - Go back to primary database server, right click on database and select Tasks –> Mirror
- In the new dialog, hit the “Configure Security” button
- Include the option to configure a “Witness” server
- Choose the appropriate locations for your principle, mirror and witness instance as long as the encryt endpoint option is deselected on each page.
- In the “Service Accounts” dialog, keep all fields blank as Windows Authentication will be used for database access using the aforementioned domain account.
- Each endpoint will be configured and each should succeed.
- Once complete, you have the option to start monitoring. Simply reconfirm that the server instances are accessed via their FQDN entries.
- Hit the start mirroring button.