none
Move database while in AlwaysOn availability group

    Question

  • Hee Guys I have a question and I try to find answer on the internet but I could find it. I have a database that is in a Availability group. There is constantly data written in the database. I really want to move it to a new disk. I am working with a SQL 2012 enterprise edition. Is there a way to move the database on both servers without losing data? 

    I was thinking of de-attaching and attaching it but that is not allowed while in the availability group. When I take it out of the group and move it to the new disk on the secondary server, then I cannot put it back in an availability group because the path is not the same. Can somebody tell me how to do this in the right way.

    Any help is appreciated!

    Monday, August 11, 2014 4:05 PM

Answers

  • Dear Sean,

    Thanks for comming back to me. I'll be honest with you I was trying this with only two availability nodes. That is what we have set-up here. Since I was getting all these connection errors I am setting up a third database node. Working on that now and then I will try again. 

    Thanks man!

    Saturday, August 16, 2014 10:06 AM

All replies

  • Hello,

    Instead of re-creating this, here is a link to what you want to do: http://blogs.msdn.com/b/sqlserverfaq/archive/2014/02/06/how-to-move-databases-configured-for-sql-server-alwayson.aspx


    Sean Gallardy | Blog | Microsoft Certified Master

    Monday, August 11, 2014 5:08 PM
    Answerer
  • Thanks Sean,

    I had a look at it, looks like a lot of manual (scary) stuff. I will dive in to it today see how far I get. 

    Tuesday, August 12, 2014 8:36 AM
  • I did some testing. What I am missing in the script is to kill transactions and processes. On one database I got the following exception:

    --MOVE FILES
    xp_cmdshell 'move "F:\Database\DBName1*.*" M:\Database\' -- Change values
    go

    F:\Database\DBName1.ldf
    The process cannot access the file because it is being used by another process.
            0 file(s) moved.
    NULL

    On the another database I got something the same: 

     

    GO
    ALTER DATABASE [DBName2] SET ONLINE -- Change values
    GO

    File activation failure. The physical file name "E:\Database\DBName2.ldf" may be incorrect.
    The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
    Msg 5181, Level 16, State 5, Line 2
    Could not restart database "DBName2". Reverting to the previous status.
    Msg 5069, Level 16, State 1, Line 2
    ALTER DATABASE statement failed.

    What is the best way to go around this? I tried to use EXEC sp_who2 and Kill but there are no processes for these database. I also looked in the resource monitor to see if there are files in used but this is also not the case.



    Wednesday, August 13, 2014 2:32 PM
  • Joost,

    Sorry for the late reply.

    If you gracefully failed the replica over this shouldn't be a problem. Data on read only secondarys can't be changed, so I'm not sure what happened to give you this error.

    Can you elaborate a bit more?


    Sean Gallardy | Blog | Microsoft Certified Master

    Friday, August 15, 2014 8:24 PM
    Answerer
  • Dear Sean,

    Thanks for comming back to me. I'll be honest with you I was trying this with only two availability nodes. That is what we have set-up here. Since I was getting all these connection errors I am setting up a third database node. Working on that now and then I will try again. 

    Thanks man!

    Saturday, August 16, 2014 10:06 AM