In some cases, while trying to add a database to a SQL Server availability group using the wizard in SQL Server Management Studio, you might get some warnings or errors. There is a chance that these warnings/errors won’t let you continue and add the database.
For example, a possible warning could be the below:
This database is encrypted by database master key, you need to provide valid password when adding it to the availability group.
So, let’s consider the following scenario: You have two or more AG-enabled database servers, on which there is one (ore more) availability groups. On the current primary replica node, you have a database which you want to add to the existing availability group. However, the wizard does not let you do this because of some conditions such the above warning message.
Don’t worry because there is another way to achieve this. It is a little bit more “manual” as it involves T-SQL, but it is very simple.
The procedure to add a database to a SQL Server availability group, when it is not possible to add it using the wizard is:
Step 1. On the primary node, add the database to the availability group
-- On Primary Node USE MASTER; GO ALTER AVAILABILITY GROUP [AGNAME] ADD DATABASE [DBNAME]; GO
Step 2: Perform a full backup of the database on the primary node/replica
You can easily do this using the SSMS database backup wizard or with a T-SQL script.
Here’s a simple example below:
BACKUP DATABASE [DBName] TO DISK = N'Path_to_Store_Backup_File\BackupFileName.bak'; GO
Recommended: Our SQL Server multi-instance database administration and security tool is named “DBA Security Advisor” and you should definitely check it out!Try DBA Security Advisor free for 30 days
Step 3: Restore the full database backup on the secondary node along with specifying the “WITH NORECOVERY” option
The NORECOVERY option specifies that a rollback of the database will not take place, thus allowing to restore even more records.
Note: You must be very careful when restoring databases. For the context of this article, first you need to make sure that the same database does not exist on the secondary node, prior to run the above-mentioned restore operation.
You can restore the database using the SSMS restore database wizard, along with specifying the NORECOVERY option or use a T-SQL statement.
RESTORE DATABASE [DBName] FROM DISK = N'Path_Database_Backup_File_Stored\BackupFileName.bak' WITH NORECOVERY; GO
Step 4: Perform a transaction log backup of the database on the primary node/replica
The next step, is to go back again on the primary node/replica and perform a transaction log backup of the database.
Again, you can do this using the SSMS backup database wizard or using a T-SQL statement.
BACKUP LOG [DBName] TO DISK = N'Path_to_Store_Log_Backup_File\LogBackupFileName.bak'; GO
Step 5: Restore the log backup on the secondary node along with specifying the option “WITH NORECOVERY”
The next step, is to restore the log backup on the secondary node, on the previously restored database (see Step 3), again with the “NORECOVERY” option.
If you prefer doing this using T-SQL instead of the SSMS restore database wizard, below you can find an example of how the T-SQL script looks like.
RESTORE LOG [DBName] FROM DISK = N'Path_Log_Backup_File_Stored\LogBackupFileName.bak' WITH NORECOVERY; GO
Step 6: On the secondary node, add the database to the availability group by altering it
The final step, is on the secondary node to add the database to the availability group by using the ALTER command.
Here’s an example of the T-SQL script:
USE MASTER; GO ALTER DATABASE [DBNAME] SET HADR AVAILABILITY GROUP = [AGNAME]; GO
Step 7: Confirm that all AG replicas are synchronized
It goes without saying that after all the above, you need to verify that all database replicas in the availability group are synchronized. You can do this via SSMS, by right clicking on the availability group under “Always On High Availability” and selecting “Show Dashboard”. There, you can see if everything is OK. If it is all green, and you see the wording “no data loss” then all is good 🙂
Did you find this article useful and interesting? Feel free to leave your comment!
Subscribe to our newsletter and stay up to date with our latest articles on SQL Server and related technologies!
Check out our latest software releases! All our software tools have 30-day Trial Versions which are free to download.
Rate this article:
Reference: SQLNetHub.com (https://www.sqlnethub.com)
© 2018 SQLNetHub