locked
Database Mirroring in SQL 2008 R2 64 bit error 1404 RRS feed

  • Question

  • Dear Folks,

    I have been trying to configure database mirroring for one of our application.

    I am using Windows 2008 Standard Server 64 bit and SQL server 2008 R2 64bit

    I need to configure a database mirroring with High Avaiability.

    I have one Primary Server,Secondary Server and a Witness Server.

    I have used the same SQL accross this 3 machine, and using the same username and password under the domain for all of them.

    The service account is also the same for all of them.

    I have configured the EDNPOINT for all of them, and i am able to connect to the database engine of all 3 server from every individual server.

    I followed the process as stated below:-

    1. Primary Server - Create a full backup from Primary and add the transaction log backup to it.
    2. Secondary Server - Disable the database protection and delete the database.
    3. Secondary Server - Restore the backup from Primary selecting both database and log.
    4. Secondary Server - Under the Option select RESTORE without RECOVERY.
    5. Secondary Server - The restoration completed with Successfull message.
    6. Secondary Server - The status still show that it in RESTORING.
    7. Secondary Server - Enable the database prortection - through error (Database  "StarWatch" cannot be opened. It is in the middle of a restore.)
    8. Primary Server - Expand the Database and right click the "StarWatch" select mirorring
    9. Primary Server - Configure the Security and connect to the Secondary and Witness.
    10. Primary Server - Configure Endpoints = Success
    11. Primary Server - Star Mirroring
    12. Primary Server - ERROR =

    AN error occurred while starting mirroring

    Additional information:

     Alter failed for Database 'StarWatch'. (Microsoft.SqlServer.Smo)

      An expection occurred while executing a Transact-SQL statement or batch.(Microsoft.SqlServer.ConnectionInfo)

       The command failed because the database mirror is busy, Reissue the command later. (MicrosoftSQL Server, Error:1404)

    I have tried by redoing the above process many times and get the same error message, i have event remove the SQL from the secondary and tried again but still have the same error.

    Someone please advise a solution for this error.

    Thank you

     

    • Changed type Tom Phillips Tuesday, August 14, 2012 1:21 PM
    • Moved by Tom Phillips Tuesday, August 14, 2012 1:21 PM Database mirroring question (From:SQL Server Database Engine)
    Tuesday, August 14, 2012 3:28 AM

Answers

  • Hi,

    I'm a bit confused on some terms you are using. Here are the steps you need to do:

    1. create mirroring endpoints on all 3 servers: principal, mirror, witness
    2. grant connect to the service account on all three instances,
    3. Backup the database and the tr log on your principal server,
    4. copy the backup to the mirror server,
    5. restore the database and tr log on the mirror server with NORECOVERY,
    6. use ALTER DATABASE SET PARTNER on the principal, mirror
    7. use ALTER DATABSE SET WITNESS to add your witness instance.

    Here is a guide for mirroring here: http://msdn.microsoft.com/en-us/library/ms189852.aspx

    I hope it helps.

    Janos


    There are 10 type of people. Those who understand binary and those who do not.

    My Blog | Hire Me

    • Proposed as answer by PrinceLucifer Tuesday, August 14, 2012 4:39 PM
    • Marked as answer by Maggie Luo Tuesday, August 21, 2012 5:38 AM
    Tuesday, August 14, 2012 12:45 PM