Search Engine Q&A #2: Moving a database while Database Mirroring is running

This was a question from the MSDN Disaster Recovery forum I started while I was at Microsoft.

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:

1. Take a full backup of the principal on node A
2. 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
3. Take the required log backup on the principal and restore on the database copy on the SAN on node B
4. Break the mirroring partnership
5. Drop the current mirror database on node B
6. Rename the database on the SAN on node B to be the mirror database — THIS DOESN”T WORK!
7. Setup the mirroring partnership to point to the newly restored database on the SAN on node B
8. Start mirroring and the new mirror will catch-up
9. Failover to the mirror on node B, which becomes the new principal
10. Follow the same procedure to move the new mirror on node A onto its SAN
11. 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.

Step 1

On SQLDEV01, take a full backup and a log backup:

BACKUP DATABASE [TicketSalesDB] TO DISK = N'C:\SQLskills\TicketSalesDB.BAK' WITH INIT;
GO

BACKUP LOG [TicketSalesDB] TO DISK = N'C:\SQLskills\TicketSalesDB_Log.bak' WITH INIT;
GO

Step 2

On SQLDEV01, break the mirroring partnership:

ALTER DATABASE [TicketSalesDB] SET PARTNER OFF;
GO

And just check that it’s gone:

SELECT [mirroring_state_desc] FROM sys.database_mirroring WHERE [database_id] = DB_ID (N'TicketSalesDB');
GO

NULL

Step 3

On SQLDEV02, drop the mirror database – this wouldn’t work unless mirroring was no longer running:

DROP DATABASE [TicketSalesDB];
GO

Step 4

Copy the backups to SQLDEV02 and restore them on the SAN and remembering to use WITH NORECOVERY:

RESTORE DATABASE [TicketSalesDB] FROM DISK = N'C:\SQLskills\TicketSalesDB.bak'
WITH
    MOVE N'TicketSalesDBData' TO N'C:\SQLDEV02SAN\TicketSalesDBData.MDF',
    MOVE N'TicketSalesFG2005Q1' TO N'C:\SQLDEV02SAN\TicketSalesFG2005Q1.NDF',
    MOVE N'TicketSalesFG2005Q2' TO N'C:\SQLDEV02SAN\TicketSalesFG2005Q2.NDF',
    MOVE N'TicketSalesFG2005Q3' TO N'C:\SQLDEV02SAN\TicketSalesFG2005Q3.NDF',
    MOVE N'TicketSalesFG2005Q4' TO N'C:\SQLDEV02SAN\TicketSalesFG2005Q4.NDF',
    MOVE N'TicketSalesDBLog' TO N'C:\SQLDEV02SAN\TicketSalesDBLog.LDF',
    NORECOVERY;
GO

Step 5

On SQLDEV02, set the mirroring partner to be SQLDEV01:

ALTER DATABASE [TicketSalesDB] SET PARTNER = 'TCP://SQLDEV01:5091';
GO

Step 6

On SQLDEV01, start mirroring:

ALTER DATABASE [TicketSalesDB] SET PARTNER = 'TCP://SQLDEV02:5092';
GO

And check that it’s running:

SELECT [mirroring_state_desc] FROM sys.database_mirroring WHERE [database_id] = DB_ID (N'TicketSalesDB');
GO

This time it returns:

SYNCHRONIZED

Step 7

Now we need to failover so that we can move the database on SQLDEV01 onto its SAN. Before we do that, let’s make sure that SQLDEV01 is the principal:

SELECT [mirroring_role_desc] FROM sys.database_mirroring WHERE [database_id] = DB_ID (N'TicketSalesDB');
GO

which returns:

PRINCIPAL

Now force the failover:

ALTER DATABASE [TicketSalesDB] SET PARTNER FAILOVER;
GO

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:

DROP DATABASE [TicketSalesDB];
GO

RESTORE DATABASE [TicketSalesDB] FROM DISK = N'C:\SQLskills\TicketSalesDB.bak'
WITH
    MOVE N'TicketSalesDBData' TO N'C:\SQLDEV01SAN\TicketSalesDBData.MDF',
    MOVE N'TicketSalesFG2005Q1' TO N'C:\SQLDEV01SAN\TicketSalesFG2005Q1.NDF',
    MOVE N'TicketSalesFG2005Q2' TO N'C:\SQLDEV01SAN\TicketSalesFG2005Q2.NDF',
    MOVE N'TicketSalesFG2005Q3' TO N'C:\SQLDEV01SAN\TicketSalesFG2005Q3.NDF',
    MOVE N'TicketSalesFG2005Q4' TO N'C:\SQLDEV01SAN\TicketSalesFG2005Q4.NDF',
    MOVE N'TicketSalesDBLog' TO N'C:\SQLDEV01SAN\TicketSalesDBLog.LDF',
    NORECOVERY;
GO

RESTORE LOG [TicketSalesDB] FROM DISK = N'C:\SQLskills\TicketSalesDB_Log.bak' WITH NORECOVERY;
GO

And setup mirroring again. On SQLDEV01:

ALTER DATABASE [TicketSalesDB] SET PARTNER = 'TCP://SQLDEV02:5092';
GO

And on SQLDEV02:

ALTER DATABASE [TicketSalesDB] SET PARTNER = 'TCP://SQLDEV01:5091';
GO

And we’re running again.

Step 9

Now all we need to do is fail the workload back to SQLDEV01 by executing this on SQLDEV02:

ALTER DATABASE [TicketSalesDB] SET PARTNER FAILOVER;
GO

Summary

So – the corrected sequence for moving a database while mirroring is running is the following:

 

1. Take a full backup of the principal database on node A, and the required log backup
2. Break the mirroring partnership
3. Drop the current mirror database on node B
4. Copy the backups to node B and restore it on the SAN on node B using WITH NORECOVERY, remembering to use WITH MOVE to place the files correctly
5. Setup the mirroring partnership to point to the newly restored database on the SAN on node B
6. Start mirroring and the new mirror will catch-up
7. Failover to the mirror on node B, which becomes the new principal
8. Follow the same procedure to move the new mirror on node A onto its SAN, but using the original backups from step 1
9. Failback

Hope this helps.