Sql-server – Update primary SQL Server in mirrored environment using transaction logs from old environment

migrationmirroringsql server

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?

Best Answer

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>>’