locked
SQL 2008 mirroring RRS feed

  • Question

  • Hi

    I have a production site with 3 servers(VMs).

    DMZ server

    Application server

    DB server

    These 3 servers have been cloned to our DR site

    The db server (10.100.7.98) is running SQL 2008 enterprise and contains 6 databases, 2 per environment (prod, DEV, UAT).  The cloned DR DB server is identical except for IP address (10.102.1.12) and server name.

     

    When I tried setting up mirroring on the UAT DB as a test run, I found once I backed up the UAT db and tried restoring it on the DR DB server with norecovery mode, for some reason when I flicked back  to the production DB server the same DB was also in recovery mode!

    Why is this ?  is it because the same SQL instance is running on both servers ? and if so, how do I fix this?

     

    I'm only attempting to mirror the prod dbs on the DR server, the remaining DBs will be removed.

     

    Thanks in Advance

    Thursday, February 24, 2011 12:35 AM

Answers

  • Only that it is not a DR server is it? If one change to one occurs on the other then what you have been given is pretty useless for any purpose. Your best bet is to go back to your Techie and simply ask them for a new VM machine with SQL on it. DB mirroring requires SQL Enterprise edition and doesnt require a "cloned" VM. The process is :-

     

    Backup source server source db

    Backup log (I usually take a few) of source server sourcedb

    Restore to destination server in with no recovery

    Apply logs to destination server with no recovery

    From Source server source db run through mirroring wizard through SSMS (although you can also do this in TSQL).


    Regards,
    Mark Broadbent.

    Contact me through (twitter|blog)

    Please click "Propose As Answer" if a post solves your problem
    or "Vote As Helpful" if a post has been useful to you
    • Marked as answer by unikoman Saturday, February 26, 2011 5:22 AM
    Friday, February 25, 2011 1:12 PM

All replies

  • Can you first confirm that the "clones" are absolutely not the one and the same VMs OR that the synchronization is not two way?

    For instance create a DB on one (A), does it come through to the other (B)?

    Delete this DB now on (B) is it gone from (A)?

    If the answer to both of these questions is yes then I think you will see what is happening when you restore to create your "mirrors".

    Finally to prove whether they are one and the same, shut the server (A) down, is it available on the other side?

    BTW I know you have said they are different IP addresses and names but I want to make sure that they are different first since the IP addr could be misleading in certain situations.


    Regards,
    Mark Broadbent.

    Contact me through (twitter |blog )

    Please click "Propose As Answer " if a post solves your problem
    or "Vote As Helpful " if a post has been useful to you
    • Proposed as answer by Bradley Ball Friday, February 25, 2011 2:55 PM
    Thursday, February 24, 2011 1:50 AM
  • Hi Mark,

     

    First of all thanks for your reply.

    I carried out your scenarios and indeed the answer is yes to both questions.

    As for the third I will test that tonight out of business hours and post the result.

     

    Thanks

    Thursday, February 24, 2011 3:56 AM
  • Cool, I'll await the result :)

    Regards,
    Mark Broadbent.

    Contact me through (twitter|blog)

    Please click "Propose As Answer" if a post solves your problem
    or "Vote As Helpful" if a post has been useful to you
    Thursday, February 24, 2011 8:05 AM
  • Hi Mark

    I stopped the SQL instance on (A) and checked SQL instance on (B) and it too stopped.  Starting it again started both instances!

    So both servers are the one and the same , as you mentioned previously.........

    Thanks

     

     

    Thursday, February 24, 2011 8:38 AM
  • Glad I could help :)

    Regards,
    Mark Broadbent.

    Contact me through (twitter|blog)

    Please click "Propose As Answer" if a post solves your problem
    or "Vote As Helpful" if a post has been useful to you
    Thursday, February 24, 2011 10:02 AM
  • Thanks.

    How do I stop this situation in order to set up mirroring? From my understanding this is happening because the VM cloning wasn't done correctly ?

    The way the set up is now I cannot get rid of the non prod dbs on the DR server (B) as it will get rid of the same DBs on the prod box(A)

    Thanks in advance

    Thursday, February 24, 2011 10:41 AM
  • Only that it is not a DR server is it? If one change to one occurs on the other then what you have been given is pretty useless for any purpose. Your best bet is to go back to your Techie and simply ask them for a new VM machine with SQL on it. DB mirroring requires SQL Enterprise edition and doesnt require a "cloned" VM. The process is :-

     

    Backup source server source db

    Backup log (I usually take a few) of source server sourcedb

    Restore to destination server in with no recovery

    Apply logs to destination server with no recovery

    From Source server source db run through mirroring wizard through SSMS (although you can also do this in TSQL).


    Regards,
    Mark Broadbent.

    Contact me through (twitter|blog)

    Please click "Propose As Answer" if a post solves your problem
    or "Vote As Helpful" if a post has been useful to you
    • Marked as answer by unikoman Saturday, February 26, 2011 5:22 AM
    Friday, February 25, 2011 1:12 PM
  • First you should fix the VM problem before going to configure mirroring
    Friday, February 25, 2011 2:06 PM
  • Thans guys, I'll go back to the hosting company who provided the VM to get this sorted. 
    Saturday, February 26, 2011 5:24 AM