Results 1 to 15 of 15

Thread: SQl Failover

  1. #1
    Join Date
    Jul 2011
    Posts
    12

    Unanswered: SQl Failover

    Hi all, i'm following the steps below on the mirroring and I'm stuck on
    STEP 2 - Restore Database on Secondary Database (Mirror Server).

    I have 3 VMs as per the instruction and not sure why i get the error "Msg 3201, Level 16, State 2, line 1
    Cannot open backup device 'D:\backup.bak' Operating System error 3
    Msg 3013, Level 16, State 1, line 1
    RESTORE DATABASE is terminating abnormally."

    On step 2 I put it on Mirror VM, it mention about restoring from the VM local drive. I don't quite understand how does it link to the Principal VM local drive ??


    How to Setup Mirroring in SQL Server ?(Screen Shots) | SQL Server Training

  2. #2
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    You'll either have to copy the backup file to your mirror server, or you'll have to share a network drive holding the backup file. In the latter case, keep in mind that SQL Server service account need to have access to the file share and the backup file.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  3. #3
    Join Date
    Jul 2011
    Posts
    12
    Oh thanks .. in this case if i manually copy it over, when there's a downtime how does the auto failover works ?

  4. #4
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    You only use backup/restore when you configure database mirroring. When it is set up, it uses a technology similar to Service Broker to exchange changes to the database, so you don't have to worry about the backups during failover.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  5. #5
    Join Date
    Jul 2011
    Posts
    12
    I got this error when trying to configure Step 2. Where can setup this log file ? I'm so sorry but my SQL knowledge is low. I'm just learning ...

    Msg 3234, Level 16, State 2, Line 1
    Logical file 'MyDatabase_log' is not part of database 'MyDatabase'. Use RESTORE FILELISTONLY to list logical file names.
    Msg 3013, Level 16, State 1, Line1
    RESTORE DATABASE is terminating abnormally

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You presumably used the MOVE option in your restore command to try to move the file 'MyDatabase_log'. Unfortunatley, that file is not known to this database. Check the spelling of the logical name of the file, and see if there is anything obvious.

  7. #7
    Join Date
    Jul 2011
    Posts
    12
    how can i create this file or can i remove it from the script ?

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Find the restore command in your script, and examine the MOVE statements.

  9. #9
    Join Date
    Jul 2011
    Posts
    12
    I manage to get the Mirroring work but on Principal Server it's showing DATABASE (Principal, Synchronized) but for Mirror Server it's showing DATABASE (Mirror, Synchronized / Restoring).

    It shouldn't be this way right ?

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    No, that is correct. The database is in a restoring state, while the logs are being sent from the primary to the mirror server.

  11. #11
    Join Date
    Jul 2011
    Posts
    12
    Thanks a lot. Tomorrow I will try to shut down the principal to see if the failover works.

    If the failover is sucessful, once the principal server is up again. Will the main database switch back to principal server ?

  12. #12
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    No, it will remain on the secondary server. Failing back would introduce a small amount of downtime. Think of it, if the "primary" server get some error causing it to reboot (blue screen for instance), would you fail back and have the risk of it happening again, or having it running on your "secondary" server? I would definitely want it to run on the "secondary" server. The same logic applies to clusters, where I may have a preferred node for an instance, but I NEVER configure failback.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  13. #13
    Join Date
    Jul 2011
    Posts
    12
    You have a point there so I guess the scenario is like this

    Server1 - Principal
    Server2 - Mirror
    Server3 - Witness

    Principal server has blue screen and database swing over to Mirror Server

    Server1 - Under repair / troubleshooting
    Server2 - Principal Server
    Server3 - Witness

    Then Server1 has been repair or replaced. I have to copy the database from Server2 back to Server1 and start the Mirroring again ?

  14. #14
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    If the server is replaced: definitely. If the same server is coming up again: As long as you don't have to reinstall SQL Server or restore the databases, AND you did not have to stop mirroring due to excessive transaction log growth, then the server should catch up when it is getting online again.

    Keep in mind, while one of the servers is down, the VLFs (Virtual Log Files) within your transaction log cannot be freed, eventually causing your log to grow. You can see this by
    Code:
    select name, log_reuse_wait_desc from sys.databases
    Where log_reuse_wait_desc is DATABASE_MIRRORING if database mirroring prevents SQL Server from freeing up space (VLFs) in your log file.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  15. #15
    Join Date
    Jul 2011
    Posts
    12
    oh i see .. We did try to successfully swing it over and I do have a concern that for SQL failover only swing over the database and the log files without the software.

    Some of the software could not start on the mirror server and we think the file in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\master.mdf may not be sync. Is there a solution for this ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •