Database Mirroring Enhancements in SQL Server 2008 from 2005

Leave a comment (1) Go to comments

Database mirroring is an alternative high-availability solution to failover clustering in SQL Server Enterprise. Database mirroring supports automatic failover, but does not require cluster-capable hardware, and can therefore provide a cost-effective alternative to failover clustering.

This article is not focused on to make you understand Database Mirroring Concept, rather this focuses on Enhancements, which are being done in SQL Server 2008 for Database Mirroring

Database Mirroring Enhancements in SQL Server 2008 from 2005
  • Page-level mirroring:
    • If a page on the principle or mirror server is corrupt, it is automatically replaced with corresponding copy on its partner
  • Automatic Page Repair on Mirror Servers
    • If a page on the principle or mirror server is corrupt, it is automatically replaced with the corresponding copy on its partner
    • Some page types cannot be automatically repaired:
      • File header pages
      • Database boot page
      • Allocation pages
    • I/O errors on the principle server may be fixed during the mirroring session
    • I/O errors on the mirror server require the mirroring session to be suspended
  • Compressed Data flow
    • Data Flow between the principle and mirror server is now compressed to improve performance
  • Manual Failover
    • Manual failover no longer require a database restart
  • Log Performance
    • Write-ahead on the incoming log stream on the mirror server
    • Improved use of log-send buffers
    • Page read-ahead during the undo phase after a failover

Database mirroring enables you to maintain two copies of a database. One copy is the principal server that client computers access. The other copy acts as a standby server. In the case of a failure of the principal server, the client computers can use the failover capability to connect to the standby computer with no loss of data. Mirroring can therefore increase the availability of a database and provide data protection. SQL Server 2008 offers several enhancements to the database-mirroring environment, including the following:


  • Page-level mirroring. This replaces corrupt pages on one server with the same page on the partner server.
  • Compressed data flow. This provides improved performance and reduces the network bandwidth that database mirroring uses.
  • Manual failover. This no longer requires a restart of the database
  • Log performance. This has the following improvements:
    • Write-ahead on the incoming log stream on the mirror server. This writes the incoming log records to disk asynchronously.
    • Improved use of log-send buffers. If the most recently used log cache contains enough free space for the current log records, they are appended to that log cache.
    • Page read-ahead during the undo phase after a failover. The new mirror server sends read-ahead hints to the principal server and the principal server puts those pages in its send buffer. This process improves the speed of the undo phase.

Before SQL Server 2008, data restore could occur at the file level only. A corrupt page may require a failover and then a restore of the file that contains the corrupted data. This procedure is expensive due to the resources and time that are used, and because more data is replaced than was actually corrupted.

SQL Server 2008 provides recovery at the page level. The database-mirroring environment automatically replaces corrupt pages on one server with the same pages from the partner server. The process does not require user intervention and does not interrupt the availability of the server. By using automatic page repair, the principal and mirror computers can recover from data page errors and from errors that prevent reading a data page.

If a mirror server finds a page with errors, it puts the mirroring session into the SUSPENDED state, logs the error, and then requests a copy of the page from the principal server. If the principal server can access the page, it sends a copy to the mirror server, which replaces the page and resumes the mirroring session. Otherwise, the mirroring session remains in a suspended state.

Automatic page repair is only available in SQL Server Enterprise. However, if you have one mirror running on Enterprise and one mirror on Standard, corrupt pages can be repaired on the Enterprise instance, but not on the Standard instance

How to Set up Database Mirroring in SQL Server 2008 (Video)

Tags: Database Mirroring Performance Enhancements,sql server 2008 Mirroring Enhancements,SQL Server 2008 High Availability enhancements,sql server Availability Enhancements,what new added in sql server 2008 for mirroring,Database Mirroring enhancements in SQL Server 2008,Setting Up Mirroring on SQL Server 2008,Pro SQL Server 2008 Mirroring,Configure Database Mirroring using SQL 2008 ,Enhancements which are being done in SQL Server 2008 for Database Mirroring ,Page-level mirroring,Automatic Page Repair on Mirror Servers,sql server mirroring Compressed Data flow, New Features in SQL Server 2008,Automatic Page Repair on Mirror Servers,sql server mirroring page types cannot be automatically repaired,sql server page types cannot be automatically repaired in Mirroring,

EOF - Database Mirroring Enhancements in SQL Server 2008 from 2005, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment

1 Comments.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.

Trackbacks and Pingbacks: