Post reply

Two beginner Availability Groups questions

  • January 24, 2020 at 8:58 pm

    #3720297

    Hello experts,

    I'm an accidental DBA who recently joined a new group, and I'm learning how to work with availability groups. Luckily, the more senior guys are patient, but I want to get up to speed asap and stop feeling ignorant about AGs.

    I have two noob AG questions I hope someone can help me with:

    Question 1: Sometimes I try to add a db to an AG with Automatic Seeding or Join Only, and it seems to hang partway through the process. See attached photo for the illustration of the behavior. I have had some success by

    1. doing a full and log backup of the primary db,
    2. copying those backups to the secondary nodes,
    3. restoring the backups
    4. adding the primary db to the AG

    Even when those steps work, though, SSMS locks up and I have to end the task from Windows. Obviously I must be doing something wrong. Can someone let me know why the add would hang in that situation, and how I can learn to do the add properly?

    Question 2: I have a primary database whose data and log file sizes I want to increase. However, after doing the increases, I go back to check the properties and the database has gone back to its previous data and log file sizes. I checked Auto Shrink and it is False, but maybe I am missing something? Or maybe the size resets have to do with AG?

    Thanks for any help!

    -- webrunner

    • This topic was modified 4 years, 10 months ago by  webrunner. Reason: Format fix
    • This topic was modified 4 years, 10 months ago by  webrunner.

    Attachments:
    You must be logged in to view attached files.

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • January 25, 2020 at 4:51 pm

    #3720389

    I never use automatic seeding - even on databases that are small enough.  The process I use - and one that has not failed yet...is:

    1. Backup database on primary
    2. Backup transaction log on primary
    3. *** stop transaction log backups on primary ***
    4. Restore database on secondary
    5. Restore transaction log on secondary
    6. Join database to availability group
    7. Restart transaction log backups on primary

    If any transaction log backups are performed between that time you start the restore on the secondary to the point where you have completed restoring the transaction log backup, then you have to restore those additional transaction log backups that have been taken to bring the secondary up to the same point in time as the primary.

    In some cases (for larger databases) - I will start the restore on the secondary and keep the transaction log backups executing.  Once the restore has complete I then start restoring the available transaction log backups - then, I disable the transaction log backups for that database - copy the latest available and restore those, then join...

    As to why SSMS is locking up - not sure...I guess it depends on whether you are using automatic seeding or not.  If you are - then SQL Server is performing the backup/restore operation and that is taking a long time.

    What version of SSMS are you using - it could also be related.

    As for your second question...not related to AG - something else is blocking your ability to change the values.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • January 26, 2020 at 12:32 pm

    #3720444

    For  your second question - Is there anything in the SQL log files about the failed increase?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply