SQL Server Availability Groups Article on SQLNetHub

How to Add a Database to a SQL Server Availability Group Using T-SQL

by

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.

 

How to Add a Database to a SQL Server Availability Group Using T-SQL - Article on SQLNetHub

 

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

 

Strengthen your SQL Server Administration Skills – Enroll to our Online Course!

Check our online course on Udemy titled “Essential SQL Server Administration Tips” (special limited-time discount included in link).

Via the course, you will 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!

Essential SQL Server Administration Tips - Online Course with Live Demonstrations and Hands-on Guides
Lifetime Access / Live Demos / Downloadable Resources and more!

Learn More

 

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.

Example:

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.

Example:

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.

Example:

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:

 

Related SQL Server Administration Articles:

Did you find this article useful and interesting? Feel free to leave your comment!

If you enjoy my SQL Server administration tips and articles, I have something special just for you. It is one of my eBooks and it is called “Administering SQL Server“. Check it out!

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: 1 Star2 Stars3 Stars4 Stars5 Stars (4 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub



Views: 13,436