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
Learn How to Efficiently Administer SQL Server Instances – Get the Course Today!
Check my online course on Udemy titled “Essential SQL Server Administration Tips” (special limited-time discount included in link).
Learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!
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 🙂
Featured Online Courses:
- Essential SQL Server Administration Tips
- SQL Server Fundamentals (SQL Database for Beginners)
- Introduction to Data Science and SQL Server Machine Learning
- The Philosophy and Fundamentals of Computer Programming
- Introduction to Azure SQL Database
- SQL Server 2019: What’s New
- Entity Framework: Getting Started (Ultimate Beginners Guide)
- How to Import and Export Data in SQL Server
- Get Started with SQL Server in 30 Minutes
- A Guide on How to Start and Monetize a Successful Blog
Related SQL Server Administration Articles:
- What are SQL Server Always On Availability Groups?
- How to Find the OS Version of your SQL Server Machine – Single vs Many Machines
- Essential SQL Sever Administration Tips
- How to Patch a Standalone SQL Server Instance
- The SQL Server Browser Service and UDP Port 1434
- The Maximum Number of Concurrent Connections Setting in SQL Server
- Top 10 SQL Server DBA Daily Tasks List
- There is no SQL Server Failover Cluster Available to Join
- Encrypting a SQL Server Database Backup
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
Artemakis Artemiou is a Senior SQL Server Architect, Author, and a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA).