Post reply

Basic Availability Group in Sql Server 2016 Standard edition setup

  • December 23, 2019 at 7:50 pm

    #3710113

    Hi,

    I am trying to setup Basic Availability Group in Sql Server 2016 Standard edition SP-2-CU11 and i'm having few issues/questions:My Primary DB is encrypted with Master Key.We have built another Sql server for Always ON but no user DB yet.On Primary Sql server DB, We are taking Transaction Log Backup every hour and FULL DB every night.1) I can't see Basic Availability Group option in Create New Availability Wizard, I can only see the drop down for Cluster Type is Windows Server Failover Cluster and Check box for Database LevelHealth Detection and Per Database DTC Support2)Another issue is I can't select the Primary DB as status shows Password Required and when i am trying to click on, getting this message:This DB is Encrypted by Master Key, You need to provide valid password when adding it to the availability group.3) Do I need to create same USer DB in Secondary Server before i set up AlwaysON and configure

    availability group?4) Do i have to take FULL and Transaction log backup again just right before creating

    availability group?I have already enabled the AlwaysON features in both the sql server using Configuration Manager.

  • December 24, 2019 at 8:10 pm

    #3710290

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • December 26, 2019 at 10:14 pm

    #3710606

    Hi there. I have some experience with AGs, but I tend to use Powershell and manual scripting because the wizard can be quite.... oversimplified. I'll take your questions in order --

    1) I can't see Basic Availability Group option in Create New Availability Wizard, I can only see the drop down for Cluster Type is Windows Server Failover Cluster and Check box for Database LevelHealth Detection and Per Database DTC Support

    -- I'm not quite sure which part of the wizard you're referring to. The first step is to name the AG. After that, if there are no databases available to add to the AG, the wizard will just ask you to specify AG partner replica server names. If you could provide more detail here, that would be most helpful.

    2)Another issue is I can't select the Primary DB as status shows Password Required and when i am trying to click on, getting this message:This DB is Encrypted by Master Key, You need to provide valid password when adding it to the availability group.

    -- Yep, this is where the wizard kind of falls down. For my AG instances, I export the db encryption certificate to a file and import it to the other AG replicas before creating the AG. I tend to also export the service master key from the "primary" replica and import it to the other replicas. This can prevent issues stemming from encryption and AG fail-overs.

    3) Do I need to create same USer DB in Secondary Server before i set up AlwaysON and configure availability group?

    -- Create? No. You will need to take a FULL backup of your database on the primary and a TLOG backup of this same db before attempting to restore both of those backups (in order) to the replica. On the replica, leave the database in the "recovery" state. This will set it up to start synchronization.

    4) Do i have to take FULL and Transaction log backup again just right before creating availability group?

    -- Not necessarily. The key question to ask yourself when restoring to the replica is 1) was there any activity since I restored the backups to the replica, and 2) were there any automated TLOG backup jobs run on the primary since I restored the backups to the AG replica? If not, the AG replication should be ok with synchronizing the databases from the last TLOG restore.

     

  • January 3, 2020 at 8:31 pm

    #3712252

    Thank you so much for your detailed answer.

    For #1 ==> I am using SSMS => (see attached image)

    Always ON high Availability => Availabilty Groups => New Availabilty Group Wizzard =>

    Specified Availabilty Group options:Specified Availabilty Group options

    Availabilty Group Name: AG1

    Cluster Type:Windows Server FailOver Cluster

    Check mark box:

    Database LEvel Health Detection

    Per Database DTC Support

     

    For #2 ==> I m working with the group to get the password and hoping if works then i can move forward

    For #3 ==> We are taking FULL Backup every day and TLOG Backup every an hour on PRIMARY so i will be fine right?

    For "On the replica, leave the database in the "recovery" state. This will set it up to start synchronization." ==> This will during Set up/ configuration, right?

    For #4 ==> We are running FULL Backup every day and TLOG Backup every an hour on PRIMARY so it's better to disable the T-Log backup job during that time?

     

    Thank you for your help!

  • January 6, 2020 at 6:22 pm

    #3712768

    Any thoughts for above>

    Greatly appreciated your guidance!

  • January 6, 2020 at 6:41 pm

    #3712790

    I can't speak to #1 but I've worked with an AG  in my environment ( I just didn't set it up, but I have had to recover it )

     

    #3/4 personally, I disabled the tlog job, took a new full and new log copied them over to the server and restored them.

    I believe I used copy-only backups and they worked just fine ( so your LSN chain won't be disrupted ).

     

  • January 9, 2020 at 3:27 am

    #3713846

    Thanks.

    I tried to restore the full Backup Copy_only DB and Transaction Log backups, Restored successfully.

    but having issue when i tried to Join the DB as i am getting following error:

    "Remote copy of database "SqlAGDB" has not been rolled forward to point in time that is encompassed in the local copy of the database log"

    I tried to check my TLog and i have restored all TLog.

    I ran few times my Restore with FULL Backup and TLog backup.

    Thanks for your help!

  • January 29, 2020 at 4:05 pm

    #3721272

    Thanks Everyone for your help!

  • January 29, 2020 at 11:17 pm

    This was removed by the editor as SPAM

  • April 25, 2020 at 9:10 am

    This was removed by the editor as SPAM

  • July 10, 2020 at 11:33 am

    This was removed by the editor as SPAM

  • July 22, 2020 at 9:03 am

    This was removed by the editor as SPAM

  • January 11, 2021 at 10:02 am

    This was removed by the editor as SPAM

Viewing 13 posts - 1 through 12 (of 12 total)

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