PolyBase External Tables and Always On Availability Group – Part 2

In this post we will see how to add a database that hosts PolyBase external tables to an Always On Availability Group. In the previous post we have extracted data from external files stored in Azure Data Lake Gen 2 storage using PolyBase.

Scenario

• A database “MAQDB” that hosts external tables.
• Database is encrypted by the Database Master Key
• Two nodes Always On Availability Group

Solution

1. 1. 1. Create Database Master Key on all secondary nodes using stored procedure “sp_control_dbmasterkey_password”. In our case with the following statement:
         
         Transact-SQL

         EXEC sp_control_dbmasterkey_password @dbname=N'MAQDB' , @password=N'Pa55w.rd@123' , @action=N'add'

         1 2 3

         EXEC sp_control_dbmasterkey_password @dbname=N'MAQDB' , @password=N'Pa55w.rd@123' , @action=N'add'

      2. Add the database “MAQDB” to an already created availability group “agMAQ” using wizard. You will see that the “Check Box” associated with “MAQDB” database is disabled with “Password required” status.
      3. Double click in the “Password” column to enter the password and press the “Refresh” button at the bottom of the screen after entering the password to enable the “Check Box” next to the database.
         
      4. Select the database “MAQDB”
         
      5. Connect to secondary replica
         
      6. I am choosing “Join Only” as I have already restored the database on the secondary node.
         
      7. Finish the wizard and your database will be added to the availability group
         
      8. Connect using listener name
         

You can also use the following statement to add the above database to availability group