Database Mirroring (DBM) is one of high availability feature in SQL Server. Database Mirroring are high availability solution in database level. It protects at database level. What is database mirroring? Database mirroring is technology from SQL Server to increase High availability by creating standby database on secondary instance or perhaps secondary machine named “Mirror Server” as an alternate of production server in case of emergency. Database mirroring works to create exact copy of principal database and synchronize every transaction change in principal database to mirror database and then in case of emergency DBM can switch role between principal and mirror database. Database mirroring require same sql server edition for principal & mirror database. According to Microsoft High Availability “Solution masks the effects of a hardware or software failure and maintains the availability of applications so that perceive downtime for user is minimized”.
Key Terms in Database Mirroring
- Principal: Primary server that contain source database (principal database). Database must be user database with Full Recovery Model.
- Mirror: Secondary server or target server that contain destination database (Mirror Database). Database must be user database with Full Recovery Model.
- Witness: Optional server to create quorum that allow automatic failover.
- Partners: Opposite server that refer to principal or mirror server.
- Session: Active relation between principal database and mirror database that contain state information about one to another.
- Operating Mode: Database Mirroring operating modes which High Protection Mode (Synchronous without witness), High Availability Mode (Synchronous with witness), High Performance Mode (Asynchronous without witness).
- Role: Server role refer to principal, mirror, witness.
Database Mirroring Operating Modes.
There are several operating modes in database mirroring. Let’s we discuss the different between operating modes and consideration to choose configuration between them.
- High Protection Mode (Synchronous without witness): High Protection mode ensure transaction chances from principal database has been synchronous in mirror database. In this operating modes Database Mirroring ensure transaction committed on mirror database and send acknowledgment to principal database so principal database committed same transaction. Transaction committed on both principal & mirror database. So application must wait until transaction committed on both database. In order to switching role mirror database become principal database, recovery database process is faster because there isn’t uncommitted transaction to be applied. Fail over can be done manually in this mode. High Protection Mode Require at least Two SQL Server Instance (Principal, Mirror) which can be on the same physical server or on another server.
- High Availability Mode (Synchronous with witness): High Availability mode works same as High Protection mode. In order to automatic failover High Availability mode use Witness server. Witness servers verify connection between Principal & Mirror server and perform automatic failover capabilities. Witness servers perform Automatic failover with established communication check between principal & Mirror server and can switching Mirror role in Mirror server to became Principal Role if Principal server unavailable. High Availability Mode Require at least Three SQL Server Instance (Principal, Mirror & Witness) which can be on the same physical server or on another server.
- High Performance Mode (Asynchronous without witness): High Performance mode perform Asynchronous operation between Principal & Mirror database. Transaction committed in principal database without wait acknowledgement from mirror database. In this mode application doesn’t wait committed transaction in mirror server. Automatic failover couldn’t be applied in this mode, because High Performance mode doesn’t need quorum (Witness Server) for establish communication between principal & mirror database. High Performance Mode Require at least Two SQL Server Instance (Principal, Mirror) which can be on the same physical server or on another server.
1. Full recovery model for principal database. How to check recovery model use the following Query.
For change Recovery model in database use the following query.
2. Set Compatibility of Database to level 90 (SQL 2005) or higher. use the following query to set compatibility database level.
3. Create Full & Transaction Log Backup of Principal Database. Disable all backup jobs (Full & Transaction Log Backup of Principal database) before establishing Database Mirroring. Use the following query for backup database Full & Transaction Log.
4. After Backup Database (Full & Transaction Log), copied Backup file to mirror server, make sure you have disabled AdventureWorks2008 backup job in principal server. After copied Backup file, Restore Full & Transaction Log into mirror server. Use the following Query for Restore Backup Full & Transaction Log with NoRecovery Option.
5. Let’s get back to database in principal server. Right click database click on properties. On mirroring page click configure security button.
6. Click next on Configure Database Mirroring Security Wizard. In Include Witness Server page, If we want to configure database mirroring with High Protection Mode or High Performance Mode choose option No. If we want to configure database mirroring with High Availability Mode we should choose option with Yes. We will configure with High Availability Mode, so choose yes.
7. In choose servers to configure Page, check list Witness Server instance to configure Witness server security.
8. In the Principal Server Instance page, review security information about 1. Principal Server Instance, 2. Listener Port, 3. End Point Name. Click next.
9. In the Mirror Server Instance Page, click button connect to choose Mirror server. Review security information about 1. Mirror Server Instance, 2. Listener Port, 3. End Point Name. Click next.
10. In the Witness Server Instance Page, click button connect to choose Witness server. Configure Listener Port for Witness Server. Review security information about 1. Witness Server Instance, 2. Listener Port, 3. End Point Name. Click next.
11. In the Service Account page, specify service accounts for each Instance. If you use different domain accounts, enter accounts for each instance. Click next to display complete.
Click Start Mirroring to start Database Mirroring with High Availability Mode.
12. Let’s we review database mirroring configuration.
Right now we have Database Mirroring with High Availability Mode, next article we will try to testing & monitoring our Database Mirroring with inserting data and try automatic failover between principal & Mirror server.