I am facing below issues while working with High Availability Group

* While executing PowerShell script as described in below link, getting error

    https://msdn.microsoft.com/en-us/library/hh213078.aspx

System.Data.SqlClient.SqlError: The log in this backup set begins at LSN Which is too recent to apply to the database.

* So i skipped log restoration on seondary server and only restored database, it worked but

          * Now database in Availability Group showing warning sign

           * In Primary server, database is showing Synchronized state and but in Secondary server it is not showing any state, Warning sign may be because of that, but i don't have any idea

It would be great if i can get some clue to get resolution for this issue.

Hello Manish,

The error you got was due to mismatch/lack of a transaction log. The state that you have the database on your secodnary at this point cannot be added to the availability group, so the best thing you can do is to initialize the database from scratch (on the secondary) - you can use again the Powershell from your link (the one that is createing FULL and TLOG backup) or you can use Management studio for the same. Just be carefull that you do not have any other transaction log backups running while you are initializing (probably you have automated backup solution that is creating TLOG backups).

Hello Ivan,

Thanks for your reply.

I have deleted database and again recreated it on primary server and then executed the script as given in below link

Add a Database to an Availability group,

But now database is in "Restoring" state. It says it should be done in Recovery Mode. So i tried with Log restore in WITHOUT specifying -NoRecovery option. So database restored successfully but not added to Availability database as error says, Database should be have NoRecover option.

So i am not sure what i am  missing here.

Hello 

OK, I am not sure I got the entire picture. Do you have an availability group already setup in SQL Server, so you can add a new database to it, or you want to create a new availability group that this new databae to be member of?

If it is the second option, then I can suggest you use the SQL Server Management Studio, that will guide you through the process and do everything for you (a link for that would be here). Before that, you should create the database on the Primary Server in FULL recovery mode and create a FULL backup (anywhere on the drive). 

Let me know if you have a different setup/requirement so I can further help. 

1st Option.

Availability group already setup in SQL server.

By default Recovery mode is FULL.

Do you mean i should not follow the scripts defined in MSDN link?

I am not  saying that you should not :-) I was just pointing to a guide that includes visual guidance, as the MSDN articles do not contain such. 

So, if you already have the availability group, let me try a quick walk-through the process that should take place (I am sorry if I am too detailed):

1. Create the database on the primary instance

2. Create a FULL and then TLOG backup of that databases

3. Go to the secondary and restore the full and then the TLOG using the NORECOVERY option (this is a must)

At this point you should have a database that is online on the primary instance and InRecovery (SSMS shows Restoring...) database on the secondary. 

If you look at the powershell script (from your first link), the above steps are the first 8 lines. The last two should actually add the database to the AG, as long as all requirements are in place and also start the data movement. 

The 9th line of the powershell script is for adding the DB to the AG (hence its status - Syncronized) and the 10th - adding the DB to the AG on the secondary instance, where the database must be in Restoring state. Once you execute the 10th line and all requirements are met, your database will be added to the AG and data movements started (state should be Syncronizing). If you go over the script again, can you confirm that you can execute everything until line 9 and that you get the error on line 10?

Hi Ivan,

Thanks

I was on leave, so were not able to reply you since 2 days.

Till yesterday, everything was working till step 9

From this morning i am getting error on step 9 and 10. Not able to get the exact reason for the same.

Add-SqlAvailabilityDatabase : Database 'TestAG' cannot be joined to availability group 'AG1'.  The
database is not an availability database on the availability group.
At SQL-AddDatabaseToAG.ps1:37 char:1
+ Add-SqlAvailabilityDatabase -Path $MyAgPrimaryPath -Database $MyDatabase
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (TestAG:String) [Add-SqlAvailabilityDatabase], ItemNotFoundException
    + FullyQualifiedErrorId : ItemNotFound,Microsoft.SqlServer.Management.PowerShell.Hadr.AddSqlAvailabilityGroupDatab
   aseCommand


Add-SqlAvailabilityDatabase : Database 'TestAG' cannot be opened. It is in the middle of a restore.
At \SQL-AddDatabaseToAG.ps1:40 char:1
+ Add-SqlAvailabilityDatabase -Path $MyAgSecondaryPath -Database $MyDatabase
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Add-SqlAvailabilityDatabase], SqlException
    + FullyQualifiedErrorId : ExecutionFailed,Microsoft.SqlServer.Management.PowerShell.Hadr.AddSqlAvailabilityGroupDa
   tabaseCommand