Saturday, January 19, 2013

Database Mirroring Basics

Prerequisite : Source database must be in FULL RECOVERY model

Initialization Steps
  1. Perform FULL backup of source database
  2. Perform LOG backup of source database
  3. Restore FULL backup with NORECOVERY on target server
  4. Restore LOG backup with NORECOVERY on target server

Configure Mirroring
  1. Right-Click Database, select Mirroring page.
  2. Use Wizard to configure Principal & Mirror Servers
    * You may need to open port 5022 on the firewall (or whichever alternative you choose).
  3. Choose between High Performance (asyncronous) or High Saftey (syncronous) modes
  4. Start Mirroring

Adding a witness
  1. Create an endpoint on the new server
  2. Ensure the same Windows logins exist on each server & grant connect permissions
  3. On the Principle Server, alter the pricinipal database to use the endpoint on the new server as a witness.

NB ;
  • High Safety (syncronous) could slow a system if bandwidth is limited as commits need to occur on both servers.
  • High Performance (asyncronous) allows the changes to be commited at the principle first.

Links :
Configure SQL Server Database Mirroring Using SSMS
Database Mirroring Tips

No comments: