I have a 600 gig database that has a mirror. I need to move the databases from local drives to a SAN. Can anyone recommend a document that lists the steps to go through to move both the principle and mirror to the SAN with no down time? or minimal down time?

As far as I know, there isn’t any such document so I had a crack at coming up with a list of operations. Here’s what I had:

Take a full backup of the principal on node A

Restore it on the SAN on node B using WITH NORECOVERY, remembering to use WITH MOVE to place the files correctly, and with a different database name than the current mirror

Take the required log backup on the principal and restore on the database copy on the SAN on node B

Break the mirroring partnership

Drop the current mirror database on node B

Rename the database on the SAN on node B to be the mirror database — THIS DOESN”T WORK!

Setup the mirroring partnership to point to the newly restored database on the SAN on node B

Start mirroring and the new mirror will catch-up

Failover to the mirror on node B, which becomes the new principal

Follow the same procedure to move the new mirror on node A onto its SAN

Failback if you want to

And I promised to try it out to make sure I had it right so in this blog post I’m going to walk through the steps of doing this. It turns out that the steps above are slightly incorrect. Step 6 above doesn’t work because the database is in recovery (so is inaccessible) and there’s a short-cut when moving the database on the first node to avoid having to take and copy more backups. Let’s see how it works and I’ll post the corrected sequence at the end.

As I did in yesterday’s mirroring post, I’m going to use the TicketSalesDB database from our Always-On DVDs. It’s only a few hundred MB instead of 600GB but the principal is the same (no pun intended :-)). I’ve got mirroring running between two nodes, SQLDEV01 (the principal) and SQLDEV02 (the mirror), both of which are running 2005 SP2 and I’ve got a simulated workload inserting rows into the database. I don’t actually have a SAN laying around so I’m cheating and I have directories called C:\SQLDEV01SAN and C:\SQLDEV02SAN instead. It’s the location change that’s the interesting part, not where the actual location is.

And query the DMV again to make sure. This time the mirroring_state_desc returned is:

MIRROR

Excellent!

Now, I did all of this while my workload was running and it automatically failed over to SQLDEV02, with the database now hosted on the SAN. To do the same move on SQLDEV01, we don’t need to go through the backup and copy process again – we can just use the original backups we took in step 1.

Step 8

We need to break the mirroring partnership again, this time executing on SQLDEV02, the new principal:

ALTER DATABASE [TicketSalesDB] SET PARTNER OFF;
GO

On SQLDEV01, we can now drop the database and restore the original backups onto the SAN: