1

We have a small database that was created on a primary node. But by the time the application was deployed and it was time to move the db into the AG I noticed that it became the secondary node. For client connectivity I gave the listener name. The application has been installed successfully. Is is still possible to move the database into the AG (because the former primary is now the secondary)

Please advice

  • I am not sure how would "which node is primary" affect the AOAG. Just make sure application is connecting via listener – Shanky Jan 10 '17 at 15:42
  • To clarify you are saying you only restored to the primary without added the database to the AG, right? And now that "primary" is the secondary. – MrTCS Jan 10 '17 at 15:44
  • Yes I created the database on primary - sql06. But by the time I wanted to move it into the AG, SQL06 shows as the secondary node. – user2923332 Jan 10 '17 at 15:51
1

You can only add a database to an AG from the Primary node. Your options are to back and restore the database to the new primary, or fail back over to SQL06 to make it the primary, then add it to the AG.

1

It is still possible, and there is very little work that needs to be done to accomplish this.

Create a new availability group on the instance that currently holds the database (you've stated this to be SQL06) and add that single database as the only database in the new AG. Have the wizard do full synchronization for you - this will populate the database on all of the replicas.

Set the replicas to be synchronous and wait for them all to be synchronized. Fail the new AG over to the replica that is currently hosting your original AG that this databases was to be part of. Once failed over, remove the new AG. Once it is properly removed, add the database as part of the original AG you wanted it to be on, choose to join only in the wizard.

This shouldn't take long and doesn't require much manual work or downtime.

0

The issue is that the DB is online on a secondary node of the AG, and you want to add it to the AG. This can be done quite easily, albeit with a small amount of downtime. This method is based on the steps to Manually Prepare a Secondary Database for an Availability Group.

Step 1) Take a full backup of your database, and restore it WITH NORECOVERY to all other AG nodes. (You can also use an existing recent full backup.)

Step 2) Take a transaction log backup of your database, and restore it WITH NORECOVERY to all other AG nodes. (If you used a recent full backup in step 1, you'll need to apply all transaction logs since then to bring the restored DB current.)

Until this point, steps are identical to manually preparing the AG secondary.

Step 3) Take a tail log backup of your database. This is done by performing BACKUP LOG....WITH NORECOVERY. This will take your database offline and put it into "Restoring..." status.

Step 4) Restore the tail log backup to only the AG primary node, using WITH RECOVERY, which will bring the database online & accessible on that node.

Step 5) Proceed with the steps to join a database to the AG. This can be done either via the SSMS GUI or T-SQL or PowerShell.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.