locked
Mirror Setup RRS feed

  • Question

  • Hello,

     

    I'm trying to setup database mirroring between 3 servers and the details are as follows:

     

    1. All 3 machines reside in the same domain.

    2. Server1 consists of SQL 2005 Ent Edition without SSAS, Server 2 consists of SQL 2005 Ent Ed without SSAS & SSRS, Server 3 consists of SQL 2005 Express Ed.

    3. All the 3 machines are having their individual service domain accounts.

    4. Server 1 consists of 5 databases which needs to be mirrored to Server 2. I took full backups and tlog backups and restored the full backups in NO Recovery mode on Server 2 and then applied the tlog backups to sync them with Server 1.

    5. I created the logins on Server 1 (Server 2 & 3), Server 2 (Server 1 & 3) and Server 3 (Server 1 & 2) and gave "sa" permissions respectively.

    6. I kept the first database on Server 1 to full recovery mode, created an endpoint with the default port and granted connection to Server 2 & 3 logins.

    7. I created an endpoint on Server 2 with the default port number and granted connection to Server 1 & 3 logins.

    8. I created an endpoint on Server 3 with the default port number and granted connection to Server 1 & 2 logins.

    9. On Server 2 I'm executing this statement

     

         ALTER DATABASE <dbname>

           SET PARTNER = 'TCP://<Server Network Address of Server 1>:<Port No>'

     

    10. On Server 1 I'm executing this statement

     

          ALTER DATABASE <dbname>

            SET PARTNER = 'TCP://<Server Network Address of Server 2>:<Port No>'

     

          ALTER DATABASE <dbname>

            SET WITNESS = 'TCP://<Server Network Address of Server 3>:<Port No>'

     

    On Step 9 & 10, I'm getting an error message "Msg 1416, Severity 16, State 2 Database <dbname> is not configured for mirroring" in the query window but when I see the error logs individually on Server 1 & 2 I'm seeing "Error: 1443, Severity: 16, State: 2. Database mirroring has been terminated for database 'dbname'. This is an informational message only. No user action is required."

     

    I would appreciate if any one can look into the problem I'm facing. I've googled and could not come up with a solution.

     

    Thanks

     

    Wednesday, July 11, 2007 1:00 PM

Answers

  • refer this,

    On the mirror server

    ===============

     

    Restored the full backup by keeping the database in No Recovery mode

    Restored the first tlog backup by keeping the database in No Recovery mode

    Restored the second log backup by keeping the database in Recovery mode ---> this has to be in norecovery mode but u have restored it in recover mode other steps are fine

    Thursday, July 12, 2007 12:55 PM

All replies

  • I just have one question, did you restore the tran logs from principal to mirror using with norecovery option???.......coz you need to restore atleast 1tran log in mirror with norecovery option prior to configuring mirroring......
    Wednesday, July 11, 2007 1:30 PM
  • Hello Deepak,

     

    I have restored 2 sets of tlog backups from principal to mirror -- first with norecovery mode and the second one with recovery mode. Still I'm getting the same errors.

     

    Query Window

    ===========

    Msg 1416, Level 16, State 31, Line 3

    Database " " is not configured for database mirroring.

     

    Error Log

    =======

    Error: 1443, Severity: 16, State: 2.

    Database mirroring has been terminated for database ' '. This is an informational message only. No user action is required.

     

    Do you have any other workaround for this problem?

     

    Thanks

     

    Wednesday, July 11, 2007 2:45 PM
  • Hello Deepak,

     

    I have even tried with a sample database on the same set of servers and encountering the same set of errors in query window and error log. Is it something to do with any sort of configuration which I need to do before implementing database mirroring?

     

    Thanks

     

    Wednesday, July 11, 2007 3:22 PM
  • here is the mistake,

    1. you should restore the full backup in the mirror using with norecovery clause

    2.you need to restore one tran log in mirror using with norecovery clause

    the idea behind restoring the full backups and tran log backups using with norecovery is that you can apply further transaction logs

    since you have applied one with norecovey and other with recovery option further tran logs cannot be applied in the mirror...that seems to be the error.........perform the 2 steps as above and then start mirroring.....

     

    Wednesday, July 11, 2007 3:32 PM
  • Hello Deepak,

     

    I took 1 full backup and 2 tlog backups from the principal server.

     

    On the mirror server

    ===============

     

    Restored the full backup by keeping the database in No Recovery mode

    Restored the first tlog backup by keeping the database in No Recovery mode

    Restored the second log backup by keeping the database in Recovery mode

     

    These are the steps I have initiated to configure database mirroring. I have even tested with a sample database following the above steps and still encounter the same error messages.

     

    Thanks

     

    Wednesday, July 11, 2007 5:48 PM
  • abybody out there who can troubleshoot my problem????

     

    TIA

    Wednesday, July 11, 2007 8:56 PM
  • well as i said earlier you need to restore the full and subsequent tran log backups in mirror using with norecovery option.

    this step has to be performed prior to configuring mirroring....further adding to your problem the error says, that the "database is not configured for mirroring".....which is a clear indication that either your mirror is not restored with norecovery or it is not in Full Recovery i believe.....this is what i know.....

    Thursday, July 12, 2007 12:54 AM
  •  

    Hello Deepak,

     

    It seems you haven't gone through my earlier messages and here it is

     

    I took 1 full backup and 2 tlog backups from the principal server.

     

    On the mirror server

    ===============

     

    Restored the full backup by keeping the database in No Recovery mode

    Restored the first tlog backup by keeping the database in No Recovery mode

    Restored the second log backup by keeping the database in Recovery mode

     

    These are the steps I have initiated to configure database mirroring. I have even tested with a sample database following the above steps and still encounter the same error messages.

     

    Thanks

    Thursday, July 12, 2007 12:47 PM
  • refer this,

    On the mirror server

    ===============

     

    Restored the full backup by keeping the database in No Recovery mode

    Restored the first tlog backup by keeping the database in No Recovery mode

    Restored the second log backup by keeping the database in Recovery mode ---> this has to be in norecovery mode but u have restored it in recover mode other steps are fine

    Thursday, July 12, 2007 12:55 PM
  • Hello Deepak,

     

    Thanks for pointing the problem and I could setup mirroring successfully..

    Thursday, July 12, 2007 8:26 PM
  • Just so everyone knows, i have had the same problems over and over trying to do this...  and I have hit my self in the head.  

    Apply SQL server 2005 SP3 Patch, problem was solved.  Worked instantly.  I still have the -T1400 startup flag for tracing, not sure if that is still needed.

    Hope this helps everyone else.

    Mac
    Friday, June 12, 2009 1:57 PM
  • Just so everyone knows, i have had the same problems over and over trying to do this... and I have hit my self in the head.

    Apply SQL server 2005 SP3 Patch, problem was solved. Worked instantly. I still have the -T1400 startup flag for tracing, not sure if that is still needed.

    Hope this helps everyone else.

    Mac

    I'll follow your instruction, Thanks for your message
    Wednesday, February 16, 2011 12:00 AM