SQL 2014 always on

"Since the Replicas can be accessed for Read Only purposes (opposite to Mirroring or Log Shipping solutions) many people start to use the Secondary Replicas for Reporting, reducing the load on the Primary Replica.

yeah off load backup task too !

during the setup process, what kind of process during setup and operation easy to fail?

did you try that if there are more than one secondary replica, you can turn off any replica and apply security patches, then put it back on to the AOG?

during the setup process, what kind of process during setup and operation easy to fail?

Process is quite easy and shouldn't fail:

1. Enable AlwaysOn feature
2. Create AG
3. Backup databases that you want to be in the AG
4. Restore them in all replicas
5. Add the databases to the AG
6. Create the Listener

I guess you already read the MSDN article about it, right?

did you try that if there are more than one secondary replica, you can turn off any replica and apply security patches, then put it back on to the AOG?

You don't need to turn off nothing or remove anything. Just apply the patches with the AG running. Don't forget that patches stops the SQL Server service just the AG won't be available until SQL Server service restarts.

"You don't need to turn off nothing or remove anything. Just apply the patches with the AG running. Don't forget that patches stops the SQL Server service just the AG won't be available until SQL Server service restarts."

but what if I don't know the impact of the security patch or SP for SQL server and I have to apply the patch one by one on each node, right?

AOG just a better cluster, so we can't just apply to one node and then apply it one bye one, verify it first if everything is ok then attach it back to the AOG group?

"I guess you already read the MSDN article about it, right?"

I read some third party article which is not godd !

but what if I don't know the impact of the security patch or SP for SQL server and I have to apply the patch one by one on each node, right?

That's why you have DEV and TEST environment. Don't be crazy to apply immediately in PROD environment.
But SQL Server patches are really very safe and easy to uninstall if you need to perform a rollback.

AOG just a better cluster, so we can't just apply to one node and then apply it one bye one, verify it first if everything is ok then attach it back to the AOG group?

AG is NOT a cluster. AG only needs a Windows Cluster because of the Quorum so it can check if other replicas are alive. You don't need a SQL Server Failover Cluster instance to have AG running. In fact, I won't recommend you to have so.

"But SQL Server patches are really very safe and easy to uninstall if you need to perform a rollback."

non. when deploy to production, still need a separate procedure for that as usually the TEST and dev environment do not have a AOG setup.

you can say SQL server patch is very safe but still need a AOG aware procedure. so we still need to apply for patch or SP one node by one node?

"AG is NOT a cluster. AG only needs a Windows Cluster because of the Quorum so it can check if other replicas are alive."

but the doc. said there are no single Quorum any more as all node is a total copy of the primary replica. it is not ?

so we still need to apply for patch or SP one node by one node?

Yes. Start with DR replica, if you have one, then the rest of Secondary Replicas and let the Primary Replica to be patched for the last.

but the doc. said there are no single Quorum any more as all node is a total copy of the primary replica. it is not ?

You're mixing concepts. Quorum is a resource for Windows Cluster. Replicas are copies of the databases. Don't try to co-relate them.

" Quorum is a resource for Windows Cluster. Replicas are copies of the databases. Don't try to co-relate them."

ok.

by this: https://msdn.microsoft.com/en-us/library/ff877884(v=sql.120).aspx

it said:

'The quorum for Always On Availability Groups is based on all nodes in the WSFC cluster regardless of whether a given cluster node hosts any availability replicas.''

this make me think about that. it is not..

"Yes. Start with DR replica,"

so in the DAG, we can have one DR replica, one primary but many secondary replica, but the DB replica also secondary replica, right ?

so during the patching/SP upgrade, there will be a moment where the patching replica do not have the same version as others for a week, as we want to see after upgrade that node, is everything else still stable, it is doable, right?

how about how we have AOG all in SQL2014, then 2 years later we try to upgrade to SQL 2016. so just upgrade the DR replica first then hold that for a week, so in our DAG, we have only one running SQL 2016 but the rest of them running SQL 2014, is it possible ?

so AOG is not based on the cluster any more? and setting up windows clusters doesn't related to AOG and they work independently ?

so AOG is not based on the cluster any more?

Always needs Windows Cluster. It doesn't need SQL Server failover cluster instances. You need to understand the difference between these two solutions.

so if we need to setup AOG, we also need windows clusters? but we don't configure SQL server failover cluster any more but configure AOG, so it just make the SQL cluster install in an much easier way ?

so if we need to setup AOG, we also need windows clusters?

Yes.

but we don't configure SQL server failover cluster any more but configure AOG, so it just make the SQL cluster install in an much easier way ?

What's more easy? A SQL Server failover instance is not that hard to install but for using AOG you don't need it. You can install a stand-alone SQL Server instance in each node of the Windows Cluster. This means that the usual single point of failure (storage) in SQL Server failover instances won't exist any more.

