SQL DBA AlwaysOn
Interview Questions and Answers – 2
One
of the best known feature introduced in SQL Server 2012 is “AlwaysOn”
which makes use of existing HA/DR features and provide additional
features like Availability Groups. This article is for SQL Server
DBA’s who are preparing for interviews, this include basic and
advanced level SQL DBA AlwaysOn Interview questions part-2.
SQL DBA AlwaysOn Interview Questions – 2
Q.
What is availability group wizard?
Ans:
Availability
Group Wizard is a GUI using SQL Server Management Studio to create
and configure an AlwaysOn availability group in SQL Server 2012.
Q.
Suppose primary database became in suspect mode. Will AG have
failover to secondary replica?
Ans:
Issues
at the database level, such as a database becoming suspect due to the
loss of a data file, deletion of a database, or corruption of a
transaction log, do not cause an availability group to failover.
Q.
Can we have two primary availability replica?
Ans:
No,
it is not possible.
Q.
Does AG support automatic page repair for protection against any page
corruption happens?
Ans:
Yes,
It automatically takes care of the automatic page repair.
Q.
How to add a secondary database from an availability group using
T-SQL?
Ans:
ALTER
DATABASE Db1 SET HADR AVAILABILITY GROUP = <AGName>;
Q.
How to remove a secondary database from an availability group?
Ans:
ALTER
DATABASE <DBName> SET HADR OFF;
Q.
SQL Server 2012 AlwaysOn supports encryption and compression?
Ans:
SQL
Server 2012 AlwaysOn Availability Group supports row and page
compression for tables and indexes, we can use the data compression
feature to help compress the data inside a database, and to help
reduce the size of the database. We can use encryption in SQL Server
for connections, data, and stored procedures; we can also perform
database level encryption: Transparent data encryption (TDE). If you
use transparent data encryption (TDE), the service master key for
creating and decrypting other keys must be the same on every server
instance that hosts an availability replica for the availability
group
Q.
Does AG support Bulk-Logged recovery model?
Ans:
No,
it does not.
Q.
Can a database belong to more than one availability group?
Ans:
No.
Q.
What is session timeout period?
Ans:
Session-timeout
period is a replica property that controls how many seconds (in
seconds) that an availability replica waits for a ping response from
a connected replica before considering the connection to have failed.
By default, a replica waits 10 seconds for a ping response. This
replica property applies only the connection between a given
secondary replica and the primary replica of the availability group.
Q.
How to change the Session Timeout period?
Ans:
ALTER
AVAILABILITY GROUP <AG Name>
MODIFY
REPLICA ON ‘<Instance Name>’ WITH (SESSION_TIMEOUT = 15);
Q.
What are different synchronization preferences are available?
Ans:
As
part of the availability group creation process, We have to make an
exact copy of the data on the primary replica on the secondary
replica. This is known as the initial data synchronization for the
Availability Group.
Q.
How many types of Data synchronization preference options are
available in Always ON?
Ans:
There
are three options- Full, Join only, or Skip initial data
synchronization.
Q.
Is it possible to run DBCC CHECKDB on secondary replicas?
Ans:
Yes.
Q.
Can I redirect the read-only connections to the secondary replica
instead of Primary replica?
Ans:
Yes,
we can specify the read_only intent in the connection string and add
only secondaries (not the primary) to the read_only_routing list. If
you want to disallow direct connections to the primary from read_only
connections, then set its allow_connections to read_write.
Q.
If a DBA expands a data file manually on the primary, will SQL Server
automatically grow the same file on secondaries?
Ans:
Yes!
It will be automatically expanded on the Secondary replica.
Q.
Is it possible to create additional indexes on read-only secondary
replicas to improve query performance?
Ans:
No,
it is not possible.
Q.
Is it possible to create additional statistics on read-only
secondaries to improve query performance?
Ans:
No.
But we can allow SQL Server to automatically create statistics on
read-only secondary replicas.
Q.
Can we manually fail over to a secondary replica?
Ans:
Yes.
If the secondary is in synchronous-commit mode and is set to
“SYNCHRONIZED” you can manually fail over without data loss. If
the secondary is not in a synchronized state then a manual failover
is allowed but with possible data loss
Q.
What is read intent option?
Ans:
There
are two options to configure secondary replica for running read
workload. The first option ‘Read-intent-only’ is used to provide
a directive to AlwaysOn secondary replica to accept connections that
have the property ApplicationIntent=ReadOnly set. The word ‘intent’
is important here as there is no application check made to guarantee
that there are no DDL/DML operations in the application connecting
with ‘ReadOnly’ but an assumption is made that customer will only
connect read workloads.
Q.
Does AlwaysOn Availability Groups repair the data page corruption as
Database Mirroring?
Ans:
Yes.
If a corrupt page is detected, SQL Server will attempt to repair the
page by getting it from another replica.
Q.
What are the benefits of Always on feature?
Ans:
-
Utilizing database mirroring for the data transfer over TCP/IP
-
providing a combination of Synchronous and Asynchronous mirroring
-
providing a logical grouping of similar databases via Availability Groups
-
Creating up to four readable secondary replicas
-
Allowing backups to be undertaken on a secondary replica
-
Performing DBCC statements against a secondary replica
-
Employing Built-in Compression & Encryption
Q.
How much network bandwidth will I need?
Ans:
For
a really rough estimate, sum up the amount of uncompressed
transaction log backups that you generate in a 24-hour period. You’ll
need to push that amount of data per day across the wire. Things get
trickier when you have multiple replicas – the primary pushes
changes out to all replicas, so if you’ve got 3 replicas in your DR
site, you’ll need 3x the network throughput. Calculating burst
requirements is much more difficult – but at least this helps you
get started.
Q.
What’s the performance overhead of a synchronous replica?
Ans:
From
the primary replica, ping the secondary, and see how long (in
milliseconds) the response takes. Then run load tests on the
secondary’s transaction log drive and see how long writes take.
That’s the minimum additional time that will be added to each
transaction on the primary. To reduce the impact, make sure your
network is low-latency and your transaction log drive writes are
fast.
Q.
How far behind will my asynchronous replica be?
Ans:
The
faster your network and your servers are, and the less transactional
activity you have, the more up-to-date each replica will be. I’ve
seen setups where the replicas are indistinguishable from the
primary. However, I’ve also seen cases with underpowered replicas,
slow wide area network connections, and heavy log activity (like
index maintenance) where the replicas were several minutes behind.
Q.
What’s the difference between AGs in SQL 2012 and SQL 2014?
Ans:
SQL
Server 2014’s biggest improvement is that the replica’s databases
stay visible when the primary drops offline – as long as the
underlying cluster is still up and running. If I have one primary and
four secondary replicas, and I lose just my primary, the secondaries
are still online servicing read-only queries. (Now, you may have
difficulties connecting to them unless you’re using the secondary’s
name, but that’s another story.) Back in SQL 2012, when the primary
dropped offline, all of the secondaries’ copies immediately dropped
offline – breaking all read-only reporting queries.
Q:
How do I monitor AlwaysOn Availability Groups?
Ans:
That’s
rather challenging right now. Uptime monitoring means knowing if the
listener is accepting writeable connections, if it’s correctly
routing read-only requests to other servers, if all read-only
replicas are up and running, if load is distributed between replicas
the way you want, and how far each replica is running behind.
Performance monitoring is even tougher – each replica has its own
statistics and execution plans, so queries can run at totally
different speeds on identical replicas.
Q:
How does licensing work with AlwaysOn Availability Groups in SQL 2012
and 2014?
Ans:
All
replicas have to have Enterprise Edition. If you run queries,
backups, or DBCCs on a replica, you have to license it. For every
server licensed with Software Assurance, you get one standby replica
for free – but only as long as it’s truly standby, and you’re
not doing queries, backups, or DBCCs on it.
Q:
Can I use AlwaysOn Availability Groups with Standard Edition?
Ans:
Not
at this time, but it’s certainly something folks have been asking
for since database mirroring has been deprecated.
Q:
Do AlwaysOn AGs require shared storage or a SAN?
Ans:
No,
you can use local storage, like cheap SSDs.
Q:
Do Availability Groups require a Windows cluster?
Ans:
Yes,
they’re built atop Windows failover clustering. This is the same
Windows feature that also enables failover clustered instances of SQL
Server, but you don’t have to run a failover clustered instance in
order to use AlwaysOn Availability Groups.
Q:
Do I need a shared quorum disk for my cluster?
Ans:
No
Q:
If I fail over to an asynchronous replica, and it’s behind, how do
I sync up changes after the original primary comes back online?
Ans:
When
I go through an AG design with a team, we talk about the work
required to merge the two databases together. If it’s complex (like
lots of parent/child tables with identity fields, and no update
datestamp field on the tables), then management agrees to a certain
amount of data loss upon failover. For example, “If we’re under
fifteen minutes of data is involved, we’re just going to walk away
from it.” Then we build a project plan for what it would take to
actually recover >15 minutes of data, and management decides
whether they want to build that tool ahead of time, or wait until
disaster strikes.
References:
We
should be thankful for the authors who has given an excellent
explanations on AlwaysOn topics from SQL Server 2012 and SQL Server
2014. Here is the list of top references:
No comments:
Post a Comment