locked
Detach and Attach 12TB database RRS feed

  • Question

  • Hi, 

    I am working on migrating my traditional SQL Cluster instance to Stand alone SQL Instance with AlwaysOn (HADR). I have a 10TB database with filestream. I am planning the below steps:

    1. Detach the 10TB database

    2. Remove node from SQL Cluster

    3. Install Stand Alone SQL Server and configure AlwaysOn

    4. Attach the 10TB database. 

    My Question is, how feasible is detaching and attaching the database of size 10TB (with filestream feature). How much time it may take, I know it will take huge time and space for backup/restore, so analyzing the option of using Detach/attach.  I have used for small size database,but not very large database like this. 

    Please help. 

    Monday, June 27, 2016 7:59 PM

Answers

  • First, Why? 

    Have you done the capacity planning to support a 10TB database with Availability Groups?

    Using AlwaysOn AGs will double your data and double your IOPS, and generate a lot of network traffic.

    How much of the 10TB is Filestream data?

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, June 27, 2016 8:13 PM
  • Attach and Detach process is very quick compared to backup\restore method.

    Typically, when you Detach, all the dirty pages are written to log and the db is detached. Make sure no one is accessing the database, other wise detach process will wait until all the existing connections to the Db are closed.

    you can force the existing connections to close by using Roll back immediate.

    after the copying the files to new location, you can attach the DB. I do not think you should have any issues, with this.

    However,  it is no clear on Step 2. are you configuring Always on in the same cluster or different cluster.

    alwayson needs Cluster. So, I am not sure what you mean by remove node from cluster.


    Hope it Helps!!

    Monday, June 27, 2016 8:16 PM

All replies

  • First, Why? 

    Have you done the capacity planning to support a 10TB database with Availability Groups?

    Using AlwaysOn AGs will double your data and double your IOPS, and generate a lot of network traffic.

    How much of the 10TB is Filestream data?

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, June 27, 2016 8:13 PM
  • Attach and Detach process is very quick compared to backup\restore method.

    Typically, when you Detach, all the dirty pages are written to log and the db is detached. Make sure no one is accessing the database, other wise detach process will wait until all the existing connections to the Db are closed.

    you can force the existing connections to close by using Roll back immediate.

    after the copying the files to new location, you can attach the DB. I do not think you should have any issues, with this.

    However,  it is no clear on Step 2. are you configuring Always on in the same cluster or different cluster.

    alwayson needs Cluster. So, I am not sure what you mean by remove node from cluster.


    Hope it Helps!!

    Monday, June 27, 2016 8:16 PM