AlwaysON Basic Availability Group configuring on Sql Server Standard 2016

1

Hello,
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.

2

This is what you need

3

Thanks Ahmed.
Our Database is not TDE encrypted. I checked with following query:
SELECT * FROM sys.dm_database_encryption_keys -- No Results

How about your guidance for other questions?
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 Support

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

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

4

You have options to create or not.
Automatic seeding will create the database on the secondary replica itself(Database file locations should be same on both replicas).
Yo can manually restore the primary database on secondary replica and use JOIN ONLY option during AG setup.

5

Thanks Ahmed.
What's your thoughts on #sql2012I 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

6

That's how it looks.

7

Thanks Ahmed.
Because I was looking Brent 's Doc and reading other docs, it was showing the check mark box but in set up it's not showing, only showing he drop down for Cluster Type is Windows Server Failover Cluster and Check box for Database LevelHealth Detection and Per Database DTC Support.
So, I should select "Database LevelHealth Detection " , right?

8

Yes, you can select that.

9

Thanks Ahmed.
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!

10

I guess there was another log backup taken while your restore was being performed.

11

Thanks.
I included all the TLog backup but still getting error.
I have taken FULL Backup and applied the afterword next TLog backup
I have also tried with Last Full backup and all the TLog backup after that last Full backup.
I have taken FULL Backup COPY_ONLY and check the next TLog back from the Tlog folder and whatever was the latest one i have applied but still having same issue.
We are taking TLog backup every hour.
So as a example, I have taken FULL Backup COPY_ONLY at 12 PM and my next Tlog backup was 12:45 pm and when i do the restore at 1 PM, I am including FULL Backup COPY_ONLY at 12 PM and 12:45 TLog backup to restore.
What's wrong here?
Anything else i am missing?
Thanks for your help!

12

I also tried COPY_ONLY Full Backup and COPY_ONLY TLog backup and restored with NORECOVERY.
I am still getting error:

mirror database has insufficient transaction log data to preserve the log backup chain of the principal database. This may happen if a log backup from the principal database has not been taken or has not been restored on the mirror database.

13

The error is telling you the problem - one or more transaction log backups have occurred since you started the restore process on the secondary.

One way to avoid this issue is to stop the transaction log backups on the primary during the restore process on the secondary. This way you are assured that no further transaction log backups have occurred.

There is no reason to perform COPY_ONLY backups unless you also perform differential backups as part of your normal maintenance.

14

Thanks Jeff for your help and guidance.

I agreed with you that error is telling clearly but when i do the fresh Backup and even though also i tried with TLog backup and restore both the backup but still throwing same error.
So i tried fresh Full backup and Full Backup plus TLog backup. Only difference is i tried with COPY_ONLY (to avoid not to break the chain as we are taking nightly full backup and every hour TLog backup) backup and restore with NORECOVERY.

Earlier i also tried with Full backup and following multiple required TLog backup.

I have also disable the TLog backup job too.

I have deleted AG group and recreated during this process to start the fresh but no luck.

From your response:

  1. one or more transaction log backups have occurred since you started the restore process on the secondary - I am disabling TLog backup job and doing Full Copy_Only backup and restoring

  2. One way to avoid this issue is to stop the transaction log backups on the primary during the restore process on the secondary ==> Already tried few times to disable the TLog job

  3. There is no reason to perform COPY_ONLY backups unless you also perform differential backups as part of your normal maintenance ==> We are doing hourly TLog Backup so don't want to break the LSN chain

15

The LSN chain is not dependent on a full backup - the only thing affected by the full backup is a differential backup which is dependent on the latest full backup.

The only way that error occurs is if you have not restored all available transaction log backups up to the current point in time since the full backup you restored to the secondary. It is highly likely that either you are not restoring all available transaction log backups - or some other process is performing additional transaction log backups that you are not aware of...

check the backup history in MSDB to see if there are any other processes performing backups.

16

Thanks once again Jeff.
I am completely agreed with you and i will check again after my regular Sunday Full backup.
But biggest questions is that We have Daily Full backup and hourly TLog backup, so i am trying to avoid all this puzzle of missing Tlog, I have shutdown the Tlog backup and taken Full backup and restored into secondary still complaining. I have also tried both Full backup and Tlog backup after that i have disable the Tlog backup and then restored it still same issue.
most of the time i am deleting database on secondary and restoring after that too.

17

This seems to indicate a problem with your backups...

Can you confirm the schedule of full, differential and transaction log backups for this system?

18

We are not taking differential backup but you are asking to take fresh, diff and TLog backup, right?
That will be COPY_ONLY backup right?

Because we are only taking Nightly Full backup and Every hour TLog backup and which are running fine.

19

No - that is not what I am recommending.

If you are performing a nightly full backup - and hourly transaction log backups then you don't need to worry about COPY_ONLY backups at all. Again - COPY_ONLY is used to maintain the differential chain so that a differential backup taken after the COPY_ONLY backup will still tie to the normal full backup.

In your situation - one or more transaction log backups have taken place between the time you took your full backup and restored it to the secondary. In order to join the new database to the availability group you must restore all transaction log backups from the full backup through current point in time - if you miss one then you will get the error you are receiving.

So - either some other process is also backing up the transaction log or you do not have a full log chain and are missing one or more transaction log backups.

20

Thanks Jeff for your prompt response. It worked Finally, I was able to Restore Nightly backup and all TLog backup.
I was using Point in Time Recovery so i was using STOP AT but i removed it and it got restored and able to join the database.

Now both database (Primary and Secondary) shows Synchronized.

I also failed over and Secondary become Primary. I have also failed over again so now Primary DB is Primary back to and Secondary is Secondary back to.

During my BAG set up, I have selected PRIMARY as backup so it will be pk, right?

On my Primary and Secondary Instance Replica, next to Database shows Synchronized lo that's the normal right?

Like:

SERVER1

DB1 (Synchronized)

SERVER2

DB1 (Synchronized)

My regular nightly backup for Primary DB will run as it is, right? or it will effect because of Synchronized?

What's the best way to check secondary that up to date with primary or not?

Any other things i have to set up or check as part of post configurations?

I haven't set up Listener so i t will be ok, right?

I have Shared folder set up.

Thanks for your kind support and help to resolve the issue and for better guidance.