Question on AlwaysON

Mikhail Firsov 1,856 Reputation points
2022-07-12T14:25:14.32+00:00

Hello,

One more question on SQL Server 2019, please: I usually use backup/restore when it comes to creating AlwaysON availablity groups and adding secondary replicas but this time I decided to try the new default method as illustrated here:
219942-0.png

The New Availability Group wizard completed without single issue (all OKs) but

1) there were not any backup/restoring or seeding-related events in the SQL Server log as described in the article
2) the secondary replica was not created on the secondary server (the db/log paths are exactly the same on both servers)
219961-02.png

Here's the screenshot from the DMVs:
219962-01.png

As far as I get it the primary server (SQL1) didn't even try to start initial seeding (providing the select * from sys.dm_hadr_automatic_seeding returns nothing). There are no corresponding errors in the logs on both SQL1 and SQL2.

What can be wrong with this seeding and what can I do in this case???

Regards,
Michael

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,259 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. YufeiShao-msft 7,036 Reputation points
    2022-07-13T08:10:47.553+00:00

    Hi @Mikhail Firsov ,

    Did you have this database joined to AG:

    https://www.sqlshack.com/automatic-seeding-in-always-on-availability-groups/

    From your picture, your database does not seem to have connect privileges to the database

    Join a secondary database to an Always On availability group

    -------------

    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.

    0 comments No comments

  2. Mikhail Firsov 1,856 Reputation points
    2022-07-13T09:06:32.947+00:00

    "Did you have this database joined to AG:" - when the wizards finishes it does join the database to a AG:
    220285-14.png

    • if the wizard reports Success in "Joining secondaries to AG..." how can this -"database does not seem to have connect privileges to the database" - be???

    Trying to add the db to AG manially raises the following error:
    220296-15.png

    -this error seems to be correct since the secondary SQL Server (SQL2) does really not have the secondary database - the main question is why does the initial seeding (the default option) doesn't create this database...


  3. Mikhail Firsov 1,856 Reputation points
    2022-07-14T09:32:45.507+00:00

    " you need to verify the endpoints is working" - how can I do that?

    220743-21.png

    220744-22.png