SQL Server DBA Interview Questions and Answers – Always ON – 2
I hope you enjoyed my last post on Always On. Here goes the final post on Always On feature.
- What is availability group wizard?
Availability Group Wizard is a GUI using SQL Server Management Studio to create and configure an AlwaysOn availability group in SQL Server 2012.
- What are the Restrictions on Availability Groups?
- Availability replicas must be hosted by different nodes of one WSFC cluster
- Unique availability group name: Each availability group name must be unique on the WSFC cluster. The maximum length for an availability group name is 128 characters.
- Availability replicas: Each availability group supports one primary replica and up to four secondary replicas. All of the replicas can run under asynchronous-commit mode, or up to three of them can run under synchronous-commit mode.
- Maximum number of availability groups and availability databases per computer: The actual number of databases and availability groups you can put on a computer (VM or physical) depends on the hardware and workload, but there is no enforced limit. Microsoft has extensively tested with 10 AGs and 100 DBs per physical machine.
- Do not use the Failover Cluster Manager to manipulate availability groups:
- What are the minimum requirements of a database to be part of the Always ON Availability Group?
- Availability groups must be created with user databases. Systems databases can’t be used.
- Databases must be read-write. Read-only databases aren’t supported.
- Databases must be multiuser databases.
- Databases can’t use the AUTO_CLOSE feature.
- Databases must use the full recovery model, and there must be a full backup of them.
- A given database can only be in a single availability group, and that database can’t be configured to use database mirroring.
- How many read-write and read only databases replica can be configure in SQL Server 2012 and 2014?
- SQL Server 2012 supported a maximum of four secondary replicas.
- With SQL Server 2014, AlwaysOn Availability Groups now supports up to eight secondary replicas.
- Is it possible to setup Log Shipping on a database which is part of Availability Group?
Yes, it can be configured.
- Is it possible to setup Replication on a database which is part of Availability Group?
Yes, It is possible.
- FILESTEAM is supported by Availability Group or not?
Yes, it is supported.
- Change Data Capture supported by Always ON or not?
Yes, it is supported.
- Database Snapshot supported by Always ON or not?
Yes, it is supported.
- Can system database participate in AG?
No.
- Suppose primary database became in suspect mode. Will AG have failover to secondary replica?
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.
- Can we have two primary availability replica?
No, it is not possible.
- Is AG support automatic page repair for protection against any page corruption happens?
Yes, It automatically takes care of the automatic page repair.
- How to Add a secondary database from an availability group?
ALTER DATABASE Db1 SET HADR AVAILABILITY GROUP = MyAG;
- How to remove a secondary database from an availability group?
ALTER DATABASE MyDb2 SET HADR OFF; GO
- Is SQL Server 2012 AlwaysOn support encryption and compression?
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
17. Does AG support Bulk-Logged recovery model?
No, it does not.
18. Can a database belong to more than one availability group?
No.
19. What is session timeout period?
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.
20. How to change the Session Timeout period?
ALTER AVAILABILITY GROUP AccountsAG MODIFY REPLICA ON 'INSTANCE09' WITH (SESSION_TIMEOUT = 15);
21. What are different synchronization preferences are available?
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.
22. How many types of Data synchronization preference options are available in Always ON?
There are three options- Full, Join only, or Skip initial data synchronization.
23. Is it possible to run DBCC CHECKDB on secondary replicas?
Yes.
24. Can I redirect the read-only connections to the secondary replica instead of Primary replica?
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.
25. If a DBA expands a data file manually on the primary, will SQL Server automatically grow the same file on secondaries?
It will be automatically expanded on the Secondary replica.
26. Is it possible to create additional indexes on read-only secondary replicas to improve query performance?
No, it is not possible.
27. Is it possible to create additional statistics on read-only secondaries to improve query performance?
No. But we can allow SQL Server to automatically create statistics on read-only secondary replicas.
28. Can we manually fail over to a secondary replica?
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
29. What is read intent option?
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.
30. Does AlwaysOn Availability Groups repair the data page corruption as Database Mirroring?
Yes. If a corrupt page is detected, SQL Server will attempt to repair the page by getting it from another replica.
31. What are the benefits of Always on feature?
- 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
References: Thanks to the all the SQL Server techies who wrote and shared the valuable information in the below blogs which helped me a lot to prepare this series of Questions. Also big thanks to Microsoft Documentation which contains each and everything about their product.
https://www.simple-talk.com/sql/database-administration/sql-server-2012-alwayson/
