Q. Do we need SQL Server Cluster instances to configure Always ON?
Ans:
No we don’t need SQL Server Cluster instances to configure Always ON.
Q. Do we need shared storage to configure Always ON?
Ans:
No, we don’t need shared storage.
Q. What is the Difference between Asynchronous‐commit mode and Synchronous‐
commit mode?
Ans:
Asynchronous‐commit mode:
An availability replica that uses this availability mode is known as an asynchronous‐
commit replica. Under asynchronous‐commit mode, the primary replica commits
transactions without waiting for acknowledgement that an asynchronous‐commit
secondary replica has hardened the log. Asynchronous‐commit mode minimizes
transaction latency on the secondary databases but allows them to lag behind the
primary databases, making some data loss possible.
Synchronous‐commit mode:
An availability replica that uses this availability mode is known as a synchronous‐
commit replica. Under synchronous‐commit mode, before committing transactions, a
synchronous‐commit primary replica waits for a synchronous‐commit secondary
replica to acknowledge that it has finished hardening the log. Synchronous‐commit
mode ensures that once a given secondary database is synchronized with the primary
database, committed transactions are fully protected.
This protection comes at the cost of increased transaction latency.
Q. What is called Primary replica?
Ans:
The availability replica that makes the primary databases available for read‐write
connections from clients is called Primary Replica. It sends transaction log records for
each primary database to every secondary replica.
Q. What is called Secondary replica?
Ans:
An availability replica that maintains a secondary copy of each availability database,
and serves as a potential failover targets for the availability group. Optionally, a
secondary replica can support read‐only access to secondary databases can support
creating backups on secondary databases.
Q. What is Availability Group listener?
Ans:
Availability Group Listener is a server name to which clients can connect in order to
access a database in a primary or secondary replica of an AlwaysOn availability group.
Availability group listeners direct incoming connections to the primary replica or to a
read‐only secondary replica.
Q. What are Readable Secondary Replicas?
Ans:
The AlwaysOn Availability Groups active secondary capabilities include support for
read‐only access to one or more secondary replicas (readable secondary replicas). A
readable secondary replica allows read‐only access to all its secondary databases.
However, readable secondary databases are not set to read‐only. They are dynamic. A
given secondary database changes as changes on the corresponding primary
database are applied to the secondary database.
Q. What are the benefits of Readable Secondary Replicas?
Ans:
Directing read‐only connections to readable secondary replicas provides the following
benefits:
Offloads your secondary read‐only workloads from your primary replica, which
conserves its resources for your mission critical workloads. If you have mission
critical read‐workload or the workload that cannot tolerate latency, you should run
it on the primary.
Improves your return on investment for the systems that host readable secondary
replicas.
In addition, readable secondaries provide robust support for read‐only operations, as
follows:
Temporary statistics on readable secondary database optimize read‐only queries.
For more information, see Statistics for Read‐Only Access Databases, later in this
topic.
Read‐only workloads use row versioning to remove blocking contention on the
secondary databases. All queries that run against the secondary databases are
snapshot isolation transaction level, even when other transaction isolation
levels are explicitly set. Also, all locking hints are ignored. This eliminates
reader/writer contention.
Q. How many synchronous secondary replicas can I have?
Ans:
We can have up to 2 synchronous replicas, but we are not required to use
any. We could run all Secondaries in asynchronous mode if desired
Q. Can we use a secondary for reporting purpose?
Ans:
Yes. An active secondary can be used to offload read‐only queries from the
primary to a secondary instance in the availability group.
Q. Can we use secondary replicas to take the db backups?
Ans:
Yes. An active secondary can be used for some types of backups
Q. What all types of DB backups are possible on Secondary Replicas?
Ans:
BACKUP DATABASE supports only copy‐only full backups of databases, files, or
filegroups when it is executed on secondary replicas. Note that copy‐only backups
do not impact the log chain or clear the differential bitmap.
Differential backups are not supported on secondary replicas.
Q. Can we take Transaction log backups on the secondary replicas?
Ans:
Yes, we can take transaction log backups on the secondary replicas without
COPY_ONLY option.
