locked
Initial Database coalescing for Mirroring RRS feed

  • Question

  • We have a decent size db (about 60 GB) but a small pipe between the two servers we're wanting to mirror (9Mb/s).  While this pipe would be fine for our transactional load to have mirroring in place, we're having difficulty on how to actually get mirroring started. 

     

    Whether we try compression and file copy or if we try to actually ship the media to our offsite location, either way we would have to modify our production backup schedule to copy over the backups. 

     

    Is it possible to utilize tx replication or some other method for copying the databases to be able to set up mirroring or is backup/restore the only way to do it?  If backup/restore is the only way, any recommendations?

     

    Thanks,

     

    --Aaron

    Thursday, September 11, 2008 3:12 PM

Answers

  •  

    Nope, reason we need to have a secondry database with no recovery mode equaly same size.

    I hope you got what i would like to say.

     

    BR
    Praveen Barath

    Mark as answer if it helps

    Monday, September 15, 2008 6:02 PM
    Answerer

All replies

  •  

    There is no as such specified network is mentioned or recommended, but the rough idea is to calculate the network bandwidth is has to three times of  Transaction log rates.

     

    If you would like to Ship your backup copy, you can do over network, and if you have time constraint then your option seems to be better.

     

    There is option in logship /Replication where you can backup and restore by that window but that seems to be very  slow.its better to backup and restore with no recovery option. Then try to configure.

     

    BR

    Praveen Barath

    Mark as answer if it helps

    Saturday, September 13, 2008 5:13 PM
    Answerer
  • Thanks for the response, however the crux of the question is basically:

    Is there another way to start sql database mirroring without doing a database backup/restore?  If so, how?

     

    Thanks again,

     

    Monday, September 15, 2008 1:16 PM
  •  

    Nope, reason we need to have a secondry database with no recovery mode equaly same size.

    I hope you got what i would like to say.

     

    BR
    Praveen Barath

    Mark as answer if it helps

    Monday, September 15, 2008 6:02 PM
    Answerer
  • Just in case anyone else was wondering, here's the POC and results I tried:

     

    All on a single VPC
    Windows 2003 R2 SP2
    SQL 2005 SP2
    Used AdventureWorks Sample Database
    No transactions were performed

    Instances:
    WIN2003VPC - principal/publisher
    WIN2003VPC\Witness - witness/distributor
    WIN2003VPC\Failover - mirror/subscriber

    Process:
    1.) Set up distributor
    2.) Set up publication
    3.) set up subscriber
    4.) Replicate
    5.) delete publication
    6.) delete subscription
    7.) backup subscriber db
    8.) restore subscriber db with no recovery
    9.) Start mirroring
    ***ERROR***
    Database mirroring cannot be enabled because the "AdventureWorks" database may have bulk logged changes that have not been backed up. The last log backup on the principal must be restored on the mirror.

    10.) backup publisher db
    11.) Start mirroring
    ***ERROR***
    The remote copy of database "AdventureWorks" is not related to the local copy of the database.

    12.) backup publisher db tx log
    13.) restore publisher db tx log with no recovery
    ***ERROR***
    The backup set holds a backup of a database other than the existing 'AdventureWorks' database.

     

     

    Thursday, September 18, 2008 3:09 PM
  • Was there any resolution to this?  We're having the same problem.
    Tuesday, February 10, 2009 4:01 PM