none
SQL SERVER – AlwaysOn Automatic Seeding – Database Stuck in Restoring State

    Question

  • Hi,

    After adding a database to the AG, node 1 says SYNCHRONIZED, but on node 2 the database is still in a restoring state.

    The AG Dashboard says:

    - Data synchronization state of availability database is not healthy

    - Secondary database is not joined

    Note: There are a lot of SQL logins for this database that are present on node 1, but not on node 2.

    Any help greatly appreciated. Thanks!

    Tuesday, April 23, 2019 12:18 PM

All replies

  • Hi,

    Is there anything in the SQL error log for this?  The account that the SQL Agent runs under will need connect permission to the endpoint for AlwaysOn so this may be the reason.  If it is then the fix is to run the below on both SQL servers:

    GRANT PERMISSION ON ENDPOINT::Hadr_Endpoint TO [USERNAME]

    The SQL users not existing on SQL2 won't stop it adding to the group, however you do need to script those over for the databases to be accessible after a failover:

    https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server

    Creating them manually wont work as the SQL level logins have their own GUID.

    Let me know if there is anything else in the error log though.

    Thanks,

    Matt


    Tuesday, April 23, 2019 1:15 PM
  • What do these give you?

    select start_time_utc, end_time_utc, estimate_time_complete_utc, total_disk_io_wait_time_ms, total_network_wait_time_ms, failure_message from sys.dm_hadr_physical_seeding_stats

    GO select current_state, failure_state_desc from sys.dm_hadr_automatic_seeding


    Tuesday, April 23, 2019 1:16 PM
  • Thanks for your feedback.

    The error log:

    Error: 911, Severity: 16, State: 1.
    Database 'my_db' does not exist. Make sure that the name is entered correctly.
    Processed 1610704 pages for database 'my_db', file 'my_db_Data' on file 1.
    Processed 74 pages for database 'my_db', file 'my_db_Log' on file 1.
    Starting up database 'my_db'.
    The database 'my_db' is marked RESTORING and is in a state that does not allow recovery to be run.

    Both servers are identical and other databases can be synchronized without any issues.

    Tuesday, April 23, 2019 1:32 PM
  • First query does not return any records.

    Second query returns:

    Current_State  failure_state_desc

    FAILED, Seeding Check Message timeout

    ...

    Tuesday, April 23, 2019 1:36 PM
  • Is there anything in the SQL Server Error Log?

    The timeout message you are getting could be because the login your SQL Server runs under on your primary does not have connect access on your secondary.

    You may need to drop the database from your AG and then try to re-initialize it again.

    Tuesday, April 23, 2019 1:40 PM
  • No, nothing in the error log that points to a related problem.

    We have removed the database from the AG and re-joined it, but still same issue.

    Tuesday, April 23, 2019 1:43 PM
  • This sounds permissions related.  Can you ensure that the SQL service and agent service user accounts have full control to the file locations of the database?  I assume you're running a custom account for these?

    Are the SQL servers on the same subnet and same site?

    Tuesday, April 23, 2019 1:44 PM
  • how big is this database? You may need to add a empty database newly created and backed up to see if you an get any database added to your AG. Add this using the join method.

    Right now, its hard to say if it is a generic connection issue, or an issue with automatic seeding.

    Tuesday, April 23, 2019 1:46 PM
  • What about restoring the database on the second node manually, adding the necessary logins and then trying the sync?

    We are on SQL Server 2016 SP2-CU6
    • Edited by Roberto_16 Wednesday, April 24, 2019 8:59 AM update
    Wednesday, April 24, 2019 7:43 AM
  • Have you checked the file system permissions to ensure the SQL service has full control?  This does sound permissions related if you're using a custom account for SQL and SQL Agent?

    Wednesday, April 24, 2019 10:05 AM
  • Yes, both nodes have full control and we are using a custom account for SQL and SQL Agent.
    Wednesday, April 24, 2019 10:12 AM
  • This on primary node or secondary node ?

    https://social.technet.microsoft.com/wiki/contents/articles/37872.sql-server-installation-on-centos-linux.aspx

    Wednesday, April 24, 2019 12:20 PM
  • Have the MDF and LDF files been created at the destination server?
    Wednesday, April 24, 2019 1:47 PM
  • No. What's wrong with restoring a fresh backup on the secondary server like I mentioned earlier?
    Wednesday, April 24, 2019 2:28 PM
  • Again, that sounds permissions related then. 

    You can do a fresh backup/restore as you mentioned, it is just more manual work.   If you do this then you will need to complete the process in-between log backups on the main database - if a log backup goes on during the process then the LSNs will be out of sync and you'll have to start again.  

    However - if the automatic seeding can't write files to the destination server then it may also not be able to send over any further changes.  So doing a manual seed may be the only data you get there...so it might not be much use.  Feel free to try it but I would check the health of the group afterwards...

    But the process for manual is to restore a full backup on the destination (with no recovery option), then a log backup restored (again, with no recovery option). Then you can add it to the group.

    I know you've checked over permissions but double check the permissions on the DATA and LOG folders against the SQL and SQL agent service accounts...check at each level down the hierarchy as this really seems to be the underlying issue.

    Let me know if you need anything else.

    Thanks,

    Matt


    Wednesday, April 24, 2019 3:12 PM
  • select *
    from sys.databases
    where name = ‘YourDatabaseName’

    Share output

    Thursday, April 25, 2019 5:29 AM
  • Did you make any progress with this?
    Monday, April 29, 2019 11:47 AM