2

We are deploying new VMs for a mirrored database that is in high safety mode with automatic failover and also going from SQL Server 2008 to SQL Server 2008R2.

Most of the documentation I've come across covers a rolling upgrade, but that only covers upgrading the SQL version, not an OS version change let alone changing servers out.

The steps as I see it are: I have to break the mirror and then recreate with the new secondary server, failover, break it again, and then recreate it with the new primary and witness.

How have you managed migrating servers and databases to new OS and new version with an existing mirror?

  • Can you take downtime to do this? – Aaron Aug 17 '15 at 20:50
  • In Test I can take an outage but in Production there is a very short window. The question will be how long it takes the DNS records to update. That is an unknown I can't account for which is why the mirror is the safer route. I can afford to wait for the DNS alias to propagate to the client site before scheduling the failover since the original primary will still be in play. – Your_comment_is_not_funny Aug 17 '15 at 21:08
  • for DNS, reduce TTL to 5 or 10 mins 3 days before. You may be able to create rules in your network that will transfer the traffic to another IP for a couple days once the old one is down What is the size of the prod DB? – Julien Vavasseur Aug 18 '15 at 6:11
  • Management of DNS is out of my control, I can certainly add that to my request but I have no way of ensuring it is implemented. We won't be reusing the ip though, the new servers are using a new name to meet a naming convention. They are adding an alias to redirect to the new server. I have several mirrored databases of varying sizes I'll be doing this for, so I can't give you a single size, the largest is 2 TB. – Your_comment_is_not_funny Aug 18 '15 at 13:34
0

I think I would first outline something like this before testing it while recording time with a stopwatch.

  1. D-X

    • get the newProd and newMirror ready (OS + SQL Server + User Account, job, ...)
    • build scripts for every step (back, restore, mirror, ...)
  2. D-3

    • add a new IP to oldProd
    • update DNS with new IP and TTL = 300s (Time To Live)
  3. D, H-2 or H-1 (I assume backup and restore both take 30min)

    • full backup of oldProd
    • restore newProd and newMirror
  4. D, H (Downtime)

    • Stop applications and DB access
    • Backup logs on oldProd
    • Restore logs on newProd
    • Remove new IP from oldProd
    • Add new IP to newProd
    • Start applications and DB access
  5. D, H+something

    • Build new mirror between newProd and newMirror

Another option for 4. (downtime should be lower)

  • Break mirror with oldMirror
  • Backup/Restore logs to newProd
  • Create new mirror between oldProd and newMirror
  • Manual failover to newProd
  • Break new mirror
  • Create new mirror between newProd and newMirror

I may have forgotten a couple things.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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