"This means that the usual single point of failure (storage) in SQL Server failover instances won't exist any more."

I don't understand this, please tell me more.

In a SQL Server failover instance the nodes share the same disks and this in a single point of failure, meaning if there's any problem with the storage the instance can't work anymore even if you try to failover it to any other node.
If you set up an AG environment with stand alone SQL Server instance, each instance will have their own disk so  this problem won't exist, i.e. if a disk of one of this instance has problems you can still failover to any of the other instances.

"f you set up an AG environment with stand alone SQL Server instance, each instance will have their own disk so  this problem won't exist, "
ok.

that's make sense, so from this point of view, AOG is better than clusters.

so the Windows cluster is for failover between SQL AOG replica ? just in this case SQL cluster is not need as AOG can do it in diff way.

that's why the AOG still not multi master. .. :):)

so the Windows cluster is for failover between SQL AOG replica ?

No. Is there only because of the Quorum disk. Is the only way the AG knows if the other replicas are alive or not.

that's why the AOG still not multi master

What that means?

"What that means?"

needs quorum disk also means one is primary and one is secondary/DR/replica, and then this mean the SQL AOG still use primary and secondary concept instead of the Oracle Mulit master.

"No. Is there only because of the Quorum disk. Is the only way the AG knows if the other replicas are alive or not.
"

so this means AOG make use of the Windows cluster;s quorum disk to know which one is primary and which one is the replica ?

needs quorum disk also means one is primary and one is secondary/DR/replica, and then this mean the SQL AOG still use primary and secondary concept instead of the Oracle Mulit master.

I don't know nothing about Oracle Multi Master feature so I can't compare.

so this means AOG make use of the Windows cluster;s quorum disk to know which one is primary and which one is the replica ?

Mostly to know which replicas are "alive" (online) so in case of failover it can failover to one of the available replicas.

"Mostly to know which replicas are "alive" (online) so in case of failover it can failover to one of the available replicas."

but you also said:

"In a SQL Server failover instance the nodes share the same disks and this in a single point of failure, meaning if there's any problem with the storage the instance can't work anymore even if you try to failover it to any other node."

cluster also use quorum disk, then this is the same or what you are saying this AOG use more than one Quorum disk , each AOG member has one?

You're mixing stuffs again. Quorum disk is to be used by the Windows Cluster. Is a very small disk (usually only needs few MB to work).
The single point of failure that was talking about it was the Data and Transaction log disks. If you loose those disks you'll loose the databases.
If you loose a Quorum disk you can add other one to have the Cluster back online and you won't loose any data.
Do you see the difference?

hi,

Just an update to you, I am reading this:

https://www.linkedin.com/groups/1778334/1778334-6242031205290774531?midToken=AQF46tSkwzf14Q&trk=eml-b2_anet_digest_weekly-group_discussions-16-grouppost~disc~0&trkEmail=eml-b2_anet_digest_weekly-group_discussions-16-grouppost~disc~0-null-1d1wwq~izovopiy~m4&lipi=urn%3Ali%3Apage%3Aemail_b2_anet_digest_weekly%3B142PI%2FmfTHWulMFX5oqYEQ%3D%3D

and I just come to work with one of my client and 50% of guys here is an indian, happy . next time please help to give some explanation which Indian like to hear, appreciated.

:):)

How do I know what Indians like to hear?
Do you want me to lie?

hi,

  wait, you are an Indian, right ? forget about it if you are not ! I am not insult you in anyway and this doesn't involve any culture conflict.
 thought  we should not discuss here.

No, I'm not Indian.
Move on :)

ok ,I think I have to close this ticket for now and i will come back if I needed.

but one thing, is it said that since SQL 2012 AOG,  we can already setup read only operation in the second replica and backup from that read only DB copy ?

Well, that's since AG exists so nothing new on that.

I just learn from MS that it seems it is only since SQL 2014 MS allow us to backup on secondary replica.

so that backup and restore back to the primary replica even it is diff hardware? server name is diff !

Show me where you learnt from.

this means I am wrong and since SQL 2012 we can do backup on secondary replica,right?

so from SQL 2012 to SQL 2014, only good thing implemented is 8 replicas and the secondary replica can help on read only reporting in a roudn robin manner ?

so that backup and restore back to the primary replica even it is diff hardware? server name is diff between primary replica and secondary replica.

Since always that you can perform a backup from any replica. Mind that some kind of backups aren't allowed in secondary replicas, like for example full and differential backups. Transaction backups are allowed as well full backups with the COPY_ONLY parameter.

the secondary replica can help on read only reporting in a roudn robin manner ?

What this means?