SQL Server Performance

Database Mirror - how to rebuild if one node is rebuilt?

Discussion in 'ALL SQL SERVER QUESTIONS' started by Trev256b, Apr 5, 2013.

  1. Trev256b Member

    I am rebuilding one node which usually has the principle database on. This means I have to re-install SQL and setup Database mirroring. The database is running on another server (usually the mirror server). What steps should i take to get this back up online?
  2. Shehap MVP, MCTS, MCITP SQL Server

    As per my understanding , you need to know the steps of re-building a new DB node participating within a mirroring solution , if so you should drop DB mirroring established currently using either Mirroring wizard
    or using the below script :

    ALTER DATABASE database_name SET PARTNER OFF

    Then you need to re-build DB mirroring using the same usual steps as follow:

    · Taking Full backup and transaction log back up for principal DB
    · Restoring it under the other mirror server using non-recovery option
    · Configuring mirroring between the 2 servers using Mirroring wizard with selecting the appropriate options (High Safety with automatic failover option is much recommended but needs for another 3rd server to act as witness server)

    Kindly let me know if it is your case or not ..? If not please elaborate more
  3. Trev256b Member

    Hi Shehap. Thanks for your reply. I have a witness setup. The database is running on the new principle (was mirror before server crash). Can you answer the following please?
    1) How is the principle-mirror relationship exactly established? Is it by running the SET PARTNER command on all three databases (mirror, principle and witness in that order)? If so how does the principle know what transactions to pass over since the last backup that was restored on the mirror server?
    2a) Am I correct in assuming I can't restore the database on the new mirror (old principle) and establish principle-mirror relationship without dropping the mirror relationship?
    2b) What stops me from just restoring the database and re-establishing the mirror relationship by creating and connecting endpoints on the principle and setting partner on the principle? Will there be too many transactions logged for transfer or doe the database mirror somehow not log the transactions correctly?
  4. Shehap MVP, MCTS, MCITP SQL Server

    1-To know how is the principal mirror relationship established using a witness server , it needs for multiple steps to build DB mirroring of the highest safety with automatic failover and you can learn more about it at :
    http://technet.microsoft.com/en-us/library/ms365603.aspx

    and also more details about the role of witness server at

    http://technet.microsoft.com/en-us/library/ms175191.aspx

    2-You should drop partnership with the mirror (old principal) to be able to restore DB because actually DB will be in use and you will not be able to restore it unless stopping this partnership

    3. Creating endpoints only is not enough to build a DB Mirror solution but the other steps shown above are necessarily to define the relationship between these endpoints and functionality of each one either it is principal, Mirror or witness
    Besides of the sequence of transaction log backup and restore from the principal and mirror , you can read more about it at http://msdn.microsoft.com/en-us/library/ms190941.aspx

    Kindly let me know if any further help is needed

Share This Page