none
Database Mirroring RRS feed

  • Pergunta

  • Olá,

     

    Em que cenário do Database Mirroring operation mode High Availability um dos Banco fica com status Isolated e não pode ser usado, Cannot Server Db?Porque?

    Tenho dificuldade em entender o porque disso.

     

    Grato,

     

    Maurício

     

    • Movido Gustavo Maia Aguiar segunda-feira, 28 de fevereiro de 2011 16:37 (De:SQL Server - Infra Geral)
    segunda-feira, 28 de fevereiro de 2011 16:28

Respostas

  • Maurìcio, boa tarde!

     

    No espelhamento de banco de dados ou database mirroring, independente do modo de operação que você deseja, o banco de dados espelho sempre ficará sem acesso, mais porque isso? Bem, na verdade o banco de dados espelho, sempre estará esperando  o log de transação do principal para aplicadar no espelho, dependendo do modo de operação que você coloca no database mirroring ele te dirá se o log será aplicado em modo síncrono ou não síncrono, no caso de  High Availability o modo é sincrono, então para uma operação poder ser commitada, deverá ser aplicada nos dois servidores, só assim a transação será completamente feita!

    O que você pode fazer para que os usuários do banco de dados espelho possam ter acesso de leitura no banco, é criar um database snapshot ou seja uma foto do banco de dados espelho, lembrando que o database snapshot só pode ser feito na versão enterprise do SQL Server.

    Agora se você deseja que esse banco de dados "espelho" seja um banco de dados somente de leitura, você pode usar o log shipping que é outro modo de alta disponibilidade que o SQL Server apresenta.


    Att,

     

    Qualquer dúvida pergunte!

     


    Luan.Moreno MCP || MTA ||MCTS SQL Server 2005 Blog: http://luanmorenodba.wordpress.com Twitter: @luansql Classifique as respostas. O seu feedback é imprescindível
    segunda-feira, 28 de fevereiro de 2011 16:52
  • MauMauBoy,

    The mirroring state has a direct impact on the availability of a database. You must monitor the mirroring situation closely and take appropriate action if the principal or the mirror databases become exposed or isolated, or if either server is unable to serve its database. Generally, the following three situations pose a risk to database availability and should be dealt with immediately:

    • The principal server cannot serve the database. In some cases, the principal server does not allow any user connections to the database or any transactions to be processed. This could be caused by the failure of either the principal server or the principal database. When the database server is operating in high-availability mode and the mirror server is able to form a quorum with the witness server, automatic failover will occur. When operating in high-protection mode, you must initiate manual failover. When operating in high-performance mode, you can also initiate forced service. However, it is possible that some committed transactions will be lost.
    • The data on the principal server is exposed. In some cases, the principal database processes transactions, but no log data is sent to the mirror server. This could be caused by the failure of the mirror server, a breakdown in network communication between the principal and mirror servers, or configuration issues.
    • A server is isolated. In some cases, a server may be operational, but the communication lines between it and both the other servers in the database mirroring session may be broken. When a witness server has been configured for the mirroring session and the principal server cannot contact either the witness server or the mirror server, the principal server will stop serving the database. The principal server will record its state as isolated when there is no server in the session with which it can form a quorum.
    Da uma lida neste link e veja se te ajuda: http://www.extremeexperts.com/sql/articles/DBMirroring2.aspx


    ------------------------------------------------------------- Oracle OCA11g
    segunda-feira, 28 de fevereiro de 2011 16:54
    Moderador
  • Olá,

    Só pra complementar:

     

    Database mirroring requires multiple instances of SQL Server, which should be installed on separate computers to provide protection from server failure. The server roles in a database mirroring solution are:

    • Principal server. The principal server hosts the active copy of the database (referred to as the principal database) and services client requests. The principal server forwards all transactions to the mirror server before it applies them in the principal database.
    • Mirror server. The mirror server hosts a copy of the principal database (referred to as the mirror database) and applies the transactions forwarded by the principal database to keep the mirror database synchronized with the principal database.
    • Witness server. The witness server is an optional component of a database mirroring solution. When present, a witness server monitors the principal and mirror servers to ensure continued connectivity and participation in the mirror session (referred to as quorum). If either server loses quorum, the witness server assigns the principal server role, causing automatic failover from the principal server to the mirror server if necessary. A witness server is required for automatic failover; however, one witness server can support several mirror sessions because it is not an intensive job.

     

     

    There are three modes of database mirroring. The level of performance and protection differ between modes and care should be taken to provide the appropriate solution for your system.

     

    High Availability Mode

    In High Availability mode, you set transaction safety to FULL, causing transactions to be applied to the principal and mirror databases synchronously. High Availability mode uses a witness server. This should be placed on a third server (not the principal or mirror) to provide redundancy.

     

    High Protection Mode

    In High Protection mode, you set transaction safety to FULL to apply transactions synchronously, as occurs in High Availability mode. However, High Protection mode does not use a witness server.

     

    High Performance Mode

    In High Performance mode, transaction safety is set to OFF and transactions are applied asynchronously, causing the principal server to respond to clients without first checking that the transactions have been applied on the mirror server. This gives better performance, but sacrifices high availability.

     

    Related Articles:
    Database Mirroring Witness", http://go.microsoft.com/fwlink/?LinkId=131101 
    Asynchronous Database Mirroring (High-Performance Mode)", http://go.microsoft.com/fwlink/?LinkId=131103

    Synchronous Database Mirroring (High-Safety Mode)", http://go.microsoft.com/fwlink/?LinkId=131104

    Database Mirroring Overview: http://go.microsoft.com/fwlink/?LinkId=131100

    Database Mirroring Endpoint: http://go.microsoft.com/fwlink/?LinkId=131105
    Setting Up Database Mirroring: http://go.microsoft.com/fwlink/?LinkId=131106

     

    Espero que ajude!!!!


    Fábio Oliveira Support Engieer | Microsoft Enterprise and Developer Support
    segunda-feira, 28 de fevereiro de 2011 19:53

