none
Adding a 2.5TB database to a SQL 2014 Ent AOAG RRS feed

  • Question

  • Hi

    We have several databases that are members of an AOAG running between two MS SQL Server 2014 Ent instances.  We wish to add another database but it's 2.5TB in size.  This makes the traditional approach, for adding a DB to an AOAG, very difficult in terms of running a full backup and restoring it on the secondary node.  We have conducted some tests and bearing in mind the replica is in another datacentre (connected with a 5Gbps link) the backup itself is taking around 55hrs.

    I know 2016 has the direct seeding option but I have seen articles saying this shouldn't be used/may not work well with large databases.  So we potentially have one option of upgrading to 2016 but have fairly limited time.  Are there any views on this approach?

    Does anyone know if you can combine, for example mirroring, with AOAG to get the database initially over to the replica node? Or a different approach?  Or perhaps a 3rd party tool that may help?

    Thanks in advance

     Sam

    Tuesday, October 8, 2019 12:45 PM

Answers

  • I also would not use automatic seeding for so large a database.

    Backup the database to multiple files and then ship them to the other data center. Pull is faster than a push. You can then do a differential after to minimize the gap after taking your original backup.

    You may also find that it is faster sometimes to courier the backup rather than sending it over the wire. All other methods involve starting with a backup for your base.

    • Marked as answer by BIT Solutions Wednesday, October 9, 2019 1:48 PM
    Tuesday, October 8, 2019 1:33 PM
  • Hi Sam,

    Please check below link. Adding a VLDB database to an SQL Server Availability Group

    Hope this could help you.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by BIT Solutions Wednesday, October 9, 2019 1:48 PM
    Wednesday, October 9, 2019 6:12 AM

All replies

  • I also would not use automatic seeding for so large a database.

    Backup the database to multiple files and then ship them to the other data center. Pull is faster than a push. You can then do a differential after to minimize the gap after taking your original backup.

    You may also find that it is faster sometimes to courier the backup rather than sending it over the wire. All other methods involve starting with a backup for your base.

    • Marked as answer by BIT Solutions Wednesday, October 9, 2019 1:48 PM
    Tuesday, October 8, 2019 1:33 PM
  • Hi Hilary

    Thanks for your response.  A couple of questions if possible?

    - When you refer to "Pull is faster than a push" I understand but in relation to my question what point are you making?

    - Is there no way that replication options can be used to seed the database?  Or any other option for that matter?

    Thanks Sam

    Tuesday, October 8, 2019 1:38 PM
  • You can log ship, but this will require a backup to be copied over to begin with.

    If you copy from your primary to your secondary you will find this is slower than if you copy from your secondary to your primary. So on your secondary open up a command window and map a drive to where your backup is stored on your primary and copy it to the secondary.

    Tuesday, October 8, 2019 1:45 PM
  • Hi Sam,

    Please check below link. Adding a VLDB database to an SQL Server Availability Group

    Hope this could help you.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by BIT Solutions Wednesday, October 9, 2019 1:48 PM
    Wednesday, October 9, 2019 6:12 AM
  • Thank-you both.  Sadly all the suggested techniques, which I suspect is just the situation we have to accept, require a full backup first.  The issue with this is the time it will all take and not allowing our backup systems to run against the DB in the meantime.

    We will look into the above while also installing some new SSD storage which will make the process much quicker, even though we will still have to follow the same process.

    Thanks for taking the time to give your advise/input.

    Wednesday, October 9, 2019 1:47 PM