Database On a Node3 Replica

Question

Forum ,

I have a 2 node(Node1/Node 2) cluster SQL 2016 always on setup which is working as expected , as per the business requirement we wanted to introduce node 3 for adhoc reporting. The 3rd node was added to the windows cluster and that too working during the failover. But when we try to add DB's to the 3rd node one at a time (as the DB's are huge and we dont want all at once)all the DB's in the availability group (Node1/Node 2)are being added to the Availability Databases . I would like to do one DB at a time not all at once adding them to the Node 3 always on. Is there are work around or did anyone has a requirement to perform this kind of scenario.

Thanks in advance,

Answer 1

Hi NoviceDBA9-9405,
In addition, please go to SQL Server instance that hosts the primary replica, then expand the Always On High Availability node and the Availability Groups node->Right-click the availability group->Select the Add Replica to add new secondary replica.

Please refer to this tutorial for more details.

Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

You can add a single database at a time, but it needs to be completed manually.

Restore the databases on the new node from the latest full backup, leave the database in a restoring state. Apply the latest differentials and logs, leaving the database in a restoring state. Alter the database and add it to the availability group.

Docs Reference Available.

-Sean