Wednesday, April 22, 2015

PowerShell Script: Adding Database into Always On Group

Every once in a while, I have  a new database that I need to put into an existing Always On group. So I have decided to create a PowerShell script that would do just that. The script can be found here.

The script accepts the following parameters:

  • DBName – The name of the database that we need to put into Always On Group.
  • DAGName – The name of the Always On group
  • ReplicaNode – Either the primary replica or one of the secondary replica of the Always On Group
  • BackupLocation – Network share location in which each of the replica node can read or write the database backup


  • The database is located in the primary replica node of the Always On group.
  • The Always On group has already been created, but the database has not been joined to the group.

Script Logic

In summary, the script would do the following:

  • Information gathering
    • Find Always On primary replica
    • Find Always On secondary replica(s)
  • Run some validations
    • Check to see if the database exists
    • Database is not one of the system database (master, model, tempdb or msdb)
    • Database is online
    • Database is in full recovery mode
    • Database is currently not in any Always On group
  • Backup the database (full and transaction log) from the primary replica into the backup location
  • Restore the database using the full and transaction log backup onto each of the secondary replica
  • Add the database into Always On group on the primary replica and each of the secondary replica

No comments:

Post a Comment