Impact of using mirroring to migrate between SQL Server 2005 and SQL Server 2008 R2

Question

I've got a DB which sits on a machine (SQL Server 2005) due for replacement very soon.

I'm planning on having the database mirrored to the replacement machine (SQL Server 2008 R2) so that when the time comes to switch everything over, I can fail it over and quickly get things back up and running on the new machine.

If I then mirror the DB on the replacement machine (now the principal) to ANOTHER machine (SQL Server 2008 R2), will there be any impact or issues which could appear, which wouldn't if I'd have backed up and restored cleanly onto the new server?

(Assume that I've copied all the users/configurations/jobs etc over to the replacement machine, and that once the replacement machine is in use, the SQL 2005 machine will be removed so it won't be failed back to)

• Aaron Bertrand: Can't think of anything. To SQL Server once the new machine has become the principal (and that mirroring has been removed) it's just as if you'd completed a successful restore with recovery. In other words, it's a database just like any other that is a candidate for mirroring to another instance.

Answer 1

What you are trying to do is a rolling upgrade sans upgrading the principal (kudos on leveraging technology). There is a link on BOL which details what to expect.

Answer 2

Everything will move over just fine. Just make sure that you don't fail over by accident ahead of schedule (I've seen someone do that) as there's no way to fail back.