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:

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:

Technet.microsoft.com

sql dba alwayson scenario based interview questions – 3