Adding a VLDB database to an SQL Server Availability Group

Recently, I was tasked with making an 60TB database highly available by adding it to an availability group. The following, is how I successfully completed adding the very large database (VLDB) to an Availability Group that had two replicas in the primary data center and a third replica in another data center with no downtime. We wanted to utilize the least amount of time during the initial synchronization process so we leverage log shipping to prepare the database on each replica.

We will call the three servers DC1Server1, DC1Server2, and DC2Server3.  We will have a pre-created AG called SQL2014AG1. We will say that the 60TB database is named VLDB.

Previously we did a side by side upgrade to upgrade VLDB from running on SQL Server 2012 to SQL Server 2014 on new hardware.  Previously, we also leveraged the new hardware to upgrade a subset of the data and build out the Availability Group for testing and training purposes.

  1. Create Availability Group. This was created in advance to deliver a proof of concept and to provide training for understanding how to use and manage an Availability Group. Therefore, there was no reason to break this and recreate it. If needed, you could obviously create the AG as part of this deployment where we just Add the VLDB into the AG.  Creating an Availability Group will not be covered here.
  2. Configure Log Shipping. The primary database in log shipping should be on the instance that is also the read/write replica for the availability group. In this case, VLDB already exists and is configured as primary on DC1Server1. This is also the primary replica for the SQL2014AG1 availability group. If you need examples on how to configure log shipping checkout my article on MSSQLTips.com.
  3. Identify the Log Shipping backup Job. This job usually starts with LSBackup_ for your database. This will be on DC1Server1.
    1. Execute the SQL Agent Job to take a log transactional log backup
    2. Disable the SQL Agent Job when the backup completes.
  4. On the secondary servers (DC1Server2 and DC2Server3) identify the LSCopy_ job for your database named VLDB.
    1. Execute the SQL Agent Job to copy the last log backup that you just created in step three.
    2. Disable the SQL Agent Job when copy backup job completes.
  5. On the secondary servers (DC1Server2 and DC2Server3) identify the LSRestore_ job for your database named VLDB.
    1. Execute the SQL Agent Job to restore the backups pending to be restored. This should include your backup taken in Step four.
    2. Disable the SQL Agent Job when the restore completes.
  6. Validate that the last log backups were copied and restored. This can be done using sp_help_log_shipping_monitor stored procedure on each instance. We would do this on DC1Server1 to validate the last log backup that was completed. On DC1Server2 and DC2Server3 we can validate that the backup was copied and also restored.
  7. On the primary server DC1Server1 add the VLDB to the existing availability group named SQL2014AG1. You can do this via the GUI.
    1. In SSMS under Availability Groups expand “SQL2014AG1” Availability Group and right click on Availability Databases and select “Add Database” to add VLDB.
    2. Select the “VLDB” database in the checkbox
    3. Select Join Only to start data synchronization as we used Log shipping to keep the VLDB in sync across all servers.
    4. Connect to all existing secondary servers (DC1Server2, and DC2Server3)
    5. Validate adding the Database to the Availability Group
    6. Add the Availability Group to your Availability Group named SQL2014AG1
  8.  Validate that Adding VLDB was successful to SQL2014AG1
    1. Remove log shipping on DC1Server1 for the VLDB database.
    2. Enable your log backup process for all three servers. This would be used to replace log shipping for taking transactional log backups.

 

 

  • Pingback: Adding a VLDB database to an SQL Server Availability Group - SQL Server - SQL Server - Toad World()

  • Garry Bargsley

    Why would you not just restore a FULL on the two secondaries in no recovery. Then once the full is restored do a DIFF and finally once the DIFF is applied take a TLOG then stop the TLOGs until you add to AG.

  • John

    Hi Garry,

    You bring up a great question. You could add a VLDB to an Availability Group with restoring Full backup, differential backup a log backup after the differential but here is why I wouldn’t go down this path with a VLDB.

    Prepping with log shipping gives you the following
    benefits. It will automate the process of applying all log backups to keep your data in sync. This would have to be
    done manually if you wanted to sync the VLDB ahead of adding the database to the Availability Group. This gives you a warm standby while you can prepare your VLDB for being added to the availability group. It also allows you to
    configure your log shipping secondary’s at different times. This can be helpful when you need to send very large full backups across data centers and restore it.

    I also wouldn’t recommend that you stop taking transactional log backups until you migrate to the AG. This could cause the AG to not sync,bloat your transactional log and prevent you from a successful implementation. It’s another reason why I like prepping VLDBs for availability groups with log shipping. The process of keeping the secondary’s in sync and also maintaining transactional backups on the primary is automated.

    I hope this is helpful.

    Regards,
    John

  • Golam

    How did you backup 60TB database and how long it took to restore? I’ve a filestream database of 15tb. Couldn’t backup yet. Any suggestion?