Database mirroring feature is available in SQL 205 and SQL 2008. Its a solution to provide database high availability and its configured per-database basis. You can only configure the database mirroring for the databases that use the full recovery model. You cannot configure the database mirroring for the System Databases (master, msdb, tempdb & model).
In database mirroring there will be 2 copies of a single database residing on 2 different SQL instance. One SQL server instance act as Primary server and holds the active databases. All the clients or applications connect to this database server. Other SQL Server instance act as Secondary server or mirror server which holds the hot or warm databases.
Once the database mirroring has been configured and databases are synchronized state, failover can be performed without a loss of data. While you configure the SQL database mirroring you can also configure the Witness server. This is going to another SQL instance which would be monitoring the primary SQL server instance. If primary SQL Service fails then witness server will automatically failover the databases from primary SQL server instance to Secondary SQL server instance.
There are 2 operating modes for configuring Database mirroring:
•Asynchronous: This doesn't need the Witness Instance, that means there is no automatic failover. Once the Database server is not available administrator to stop the database mirroring session manually.
•Synchronous: You can configure this with witness server (optional). If you configure with witness server then you get the advantage of automatic failover feature. However, automatic failover requires both the databases to synchronized.
•Synchronous: You can configure this with witness server (optional). If you configure with witness server then you get the advantage of automatic failover feature. However, automatic failover requires both the databases to synchronized.
Lets look at the advantages and disadvantages of database mirroring.
•Database Mirroring Advantages:
◦Easy setup and maintenance
◦Real-time synchronization of transaction log
◦Automated failover (in High Availability Mode, Witness Server Instance required)
◦Fast & easy Failover
◦Using connection strings you can make your application mirroring aware or using SQL alias.
◦Easy setup and maintenance
◦Real-time synchronization of transaction log
◦Automated failover (in High Availability Mode, Witness Server Instance required)
◦Fast & easy Failover
◦Using connection strings you can make your application mirroring aware or using SQL alias.
•Database Mirroring limitations:
◦Database mirroring is configured per database not per SQL server Instance.
◦Mirror database always in recovery mode and this database can’t be used for any purpose or database queries.
◦Database mirroring can only configured for one database to another database. There can’t be more secondary server.
◦Database mirroring can’t be configured for once database once than once.
◦Database mirroring is configured per database not per SQL server Instance.
◦Mirror database always in recovery mode and this database can’t be used for any purpose or database queries.
◦Database mirroring can only configured for one database to another database. There can’t be more secondary server.
◦Database mirroring can’t be configured for once database once than once.
Note:- Database mirroring can be configured for the databases having Full Recovery Model. Database mirroring can not be
No comments:
Post a Comment