In this particular post, I am going to share steps for adding a database into an SQL Always On Availability group. This can be done both via GUI or PowerShell, but here my focus would be on PowerShell in order to make it simple and automated.
For this particular scenario, I am going to add a new database on an already existing AG. New database can be created or restored via a valid backup. I am assuming SQL AG is already setup and we are going to add databases in AG. There is plenty of information available around setting up your SQL AG Setting Up Always on AG for SQL Server.
Once you have created you SQL AG. It’s time to add databases. Now this can be done via GUI as well as PowerShell. Many of us (including me) may find GUI process as a real pain. First of all because it is manual and secondly it leads into other cluster related issues. So here I am going to show you how to add a new database in an existing AG via PowerShell script.
Now for this example, I am assuming a two node SQL AG. I am naming two nodes as sqlserver-0(Primary) and sqlserver-1 (Secondary). Also, for this example, I am going to add a new database in AG by creating a new database onto primary server. Let us name this database as Testdb01. We can also use a backup file to restore it to an existing database.
I will follow standard procedure to add a new database via SQL management studio. While creating or restoring a database important thing to take care of is to make sure the Recovery Model is set to Full. See screenshot below.
Now once the database is created on The Primary node, it’s time to move onto the secondary node. We can forget Primary node for a while and focus on the secondary node. Now before we begin, I would like to share briefly what we are going to do next. So basically we will be running a PowerShell script which will connect to Primary node and create a backup on a local share, then it will restore this backup back onto Primary node and then add it to Always on AG. It’s just that simple.
Now important step here is to create a network share which will be used as a backup path for sql database backups. Let us say we create it at F:\Backup and then share this folder with read/write permissions for SQL service account or SQL admin account.
PowerShell Script
As you can see from:
Line 1-11 we have set our variables.
In line 13 and 14 we are backing up primary instance from server-0 onto Server-1 (line 13). In line 14 same is done for transaction log file.
Line 16 and 17 are using the newly created backup on Server-1 to restore database onto secondary Instance (Server-1).
Line 19 and 20 are adding database into primary and secondary node of the availability group.
And that’s it. We are done. As easy as it is. However, do watch out for one thing which might trouble you. For example below error:
Add-SqlAvailabilityDatabase : The mirror database, “Testdb01”, has insufficient transaction log data to preserve the log backup chain of the principal database. This may happen if a log backup from the principal database has not been taken or has not been restored on the mirror database.
Make sure that there is no existing backup in the shared folder where you are going to create a new backup otherwise you will get a similar error as shown below. Delete any old backup file before you proceed. That would be another reason for you to create a separate folder for this purpose rather than using your default daily backups folder. If you run into this issue, before attempting again, make sure you go into Shared backup folder and delete old backups and also go into management studio of secondary node and delete the database which will be now showing as restoring.
This is it we are done. We have successfully added our new or restored databases into SQL Always on AG. This means now we are protected against any SQL failures. Databases will fail over to secondary node without any data loss. Also, this means now we have a second copy of our database available in case we accidentally delete a database. God forbid of course J.