I created implementation steps with fallback plan to add a database to an existing Always On availability group.Can someone please take time and go through the steps and let me know if this everything is correct? If I am missing any steps, kindly show me some pointers or guidance.

(Points written with reference to Microsoft Technet Documentation)

Step 1: Creating Database

Using SQL Server Management Studio:

  1. In Object Explorer, connect xxxx of the SQL Server Database Engine and then expand that instance.
  2. Right-click Databases, and then click New Database.
  3. In New Database, enter a database name xxxxx

    • Adding data and log files to the database
      a) In the Database Properties dialog box, select the Files page
      b) To add data and transaction log file, click Add.
      c) In the Database files grid, enter a logical name for the file.
      d) Select the file type, data or log. I will be creating both data and log files.
      e) For a data file, select the file group in which the file should be included from the list, or select to create a new file group.
      f) Specify the initial size of the file. The initial size of the File will be 5GB.
      g) To allow for the currently selected file to grow as more data space is required, select the Enable Autogrowth check box and then select from the following options:
      h) To specify that the file should grow by fixed increments, select In Megabytes and specify a value. In this case it will be 512MB.
      i) Specify the path for the file location. The specified path must exist before adding the file. In this case it would be D:\ for the data files and E:\ for the log files.
  4. To change the recovery model, select the Options page and select a recovery model from the list. The recovery model we will be using is FULL because Always on only supports Full recovery model.

  5. Click on "OK" to create the Database.

Step 2: Adding the database to the Vxxxxx AAG on the Primary xxxxxx)

In Object Explorer, connect to the server instance that hosts the primary replica, and expand the server tree.

  1. Expand the AlwaysOn High Availability node and the Availability Groups node.
  2. Right-click the availability group, and select one of the following commands:
    a. To launch the Add Database to Availability Group Wizard, select the Add Database command.
    b. In the Availability Databases pane, click the Add button. This creates and selects a blank database field.
    c. Enter the name of a database xxxx that meets the availability-databases prerequisites (should be in full recovery model)
    d. Click OK to complete the operation.
  3. On the Select Initial Data Synchronization page, choose how you want your new secondary databases to be created and joined to the availability group. Full Data Synchronization will be selected. To initiate this process a full and a transactional log backup of the database will be performed and stored at the Network share: xxxxx
  4. In the Specify a shared network location accessible by all replicas: field, specify a backup share to which all of the server instance that host replicas have read-write access. In this case we will specify xxxxx
  5. The Validation page verifies whether the values you specified in this Wizard meet the requirements of the New Availability Group Wizard. To make a change, you can click Previous to return to an earlier wizard page to change one or more values. The click Next to return to the Validation page, and click Re-run Validation.
  6. Summary page, review your choices for the new availability group. To make a change, click Previous to return to the relevant page. After making the change, click Next to return to the Summary page.
  7. The Progress page displays the progress of the steps for creating the availability group (configuring endpoints, creating the availability group, and joining the secondary replica to the group).
  8. When these steps complete, the Results page displays the result of each step.
  9. When the wizard completes, click Close to exit.

Fall Back Plan

The fall back plan will be implemented if the Initial Data Synchronization has not been successful. Below are the steps which will be performed to complete this operation:

Using SQL Server Management Studio

  1. In Object Explorer, connect to the server instance that hosts the primary replica of the database or databases to be removed, and expand the server tree.
  2. Expand the AlwaysOn High Availability node and the Availability Groups node.
  3. Select the availability group xxxx, and expand the Availability Databases node.
  4. To remove a single database xxxx select it in either the Object Explorer pane or the Object Explorer Details pane.
  5. Right-click the xxxx database, and select Remove Database from Availability Group in the command menu
  6. In the Remove Databases from Availability Group dialog box, to remove the xxxxx the listed databases, click OK
  7. Once the Database has been removed from the AAG Group xxxxx, run the following TSQL in the query editor window:

    DROP DATABASE xxxxx;
  • Yes. Whatever you have written with reference with MSDN Bol regarding the AG implementation that is complete steps from 'Creating database' to implement that database in AG group (Primary replica server). 'Fall Back Plan' is the steps for removing the 'Availability Database' from AG , if you shall commit any mistake during implementation. – Md Haidar Ali Khan Oct 4 '16 at 10:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.