If you follow this blog or my Twitter feed you should know I am a massive fan of SQL Server AlwaysOn.
Recently I restored and moved some TFS databases around, and one of them remained on a temporary storage because of the massive size involved. After a while I managed to sort out the primary storage so I could move this database (and its Transaction Log) back to it.
This what I did, no warranties of course but it worked on my machines!
First of all, you need to be aware that you will have a limited availability during this period. It doesn't mean you are going to have an outage, but that you cannot rely on the Secondary Replica while you work on it. Why? Because you need to disable the Automatic Failover and make any Secondary non-readable:
Then suspend Data Movement from the Primary. This means your Primary Replica is not going to sync with the Secondary.
You will get your database to move in a non synchronised state.
Now note down your logical names for the files you need to move. Use these in the following query, the path in the FILENAME is going to be the new destination:
Run this on all servers. You might want to wait for the Secondary to be up-and-running, but don't forget to run it against the Primary too!
Copy all the files to the new destination, once done restart SQL Server on the Secondary:
Now check that if Secondary is in a green state.
If the Secondary is green, resume Data Movement and after the status is Synchronised again perform a manual Failover so that the roles are swapped. Then perform all of the above on the new Secondary and you will be done.
Eventually, don't forget to re-enable any configuration you disabled before performing this!
Recently I restored and moved some TFS databases around, and one of them remained on a temporary storage because of the massive size involved. After a while I managed to sort out the primary storage so I could move this database (and its Transaction Log) back to it.
This what I did, no warranties of course but it worked on my machines!
First of all, you need to be aware that you will have a limited availability during this period. It doesn't mean you are going to have an outage, but that you cannot rely on the Secondary Replica while you work on it. Why? Because you need to disable the Automatic Failover and make any Secondary non-readable:
Then suspend Data Movement from the Primary. This means your Primary Replica is not going to sync with the Secondary.
You will get your database to move in a non synchronised state.
Now note down your logical names for the files you need to move. Use these in the following query, the path in the FILENAME is going to be the new destination:
Run this on all servers. You might want to wait for the Secondary to be up-and-running, but don't forget to run it against the Primary too!
Copy all the files to the new destination, once done restart SQL Server on the Secondary:
Now check that if Secondary is in a green state.
If the Secondary is green, resume Data Movement and after the status is Synchronised again perform a manual Failover so that the roles are swapped. Then perform all of the above on the new Secondary and you will be done.
Eventually, don't forget to re-enable any configuration you disabled before performing this!
No comments:
Post a Comment