MS SQL SERVER DBA

The principal database and the mirror database should be on separate physical hardware, and ideally, in different physical locations. The witness server should be on separate physical hardware, and be on a separate network (best if at a third location). Initial database mirroring setup should be done during less busy times, as the setup process can negatively affect performance of the production database being mirrored. Use high availability mode whenever possible, and high performance mode only when required. The hardware, along with the OS and SQL Server configuration, should be identical (at least very similar) between the two servers. While a fast connection is not required between mirrored servers, the faster the connection, and the better quality the connection, the better. You will want to optimize the performance of the mirrored database as much as po...

Database states for each server are kept during the database mirroring session, recorded on each partner server, and reported by the sys.database_mirroring catalog view. The mirroring_state column returns a number for the state, and the mirroring_state_desc column returns the descriptive name for the state. State information about the witness is also reported from the same catalog view. In addition to the states reported for each database, there are three phrases that are useful in describing the servers and databases involved in database mirroring. 1. Exposed - The data on the principal is exposed when it is processing transactions but no log data is being sent to the mirror. When a principal database is exposed, it is active with user connections and processing transactions. However, no log records are being sent to the mirror database, and if the principal should fail, the mirror will not have any of the transactions from the pr...

1. Make sure that the two partners that is the principal server and mirror server, are running the same edition of Microsoft SQL Server 2005. The partners require either SQL Server 2005 Standard Edition or SQL Server 2005 Enterprise Edition or SQL Server 2005 Developer Edition. 2. If you are using a witness, make sure that SQL Server 2005 is installed on its system. The witness can run on any reliable computer system that supports SQL Server 2005 Standard Edition, Enterprise Edition, Workgroup Edition, or Express Edition. 3. SQL 2005 SP1 or later version is required for Mirroring 4. The principal database must be in the FULL recovery model . Log records that result from bulk-logged operations cannot be sent to the mirror database. 5. Verify that the mirror server has enough disk space for the mirror database. 6. All of the server instances in a mirroring session should use the same master code page and ...

Overview of Mirroring: Mirroring is mainly implemented for increasing the database availability. Similar to log shipping mirroring is also implemented on per database basis. 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). Mirroring provides a hybrid solution i.e 1. Provides a copy of the database like Log Shipping and 2. Rapid failover capabilities like Clustering Advantages of Mirroring : * Increases data protection ---> Depending on the mode of operation Mirroring provides minimal data loss. * Increases availability of a database ---> In the event of a disaster, in...