question

VijayKumar768 avatar image
0 Votes"
VijayKumar768 asked Yufeishao-msft edited

AlwaysOn Availability Group SQL Server 2016

Hi,

I am planning to add DB (5TB) to secondary server.

This is SQL Server 2016 Availability Group (Primary+1 Secondary).

My question is?


If i Restore full backup on Secondary (With Norecovery) then enable Seeding. Doesn't works? if yes how?

Just i am planning to do some experientment.





sql-server-generalsql-server-transact-sqlsql-server-migration
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Yufeishao-msft avatar image
0 Votes"
Yufeishao-msft answered Yufeishao-msft edited

Hi @VijayKumar768,

why you want to restore backup file on the secondary replica?

about seeding, you can refert to :
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/automatic-seeding-secondary-replicas?view=sql-server-ver15#create-an-availability-group-with-automatic-seeding


It is possible to combine the traditional backup, copy, and restore with automatic seeding.
In this case, first restore the database on a secondary replica including all available transaction logs. Next, enable automatic seeding when creating the availability group to "catch up" the secondary replica's database, as if a tail-log backup were restored


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

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.




· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,

Yes i tested in my test lab.


We already have availabality group.
Just adding another DB to AG.

First i restored full backup+couple of t-logs on secondary with no recovery.
Then from SSMS when i try to select Auto seeding and click next, i got error like, DB already exists on secondary.


0 Votes 0 ·

auto seeding can create a database for secondary replica, you have restored the database on the secondary replica manually, so your error will occur, the database already exists, your operation has actually performed a auto seeding.

0 Votes 0 ·