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

Assumptions

• 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