SQLTeam.com | Weblogs | Forums

AlwaysON Basic Availability Group configuring on Sql Server Standard 2016

Availability group dashboard will shows you the health of your AG.

Thanks Ahmed for all you help which helps me to move forward.
Right now my database shows Synchronized in both the server next to database name, this is normal or Primary shouldn't show Synchronized and just normal like other database name only?
The reason, i am asking because in Primary, My regular Database backup didn't happened and this was happened earlier too as soon as i removed Availability group and it started to show normal not Synchronized and database nightly backup works fine so i am just curious what i have to do for any AG Group database Primary DB backup work?

Thanks for your help!

Synchronized means everything is up to date - synchronizing would tell you that it is catching up and may or may not be an issue.

To answer some of your questions:

Primary as backup is ok - unless you license the secondary node then performing backups on that node is not supported and I don't believe a BAG would allow that anyways.

The backups will not affect the status - however, other maintenance can and will have an impact. For example, rebuilding an index will need to be transferred to the secondary and could cause the send queue and redo queues to back up. This is normal but something you should be aware of...

The easiest way to check is to look at the dashboard (you have to add columns to the report to see the send queue, redo queue and other related fields). The report would identify any issues with any databases in the availability group.

A listener is not required - but the listener allows your applications to connect to the node currently hosting the database. Without a listener you have to modify your connections when you fail over.

As for your issue with the backup - that depends on how you have implemented them. Agent jobs do not automatically get created or moved to the secondary, so if the database is on the secondary node those agent jobs on the primary node will not execute successfully.

Thanks Jeff.
Somehow my Primary database didn't get back up (Full backup - nightly job) and that also happened previously.

I didn't see any error or message into log.

Last time when i deleted AG group then started to take the FULL backup.

TLog backup running fine that we are using via Sql Agent job.

Full nightly backup we are using 3rd party tool.

Any idea?

Thanks for your time and great help!

What is the preferred replica for backup?
why are you using 2 different ways to backup full and logs?

I would suggest perform both Full and Log backups from either SQL agent or your third party tool.

Thanks Ahmed.
We have set up Primary Replica for backup while i was setting up AG.
I am new here, we have set up this way to take FULL backup through 3rd party backup software and TLog backup we set up through Ola script using Sql Agent.
Only issue when the database is in Synchronized state for Primary then i think it's not taking backup.

You need to follow up with the 3rd party vendor to find out why they are not taking backups when the database is in an availability group. This has nothing to do with SQL Server - which doesn't have a problem running backups on either the primary or secondary.

Jeff,

Ok, Thanks for your prompt response.
I am doing 1st time so just checking if anything with sql related.

Do i have to check with FileSahre Qurom my OPs team configured during Cluster configuration?

I would verify the cluster configuration myself - but that really depends on how well your system team understands setting up and configuring windows clusters. As long as you have enough quorum votes for the cluster then I would not worry about it.

Ok, Thanks

Thanks everyone for your help!