Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.
Loading

1st Prize - Nokia Lumia 710


Win 31 copies of

SQLServer Quiz 2012 - Though High-Availability is something critical for all large database deployments

  • Though High-Availability is something critical for all large database deployments. There are multiple options one can use. For discussion today, what is database mirroring option? What are the Database Mirroring enhancements done with SQL Server 2008 R2 edition? How is it different from Log shipping?

    Posted on 01-23-2012 00:00 |
    InterviewQuestions
    73 · 2% · 775

6  Answers  

Subscribe to Notifications
  • Score
    10

    what is database mirroring option?

    SQL Server 2005 provides a set of high availability methods that the users can use to achieve fault tolerance and to prevent server outages and data loss. The selection of the high availability method depends on various factors. Some DBAs need the servers to be available 24/7, while others can afford an outage of a couple of hours.

    The following high availability features are available with the Enterprise edition:

    Failover Clustering

    Multiple Instances(up to 50)

    Log shipping

    Database Snapshots

    Database Mirroring

    Replication

    In SQLServer Denali - AlwaysOn High Availability

    The following high availability features are available with Standard Edition:

    Failover Clustering(maximum two nodes)

    Multiple instances(up to 16)

    Log shipping

    Database Mirroring

    Database mirroring is a new SQL Server 2005 technology available for review for increasing database availability. Database mirroring transfers transaction log records directly from one server to another and can quickly fail over to the standby server. You can code client applications to automatically redirect their connection information, and in the event of a failover, automatically connect to the standby server and database. Database Mirroring is a primarily software solution for increasing database availability. Mirroring is implemented on a per-database basis. Mirroring works only with full recovery model. Database mirroring is available in the Enterprise edition and in the Standard edition. The user can mirror only the user databases.

    Understanding Database Mirroring Concepts

    Principal: The principal server is the primary database.This acts as a starting point in a database mirroring session.Every transaction that is applied to the principal database will be transferred to the mirrored database.

    Mirror : Mirror is the database that will receive the copies from the principal server.There should be consistent connection between the mirrored and the principal server.

    Standby Server: In the process of database mirroring,a standby server is maintained.This is not accesible to the users.In case of the principal server failing,the users can easily switch over.

    Modes of Database Mirroring: Database Mirroring can work in two ways:synchronous or asynchronous

    What are the Database Mirroring enhancements done with SQL Server 2008 R2 edition?

    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

    1. If a page on the principle or mirror server is corrupt, it is automatically replaced with the corresponding copy on its partner

    2. Some page types cannot be automatically repaired:

       a. File header pages 
      
      
       b. Database boot page 
      
      
       c. Allocation pages
      
    3. I/O errors on the principle server may be fixed during the mirroring session

    4. 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

      a. Write-ahead on the incoming log stream on the mirror server 
      b. Improved use of log-send buffers 
      c. Page read-ahead during the undo phase after a failover
    

    Beginning with SQL Server 2008, a database mirroring partner tries to automatically recover from corrupted pages on the mirror database by resolving certain types of errors that prevent reading a data page. The partner that is unable to read a page requests a fresh copy from the other partner. If this request succeeds, the unreadable page is replaced by the copy, which usually resolves the error.An automatic page repair is an asynchronous process that runs in the background. Therefore, even for a mirrored database, a database operation that requests an unreadable page fails, and it returns the error code for whatever condition caused the failure. When developing an application for a mirrored database, you should intercept exceptions for failed operations. If the SQL Server error code is 823, 824, or 829, you should retry the operation later. Database mirroring automatic page repair tries to repair only pages in a data file on which an operation has failed for one of the errors listed in the following table.

    How is it different from Log shipping?

    alt text

    Database Mirroring benefits:

    Instantaneous synchronization of changes

    Easy setup and maintenance

    Automated failover (in High Availability Mode)

    Easy to perform failover

    Fast failover. (if no transactions are queued)

    Applications can be mirroring aware using failover partner parameter in connection string

    Database Mirroring limitations:

    Limited number of databases can be mirrored per SQL instance. (however, this number is higher than the 10 you see plastered everywhere, but really depends on your hardware)

    Mirrored database cannot be queried, backed up, or basically touched in any way (snapshot can be taken however)

    Only supports one to one server pairings

    Cannot mirror a database more than once

    Synchronous mirroring is intolerant to latency and bandwidth constraints (Asynchronous mirroring has no problems with latency or bandwidth)

    Log Shipping benefits:

    Very flexible - Independent jobs govern backup/copy/restore behavior

    Delay of restore job can protect from database corruption

    Log shipped database can be queried. (in standby mode)

    Log shipping allows multiple standby databases

    Perhaps more tolerant to latency than sychronous mirroring.

    Doesn't affect performance of primary server

    Easy setup

    Log Shipping limitations:

    No automated failover

    Cannot synchronize faster than once a minute

    Failover is more complicated than mirroring

    Harder to view status of log shipping

    Replied on Jan 23 2012 2:47AM  . 
    Latheesh NK
    51 · 4% · 1178
  • Score
    9

    Database mirroring

    Database mirroring is a primarily software solution for increasing database availability. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model. The simple and bulk-logged recovery models do not support database mirroring. Therefore, all bulk operations are always fully logged. Database mirroring works with any supported database compatibility level.

    You cannot mirror the master, msdb, tempdb, or model databases.

    Database mirroring maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine. Typically, these server instances reside on computers in different locations. One server instance serves the database to clients (the principal server). The other instance acts as a hot or warm standby server (the mirror server), depending on the configuration and state of the mirroring session. When a database mirroring session is synchronized, database mirroring provides a hot standby server that supports rapid failover without a loss of data from committed transactions. When the session is not synchronized, the mirror server is typically available as a warm standby server (with possible data loss).


    1) PERFORMANCE ENHANCEMENTS INCLUDE THE FOLLOWING:

    • Write-ahead on the incoming log stream on the mirror server.

    In SQL Server 2008, when receiving incoming log records, the mirror server writes them to disk asynchronously. At the same time,

    the mirror server processes log records that have already been written to disk.

    • Improved use of log send buffers.

    In SQL Server 2005, every log-flush operation on the principal server reserves a whole database mirroring log send buffer for its

    log records. In SQL Server 2008, if the most recently used log cache contains sufficient free space for the log records of the next

    log-flush operation, they are appended to that log cache. Otherwise, a new log cache is allocated.

    • Compression of the stream of transaction log records.

    The principal server compresses the stream of transaction log records before sending it to the mirror server. This log compression

    occurs in all mirroring sessions.

    • Compression of stream data for which at least a 12.5 percent compression ratio can be achieved.

    • Page read-ahead during the undo phase.

    After a failover, the new mirror server must undo every change for which a page was written to disk locally but for which the log record might not have reached the former mirror server (the new principal server). To undo such changed pages, the mirror server must first request and receive the corresponding pages from the new principal server. Performance of this part of the undo phase is enhanced in SQL Server 2008. Early in the undo phase, the mirror server sends read-ahead hints to the principal server to indicate which pages will be requested later. After receiving a read-ahead hint for a page, the principal server puts that page in its send buffer. On receiving the corresponding page request, the principal server can respond immediately.

    • Automatic Recovery from Corrupted Pages

    A database mirroring partner running on SQL Server 2008 or later versions automatically tries to resolve certain types of errors that prevent reading a data page. The partner that is unable to read a page requests a fresh copy from the other partner. If this request succeeds, the unreadable page is replaced by the copy, which usually resolves the error.

    • 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

    • 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


    2) USING A WITNESS IN MULTIPLE SESSIONS

    A specific server instance can act as a witness in concurrent database mirroring sessions, each for a different database. Different sessions can be with different partners.

    Sessions

    A single-server instance can also function at the same time as a witness in some sessions and a partner in other sessions. However, in practice, a server instance typically functions as either a witness or a partner. This is because the partners require sophisticated computers that have enough hardware to support a production database, whereas the witness can run on any available Windows system that supports SQL Server 2008.


    SOMETHING ABOUT LOG SHIPPING

    Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually. An optional third server instance, known as the monitor server, records the history and status of backup and restore operations and, optionally, raises alerts if these operations fail to occur as scheduled.

    Log shipping consists of three operations:

    1.Back up the transaction log at the primary server instance.
    
    2.Copy the transaction log file to the secondary server instance.
    
    3.Restore the log backup on the secondary server instance.
    

    The log can be shipped to multiple secondary server instances. In such cases, operations 2 and 3 are duplicated for each secondary server instance.

    A log shipping configuration does not automatically fail over from the primary server to the secondary server. If the primary database becomes unavailable, any of the secondary databases can be brought online manually.

    DATABASE MIRRORING VS LOG-SHIPPING

    1 )

    Database mirroring is functionality in the SQL Server engine that reads from the transaction log and copies transactions from the principal server instance to the mirror server instance. Database mirroring can operate synchronously or asynchronously.

    Log shipping is based on SQL Server Agent jobs that periodically take log backups of the primary database, copy the backup files to one or more secondary server instances, and restore the backups into the secondary database(s). Log shipping supports an unlimited number of secondary’s for each primary database.

    2)
    Database mirroring can operate synchronously or asynchronously. If configured to operate synchronously, the transaction on the principal will not be committed until it is hardened to disk on the mirror.

    Log shipping is always asynchrony. Log shipping totally depends on the log backup and restore schedule.

    3)
    Database mirroring supports only one mirror for each principal database. That means DB mirroring is at database level

    Log-shipping can work on database and server level. You can configure multiple databases in logshipping.

    4)

    Data Transfer:

    Mirroring- Individual T-Log records are transferred using TCP endpoints

    Log-shipping-T-Logs are backed up and transferred to secondary server

    5)

    Transactional Consistency:

    For Mirroring Only committed transactions are transferred

    For Log-shipping All committed and un-committed are transferred

    6)

    Server Limitation:

    For Mirroring Can be applied to only one mirror server

    For Log-shipping Can be applied to multiple stand-by servers

    7)

    Failover:

    For Mirroring it is Automatic

    For Log-shipping it is Manual

    8)

    Failover Duration:

    For Mirroring Failover is fast, sometimes < 3 seconds but not more than 10 seconds.

    For Log-shipping Can take more than 30 mins.

    9)

    Role Change:

    For Mirroring Role change is fully automatic

    For Log shipping-Role change is manual

    10)

    Client Re-direction:

    For Mirroring Fully automatic as it uses .NET 2.0/.Net 3.0

    For Log-shipping Manual changes required.

    11)

    Mirroring Support only full recovery model

    Log-shipping Supports full and bulk-logged recovery model

    12)

    Mirror database is always in recovery mode. To read it you have use database snapshot.

    You can use the stand-by option to read the database on standby server

    13)

    Mirroring database - Auto Page Recovery introduced with SQL SERVER 2008 so it will recover the damaged pages.

    For Log-shipping Not supported

    14) Mirroring database -it is possible to introduce a fixed delay when applying logs to allow the secondary to be used for recovering from user error

    15) Mirroring database - To use database mirroring on a database, the full recovery model is always required.

    Not same for log shipping

    Refrence Links ::

    http://sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-2-Moving-a-database-while-Database-Mirroring-is-running.aspx

    http://www.sqlserver-training.com/database-mirroring-enhancements-in-sql-server-2008-from-2005/-

    http://sqldbpool.com/2010/02/15/database-mirroring-vs-log-shipping/

    Thanks

    Yogesh

    Replied on Jan 23 2012 3:47AM  . 
    Yogesh Kamble
    142 · 1% · 349
  • Score
    9

    What is Database Mirroring?

    Database mirroring is the feature in SQL Server 2005 and SQL Server 2008 that provides a high availability solution for Databases. This feature can be enabled and used only on a database with Full recovery models. The database can be mirrored from one SQL Server instance to another SQL Server instance. The source instance is called Principal server; the target instance is called Mirrored server. We could have one more server called Witness server.The witness server doesn’t participate in mirroring per se—it just helps decide when an automatic failover can occur, under very specific circumstances.

    Two types of mirroring are synchronous and asynchronous

    In Synchronous mirroring, the transaction is written to the log of server a, then to the log of server B, returns success to server A and then is committed. Rollback also takes the same route.

    For asynchronous mirroring, the transaction is written to Server As log and then returns success to the client - the mirroring happens in the same manner as above, but asyncrhonously, resulting in better overall performance.

    DB Mirroring Modes

    Database mirroring operates in three different modes: high-availability mode, high-protection mode, and high-performance mode.

    Quick Note of Database Mirroring

    • Database Mirroring works on physical log records.
    • System databases(master, msdb, tempdb, or model) can not be mirrored.*
    • Database which needs to be mirrored requires it to be in FULL recovery mode.
    • SQL Server 2008 compresses the Transaction Log at Principal Server before it is transferred to mirror server.
    • High-Availability Mode – Synchronous operationed – All the three server roles: principal, mirror, and witness – The transaction safety level is set to FULL – Automatically perform the failover because there is witness serve.Log are committed on Principal and Mirror when databases are synchronized.
    • High Safety Mode – Synchronous operation – Two server roles: principal and mirror – The transaction safety level is set to FULL – Manually perform the failover because there is no witness server – No need a witness server. Log are committed on Principal and Mirror when databases are synchronized.
    • High Performance Mode – Asynchronous operation – Two server roles: principal and mirror – The transaction safety level is set to OFF – Manually perform the failover because there is no witness server – Principal commits and Mirror tries to keep up with received log records from Principal.
    • Database mirroring can be configured along with log shipping, database snapshots and replication.
    • Unlike replication (subscriber server) in mirroring the mirrored server can not be used during mirroring operation going on.
    • Automatic Failover is only possible when high safety mode is configured with automatic failover.

    http://blog.sqlauthority.com/2010/03/16/sql-server-quick-note-of-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

    http://www.sqlserver-training.com/database-mirroring-enhancements-in-sql-server-2008-from-2005

    How is it different from Log shipping?
    The basic difference is that log shipping requires you to back up your log, copy it to the 2nd server and restore it there, whereas mirroring does this as part of the transaction - the second server needs to be running and accessible throughout the transaction lifecycle.

    DB Mirroring Vs Log Shipping?

    Log shipping and database mirroring are two different technologies that provide protection for individual databases. Both technologies rely on the restore and recovery capabilities of SQL Server databases, but implement it in different ways. Log shipping is based on scheduling frequent backups for the transaction log files and storing the backup files in the database of a secondary server. Both committed and rolled-back transactions are logged in the transaction log file on the primary server. This transaction data is then sent to the backup log file on the secondary server.

    Database mirroring is based on TCP endpoints. In database mirroring, only committed transaction data is sent from the principal to the mirror server; rolled-back transaction data is not sent to the mirror server. In log shipping, both committed and rolled-back transaction data are backed up. Database mirroring cannot transfer bulk-logged data, and you can use only one mirror server. Log shipping, on the other hand, can transfer bulk-logged data, and you can use multiple secondary servers.

    Unlike log shipping, database mirroring facilitates failover. If there is a witness server, failover occurs automatically; otherwise, failover needs to be performed manually. A failover takes less than 3 seconds, and the database downtime during a failover is less than 10 seconds. During a failover, the mirror server performs the role of the principal server. Failover preserves only committed transaction data.

    http://www.extremeexperts.com/sql/articles/DBMirroring1.aspx

    Log shipping

    • Primary server, secondary server and monitor server are the components in log shipping set up.
    • Monitor server is an optional.
    • Log shipping is a manual failover process.
    • There will not be an automatic application connection redirection. It has to be redirected manually.
    • Log shipping will have multiple secondary databases for the synchronization.
    • There will be data transfer latency.
    • In log shipping, secondary database can be used for reporting solution.
    • Both committed and uncommitted transactions are transferred to the secondary database.
    • Log shipping supports both bulk logged recovery model and full recovery model.
    • Log shipping can be configured as One to Many. i.e one primary server and many secondary servers.
    • Log shipping cannot handle database file operations
    • Log shipping dependent of SQL Server agent, if sql server agent is down, then log shipping will be unavailable.
    • Secondary database can be used in read-only mode
    • Log shipping is always asynchrony. Log shipping totally depends on the log backup and restore schedule

    DB Mirroring

    • Principal server, mirror server, and witness server are the components involve in database mirroring set up.
    • Witness server is an optional but it is a must for setting up automatic failover since witness is a watchdog instance to check if principal server is working.
    • Database mirroring is an automatic failover process.
    • Application connection can be redirected automatically with proper configuration.
    • Database mirroring will not have multiple database destinations for mirroring the principal database. It will have one mirror * database synchronizes with principal database.
    • There will not be data transfer latency.
    • In database mirroring, mirror database cannot be used for reporting solution. If need comes, database snapshot should be created to set up for the reporting solution.
    • Only committed transactions are transferred to the mirror database.
    • Mirroring supports only Full Recovery model.
    • Mirroring, its one to one. i.e. One primary server to another secondary server
    • Mirroring will handle operations database file operations, such as adding files etc. automatically
    • Independent on SQL Server agent, since communication between primary and secondary happens using TCPIP ports
    • Secondary database will be always in restoring state. We can take snapshots from the secondary database and make read-only to users
    • Database mirroring can operate synchronously or asynchronously. If configured to operate synchronously, the transaction on the principal will not be committed until it is hardened to disk on the mirror.

    http://databaseexpertise.com/sql-server/difference-between-log-shipping-and-database-mirroring/

    http://sql-articles.com/articles/high-availability/comparison-between-mirroring-vs-log-shipping-and-mirroring-vs-clustering/

    http://sqldbpool.com/2010/02/15/database-mirroring-vs-log-shipping/

    Replied on Jan 23 2012 11:52AM  . 
    Mitesh Modi
    18 · 10% · 3080
  • Score
    4

    What is database mirroring option?

    DBM is a DR solution and when synchronous DBM is implemented along with witness server,then a disaster situation can be managed efficiently.Database mirroring involves 2 server to a minimum and a third witness server according to the requirement.

    1 - Principal server which will be hosting the principal database to be mirrored 2 - Mirrored server which will be hosting the mirrored database. 3 - Witness server which will ensure automatic failover

    DBM can be either synchronous or asynchronous.Syn DBM ensures that the mirrored database is updated before an acknowledgement is sent back to the application and in Async mirroring situation a different thread will update the mirrored database.

    Async mirroring can lead to data loss and Sync mirroring can bring in network latency.So exact mirroring requirement needs to be analyzed well before implementing.

    What are the Database Mirroring enhancements done with SQL Server 2008 R2 edition?

    There is no special or extra enhancement in SQL 2008 R2 for DBM,however starting 2008 there is automatic page repair which will happen behind the scene.

    How is it different from Log shipping?

    DBM is different from log shipping in many ways,couple of important points are -

    1. LS does'nt have automatic failover options.
    2. DBM mirrored database is not available for reads/writes and its in restoring state but LS secondary can be made available only for reads.
    3. With sync mirroring we can ensure that potential data loss is zero,however with LS we have to risk data loss. 4.With LS you can have multiple secondaries,however we cannot have multiple mirrored databases for a single principal database.
    Replied on Jan 23 2012 6:30PM  . 
    Anup Warrier
    209 · 1% · 224
  • Score
    9

    Database mirroring

    Database mirroring is a primarily software solution for increasing database availability. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model. The simple and bulk-logged recovery models do not support database mirroring. Therefore, all bulk operations are always fully logged. Database mirroring works with any supported database compatibility level.

    Database mirroring maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine. Typically, these server instances reside on computers in different locations. One server instance serves the database to clients (the principal server). The other instance acts as a hot or warm standby server (the mirror server), depending on the configuration and state of the mirroring session. When a database mirroring session is synchronized, database mirroring provides a hot standby server that supports rapid failover without a loss of data from committed transactions. When the session is not synchronized, the mirror server is typically available as a warm standby server (with possible data loss).

    Benefits of Database Mirroring

    Database mirroring is a simple strategy that offers the following benefits:

    • Increases data protection.

    Database mirroring provides complete or almost complete redundancy of the data, depending on whether the operating mode is high-safety or high-performance.

    A database mirroring partner running on SQL Server 2008 Enterprise or later versions automatically tries to resolve certain types of errors that prevent reading a data page. The partner that is unable to read a page requests a fresh copy from the other partner. If this request succeeds, the unreadable page is replaced by the copy, which usually resolves the error. For more information, see Automatic Page Repair During a Database Mirroring Session.

    • Increases availability of a database.

    In the event of a disaster, in high-safety mode with automatic failover, failover quickly brings the standby copy of the database online (without data loss). In the other operating modes, the database administrator has the alternative of forcing service (with possible data loss) to the standby copy of the database.

    • Improves the availability of the production database during upgrades.

    To minimize downtime for a mirrored database, you can sequentially upgrade the instances of SQL Server that are participating in a database mirroring session. This will incur the downtime of only a single failover. This form of upgrade is known as a rolling upgrade.

    How Database Mirroring Works

    The principal and mirror servers communicate and cooperate as partners in a database mirroring session. The two partners perform complementary roles in the session: the principal role and the mirror role. At any given time, one partner performs the principal role, and the other partner performs the mirror role. Each partner is described as owning its current role. The partner that owns the principal role is known as the principal server, and its copy of the database is the current principal database. The partner that owns the mirror role is known as the mirror server, and its copy of the database is the current mirror database. When database mirroring is deployed in a production environment, the principal database is the production database.

    Database mirroring involves redoing every insert, update, and delete operation that occurs on the principal database onto the mirror database as quickly as possible. Redoing is accomplished by sending a stream of active transaction log records to the mirror server, which applies log records to the mirror database, in sequence, as quickly as possible. Unlike replication, which works at the logical level, database mirroring works at the level of the physical log record. Beginning in SQL Server 2008, the principal server compresses the stream of transaction log records before sending it to the mirror server. This log compression occurs in all mirroring sessions.

    Enhancements in Database Mirroring

    The availability of SQL Server 2008 databases is improved by enhancements to database mirroring. Database mirroring enables the creation of hot standby servers that provide rapid failover support with no loss of data from committed transactions.

    Performance enhancements include the following:

    • Write-ahead on the incoming log stream on the mirror server.

    In SQL Server 2008, when receiving incoming log records, the mirror server writes them to disk asynchronously. At the same time, the mirror server processes log records that have already been written to disk.

    • Improved use of log send buffers.

    In SQL Server 2005, every log-flush operation on the principal server reserves a whole database mirroring log send buffer for its log records. In SQL Server 2008, if the most recently used log cache contains sufficient free space for the log records of the next log-flush operation, they are appended to that log cache. Otherwise, a new log cache is allocated.

    • Compression of the stream of transaction log records.

    The principal server compresses the stream of transaction log records before sending it to the mirror server. This log compression occurs in all mirroring sessions.

    • Compression of stream data for which at least a 12.5 percent compression ratio can be achieved.

    • Page read-ahead during the undo phase.

    After a failover, the new mirror server must undo every change for which a page was written to disk locally but for which the log record might not have reached the former mirror server (the new principal server). To undo such changed pages, the mirror server must first request and receive the corresponding pages from the new principal server. Performance of this part of the undo phase is enhanced in SQL Server 2008. Early in the undo phase, the mirror server sends read-ahead hints to the principal server to indicate which pages will be requested later. After receiving a read-ahead hint for a page, the principal server puts that page in its send buffer. On receiving the corresponding page request, the principal server can respond immediately.

    Replied on Jan 27 2012 3:36AM  . 
    ATif-ullah Sheikh
    132 · 1% · 391
  • Score
    6

    DATABASE MIRRORING

    Database mirroring is a primarily software solution for increasing database availability. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model. The simple and bulk-logged recovery models do not support database mirroring. Therefore, all bulk operations are always fully logged. Database mirroring works with any supported database compatibility level.

    You cannot mirror the master, msdb, tempdb, or model databases.

    Database mirroring maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine. Typically, these server instances reside on computers in different locations. One server instance serves the database to clients (the principal server). The other instance acts as a hot or warm standby server (the mirror server), depending on the configuration and state of the mirroring session. When a database mirroring session is synchronized, database mirroring provides a hot standby server that supports rapid failover without a loss of data from committed transactions. When the session is not synchronized, the mirror server is typically available as a warm standby server (with possible data loss).



    What are the Database Mirroring enhancements

    1)     PERFORMANCE ENHANCEMENTS INCLUDE THE FOLLOWING:

    Ø  Write-ahead on the incoming log stream on the mirror server.

    In SQL Server 2008, when receiving incoming log records, the mirror server writes them to disk asynchronously. At the same time, the mirror server processes log records that have already been written to disk.

    Ø  Improved use of log send buffers.

    In SQL Server 2005, every log-flush operation on the principal server reserves a whole database mirroring log send buffer for its log records.

    In SQL Server 2008, if the most recently used log cache contains sufficient free space for the log records of the next log-flush operation, they are appended to that log cache. Otherwise, a new log cache is allocated.

    Ø  Compression of the stream of transaction log records.

    The principal server compresses the stream of transaction log records before sending it to the mirror server. This log compression occurs in all mirroring sessions.

    Ø  Compression of stream data for which at least a 12.5 percent compression ratio can be achieved.

    Ø  Page read-ahead during the undo phase.

    After a failover, the new mirror server must undo every change for which a page was written to disk locally but for which the log record might not have reached the former mirror server (the new principal server). To undo such changed pages, the mirror server must first request and receive the corresponding pages from the new principal server. Performance of this part of the undo phase is enhanced in SQL Server 2008.

    Early in the undo phase, the mirror server sends read-ahead hints to the principal server to indicate which pages will be requested later. After receiving a read-ahead hint for a page, the principal server puts that page in its send buffer. On receiving the corresponding page request, the principal server can respond immediately.

    Ø  Automatic Recovery from Corrupted Pages

    A database mirroring partner running on SQL Server 2008 or later versions automatically tries to resolve certain types of errors that prevent reading a data page. The partner that is unable to read a page requests a fresh copy from the other partner. If this request succeeds, the unreadable page is replaced by the copy, which usually resolves the error.

    Ø  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:

    1. File header pages

    2. Database boot page

    3. 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

    Ø  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


    2) USING A WITNESS IN MULTIPLE SESSIONS

    A specific server instance can act as a witness in concurrent database mirroring sessions, each for a different database. Different sessions can be with different partners.

    Ø  Sessions

    A single-server instance can also function at the same time as a witness in some sessions and a partner in other sessions. However, in practice, a server instance typically functions as either a witness or a partner. This is because the partners require sophisticated computers that have enough hardware to support a production database, whereas the witness can run on any available Windows system that supports SQL Server 2008.


    SOMETHING ABOUT LOG SHIPPING

    Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually. An optional third server instance, known as the monitor server, records the history and status of backup and restore operations and, optionally, raises alerts if these operations fail to occur as scheduled.

    Log shipping consists of three operations:

    A.  Back up the transaction log at the primary server instance.  

    B.  Copy the transaction log file to the secondary server instance.

    C.  Restore the log backup on the secondary server instance.

     

    The log can be shipped to multiple secondary server instances. In such cases, operations 2 and 3 are duplicated for each secondary server instance.

    A log shipping configuration does not automatically fail over from the primary server to the secondary server. If the primary database becomes unavailable, any of the secondary databases can be brought online manually.

     

    DATABASE MIRRORING VS LOG-SHIPPING

    DATABASE MIRRORING LOG-SHIPPING
    Database mirroring is functionality in the SQL Server engine that reads from the transaction log and copies transactions from the principal server instance to the mirror server instance. Database mirroring can operate synchronously or asynchronously. Log shipping is based on SQL Server Agent jobs that periodically take log backups of the primary database, copy the backup files to one or more secondary server instances, and restore the backups into the secondary database(s). Log shipping supports an unlimited number of secondary’s for each primary database.
    Database mirroring can operate synchronously or asynchronously. If configured to operate synchronously, the transaction on the principal will not be committed until it is hardened to disk on the mirror. Log shipping is always asynchrony. Log shipping totally depends on the log backup and restore schedule.
    Database mirroring supports only one mirror for each principal database. That means DB mirroring is at database level. Log-shipping can work on database and server level. You can configure multiple databases in logshipping.
    Data Transfer: Individual T-Log records are transferred using TCP endpoints Data Transfer: T-Logs are backed up and transferred to secondary server
    Transactional Consistency: Only committed transactions are transferred Transactional Consistency: All committed and un-committed are transferred
    Server Limitation: Can be applied to only one mirror server Server Limitation: Can be applied to multiple stand-by servers
    Failover: it is Automatic Failover: it is Manual
    Failover Duration: Failover is fast, sometimes < 3 seconds but not more than 10 seconds. Failover Duration: Can take more than 30 mins.
    Role Change: Role change is fully automatic Role Change: -Role change is manual
    Client Re-direction: Fully automatic as it uses .NET 2.0/.Net 3.0 Client Re-direction: Manual changes required.
    Mirroring Support only full recovery model Log-shipping Supports full and bulk-logged recovery model
    Mirror database is always in recovery mode. To read it you have use database snapshot You can use the stand-by option to read the database on standby server
    Auto Page Recovery introduced with SQL SERVER 2008 so it will recover the damaged pages. Not supported
    it is possible to introduce a fixed delay when applying logs to allow the secondary to be used for recovering from user error and To use database mirroring on a database, the full recovery model is always required. Not supported

    Replied on Feb 20 2012 7:50AM  . 
    Yogesh Kamble
    142 · 1% · 349

Your Answer


Sign Up or Login to post an answer.
Please note that your answer will not be considered as part of the contest because the competition is over. You can still post an answer to share your knowledge with the community.

Copyright © Rivera Informatic Private Ltd.