Todas as Respostas

  • Maurìcio, boa tarde!

     

    No espelhamento de banco de dados ou database mirroring, independente do modo de operação que você deseja, o banco de dados espelho sempre ficará sem acesso, mais porque isso? Bem, na verdade o banco de dados espelho, sempre estará esperando  o log de transação do principal para aplicadar no espelho, dependendo do modo de operação que você coloca no database mirroring ele te dirá se o log será aplicado em modo síncrono ou não síncrono, no caso de  High Availability o modo é sincrono, então para uma operação poder ser commitada, deverá ser aplicada nos dois servidores, só assim a transação será completamente feita!

    O que você pode fazer para que os usuários do banco de dados espelho possam ter acesso de leitura no banco, é criar um database snapshot ou seja uma foto do banco de dados espelho, lembrando que o database snapshot só pode ser feito na versão enterprise do SQL Server.

    Agora se você deseja que esse banco de dados "espelho" seja um banco de dados somente de leitura, você pode usar o log shipping que é outro modo de alta disponibilidade que o SQL Server apresenta.


    Att,

     

    Qualquer dúvida pergunte!

     


    Luan.Moreno MCP || MTA ||MCTS SQL Server 2005 Blog: http://luanmorenodba.wordpress.com Twitter: @luansql Classifique as respostas. O seu feedback é imprescindível
    segunda-feira, 28 de fevereiro de 2011 16:52
  • MauMauBoy,

    The mirroring state has a direct impact on the availability of a database. You must monitor the mirroring situation closely and take appropriate action if the principal or the mirror databases become exposed or isolated, or if either server is unable to serve its database. Generally, the following three situations pose a risk to database availability and should be dealt with immediately:

    • The principal server cannot serve the database. In some cases, the principal server does not allow any user connections to the database or any transactions to be processed. This could be caused by the failure of either the principal server or the principal database. When the database server is operating in high-availability mode and the mirror server is able to form a quorum with the witness server, automatic failover will occur. When operating in high-protection mode, you must initiate manual failover. When operating in high-performance mode, you can also initiate forced service. However, it is possible that some committed transactions will be lost.
    • The data on the principal server is exposed. In some cases, the principal database processes transactions, but no log data is sent to the mirror server. This could be caused by the failure of the mirror server, a breakdown in network communication between the principal and mirror servers, or configuration issues.
    • A server is isolated. In some cases, a server may be operational, but the communication lines between it and both the other servers in the database mirroring session may be broken. When a witness server has been configured for the mirroring session and the principal server cannot contact either the witness server or the mirror server, the principal server will stop serving the database. The principal server will record its state as isolated when there is no server in the session with which it can form a quorum.
    Da uma lida neste link e veja se te ajuda: http://www.extremeexperts.com/sql/articles/DBMirroring2.aspx


    ------------------------------------------------------------- Oracle OCA11g
    segunda-feira, 28 de fevereiro de 2011 16:54
    Moderador
  • Olá,

    Só pra complementar:

     

    Database mirroring requires multiple instances of SQL Server, which should be installed on separate computers to provide protection from server failure. The server roles in a database mirroring solution are:

    • Principal server. The principal server hosts the active copy of the database (referred to as the principal database) and services client requests. The principal server forwards all transactions to the mirror server before it applies them in the principal database.
    • Mirror server. The mirror server hosts a copy of the principal database (referred to as the mirror database) and applies the transactions forwarded by the principal database to keep the mirror database synchronized with the principal database.
    • Witness server. The witness server is an optional component of a database mirroring solution. When present, a witness server monitors the principal and mirror servers to ensure continued connectivity and participation in the mirror session (referred to as quorum). If either server loses quorum, the witness server assigns the principal server role, causing automatic failover from the principal server to the mirror server if necessary. A witness server is required for automatic failover; however, one witness server can support several mirror sessions because it is not an intensive job.

     

     

    There are three modes of database mirroring. The level of performance and protection differ between modes and care should be taken to provide the appropriate solution for your system.

     

    High Availability Mode

    In High Availability mode, you set transaction safety to FULL, causing transactions to be applied to the principal and mirror databases synchronously. High Availability mode uses a witness server. This should be placed on a third server (not the principal or mirror) to provide redundancy.

     

    High Protection Mode

    In High Protection mode, you set transaction safety to FULL to apply transactions synchronously, as occurs in High Availability mode. However, High Protection mode does not use a witness server.

     

    High Performance Mode

    In High Performance mode, transaction safety is set to OFF and transactions are applied asynchronously, causing the principal server to respond to clients without first checking that the transactions have been applied on the mirror server. This gives better performance, but sacrifices high availability.

     

    Related Articles:
    Database Mirroring Witness", http://go.microsoft.com/fwlink/?LinkId=131101 
    Asynchronous Database Mirroring (High-Performance Mode)", http://go.microsoft.com/fwlink/?LinkId=131103

    Synchronous Database Mirroring (High-Safety Mode)", http://go.microsoft.com/fwlink/?LinkId=131104

    Database Mirroring Overview: http://go.microsoft.com/fwlink/?LinkId=131100

    Database Mirroring Endpoint: http://go.microsoft.com/fwlink/?LinkId=131105
    Setting Up Database Mirroring: http://go.microsoft.com/fwlink/?LinkId=131106

     

    Espero que ajude!!!!


    Fábio Oliveira Support Engieer | Microsoft Enterprise and Developer Support
    segunda-feira, 28 de fevereiro de 2011 19:53
  • Fábio,

     

    Parabéns, sempre postando bastantes link's para o pessoal, você está completamente certo, vou passar a fazer mais isso!

     

     

    Obrigado pela complementação fundamental.!!


    Luan.Moreno MCP || MTA ||MCTS SQL Server 2005 Blog: http://luanmorenodba.wordpress.com Twitter: @luansql Classifique as respostas. O seu feedback é imprescindível
    segunda-feira, 28 de fevereiro de 2011 21:18