Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm currently migrating a large (~40gb) database to a new environment. The new environment is a HA/mirrored set up with primary, mirror and witness nodes.

I'm backing up the database in the old environment, restoring to primary & mirror, and turning on mirroring. During this time the old environment is still active, and data is changing. When we are ready to go live with the new environment I plan to take another transaction log from the old environment and restore that to the new primary server. Is that possible? Will this be successfully synchronised to the mirror?

share|improve this question
add comment

2 Answers 2

When we are ready to go live with the new environment I plan to take another transaction log from the old environment and restore that to the new primary server. Is that possible? Will this be successfully synchronized to the mirror?

You can set up mirroring as normal. But when you want to failover, then use SYNCHRONOUS - High safety without automatic failover.

enter image description here

using T-SQL :

ALTER DATABASE <<DB NAME>> SET PARTNER FAILOVER

Doing so, only the role reversal happens and mirroring direction is reversed - principal becomes mirror and vice-versa. Note that MIRRORING is not BROKEN.

Now once you verify everything is OK, then you can break mirroring with below steps :

Turn mirroring off (break mirroring) on the mirror DB.

ALTER DATABASE <<DB NAME>> SET PARTNER OFF

Set the DB to RECOVERY

RESTORE DATABASE <<DB NAME>> WITH RECOVERY

Migrate Logins as well as any sql agent jobs to secondary. You might have to fix orphaned users.

EXEC sp_change_users_login ‘Auto_Fix’ , ‘<<username>>’
share|improve this answer
    
Hi Kin really sorry but I'm not looking to failover... I've actually got environment A and environment B, looking to move the DB from environment A to environment B while environment A is still running, then at the last minute do another update from A->B and go live with B. –  Aligma Jun 21 '13 at 2:51
    
@Aligma If I understand your question, you want to failover your old mirrored DB to new server. Then just change the mirroring mode to use SYNCHRONOUS - High safety without automatic failover. And then initiate a failover. Your secondary will become primary. Refer to Database availability during planned downtime msdn.microsoft.com/en-us/library/cc917681.aspx#EIAA –  Kin Jun 21 '13 at 2:55
    
the database in the old environment is not mirrored, and the database servers are in two different networks. –  Aligma Jun 21 '13 at 3:02
    
@Aligma please specify that in your question so we can help you by giving correct answer. why do you want mirroring ? Have you looked into logshipping as that will be more easy to setup and when you want to failover, you just have to take a tail log backup from primary and then apply it to secondary server with recovery to bring the secondary database online. –  Kin Jun 21 '13 at 12:59
    
the database in the old environment is not mirrored, the new mirrored database is running across new servers in a new network –  Aligma Jun 22 '13 at 7:51
add comment
up vote 0 down vote accepted

I now believe the approach I am looking for is

  • Restore backup from A to B Primary and B Mirror
  • Wait for migration time
  • Take a transaction log from A
  • Restore transaction log on B Primary
  • Restore transaction log on B Mirror with NORECOVERY
  • Set reconnect mirroring
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.