locked
getting mirrored db temporarily online RRS feed

  • Question

  •  

    We have a number of databases mirrored between production servers and DR servers using SQL Server 2005.   I have an automated procedure for synchronising Logins between production and DR servers but it depends on the DR database being online - needs to be online when creating a Login with mappings to that db.   However, when the db is mirrored, it is of course not available and therefore Logins can't be created with user mappings to that db.  So, I have been playing with a procedure to temporarily stop mirroring and bring the DR database online.   All that is fine but the problem is that I need to get the DR db back into 'norecovery' mode to start the mirroring again.   The practical problem is that we can't simply restore a backup of the corresponding production databases just to perform this procedure - too many largish databases and it's just impractical.  Question... Is there a way to get a database into 'norecovery' mode without actually restoreing a database?   I know it's possible to take a database out of 'recovery' mode...

     

    RESTORE DATABASE MyDB WITH RECOVERY

     

    ... The above only works going from 'norecovery' to 'recovery' and not the other way round.

     

    Any way do do this?

     

    Clive

    Wednesday, October 1, 2008 4:17 PM

All replies

  •  

    unfortunately this is not possible. once you take a mirrored database out of the restoring mode, it is recovered past the hardened lsn (i.e., new log is generated, data pages are changed) and hence you cannot take it back to the point in time where it can pick-up the log stream from the principal at the time it left off.

     

    would database snapshots on the mirror work for your purposes ?  alternatively, you could try to force service on the mirror, but then any changes on the former principal will be discarded.

     

    hth,

    -kal.

    Wednesday, October 1, 2008 11:13 PM
  •  

    Kal,

    Thanks for that.   The reason I wanted to get the mirrored db online for a short time was so that I could create Logins.  I thought that the error I was getting when creating Logins was because of the db user mappings but it's probably that the default db wasn't available.   I'm wondering if it would be ok to change all the default db's per login to the 'Master' db, which of course will be available.   I don't think the default db change to Master would affect App access in any way but I seem to rmember that there are good reason for not setting the default db to Master for ordinary user Logins... Not sure what the issue is though.

     

    Alternatively, from a DR point-of-view, I could just script out the Logins from time-to-time (automatically) and leave them in a file on the DR server - specifying in the DR plan that the Logins had to be manually created as soon as the user db's were online.  I would of course prefer to avoid any manual steps like this.

     

    Clive

    Thursday, October 2, 2008 1:32 PM
  • You can try Transfer Logins Task in SSIS to transfer the logins from principal to mirror server. You can schedule this as a job everyday to transfer the logins from principal to mirror server.
    Thursday, October 9, 2008 9:54 AM